GVT_PI_EDUC_VW

(SQL View)
 Index Back

Education View for Federal PYI


SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,ACT.EDUCATION_LVL ,AC.YR_ACQUIRED ,AC.MAJOR_CODE ,'R' FROM PS_GVT_PERS_DATA A ,PS_ACCOMP_TBL ACT ,PS_ACCOMPLISHMENTS AC WHERE ACT.EDUCATION_LVL = (SELECT MAX(AT1.EDUCATION_LVL) FROM PS_ACCOMP_TBL AT1, PS_ACCOMPLISHMENTS AC1 WHERE AT1.ACCOMP_CATEGORY = 'DEG' AND AT1.ACCOMPLISHMENT = AC1.ACCOMPLISHMENT AND AC1.EMPLID = A.EMPLID) AND A.EMPLID = AC.EMPLID AND A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_GVT_PERS_DATA A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A.EFFSEQ = A1.EFFSEQ) AND A.EFFSEQ = (SELECT MAX(A1.EFFSEQ) FROM PS_GVT_PERS_DATA A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A.EFFDT = A1.EFFDT) AND ACT.ACCOMPLISHMENT = AC.ACCOMPLISHMENT

# 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 Record
3 EFFDT Date(10) DATE Effective Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 EDUCATION_LVL Character(2) VARCHAR2(2) NOT NULL Education Level
01=No Formal Education
02=Elementary School Completed
03=Some High School
04=High School Graduate
05=Terminal Occupation Prog DNC
06=Terminal Occupation Program
07=Some College - Less than 1 Yr
08=One Year College
09=Two Years College
10=Associate Degree
11=Three Years College
12=Four Years College
13=Bachelor Degree
14=Post Bachelors
15=First Professional
16=Post-first Professional
17=Master's Degree
18=Post Master's
19=Sixth-year Degree
20=Post-sixth Year
21=Doctorate Degree
22=Post-Doctorate
A=University - Advanced
T=Trade School
U=University
6 YR_ACQUIRED Number(4,0) SMALLINT NOT NULL Year Acquired
7 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code
8 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