Composite Query

Composite Query enables us to combine data from existing queries and then apply filters, aggregates, and so on before presenting the report results, which show the combined data set. Composite Query retrieves multiple levels of related information on existing queries and presents the combined data as a single and flattened query result. There is good amount of info on how these work, limitation etc so I am not going to cover those here. I found them pretty useful to create inline SQL or query which isn't  possible via the regular PS Query (Query Manager) or even via Connected query feature in PeopleSoft.

So for this POC I am running PT 8.58.05 on HCM 9.2 (PUM 35). The SQL or output that I am trying to re-produce is as follows. 

I am listing employees from PS_JOB but I want to list their min ORIG_HIRE_DT value regardless of their current relationship in the orignatization. 

So I created two simple queries in Query Manager as follows.

Parent_Query

SELECT A.EMPLID, A.EMPL_RCD, (CONVERT(CHAR(10),A.EFFDT,121)), A.EFFSEQ,  (CONVERT(CHAR(10),A.LAST_HIRE_DT,121))
  FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1
  WHERE ( A.EMPLID = A1.EMPLID
    AND A.EMPL_RCD = A1.EMPL_RCD
    AND A1.OPRID = 'PS'
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT) ))

Inline_Query 

SELECT A.EMPLID, A.ORG_INSTANCE_ERN, MIN( (CONVERT(CHAR(10),A.ORIG_HIRE_DT,121)))
  FROM PS_PER_ORG_INST A, PS_PERS_SRCH_QRY A1
  WHERE ( A.EMPLID = A1.EMPLID
    AND A1.OPRID = 'PS')
  GROUP BY  A.EMPLID,  A.ORG_INSTANCE_ERN

Navigated to Reporting Tools > Composite Query > Composite Query Manager and stepped through the wizard.

1. Gave a name to the composite query

2. Provided the Query 1 and Query values. So in my case Query 1 = Parent_Query and Query 2 = Inline_Query. Unfortunately we cannot have more than one inline SQLs, so this looks like a limitation.

3.   Sepcified Query Joins as shown below.

4. On the Output fields page I made the selections as shown below.

5.  On the final page set the status as active and saved the page.

Hovering over the gear icon next to the SQL button presents the option to Run/Preview the data or ouput.

Application Engine Plug-ins

PeopleTools provides a feature where in we can alter the SQL or PeopleCode actions of any Application Engine program, without customizing the delivered AE program. The configured plug-in action is executed at runtime. This effectively allows us to modify the code without actually customizing or re-designing the Application Engine program.

Configure the selected SQL or PeopleCode action of the Application Engine program by adding new SQL, PeopleCode, or both and/or by replacing the existing SQL or PeopleCode action through PIA in the AE Action Plugin page (PeopleTools, Application Engine).

Note: You can add or replace only SQL and PeopleCode actions in the Application Engine program. Only sections with steps having SQL or PeopleCode actions of the Application Engine program, which you plan to configure, will be displayed in PIA. This feature is not designed to add new sections or steps to an existing Application Engine program.

Actions belonging to the same step of the same section of the Application Engine program can have multiple plug-in actions defined. The plug-in action type does not have to match the action type that is selected for configuration. You can re-use the same SQL or PeopleCode plug-in action multiple times for different Application Engine programs.

The Application Engine action that is being configured cannot be used as a plug-in to configure another Application Engine program. Also, you cannot define a plug-in for the Application Engine action that is already been used as a plug-in.

For example, if Application Engine program A action Y is configured to use Application Engine program B action X as a plug-in, then Application Engine program A action Y cannot be used as a plug-in for any other Application Engine program. Also, you cannot configure a plug-in for Application Engine program B action X.

For the following test I am running PT 8.58.05, HCM 9.2 on PUM 35.

So AEMINITEST is my delivered program and then I created another standard AE program called as N_PLUGIN. My custom AE just has one step with action as peoplecode under MAIN. I have enbled disable restart under object properties of my custom AE.

In my custom peoplecode action I have the following code.

