HRS_G_APPRKL_VW (SQL View)	Index Back
Cert of Eligibles report view View for crystal report FGREC821, Certificate of Eligibles

HRS_G_APPRKL_VW

(SQL View)

Cert of Eligibles report view

View for crystal report FGREC821, Certificate of Eligibles

SELECT E.OPRID , A.HRS_PERSON_ID , A.JOBCODE , C.STATUS_CODE , X.DESCR , A.STATUS_REASON , A.HRS_STATUS_DT , A.GVT_APPL_COMP_STS , A.GVT_RQSN_CERT_NBR , A.GVT_APPL_VP_SCORE , A.GVT_VET_RECR_CODE , B.GVT_ORG_TTL_DESCR , B.GVT_PAY_PLAN , B.GRADE , B.GVT_OCC_SERIES , A.GVT_SORT_ORDER , C.HRS_JOB_OPENING_ID , C.HRS_G_JO_TYPE , D.NAME_DISPLAY , %Coalesce((select F.NATIONAL_ID from PS_HRS_APP_NID F WHERE F.HRS_PERSON_ID = A.HRS_PERSON_ID and F.COUNTRY = 'USA' and F.NATIONAL_ID_TYPE = 'PR'), ' ') , A.HRS_SCR_TTL_SCORE FROM PS_HRS_RCMNT_JOB A , PS_JOBCODE_TBL B , PS_HRS_JO_I C , PS_HRS_APP_NAME_I D , PS_HRS_APP_SEC_VW E , PS_HRS_RCMNT J , PS_HRS_STS_TBL X , PS_HRS_JO_RQMT_VW W WHERE A.JOBCODE = B.JOBCODE AND B.SETID = ( SELECT G.SETID FROM PS_SET_CNTRL_GROUP G WHERE G.SETCNTRLVALUE = C.BUSINESS_UNIT AND G.REC_GROUP_ID = ( SELECT H.REC_GROUP_ID FROM PS_REC_GROUP_REC H WHERE H.RECNAME = 'JOBCODE_TBL')) AND B.EFFDT = ( SELECT MAX(I.EFFDT) FROM PS_JOBCODE_TBL I WHERE I.SETID = B.SETID AND I.JOBCODE = B.JOBCODE AND I.EFFDT <= %CurrentDateIn) AND D.HRS_PERSON_ID = A.HRS_PERSON_ID AND E.HRS_PERSON_ID = A.HRS_PERSON_ID AND J.HRS_PERSON_ID = A.HRS_PERSON_ID AND J.HRS_RCMNT_ID = A.HRS_RCMNT_ID AND J.HRS_JOB_OPENING_ID = C.HRS_JOB_OPENING_ID AND X.STATUS_AREA = '1' AND X.STATUS_CODE = C.STATUS_CODE AND A.JOBCODE = W.JOBCODE AND C. HRS_JOB_OPENING_ID = W.HRS_JOB_OPENING_ID AND A.GVT_APPL_RANK <= W.GVT_NUM_APPS_CONS

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id

3 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

4 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.

5 DESCR Character(30) VARCHAR2(30) NOT NULL Description

6 STATUS_REASON Character(3) VARCHAR2(3) NOT NULL Identifies a reason associated with a particular status.

7 HRS_STATUS_DT Date(10) DATE Recruiting Solutions Status Date field

8 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

9 GVT_RQSN_CERT_NBR Character(10) VARCHAR2(10) NOT NULL Certificate of Eligibles Nbr

10 GVT_APPL_VP_SCORE Signed Number(3,0) DECIMAL(2) NOT NULL Veterans Preference Points

11 GVT_VET_RECR_CODE Character(3) VARCHAR2(3) NOT NULL Recruitment Code
1=CPS
2=CP
3=XP
4=TP
5=NV

12 GVT_ORG_TTL_DESCR Character(70) VARCHAR2(70) NOT NULL Organization Position Title

13 GVT_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Pay Plan

14 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade

15 GVT_OCC_SERIES Character(4) VARCHAR2(4) NOT NULL Occupational Series

16 GVT_SORT_ORDER Number(14,10) DECIMAL(13,10) NOT NULL Sort Order Field

17 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone

18 HRS_G_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Federal job requisition type
I=Internal
M=Merit Promotion
O=Open Competition

19 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

20 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID

21 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.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	HRS_PERSON_ID	Number(15,0)	DECIMAL(15) NOT NULL	Person Id
3	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
4	STATUS_CODE	Character(3)	VARCHAR2(3) NOT NULL	Identifies a status.
5	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
6	STATUS_REASON	Character(3)	VARCHAR2(3) NOT NULL	Identifies a reason associated with a particular status.
7	HRS_STATUS_DT	Date(10)	DATE	Recruiting Solutions Status Date field
8	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
9	GVT_RQSN_CERT_NBR	Character(10)	VARCHAR2(10) NOT NULL	Certificate of Eligibles Nbr
10	GVT_APPL_VP_SCORE	Signed Number(3,0)	DECIMAL(2) NOT NULL	Veterans Preference Points
11	GVT_VET_RECR_CODE	Character(3)	VARCHAR2(3) NOT NULL	Recruitment Code 1=CPS 2=CP 3=XP 4=TP 5=NV
12	GVT_ORG_TTL_DESCR	Character(70)	VARCHAR2(70) NOT NULL	Organization Position Title
13	GVT_PAY_PLAN	Character(2)	VARCHAR2(2) NOT NULL	Pay Plan
14	GRADE	Character(3)	VARCHAR2(3) NOT NULL	Salary Grade
15	GVT_OCC_SERIES	Character(4)	VARCHAR2(4) NOT NULL	Occupational Series
16	GVT_SORT_ORDER	Number(14,10)	DECIMAL(13,10) NOT NULL	Sort Order Field
17	HRS_JOB_OPENING_ID	Number(15,0)	DECIMAL(15) NOT NULL	Job Opening Clone
18	HRS_G_JO_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Federal job requisition type I=Internal M=Merit Promotion O=Open Competition
19	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
20	NATIONAL_ID	Character(20)	VARCHAR2(20) NOT NULL	National ID
21	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.