|
SELECT JOB.SETID_LOCATION , JOB.EMPLID ,JOB.LOCATION FROM PS_JOB JOB WHERE ( JOB.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE JOB.EMPLID = A_ED.EMPLID AND JOB.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= %CurrentDateIn)) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT = JOB.EFFDT) AND JOB.EMPL_RCD = ( SELECT MIN(JOB2.EMPL_RCD) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB2.EMPLID AND EMPL_RCD = JOB2.EMPL_RCD AND EFFDT <= JOB.EFFDT) AND JOB2.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB2.EMPLID AND EMPL_RCD = JOB2.EMPL_RCD AND EFFDT = JOB2.EFFDT) AND ((JOB2.EMPL_STATUS IN('A','L','P','S','W') AND JOB2.JOB_INDICATOR = 'P') OR NOT EXISTS ( SELECT 'X' FROM PS_JOB JOB3 WHERE JOB3.EMPLID = JOB2.EMPLID AND JOB3.EMPL_RCD <> JOB2.EMPL_RCD AND JOB3.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = JOB3.EMPLID AND EMPL_RCD = JOB3.EMPL_RCD AND EFFDT <= JOB.EFFDT) AND JOB3.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB3.EMPLID AND EMPL_RCD = JOB3.EMPL_RCD AND EFFDT = JOB3.EFFDT) AND ((JOB3.EMPL_STATUS IN('A','L','P','S','W') AND (JOB2.EMPL_STATUS NOT IN('A','L','P','S','W') OR (JOB3.JOB_INDICATOR = 'P' AND JOB2.JOB_INDICATOR <> 'P'))) OR (JOB3.JOB_INDICATOR = 'P' AND JOB2.JOB_INDICATOR <> 'P' AND JOB2.EMPL_STATUS NOT IN('A','L','P','S','W'))))))
|