PeopleSoft Reporting Web Services

While browsing through Peoplebooks for API syntax I ran into this topic related to Reporting web services and I was pleasantly surprised that this feature is available out of the box but I don't think is used frequently. Looks like it has been around since 8.56.x  or maybe even before. So PeopleSoft provides SOAP as well as REST based web services to expose PeopleSoft data based on PS Query or Connected query to external systems. I think this is pretty cool. So as long as the data set can be constructed in PS Query or Connected Query it can be exposed out to a 3rd party service using out of the box infrastructure. This greatly simplifies elaborate design and development time of application engine programs which are generally used for building interfaces.

PeopleSoft provides QAS_QRY_SERVICE which is the service for Query Access Manager.

and PROCESSREQUEST which is the service for Process Scheduler. Via these services once can create and get query and process request type items. I tried out the query service to execute a query via Postman and it works like a charm.

There is lot of good info in PeopleBooks so I won't repeat all of that here. So I currently have two RSS feeds based on PS Query which I use for monitoring the status of process monitor processes and jobs and another one which monitors the asynchronous  services. I used these as the test cases to fetch data using the web services instead of RSS. Also setup a user which has access to the service operation and to the queries and can only run the queries. 

As I have the queries already built I tested out the REST service operation QAS_EXECUTEQRY_REST_GET to execute the queries. 

Example of the URI Template:

{OwnerType}/{QueryName}/{OutResultType}/{OutResultFormat}?isconnectedquery=
{isConnectedQuery}&maxrows={MaxRow}&prompt_psqueryname={Prompt_PSQueryName*}
&prompt_uniquepromptname={Prompt_UniquePromptName*}&prompt_fieldvalue=
{Prompt_FieldValue*}&filterfields={FilterFields*}&json_resp={json_response}

Provided QueryName, OwnerName is PUBLIC as the query I am using is public query, OutResultType is JSON as I want the results back in JSON format, OutResultFormat is NONFILE as I want the response in message object, isconnectedquery = N, maxrows set it to 100 ( I would never get so many rows back), json_resp set it to true, did not set anything for rest of the URI parameters. 

HTTP GET https://myserver/PSIGW/RESTListeningConnector/PSFT_HR/ExecuteQuery.v1/PUBLIC/N_MY_QUERY/JSON/NONFILE?isconnectedquery=N&maxrows=100&prompt_psqueryname=&prompt_uniquepromptname=&prompt_fieldvalue=&filterfields=&json_resp=true 

The response is received as follows:

{

   "status":"success",

   "data":{

      "query":{

         "numrows":3,

         "queryname=":"N_MY_QUERY",

         "rows":[

            {

               "attr:rownumber":1,

               "PRCSINSTANCE":558373,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"PSJob",

               "PRCSNAME":"MYPRCS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Processing",

               "DISTSTATUS":"Scheduled"

            },

            {

               "attr:rownumber":2,

               "PRCSINSTANCE":558374,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"SQR Process",

               "PRCSNAME":"MYSQRPRCS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Processing",

               "DISTSTATUS":"Scheduled"

            },

            {

               "attr:rownumber":3,

               "PRCSINSTANCE":558375,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"SQR Report",

               "PRCSNAME":"MYPROCESS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Pending",

               "DISTSTATUS":"Scheduled"

            }

         ]

      }

   }

}

Following is an example when query does not return any results.

{

   "status":"success",

   "data":{

      "query":{

         "numrows":0,

         "queryname=":"N_MY_QUERY",

         "rows":[

         ]

      }

   }

}

XML Parser

XML related API has been around for some time now in PeopleSoft and this post is not discussing anything new. Just something that I wanted to pen down for future reference. 

So recently I have been working on a requirement where in I have to parse out a XML response and fetch data values in order to process them further. In this case the same tag element names repeat throughout the XML message and there are multiple levels in the message. The requirement was to traverse each "item" tag and fetch the value for "key" and "value" tags.

Format of the XML message is as shown below.

<?xml version="1.0" encoding="UTF-8"?> <root> <level1> <level2> <level3> <item> <key>FieldName1</key> <value>Value1</value> <type>String</type> </item> <item> <key>FieldName2</key> <value>Value2</value> <type>Date</type> </item> <item> <key>FieldName3</key> <value>Value3</value> <type>Number</type> </item> </level3> </level2> </level1> </root>



Wrote two methods, first one is way longer than the second one.
For both methods I am passing the "field_name_value" and getting back the &fieldvalue value.


Method one - longer.


Local XmlDoc &NewHireXMLDoc;
Local XmlNode &root, &level1, &level2, &level3, &item;
Local number &z, &y, &x, &w, &v;
Local string &sResp, &fieldvalue;
Local boolean &bRet;


rem &sResp is the XML message in string form;
   
&NewHireXMLDoc = CreateXmlDoc();
&bRet = &NewHireXMLDoc.ParseXmlString(&sResp);

&rootNode = &NewHireXMLDoc.DocumentElement;

