CU_BAR_HR_00005

SELECT A.HRS_JOB_OPENING_ID, A.HRS_RCRT_TMPL_ID, A.HRS_JO_TYPE, A.REFERRAL_PGM_ID, A.ORIGINATOR_ID, A.MANAGER_ID, A.RECRUITER_ID, D.NAME_DISPLAY, A.GVT_SEL_OFFICIAL, TO_CHAR(A.OPEN_DT,'YYYY-MM-DD'), TO_CHAR(A.CLOSE_DT,'YYYY-MM-DD'), A.STATUS_CODE, B.DESCR, A.STATUS_REASON, C.DESCR, TO_CHAR(A.STATUS_DT,'YYYY-MM-DD'), A.HRS_OPENING_LIMIT, A.OPENINGS_CNT, A.OPENINGS_TARGET, A.HOT_JOB, A.BUSINESS_UNIT, A.SETID_DEPT, A.DEPTID, A.COMPANY, A.HRS_PRM_JOBCODE, A.HRS_PRM_POSITION, A.HRS_PRM_LOCATION, A.HRS_PRM_LOC_AREA, A.HRS_PRM_PST_TITLE, TO_CHAR(A.DESIRED_START_DT,'YYYY-MM-DD'), A.WC_COUNCIL_ID, A.ENCUMB_DT_INDC, TO_CHAR(A.PROJECTED_FILL_DT,'YYYY-MM-DD'), TO_CHAR(A.AUTHORIZATION_DT,'YYYY-MM-DD'), A.ESTABID, A.COUNTRY, A.CITIZENSHIP_STATUS, A.MIN_AGE, A.MAX_AGE, A.SEX, A.HRS_CONTACT_ID, A.GVT_CONSIDER_AREA, A.GVT_DRAFT_STATUS, A.HRS_G_JO_TYPE, A.JOB_FAMILY, TO_CHAR(CAST((A.HRS_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.HRS_ROW_ADD_OPRID, TO_CHAR(CAST((A.HRS_ROW_UPD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.HRS_ROW_UPD_OPRID, B.STATUS_AREA, B.STATUS_PHASE, B.DEFAULT_STS_IND, B.HRS_DRFT_STS_IND, B.HRS_APRV_STS_IND, B.HRS_DND_APRV_IND, B.HRS_DFLT_APRV_IND, B.DATA_TYPE_CD, C.STATUS_AREA, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),B.STATUS_CODE,C.STATUS_CODE,C.STATUS_REASON

FROM (SELECT TA.HRS_JOB_OPENING_ID, TA.HRS_RCRT_TMPL_ID, TA.HRS_JO_TYPE, TA.REFERRAL_PGM_ID, TA.ORIGINATOR_ID, TA.MANAGER_ID, TA.RECRUITER_ID, TA.GVT_SEL_OFFICIAL, TA.OPEN_DT, TA.CLOSE_DT, TA.STATUS_CODE, TA.STATUS_REASON, TA.STATUS_DT, TA.HRS_OPENING_LIMIT, TA.OPENINGS_CNT, TA.OPENINGS_TARGET, TA.HOT_JOB, TA.BUSINESS_UNIT, TA.SETID_DEPT, TA.DEPTID, TA.COMPANY, TA.HRS_PRM_JOBCODE, TA.HRS_PRM_POSITION, TA.HRS_PRM_LOCATION, TA.HRS_PRM_LOC_AREA, TA.HRS_PRM_PST_TITLE, TA.DESIRED_START_DT, TA.WC_COUNCIL_ID, TA.ENCUMB_DT_INDC, TA.PROJECTED_FILL_DT, TA.AUTHORIZATION_DT, TA.ESTABID, TA.COUNTRY, TA.CITIZENSHIP_STATUS, TA.MIN_AGE, TA.MAX_AGE, TA.SEX, TA.HRS_CONTACT_ID, TA.GVT_CONSIDER_AREA, TA.GVT_DRAFT_STATUS, TA.HRS_G_JO_TYPE, TA.JOB_FAMILY, TA.HRS_ROW_ADD_DTTM, TA.HRS_ROW_ADD_OPRID, TA.HRS_ROW_UPD_DTTM, TA.HRS_ROW_UPD_OPRID, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')

FROM PS_HRS_JOB_OPENING TA,PS_HRS_JO_SEC_VW A1 WHERE TA.HRS_JOB_OPENING_ID = A1.HRS_JOB_OPENING_ID AND A1.OPRID = '23420211' ) A, PS_HRS_STS_JO_I B, PS_HRS_RSN_JO_I C, (SELECT TD.NAME_DISPLAY, TD.EFFDT, TD.EMPLID,TD.NAME_TYPE

FROM PS_NAMES TD,PS_PERALL_SEC_QRY D1 WHERE TD.EMPLID = D1.EMPLID AND D1.OPRID = '23420211' ) D

WHERE ( B.STATUS_CODE = A.STATUS_CODE

AND A.STATUS_CODE = C.STATUS_CODE (+)

AND A.STATUS_REASON = C.STATUS_REASON (+)

AND A.BUSINESS_UNIT = 'BAR01'

AND D.EFFDT =

(SELECT MAX(D_ED.EFFDT) FROM PS_NAMES D_ED

WHERE D.EMPLID = D_ED.EMPLID

AND D.NAME_TYPE = D_ED.NAME_TYPE

AND D_ED.EFFDT <= SYSDATE)

AND A.RECRUITER_ID = D.EMPLID(+) )

ORDER BY 1