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

 A.PRCSNAME='AEMINITEST' 

Find the recurrence for particular process type

Add the A.PRCSTPE and comment out or change A.PRCSNAME.

 A.PRCSTYPE='Application Engine' 

Here is the list of different Process Types

Application Engine
COBOL SQL
Crystal
Cube Builder
Data Mover
Database Agent
Demand Planning Upload
Essbase
Essbase Cube Builder
HyperCube Builder
Optimization Engine
SQR PO-Special Process
SQR Process
SQR Report
SQR Report For WF Delivery
Shell Script
Winword
XML Publisher
nVision
nVision-Report
nVision-ReportBook

Prashant
 

Click Here to Leave a Comment Below 0 comments