Mass upload/download files

Recently I was presented with a requirement wherein the user should be able to mass upload files from their local workstation to PeopleSoft and then mass download the files from PeopleSoft to their local workstation. The files would be stored in the database record.

I am running PT 8.59.x and PeopleSoft does provide a couple of functions MAddAttachment and DetachAttachment for this exact requirement. 

The MAddAttachment function is pretty straight forward. I was able to call it via FieldChange event, where in a dialog box is presented to the user to select a single file or multiple files and the files are loaded to the URL value provided as part of the function parameter. The function also has a parameter which can limit the number of files uploaded at a time. Just like the AddAttachment function this function also converts the filenames which have special characters like a space, amersand, plus sign etc. to underscores. More info about this under the "Understanding the File Attachment Functions" and "File Name Considersations" section in Peoplebooks.

For my requirement I was displaying the files loaded via MAddAttachment in a grid on the page. Now I provided another button, and on FieldChange of this button the plan was to invoke DetachAttachment to run through the files in the grid and download them locally to the user's workstation. For some reason this does not work. The code would run through the grid but would download only the last file in the grid, no errors anywhere.  So following is what I did to get around this limitation.

I am using GetAttachment first to read through the grid rowset and download the files to a temporary location on the server. I am programmatically creating a folder structure to download the files to. Then I am using java clases to compress or zip up the files in the temporary folder. Process to compress files available here. Then I am using PutAttachment function to upload the single compressed/zip file back into the database. Finally I am calling DetachAttachment to download the compressed/zip file to the user's local workstation. As part of post-cleanup I am using DeleteAttachment to delete the compressed/zip file from the database and then using RemoveDirectory to delete the temporary location created on the sevrer to download the files to.

Peoplecode to zip/archive files

Following is a way to archive, compress or zip up files via peoplecode. Alternative would be to use command line calls to products like winzip, 7-zip etc. For this test I am running PT 8.59.x. The same should also be possible in slightly older Peopletools releases too, like PT 8.56.x

Oracle provides java classes that can be used for this task. This method will create a new archive file and add files to the root location of the archive or zip file. 

The solution uses the following three java classes that are delivered with PeopleTools

1. java.io.FileOutputStream

2. java.util.zip.ZipOutputStream

3. java.util.zip.ZipEntry

/* initialize Java Obejct &myArchive */
Local JavaObject &myArchive = CreateJavaObject("java.util.zip.ZipOutputStream", CreateJavaObject("java.io.FileOutputStream", &zipFileName, True));

In the above line of code &zipFileName is the complete path of the archive file that we will create.

&fileNametoAdd - will have the complete path to the file that we will be adding to the archive. 

In order to get the filename value from the file path do the following.