For &z = 1 To &rootNode.ChildNodeCount
   If &root.GetChildNode(&z).NodeName = "level1" Then
      &level1= &root.GetChildNode(&z);
      For &y = 1 To &level1.ChildNodeCount
         If &level1.GetChildNode(&y).NodeName = "level2" Then
            &level2= &level1.GetChildNode(&y);
            For &x = 1 To &level2.ChildNodeCount
               If &level2.GetChildNode(&x).NodeName = "level3" Then
                  &level3 = &level2.GetChildNode(&x);
                  For &w = 1 To &level3.ChildNodeCount
                     If &level3.GetChildNode(&w).NodeName = "item" Then
                        &item = &level3.GetChildNode(&w);
                        For &v = 1 To &item.ChildNodeCount
                           If &item.GetChildNode(&v).NodeName = "key" And
                                 &item.GetChildNode(&v).NodeValue = "field_name_value" Then
                              &fieldvalue = &item.GetChildNode(&v + 1).NodeValue;
                              &v = &v + 1;
                           End-If;
                       End-For;
                     End-If;
                  End-For;
               End-If;
            End-For;
         End-If;
      End-For;
   End-If;
End-For;



Method two - shorter

Local XmlDoc &NewHireXMLDoc;
Local string &sResp, &fieldvalue;
Local boolean &bRet;

Local array of XmlNode &items;
Local number &y, &z;

rem &sResp is the XML message in string form;
   
&NewHireXMLDoc = CreateXmlDoc();
&bRet = &NewHireXMLDoc.ParseXmlString(&sResp);


&items = &NewHireXMLDoc.DocumentElement.FindNodes("level1/level2/level3/item");
   
   For &z = 1 To &items.Len
      For &y = 1 To &items [&z].ChildNodeCount
         
         If &items [&z].GetChildNode(&y).NodeName = "key" And
               &items [&z].GetChildNode(&y).NodeValue = "field_name_value" Then
            &fieldvalue = &items [&z].GetChildNode(&y + 1).NodeValue;
            Break;
         End-If;
      End-For;

   End-For;

Creating JSON request for REST web services

PeopleSoft provides Document technology to be used to generate JSON request messages but in my experience they are very restrictive especially when working on integrating with 3rd party web services. So following is what I did to generate a JSON request message to post to a 3rd party REST web service. 

The request that I have to generate is in the following form.
[
   {
      "attrib1":"value1",
      "attrib2":"value2",
      "attrib3":{
         "attrib3_1":"values3_1",
         "attrib3_2":"values3_2",
         "attrib3_3":"values3_3",
         "attrib3_4":"values3_4"
      }
   }
]

I am running PT 8.57.x and at this time its not possible to build a document with the root node as an array as shown in the example below. Also I have nested compounds which is also a challenge, the parent compound does not have a label where as the child does. 
So to build something like above I am using the CreateJsonBuilder API provided by PeopleSoft.

Local JsonBuilder &jbldr = CreateJsonBuilder();
Local JsonArray &jArray;

Local string &json;
Local message &request, &response;
Local boolean &bRet;

&jbldr.StartArray(""); /* no label */
 &jbldr.StartObject(""); /* no label */
  &jbldr.AddProperty("attrib1", "value1");
  &jbldr.AddProperty("attrib2", "value2");
   &jbldr.StartObject("attrib3"); /* need a label */
    &jbldr.AddProperty("attrib3_1", "value3_1");
    &jbldr.AddProperty("attrib3_2", "value3_2");
    &jbldr.AddProperty("attrib3_3", "value3_3");
    &jbldr.AddProperty("attrib3_4", "value3_4");
   &jbldr.EndObject("attrib3"); /* closing out the compound or JSONObject */
 &jbldr.EndObject("");
&jbldr.EndArray("");


/* this will return the array just like what I want */
&jArray = &jbldr.GetRootNode().GetJsonObject().GetJsonArray("");
&json = &jArray.ToString();

Created a basic non-rowset based message and assigned that as the request message in my service operation. Use this method to set the content for the message segment for a non-rowset-based message only.

&bRet = &request.SetContentString(&json);
&response = %IntBroker.SyncRequest(&request);

That's it, works like a charm.

Event Mapping (managing customizations)

Event Mapping is a PeopleSoft delivered framework that helps applications and customers adapt PeopleSoft to meet their business needs with minimum upgrade impact. It introduces business logic as a configuration instead of customization. Event mapping allows the user to develop reusable code and artifacts and ensures that the customer’s code will not be overwritten by PeopleSoft delivered code during an upgrade.

Following is what I have tried in PeopleTools 8.56.07 and 8.57.08 on HCM 9.2 (PUM 31 and beyond). For this POC I am going to call my custom application package peoplecode on SavePostChange event on the DEPARTMENT_TBL component to perform something like sending out a notification. In the absence of the event mapping functionality I would have written this code in SavePostChange event of the DEPARTMENT_TBL component thus customizing the delivered object.