The delivered AE AEMINITEST has 2 steps under MAIN, first one is a SQL and then second one is peoplecode. I added a line of text to the peoplecode to understand the flow (obvisously this is not necessary).

Next logged into PIA and navigated to PeopleTools > Application Engine > AE Action Plugin and pulled up AEMINITEST.

I tried the following 4 tests.

1. Run my peoplecode action after AEMINITEST's peoplecode 

Value of variable TEST in AEMINITEST is 2 (0,0)

Value of variable TEST in N_PLUGIN is 20 (0,0)
Application Engine program AEMINITEST ended normally

Here I tried if I can display the value of the variable in my N_PLUGIN prgram which is set in the parent AEMINITEST program. This would work only if the variable is declared as global or component, not as local or if its auto declared. Similarly values from AET records could possibly be shared.   

2. Run my peoplecode action before AEMINITEST's peoplecode

Value of variable TEST in N_PLUGIN is 20 (0,0)

Value of variable TEST in AEMINITEST is 2 (0,0)
Application Engine program AEMINITEST ended normally

3. Run my peoplecode action replacing AEMINITEST's peoplecode

Value of variable TEST in N_PLUGIN is 20 (0,0)
Application Engine program AEMINITEST ended normally

4. Replace AEMINITEST's SQL with my peoplecode action

Value of variable TEST in N_PLUGIN is 20 (0,0)

Value of variable TEST in AEMINITEST is 2 (0,0)
Application Engine program AEMINITEST ended normally

So this is a great feature and will definitely assist when customizing Application Engine programs.

XMLP BI Publisher sub-templates

Recently was presented with this challenge which turned out to be good enough exercise and hence warranted this post. Request was to display appropriate logo in the report header based on data available in the source XML file. 

For this exercise I am running PT 8.58.05 and BI Publisher 12.2.1.4.0 and rtf templates. My main report is called parent.rtf, sub-template is called headers.rtf and data source xml file is data.xml. The goal is to print or display the appropriate logo in the final report based on the data value provided in the XML file. 

In the main report, under MS Word's heading section added the following code. This is in regular text and not in any form field. Instead of choose (which works like peoplecode evaluate) I could have also used "If - Then - Else", but I wanted to try how "choose" works in XMLP.

<?import:file:C:///temp/headers.rtf?>
<?choose:?>
<?when:logo='Oracle'?>
    <?call:Oracle?>
<?end when?>
<?when:logo='Microsoft'?>
    <?call:Microsoft?>
<?end when?>
<?when:logo='Apple'?>
    <?call:Apple?>
<?end when?>
<?otherwise:?>
    <?call:Default?>
<?end otherwise?>
<?end choose:?>  

While testing this rtf locally or via Template Viewer the following import command will work, provided the headers.rtf file is available under c:\temp folder.

<?import:file:C:///temp/headers.rtf?>

But once we are ready to deploy and run this in the PIA environment, the line has to be changed as shown below. Here "STDHEADER" is the name provided while creating/uploading the headers rtf template in PIA under content library.

 

<?import:psxmlp://STDHEADER?>

In the headers.rtf I have defined the various headers as shown below. The XML tags are in regular text and not form fields.

<?template:Oracle?>

Oracle image was added here.

<?end template?>

<?template:Microsoft?>

Microsoft image was added here.

<?end template?>

<?template:Apple?>

Apple image was added here.

<?end template?>

<?template:Default?>

A default image was added here.

<?end template?>

My data source data.xml has the following structure where in I have the value for the logo to be used.

<?xml version="1.0" encoding="UTF-8"?>

<Start>

   <Heading>

      <logo>Apple</logo>

   </Heading>

   <ReportData>

      ...

   </ReportData>

</Start>

If you are receiving errors while testing this solution locally then the following has to be done. Under your local workstation install of the BI Publisher plugin you will have to modify the xdo.cfg file and add the following line under the properties section.

if no xdo.cfg file exists then make a copy of xdo example.cfg as xdo.cfg and add the line. This property is available in Template Viewer, so you can just set it there if testing via template viewer.

<property name="xdk-secure-io-mode">false</property>

That's it works like a charm.

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;