HR_CP_EDUC2_VW

(SQL View)
 Index Back

CP IDP Education View

Data used for Career Planning Individual Development Plan report.

SELECT DISTINCT A.EMPLID ,%Substring(B.JPM_CAT_ITEM_ID,1,8) , B.EFFDT , B.JPM_INTEGER_2 , B.MAJOR_CODE , %Substring(C.JPM_DESCR90,1,30) , B.SCHOOL_DESCR , C.EDUCATION_LVL , ' ' , %Substring(B.MAJOR_DESCR,1,30) ,B.GVT_CREDIT_HOURS , B.GVT_CRED_HRS_TYPE , ' ' , B.JPM_DECIMAL_1 FROM PS_HR_CP_CAT_VW A , PS_JPM_JP_ITEMS B , PS_JPM_CAT_ITEMS C WHERE A.JPM_PROFILE_ID = B.JPM_PROFILE_ID AND A.JPM_CAT_TYPE = B.JPM_CAT_TYPE AND A.JPM_CAT_TYPE = C.JPM_CAT_TYPE AND B.JPM_CAT_ITEM_ID = C.JPM_CAT_ITEM_ID AND A.JPM_CAT_TYPE = 'DEG' AND B.EFF_STATUS = 'A' AND B.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_JPM_JP_ITEMS C1 WHERE B.JPM_PROFILE_ID = C1.JPM_PROFILE_ID AND B.JPM_CAT_TYPE = C1.JPM_CAT_TYPE AND B.JPM_CAT_ITEM_ID = C1.JPM_CAT_ITEM_ID AND B.JPM_CAT_ITEM_QUAL = C1.JPM_CAT_ITEM_QUAL AND B.JPM_CAT_ITEM_QUAL2 = C1.JPM_CAT_ITEM_QUAL2 AND C1.EFFDT <= %CurrentDateIn) AND C.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_JPM_CAT_ITEMS D WHERE C.JPM_CAT_TYPE = D.JPM_CAT_TYPE AND C.JPM_CAT_ITEM_ID = D.JPM_CAT_ITEM_ID AND D.EFFDT <=%CurrentDateIn)

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

    2 JPM_CAT_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Catalog Item Id. Identifies a unique catalog item defintion within a catalog type.
    3 DT_ISSUED Date(10) DATE Issue Date
    4 YR_ACQUIRED Number(4,0) SMALLINT NOT NULL Year Acquired
    5 MAJOR_CODE Character(10) VARCHAR2(10) NOT NULL Major Code
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 SCHOOL_DESCR Character(100) VARCHAR2(100) NOT NULL Name of the school
    8 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
    9 LONGNAME Character(30) VARCHAR2(30) NOT NULL Long Name
    10 MAJOR Character(30) VARCHAR2(30) NOT NULL Major
    11 GVT_CREDIT_HOURS Character(3) VARCHAR2(3) NOT NULL Education Credit Hours
    12 GVT_CRED_HRS_TYPE Character(1) VARCHAR2(1) NOT NULL Type of education credit hours
    C=Continuing Education
    O=Other Pay
    Q=Quarter Hours
    S=Semester Hours
    13 LONGNAME1 Character(30) VARCHAR2(30) NOT NULL Field Long Name
    14 GPA Number(5,2) DECIMAL(4,2) NOT NULL GPA