HRS_APPI_ALL_VW

(SQL View)
 Index Back

Internal Search Indexing View

This record is used in the internal search indexing process to collect information for the Verity Index.

SELECT APP.HRS_PERSON_ID , PROF.HRS_PROFILE_SEQ , PROF.HRS_SUBMITTED_DTTM , PROF.HRS_RESUME_ID , PROF.LANG_CD , APP.EMPLID , APP.APP_PER_STATUS , APP.STATUS_CODE , PROF.BUSINESS_UNIT , NM.NAME_DISPLAY , PROF.HRS_AL_LOG_ID , PROF.PREV_EMPL_BY_COMPY , AD.CITY , AD.STATE , AD.COUNTRY , CASE WHEN AI.HRS_ROW_UPD_DTTM IS NOT NULL AND R.HRS_ROW_UPD_DTTM > PROF.HRS_ROW_UPD_DTTM AND R.HRS_ROW_UPD_DTTM > AI.HRS_ROW_UPD_DTTM THEN R.HRS_ROW_UPD_DTTM WHEN AI.HRS_ROW_UPD_DTTM IS NOT NULL AND AI.HRS_ROW_UPD_DTTM > PROF.HRS_ROW_UPD_DTTM AND AI.HRS_ROW_UPD_DTTM > R.HRS_ROW_UPD_DTTM THEN AI.HRS_ROW_UPD_DTTM WHEN AI.HRS_ROW_UPD_DTTM IS NULL AND R.HRS_ROW_UPD_DTTM > PROF.HRS_ROW_UPD_DTTM THEN R.HRS_ROW_UPD_DTTM ELSE PROF.HRS_ROW_UPD_DTTM END , (%Substring(%Cast(%DateTimeOut(PROF.HRS_SUBMITTED_DTTM), DateTime, Character, 10),1, 4) %Concat '/' %Concat %Substring(%Cast(%DateTimeOut(PROF.HRS_SUBMITTED_DTTM), DateTime, Character, 10),6, 2)) FROM PS_HRS_APPLICANT APP LEFT OUTER JOIN PS_HRS_APP_ADDR_I AD ON APP.HRS_PERSON_ID = AD.HRS_PERSON_ID , PS_HRS_APP_PROFILE PROF LEFT OUTER JOIN PS_HRS_APPINDX_CTL AI ON (PROF.HRS_PERSON_ID = AI.HRS_PERSON_ID AND PROF.HRS_PROFILE_SEQ = AI.HRS_PROFILE_SEQ) , PS_HRS_APP_NAMES NM , PS_HRS_RCMNT R WHERE APP.HRS_PERSON_ID = PROF.HRS_PERSON_ID AND APP.HRS_PERSON_ID = NM.HRS_PERSON_ID AND (APP.STATUS_CODE IN ( SELECT STATUS_CODE FROM PS_HRS_SRCHIDX_APS APS WHERE APS.HRS_SRCH_INDEX_CD = 'A') OR NOT EXISTS ( SELECT 1 FROM PS_HRS_SRCHIDX_APS APS WHERE APS.HRS_SRCH_INDEX_CD = 'A')) AND APP.HRS_PERSON_ID = R.HRS_PERSON_ID AND R.HRS_RCMNT_ID = ( SELECT MAX(R2.HRS_RCMNT_ID) FROM PS_HRS_RCMNT R2 WHERE R2.HRS_PERSON_ID = R.HRS_PERSON_ID) AND R.HRS_LOGICAL_DELETE = 'N'

  • Related Language Record: HRS_APPI_ALL_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_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece
    3 HRS_SUBMITTED_DTTM DateTime(26) TIMESTAMP Submitted Date Time
    4 HRS_RESUME_ID Number(15,0) DECIMAL(15) NOT NULL Resume id
    5 LANG_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    CFR=Canadian French
    DAN=Danish
    DUT=Dutch
    ENG=English
    ESP=Spanish
    FRA=French
    GER=German
    GRK=Greek
    INE=International English
    ITA=Italian
    JPN=Japanese
    KOR=Korean
    POR=Portuguese
    SVE=Swedish
    THA=Thai
    ZHS=Simplified Chinese
    ZHT=Traditional Chinese
    6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    7 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
    8 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.
    9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    10 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    11 HRS_AL_LOG_ID Number(15,0) DECIMAL(15) NOT NULL Resume Extraction Queue ID
    12 PREV_EMPL_BY_COMPY Character(1) VARCHAR2(1) NOT NULL Previously Employed by Company
    N=No
    Y=Yes
    13 CITY Character(30) VARCHAR2(30) NOT NULL City
    14 STATE Character(6) VARCHAR2(6) NOT NULL State
    15 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    16 HRS_ROW_UPD_DTTM DateTime(26) TIMESTAMP Row Updated DateTime
    17 HRS_APPIDX_YEAR Character(7) VARCHAR2(7) NOT NULL Applied In