SQL Tip: Find PS Query set on recurrence
When you schedule a PeopleSoft query, you can see the scheduled process is of type PSQUERY but process scheduler doesn’t show the actual query name that is set on recurrence.
In case you want to find out the queries that are set to run on recurrence, here is a SQL that you can run.
This SQL will provide the following information:
- Process Instance
- Query Name
- Query Description
- Recurrence Name
- User Id who submitted the recurrence
- Run control ID used to schedule the query
SELECT A.PRCSINSTANCE, B.QRYNAME, B.DESCR, A.PRCSNAME, A.RECURNAME, A.OPRID, C.OPRDEFNDESC, A.RUNCNTLID, A.RQSTDTTM FROM PSPRCSRQST A, PS_QUERY_RUN_CNTRL B, PSOPRDEFN C WHERE A.PRCSNAME = 'PSQUERY' AND A.RUNCNTLID = B.RUN_CNTL_ID AND A.OPRID = C.OPRID AND A.RUNSTATUS = '5' ORDER BY OPRID DESC;
Possible modifications to above SQL
Find the recurrence for other processes
Change the A.PRCSNAME to appropriate process name
Find the recurrence for particular process type
Add the A.PRCSTPE and comment out or change A.PRCSNAME.
Here is the list of different Process Types
Demand Planning Upload
Essbase Cube Builder
SQR PO-Special Process
SQR Report For WF Delivery