|
SELECT A.EMPLID , B.NAME , G.SUPERVISOR_ID , C.POOL_ID , C.DESCR , C.MANAGER_ID , A.REGION_CD , D.ASSIGNMENT_ID , MIN(E.START_DT) , MAX(E.END_DT) , F.RS_END_DATE , D.PROJ_ROLE , D.BUSINESS_UNIT , D.PROJECT_ID , %DateDiff(%CURRENTDATEIN,MAX(E.END_DT)) FROM PS_RS_WORKER_TBL A , PS_PERSONAL_DATA B , PS_RS_RES_POOL C , PS_RS_ASSIGNMENT D , PS_RS_ASSGN_DETAIL E , PS_RS_APPOINTMENT F , PS_JOB G , PS_RS_WRKR_EFFDT H WHERE A.EMPLID = B.EMPLID AND D.EMPLID = A.EMPLID AND G.EMPLID = A.EMPLID AND H.EMPLID = A.EMPLID AND H.SYSTEM_SOURCE = 'RS' AND H.EFFDT = ( SELECT MAX(H1.EFFDT) FROM PS_RS_WRKR_EFFDT H1 WHERE H.EMPLID = H1.EMPLID AND H1.SYSTEM_SOURCE = H.SYSTEM_SOURCE) AND G.EMPL_RCD = H.EMPL_RCD AND G.EFFDT = ( SELECT MAX(G1.EFFDT) FROM PS_JOB G1 WHERE G.EMPLID = G1.EMPLID AND G.EMPL_RCD = G1.EMPL_RCD AND G1.EFFDT <= %CurrentDateIn) AND G.EFFSEQ = ( SELECT MAX(G2.EFFSEQ) FROM PS_JOB G2 WHERE G2.EMPLID = G.EMPLID AND G2.EMPL_RCD = G.EMPL_RCD AND G2.EFFDT = G.EFFDT) AND G.EMPL_STATUS NOT IN('I','T','S','D','U','V','Q','R','X') AND D.ASSIGNMENT_ID =( SELECT DISTINCT(F1.RS_APPT_ID) FROM PS_RS_APPOINTMENT F1 WHERE F1.SYSTEM_SOURCE = 'RS' AND F1.RS_APPT_TYPE = 'ASN' AND F1.EMPLID = A.EMPLID AND F1.RS_END_DATE = ( SELECT MAX(F2.RS_END_DATE) FROM PS_RS_APPOINTMENT F2 WHERE F1.EMPLID = F2.EMPLID AND F1.SYSTEM_SOURCE = F2.SYSTEM_SOURCE AND F1.RS_APPT_TYPE = F2.RS_APPT_TYPE )) AND C.POOL_ID = ( SELECT C2.POOL_ID FROM PS_RS_POOL_MEMBER C1 , PS_RS_MEMBER_DATES C2 WHERE C1.POOL_ID = C2.POOL_ID AND C1.MEMBER_ID = C2.MEMBER_ID AND C1.EMPLID = A.EMPLID AND C2.START_DATE <= %CurrentDateIn AND (C2.END_DATE IS NULL OR C2.END_DATE >= %CurrentDateIn ) ) AND E.ASSIGNMENT_ID = D.ASSIGNMENT_ID AND E.ASSIGNMENT_ID = F.RS_APPT_ID AND F.SYSTEM_SOURCE = 'RS' AND F.RS_APPT_TYPE = 'ASN' GROUP BY A.EMPLID,B.NAME,G.SUPERVISOR_ID,C.POOL_ID,C.DESCR,C.MANAGER_ID,A.REGION_CD, D.ASSIGNMENT_ID, F.RS_END_DATE ,D.PROJ_ROLE, D.BUSINESS_UNIT, D.PROJECT_ID
|