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 web service response namespace issue

Running PT 8.53.x, wrote a simple custom web service "Hello World". Service works fine but saw one issue where the namespace returned by the response message is not defined in the WSDL. So any other 3rd party solution like a .Net program when they consume and execute this service it fails to receive the response due to the unidentified namespace.

Request 

<soapenv:Envelope xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://schemas.xmlsoap.org/ws/2003/03/addressing/" xmlns:xsd="http://www.w3.org/2001/XMLSchema/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance/">
  <soapenv:Header xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  </soapenv:Header>
  <soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <HelloWorld xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/HELLOWORLD.V1"></HelloWorld>
  </soapenv:Body>
</soapenv:Envelope>

Response via PeopleSoft SOAP message template

<soapenv:Envelope xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://schemas.xmlsoap.org/ws/2003/03/addressing/" xmlns:xsd="http://www.w3.org/2001/XMLSchema/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance/">
  <soapenv:Header xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  </soapenv:Header>
  <soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <HelloWorldResponse xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/HELLOWORLDRESPONSE.V1">
      <HelloWorldResult>XYZ</HelloWorldResult>
    </HelloWorldResponse>
  </soapenv:Body>
</soapenv:Envelope>

Actual response via SOAPUI or Postman

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <HelloWorldResponse xmlns="http://peoplesoft.com/HelloWorldResponseResponse">
         <HelloWorldResult>Success</HelloWorldResult>
      </HelloWorldResponse>
   </soapenv:Body>
</soapenv:Envelope>


As you see the the problem is the namespace value returned for the root tag "HelloWorldResponse".

<HelloWorldResponse xmlns="http://peoplesoft.com/HelloWorldResponseResponse">


PeopleSoft puts the default value (not sure where this is stored)
http://peoplesoft.com/HelloWorldResponseResponse
instead of what is defined in the WSDL which is 
http://xmlns.oracle.com/Enterprise/Tools/schemas/HELLOWORLDRESPONSE.V1

I have a very basic synchronous service operation, with a request and response message and the handler OnRequest peoplecode is doing all the work.

Following was my application package peoplecode initially.

&response = CreateMessage(Operation.HELLOWORLD_SVCOP, %IntBroker_Response);
&xmldata = "<?xml version='1.0'?><HelloWorldResponse/>";
&xmlresponsedoc = CreateXmlDoc(&xmldata);

&HWResponseNode = &xmlNode.AddElement("HelloWorldResult");
&HWResponseNode.NodeValue = "Success";
&response.SetXmlDoc(&xmlresponsedoc);
Return &response;


So when creating the XmlDoc I was not specifying any namespace and I was hoping PeopleSoft will add it based on the schema definition. 

Instead following is what I had to do to correct the issue. (only displaying the change, no change to rest of the code)

&xmlresponsedoc = CreateXmlDoc("");

&docTypeNode = &xmlresponsedoc.CreateDocumentType("", "", "");
&rootNode = &xmlresponsedoc.CreateDocumentElement("HelloWorldResponse", "http://xmlns.oracle.com/Enterprise/Tools/schemas/HELLOWORLDRESSPONSE.V1", &docTypeNode);

&xmlNode = &xmlresponsedoc.DocumentElement;

So I have to use the CreateDocumentElement method of CreateXmlDoc in order to define the namespace. In order to use this method I had to first initialize the &docTypeNode variable by using the CreateDocumentType method.

After making the above change the response comes back correctly.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <HelloWorldResponse xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/HELLOWORLDRESSPONSE.V1">
         <HelloWorldResult>Success</HelloWorldResult>
      </HelloWorldResponse>
   </soapenv:Body>
</soapenv:Envelope>

JMS Connectors and Queues

Goal of this exercise is to design a web service solution wherein we are hosting the service and it’s consumed by a SaaS solution. In this POC I am exposing/hosting a simple SOAP based XML web service. 

I am testing this out on PeopleSoft HCM 9.1 running PT 8.53.26.

Transaction data flow is as follows:

• Expose web service which takes in the request message
• Request XML message is written to the request queue on the JMS server
• A listener process reads the request queue and fetches the message
• A process/program processes the request message and populates the result or response XML message on the response queue.
• The web service reads the response queue and sends the response back to the caller

JMS Correlation ID is used as a key to fetch/write the appropriate message from/to the queue.

JMS Server and Queue Setup

Following are the steps to setup a JMS Server, Connection Factory and three queues, Request queue, Response queue and Error queue via the weblogic admin console. I am running Weblogic version 10.3.6.0.13.

Open the Weblogic Console and navigate to Services > Messaging > JMS Modules, Click Lock&Edit, then New, then enter a name for the JMS Module and click "Next”:

Select PIA as Target and click "Next": 
Uncheck "Would you like to add resources to this JMS system module?" and click "Finish"

