HRS_PERSON_IDHRS_RCMNT_IDHRS_APPNOANS_VW(SQL View) |
Index Back |
|---|---|
Applicants No Ans to QuestionsVIew to list applicants who have applied to JO with questions but have not yet submitted any answer. |
| SELECT DISTINCT RCT.HRS_PERSON_ID , RCT.HRS_RCMNT_ID , RCT.HRS_JOB_OPENING_ID , RCT.STATUS_CODE , RCT.STATUS_REASON , RCT.HRS_STATUS_DT , OPN.ORIGINATOR_ID , OPN.MANAGER_ID , OPN.RECRUITER_ID , TEA.EMPLID , OPN.HRS_PRM_PST_TITLE POSTING_TITLE , NAM.NAME_DISPLAY , RCT.HRS_ROW_ADD_DTTM FROM PS_HRS_RCMNT RCT , PS_HRS_APP_NAMES NAM , PS_HRS_JOB_OPENING OPN , PS_HRS_JO_TEAM TEA WHERE RCT.HRS_PERSON_ID = NAM.HRS_PERSON_ID AND RCT.HRS_JOB_OPENING_ID = TEA.HRS_JOB_OPENING_ID AND RCT.HRS_JOB_OPENING_ID = OPN.HRS_JOB_OPENING_ID AND OPN.STATUS_CODE NOT IN ('100', '110', '120') AND (RCT.STATUS_CODE NOT IN ('005','110','120','130','140') ) AND rct.HRS_SUBMIT_SOURCE = 'T' AND RCT.HRS_JOB_OPENING_ID IN ( SELECT HRS_JOB_OPENING_ID FROM PS_HRS_JO_QSTN QST) AND RCT.HRS_PERSON_ID IN ( SELECT HRS_PERSON_ID FROM PS_HRS_APPLICANT WHERE STATUS_CODE NOT IN ('030')) AND (NOT EXISTS ( SELECT 1 FROM PS_HRS_APP_ANSWER ANS WHERE ANS.HRS_PERSON_ID = RCT.HRS_PERSON_ID AND ANS.HRS_JOB_OPENING_ID = RCT.HRS_JOB_OPENING_ID ) OR (EXISTS ( SELECT 1 FROM PS_HRS_APP_ANSWER ANS WHERE ANS.HRS_PERSON_ID = RCT.HRS_PERSON_ID AND ANS.HRS_ANSWER_ID = 0 AND ANS.HRS_JOB_OPENING_ID = RCT.HRS_JOB_OPENING_ID ) )) |
| # | 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_JOB_OPENING_ID | Number(15,0) | DECIMAL(15) NOT NULL | Job Opening Clone |
| 4 | STATUS_CODE | Character(3) | VARCHAR2(3) NOT NULL | Identifies a status. |
| 5 | STATUS_REASON | Character(3) | VARCHAR2(3) NOT NULL | Identifies a reason associated with a particular status. |
| 6 | HRS_STATUS_DT | Date(10) | DATE | Recruiting Solutions Status Date field |
| 7 | ORIGINATOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Originator's ID |
| 8 | MANAGER_ID | Character(11) | VARCHAR2(11) NOT NULL | Manager ID |
| 9 | RECRUITER_ID | Character(11) | VARCHAR2(11) NOT NULL | Recruiter ID |
| 10 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 11 | POSTING_TITLE | Character(200) | VARCHAR2(200) NOT NULL | Job posting title |
| 12 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 13 | HRS_ROW_ADD_DTTM | DateTime(26) | TIMESTAMP | Row Added DateTime |