AV_LEG_DEG_VW

(SQL View)
 Index Back

Legacy Degrees

Retrieves all Legacy Degrees. One row for every degree.

SELECT A.EMPLID , 'L' , A.INSTITUTION , %NumToChar(A.AV_CLASS_YR) , A.ACCOMPLISHMENT , A.AV_MAJOR1_CD , A.AV_MAJOR2_CD , A.AV_MINOR1_CD , A.AV_MINOR2_CD , B.DESCR , C.DESCR , E.DESCR , F.DESCR , G.DESCR , H.DESCR FROM PS_AV_LEGACY_DEG A LEFT OUTER JOIN PS_MAJOR_TBL E ON A.AV_MAJOR1_CD = E.MAJOR_CODE LEFT OUTER JOIN PS_MAJOR_TBL F ON A.AV_MAJOR2_CD = F.MAJOR_CODE LEFT OUTER JOIN PS_MAJOR_TBL G ON A.AV_MINOR1_CD = G.MAJOR_CODE LEFT OUTER JOIN PS_MAJOR_TBL H ON A.AV_MINOR2_CD = H.MAJOR_CODE , PS_INSTITUTION_TBL B , PS_ACCOMP_TBL C WHERE B.INSTITUTION = A.INSTITUTION AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_INSTITUTION_TBL B1 WHERE B.INSTITUTION = B1.INSTITUTION AND B1.EFFDT <= %CurrentDateIn) AND B.EFF_STATUS = 'A' AND A.ACCOMPLISHMENT = C.ACCOMPLISHMENT

  • Related Language Record: AV_LEG_DEG_LANG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 AV_DEGREE_TYPE Character(1) VARCHAR2(1) NOT NULL The degree type. Internal, External or legacy.
    E=External Degree
    I=Internal Degree
    L=Legacy Degree
    3 AV_INST_ORG_ID Character(11) VARCHAR2(11) NOT NULL Institution or EXT_ORG_ID container
    4 AV_YEAR Character(6) VARCHAR2(6) NOT NULL Year
    5 ACCOMPLISHMENT Character(8) VARCHAR2(8) NOT NULL Accomplishment
    6 AV_MAJOR1_CD Character(10) VARCHAR2(10) NOT NULL Major
    7 AV_MAJOR2_CD Character(10) VARCHAR2(10) NOT NULL Major 2
    8 AV_MINOR1_CD Character(10) VARCHAR2(10) NOT NULL Minor
    9 AV_MINOR2_CD Character(10) VARCHAR2(10) NOT NULL Minor 2
    10 INSTITUTION_NAME Character(40) VARCHAR2(40) NOT NULL Institution Name
    11 AV_DEGREE_DESCR Character(30) VARCHAR2(30) NOT NULL Description of a degree that was conferred to a person.
    12 AV_MAJOR1_NAME Character(50) VARCHAR2(50) NOT NULL Major1 description of a person
    13 AV_MAJOR2_NAME Character(50) VARCHAR2(50) NOT NULL Major2 description
    14 AV_MINOR1_NAME Character(50) VARCHAR2(50) NOT NULL Minor1 description
    15 AV_MINOR2_NAME Character(50) VARCHAR2(50) NOT NULL Minor2 description