List of processes requested to the process scheduler with the Process Status

The below query fetches the processes requested to the process scheduler along with the Process Status:
select
RQST.RUNSTATUS,
RQST.PRCSTYPE,
(select XLAT.XLATLONGNAME
from PSXLATITEM XLAT
where XLAT.EFFDT = (select max(XLAT_ED.EFFDT)
                                       from PSXLATITEM XLAT_ED
                                       where XLAT_ED.FIELDNAME = XLAT.FIELDNAME
                                       and XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE)
and XLAT.FIELDNAME = 'RUNSTATUS'
and XLAT.FIELDVALUE = RQST.RUNSTATUS
) as RUNSTATUS_XLAT,
count(RQST.PRCSINSTANCE) as TOTAL_PROCESSES,
min(RUNDTTM) as FIRST_OCCURRED,
max(RUNDTTM) as LAST_OCCURRED
from PSPRCSRQST RQST
group by RQST.RUNSTATUS, RQST.PRCSTYPE
order by RUNSTATUS_XLAT, RQST.PRCSTYPE;
 
In the PeopleTools 8.4x the translate values of the field RUNSTATUS gives the Values and Status.

The below SQL query will return the summary of the process run statuses in the process request table:
select
RUNSTATUS,
(select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = RUNSTATUS
) as RUNSTATUS_DESCR,
count(PRCSINSTANCE)
from
PSPRCSRQST
group by RUNSTATUS
order by RUNSTATUS;

Courtesy: peoplesoft.wikidot.com