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