E-PRCS: How to find scheduled jobs and processes in PeopleSoft?

You can use this SQL to fetch the scheduled jobs and process in PeopleSoft application. This proves useful during application maintenance activities like Application or PeopleTools upgrade, data archiving etc.

SELECT A.PRCSTYPE, A.PRCSNAME
, A.RUNCNTLID, A.OPRID
, (SELECT avg(b.enddttm - b.begindttm) FROM PSPRCSRQST b
WHERE b.RECURNAME <> ' '
AND b.PRCSJOBSEQ = '0'
AND b.prcsname = a.prcsname
AND b.runcntlid = a.runcntlid
and b.runstatus = '9') as Average
, (SELECT MAX(C.enddttm - C.begindttm) FROM PSPRCSRQST C
WHERE C.RECURNAME <> ' '
AND C.PRCSJOBSEQ = '0'
AND C.prcsname = a.prcsname
AND C.runcntlid = a.runcntlid
and C.runstatus = '9') as lONGEST
, DECODE(A.PRCSTYPE,'PSJob',(SELECT D.DESCR FROM ps_PRCSJOBDEFN D
WHERE d.prcsjobname = a.prcsname),(SELECT E.DESCR FROM ps_prcsdefn E
WHERE e.prcstype = a.prcstype and e.prcsname = a.prcsname)) as
PRCS_DESCR
, A.RECURNAME, to_Char(E.begindttm,'HH24:MI') Time
FROM PSPRCSRQST A, PS_PRCSRECUR E
WHERE A.recurname = e.recurname (+)
and A.RECURNAME <> ' '
AND PRCSJOBSEQ = 0
AND RUNSTATUS = '5'
order by to_Char(E.begindttm,'HH24:MI'), a.oprid ;