HRS_SA_APPLST_I

(SQL View)
 Index Back

In Process Applicants for SA

This view lists all in-process applicants along with their disposition information to be used for Simplified Analytics

SELECT RCMNT.HRS_PERSON_ID , RCMNT.HRS_RCMNT_ID , RCMNT.HRS_PROFILE_SEQ , OPR.OPRID , APP_NM.NAME_DISPLAY , APPL.APP_PER_STATUS , 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_LOC_AREA , COALESCE(( SELECT L.DESCR FROM PS_HRS_LOCATION L WHERE L.HRS_LOCATION_ID = JO.HRS_PRM_LOC_AREA AND L.SETID = JO.SETID_DEPT),' ') , JO.HRS_PRM_JOBCODE , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = JO.SETID_DEPT AND JC.JOBCODE = JO.HRS_PRM_JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.EFFDT <= %CurrentDateIn)),' ') , JO.HRS_PRM_POSITION , COALESCE (( SELECT POS.DESCR FROM PS_POSITION_DATA POS WHERE POS.POSITION_NBR = JO.HRS_PRM_POSITION AND POS.EFFDT = ( SELECT MAX(EFFDT) FROM PS_POSITION_DATA WHERE POSITION_NBR = POS.POSITION_NBR AND EFFDT <= %CurrentDateIn) AND POS.EFF_STATUS = 'A'), ' ') , JO.SETID_DEPT, ' ' 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 , PS_HRS_APPLICANT APPL 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 = 'M' 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 AND APPL.HRS_PERSON_ID = RCMNT.HRS_PERSON_ID

  • Related Language Record: HRS_SA_APLST_LN

    • # 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 APP_PER_STATUS Character(1) VARCHAR2(1) NOT NULL Identifies the type of applicant - External Applicant, Employee or Non-Employee
    A=External Applicant
    E=Employee
    N=Non-Employee
    7 HRS_STATUS_DT Date(10) DATE Recruiting Solutions Status Date field
    8 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone
    9 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    10 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.
    11 HRS_STS_DESCR Character(30) VARCHAR2(30) NOT NULL Status Description
    12 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
    13 NAME_DESCR Character(50) VARCHAR2(50) NOT NULL Description
    14 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID
    15 NAME Character(50) VARCHAR2(50) NOT NULL Name
    16 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    17 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    18 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    19 HRS_BU_DESCR Character(30) VARCHAR2(30) NOT NULL candidate gateway business unit description
    20 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    21 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    22 HRS_PRM_LOC_AREA Number(15,0) DECIMAL(15) NOT NULL Primary recruiting location area
    23 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    24 HRS_PRM_JOBCODE Character(6) VARCHAR2(6) NOT NULL Primary Job Code of Job Opening.
    25 HRS_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    26 HRS_PRM_POSITION Character(8) VARCHAR2(8) NOT NULL Position Number
    27 HRS_POS_DATA_DESCR Character(70) VARCHAR2(70) NOT NULL Position
    28 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    29 HRS_YES_NO Character(1) VARCHAR2(1) NOT NULL Registered Online
    N=No
    Y=Yes