|
SELECT J.EMPLID , J.COMPANY FROM PS_JOB J , PS_PER_ORG_ASGN B , PS_PY_PDF_COMP_MAP C , PS_FED_TAX_DATA D WHERE J.EMPLID = B.EMPLID AND J.EMPL_RCD = B.EMPL_RCD AND J.COMPANY = C.COMPANY AND J.EMPLID = D.EMPLID AND J.COMPANY = D.COMPANY AND ((C.PY_VIEW_INACT_JOB = 'Y') OR (C.PY_VIEW_INACT_JOB = 'N' AND J.HR_STATUS = 'A')) AND ((C.PY_POI_UPD_TAX = 'Y' AND B.PER_ORG IN ('EMP','POI')) OR (C.PY_POI_UPD_TAX = 'N' AND B.PER_ORG = 'EMP')) AND J.EFFDT = ( SELECT MIN(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.HR_STATUS = 'A' AND J1.EFFDT > %CurrentDateIn AND J1.EFFSEQ IN ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID=J1.EMPLID AND J2.EMPL_RCD=J1.EMPL_RCD AND J2.EFFDT=J1.EFFDT)) AND NOT EXISTS ( SELECT 'Y' FROM PS_JOB J3 WHERE J3.EMPLID = J.EMPLID AND J3.EMPL_RCD = J.EMPL_RCD AND J3.EFFDT = ( SELECT MAX(J4.EFFDT) FROM PS_JOB J4 WHERE J4.EMPLID = J3.EMPLID AND J4.EMPL_RCD = J3.EMPL_RCD AND J4.EFFDT <= %CurrentDateIn ) AND J3.EFFSEQ = ( SELECT MAX(J5.EFFSEQ) FROM PS_JOB J5 WHERE J5.EMPLID = J3.EMPLID AND J5.EMPL_RCD = J3.EMPL_RCD AND J5.EFFDT = J3.EFFDT ) AND J3.COMPANY = J.COMPANY AND J3.HR_STATUS = 'A')
|