Peering into the depths of the PeopleSoft Process Scheduler Queue

On occaision you need to be able to see what's been running in the PeopleSoft Batch queue at a given moment in time. At such times the following SQL comes in quite handy.

-- --------------------------
-- Dump Process Monitor Table
-- --------------------------
select PRCS.OPRID,
       PRCS.PRCSJOBNAME,
       PRCS.PRCSNAME,
       PRCS.RUNCNTLID,
       XLAT.XLATLONGNAME,
       to_char(PRCS.BEGINDTTM,'yyyy-mm-dd-hh24.mi.ss') as Began,
       to_char(PRCS.ENDDTTM,'yyyy-mm-dd-hh24.mi.ss')   as Ended
       (((TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'J')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'J'))) * 86400)+(TO_NUMBER(TO_CHAR(PRCS.ENDDTTM, 'SSSSS')) - TO_NUMBER(TO_CHAR(PRCS.BEGINDTTM, 'SSSSS')))) as Dur_Sec
  from PSPRCSRQST   PRCS,
       XLATTABLE_VW XLAT
 where PRCS.BEGINDTTM   > to_date('2009-03-02-01.00.00','YYYY-MM-DD-HH24.MI.SS')
   and PRCS.RUNSTATUS    = XLAT.FIELDVALUE
   and XLAT.FIELDNAME    = 'RUNSTATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'
 order by PRCS.BEGINDTTM Desc

Be careful when you stare into the void however, the void might stare back at you.