GVT_PI_DATA_VW

(SQL View)
 Index Back

Federal Payroll Interface Data

PACT Payroll Interface Information

SELECT C.EMPLID ,C.EMPL_RCD ,C.EFFDT ,C.EFFSEQ ,C.GVT_PI_UPD_IND ,D.OPRID ,NM.NAME ,NM.FIRST_NAME ,NM.LAST_NAME ,NM.MIDDLE_NAME ,A.SEX ,A.GVT_DISABILITY_CD ,A.BIRTHDATE ,J.CITIZENSHIP_STATUS ,NID.NATIONAL_ID ,A.ETHNIC_GROUP ,A.MILITARY_STATUS ,A.GVT_MIL_RESRVE_CAT ,A.GVT_MIL_SVCE_END ,A.GVT_VET_PREF_APPT ,B.SERVICE_DT ,B.CMPNY_SENIORITY_DT ,B.GVT_SCD_RETIRE ,B.GVT_SCD_TSP ,B.LAST_DATE_WORKED ,B.GVT_RTND_GRADE_EXP ,B.GVT_APPT_EXPIR_DT ,B.GVT_CNV_BEGIN_DATE ,B.GVT_APPT_LIMIT_HRS ,B.GVT_APPT_LIMIT_DYS ,B.GVT_APPT_LIMIT_AMT ,C.JOBCODE ,C.POSITION_NBR ,C.GVT_NOA_CODE ,C.GVT_LEG_AUTH_1 ,C.GVT_LEG_AUTH_2 ,C.ACTION_DT ,C.LOCATION ,C.GVT_WORK_SCHED ,C.COMPANY ,C.GVT_SUB_AGENCY ,C.PAYGROUP ,C.GVT_ELIG_FEHB ,C.STD_HOURS ,C.GVT_PAY_RATE_DETER ,C.GRADE ,C.GRADE_ENTRY_DT ,C.STEP ,C.GVT_PAY_BASIS ,C.COMPRATE ,C.GVT_XFER_FROM_AGCY ,C.GVT_XFER_TO_AGCY ,C.GVT_RETIRE_PLAN ,A.GVT_VET_PREF_RIF ,C.GVT_ANN_IND ,C.GVT_FEGLI ,B.GVT_TENURE ,C.GVT_ANNUITY_OFFSET ,C.GVT_CSRS_FROZN_SVC ,C.GVT_PREV_RET_COVRG ,A.GVT_CRED_MIL_SVCE ,C.GVT_TYPE_OF_APPT ,B.GVT_SPEP ,B.GVT_DT_LEI ,C.GVT_POI ,C.GVT_POSN_OCCUPIED ,C.GVT_CHANGE_FLAG ,C.GVT_STATUS_TYPE ,C.GVT_RTND_SAL_PLAN ,C.GVT_PAR_NTE_DATE ,C.GVT_COMPRATE ,B.HIRE_DT ,C.GVT_PAY_PLAN ,C.GVT_RTND_PAY_PLAN ,C.GVT_PAR_AUTH_D1 ,C.GVT_PAR_AUTH_D2 ,G.GVT_GEOLOC_CD FROM PS_GVT_PERS_DATA A , PS_GVT_NM_DT_VW NM ,PS_GVT_EMPLOYMENT B ,PS_GVT_JOB C ,PS_GVT_EE_DATA_TRK D ,PS_LOCATION_TBL G ,PS_GVT_PERS_NID NID ,PS_CITIZENSHIP J WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT = A.EFFDT AND C.EFFSEQ = A.EFFSEQ AND C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.EFFDT = B.EFFDT AND C.EFFSEQ = B.EFFSEQ AND C.GVT_STATUS_TYPE IN ('CAN','COR','COM') AND C.EMPLID = D.EMPLID AND C.EMPL_RCD = D.EMPL_RCD AND C.EFFDT = D.EFFDT AND C.EFFSEQ = D.EFFSEQ AND A.EMPLID = J.EMPLID AND A.COUNTRY = J.COUNTRY AND D.GVT_TRK_SEQUENCE = ( SELECT MAX(GVT_TRK_SEQUENCE) FROM PS_GVT_EE_DATA_TRK E WHERE E.EMPLID = C.EMPLID AND E.EMPL_RCD = C.EMPL_RCD AND E.EFFDT = C.EFFDT AND E.EFFSEQ = C.EFFSEQ) AND C.LOCATION = G.LOCATION AND G.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL H WHERE C.LOCATION = H.LOCATION) AND A.EMPLID = NID.EMPLID AND A.EMPL_RCD = NID.EMPL_RCD AND A.EFFDT = NID.EFFDT AND A.EFFSEQ = NID.EFFSEQ AND NM.EMPLID = A.EMPLID AND NM.EMPL_RCD = A.EMPL_RCD AND NM.EFFDT = A.EFFDT AND NM.EFFSEQ = A.EFFSEQ

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 EFFDT Date(10) DATE Effective Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 GVT_PI_UPD_IND Character(1) VARCHAR2(1) NOT NULL PI upd ind
A=Applied at NFC
I=Auto Action Inserted
N=Not Ready to Process
P=In Process
R=Ready To Process
S=SINQ Error
Z=Other
6 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
7 NAME Character(50) VARCHAR2(50) NOT NULL Name
8 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
9 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
10 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
11 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
12 GVT_DISABILITY_CD Character(2) VARCHAR2(2) NOT NULL Disability Code
13 BIRTHDATE Date(10) DATE Date of Birth
14 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status

