HRS_APP_NTF_VW

(SQL View)
 Index Back

Intvw Schedule Notifications

Candidate Experience for the Applicant Homepage

SELECT A.HRS_PERSON_ID , 0 , A.HRS_JOB_OPENING_ID , ' ' , 'I' , CASE WHEN B.HRS_PERSON_ID IS NOT NULL THEN 'Y' ELSE 'N' END , A.HRS_ROW_UPD_DTTM , ' ' , 'I,' || A.HRS_RCMNT_ID || ',' || A.HRS_RCM_INT_ID || ',' || A.HRS_JOB_OPENING_ID FROM PS_HRS_APP_INT_NTF A LEFT OUTER JOIN PS_HRS_NTF_INT_VW B ON A.HRS_PERSON_ID = B.HRS_PERSON_ID AND A.HRS_RCMNT_ID = B.HRS_RCMNT_ID AND A.HRS_RCM_INT_ID = B.HRS_RCM_INT_ID WHERE A.HRS_LOGICAL_DELETE = 'N' AND A.STATUS_CODE <> '130' UNION SELECT C.HRS_PERSON_ID , 0 , 0 , ' ' , 'R' , CASE WHEN D.HRS_PERSON_ID IS NOT NULL THEN 'Y' ELSE 'N' END , C.HRS_ROW_ADD_DTTM , ' ' ,'R,' || C.HRS_CRSP_ID FROM PS_HRS_APP_RFCR_VW C LEFT OUTER JOIN PS_HRS_NTF_REF_VW D ON C.HRS_PERSON_ID = D.HRS_PERSON_ID AND C.HRS_CRSP_ID = D.HRS_CRSP_ID WHERE C.HRS_CRSP_TYPE='R' UNION SELECT E.HRS_PERSON_ID , 0 , 0 , E.SEARCH_NAME , 'J' , CASE WHEN F.HRS_PERSON_ID IS NOT NULL THEN 'Y' ELSE 'N' END , E.HRS_ROW_UPD_DTTM , ' ' , 'J,' || E.SEARCH_NAME FROM PS_HRS_AGNT_NOTI_I E LEFT OUTER JOIN PS_HRS_NTF_SCH_VW F ON E.HRS_PERSON_ID = F.HRS_PERSON_ID AND E.SEARCH_NAME = F.SEARCH_NAME UNION SELECT G.HRS_PERSON_ID , 0 , G.HRS_JOB_OPENING_ID , ' ' , 'O' , CASE WHEN H.HRS_PERSON_ID IS NOT NULL THEN 'Y' ELSE 'N' END , G.HRS_ROW_UPD_DTTM , G.HRS_PRM_PST_TITLE , 'O,' || G.HRS_RCMNT_ID || ',' || G.HRS_JOB_OPENING_ID || ',' || G.HRS_OFF_ID FROM PS_HRS_ONLOFR_CE_I G LEFT OUTER JOIN PS_HRS_NTF_OFF_VW H ON G.HRS_PERSON_ID = H.HRS_PERSON_ID AND G.HRS_RCMNT_ID = H.HRS_RCMNT_ID AND G.HRS_OFF_ID = H.HRS_OFF_ID WHERE G.HRS_JO_NTF_DELETED <> 'Y' AND G.HRS_LOGICAL_DELETE = 'N' AND G.STATUS_CODE <> '130' UNION SELECT I.HRS_PERSON_ID , I.HRS_SITE_ID , 0 , ' ' , 'S' , CASE WHEN I.HRS_CCE_NOTIF_IND = 'Y' THEN 'N' ELSE 'Y' END , I.HRS_ROW_ADD_DTTM , ' ' , 'S,' || I.HRS_SITE_ID FROM PS_HRS_APP_SITE I WHERE I.HRS_CCE_NOTIF_IND NOT IN ('D','I') UNION ALL SELECT J.HRS_PERSON_ID , J.HRS_SITE_ID , 0 , ' ' , 'B' , CASE WHEN J.HRS_CCE_NOTIF_IND = 'Y' THEN 'N' ELSE 'Y' END , J.HRS_ROW_ADD_DTTM , ' ' , 'B,' || J.HRS_SITE_ID || ',' || J.HRS_RCMNT_ID FROM PS_HRS_UNSUB_NOTIF J WHERE J.HRS_CCE_NOTIF_IND NOT IN ('D','I')

  • Parent record: HRS_RCM_INT

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id

    Prompt Table: HRS_APPLICANT_I

    2 HRS_SITE_ID Number(15,0) DECIMAL(15) NOT NULL Site ID
    3 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone
    4 SEARCH_NAME Character(20) VARCHAR2(20) NOT NULL Stored Search Name
    5 HRS_NOTIF_TYPE Character(1) VARCHAR2(1) NOT NULL Notification Type
    B=Unsubmitted Jobs Notification
    I=Interviews
    J=Job agent
    L=Link Applicant
    O=Job Offer
    R=References
    S=Registration
    6 HRS_VIEW_STATUS Character(1) VARCHAR2(1) NOT NULL viewed status of CG notifications
    N=New
    Y=Viewed
    7 HRS_ROW_ADD_DTTM DateTime(26) TIMESTAMP Row Added DateTime
    8 HRS_DFLT_SUBJ Character(254) VARCHAR2(254) NOT NULL Subject
    9 URL Character(254) VARCHAR2(254) NOT NULL Internet URL (Universal Resource Locator)