&FilePath = Split(&fileNametoAdd, "\");
&fname = &FilePath [&FilePath.Len];

Local JavaObject &ArchiveEntry = CreateJavaObject("java.util.zip.ZipEntry", &fname);

/* Add Archive entry to OutputStream */

&myArchive.putNextEntry(&ArchiveEntry);

/* now add the file to the archive */

/* Read source file into input stream */

Local JavaObject &in = CreateJavaObject("java.io.FileInputStream", &fileNametoAdd);

/* Java Array that will read bytes from input file */

Local JavaObject &filebuffer = CreateJavaArray("byte[]", 1024);

Local number &byteCount = &in.read(&filebuffer);

   

/* Read bytes from input file and load it byte array  - Do until all bytes are read*/

While &byteCount > 0

      /* Write Bytes of Data to corresponding Archive Output Stream */

      &myArchive.write(&filebuffer, 0, &byteCount);

      &byteCount = &in.read(&filebuffer);

End-While;

   

/* Close input stream */

&in.close();

/* Close archive */

&myArchive.close();

Securing PDF

Following is a way to secure pdf files generated via non-BI Publisher technology like SQR. For this test I am running PT 8.59.x. Should be available in slightly older Peopletools releases too, like PT 8.56.x

Oracle provides java classes that can be used for securing a pdf file. This method will add a password to the pdf file, so that a password is prompted to the user while opening the pdf as well as adds a digital signature to the pdf. This class does both so if the requirement is to only password protect a pdf file, we still have to add a signature but make it small or invisible. 

This method also requires a digital certificate signed by one of the approved adobe certificate authorities called as AATL. The certificate has to be pfx format (can be converted using tools like openssl). If a self-signed certificate is used, the process still works but the end user will receive a warning banner in adobe reader once the document is opened and will have to manually trust the certificate by adding it to the adbobe trust store. 

The solution uses two java classes that are delivered with PeopleTools

1. java.util.Properties

2. oracle.xdo.common.pdf.signature.PDFSignature

Local JavaObject &jProp = CreateJavaObject("java.util.Properties");

&jProp.setProperty("signature-enable", "True");
&jProp.setProperty("pdf-security", "True");
&jProp.setProperty("pdf-open-password", &EncryptPswd);
&jProp.setProperty("pdf-permissions-password", &EncryptPswd);
&jProp.setProperty("pdf-changes-allowed", "0");

&EncryptPswd is the password that will be used to open the pdf file. 

&inFile is the complete path to the source pdf file and &outFile is the complete path of the secured pdf file. 

Local JavaObject &pdfSignature = CreateJavaObject("oracle.xdo.common.pdf.signature.PDFSignature", &inFile, &outFile);
   
&pdfSignature.setConfig(&jProp);
&pdfSignature.setLocale("en");

&digPswd is the password of the pfx digitial certificate file and &digSign is the complete path to the pfx file. Make sure the paths use "//" insread of "/" or "\\" instead of "\".

&pdfSignature.init(&digPswd, &digSign);

Following plots the signature in the pdf file. If a signature is not needed then make it small or invisible. Adjust the values as required. 

&xCord = 0;

&yCord = 0;

&width = 0;

&height = 0;

&pageIndex = 1;

&sReason can be some text or blank if none is required.

      
Local JavaObject &jFloatArray = CreateJavaObject("float[]", &xCord, &yCord, &width, &height);
&pdfSignature.addSignatureField(&pageIndex, &jFloatArray, "PSoftSign");
&pdfSignature.sign("PSoftSign", &sReason);
&pdfSignature.cleanup();

clean-up memory once done.

&jProp = Null;

&pdfSignature = Null;

&jFloatArray = Null;

/* delete the un-encrypted file */

Local object &delFile = CreateJavaObject("java.io.File", &inFile);

&delFile.delete();

Displaying/exporting PeopleSoft image/picture files

There isn't a good way to display/export images or picture files deliverd by PeopleSoft. Some smaller images can be viewed via Application Designer but not the ones that are used on fluid tiles. So wrote this small routine to view the images online as well as create a file with the same information which can be shared with the team.

For this POC running PT 8.58.x, HCM 9.2

Created a SQL object N_IMAGES_SQL, selecting image name and version from the PSCONTDEFN table. Selecting only SVG files as those are the ones generally used on fluid tiles. 

Created a page and added a long field of the type HTML Area to the page. 

N_TEST_WRK.HTMLAREA in the code below is that field.

So displaying the images returned by the SQL on the online page along with the image name. When this happens the images are also written to the web server cache folder. So created another string with path to the physical files in the web server cache folder and created an html file. Via a browser opened this html file and saved it as a pdf which can now be shared with the team.   

Complete peoplecode below

Output

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":[

         ]

      }

   }

}

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.

Drop Zones

Configurable drop zones allow us to add fields to delivered fluid pages without customizing the component or fluid page definition. Our custom fields are displayed and processed along with fields from the main fluid page definition and any nested fluid subpages.

Following is what I tried in PeopleTools 8.56.07, HCM database on PUM 31.

The goal was to display Job Family information on the ESS additional info page.

1. Created a new fluid page based on the PSL_APPS_CONTENT template

2. The fluid page type is set as sub-page

3. Inserted a group-box, and on the group box properties, fluid tab, set group box type as "Container" and added style class  psc_padding-0em. Took default values for rest of the properties of the group box.

4. In this group box added two fields, first one is job family which is display control field and invisible and the second one is a related display field as shown below

That is it as far as development in application designer is concerned.

5. Via PIA navigate to PeopleTools > Portal > Configure Drop Zones

6. Pull up delivered ESS Additional Information page and select which drop zone to be used. So in my example the job family information will be displayed after highest education level info.

7. Save the configuration and verify the results by navigating to the Fluid ESS page. 

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.

Query Transformations

I rarely do much work in PS Query, but while doing some other troubleshooting for PS Query related issues, I noticed the "Transformations" tab in Query Manager. I am running PT 8.56.x in HCM 9.2, but looks like this has been around since 8.53 or 8.54. 

So decided to do a quick test to see what this does and how it works. So the benefit here is that the output can be transformed to create reports right out off the output delivered by PS Query.

So as part of this POC used any existing query. Transforming the output to HTML and using a XSL transformation to highlight the row when a certain condition is matched.

On the Transformation tab within Query Manager, click on Add XSLT which opens a window to key in free-form XSLT text. It would have been nice if there is a wizard of some sort to build the XSLT. 

Gave it a Name and in the "Output File Type" lookup selected HTML. Following are the available choices - 001, A01, ABA, AET, APC, CSS, CSV, D01, DAT, DTD, DTL, ERR, GIF, GIR, HTM, HTML, IDX, JCD, JS, LIS, LOG, MDL, MT9, N01, NVT, NXT, OUT, PS, SQL, STDOUT, SYS, TMP, TRC, TRN, TXT, XLSM, XLSX, XML, XSD, ZIP

Not sure what is the difference between HTM and HTML, but I selected HTML. My sample XSLT is designed as follows.

<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match="/"> <html>
<!-- defined a style for the row that is going to be highlighted -->
<style type="text/css">
tr.redRow td {
font-size: 100%;
font-family: calibri;
font-weight:bold;
background-color: #FF2626;
padding-right: 10px;
color:#000;
}
</style>
<body>
<!-- in the html body defined a html table --> <table border="1">
<!-- Added a heading --> <font color="blue"> <h2>Sample Transformation test</h2> </font>
<!-- defined column headers --> <tr> <th>Employee ID</th> <th>Last Name</th> <th>First Name</th>
<th>Location</th> </tr>
<!-- Here I am selecting the data from PS Query --> <xsl:for-each select="query/row"> <tr>
<!-- defined a if condition to check if -->
<!-- value is McLean then highlight the row --> <xsl:if test="L.DESCR='McLean'"> <xsl:attribute name="class">redRow</xsl:attribute> </xsl:if>
<!-- Output the PS Query results --> <td> <xsl:value-of select="A.EMPLID" /> </td> <td> <xsl:value-of select="C.LAST_NAME" /> </td> <td> <xsl:value-of select="C.FIRST_NAME" /> </td> <td> <xsl:value-of select="L.DESCR" /> </td> </tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>

So saved this and hit the preview button to preview the results. My query uses runtime prompts so those pop'ed up first, provided values for them and results are displayed in a new window (disable pop-up blocker or allow pop-up for this site).

Sample Transformation test

Employee ID	Last Name	First Name	Location
12345	Doe	John	Madrid
13244	Doe	Jane	Egypt
43423	Hufton	Abcde	London
42343	Fleming	Jill	McLean
34423	Tibold	Jozsef	London
67554	Farrell	William	Johannesburg
43556	Myers	Tony	Ireland
75757	Kramer	Nicole	McLean
27341	Wada	Mark	New York
27045	Roger	Wai	McLean
27098	Wayne	Jessica	New York
27125	Garcia	Guillermo	New York
75688	Deepak	Shirguppi	Maryland

If this query is scheduled using Query Scheduler option then a new format of XFORM is available and then I can pick any transformations (can define n number of transformations) that I have defined on the query.