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

         ]

      }

   }

}

PeopleSoft BI Publisher Pivot Wizard

The following explains how the crosstab wizard builds the crosstab and what the fields inside the resulting template structure are there for.

To create the crosstab a new XDO command "<?crosstab:...?>" has been created.
XDO Command: <?crosstab: ctvarname; data-element; rows; columns; measures; aggregation?>

Ctvarname - Crosstab variable name. This is automatically generated by the Add-in. e.g. c72515

data-element - This is the XML data element that contains the data. e.g. "//Payroll"

Rows - This contains a list of XML elements for row headers. The ordering information is specified within "{" and "}". The first attribute is the sort element. Leaving it blank means the sort element is the same as the row header element. The attribute "o" means order. Its value can be "a" for ascending, or "d" for descending. The attribute "t" means type. Its value can be "t" for text, and "n" for numeric.

There can be more than one sort elements, example: "emp-full-name {emp-lastname,o=a,t=n}{emp-firstname,o=a,t=n}. This will sort employee by last name and first name.
e.g. "EMPLID{,o=a,t=t}, NAME{,o=a,t=t}"

In the example, the first row header is "EMPLID". It is sort by "EMPLID", order is ascending, and type is text. The second row header is "NAME". It is sort by "NAME", order is ascending, and type is text.

Columns - This contains a list of XML elements for columns headers. The ordering information is specified within "{" and "}". The first attribute is the sort element. Leaving it blank means the sort element is the same as the column header element. The attribute "o" means order. Its value can be "a" for ascending, or "d" for descending. The attribute "t" means type. Its value can be "t" for text, and "n" for numeric.

There can be more than one sort elements, example: "emp-full-name {emp-lastname,o=a,t=n}{emp-firstname,o=a,t=n}. This will sort employee by last name and first name.
e.g. "CHECK_DT{,o=a,t=t}"

In the example, the first column header is "CHECK_DT". It is sort by "CHECK_DT", order is ascending, and type is text.

Measures - This contains a list of XML elements for measures. e.g. "ANNUAL_RT"

Aggregation - The aggregation function name. Currently, we only support "sum" and “count”. e.g. "sum"

Using the Oracle BI Publisher Template Builder for Word add-in, we are able to construct the following Pivot Table:

Row layout can be Inline or Outline.

Row/Column totals

Table defined in RTF template

C H ACCEMPLID	NAME	G CHECK_DTE	Total
G RS EMPLID	G NAME	G 999E	999E
		G 999E	999

Code generated by the Pivot Wizard

XML file

……

……

……

Output

PeopleSoft Process Security (setup/configuration)

In order to provide a user access to run processes in PeopleSoft provide a value for primary permission list as well as process profile permission list on the general tab on the user profile page.

Following is from Peoplebooks.

PrimaryPermissionList

PeopleSoft determines which data permissions to grant a user by examining the primary permission list and row security permission list. Which one is used varies by application and data entity (employee, customer, vendor, business unit, and so on). Consult your PeopleSoft application documentation for more details. PeopleSoft also determines mass change and definition security permissions from the primary permission list.

ProcessProfilePermissionList 

The process profile contains the permissions that a user requires for running batch processes through PeopleSoft Process Scheduler. For example, the process profile authorizes users to view output, update run locations, restart processes, and so on. Only the process profile comes from this permission list, not the list of process groups.

PeopleSoft Recurrences

Recurrence definitions enable you to make sure that important process requests and jobs that you must run on a regular basis always run automatically in the background. This eliminates the possibility of anyone forgetting to submit a process request or submitting one incorrectly. When you specify a recurrence definition, the process request continues the cycle until you stop it manually.

Here are some different ones that I have used.

Run on 15th of every month

Every other week on a Monday

Twice a week (Sun/Wed)

XSL Template in BI Publisher (PeopleSoft)

Here is a quick tip on how to create and use XSL template via BI Publisher in PeopleSoft.
For this test I am using PeopleTools 8.52.x but I am sure this is even possible in older releases like 8.49.x and above.

When you create a new Report defn, pick XSL as the template type.

1. Using XRFWIN as the data source create a new report defn

2. Pick XSL as the template type

3. For the XSL template, either you can manually create a file manually or some other web based app or use the following steps to generate one for you.

Take the delivered xrfwin.rtf RTF template.

Create a dir, lets say c:\temp\xmlp and copy it here. Copy the jlif dir from your local install of the BI Publisher Desktop. (C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib)

So you will have c:\temp\xmlp\jlib

Now via cmd line from under c:\temp\xmlp execute the following.

javaw -Duser.language=en -Duser.region=US -Xmx256M -jar ".\jlib\RTF2PDF.jar" "xrfwin.rtf" "dummy" "." "XSL" "TRUE" "en-US" "dummy" "dummy" "." ""

This will create two sub-dirs log and tmp. The xml file under tmp will be you xsl template. Upload this to report defn and verify.

XMLP Output Options

Following are the different ways to display a XMLP report, via peoplecode.

/* initialize */
&reportdefn = create PSXP_RTPDEFNMANAGER:ReportDefn(&report);
&template = &reportdefn.GetDefaultTemplateID();

/* this will create the PDF report output under c:\temp\ */
/* useful when generating report via an AE program */
&reportdefn.OutDestination = "c:\temp\";
&reportdefn.ProcessReport(&template,%Language_User,%Date,"PDF");
&reportdefn.Publish("","","",N_RECORD_AET.PROCESS_INSTANCE);

/* without setting &reportdefn.OutDestination as mentioned above the PDF report will be written to the report respository and available via the Report Manager */
&reportdefn.ProcessReport(&template,%Language_User,%Date,"PDF");
&reportdefn.Publish("","","",N_RECORD_AET.PROCESS_INSTANCE);

/* this will open the output in a new browser window. In this case code is executed on the appserver as against process scheduler in the above two scenarios */
&reportdefn.ProcessReport(&template,%Language_User,%Date,"PDF");
CommitWork();
&reportdefn.DisplayOutput();

Based on the above scenarios here is cool trick to show the report under process monitor (view/log trace) instead of the report manager.
SQLExec("SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = :1",N_RECORD_AET.PROCESS_INSTANCE, &dir);
&reportdefn.OutDestination = &dir | "\";
&reportdefn.ProcessReport(&template,%Language_User,%Date,"PDF");
&reportdefn.Publish("","","",N_RECORD_AET.PROCESS_INSTANCE);