HRS_JOOFRHTVW2L

(SQL View)
 Index Back

JOB OFFER HEADER VIEW

JOB OFFER Mobile Approval Header view

SELECT OFR.HRS_OFF_ID , OFR.HRS_PERSON_ID , OFR.HRS_RCMNT_ID , JCL.LANGUAGE_CD , JCL.DESCR , BU.DESCR , COALESCE(( SELECT NML.NAME_DISPLAY FROM PS_HRS_PERSON_NM_L NML WHERE NML.EMPLID = OFR.HRS_HIRING_MGR_ID AND NML.LANGUAGE_CD = JCL.LANGUAGE_CD),( SELECT NM.NAME_DISPLAY FROM PS_HRS_PERSON_NM_I NM WHERE NM.EMPLID = OFR.HRS_HIRING_MGR_ID )) , COALESCE(( SELECT NML.NAME_DISPLAY FROM PS_HRS_PERSON_NM_L NML WHERE NML.EMPLID = OFR.RECRUITER_ID AND NML.LANGUAGE_CD = JCL.LANGUAGE_CD),( SELECT NM.NAME_DISPLAY FROM PS_HRS_PERSON_NM_I NM WHERE NM.EMPLID = OFR.RECRUITER_ID )) ,COALESCE(( SELECT NML.NAME_DISPLAY FROM PS_HRS_APP_NM_LNG NML WHERE NML.HRS_PERSON_ID = OFR.HRS_PERSON_ID AND NML.LANGUAGE_CD = JCL.LANGUAGE_CD),( SELECT NM.NAME_DISPLAY FROM PS_HRS_APP_NAMES NM WHERE NM.HRS_PERSON_ID = OFR.HRS_PERSON_ID)) FROM PS_HRS_OFFER OFR , PS_HRS_RCMNT RCM , PS_JOBCODE_TBL JCD , PS_JOBCODE_LANG JCL , PS_BUS_UNIT_HR_LNG BU WHERE RCM.HRS_PERSON_ID = OFR.HRS_PERSON_ID AND RCM.HRS_RCMNT_ID = OFR.HRS_RCMNT_ID AND JCD.SETID = ( SELECT SCR.SETID FROM PS_SET_CNTRL_REC SCR WHERE SCR.SETCNTRLVALUE = OFR.BUSINESS_UNIT AND RECNAME = 'JOBCODE_TBL') AND JCD.JOBCODE = OFR.JOBCODE AND JCD.EFFDT = ( SELECT MAX(JCD2.EFFDT) FROM PS_JOBCODE_TBL JCD2 WHERE JCD2.SETID = JCD.SETID AND JCD2.JOBCODE = JCD.JOBCODE AND JCD2.EFFDT <= %CurrentDateIn) AND JCD.EFF_STATUS = 'A' AND JCL.SETID = JCD.SETID AND JCL.JOBCODE = JCD.JOBCODE AND JCL.EFFDT = JCD.EFFDT AND BU.BUSINESS_UNIT = OFR.BUSINESS_UNIT AND BU.LANGUAGE_CD = JCL.LANGUAGE_CD

  • Related Language Record for HRS_JOOFR_HTVW2

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HRS_OFF_ID Number(15,0) DECIMAL(15) NOT NULL Offer ID
    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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    5 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    8 NAME_FIELD Character(50) VARCHAR2(50) NOT NULL Name Field
    9 NAME_DESCR Character(50) VARCHAR2(50) NOT NULL Description