HRS_PG_APPL_LNG

(SQL View)
 Index Back

Applicant List for Pivot Grid

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

SELECT APP.HRS_PERSON_ID , APP.HRS_RCMNT_ID , APP.HRS_PROFILE_SEQ , APP.OPRID ,P.LANGUAGE_CD , ( SELECT APP_NM.NAME_DISPLAY FROM PS_HRS_APP_NM_LNG APP_NM WHERE APP_NM.HRS_PERSON_ID = APP.HRS_PERSON_ID AND APP_NM.NAME_TYPE = 'PRI' AND APP_NM.LANGUAGE_CD = P.LANGUAGE_CD), ( SELECT JOLNG.HRS_PRM_PST_TITLE FROM PS_HRS_JO_LNG JOLNG WHERE JOLNG.HRS_JOB_OPENING_ID = APP.HRS_JOB_OPENING_ID AND JOLNG.LANGUAGE_CD = P.LANGUAGE_CD), ( SELECT STS.DESCR FROM PS_HRS_STS_LNG STS WHERE STS.STATUS_CODE = APP.STATUS_CODE AND STS.STATUS_AREA = '3' AND STS.LANGUAGE_CD = P.LANGUAGE_CD), ( SELECT REC.NAME_DISPLAY FROM PS_NAMES_LNG REC WHERE REC.EMPLID = JO.RECRUITER_ID AND REC.NAME_TYPE = 'PRI' AND REC.LANGUAGE_CD = P.LANGUAGE_CD AND REC.EFFDT = ( SELECT MAX(REC1.EFFDT) FROM PS_NAMES_LNG REC1 WHERE REC1.EMPLID = REC.EMPLID AND REC1.NAME_TYPE = 'PRI' AND REC1.LANGUAGE_CD = REC.LANGUAGE_CD AND REC1.EFFDT <= %CurrentDateIn)), ( SELECT REC.NAME_DISPLAY FROM PS_NAMES_LNG REC WHERE REC.EMPLID = JO.MANAGER_ID AND REC.LANGUAGE_CD = P.LANGUAGE_CD AND REC.NAME_TYPE = 'PRI' AND REC.EFFDT = ( SELECT MAX(REC1.EFFDT) FROM PS_NAMES_LNG REC1 WHERE REC1.EMPLID = REC.EMPLID AND REC1.NAME_TYPE = 'PRI' AND REC1.LANGUAGE_CD = REC.LANGUAGE_CD AND REC1.EFFDT <= %CurrentDateIn)) , ( SELECT JF.DESCR FROM PS_JOB_FAMILY_LNG JF WHERE APP.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = P.LANGUAGE_CD AND JF.EFFDT = ( SELECT MAX(JF1.EFFDT) FROM PS_JOB_FAMILY_LNG JF1 WHERE JF1.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JF1.LANGUAGE_CD AND JF1.EFFDT <= RCMNT.HRS_STATUS_DT)) ,( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE APP.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = P.LANGUAGE_CD) , ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JO.SETID_DEPT = DEPT.SETID AND JO.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD =P.LANGUAGE_CD AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL_LANG DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.LANGUAGE_CD = DEPT1.LANGUAGE_CD AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= RCMNT.HRS_STATUS_DT)) , ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND LOCN.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = JO.BUSINESS_UNIT AND JO.HRS_PRM_LOCATION = LOCN.LOCATION AND LOCN.EFFDT = ( SELECT MAX(LOCN1.EFFDT) FROM PS_LOCATION_LANG LOCN1 WHERE LOCN.SETID = LOCN1.SETID AND LOCN.LOCATION = LOCN1.LOCATION AND LOCN.EFFDT <= %CurrentDateIn) AND LOCN.LANGUAGE_CD = P.LANGUAGE_CD) , ( SELECT CN.DESCR FROM PS_COUNTRYTBL_LANG CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND CN1.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = JO.BUSINESS_UNIT AND CN1.LOCATION = JO.HRS_PRM_LOCATION AND CN1.EFFDT = ( SELECT MAX(CN2.EFFDT) FROM PS_LOCATION_TBL CN2 WHERE CN2.LOCATION = CN1.LOCATION AND CN2.SETID = CN1.SETID AND CN2.EFFDT <= RCMNT.HRS_STATUS_DT)) AND CN.LANGUAGE_CD = P.LANGUAGE_CD) FROM PS_HRS_PG_APPL_LST APP , PS_HRS_JOB_OPENING JO , PSPRSMDEFNLANG P, PS_HRS_RCMNT RCMNT WHERE APP.HRS_JOB_OPENING_ID = JO.HRS_JOB_OPENING_ID AND APP.HRS_PERSON_ID = RCMNT.HRS_PERSON_ID AND APP.HRS_RCMNT_ID = RCMNT.HRS_RCMNT_ID AND APP.HRS_PROFILE_SEQ = RCMNT.HRS_PROFILE_SEQ AND P.PORTAL_OBJNAME = 'HC_RECRUIT_EMPLOYEE'

  • Related Language Record for HRS_PG_APPL_LST

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    7 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    8 HRS_STS_DESCR Character(30) VARCHAR2(30) NOT NULL Status Description
    9 NAME_DESCR Character(50) VARCHAR2(50) NOT NULL Description
    10 NAME Character(50) VARCHAR2(50) NOT NULL Name
    11 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    12 HRS_BU_DESCR Character(30) VARCHAR2(30) NOT NULL candidate gateway business unit description
    13 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    14 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    15 COUNTRY_DESCR Character(30) VARCHAR2(30) NOT NULL Country Description