Some useful SQLs in PeopleSoft

--the permission lists associated to a user
SELECT A.OPRID, D.ROLEUSER, B.ROLENAME, B.CLASSID, C.ClASSDEFNDESC
FROM PSOPRDEFN A, PSROLECLASS B, PSCLASSDEFN C, PSROLEUSER D
WHERE A.OPRID=D.ROLEUSER AND B.CLASSID=C.CLASSID AND B.ROLENAME=D.ROLENAME AND A.OPRID= 'SPNR0000'
ORDER BY B.CLASSID;

-- The Value corresponding to a Process Run Status
SELECT XLATLONGNAME, FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME = 'RUNSTATUS';

-- To get the processing time
SELECT
PRCSINSTANCE,
PRCSNAME,
OPRID,
RUNSTATUSDESCR,
DECODE (FLOOR(TO_CHAR((enddttm-begindttm)*24*60))||':'||LPAD(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))- FLOOR(TO_CHAR((enddttm-begindttm)*24*60)))*60) ,2,0), ':',' ',FLOOR(TO_CHAR((enddttm- begindttm)*24*60))||':'||LPAD(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))-FLOOR(TO_CHAR((enddttm-begindttm)*24*60)))*60) ,2,0)) AS MINSEC
FROM PS_PMN_PRCSLIST
WHERE runstatusdescr ='Success';