Running a PeopleSoft Query using the Reporting Web Services

By Chris Malek | Tue, Jul 14, 2015

PeopleTools delivers some functionality called the “Reporting Web Services” which allows a third party to execute a PeopleSoft Query over a web service. There are several different execution models documented in the Reporting Web Services PeopleBooks. However, we are going to cover only the synchronous method which the query results will be run in real-time and an XML response will be returned while the client waits on the line. This is the most simple approach and requires the least amount of code for the third party.

This can be an easy way for external systems to pull data out of PeopleSoft. You don’t have to create a new web service for each new data extract. You would just create a new public or private query and ensure that the “default Node user” on the node sending the messages has the proper query security to execute the query. The third party just needs to send in a different query name as a parameter to get the different data.

I had previously written an article called Running a PeopleSoft Query with Ruby over HTTP which used HTTP but it did not use these query web services. That method actually logged into PeopleSoft using a login form, then submitted a query request using the same URL that a web browser would use. So it was a bit of hack but it worked. The method documented in this article is a bit more robust.

Required Setup: Node and Service Operation

First let’s cover some setup prerequisites.

We are going to assume that you have setup a node in PeopleSoft that represents the client system.

Node Type: External
Authentication Option: Password
Node Password: Something long and random. (For this article we used “$ecretknock1234”)
Default User: Some valid user
- That user needs query security access to all queries you want to run.
- The user also needs web service security to the QAS_EXECUTEQRYSYNC_OPER service operation.

In the environment I was working with, SSL was terminated before it got to the PSIGW Web Logic servers. So I had to disable the security on the QAS_EXECUTEQRYSYNC_OPER service operation. You can take a look at these two Oracle Support Documents: Doc ID 1946201.1, and Doc ID 1965296.1 if your WebLogic servers do not handle SSL.

I also setup a Service Operation routing on QAS_EXECUTEQRYSYNC_OPER inbound from my node.

If you want to see an example of how to setup a service operation from scratch and use a python library check out my article Synchronous HTTP Post to PeopleSoft Integration Broker using Python

Running a Query - No Query Prompts & WebRowset Format

For the sake of this article we are going to write a query that returns information on the last few days of subscription contracts in the integration broker. This is just an example query. The query that you would use could be anything you can do in Query Manager.

I created a public query called GET_LAST_2_DAYS_SUB_CONTRACTS. The SQL is:

SELECT A.IBTRANSACTIONID, A.IB_SEGMENTINDEX, A.ACTIONNAME, 
A.PUBNODE, TO_CHAR(CAST((A.CREATEDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 
A.SUBCONSTATUS, A.STATUSSTRING 
    FROM PSAPMSGSUBCON A 
    WHERE ( A.CREATEDTTM > ((CAST(SYSTIMESTAMP AS TIMESTAMP)) + ( -2)) )

This query is hard coded with “-2” to get the last 2 days of subscription contracts.

There are really two different formats to get the data back: XMLP & webrowset. There are some other options but you don’t get those back in a synchronous called. See Peoplebooks for more. In this first example, we are going to call the web service using the webrowset result type which I prefer.

Now lets look at the web service HTTP call. Note, we are using a technology neutral format.

POST /PSIGW/HttpListeningConnector HTTP/1.1
Host: ibdemo.cedarhillsgroup.com
Content-Type: application/xml
To: DEMO
OperationName: QAS_EXECUTEQRYSYNC_OPER.VERSION_1
From: CHG_TEST
password: $ecretknock1234

<?xml version="1.0"?>
<QAS_EXEQRY_SYNC_REQ_MSG>
   <QAS_EXEQRY_SYNC_REQ>
      <QueryName>GET_LAST_2_DAYS_SUB_CONTRACTS</QueryName>
      <isConnectedQuery>N</isConnectedQuery>
      <OwnerType>PUBLIC</OwnerType>
      <BlockSizeKB>0</BlockSizeKB>
      <MaxRow>99999</MaxRow>
      <OutResultType>webrowset</OutResultType>
      <OutResultFormat>NONFILE</OutResultFormat>
   </QAS_EXEQRY_SYNC_REQ>
</QAS_EXEQRY_SYNC_REQ_MSG>

Below is the response received.

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
 <webRowSet xmlns="http://java.sun.com/xml/ns/jdbc">
  <properties>
   <escape-processing>true</escape-processing>
   <fetch-direction>1000</fetch-direction>
   <fetch-size>0</fetch-size>
   <isolation-level>1</isolation-level>
   <key-columns/>
   <map/>
   <max-field-size>0</max-field-size>
   <max-rows>0</max-rows>
   <query-timeout>0</query-timeout>
   <read-only>true</read-only>
   <show-deleted>false</show-deleted>
   <table-name/>
  </properties>
  <metadata>
   <column-count>7</column-count>
   <column-definition>
    <column-index>1</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>false</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>false</signed>
    <searchable>false</searchable>
    <column-display-size>0</column-display-size>
    <column-label>Transaction ID</column-label>
    <column-name>A.IBTRANSACTIONID</column-name>
    <schema-name/>
    <column-precision>0</column-precision>
    <column-scale>0</column-scale>
    <table-name/>
    <catalog-name/>
    <column-type>12</column-type>
    <column-type-name>VARCHAR</column-type-name>
   </column-definition>
   <column-definition>
    <column-index>2</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>false</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>false</signed>
    <searchable>false</searchable>
    <column-display-size>0</column-display-size>
    <column-label>Segment Index</column-label>
    <column-name>A.IB_SEGMENTINDEX</column-name>
    <schema-name/>
    <column-precision>0</column-precision>
    <column-scale>0</column-scale>
    <table-name/>
    <catalog-name/>
    <column-type>5</column-type>
    <column-type-name>SMALL INT</column-type-name>
   </column-definition>
   <column-definition>
    <column-index>3</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>false</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>false</signed>
    <searchable>false</searchable>
    <column-display-size>0</column-display-size>
    <column-label>ACTIONNAME</column-label>
    <column-name>A.ACTIONNAME</column-name>
    <schema-name/>
    <column-precision>0</column-precision>
    <column-scale>0</column-scale>
    <table-name/>
    <catalog-name/>
    <column-type>12</column-type>
    <column-type-name>VARCHAR</column-type-name>
   </column-definition>
   <column-definition>
    <column-index>4</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>false</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>false</signed>
    <searchable>false</searchable>
    <column-display-size>0</column-display-size>
    <column-label>Pub Node</column-label>
    <column-name>A.PUBNODE</column-name>
    <schema-name/>
    <column-precision>0</column-precision>
    <column-scale>0</column-scale>
    <table-name/>
    <catalog-name/>
    <column-type>12</column-type>
    <column-type-name>VARCHAR</column-type-name>
   </column-definition>
   <column-definition>
    <column-index>5</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>false</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>false</signed>
    <searchable>false</searchable>
    <column-display-size>0</column-display-size>
    <column-label>Creation Dttm</column-label>
    <column-name>A.CREATEDTTM</column-name>
    <schema-name/>
    <column-precision>0</column-precision>
    <column-scale>0</column-scale>
    <table-name/>
    <catalog-name/>
    <column-type>93</column-type>
    <column-type-name>TIMESTAMP</column-type-name>
   </column-definition>
   <column-definition>
    <column-index>6</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>false</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>false</signed>
    <searchable>false</searchable>
    <column-display-size>0</column-display-size>
    <column-label>Sub Con Status</column-label>
    <column-name>A.SUBCONSTATUS</column-name>
    <schema-name/>
    <column-precision>0</column-precision>
    <column-scale>0</column-scale>
    <table-name/>
    <catalog-name/>
    <column-type>5</column-type>
    <column-type-name>SMALL INT</column-type-name>
   </column-definition>
   <column-definition>
    <column-index>7</column-index>
    <auto-increment>false</auto-increment>
    <case-sensitive>false</case-sensitive>
    <currency>false</currency>
    <nullable>0</nullable>
    <signed>false</signed>
    <searchable>false</searchable>
    <column-display-size>0</column-display-size>
    <column-label>Status String</column-label>
    <column-name>A.STATUSSTRING</column-name>
    <schema-name/>
    <column-precision>0</column-precision>
    <column-scale>0</column-scale>
    <table-name/>
    <catalog-name/>
    <column-type>12</column-type>
    <column-type-name>VARCHAR</column-type-name>
   </column-definition>
  </metadata>
  <data>
   <currentRow>
    <columnValue><![CDATA[d43fcf18-18b4-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T01:01:03-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[e6b9766c-18b4-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T01:01:34-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[e6c4e6aa-18b4-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T01:01:34-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[e6cdcc84-18b4-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T01:01:35-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[e6d9b166-18b4-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T01:01:35-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[dee7b3d2-1917-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T12:50:01-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[0fd1accc-1919-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T12:58:33-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[60c7997a-1923-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T14:12:24-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[9c1fb44a-1927-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[USER]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T14:42:41-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[9c1fc368-1927-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[userAudit]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T14:42:41-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[2337170c-192d-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-22T15:22:15-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[03ae4602-197e-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-23T01:01:12-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[1624eb6a-197e-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-23T01:01:43-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[163426ca-197e-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-23T01:01:43-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[163ea640-197e-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-23T01:01:43-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
   <currentRow>
    <columnValue><![CDATA[1fb83506-197e-11e5-a95b-c37db93cd509]]></columnValue>
    <columnValue>0</columnValue>
    <columnValue><![CDATA[ReportCreate]]></columnValue>
    <columnValue><![CDATA[DEMO]]></columnValue>
    <columnValue>2015-06-23T01:01:59-0700</columnValue>
    <columnValue>4</columnValue>
    <columnValue><![CDATA[DONE]]></columnValue>
   </currentRow>
  </data>
 </webRowSet>
</QAS_GETQUERYRESULTS_RESP_MSG>

If we look closely at the webrowset XML response it has some meta-data about the results including the number of columns, information about each column returned like the label and data format. Then there is the actual data. I think this is probably the most useful format for a third party to call various queries because there is robust information about the data returned. Thus code could be written to dynamically handle changes to the query structure or running different queries.

Running a Query - No Query Prompts & XMLP Format

Now lets run the same query but request a different output format for comparison. We will run this example using the XMLP format.The only difference between this request and the one above is the OutResultType parameter.

POST /PSIGW/HttpListeningConnector HTTP/1.1
Host: ibdemo.cedarhillsgroup.com
Content-Type: application/xml
To: DEMO
OperationName: QAS_EXECUTEQRYSYNC_OPER.VERSION_1
From: CHG_TEST
password: $ecretknock1234
Cache-Control: no-cache

<?xml version="1.0"?>
<QAS_EXEQRY_SYNC_REQ_MSG>
   <QAS_EXEQRY_SYNC_REQ>
      <QueryName>GET_LAST_2_DAYS_SUB_CONTRACTS</QueryName>
      <isConnectedQuery>N</isConnectedQuery>
      <OwnerType>PUBLIC</OwnerType>
      <BlockSizeKB>0</BlockSizeKB>
      <MaxRow>99999</MaxRow>
      <OutResultType>xmlp</OutResultType>
      <OutResultFormat>NONFILE</OutResultFormat>
   </QAS_EXEQRY_SYNC_REQ>
</QAS_EXEQRY_SYNC_REQ_MSG>

And here is the response from the server.

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
 <query numrows="16" queryname="GET_LAST_2_DAYS_SUB_CONTRACTS" xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_QUERYRESULTS_XMLP_RESP.VERSION_1">
  <row rownumber="1">
   <IBTRANSACTIONID><![CDATA[d43fcf18-18b4-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T01:01:03-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="2">
   <IBTRANSACTIONID><![CDATA[e6b9766c-18b4-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T01:01:34-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="3">
   <IBTRANSACTIONID><![CDATA[e6c4e6aa-18b4-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T01:01:34-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="4">
   <IBTRANSACTIONID><![CDATA[e6cdcc84-18b4-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T01:01:35-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="5">
   <IBTRANSACTIONID><![CDATA[e6d9b166-18b4-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T01:01:35-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="6">
   <IBTRANSACTIONID><![CDATA[dee7b3d2-1917-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T12:50:01-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="7">
   <IBTRANSACTIONID><![CDATA[0fd1accc-1919-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T12:58:33-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="8">
   <IBTRANSACTIONID><![CDATA[60c7997a-1923-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T14:12:24-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="9">
   <IBTRANSACTIONID><![CDATA[9c1fb44a-1927-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[NU_USER]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T14:42:41-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="10">
   <IBTRANSACTIONID><![CDATA[9c1fc368-1927-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[userAudit]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T14:42:41-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="11">
   <IBTRANSACTIONID><![CDATA[2337170c-192d-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-22T15:22:15-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="12">
   <IBTRANSACTIONID><![CDATA[03ae4602-197e-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-23T01:01:12-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="13">
   <IBTRANSACTIONID><![CDATA[1624eb6a-197e-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-23T01:01:43-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="14">
   <IBTRANSACTIONID><![CDATA[163426ca-197e-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-23T01:01:43-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="15">
   <IBTRANSACTIONID><![CDATA[163ea640-197e-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-23T01:01:43-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
  <row rownumber="16">
   <IBTRANSACTIONID><![CDATA[1fb83506-197e-11e5-a95b-c37db93cd509]]></IBTRANSACTIONID>
   <IB_SEGMENTINDEX>0</IB_SEGMENTINDEX>
   <ACTIONNAME><![CDATA[ReportCreate]]></ACTIONNAME>
   <PUBNODE><![CDATA[DEMO]]></PUBNODE>
   <CREATEDTTM>2015-06-23T01:01:59-0700</CREATEDTTM>
   <SUBCONSTATUS>4</SUBCONSTATUS>
   <STATUSSTRING><![CDATA[DONE]]></STATUSSTRING>
  </row>
 </query>
</QAS_GETQUERYRESULTS_RESP_MSG>

If you look closely at this XMLP response format it is less friendly to the third party because there is less meta-data about the response data. Additionally, the raw PeopleSoft field names come through and you loose any labels you have have set on the query definition which are preserved in the webrowset format.

The rest of this article will use the webrowset format as that is my preference.

Running a Query with 1 Query Prompt

Now let’s look at what happens if we want to pass a query prompt instead of having that hard coded “-2”. Maybe you want to go back 4 days, 5 days, or 1 day.

I wrote a different query called GET_LAST_X_DAYS_SUB_CONTRACTS. We just add a prompt to the where clause. The prompt name in PSQUERY is called BIND1. Here is the SQL generated by query manager.

SELECT A.IBTRANSACTIONID, A.IB_SEGMENTINDEX, A.ACTIONNAME, A.PUBNODE, TO_CHAR(CAST((A.CREATEDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.SUBCONSTATUS, A.STATUSSTRING 
    FROM PSAPMSGSUBCON A 
    WHERE ( A.CREATEDTTM > ((CAST(SYSTIMESTAMP AS TIMESTAMP)) + ( :1)) )

Now lets invoke the web service passing in some additional parameters in the payload. Take notice of the prompts XML parent node where we pass in “BIND1” value of “-1” which equates to 1 day back in this query.

POST /PSIGW/HttpListeningConnector HTTP/1.1
Host: ibdemo.cedarhillsgroup.com
Content-Type: application/xml
To: DEMO
OperationName: QAS_EXECUTEQRYSYNC_OPER.VERSION_1
From: CHG_TEST
password: $ecretknock1234
Cache-Control: no-cache

<?xml version="1.0"?>
<QAS_EXEQRY_SYNC_REQ_MSG>
   <QAS_EXEQRY_SYNC_REQ>
      <QueryName>GET_LAST_X_DAYS_SUB_CONTRACTS</QueryName>
      <isConnectedQuery>N</isConnectedQuery>
      <OwnerType>PUBLIC</OwnerType>
      <BlockSizeKB>0</BlockSizeKB>
      <MaxRow>99999</MaxRow>
      <OutResultType>webrowset</OutResultType>
      <OutResultFormat>NONFILE</OutResultFormat>
        <Prompts>
           <PROMPT>
              <UniquePromptName>BIND1</UniquePromptName>
              <FieldValue>-1</FieldValue>
           </PROMPT>
        </Prompts>
      
   </QAS_EXEQRY_SYNC_REQ>
</QAS_EXEQRY_SYNC_REQ_MSG>

The response to this would be identical as the first example in this article only with less data because we went back 1 day instead of 2.

Some Use Cases and Additional Notes

There are several use cases that come to mind where you may want to use this.

When you need a third party to pull data from PeopleSoft and you don’t need complex logic or data translation.
Integration scenarios where a third party needs a “valid list of values” for certain field like departments or any setup table.
To build dashboard applications from PeopleSoft data.
For quick integrations that may not have a long lifespan and don’t need to be stable for years.

The nice thing about this is that anyone with query expertise could manage the integration with the third party. For example, maybe you are returning a list of employees in certain departments to a client application. Those list of departments may change periodically. It would be easy to just have a functional analyst tweak the query instead of getting a developer involved.

There are also a few pitfalls with these web services that you may want to look out for.

Since any query can be run, you run the risk of a third party relying on a certain query result set to run. Then a user changes the query definition and breaks the integration.
Large result sets could be problematic. There are some other usage patterns to these web services where you can run large queries asynchronously. See PeopleBooks for more information.

There are some REST versions of these web service as well which we did not document.

Additional Reading

Reporting Web Services: Using the REST Web Services to run a Query

Article Categories

Web Services

Author Info

Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris

Looking for pain-free PeopleSoft web services? 😀

PeopleSoft Simple Web Services (SWS)

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Integration Broker - The Missing Manual

I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.

Read Online