HRS_OFFPREP_VW(SQL View) |
Index Back |
|---|---|
Positions ready for OfferRetrieves a list of position applied fors that are ready to have an offer made. |
| SELECT A.HRS_PERSON_ID ,A.HRS_RCMNT_ID ,A.HRS_JOB_OPENING_ID , J.OPRID ,A.STATUS_CODE ,A.STATUS_REASON ,A.HRS_STATUS_DT ,A.ENTRY_SOURCE ,A.ENTRY_DATE ,A.HRS_DES_LOC1 ,A.HRS_DES_LOC2 ,A.DESIRED_REG_TEMP ,A.DESIRED_FULL_PART ,A.DESIRED_START_DT ,A.DESIRED_HOURS ,A.TRAVELLING ,A.HRS_TRAVEL_PERCENT ,A.HIGHEST_EDUC_LVL ,A.YEARS_OF_EXP ,A.KEYWORD_MATCH ,J.ORIGINATOR_ID ,J.RECRUITER_ID , J.POSTING_TITLE , K.NAME_DISPLAY FROM PS_HRS_PSN_APP_VW A , PS_HRS_JO_OPEN_I J , PS_HRS_APP_NAME_I K WHERE A.OPEN_STS_IND = 'Y' AND A.HRS_JOB_OPENING_ID = J.HRS_JOB_OPENING_ID AND A.HRS_PERSON_ID = K.HRS_PERSON_ID AND (A.STATUS_PHASE = '5' AND EXISTS ( SELECT 'X' FROM PS_HRS_RCM_INT I , PS_HRS_STS_INT_I S WHERE A.HRS_PERSON_ID = I.HRS_PERSON_ID AND A.HRS_RCMNT_ID = I.HRS_RCMNT_ID AND I.HRS_INT_STTS = S.STATUS_CODE AND S.COMPLETE_STS_IND = 'Y') OR EXISTS ( SELECT 'X' FROM PS_HRS_OFFER_MAX_I B , PS_HRS_STS_OFF_I C WHERE A.HRS_PERSON_ID = B.HRS_PERSON_ID AND A.HRS_RCMNT_ID = B.HRS_RCMNT_ID AND B.STATUS_CODE = C.STATUS_CODE AND C.OPEN_STS_IND <> 'Y' AND C.COMPLETE_STS_IND <> 'Y' AND C.HRS_APRV_STS_IND <> 'Y' AND C.HRS_DRFT_STS_IND <> 'Y') OR (A.STATUS_PHASE = '6' AND NOT EXISTS ( SELECT 'X' FROM PS_HRS_OFFER O WHERE A.HRS_PERSON_ID = O.HRS_PERSON_ID AND A.HRS_RCMNT_ID = O.HRS_RCMNT_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 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 5 | STATUS_CODE | Character(3) | VARCHAR2(3) NOT NULL |
Identifies a status.
Prompt Table: HRS_STS_REC_I |
| 6 | STATUS_REASON | Character(3) | VARCHAR2(3) NOT NULL |
Identifies a reason associated with a particular status.
Prompt Table: HRS_RSN_REC_I |
| 7 | STATUS_DT | Date(10) | DATE | Status Date |
| 8 | ENTRY_SOURCE | Character(1) | VARCHAR2(1) NOT NULL |
Entry Source
A=Application O=Organization |
| 9 | ENTRY_DATE | Date(10) | DATE | Date Entered |
| 10 | HRS_DES_LOC1 | Number(15,0) | DECIMAL(15) NOT NULL |
Desired Location 1
Prompt Table: HRS_LOC_I |
| 11 | HRS_DES_LOC2 | Number(15,0) | DECIMAL(15) NOT NULL |
Desired Location 2
Prompt Table: HRS_LOC_I |
| 12 | DESIRED_REG_TEMP | Character(1) | VARCHAR2(1) NOT NULL |
Regular/Temporary
E=Either R=Regular T=Temporary |
| 13 | DESIRED_FULL_PART | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part-Time
E=Either F=Full-Time P=Part-Time |
| 14 | DESIRED_START_DT | Date(10) | DATE | Desired Start Date |
| 15 | DESIRED_HOURS | Number(4,1) | DECIMAL(3,1) NOT NULL | Desired Hours Per Week |
| 16 | TRAVELLING | Character(1) | VARCHAR2(1) NOT NULL |
Willing to Travel
Y/N Table Edit Default Value: N |
| 17 | HRS_TRAVEL_PERCENT | Character(1) | VARCHAR2(1) NOT NULL |
Travel percent
0=None 1=<= 25% 2=26 - 50 % 3=51 - 75 % 4=76 - 100 % |
| 18 | HIGHEST_EDUC_LVL | Character(2) | VARCHAR2(2) NOT NULL |
Highest Education Level
Prompt Table: HIGH_EDUCLVL_VW |
| 19 | YEARS_OF_EXP | Number(5,1) | DECIMAL(4,1) NOT NULL | Years of Work Experience |
| 20 | KEYWORD_MATCH | Character(1) | VARCHAR2(1) NOT NULL | Keywords Matched |
| 21 | ORIGINATOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Originator's ID |
| 22 | RECRUITER_ID | Character(11) | VARCHAR2(11) NOT NULL | Recruiter ID |
| 23 | POSTING_TITLE | Character(200) | VARCHAR2(200) NOT NULL | Job posting title |
| 24 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |