HRS_OFFPREP_VW

(SQL View)
 Index Back

Offers to Prepare Alert View

Retrieves a list of applications that are ready for an offer or have an unsubmitted draft offer in progress.

SELECT OPR.OPRID , A.HRS_PERSON_ID , A.HRS_RCMNT_ID , A.HRS_JOB_OPENING_ID , A.STATUS_CODE , A.STATUS_REASON , A.HRS_STATUS_DT , A.ENTRY_SOURCE , A.ENTRY_DATE , A.HRS_DES_LOC1 , A.HRS_DES_LOC2 , A.DESIRED_REG_TEMP , A.DESIRED_FULL_PART , A.DESIRED_START_DT , A.DESIRED_HOURS , A.TRAVELLING , A.HRS_TRAVEL_PERCENT , A.HIGHEST_EDUC_LVL , A.YEARS_OF_EXP , A.KEYWORD_MATCH , J.ORIGINATOR_ID , J.RECRUITER_ID , J.HRS_PRM_PST_TITLE , 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_NAMES 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 ((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' )) 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' )) UNION SELECT OPR.OPRID , A.HRS_PERSON_ID , A.HRS_RCMNT_ID , A.HRS_JOB_OPENING_ID , A.STATUS_CODE , A.STATUS_REASON , A.HRS_STATUS_DT , A.ENTRY_SOURCE , A.ENTRY_DATE , A.HRS_DES_LOC1 , A.HRS_DES_LOC2 , A.DESIRED_REG_TEMP , A.DESIRED_FULL_PART , A.DESIRED_START_DT , A.DESIRED_HOURS , A.TRAVELLING , A.HRS_TRAVEL_PERCENT , A.HIGHEST_EDUC_LVL , A.YEARS_OF_EXP , A.KEYWORD_MATCH , J.ORIGINATOR_ID , J.RECRUITER_ID , J.HRS_PRM_PST_TITLE , 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_NAMES 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 ((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' )) AND 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' ) UNION SELECT OPR.OPRID , A.HRS_PERSON_ID , A.HRS_RCMNT_ID , A.HRS_JOB_OPENING_ID , A.STATUS_CODE , A.STATUS_REASON , A.HRS_STATUS_DT , A.ENTRY_SOURCE , A.ENTRY_DATE , A.HRS_DES_LOC1 , A.HRS_DES_LOC2 , A.DESIRED_REG_TEMP , A.DESIRED_FULL_PART , A.DESIRED_START_DT , A.DESIRED_HOURS , A.TRAVELLING , A.HRS_TRAVEL_PERCENT , A.HIGHEST_EDUC_LVL , A.YEARS_OF_EXP , A.KEYWORD_MATCH , J.ORIGINATOR_ID , J.RECRUITER_ID , J.HRS_PRM_PST_TITLE , 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_NAMES 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 ((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' )) AND (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') ))

  • Related Language Record: HRS_OFFPREP_LN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id
    3 HRS_RCMNT_ID Number(15,0) DECIMAL(15) NOT NULL App Tracking Recruitment ID
    4 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone
    5 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.

    Prompt Table: HRS_STS_REC_I

    6 STATUS_REASON Character(3) VARCHAR2(3) NOT NULL Identifies a reason associated with a particular status.

    Prompt Table: HRS_RSN_REC_I

    7 STATUS_DT Date(10) DATE Status Date
    8 ENTRY_SOURCE Character(1) VARCHAR2(1) NOT NULL Entry Source
    A=Application
    O=Organization
    9 ENTRY_DATE Date(10) DATE Date Entered
    10 HRS_DES_LOC1 Number(15,0) DECIMAL(15) NOT NULL Desired Location 1

    Prompt Table: HRS_LOC_I

    11 HRS_DES_LOC2 Number(15,0) DECIMAL(15) NOT NULL Desired Location 2

    Prompt Table: HRS_LOC_I

    12 DESIRED_REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
    E=Either
    R=Regular
    T=Temporary
    13 DESIRED_FULL_PART Character(1) VARCHAR2(1) NOT NULL Full/Part-Time
    E=Either
    F=Full-Time
    P=Part-Time
    14 DESIRED_START_DT Date(10) DATE Desired Start Date
    15 DESIRED_HOURS Number(4,1) DECIMAL(3,1) NOT NULL Desired Hours Per Week
    16 TRAVELLING Character(1) VARCHAR2(1) NOT NULL Willing to Travel
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    17 HRS_TRAVEL_PERCENT Character(1) VARCHAR2(1) NOT NULL Travel percent
    0=Never or rarely
    1=Up to 25% of the time
    2=Up to 50% of the time
    3=Up to 75% of the time
    4=Up to 100% of the time
    18 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level

    Prompt Table: HIGH_EDUCLVL_VW

    19 YEARS_OF_EXP Number(5,1) DECIMAL(4,1) NOT NULL Years of Work Experience
    20 KEYWORD_MATCH Character(1) VARCHAR2(1) NOT NULL Keyword match
    21 ORIGINATOR_ID Character(11) VARCHAR2(11) NOT NULL Originator's ID
    22 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
    23 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    24 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country