|
SELECT A.EMPLID , A.EMPL_RCD , A.JOBCODE , A.LOCATION , A.DEPTID , G.DESCR , H.DESCR , I.DESCR FROM %Table(JOB) A , %Table(SET_CNTRL_REC) B , %Table(SET_CNTRL_REC) C , %Table(SET_CNTRL_REC) D , %Table(DEPT_TBL) G , %Table(LOCATION_TBL) H , %Table(JOBCODE_TBL) I WHERE A.EMPL_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM %Table(JOB) A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A3.EFFSEQ) FROM %Table(JOB) A3 WHERE A3.EMPLID = A.EMPLID AND A3.EMPL_RCD = A.EMPL_RCD AND A3.EFFDT = A.EFFDT) AND A.BUSINESS_UNIT = B.SETCNTRLVALUE AND B.RECNAME = 'DEPT_TBL' AND B.SETID = G.SETID AND A.DEPTID = G.DEPTID AND G.EFFDT = ( SELECT MAX(EFFDT) FROM %Table(DEPT_TBL) L WHERE G.SETID = L.SETID AND G.DEPTID = L.DEPTID AND L.EFFDT <= %CurrentDateIn) AND G.EFF_STATUS = 'A' AND A.BUSINESS_UNIT = C.SETCNTRLVALUE AND C.RECNAME = 'LOCATION_TBL' AND C.SETID = H.SETID AND A.LOCATION = H.LOCATION AND H.EFFDT = ( SELECT MAX(EFFDT) FROM %Table(LOCATION_TBL) M WHERE M.SETID = H.SETID AND M.LOCATION = H.LOCATION AND M.EFFDT <= %CurrentDateIn) AND H.EFF_STATUS = 'A' AND A.BUSINESS_UNIT = D.SETCNTRLVALUE AND D.RECNAME = 'JOBCODE_TBL' AND D.SETID = I.SETID AND A.JOBCODE = I.JOBCODE AND I.EFFDT = ( SELECT MAX(EFFDT) FROM %Table(JOBCODE_TBL) N WHERE I.SETID = N.SETID AND I.JOBCODE = N.JOBCODE AND N.EFFDT <= %CurrentDateIn) AND I.EFF_STATUS = 'A'
|
EMPLID