|
SELECT A.PROCESS_INSTANCE , A.EMPLID , A.EMPL_RCD , A.FPY_MODE , A.FPY_JOB_CI , A.FPY_ASG_ID , A.FPY_PERSON_NUM , A.FPY_PERSON_ID , A.FPY_WR_POS_ID FROM PS_FPY_ASG_DET A WHERE A.FPY_BYPASS_FLG <>'Y' AND A.FPY_PROCESSED_FLG <> 'Y' AND A.EMPLID <> ' ' AND A.EMPLID IN ( SELECT DISTINCT(B.SUPERVISOR_ID) FROM PS_FPY_ASG_DET B WHERE B.SUPERVISOR_ID <> ' ') AND NOT EXISTS( SELECT 'X' FROM PS_JOB C WHERE A.EMPLID = C.EMPLID AND C.HR_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_JOB C1 WHERE C.EMPLID = C1.EMPLID AND C.EMPL_RCD = C1.EMPL_RCD) AND C.EFFSEQ = ( SELECT MAX(C2.EFFSEQ) FROM PS_JOB C2 WHERE C.EMPLID = C2.EMPLID AND C.EMPL_RCD = C2.EMPL_RCD AND C.EFFDT = C2.EFFDT)) AND A.FPY_MODE = 'C' AND A.FPY_JOB_CI IN ( 'E' , 'C', 'P') AND A.FPY_ASG_ID = ( SELECT MIN(D.FPY_ASG_ID) FROM PS_FPY_ASG_DET D WHERE D.FPY_PERSON_NUM = A.FPY_PERSON_NUM AND A.FPY_MODE = D.FPY_MODE AND A.FPY_JOB_CI = D.FPY_JOB_CI) GROUP BY A.PROCESS_INSTANCE , A.EMPLID , A.EMPL_RCD , A.FPY_MODE , A.FPY_JOB_CI , A.FPY_ASG_ID, A.FPY_PERSON_NUM, A.FPY_PERSON_ID, A.FPY_WR_POS_ID
|