SQL to Check PeopleSoft Process Name Status

Your rating: None Average: 3.6 (5 votes)

Run this SQL on a particulare process name to get it's process status form PeopleSoft process request.

SELECT a.prcsname, a.oprid, a.servernamerun, b.xlatlongname AS STATUS,
a.rundttm,
TRUNC (MOD ((a.enddttm - a.rundttm) * 24, 24)) "Hr",
TRUNC (MOD ((a.enddttm - a.rundttm) * 24 * 60, 60)) mi,
TRUNC (MOD ((a.enddttm - a.rundttm) * 24 * 60 * 60, 60)) sec,
a.prcsinstance, c.outputdir
FROM psprcsrqst a, xlattable b, ps_cdm_list c
WHERE b.fieldname = 'RUNSTATUS'
AND b.fieldvalue = a.runstatus
AND a.prcsinstance = c.prcsinstance
AND a.prcsname = 'YourProcessName'
ORDER BY 5 DESC

Update: 01/10/08
I noticed the above SQL doesn't work in PT 8.49 due to table name change. You would need to change table xlattable to PSXLATITEM for the SQL to work. Here is an update version:

SELECT a.prcsname, a.oprid, a.servernamerun, b.xlatlongname AS STATUS,
a.rundttm,
TRUNC (MOD ((a.enddttm - a.rundttm) * 24, 24)) "Hr",
TRUNC (MOD ((a.enddttm - a.rundttm) * 24 * 60, 60)) mi,
TRUNC (MOD ((a.enddttm - a.rundttm) * 24 * 60 * 60, 60)) sec,
a.prcsinstance, c.outputdir
FROM psprcsrqst a, PSXLATITEM b, ps_cdm_list c
WHERE b.fieldname = 'RUNSTATUS'
AND b.fieldvalue = a.runstatus
AND a.prcsinstance = c.prcsinstance
AND a.prcsname = 'your_process_name'
ORDER BY 5 DESC;

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!