GM_PERS_IND_VW

(SQL View)
 Index Back

Person Accomps w/ Category Vw

This view selects the education accomplishments of a professional (by category=DEG)

SELECT C.BUSINESS_UNIT , C.PROPOSAL_ID , C.VERSION_ID , C.EMPLID , C.USER_FIELD_1 , F.SETID , A.ACCOMPLISHMENT , B.DESCR , A.DT_ISSUED , A.MAJOR_CODE , B.ACCOMP_CATEGORY , A.YR_ACQUIRED , %subrec(LIC_CERT_SBR,A) , %subrec(EDUCATION_SBR,A) , %subrec(EDUCATN_GER_SBR,A) , G.DEPTID , F.DESCR FROM PS_GM_PROPSL_FORMS C , PS_ACCOMPLISHMENTS A , PS_ACCOMP_TBL B , PS_DEPT_TBL F , PS_JOB G WHERE C.EMPLID = A.EMPLID AND A.ACCOMPLISHMENT=B.ACCOMPLISHMENT AND B.ACCOMP_CATEGORY='DEG' AND A.DT_ISSUED = ( SELECT MAX(D.DT_ISSUED) FROM PS_ACCOMPLISHMENTS D WHERE A.EMPLID = D.EMPLID AND A.ACCOMPLISHMENT = D.ACCOMPLISHMENT) AND G.EMPLID = A.EMPLID AND F.DEPTID = G.DEPTID AND F.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_DEPT_TBL H WHERE F.SETID = H.SETID AND F.DEPTID = H.DEPTID AND H.EFF_STATUS = 'A' AND H.EFFDT <= %CurrentDateIn) AND G.EMPL_STATUS = 'A' AND G.EMPL_RCD = ( SELECT MIN (C3.EMPL_RCD) FROM PS_JOB C3 WHERE C3.EMPLID = G.EMPLID AND C3.EFFDT = ( SELECT MAX(C31.EFFDT) FROM PS_JOB C31 WHERE C31.EMPLID = C3.EMPLID AND C31.EMPL_RCD = C3.EMPL_RCD AND C31.EFFDT <= %CurrentDateIn) AND C3.EFFSEQ = ( SELECT MAX(C32.EFFSEQ) FROM PS_JOB C32 WHERE C32.EMPLID = C3.EMPLID AND C32.EMPL_RCD = C3.EMPL_RCD AND C32.EFFDT = C3.EFFDT)) AND G.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_JOB C1 WHERE C1.EMPLID = G.EMPLID AND C1.EMPL_RCD = G.EMPL_RCD AND C1.EFFDT <= %CurrentDateIn) AND G.EFFSEQ = ( SELECT MAX(C2.EFFSEQ) FROM PS_JOB C2 WHERE C2.EMPLID = G.EMPLID AND C2.EMPL_RCD = G.EMPL_RCD AND C2.EFFDT = G.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PROPOSAL_ID Character(25) VARCHAR2(25) NOT NULL Proposal ID
3 VERSION_ID Character(7) VARCHAR2(7) NOT NULL Version ID
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSONAL_DATA

5 USER_FIELD_1 Character(10) VARCHAR2(10) NOT NULL User Field 1
6 SETID Character(5) VARCHAR2(5) NOT NULL SetID
7 ACCOMPLISHMENT Character(8) VARCHAR2(8) NOT NULL Accomplishment

Prompt Table: %RECNAME_EDIT

8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
9 DT_ISSUED Date(10) DATE Issue Date
10 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code

Prompt Table: MAJOR_TBL

11 ACCOMP_CATEGORY Character(3) VARCHAR2(3) NOT NULL Accomplishment Category
DEG=Degree
HON=Honor or Award
LIC=License or Certification
LNG=Language
MEM=Membership
NVQ=NVQ
TST=Test
12 YR_ACQUIRED Number(4,0) SMALLINT NOT NULL Year Acquired
13 LICENSE_NBR Character(15) VARCHAR2(15) NOT NULL License/Certification Number
14 ISSUED_BY Character(30) VARCHAR2(30) NOT NULL Issued By
15 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

16 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

17 EXPIRATN_DT Date(10) DATE Expiration Date
18 LICENSE_VERIFIED Character(1) VARCHAR2(1) NOT NULL License Verified

Y/N Table Edit

Default Value: N

19 RENEWAL Character(1) VARCHAR2(1) NOT NULL Renewal in Progress

Y/N Table Edit

Default Value: N

20 MAJOR Character(30) VARCHAR2(30) NOT NULL Major
21 IPE_SW Character(1) VARCHAR2(1) NOT NULL Intern. Professional Education

Y/N Table Edit

Default Value: N

22 AVERAGE_GRADE Character(5) VARCHAR2(5) NOT NULL Average Grade
23 EDUCATOR Character(50) VARCHAR2(50) NOT NULL Educator
24 GRADUATE_INDICATOR Character(1) VARCHAR2(1) NOT NULL Graduated

Y/N Table Edit

Default Value: Y

25 SCHOOL_CODE Character(10) VARCHAR2(10) NOT NULL School Code

Prompt Table: SCHOOL_TBL

26 SCHOOL Character(30) VARCHAR2(30) NOT NULL School Name
27 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

Prompt Table: STATE_NAMES_OTH

28 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

Prompt Table: COUNTRY_TBL

29 TERMINAL_DEGREE Character(1) VARCHAR2(1) NOT NULL Terminal Degree for Discipline

Y/N Table Edit

Default Value: N

30 PRACTIC_GRADE_GER Character(4) VARCHAR2(4) NOT NULL Practical Grade
31 THEORY_GRADE_GER Character(4) VARCHAR2(4) NOT NULL Theoretical Grade
32 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
33 DESCR_2 Character(30) VARCHAR2(30) NOT NULL Description