Prompt Table: CITIZENSHIP

15 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
16 ETHNIC_GROUP Character(1) VARCHAR2(1) NOT NULL Ethnic Group
1=White
2=Black/African American
3=Hispanic/Latino
4=Asian
5=American Indian/Alaska Native
6=Not Specified
7=Native Hawaiian/Oth Pac Island
A=American Indian/Alaskan Native
B=Asian or Pacific Islander
C=Black, not of Hispanic origin
D=Hispanic
E=White, not of Hispanic origin
F=Asian Indian
G=Chinese
H=Filipino
J=Guamanian
K=Hawaiian
L=Japanese
M=Korean
N=Samoan
P=Vietnamese
Q=All Other Asian o Pacific Is
Y=Not Hispanic in Puerto Rico
17 MILITARY_STATUS Character(1) VARCHAR2(1) NOT NULL Veteran Status / Military Status
1=Not indicated
2=No Military Service
3=Veteran of the Vietnam Era
4=Active Duty/Campaign Badge Vet
4=Other Protected Veteran
5=Active Reserve
6=Inactive Reserve
7=Retired Military
8=Veteran (VA Ineligible)
9=Vietnam & Active Duty Vet
9=Vietnam & Other Protected Vet
A=Armed Forces Service Medal Vet
B=Pre-Vietnam-Era Veteran
C=Separated/Srvc Medal/Active
C=Separated/Srvc Medal/Other Vet
D=Protected, Not Classified
N=Not a Vietnam-Era Veteran
O=Service Medal & Active Duty
O=Service Medal & Other Vet
P=Post-Vietnam-Era Veteran
R=Recently Separated Veteran
S=Separated & Service Medal Vet
T=Separated & Active Duty Vet
T=Separated & Other Vet
V=Vietnam-Era Veteran
X=Not a Veteran
X=Not a Veteran
Z=Not a Protected Veteran
18 GVT_MIL_RESRVE_CAT Character(1) VARCHAR2(1) NOT NULL Reserve Category
A=IMA - Air Force
B=IMA - Army
C=IMA - Coast Guard
D=IMA - Marines
E=IMA - Navy
F=Selected Reserve - Air Force
G=Selected Reserve - Army
H=Selected Reserve - Coast Guard
I=Selected Reserve - Marine Corp
J=Selected Reserves - Navy
K=Air National Guard
L=Army National Guard
M=IRR - Air Force
N=IRR - Army
O=IRR - Coast Guard
P=IRR - Marine Corps
Q=IRR - Navy
S=Standby Reserve - Air Force
T=Standby Reserve - Army
U=Standby Reserve - Coast Guard
V=Standby Reserve - Marine Corps
W=Standby Reserve - Navy
X=Navy Reserve - Merchant Marine
Y=Draft Eligible
Z=Not Applicable
19 GVT_MIL_SVCE_END Date(10) DATE Military Service End Date
20 GVT_VET_PREF_APPT Character(1) VARCHAR2(1) NOT NULL Veterans Preference
21 SERVICE_DT Date(10) DATE Service Date
22 CMPNY_SENIORITY_DT Date(10) DATE Company Seniority Date
23 GVT_SCD_RETIRE Date(10) DATE Service Comp Date - Retire
24 GVT_SCD_TSP Date(10) DATE Service Comp Date - TSP
25 LAST_DATE_WORKED Date(10) DATE Last Date Worked
26 GVT_RTND_GRADE_EXP Date(10) DATE Retained Grade Expires
27 GVT_APPT_EXPIR_DT Date(10) DATE Appointment Expiration Date
28 GVT_CNV_BEGIN_DATE Date(10) DATE Conv Begin Date
29 GVT_APPT_LIMIT_HRS Number(4,0) SMALLINT NOT NULL Appointment Limit in Hours
30 GVT_APPT_LIMIT_DYS Number(3,0) SMALLINT NOT NULL Appointment Limit in Days
31 GVT_APPT_LIMIT_AMT Number(6,0) INTEGER NOT NULL Appointment Limit Amount
32 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
33 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
34 GVT_NOA_CODE Character(3) VARCHAR2(3) NOT NULL Nature of Action Code
35 GVT_LEG_AUTH_1 Character(3) VARCHAR2(3) NOT NULL Legal Authority (1)
36 GVT_LEG_AUTH_2 Character(3) VARCHAR2(3) NOT NULL Legal Authority (2)
37 ACTION_DT Date(10) DATE Action Date
38 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
39 GVT_WORK_SCHED Character(1) VARCHAR2(1) NOT NULL Work Schedule
B=Baylor Plan
F=Full Time
G=Full Time Seasonal
I=Intermittent
J=Intermittent-Seasonal
P=Part Time
Q=Part-Time Seasonal
S=Part-Time Job Sharer
T=Part-Time Seasonal Job Sharer
40 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
41 GVT_SUB_AGENCY Character(2) VARCHAR2(2) NOT NULL Sub-Agency
42 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
43 GVT_ELIG_FEHB Character(3) VARCHAR2(3) NOT NULL Eligible for FEHB
CC=Continuing Coverage
NE=Not Eligible
PER=Permanent
TAP=Temporary Appointment > 1 yr
TSV=Temp Appt < 1yr + FedSvc > 1yr
44 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
45 GVT_PAY_RATE_DETER Character(1) VARCHAR2(1) NOT NULL Pay Rate Determinant
0=Regular Rate
2=Saved Rate - Indefinite
3=Retained Pay - Spec Rt Adjust
4=Saved Rate - Other
5=Special & Superior Quals
6=Special Rate
7=Superior Qualifications Rate
A=Retained Grade - Diff Posn
B=Retained Grade - Same Posn
C=Critical Position Pay
E=Rtnd Grd & Spec Rt - Diff Posn
F=Rtnd Grd & Spec Rt - Same Posn
J=Retained Pay - Same Position
K=Retained Pay - Diff Posn
M=Continued IGA Pay
P=Preserved IGA Pay
R=Retained Pay - SES Removal
S=Continued SES Basic Pay
U=Rtnd Grade & Pay - Same Posn
V=Rtnd Grade & Pay - Diff Posn
46 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
47 GRADE_ENTRY_DT Date(10) DATE Grade Entry Date
48 STEP Number(2,0) SMALLINT NOT NULL Step
49 GVT_PAY_BASIS Character(2) VARCHAR2(2) NOT NULL Pay Basis
BW=Bi-weekly
FB=Fee Basis
PA=Per Annum
PD=Per Diem
PH=Per Hour
PM=Per Month
PW=Piece Work
SM=Semi-monthly
SY=School Year
WC=Without Compensation
50 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
51 GVT_XFER_FROM_AGCY Character(2) VARCHAR2(2) NOT NULL Transferred From Agency
52 GVT_XFER_TO_AGCY Character(2) VARCHAR2(2) NOT NULL Transferred To Agency
53 GVT_RETIRE_PLAN Character(2) VARCHAR2(2) NOT NULL Retirement Plan
54 GVT_VET_PREF_RIF Character(1) VARCHAR2(1) NOT NULL Veterans Preference RIF
55 GVT_ANN_IND Character(1) VARCHAR2(1) NOT NULL Annuitant Indicator
1=Reempl Ann-CS
2=Ret Officer
3=Ret Enlisted
4=Ret Off/Reempl Ann-CS
5=Ret Enl/Reempl Ann-CS
6=CS-No Reduction
7=Ret Off/CS-No Reduc
8=Ret Enl/CS-No Reduc
9=Not Applicable
A=Reempl Ann-FE
B=Former Ann-FE
C=Ret Off/Reempl Ann-FE
D=Ret Off/Former Ann-FE
E=Ret Enl/Reempl Ann-FE
F=Ret Enl/Former Ann-FE
G=FE - No Reduction
H=Ret Off/FE-No Reduc
J=Ret Enl/FE-No Reduc
56 GVT_FEGLI Character(2) VARCHAR2(2) NOT NULL FEGLI Code
57 GVT_TENURE Character(1) VARCHAR2(1) NOT NULL Highest Career Tenure
0=None
1=Permanent
2=Conditional
3=Indefinite
58 GVT_ANNUITY_OFFSET Number(8,0) INTEGER NOT NULL CSRS Annuity Offset Amount
59 GVT_CSRS_FROZN_SVC Character(4) VARCHAR2(4) NOT NULL CSRS Frozen Service
60 GVT_PREV_RET_COVRG Character(1) VARCHAR2(1) NOT NULL Previous Retirement Coverage
N=Never Covered
P=Previously Covered
61 GVT_CRED_MIL_SVCE Character(4) VARCHAR2(4) NOT NULL Creditable Military Service
62 GVT_TYPE_OF_APPT Character(2) VARCHAR2(2) NOT NULL Type of Appointment
10=Career (Competitive Svc Perm)
15=Career-Conditional (Comp Perm)
20=Nonpermanent (Comp NonPerm)
30=Schedule A (Except Svc Perm)
32=Schedule B (Except Svc Perm)
34=Schedule C (Except Svc Perm)
36=Executive (Except Svc Perm)
38=Other (Except Svc Perm)
40=Schedule A (Except NonPerm)
42=Schedule B (Except NonPerm)
44=Schedule C (Except NonPerm)
46=Executive (Except NonPerm)
48=Other (Except NonPerm)
50=Career (Senior Exec Perm)
55=Noncareer (Senior Exec Perm)
60=Limited Term (Senior Exec NPrm
65=Limited Emergency (SES NPerm)
63 GVT_SPEP Character(2) VARCHAR2(2) NOT NULL Special Employment Program
00=Not Applicable
59=Veteran's Readjustment Program
69=Presidential Mgt Intern
80=SES Candidate Development
99=Other
64 GVT_DT_LEI Date(10) DATE Date Last Equivalent Increase
65 GVT_POI Character(4) VARCHAR2(4) NOT NULL Personnel Office ID
66 GVT_POSN_OCCUPIED Character(1) VARCHAR2(1) NOT NULL Position Occupied
1=Competitive
2=Excepted
3=SES General
4=SES Career Reserved
67 GVT_CHANGE_FLAG Character(1) VARCHAR2(1) NOT NULL Change Flag
68 GVT_STATUS_TYPE Character(3) VARCHAR2(3) NOT NULL Status Type
CAN=Cancelled
COM=Completed
COR=Corrected
IRR=IRR Reported
WIP=Work-in-progress
69 GVT_RTND_SAL_PLAN Character(4) VARCHAR2(4) NOT NULL Retained Pay Table
70 GVT_PAR_NTE_DATE Date(10) DATE Not To Exceed Date
71 GVT_COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Base Pay
72 HIRE_DT Date(10) DATE First Start Date
73 GVT_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Pay Plan
74 GVT_RTND_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Retained Pay Plan
75 GVT_PAR_AUTH_D1 Character(25) VARCHAR2(25) NOT NULL Authority 1 Descr - Part 1
76 GVT_PAR_AUTH_D2 Character(25) VARCHAR2(25) NOT NULL Authority 2 Descr - Part 1
77 GVT_GEOLOC_CD Character(9) VARCHAR2(9) NOT NULL Geog Location Code