HRS_MY_APP_VW

(SQL View)
 Index Back

My Applicants

This view is used by the My Applicants pagelet to return a list of applicants that have been linked to job openings.

SELECT DISTINCT A.HRS_PERSON_ID , B.HRS_RCMNT_ID , TM.EMPLID , D.HRS_PROFILE_SEQ , B.HRS_ROW_UPD_DTTM , C.NAME_DISPLAY , A.APP_PER_STATUS , A.APP_PER_STATUS , A.STATUS_CODE , A.HRS_APP_PREF_CONT , F.EMAIL_ADDR , %subrec(PHONE_SBR, G) , B.HRS_JOB_OPENING_ID , E.HRS_PRM_PST_TITLE , B.STATUS_CODE , D.HRS_RESUME_ID FROM PS_HRS_APPLICANT A LEFT OUTER JOIN PS_HRS_APP_EMAIL F ON (F.HRS_PERSON_ID = A.HRS_PERSON_ID AND F.PREF_EMAIL_FLAG = 'Y') LEFT OUTER JOIN PS_HRS_APP_PHONE G ON (G.HRS_PERSON_ID = A.HRS_PERSON_ID AND G.PREF_PHONE_FLAG = 'Y') , PS_HRS_RCMNT B , PS_HRS_APP_NAMES C , PS_HRS_APP_PROFILE D , PS_HRS_JOB_OPENING E , PS_HRS_JO_TEAM TM WHERE B.HRS_PERSON_ID = A.HRS_PERSON_ID AND C.HRS_PERSON_ID = B.HRS_PERSON_ID AND D.HRS_PERSON_ID = B.HRS_PERSON_ID AND D.HRS_PROFILE_SEQ = B.HRS_PROFILE_SEQ AND E.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND TM.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND C.NAME_TYPE = 'PRI' AND B.STATUS_CODE IN ( SELECT H.STATUS_CODE FROM PS_HRS_STS_TBL H WHERE H.STATUS_AREA = '3' AND H.STATUS_PHASE < '9' AND H.HRS_DRFT_STS_IND IN ('N', ' ')) AND E.STATUS_CODE IN ( SELECT I.STATUS_CODE FROM PS_HRS_STS_TBL I WHERE I.STATUS_AREA = '1' AND I.OPEN_STS_IND = 'Y') AND B.HRS_RCMNT_ID = ( SELECT MAX(X.HRS_RCMNT_ID) FROM PS_HRS_RCMNT X WHERE X.HRS_PERSON_ID = B.HRS_PERSON_ID AND X.HRS_JOB_OPENING_ID IN ( SELECT Y.HRS_JOB_OPENING_ID FROM PS_HRS_JOB_OPENING Y , PS_HRS_JO_TEAM TM2 WHERE Y.HRS_JOB_OPENING_ID = X.HRS_JOB_OPENING_ID AND TM2.HRS_JOB_OPENING_ID = Y.HRS_JOB_OPENING_ID AND TM2.EMPLID = TM.EMPLID AND Y.STATUS_CODE IN ( SELECT Z.STATUS_CODE FROM PS_HRS_STS_TBL Z WHERE Z.STATUS_AREA = '1' AND Z.OPEN_STS_IND = 'Y')) AND X.STATUS_CODE IN ( SELECT J.STATUS_CODE FROM PS_HRS_STS_TBL J WHERE J.STATUS_AREA = '3' AND J.STATUS_PHASE < '9' AND J.HRS_DRFT_STS_IND IN ('N', ' ')))

  • Related Language Record: HRS_MY_APPVWLNG

    • # 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 HRS_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece
    5 HRS_SUBMITTED_DTTM DateTime(26) TIMESTAMP Submitted Date Time
    6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    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 APP_PER_STATUS1 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
    9 HRS_STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Applicant Status
    10 HRS_APP_PREF_CONT Character(1) VARCHAR2(1) NOT NULL Preferred Method of Contact
    E=Email
    M=Mail
    N=Not Specified
    P=Phone

    Default Value: N

    11 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address
    12 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
    13 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
    14 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone
    15 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    16 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.

    Prompt Table: HRS_STS_REC_I

    17 HRS_RESUME_ID Number(15,0) DECIMAL(15) NOT NULL Resume id