Disciplinary Actions Report PeopleSoft HRMS

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.COMPANY FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND
A.EMPLID = JB.EMPLID)COMPANY,
(SELECT JB.BUSINESS_UNIT FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND A.EMPLID = JB.EMPLID)BUSINESS_UNIT,

(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) AND
X2.REG_REGION IN ('AUS','CHN','IND','IDN','NZL','ZAF','VNM','TWN','THA','PHL','JPN');