|
SELECT DISTINCT RCMN.HRS_PERSON_ID , RCMN.HRS_RCMNT_ID , RCMN.HRS_PROFILE_SEQ , RCMN.HRS_JOB_OPENING_ID , RCMN.STATUS_CODE , RCMN.STATUS_REASON , RCMN.HRS_STATUS_DT , RCMN.HRS_JO_NTF_DELETED , RCMN.HRS_LOGICAL_DELETE , %subrec(HRS_SCR_SBR,RCMN) , %subrec(HRS_AUDIT_SBR,RCMN) FROM PS_HRS_RCMNT_I RCMN , PS_HRS_APP_PROFILE PROF /* Bug 35294655 changes begin */ /* , PS_HRS_JO_QSTN QST */ , PS_HRS_JO_QST_OR_I QST /* Bug 35294655 changes end */ , PS_HRS_JOB_OPENING JO , PS_HRS_RCRT_TMPL RCRT , PS_HRS_RES_SECTION RTS WHERE RCMN.HRS_PERSON_ID = PROF.HRS_PERSON_ID AND RCMN.HRS_PROFILE_SEQ = PROF.HRS_PROFILE_SEQ AND RCMN.HRS_JOB_OPENING_ID = QST.HRS_JOB_OPENING_ID AND RCMN.HRS_PERSON_ID NOT IN ( SELECT ANSWR.HRS_PERSON_ID FROM PS_HRS_APP_ANSWER ANSWR WHERE RCMN.HRS_PERSON_ID = ANSWR.HRS_PERSON_ID AND RCMN.HRS_JOB_OPENING_ID = ANSWR.HRS_JOB_OPENING_ID) AND RCMN.HRS_PERSON_ID NOT IN ( SELECT OANSWR.HRS_PERSON_ID FROM PS_HRS_APP_OPN_ANS OANSWR WHERE RCMN.HRS_PERSON_ID = OANSWR.HRS_PERSON_ID AND RCMN.HRS_JOB_OPENING_ID = OANSWR.HRS_JOB_OPENING_ID) AND RCMN.HRS_JOB_OPENING_ID = JO.HRS_JOB_OPENING_ID AND JO.HRS_RCRT_TMPL_ID = RCRT.HRS_RCRT_TMPL_ID AND RCRT.HRS_RES_TMPL_ID = RTS.HRS_RES_TMPL_ID /* Bug 35294655 changes begin */ /* AND RTS.HRS_RES_SEC_NAME12 = 'QUE' */ AND RTS.HRS_RES_SEC_NAME12 IN ('QUE','EMP') AND RCMN.HRS_PERSON_ID NOT IN ( SELECT RCMN1.HRS_PERSON_ID FROM PS_HRS_RCMNT RCMN1 WHERE RCMN1.HRS_PERSON_ID = RCMN.HRS_PERSON_ID AND RCMN1.HRS_JOB_OPENING_ID = RCMN.HRS_JOB_OPENING_ID AND RCMN1.HRS_SUBMIT_SOURCE not in ('T','C'))/* Bug 35294655 changes end */
|
| # |
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_PROFILE_SEQ |
Number(3,0) |
SMALLINT NOT NULL |
Profile sequece
|
| 4 |
HRS_JOB_OPENING_ID |
Number(15,0) |
DECIMAL(15) NOT NULL |
Job Opening Clone
Prompt Table:
HRS_JO_OPEN_I
|
| 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 |
HRS_STATUS_DT |
Date(10) |
DATE |
Recruiting Solutions Status Date field
Default Value: %date
|
| 8 |
HRS_JO_NTF_DELETED |
Character(1) |
VARCHAR2(1) NOT NULL |
Job Offer Notification Deletion Indicator
Default Value: N
|
| 9 |
HRS_LOGICAL_DELETE |
Character(1) |
VARCHAR2(1) NOT NULL |
Indicates row has been logically deleted.
|
| 10 |
GVT_APPL_COMP_STS |
Character(1) |
VARCHAR2(1) NOT NULL |
Applicant Competitive Status
0=Mark...
C=Competitive
N=Non-Competitive VRA
O=Non-Competitive Other
|
| 11 |
GVT_RQSN_CERT_NBR |
Character(10) |
VARCHAR2(10) NOT NULL |
Certificate of Eligibles Nbr
|
| 12 |
GVT_QUALS_DETERM |
Character(1) |
VARCHAR2(1) NOT NULL |
Qualifications Determination Flag
B=Best Qualified
D=Does Not Meet Min Quals
N=Eligible/Not Rated
Q=Qualified
W=Well Qualified
|
| 13 |
GVT_QUALS_DET_DATE |
Date(10) |
DATE |
Qualifications Determ Date
|
| 14 |
GVT_MIN_QUAL_SCORE |
Number(5,1) |
DECIMAL(4,1) NOT NULL |
Minimum Quals Score
|
| 15 |
GVT_APPL_AP_SCORE |
Signed Number(6,1) |
DECIMAL(4,1) NOT NULL |
Augmentation Points for Applicants
|
| 16 |
GVT_APPL_VP_SCORE |
Signed Number(3,0) |
DECIMAL(2) NOT NULL |
Veterans Preference Points
|
| 17 |
HRS_APP_SEL_SCORE |
Signed Number(6,1) |
DECIMAL(4,1) NOT NULL |
Selective factor points earned by an applicant.
|
| 18 |
HRS_SCR_SCORE |
Signed Number(6,1) |
DECIMAL(4,1) NOT NULL |
Screening score. Includes all points awarded to an applicant for a screening level, less veterans preference points, augmenting points, and selective factor points.
|
| 19 |
HRS_SCR_TTL_SCORE |
Signed Number(6,1) |
DECIMAL(4,1) NOT NULL |
Screening score. Includes all points awarded to an applicant for a screening level, INCLUDING veterans preference points, augmenting factor points, and selective factor points.
|
| 20 |
GVT_RANK_CATEGORY |
Character(1) |
VARCHAR2(1) NOT NULL |
Ranking Category
1=Category 1
2=Category 2
3=Category 3
4=Category 4
|
| 21 |
GVT_RANK_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Ranking Status
N=Do Not Rank
R=Rank
Default Value: N
|
| 22 |
GVT_APPL_RANK |
Number(3,0) |
SMALLINT NOT NULL |
Rank on Certificate
|
| 23 |
GVT_VET_RECR_CODE |
Character(3) |
VARCHAR2(3) NOT NULL |
Recruitment Code
1=CPS
2=CP
3=XP
4=TP
5=NV
|
| 24 |
GVT_SORT_ORDER |
Number(14,10) |
DECIMAL(13,10) NOT NULL |
Sort Order Field
|
| 25 |
GVT_PRIO_WEIGHT |
Number(3,0) |
SMALLINT NOT NULL |
Priority Placement Weight
|
| 26 |
GVT_PRIO_PLCMNT_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Priority Placement Code
Prompt Table:
HRS_G_PR_PL_TBL
|
| 27 |
GVT_RQSN_SEL_CRITE |
Character(1) |
VARCHAR2(1) NOT NULL |
Job Opening Selection Criteria
1=None
2=Must Consider
3=Must Select
|
| 28 |
HRS_ROW_ADD_DTTM |
DateTime(26) |
TIMESTAMP |
Row Added DateTime
|
| 29 |
HRS_ROW_ADD_OPRID |
Character(30) |
VARCHAR2(30) NOT NULL |
Row Added Operator Id
|
| 30 |
HRS_ROW_UPD_DTTM |
DateTime(26) |
TIMESTAMP |
Row Updated DateTime
|
| 31 |
HRS_ROW_UPD_OPRID |
Character(30) |
VARCHAR2(30) NOT NULL |
Row Updated Operator Id
|