SELECT J.EMPLID ,J.EMPL_RCD ,C.LANGUAGE_CD ,C.DESCRSHORT FROM PS_JOB J , PS_JOBCODE_LANG C WHERE J.EFFDT = ( SELECT MAX (J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <= %CurrentDateIn) 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 C.EFFDT = ( SELECT MAX (C2.EFFDT) FROM PS_JOBCODE_LANG C2 WHERE C2.SETID = C.SETID AND C2.JOBCODE = C.JOBCODE AND C2.LANGUAGE_CD = C.LANGUAGE_CD AND C2.EFFDT <= %CurrentDateIn) AND J.JOBCODE = C.JOBCODE AND J.SETID_JOBCODE = C.SETID
|