So, step one is to build the application package peoplecode. Import of PT_RCF:ServiceInterface is the key. The class name can be anything but the method name has to be "Execute".

import PT_RCF:ServiceInterface;

class FoundationDataNotify extends PT_RCF:ServiceInterface
   method Execute();
end-class;

method Execute
   /+ Extends/implements PT_RCF:ServiceInterface.execute +/

   /* custom logic to send out notification goes here */
end-method;
  
Once the package is developed navigate to PeopleTools > Portal > Related Content Service > Define Related Content Service. Create a new definition and select "URL Type" as application class and then provide the package details. For this POC I selected "Public Access" under security options. Save the service definition.

Then navigate to PeopleTools > Portal > Related Content Service > Manage Related Content Service, Event Mapping tab. Click on the "Map Application Classes to Component Events" link and then navigate the portal tree to find the component on which this event would be based on or associated with. In my case it would be the department table component located under Set Up HCM > Foundation Tables > Organization > Departments.

Under "Component Level Event Mapping" section, select "enable" check-box, event name would be SavePostChange and select the Service ID created in the earlier step. Set sequence number to 1 and processing sequence as "Post Process". Save the page and that is it. 

Navigate to the department component make a change and save the page and verify that the custom application package peoplecode fires.


The PeopleCode Editor provides an Event Mapping button in the dynamic Application Designer toolbar. For an application developer, the Event Mapping button is a visual indicator of custom PeopleCode programs mapped to events of a component, component page, component record, or component record field. This button is not available for page level event mapping.

So opened component peoplecode of component DEPARTMENT_TBL and clicked on the button to produce the following output.

Right-clicking on the application package code and selecting "Event Mapping References" in the pop-up menu as shown below should display the component but that did not work.

"Event Mapping References" tab is empty as shown below.

So I guess this might work in future releases or maybe I don't have something configured correctly for this to work.

JSON Parser

PeopleSoft has undocumented JSON related API and this post covers some of the routines that I have tried to dynamically parse a json response. I think this API calls were made available in PT 8.56.x as part of PeopleSoft's inbuilt integration with ElasticSearch. 

I am making a RESTful web service call to a service hosted by a 3rd party vendor from PeopleSoft; so PeopleSoft is a consumer of the service. Focus here is to parse the response from the service so I am not covering how the service is setup and the request part of the service. 

So I have two types of responses. Response 1 as shown below 

{

    "error": {

        "message": "Some message text",

        "detail": "detail text about the error"

    },

    "status": "failure"

}

and Response 2 as follows.

{

   "import_set":"Import set value",

   "staging_table":"tablename",

   "result":[

      {

         "status":"updated",

         "error_message":"Unable to format 01-01-2019 using format string yyyyMMdd  for field hire_dt"

      }

   ]

}

In Response 1, I has 2 children, viz "error" and "status", whereas Response 2 has 3 children, import_set, staging_table and result.

In Response 1, "error" is a JSonObject which has 2 more children, message and detail. In Response 2, "result" is a JSonArray which has 2 children status and error_message.

Local string &content, &propName, &propValue;

Local JsonParser &parser;

Local JsonObject &jsonRoot, &jsonDetails;

Local JsonArray &jArray;

Local boolean &ret; 

Local number &i, &j, &k, &l;

&parser = CreateJsonParser(); /* this is the undocumented API */

&ret = &parser.Parse(&content); /* &content is the json response as a string */

&jsonRoot = &parser.GetRootObject();

For &i = 1 To &jsonRoot.GetChildCount()
/* for Response 1, following will get status tag and its value */
/* for Response 2, following will get import_set, staging_table and its values */

   &propName = &jsonRoot.GetPropertyNameAt(&i);

   &propValue = &jsonRoot.GetProperty(&propName);

   /* if there is a nested value then its either JsonArray or JsonObject */

   Evaluate &propValue

   When = "JsonArray"
/* this will return status and error_message which are in Response 2 */

      &jArray = &jsonRoot.GetJsonArray(&propName);

      For &j = 1 To &jArray.Length()

         &jsonDetails = &jArray.GetJsonObject(&j);

         For &k = 1 To &jsonDetails.GetChildCount()

            &propName = &jsonDetails.GetPropertyNameAt(&k);

            &propValue = &jsonDetails.GetProperty(&propName);

         End-For;

      End-For;

      Break;

   When = "JsonObject"
/* this will return message and detail which are in Response 1 */

      &jsonDetails = &jsonRoot.GetJsonObject(&propName);

      &numCnt = &jsonDetails.GetChildCount();

      For &l = 1 To &jsonDetails.GetChildCount()

         &propName = &jsonDetails.GetPropertyNameAt(&l);

         &propValue = &jsonDetails.GetProperty(&propName);

      End-For;

      Break;

   When-Other;

      /* when not JsonArray or JsonObject, get prop name and value which is at root level */

      Break;

   End-Evaluate;

End-For;

Hope this helps.