CU_BAR_HR_0003_1

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, F.ADDRESS1, F.ADDRESS2, F.CITY, F.COUNTY, F.STATE, F.POSTAL, 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'),H.SETID,H.JOBCODE,TO_CHAR(H.EFFDT,'YYYY-MM-DD'),I.UNION_CD,TO_CHAR(I.EFFDT,'YYYY-MM-DD'),G.SETID,G.LOCATION,TO_CHAR(G.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_JOBCODE_TBL H, PS_UNION_TBL I, PS_LOCATION_TBL G, 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.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.EMPLID IN ('10836695','10835570','10835409','10835020','23418805','23418524','23430808','23420211','10859960','10863316','10861514','11012258','23135557','10834062')

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 A.JOB_INDICATOR IN ('P','S')

AND B.EMPLID = F.EMPLID

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 = 'Y'

OR E.PREF_PHONE_FLAG IS NULL)

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