|
SELECT A.EMPLID , A.EMPL_RCD , A.BENEFIT_RCD_NBR , A.HOME_HOST_CLASS , A.HIRE_DT , A.REHIRE_DT , A.CMPNY_SENIORITY_DT , A.SERVICE_DT , A.PROF_EXPERIENCE_DT , A.LAST_VERIFICATN_DT , A.EXPECTED_RETURN_DT , A.TERMINATION_DT , A.LAST_DATE_WORKED , A.LAST_INCREASE_DT , A.OWN_5PERCENT_CO , A.BUSINESS_TITLE , A.PROBATION_DT , A.POSITION_PHONE , A.DED_TAKEN , A.DED_SUBSET_ID , %subrec(EMPL_FED_SBR,A) , A.TENURE_ACCR_FLG , A.FTE_TENURE , A.EG_GROUP , A.FTE_FLX_SRVC , A.CONTRACT_LENGTH , A.APPOINT_END_DT , A.NEE_PROVIDER_ID FROM PS_GVT_EMPLOYMENT A , PS_GVT_JOB B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND B.GVT_STATUS_TYPE IN ('COR', 'COM') AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_GVT_JOB D WHERE D.EMPLID = B.EMPLID AND D.EMPL_RCD = B.EMPL_RCD AND D.EFFDT <= %CurrentDateIn AND D.GVT_STATUS_TYPE IN ('COR', 'COM')) AND B.EFFSEQ = ( SELECT MAX(C.EFFSEQ) FROM PS_GVT_JOB C WHERE C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.EFFDT = B.EFFDT AND C.GVT_STATUS_TYPE IN ('COM', 'COR'))
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 EMPLID |
Character(11) |
VARCHAR2(11) NOT NULL |
Employee ID
Default Value: PERSON.EMPLID
Prompt Table:
PERSON
|
| 2 |
EMPL_RCD |
Number(3,0) |
SMALLINT NOT NULL |
Empl Record
|
| 3 |
BENEFIT_RCD_NBR |
Number(3,0) |
SMALLINT NOT NULL |
Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
|
| 4 |
HOME_HOST_CLASS |
Character(1) |
VARCHAR2(1) NOT NULL |
Home/Host Classification
M=Home
S=Host
|
| 5 |
HIRE_DT |
Date(10) |
DATE |
First Start Date
|
| 6 |
REHIRE_DT |
Date(10) |
DATE |
Rehire Date
|
| 7 |
CMPNY_SENIORITY_DT |
Date(10) |
DATE |
Company Seniority Date
|
| 8 |
SERVICE_DT |
Date(10) |
DATE |
Service Date
|
| 9 |
PROF_EXPERIENCE_DT |
Date(10) |
DATE |
Professional Experience Date
|
| 10 |
LAST_VERIFICATN_DT |
Date(10) |
DATE |
Last Verification Date
|
| 11 |
EXPECTED_RETURN_DT |
Date(10) |
DATE |
Expected Return Date
|
| 12 |
TERMINATION_DT |
Date(10) |
DATE |
Termination Date
|
| 13 |
LAST_DATE_WORKED |
Date(10) |
DATE |
Last Date Worked
|
| 14 |
LAST_INCREASE_DT |
Date(10) |
DATE |
Date Last Increase
|
| 15 |
OWN_5PERCENT_CO |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates whether or not (Y or N) an employee owns 5% or more of the company. This value is used in nondiscrimination testing.
Y/N Table Edit
Default Value: N
|
| 16 |
BUSINESS_TITLE |
Character(30) |
VARCHAR2(30) NOT NULL |
Business Title
|
| 17 |
PROBATION_DT |
Date(10) |
DATE |
Probation Date
|
| 18 |
POSITION_PHONE |
Character(24) |
VARCHAR2(24) NOT NULL |
Phone number for the Position
|
| 19 |
DED_TAKEN |
Character(1) |
VARCHAR2(1) NOT NULL |
Deductions Taken
D=Deduction Table Governs
N=None
R=No Override
S=Deduction Table Subset Governs
Default Value: R
|
| 20 |
DED_SUBSET_ID |
Character(3) |
VARCHAR2(3) NOT NULL |
Deduction Subset ID
Prompt Table:
DED_SUBSET_TBL
|
| 21 |
GVT_SCD_RETIRE |
Date(10) |
DATE |
Service Comp Date - Retire
|
| 22 |
GVT_SCD_TSP |
Date(10) |
DATE |
Service Comp Date - TSP
|
| 23 |
GVT_SCD_LEO |
Date(10) |
DATE |
Service Comp Date - LEO
|
| 24 |
GVT_SCD_SEVPAY |
Date(10) |
DATE |
Service Comp Date - Sev Pay
|
| 25 |
GVT_SEVPAY_PRV_WKS |
Number(2,0) |
SMALLINT NOT NULL |
Severance Pay Previous Weeks
|
| 26 |
GVT_MAND_RET_DT |
Date(10) |
DATE |
Mandatory Retirement Date
|
| 27 |
GVT_WGI_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Current WGI Status
A=Approved
C=WGI Created
D=Denied
M=Manual WGI
N=WGI Not Applicable
P=Postponed
W=Waiting WGI Processing
Default Value: W
|
| 28 |
GVT_INTRM_DAYS_WGI |
Number(3,0) |
SMALLINT NOT NULL |
Intermittent Days Worked
|
| 29 |
GVT_NONPAY_NOA |
Character(3) |
VARCHAR2(3) NOT NULL |
NonPay Nature of Action - Last
Prompt Table:
GVT_NOAC_TBL
|
| 30 |
GVT_NONPAY_HRS_WGI |
Number(7,2) |
DECIMAL(6,2) NOT NULL |
Non-Pay Hours WGI
|
| 31 |
GVT_NONPAY_HRS_SCD |
Number(7,2) |
DECIMAL(6,2) NOT NULL |
Non-Pay Hours for SCD
|
| 32 |
GVT_NONPAY_HRS_TNR |
Number(7,2) |
DECIMAL(6,2) NOT NULL |
Career Tenure Hours
|
| 33 |
GVT_NONPAY_HRS_PRB |
Number(7,2) |
DECIMAL(6,2) NOT NULL |
Non-Pay Hours for Probation
|
| 34 |
GVT_TEMP_PRO_EXPIR |
Date(10) |
DATE |
Temp Promotion Expires
|
| 35 |
GVT_TEMP_PSN_EXPIR |
Date(10) |
DATE |
Temporary Posn Change Expires
|
| 36 |
GVT_DETAIL_EXPIRES |
Date(10) |
DATE |
Date Detail Expires
|
| 37 |
GVT_SABBATIC_EXPIR |
Date(10) |
DATE |
Date Sabbatical Expires
|
| 38 |
GVT_RTND_GRADE_BEG |
Date(10) |
DATE |
Retained Grade Begins
|
| 39 |
GVT_RTND_GRADE_EXP |
Date(10) |
DATE |
Retained Grade Expires
|
| 40 |
GVT_NOA_CODE |
Character(3) |
VARCHAR2(3) NOT NULL |
Nature of Action Code
|
| 41 |
GVT_CURR_APT_AUTH1 |
Character(3) |
VARCHAR2(3) NOT NULL |
Current Appointment Auth Nbr 1
Prompt Table:
GVT_LEGAL_AUTH
|
| 42 |
GVT_CURR_APT_AUTH2 |
Character(3) |
VARCHAR2(3) NOT NULL |
Current Appointment Auth Nbr 2
Prompt Table:
GVT_LEGAL_AUTH
|
| 43 |
GVT_APPT_EXPIR_DT |
Date(10) |
DATE |
Appointment Expiration Date
|
| 44 |
GVT_CNV_BEGIN_DATE |
Date(10) |
DATE |
Conv Begin Date
|
| 45 |
GVT_CAREER_CNV_DUE |
Date(10) |
DATE |
Conversion To Career Due
|
| 46 |
GVT_CAREER_COND_DT |
Date(10) |
DATE |
Career Cond Conversion Date
|
| 47 |
GVT_APPT_LIMIT_HRS |
Number(4,0) |
SMALLINT NOT NULL |
Appointment Limit in Hours
|
| 48 |
GVT_APPT_LIMIT_DYS |
Number(3,0) |
SMALLINT NOT NULL |
Appointment Limit in Days
|
| 49 |
GVT_APPT_LIMIT_AMT |
Number(6,0) |
INTEGER NOT NULL |
Appointment Limit Amount
|
| 50 |
GVT_SUPV_PROB_DT |
Date(10) |
DATE |
Supv/Manager Probation Date
|
| 51 |
GVT_SES_PROB_DT |
Date(10) |
DATE |
SES Probation Date
|
| 52 |
GVT_ERN_PGM_PERM |
Character(2) |
VARCHAR2(2) NOT NULL |
Permanent Pay Plan - RIF
Prompt Table:
ERN_PROGRAM_TBL
|
| 53 |
GVT_OCC_SERS_PERM |
Character(4) |
VARCHAR2(4) NOT NULL |
Permanent Occ Series - RIF
Prompt Table:
GVT_OCCUPATION
|
| 54 |
GVT_GRADE_PERM |
Character(3) |
VARCHAR2(3) NOT NULL |
Permanent Grade - RIF
|
| 55 |
GVT_COMP_AREA_PERM |
Character(2) |
VARCHAR2(2) NOT NULL |
Comp/Area Level
|
| 56 |
GVT_COMP_LVL_PERM |
Character(3) |
VARCHAR2(3) NOT NULL |
Permanent Comp Level - RIF
|
| 57 |
GVT_CHANGE_FLAG |
Character(1) |
VARCHAR2(1) NOT NULL |
Change Flag
Y/N Table Edit
Default Value: N
|
| 58 |
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
Default Value: 00
|
| 59 |
GVT_WGI_DUE_DATE |
Date(10) |
DATE |
Within-Grade Increase Due Date
|
| 60 |
GVT_DT_LEI |
Date(10) |
DATE |
Date Last Equivalent Increase
|
| 61 |
GVT_FIN_DISCLOSURE |
Character(1) |
VARCHAR2(1) NOT NULL |
Financial Disclosure Required
Y/N Table Edit
Default Value: N
|
| 62 |
GVT_FIN_DISCL_DATE |
Date(10) |
DATE |
Financial Disclosure Due Date
|
| 63 |
GVT_TENURE |
Character(1) |
VARCHAR2(1) NOT NULL |
Highest Career Tenure
0=None
1=Permanent
2=Conditional
3=Indefinite
|
| 64 |
GVT_DETL_BARG_UNIT |
Character(4) |
VARCHAR2(4) NOT NULL |
Govt Detail Bargaining Unit
Prompt Table:
GVT_BARG_UNIT
|
| 65 |
GVT_DETL_UNION_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Govt Detail Union Code
Prompt Table:
UNION_TBL
|
| 66 |
NEXT_REVIEW_DT |
Date(10) |
DATE |
Next Review Date
|
| 67 |
GVT_WELFARE_WK_CD |
Character(2) |
VARCHAR2(2) NOT NULL |
Welfare to Work
00=Not Applicable
50=Welfare to Work Program Hire
50=Welfare to Work Program Hire
60=Hire not Welfare to Work Prog
60=Hire not Welfare to Work Prog
Default Value: 00
|
| 68 |
TENURE_ACCR_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Accrue Tenure Services
N=Do Not Accrue
Y=Accrue Tenure
|
| 69 |
FTE_TENURE |
Number(4,2) |
DECIMAL(3,2) NOT NULL |
FTE for Tenure Accrual
|
| 70 |
EG_GROUP |
Character(6) |
VARCHAR2(6) NOT NULL |
Service Calculation Group
|
| 71 |
FTE_FLX_SRVC |
Number(4,2) |
DECIMAL(3,2) NOT NULL |
FTE for Flex Service Accrual
|
| 72 |
CONTRACT_LENGTH |
Character(1) |
VARCHAR2(1) NOT NULL |
Contract Length
0=Not Applicable
1=<9 Month
1=<9 Month
2=9-10 Month
2=9-10 Month
3=11-12 Month
3=11-12 Month
4=9 Month
5=10 Month
6=11 Month
7=12 Month
8=>12 Month
|
| 73 |
APPOINT_END_DT |
Date(10) |
DATE |
Appointment End Date
|
| 74 |
NEE_PROVIDER_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
The ID for the Non Employee Provider, Agency, or Employer.
|