(FOC14013) CORRELATED SUBQUERY ON COLUMN 'SETID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'JOBCODE' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'SETID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'DEPTID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'POSITION_NBR' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'SETID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'JOBCODE' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'SETID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'DEPTID' IS NOT SUPPORTED
 (FOC14013) CORRELATED SUBQUERY ON COLUMN 'POSITION_NBR' IS NOT SUPPORTED

-SET &SYSTDATE = &DATEYYMD |' '|'23:59:59';

SQL
SELECT A.HRS_JOB_OPENING_ID, D.JOBCODE, A.OPEN_DT, B.DESCR, A.DEPTID, C.DESCR, A.POSITION_NBR, A.RECRUITER_ID, D.REG_TEMP, D.FULL_PART_TIME, D.STD_HOURS, B.COMP_FREQUENCY, B.EEO1CODE
  FROM PS_UHS_JOB_REQ_UPGD A, PS_JOBCODE_TBL B, PS_SET_CNTRL_REC B2, PS_DEPT_TBL C, PS_SET_CNTRL_REC C2, PS_POSITION_DATA D, PS_POSTN_SRCH_QRY D1
  WHERE B.JOBCODE = A.JOBCODE
    AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND B2.RECNAME = 'JOBCODE_TBL'
    AND B2.SETID = B.SETID
    AND C.DEPTID = A.DEPTID
    AND C2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND C2.RECNAME = 'DEPT_TBL'
    AND C2.SETID = C.SETID
    AND D.POSITION_NBR = D1.POSITION_NBR
    AND D1.ROWSECCLASS = 'DPALL'
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_JOBCODE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.JOBCODE = B_ED.JOBCODE
          AND B_ED.EFFDT <= '&SYSTDATE')
     AND A.JOBCODE = B.JOBCODE
     AND A.DEPTID = C.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 <= '&SYSTDATE')
     AND D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_POSITION_DATA D_ED
        WHERE D.POSITION_NBR = D_ED.POSITION_NBR
          AND D_ED.EFFDT <= '&SYSTDATE')
     AND A.POSITION_NBR = D.POSITION_NBR
     AND A.STATUS_CODE IN ('010','090','020','050','060','070','030','040')
     AND A.DEPTID <> '40902000' )
UNION
SELECT E.HRS_JOB_OPENING_ID, I.JOBCODE, E.STATUS_DT, J.DESCR, I.DEPTID, H.DESCR, I.POSITION_NBR, E.RECRUITER_ID, I.REG_TEMP, I.FULL_PART_TIME, I.STD_HOURS, G.COMP_FREQUENCY, G.EEO1CODE
  FROM PS_UHS_JOB_REQ_UPGD E, PS_PCMH_ER_REQ_POS F, PS_JOBCODE_TBL G, PS_DEPT_TBL H, PS_POSITION_DATA I, PS_POSTN_SRCH_QRY I1, PS_POSN_NOEFFDT_VW J
  WHERE J.POSITION_NBR = F.PCMH_REQ_POS_NBR
    AND I.POSITION_NBR = I1.POSITION_NBR
    AND I1.ROWSECCLASS = 'DPALL'
    AND J.POSITION_NBR = I1.POSITION_NBR
    AND ( E.HRS_JOB_OPENING_ID_A6 = F.JOB_REQ_NBR
     AND G.JOBCODE = E.JOBCODE
     AND G.EFFDT =
        (SELECT MAX(G_ED.EFFDT) FROM PS_JOBCODE_TBL G_ED
        WHERE G.SETID = G_ED.SETID
          AND G.JOBCODE = G_ED.JOBCODE
          AND G_ED.EFFDT <= '&SYSTDATE')
     AND H.DEPTID = I.DEPTID
     AND H.EFFDT =
        (SELECT MAX(H_ED.EFFDT) FROM PS_DEPT_TBL H_ED
        WHERE H.SETID = H_ED.SETID
          AND H.DEPTID = H_ED.DEPTID
          AND H_ED.EFFDT <= '&SYSTDATE')
     AND I.EFFDT =
        (SELECT MAX(I_ED.EFFDT) FROM PS_POSITION_DATA I_ED
        WHERE I.POSITION_NBR = I_ED.POSITION_NBR
          AND I_ED.EFFDT <= '&SYSTDATE')
     AND E.STATUS_CODE IN ('010','090','020','050','060','070','030','040')
     AND F.PCMH_POS_FULL_FLAG = 'O'
     AND F.PCMH_REQ_POS_NBR = I.POSITION_NBR
     AND I.DEPTID <> '40902000' )
  ORDER BY 4
