|
SELECT P.EMPLID ,P.NAME ,W.REGION_CD ,J.BUSINESS_UNIT ,B.DESCR ,J.DEPTID ,D.DESCR ,P.COUNTRY ,C.DESCR ,P.STATE ,S.DESCR FROM PS_PERSONAL_DATA P ,PS_RS_WORKER_TBL W ,PS_JOB J ,PS_BUS_UNIT_TBL_HR B , PS_DEPT_TBL D ,PS_COUNTRY_TBL C ,PS_STATE_TBL S WHERE P.EMPLID = W.EMPLID AND P.EMPLID = J.EMPLID AND J.EMPL_RCD = 0 AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J.EMPLID = J1.EMPLID AND J.EMPL_RCD = J1.EMPL_RCD AND J1.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J.EMPLID = J2.EMPLID AND J.EMPL_RCD = J2.EMPL_RCD AND J.EFFDT = J2.EFFDT) AND J.EMPL_STATUS IN ('A','L','P','W') AND J.BUSINESS_UNIT = B.BUSINESS_UNIT AND J.SETID_DEPT = D.SETID AND J.DEPTID = D.DEPTID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D.SETID = D1.SETID AND D.DEPTID = D1.DEPTID AND D1.EFFDT <= %CurrentDateIn) AND P.COUNTRY = C.COUNTRY AND P.COUNTRY = S.COUNTRY AND P.STATE = S.STATE
|