Wednesday, September 15, 2010

List of Batch Processes in queued status

The following sql will generate a list of batch processes in queued status in Process Monitor.

SELECT b.prcsinstance,
       b.prcstype,
       b.prcsname,
       (SELECT e.descr
        FROM   ps_prcsdefn e
        WHERE  e.prcstype = b.prcstype
               AND e.prcsname = b.prcsname) descr,
       b.oprid,
       (SELECT a.oprdefndesc
        FROM   psoprdefn a
        WHERE  a.oprid = b.oprid) NAME,
       runcntlid,
       (SELECT f.qryname
               || ', '
               || f.descr
        FROM   ps_query_run_cntrl f
        WHERE  f.oprid = b.oprid
               AND f.run_cntl_id = b.runcntlid) qryname,
       recurname,
       runstatus,
       (SELECT xlatshortname
        FROM   psxlatitem c
        WHERE  c.fieldname = 'RUNSTATUS'
               AND c.fieldvalue = b.runstatus)  rundescr,
       diststatus,
       (SELECT d.xlatshortname
        FROM   psxlatitem d
        WHERE  d.fieldname = 'DISTSTATUS'
               AND d.fieldvalue = b.diststatus) distdescr,
       pt_retentiondays,
       rundttm,
       rqstdttm,
       lastupddttm,
       servernamerqst
FROM   psprcsrqst b
WHERE  runstatus IN ( 5 )
ORDER  BY rundttm DESC 

2 comments:

Banksy said...

I suggest you format SQL before postiing, using something like http://www.dpriver.com/pp/sqlformat.htm first to aid readability.

Your Email Design.com said...

This is very helpful information. Please feel free to share on http://www.peoplesoftcentral.com.