;
TABLE ON TABLE PCHOLD FORMAT ALPHA
END

-SET &SYSTDATE = &DATEYYMD |' '|'23:59:59';

SET SQLENGINE = SQLORA
SQL SET SERVER PSHRTST
-RUN

SQL SQLORA

SELECT A.HRS_JOB_OPENING_ID, D.JOBCODE, A.OPEN_DT, B.DESCR, A.DEPTID, C.DESCR, A.POSITION_NBR, A.RECRUITER_ID, D.REG_TEMP, D.FULL_PART_TIME, D.STD_HOURS, B.COMP_FREQUENCY, B.EEO1CODE
  FROM PS_UHS_JOB_REQ A, PS_JOBCODE_TBL B, PS_SET_CNTRL_REC B2, PS_DEPT_TBL C, PS_SET_CNTRL_REC C2, PS_POSITION_DATA D
  WHERE B.JOBCODE = A.JOBCODE
    AND B2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND B2.RECNAME = 'JOBCODE_TBL'
    AND B2.SETID = B.SETID
    AND C.DEPTID = A.DEPTID
    AND C2.SETCNTRLVALUE = A.BUSINESS_UNIT
    AND C2.RECNAME = 'DEPT_TBL'
    AND C2.SETID = C.SETID
    AND ( B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_JOBCODE_TBL B_ED
        WHERE B.SETID = B_ED.SETID
          AND B.JOBCODE = B_ED.JOBCODE
          AND B_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND A.JOBCODE = B.JOBCODE
     AND A.DEPTID = C.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 <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_POSITION_DATA D_ED
        WHERE D.POSITION_NBR = D_ED.POSITION_NBR
          AND D_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND A.POSITION_NBR = D.POSITION_NBR
     AND A.STATUS_CODE IN ('010','090','020','050','060','070','030','040')
     AND A.DEPTID <> '40902000' )
UNION
SELECT E.HRS_JOB_OPENING_ID, I.JOBCODE, E.STATUS_DT, J.DESCR, I.DEPTID, H.DESCR, I.POSITION_NBR, E.RECRUITER_ID, I.REG_TEMP, I.FULL_PART_TIME, I.STD_HOURS, G.COMP_FREQUENCY, G.EEO1CODE
  FROM PS_UHS_JOB_REQ E, PS_JOBCODE_TBL G, PS_DEPT_TBL H, PS_POSITION_DATA I, PS_POSN_NOEFFDT_VW J
  WHERE  (
     G.JOBCODE = E.JOBCODE
     AND G.EFFDT =
        (SELECT MAX(G_ED.EFFDT) FROM PS_JOBCODE_TBL G_ED
        WHERE G.SETID = G_ED.SETID
          AND G.JOBCODE = G_ED.JOBCODE
          AND G_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND H.DEPTID = I.DEPTID
     AND H.EFFDT =
        (SELECT MAX(H_ED.EFFDT) FROM PS_DEPT_TBL H_ED
        WHERE H.SETID = H_ED.SETID
          AND H.DEPTID = H_ED.DEPTID
          AND H_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND I.EFFDT =
        (SELECT MAX(I_ED.EFFDT) FROM PS_POSITION_DATA I_ED
        WHERE I.POSITION_NBR = I_ED.POSITION_NBR
          AND I_ED.EFFDT <= to_timestamp('&SYSTDATE', 'YYYY/MM/DD HH24:MI:SS'))
     AND E.STATUS_CODE IN ('010','090','020','050','060','070','030','040')
     AND I.DEPTID <> '40902000' )
  ORDER BY 4
;
TABLE
ON TABLE HOLD AS ASJOBPOST1
END