HRS_JO_APP_A_VW

(SQL View)
 Index Back

Position Applied For - Max Req

Maximum app dt for position applied for record for an applicant for each requisition. 25360319 - Column REFERRAL_ID is being used for idetifying if referral exists for the applicant and it stores Applicant ID from HRS_EE_REF_JO record.

SELECT A.HRS_JOB_OPENING_ID ,A.STATUS_CODE ,A.HRS_PERSON_ID ,A.HRS_RCMNT_ID ,A.HRS_PROFILE_SEQ ,A.HRS_APP_INTEREST ,A.ENTRY_DATE ,A.STATUS_REASON ,B.DESCRSHORT ,B.DESCR ,A.HRS_STATUS_DT ,B.STATUS_PHASE ,NM.LAST_NAME ,NM.NAME_DISPLAY ,APP.APP_PER_STATUS ,A.HRS_ROW_UPD_DTTM ,PROF.PREV_EMPL_BY_COMPY ,PROF.HRS_RESUME_ID ,RSE.HRS_RESUME_TITLE ,%NumToChar(E.HRS_PERSON_ID) ,APPUS.MILITARY_STATUS ,CASE WHEN EXISTS ( SELECT 'X' FROM PS_HRS_APP_REF REF WHERE REF.HRS_PERSON_ID = A.HRS_PERSON_ID) THEN 'Y' ELSE 'N' END ,CASE WHEN A.HRS_SUBMIT_SOURCE = 'C' THEN PROF.HRS_SUBMITTED_DTTM WHEN A.HRS_SUBMIT_SOURCE = 'T' THEN A.HRS_ROW_ADD_DTTM ELSE PROF.HRS_SUBMITTED_DTTM END FROM PS_HRS_RCMNT A LEFT OUTER JOIN PS_HRS_EE_RF_J_VW E ON (A.HRS_JOB_OPENING_ID = E.HRS_JOB_OPENING_ID AND A.HRS_PERSON_ID = E.HRS_PERSON_ID AND A.HRS_PROFILE_SEQ = E.HRS_PROFILE_SEQ) , PS_HRS_APPLICANT APP LEFT OUTER JOIN PS_HRS_APP_USA APPUS ON (APP.HRS_PERSON_ID = APPUS.HRS_PERSON_ID ) , PS_HRS_APP_PROFILE PROF LEFT OUTER JOIN PS_HRS_RESTITLE_VW RSE ON(PROF.HRS_PERSON_ID = RSE.HRS_PERSON_ID AND PROF.HRS_RESUME_ID = RSE.HRS_RESUME_ID) , PS_HRS_STS_TBL B , PS_HRS_APP_NAMES NM WHERE APP.HRS_PERSON_ID = A.HRS_PERSON_ID AND PROF.HRS_PERSON_ID = A.HRS_PERSON_ID AND PROF.HRS_PROFILE_SEQ = A.HRS_PROFILE_SEQ AND B.STATUS_CODE = A.STATUS_CODE AND NM.HRS_PERSON_ID = A.HRS_PERSON_ID AND B.STATUS_AREA = '3' AND B.HRS_DRFT_STS_IND IN ('N', ' ') AND NM.NAME_TYPE = 'PRI' AND A.HRS_LOGICAL_DELETE = 'N' AND (APPUS.EFFDT = ( SELECT MAX(APPUS1.EFFDT) FROM PS_HRS_APP_USA APPUS1 WHERE APPUS1.HRS_PERSON_ID = APPUS.HRS_PERSON_ID) OR APPUS.EFFDT IS NULL)

  • Related Language Record: HRS_JO_APP_LN_I

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone
    2 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.

    Prompt Table: HRS_STS_JO_I

    3 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id
    4 HRS_RCMNT_ID Number(15,0) DECIMAL(15) NOT NULL App Tracking Recruitment ID
    5 HRS_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece
    6 HRS_APP_INTEREST Character(1) VARCHAR2(1) NOT NULL Applicant Interest
    1=Low
    2=Medium
    3=High
    7 ENTRY_DATE Date(10) DATE Date Entered

    Default Value: %date

    8 STATUS_REASON Character(3) VARCHAR2(3) NOT NULL Identifies a reason associated with a particular status.

    Prompt Table: HRS_RSN_JO_I

    9 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    11 HRS_STATUS_DT Date(10) DATE Recruiting Solutions Status Date field
    12 STATUS_PHASE Character(1) VARCHAR2(1) NOT NULL Identifies a phase within a status area.
    1=1-Applied
    2=2-Reviewed
    3=3-Screen
    4=4-Route
    5=5-Interview
    6=6-Offer
    7=7-Hire
    8=8-Hold
    9=9-Reject
    13 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    14 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    15 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
    16 HRS_ROW_UPD_DTTM DateTime(26) TIMESTAMP Row Updated DateTime
    17 PREV_EMPL_BY_COMPY Character(1) VARCHAR2(1) NOT NULL Previously Employed by Company
    N=No
    Y=Yes
    18 HRS_RESUME_ID Number(15,0) DECIMAL(15) NOT NULL Resume id
    19 HRS_RESUME_TITLE Character(60) VARCHAR2(60) NOT NULL Profile ID
    20 REFERRAL_ID Character(20) VARCHAR2(20) NOT NULL Referral Track ID
    21 MILITARY_STATUS Character(1) VARCHAR2(1) NOT NULL Veteran Status / Military Status
    22 HRS_REFERENCES Character(1) VARCHAR2(1) NOT NULL References
    N=No
    Y=Yes
    23 HRS_SUBMITTED_DTTM DateTime(26) TIMESTAMP Submitted Date Time