|
SELECT J.EMPLID , J.EMPL_RCD , L.LANGUAGE_CD , COALESCE(( SELECT DL.DESCR FROM PS_DEPT_TBL_LANG DL WHERE DL.SETID = J.SETID_DEPT AND DL.DEPTID = J.DEPTID AND DL.LANGUAGE_CD = L.LANGUAGE_CD AND DL.EFFDT = ( SELECT MAX(DL1.EFFDT) FROM PS_DEPT_TBL_LANG DL1 WHERE DL1.SETID = DL.SETID AND DL1.DEPTID = DL.DEPTID AND DL1.EFFDT <= %CurrentDateIn)), ( SELECT D.DESCR FROM PS_DEPT_TBL D WHERE D.SETID = J.SETID_DEPT AND D.DEPTID = J.DEPTID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID = D.SETID AND D1.DEPTID = D.DEPTID AND D1.EFFDT <= %CurrentDateIn)), ' ') , COALESCE(( SELECT JCL.DESCR FROM PS_JOBCODE_LANG JCL WHERE JCL.SETID = J.SETID_JOBCODE AND JCL.JOBCODE = J.JOBCODE AND JCL.LANGUAGE_CD = L.LANGUAGE_CD AND JCL.EFFDT = ( SELECT MAX(JCL1.EFFDT) FROM PS_JOBCODE_LANG JCL1 WHERE JCL1.SETID = JCL.SETID AND JCL1.JOBCODE = JCL.JOBCODE AND JCL1.EFFDT <= %CurrentDateIn)), ( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.EFFDT <= %CurrentDateIn)), ' ') FROM PS_JOB J , PSLANGUAGES L WHERE J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND ((J1.EFFDT <= %CurrentDateIn) OR (J.EFFDT > %CurrentDateIn AND %CurrentDateIn <( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD)))) AND J.EFFSEQ = ( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE J3.EMPLID = J.EMPLID AND J3.EMPL_RCD = J.EMPL_RCD AND J3.EFFDT = J.EFFDT) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT O.LANGUAGE_CD FROM PSOPTIONS O)
|
EMPLID