Navigate to Services > Messaging > JMS Servers and Click "new", enter the name of the JMS Server and click "Next":

Navigate to Services > Messaging > JMS Modules and select the JMS Module created in previous steps.

Now create a queue as below. 

Similarly create two more queues, the ResponseQueue and ErrorQ

PeopleSoft setup

Nodes:
Create two nodes, of type external, one for listening on the request queue and another one to write back to the response queue. Setup is pretty much identical except for the queue name. Use the JMSTARGET connector ID for both the nodes and key in properties as follows:
JMSFactory = MyConnectionFactory (this is the JNDI name defined via the weblogic console)
JMSMessageType = Text
JMSProvider = Weblogic
JMSQueue = RequestQueue for one node and ResponseQueue for the other node
JMSUrl = Weblogic JMS Server in the form t3://server:port

Messages:

Create two non-rowset based messages, again one for request and the other for response.
Added a schema to each message (this is optional, you can manage it via peoplecode too). Request message for this POC has the following structure:

<?xml version='1.0'?>
<person>
    <emplid>string</emplid>
</person>

and response message would be as follows:

<?xml version='1.0'?>
<person>
    <name>string</name>

</person>

Service Operation:

Created a service and assigned two service operations to it. Assigned security to both the service operations. The listener service operation has handler application package onNotify peoplecode defined, nothing for the response service operation. 
So in case of the listening service operation the routing would be:
Sender Node = newly created JMS listening node (above)
Receiver Node = local default node 

In case of response service operation the routing would be:
Sender Node = local default node
Receiver Node =  newly created JMS response node (above)

Handler Peoplecode:

method onNotify

   /+ &MSG as Message +/

   /+ Extends/implements PS_PT:Integration:INotificationHandler.OnNotify +/

  

   Local XmlDoc &xmldoc = &MSG.GetXmlDoc();

   Local XmlNode &xmlNode = &xmldoc.DocumentElement;

   Local string &emplid = %This.FetchDataValue(&xmldoc, 1, "emplid");

   Local string &name;

  

   SQLExec("select ISNULL((SELECT NAME FROM PS_PERSONAL_DATA WHERE EMPLID = :1),'No Name')", &emplid, &name);

  

/* create the reponse message */

Local Message &ResponseMSG = CreateMessage(Operation.JMS_RESP_SO, %IntBroker_Request);

   Local string &xmldata = "<?xml version='1.0'?><person/>";

   Local XmlDoc &ResponseDOC = CreateXmlDoc(&xmldata);

   &xmlNode = &ResponseDOC.DocumentElement;

  

   Local XmlNode &NameNode = &xmlNode.AddElement("name");

   &NameNode.NodeValue = &name;

  

/* create the reponse message */

   &ResponseMSG.SetXmlDoc(&ResponseDOC);

   %IntBroker.Publish(&ResponseMSG);

  

end-method;

method FetchDataValue

   /+ &xmldoc as XmlDoc, +/

   /+ &row as Number, +/

   /+ &dataTag as String +/

   /+ Returns String +/

  

   Local string &dataValue;

   Local array of XmlNode &dataList;

  

   &dataList = &xmldoc.DocumentElement.FindNodes(&dataTag);

   If &dataList.Len = 0 Then

      &dataValue = "";

   Else

      &dataValue = &dataList [&row].NodeValue;

   End-If;

   Return &dataValue;

end-method;

IB changes:

Integrationgateway.properties file can be edited via the gateway setup properties link from the gateway component. Updated that as follows for the local gateway.

Showing only the pieces that I changed updated, under the JMS configuration section. need to only define the listening queue here. Error queue is optional but good to have. 

ig.jms.Queues=1

ig.jms.Queue1=RequestQueue
ig.jms.Queue1.Provider=Weblogic
ig.jms.Queue1.JMSFactory=MyConnectionFactory
ig.jms.Queue1.Url=t3://server:port
# following service operation details are required only for a listening operation.  
ig.jms.Queue1.OperationName=JMS_REQ_SO.v1
ig.jms.Queue1.OperationVersion=v1
ig.jms.Queue1.RequestingNode=JMS_LISTENER_NODE
ig.jms.Queue1.DestinationNode=PSFT_HR

ig.jms.ErrorQueue=ErrorQueue

ig.jms.ErrorQueue-Provider=Weblogic
ig.jms.ErrorQueue-JMSFactory=MyConnectionFactory
ig.jms.ErrorQueue-Url=t3://server:port 

Testing:

Create a message via the weblogic console in MyQueue1 or the RequestQueue and verify via integration broker monitor that the message is picked up processed and written to MyQueue2 or the ResponseQueue.

Any issues or errors will be written to errorLog.html and/or msgLog.html files on the webserver.