|
SELECT DISTINCT A.EMPLID , L.LANGUAGE_CD , COALESCE(( SELECT NL.NAME_DISPLAY FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.NAME_DISPLAY FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT DL.DESCR FROM PS_DEPT_TBL_LANG DL WHERE DL.SETID = A.SETID_DEPT AND DL.DEPTID = A.DEPTID AND DL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DL1, DL, %CurrentDateIn)), ( SELECT D.DESCR FROM PS_DEPT_TBL D WHERE D.SETID = A.SETID_DEPT AND D.DEPTID = A.DEPTID AND %EffdtCheck(DEPT_TBL D1, D, %CurrentDateIn)), ' ') , COALESCE(( SELECT JCL.DESCR FROM PS_JOBCODE_LANG JCL WHERE JCL.SETID = A.SETID_JOBCODE AND JCL.JOBCODE = A.JOBCODE AND JCL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCL1, JCL, %CurrentDateIn)), ( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = A.SETID_JOBCODE AND JC.JOBCODE = A.JOBCODE AND %EffdtCheck(JOBCODE_TBL JC1, JC, %CurrentDateIn)), ' ') FROM PS_JOB A, PSLANGUAGES L WHERE A.EMPL_STATUS IN ('A','L','P','S','W') AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT<= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT = A.EFFDT) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT O.LANGUAGE_CD FROM PSOPTIONS O)
|
EMPLID