Peoplesoft Disciplinary Actions by Reg region SQL

posted by Dineshkumar R on April 19, 2016

-- Disciplinary Actions by Reg_region,employee type

SELECT X2.*,X1.DESCR FROM SYSADM.PS_DISCIP_TYPE_TBL X1,(SELECT

(SELECT JB.REG_REGION FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND A.EMPLID = JB.EMPLID)REG_REGION,

(SELECT JB.EMPL_TYPE FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND A.EMPLID = JB.EMPLID)EMPLOYEE_TYPE,

A.DISCIPLINE_DT,A.EMPLID,A.PURGE_DT, A.DISCIPLINARY_TYPE

FROM SYSADM.PS_DISCIPLIN_ACTN A ORDER BY 1,2,3 ASC)X2 WHERE X2.DISCIPLINARY_TYPE = X1.DISCIPLINARY_TYPE AND X2.REG_REGION = X1.REG_REGION

AND X1.EFFDT =(SELECT MAX(X3.EFFDT) FROM SYSADM.PS_DISCIP_TYPE_TBL X3 WHERE X1.DISCIPLINARY_TYPE = X3.DISCIPLINARY_TYPE AND

X3.REG_REGION = X1.REG_REGION AND X3.EFFDT < SYSDATE);