HRS_PG_APPL_LST

(SQL View)
 Index Back

Applicant List for Pivot Grid

This view lists all applicants along with their recruitment status information.

SELECT RCMNT.HRS_PERSON_ID , RCMNT.HRS_RCMNT_ID , RCMNT.HRS_PROFILE_SEQ , OPR.OPRID , APP_NM.NAME_DISPLAY , RCMNT.HRS_STATUS_DT , RCMNT.HRS_JOB_OPENING_ID , JO.HRS_PRM_PST_TITLE , RCMNT.STATUS_CODE , STS.DESCR , JO.RECRUITER_ID , COALESCE(( SELECT REC.NAME_DISPLAY FROM PS_NAMES REC WHERE REC.EMPLID = JO.RECRUITER_ID AND REC.NAME_TYPE = 'PRI' AND REC.EFFDT = ( SELECT MAX(REC1.EFFDT) FROM PS_NAMES REC1 WHERE REC1.EMPLID = REC.EMPLID AND REC1.NAME_TYPE = 'PRI' AND REC1.EFFDT <= %CurrentDateIn)),' ') , JO.MANAGER_ID , COALESCE(( SELECT REC.NAME_DISPLAY FROM PS_NAMES REC WHERE REC.EMPLID = JO.MANAGER_ID AND REC.NAME_TYPE = 'PRI' AND REC.EFFDT = ( SELECT MAX(REC1.EFFDT) FROM PS_NAMES REC1 WHERE REC1.EMPLID = REC.EMPLID AND REC1.NAME_TYPE = 'PRI' AND REC1.EFFDT <= %CurrentDateIn)),' ') , JO.JOB_FAMILY , COALESCE(( SELECT JOFAM.DESCR FROM PS_JOB_FAMILY_TBL JOFAM WHERE JOFAM.JOB_FAMILY = JO.JOB_FAMILY AND JOFAM.EFFDT = ( SELECT MAX(JOFAM1.EFFDT) FROM PS_JOB_FAMILY_TBL JOFAM1 WHERE JOFAM1.JOB_FAMILY = JOFAM.JOB_FAMILY AND JOFAM1.EFFDT <= RCMNT.HRS_STATUS_DT)),' ') , JO.BUSINESS_UNIT , BU.DESCR , JO.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.DEPTID = JO.DEPTID AND DEPT.SETID = JO.SETID_DEPT AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT1.DEPTID = DEPT.DEPTID AND DEPT1.SETID = DEPT.SETID AND DEPT1.EFFDT <= RCMNT.HRS_STATUS_DT)),' ') , JO.HRS_PRM_LOCATION , COALESCE(( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND LOC.LOCATION = JO.HRS_PRM_LOCATION AND LOC.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = JO.BUSINESS_UNIT AND LOC.EFFDT = ( SELECT MAX(LOC1.EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC1.LOCATION = LOC.LOCATION AND LOC1.SETID = LOC.SETID AND LOC1.EFFDT <= RCMNT.HRS_STATUS_DT)),' ') , COALESCE(( SELECT LOC_C.COUNTRY FROM PS_LOCATION_TBL LOC_C , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND LOC_C.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = JO.BUSINESS_UNIT AND LOC_C.LOCATION = JO.HRS_PRM_LOCATION AND LOC_C.EFFDT = ( SELECT MAX(LOC_C1.EFFDT) FROM PS_LOCATION_TBL LOC_C1 WHERE LOC_C1.LOCATION = LOC_C.LOCATION AND LOC_C1.SETID = LOC_C.SETID AND LOC_C1.EFFDT <= RCMNT.HRS_STATUS_DT)),' ') , COALESCE(( SELECT CNTRY.DESCR FROM PS_COUNTRY_TBL CNTRY WHERE CNTRY.COUNTRY = ( SELECT CNTRY1.COUNTRY FROM PS_LOCATION_TBL CNTRY1 , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND CNTRY1.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = JO.BUSINESS_UNIT AND CNTRY1.LOCATION = JO.HRS_PRM_LOCATION AND CNTRY1.EFFDT = ( SELECT MAX(CNTRY2.EFFDT) FROM PS_LOCATION_TBL CNTRY2 WHERE CNTRY2.LOCATION = CNTRY1.LOCATION AND CNTRY2.SETID = CNTRY1.SETID AND CNTRY2.EFFDT <= RCMNT.HRS_STATUS_DT))),' ') , %DateNull , %DateNull , ' ' FROM PS_HRS_RCMNT RCMNT , PS_HRS_JOB_OPENING JO , PS_HRS_STS_TBL STS , PS_HRS_STS_TBL STS1 , PS_HRS_JO_TEAM TM , PSOPRDEFN OPR , PS_HRS_APP_NAMES APP_NM , PS_BUS_UNIT_TBL_HR BU WHERE RCMNT.HRS_JOB_OPENING_ID = JO.HRS_JOB_OPENING_ID AND TM.HRS_JOB_OPENING_ID = JO.HRS_JOB_OPENING_ID AND RCMNT.STATUS_CODE = STS.STATUS_CODE AND STS.STATUS_AREA = '3' AND STS.STATUS_PHASE < '9' AND STS.HRS_DRFT_STS_IND <> 'Y' AND TM.HRS_JO_TM_TYPE = ( SELECT MAX(TM2.HRS_JO_TM_TYPE) FROM PS_HRS_JO_TEAM TM2 WHERE TM.HRS_JOB_OPENING_ID = TM2.HRS_JOB_OPENING_ID AND TM.EMPLID=TM2.EMPLID) AND TM.EMPLID = OPR.EMPLID AND JO.STATUS_CODE = STS1.STATUS_CODE AND STS1.STATUS_AREA = '1' AND STS1.OPEN_STS_IND = 'Y' AND APP_NM.HRS_PERSON_ID = RCMNT.HRS_PERSON_ID AND APP_NM.NAME_TYPE = 'PRI' AND BU.BUSINESS_UNIT = JO.BUSINESS_UNIT

  • Related Language Record: HRS_PG_APPL_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id
    2 HRS_RCMNT_ID Number(15,0) DECIMAL(15) NOT NULL App Tracking Recruitment ID
    3 HRS_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece
    4 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    5 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    6 HRS_STATUS_DT Date(10) DATE Recruiting Solutions Status Date field
    7 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone
    8 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    9 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.
    10 HRS_STS_DESCR Character(30) VARCHAR2(30) NOT NULL Status Description
    11 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
    12 NAME_DESCR Character(50) VARCHAR2(50) NOT NULL Description
    13 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID
    14 NAME Character(50) VARCHAR2(50) NOT NULL Name
    15 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    16 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    17 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    18 HRS_BU_DESCR Character(30) VARCHAR2(30) NOT NULL candidate gateway business unit description
    19 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    20 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    21 HRS_PRM_LOCATION Character(10) VARCHAR2(10) NOT NULL Primary Recruiting Location of Job Opening,
    22 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    23 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    24 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description
    25 FROM_DT Date(10) DATE From Date
    26 TO_DT Date(10) DATE To Date
    27 HRS_YES_NO Character(1) VARCHAR2(1) NOT NULL Registered Online
    N=No
    Y=Yes