AV_LEGACYDEG_VW

(SQL View)
 Index Back

Legacy Degree View

View with Descriptive joins for reporting (BIOBIT)

SELECT A.INSTITUTION , A.EMPLID , A.SEQ_NO , B.DESCR , A.ACCOMPLISHMENT , C.DESCR , A.AV_CLASS_YR , A.AV_MAJOR1_CD , A.AV_MAJOR2_CD , A.AV_MINOR1_CD , A.AV_MINOR2_CD , A.CAMPUS , D.DESCR , A.ACAD_ORG , E.DESCR , A.AV_DISTINCTION , A.ATTENDED_FROM , A.ATTENDED_TO FROM PS_AV_LEGACY_DEG A , PS_INSTITUTION_TBL B , PS_ACCOMP_TBL C , PS_CAMPUS_TBL D , PS_ACAD_ORG_TBL_VW E WHERE A.INSTITUTION = B.INSTITUTION AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_INSTITUTION_TBL B1 WHERE B1.INSTITUTION = B.INSTITUTION AND B1.EFFDT <= %CurrentDateIn) AND A.ACCOMPLISHMENT = C.ACCOMPLISHMENT AND A.INSTITUTION = D.INSTITUTION AND A.CAMPUS = D.CAMPUS AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_CAMPUS_TBL D1 WHERE D1.INSTITUTION = D.INSTITUTION AND D1.CAMPUS = D.CAMPUS AND D1.EFFDT <= %CurrentDateIn) AND A.INSTITUTION = E.INSTITUTION AND A.ACAD_ORG = E.ACAD_ORG AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_ACAD_ORG_TBL_VW E1 WHERE E1.INSTITUTION = E.INSTITUTION AND E1.ACAD_ORG = E.ACAD_ORG AND EFFDT <= %CurrentDateIn)

  • Related Language Record: AVLEGACYDEGVWLN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 SEQ_NO Number(2,0) SMALLINT NOT NULL Sequence Number
    4 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    5 ACCOMPLISHMENT Character(8) VARCHAR2(8) NOT NULL Accomplishment

    Prompt Table: DEGREE_VW

    6 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    7 AV_CLASS_YR Number(4,0) SMALLINT NOT NULL Class Year
    8 AV_MAJOR1_CD Character(10) VARCHAR2(10) NOT NULL Major

    Prompt Table: MAJOR_TBL

    9 AV_MAJOR2_CD Character(10) VARCHAR2(10) NOT NULL Major 2

    Prompt Table: MAJOR_TBL

    10 AV_MINOR1_CD Character(10) VARCHAR2(10) NOT NULL Minor

    Prompt Table: MAJOR_TBL

    11 AV_MINOR2_CD Character(10) VARCHAR2(10) NOT NULL Minor 2

    Prompt Table: MAJOR_TBL

    12 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus

    Prompt Table: CAMPUS_TBL

    13 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    14 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization

    Prompt Table: ACAD_ORG_TBL_VW

    15 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    16 AV_DISTINCTION Character(30) VARCHAR2(30) NOT NULL Distinction
    17 ATTENDED_FROM Number(4,0) SMALLINT NOT NULL Attended From
    18 ATTENDED_TO Number(4,0) SMALLINT NOT NULL To