CU_BAR_HR_00010

SELECT A.POSITION_NBR, CASE A.EFF_STATUS WHEN 'A' THEN 'Open/Vacant' END, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFF_STATUS, A.DESCR, A.DESCRSHORT, A.ACTION, A.ACTION_REASON, TO_CHAR(A.ACTION_DT,'YYYY-MM-DD'), A.BUSINESS_UNIT, A.DEPTID, C.DESCR, A.JOBCODE, D.DESCR, A.POSN_STATUS, TO_CHAR(A.STATUS_DT,'YYYY-MM-DD'), A.BUDGETED_POSN, A.CONFIDENTIAL_POSN, A.KEY_POSITION, A.MAX_HEAD_COUNT, A.REPORTS_TO, A.FULL_PART_TIME, TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.LASTUPDOPRID, 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'),D.SETID,D.JOBCODE,TO_CHAR(D.EFFDT,'YYYY-MM-DD')

FROM PS_POSITION_DATA A, PS_POSTN_SRCH_QRY A1, PS_DEPT_TBL C, PS_JOBCODE_TBL D

WHERE A.POSITION_NBR = A1.POSITION_NBR

AND A1.OPRID = '23420211'

AND A.EFFDT =

(SELECT MAX(A_ED.EFFDT) FROM PS_POSITION_DATA A_ED

WHERE A.POSITION_NBR = A_ED.POSITION_NBR

AND A_ED.EFFDT <= SYSDATE)

AND A.FULL_PART_TIME = 'P'

AND A.EFF_STATUS = 'A'

AND A.POSITION_NBR IN (SELECT B.POSITION_NBR

FROM PS_POSN_VACANT B, PS_DEPT_SRCH_QRY B1

WHERE B.SETID = B1.SETID

AND B.DEPTID = B1.DEPTID

AND B1.OPRID = '23420211'

AND B.FULL_PART_TIME = 'P')

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 C.SETID_LOCATION = 'BAR01'

AND C.EFF_STATUS = 'A'

AND D.JOBCODE = A.JOBCODE

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 <= SYSDATE)

AND D.SETID = 'SHARE'

AND D.EFF_STATUS = 'A'

ORDER BY 1