SELECT E.OPRID , C.BUDGET_PERIOD , D.BUSINESS_UNIT , B.ESTABID , A.EMPLID , A.LANGUAGE_CD , A.NAME_DISPLAY FROM PSOPRDEFN E , PS_NAMES_LNG A , PS_JOB B , PS_BUDGET_PERIOD C , PS_BUDGET_BUS_UNIT D WHERE A.EMPLID = B.EMPLID AND A.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES_LNG, A, %CurrentDateIn) AND B.BUSINESS_UNIT=D.BUSINESS_UNIT AND D.BUDGET_PERIOD=C.BUDGET_PERIOD AND B.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_JOB D1 WHERE D1.EMPLID = B.EMPLID AND D1.EMPL_RCD = B.EMPL_RCD AND D1.EFFDT<=C.THRU_DATE ) AND B.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT = B.EFFDT) AND B.JOB_INDICATOR = 'P'
|