Ask Question
Help Others
Home › Blogs › Lepa's blog

SQL to Check PeopleSoft Process Name Status

Mon, 11/26/2007 - 9:31pm — Lepa
Your rating: None Average: 5 (3 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;

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
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!

Comments

Have a question? Please ask it on the forum instead.

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.