|
SELECT OPR.OPRID , A.HRS_PERSON_ID , A.HRS_RCMNT_ID , K.LANGUAGE_CD , K.NAME_DISPLAY FROM PS_HRS_PSN_APP_VW A , PS_HRS_JOB_OPENING J , PSOPRDEFN OPR , PS_HRS_STS_TBL C , PS_HRS_APP_NM_LNG K WHERE C.STATUS_CODE = J.STATUS_CODE AND A.HRS_JOB_OPENING_ID = J.HRS_JOB_OPENING_ID AND A.HRS_PERSON_ID = K.HRS_PERSON_ID AND C.STATUS_AREA = '1' AND C.OPEN_STS_IND = 'Y' AND A.OPEN_STS_IND = 'Y' AND ((A.STATUS_PHASE = '5' AND EXISTS ( SELECT 'X' FROM PS_HRS_RCM_INT I , PS_HRS_STS_TBL S WHERE A.HRS_PERSON_ID = I.HRS_PERSON_ID AND A.HRS_RCMNT_ID = I.HRS_RCMNT_ID AND I.HRS_INT_STTS = S.STATUS_CODE AND S.STATUS_AREA = '5' AND S.COMPLETE_STS_IND = 'Y')) OR EXISTS ( SELECT 'X' FROM PS_HRS_OFFER_MAX_I B , PS_HRS_STS_TBL C WHERE A.HRS_PERSON_ID = B.HRS_PERSON_ID AND A.HRS_RCMNT_ID = B.HRS_RCMNT_ID AND B.STATUS_CODE = C.STATUS_CODE AND C.STATUS_AREA = '6' AND C.HRS_DRFT_STS_IND = 'Y') OR (A.STATUS_PHASE = '6' AND NOT EXISTS ( SELECT 'X' FROM PS_HRS_OFFER_MAX_I D , PS_HRS_STS_TBL E WHERE A.HRS_PERSON_ID = D.HRS_PERSON_ID AND A.HRS_RCMNT_ID = D.HRS_RCMNT_ID AND D.STATUS_CODE = E.STATUS_CODE AND E.STATUS_AREA = '6' AND (E.OPEN_STS_IND = 'Y' OR E.COMPLETE_STS_IND = 'Y')))) AND ((J.HRS_JOB_OPENING_ID IN ( SELECT JO.HRS_JOB_OPENING_ID FROM PS_HRS_JO_TEAM JO WHERE JO.HRS_JOB_OPENING_ID = J.HRS_JOB_OPENING_ID AND JO.EMPLID = OPR.EMPLID AND JO.EMPLID <> ' ' AND JO.HRS_JO_TM_TYPE IN ('G','M','R'))) OR EXISTS ( SELECT 'X' FROM PS_HRS_OFFER Z , PS_HRS_STS_TBL Y WHERE Z.HRS_PERSON_ID = A.HRS_PERSON_ID AND Z.HRS_RCMNT_ID = A.HRS_RCMNT_ID AND (Z.ORIGINATOR_ID = OPR.EMPLID OR Z.RECRUITER_ID = OPR.EMPLID OR Z.HRS_HIRING_MGR_ID = OPR.EMPLID) AND Y.STATUS_CODE = Z.STATUS_CODE AND Y.STATUS_AREA = '6' AND Y.HRS_DRFT_STS_IND = 'Y'))
|
OPRID