|
SELECT A.EMPLID , A.EMPL_RCD , L1.LANGUAGE_CD , L1.DESCR , L2.DESCR , L3.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 , %Table(DEPT_TBL_LANG) L1 , %Table(LOCATION_LANG) L2 , %Table(JOBCODE_LANG) L3 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' AND L1.SETID = G.SETID AND L1.DEPTID = G.DEPTID AND %EffdtCheck(DEPT_TBL_LANG, L1, %CurrentDateIn) AND L2.SETID = H.SETID AND L2.LOCATION = H.LOCATION AND %EffdtCheck(LOCATION_LANG, L2, %CurrentDateIn) AND L3.SETID = I.SETID AND L3.JOBCODE = I.JOBCODE AND %EffdtCheck(JOBCODE_LANG, L3, %CurrentDateIn) AND L1.LANGUAGE_CD = L2.LANGUAGE_CD AND L1.LANGUAGE_CD = L3.LANGUAGE_CD
|
EMPLID