...
Query SQL: | |
---|---|
SELECT B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.EMPLID, A.HR_STATUS, A.FULL_PART_TIME, A.JOB_INDICATOR, A.EMPL_RCD, A.JOBCODE, H.DESCR, A.LABOR_AGREEMENT, A.UNION_POS, A.UNION_CD, I.DESCR, A.DEPTID, C.DESCR, D.EMAIL_ADDR, E.PHONE, E.PHONE_TYPE, A.LOCATION, G.DESCR, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),C.SETID,C.DEPTID,TO_CHAR(C.EFFDT,'YYYY-MM-DD'),GH.SETID,GH.LOCATIONJOBCODE,TO_CHAR(GH.EFFDT,'YYYY-MM-DD'),HI.SETID,H.JOBCODE,UNION_CD,TO_CHAR(HI.EFFDT,'YYYY-MM-DD'),G.SETID,IG.UNION_CDLOCATION,TO_CHAR(IG.EFFDT,'YYYY-MM-DD') FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1, PS_PERSON_NAME B, PS_DEPT_TBL C, (SELECT TD.EMAIL_ADDR, TD.E_ADDR_TYPE, TD.EMPLID FROM PS_EMAIL_ADDRESSES TD,PS_PERALL_SEC_QRY D1 WHERE TD.EMPLID = D1.EMPLID AND D1.OPRID = '23420211' ) D, (SELECT TE.PHONE, TE.PHONE_TYPE, TE.PREF_PHONE_FLAG, TE.EMPLID FROM PS_PERSONAL_PHONE TE,PS_PERALL_SEC_QRY E1 WHERE TE.EMPLID = E1.EMPLID AND E1.OPRID = '23420211' ) E, PS_PERSONAL_DATA F, PS_PERALL_SEC_QRY F1, PS_LOCATIONJOBCODE_TBL GH, PS_JOBCODEUNION_TBL HI, PS_UNIONLOCATION_TBL IG, PS_SET_CNTRL_REC G2 WHERE G.LOCATION = A.LOCATION AND G2.SETCNTRLVALUE = A.BUSINESS_UNIT AND G2.RECNAME = 'LOCATION_TBL' AND G2.SETID = G.SETID AND A.WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.OPRID = '23420211' AND F.EMPLID = F1.EMPLID AND F1.OPRID = '23420211' 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 <= SYSDATE) 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 A.EMPLID = B.EMPLID AND A.HR_STATUS = 'A' AND A.FULL_PART_TIME IN ('F','P') AND A.LABOR_AGREEMENT IN ('PSC','WC','BC','ECP','STE','CME','NONE') AND C.DEPTID = A.DEPTID AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_DEPT_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.DEPTID = C_ED.DEPTID AND C_ED.EFFDT <= SYSDATE) AND C.SETID = 'BAR01' AND B.EMPLID = D.EMPLID(+) AND D.E_ADDR_TYPE = 'BUSN' AND B.EMPLID = E.EMPLID(+) AND E.PREF_PHONE_FLAG = 'Y'AND A.JOB_INDICATOR IN ('P','S') AND B.EMPLID = F.EMPLIDAND G.LOCATION = A.LOCATION AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_LOCATION_TBL G_ED WHERE G.SETID = G_ED.SETID AND G.LOCATION = G_ED.LOCATION AND G_ED.EFFDT <= SYSDATE) AND H.JOBCODE = A.JOBCODE AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_JOBCODE_TBL H_ED WHERE H.SETID = H_ED.SETID AND H.JOBCODE = H_ED.JOBCODE AND H_ED.EFFDT <= SYSDATE) AND I.UNION_CD = A.UNION_CD AND I.EFFDT = (SELECT MAX(I_ED.EFFDT) FROM PS_UNION_TBL I_ED WHERE I.UNION_CD = I_ED.UNION_CD AND I_ED.EFFDT <= SYSDATE) AND A.EMPL_STATUS IN ('A','L','P','S') AND ( E.PREF_PHONE_FLAG IS NULL OR E.PREF_PHONE_FLAG = 'Y') AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_LOCATION_TBL G_ED WHERE G.SETID = G_ED.SETID AND G.LOCATION = G_ED.LOCATION AND G_ED.EFFDT <= A.EFFDT) ) ORDER BY 4, 7, 8 |