Image of CU_BAR_HR_00009:
Query SQL:
Query SQL: | |
---|---|
SELECT A.POSITION_NBR, CASE A.EFF_STATUS WHEN 'A' THEN 'Filled' END, C.EMPLID, 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, D.DESCR, A.JOBCODE, E.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'),D.SETID,D.DEPTID,TO_CHAR(D.EFFDT,'YYYY-MM-DD'),E.SETID,E.JOBCODE,TO_CHAR(E.EFFDT,'YYYY-MM-DD') FROM PS_POSITION_DATA A, PS_POSTN_SRCH_QRY A1, PS_POSN_INCUMBENT C, PS_POSTN_SRCH_QRY C1, PS_DEPT_TBL D, PS_JOBCODE_TBL E WHERE A.POSITION_NBR = A1.POSITION_NBR AND A1.OPRID = '23420211' AND C.POSITION_NBR = C1.POSITION_NBR AND C1.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 = 'F' AND A.EFF_STATUS = 'A' AND A.POSITION_NBR IN (SELECT B.POSITION_NBR FROM PS_JOB B, PS_EMPLMT_SRCH_QRY B1 WHERE B.EMPLID = B1.EMPLID AND B.EMPL_RCD = B1.EMPL_RCD AND B1.OPRID = '23420211' AND ( B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= SYSDATE) AND B.EFFSEQ = (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.EMPL_RCD = B_ES.EMPL_RCD AND B.EFFDT = B_ES.EFFDT) AND B.HR_STATUS = 'A' AND B.FULL_PART_TIME = 'F' AND B.EMPL_STATUS IN ('A','P','L','S') )) AND A.POSITION_NBR = C.POSITION_NBR AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_POSN_INCUMBENT C_ED WHERE C.POSITION_NBR = C_ED.POSITION_NBR AND C_ED.EFFDT <= SYSDATE) AND D.DEPTID = A.DEPTID AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_DEPT_TBL D_ED WHERE D.SETID = D_ED.SETID AND D.DEPTID = D_ED.DEPTID AND D_ED.EFFDT <= SYSDATE) AND D.SETID = 'BAR01' AND D.SETID_LOCATION = 'BAR01' AND D.EFF_STATUS = 'A' AND E.JOBCODE = A.JOBCODE AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_JOBCODE_TBL E_ED WHERE E.SETID = E_ED.SETID AND E.JOBCODE = E_ED.JOBCODE AND E_ED.EFFDT <= SYSDATE) AND E.SETID = 'SHARE' AND E.EFF_STATUS = 'A' ) ORDER BY 1 |