|
SELECT A.EMPLID ,B.DESCR ,G.PROCESS_INSTANCE FROM PS_JOB A , PS_POSITION_DATA B , PS_GPTH_RPT_G_VW G WHERE A.EMPLID=G.EMPLID AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= G.PRD_END_DT) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND B.POSITION_NBR = A.POSITION_NBR AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_POSITION_DATA B_ED WHERE B.POSITION_NBR = B_ED.POSITION_NBR AND B_ED.EFF_STATUS = 'A' AND B_ED.EFFDT <= G.PRD_END_DT )
|