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 the 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
Run status of ‘5’ refers to ‘Queued’ processes. You can get the complete list of process scheduler run status codes here.
Possible modifications to the above SQL
Find the recurrence for other processes
Change the A.PRCSNAME to appropriate process name
A.PRCSNAME='AEMINITEST'
Find the recurrence for a 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