CU_BAR_HR_00014

SELECT B.DESCR, C.FIRST_NAME, C.LAST_NAME, A.EMPLID, D.DESCR, A.FULL_PART_TIME, G.NAME, TO_CHAR(A.LAST_HIRE_DT,'YYYY-MM-DD'), E.EMPLID, F.NAME,B.SETID,B.DEPTID,TO_CHAR(B.EFFDT,'YYYY-MM-DD'),D.SETID,D.JOBCODE,TO_CHAR(D.EFFDT,'YYYY-MM-DD')

FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1, PS_DEPT_TBL B, PS_SET_CNTRL_REC B2, PS_PERSON_NAME C, PS_JOBCODE_TBL D, PS_SET_CNTRL_REC D2, PS_POSN_INCUMBENT E, PS_POSTN_SRCH_QRY E1, PS_PERSON_NAME F, PS_EMPLOYEES G, PS_EMPLMT_SRCH_QRY G1

WHERE B.DEPTID = A.DEPTID

AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT

AND B2.RECNAME = 'DEPT_TBL'

AND B2.SETID = B.SETID

AND D.JOBCODE = A.JOBCODE

AND D2.SETCNTRLVALUE = A.BUSINESS_UNIT

AND D2.RECNAME = 'JOBCODE_TBL'

AND D2.SETID = D.SETID

AND A.EMPLID = A1.EMPLID

AND A.EMPL_RCD = A1.EMPL_RCD

AND A1.OPRID = '23420211'

AND E.POSITION_NBR = E1.POSITION_NBR

AND E1.OPRID = '23420211'

AND G.EMPLID = G1.EMPLID

AND G.EMPL_RCD = G1.EMPL_RCD

AND G1.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.HR_STATUS = 'A'

AND B.EFFDT =

(SELECT MAX(B_ED.EFFDT) FROM PS_DEPT_TBL B_ED

WHERE B.SETID = B_ED.SETID

AND B.DEPTID = B_ED.DEPTID

AND B_ED.EFFDT <= A.EFFDT)

AND A.EMPLID = C.EMPLID

AND D.EFFDT =

(SELECT MAX(D_ED.EFFDT) FROM PS_JOBCODE_TBL D_ED

WHERE D.SETID = D_ED.SETID

AND D.JOBCODE = D_ED.JOBCODE

AND D_ED.EFFDT <= A.EFFDT)

AND E.POSITION_NBR = A.REPORTS_TO

AND E.EFFDT =

(SELECT MAX(E_ED.EFFDT) FROM PS_POSN_INCUMBENT E_ED

WHERE E.POSITION_NBR = E_ED.POSITION_NBR

AND E_ED.EFFDT <= SYSDATE)

AND F.EMPLID = E.EMPLID

AND G.EFFDT =

(SELECT MAX(G_ED.EFFDT) FROM PS_EMPLOYEES G_ED

WHERE G.EMPLID = G_ED.EMPLID

AND G.EMPL_RCD = G_ED.EMPL_RCD

AND G_ED.EFFDT <= SYSDATE)

AND G.EFFSEQ =

(SELECT MAX(G_ES.EFFSEQ) FROM PS_EMPLOYEES G_ES

WHERE G.EMPLID = G_ES.EMPLID

AND G.EMPL_RCD = G_ES.EMPL_RCD

AND G.EFFDT = G_ES.EFFDT)

AND B.MANAGER_POSN = G.POSITION_NBR

AND A.DEPTID = :1

ORDER BY 1, 7, 10