AV_INT_DEG_VW

(SQL View)
 Index Back

Internal Degrees

Retrieves all InternalDegrees. One row for every degree.

SELECT A.EMPLID , 'I' , A.INSTITUTION , %Substring(CAST(%Dateout(F.DEGR_CONFER_DT) as CHAR(10)),1,4) , B.STDNT_DEGR , F.DEGREE , B.ACAD_PLAN , C.ACAD_PLAN , D.ACAD_PLAN , E.ACAD_PLAN , G.DESCR , H.DESCR , B.AV_MAJOR1_NAME , C.AV_MAJOR2_NAME , D.AV_MINOR1_NAME , E.AV_MINOR2_NAME FROM PS_ACAD_PROG A LEFT OUTER JOIN PS_AV_PLAN_MAJ1_VW B ON A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ LEFT OUTER JOIN PS_AV_PLAN_MAJ2_VW C ON A.EMPLID = C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND A.EFFDT = C.EFFDT AND A.EFFSEQ = C.EFFSEQ LEFT OUTER JOIN PS_AV_PLAN_MIN1_VW D ON A.EMPLID = D.EMPLID AND A.ACAD_CAREER = D.ACAD_CAREER AND A.STDNT_CAR_NBR = D.STDNT_CAR_NBR AND A.EFFDT =D.EFFDT AND A.EFFSEQ = D.EFFSEQ LEFT OUTER JOIN PS_AV_PLAN_MIN2_VW E ON A.EMPLID = E.EMPLID AND A.ACAD_CAREER = E.ACAD_CAREER AND A.STDNT_CAR_NBR = E.STDNT_CAR_NBR AND A.EFFDT = E.EFFDT AND A.EFFSEQ = E.EFFSEQ LEFT OUTER JOIN PS_ACAD_DEGR F ON (A.EMPLID = F.EMPLID AND B.STDNT_DEGR = F.STDNT_DEGR) LEFT OUTER JOIN PS_AV_DEG_DESCR_VW H ON (F.DEGREE = H.DEGREE) , PS_INSTITUTION_TBL G WHERE A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_ACAD_PROG A1 WHERE A.EMPLID = A1.EMPLID AND A.ACAD_CAREER = A1.ACAD_CAREER AND A.STDNT_CAR_NBR = A1.STDNT_CAR_NBR) AND A.EMPLID = F.EMPLID AND B.STDNT_DEGR = F.STDNT_DEGR AND G.INSTITUTION = A.INSTITUTION AND G.EFFDT = ( SELECT MAX(G1.EFFDT) FROM PS_INSTITUTION_TBL G1 WHERE G.INSTITUTION = G1.INSTITUTION AND G1.EFFDT <= A.EFFDT) AND G.EFF_STATUS = 'A'

  • Related Language Record: AV_INT_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 STDNT_DEGR Character(2) VARCHAR2(2) NOT NULL Student Degree Nbr
    6 ACCOMPLISHMENT Character(8) VARCHAR2(8) NOT NULL Accomplishment
    7 AV_MAJOR1_CD Character(10) VARCHAR2(10) NOT NULL Major
    8 AV_MAJOR2_CD Character(10) VARCHAR2(10) NOT NULL Major 2
    9 AV_MINOR1_CD Character(10) VARCHAR2(10) NOT NULL Minor
    10 AV_MINOR2_CD Character(10) VARCHAR2(10) NOT NULL Minor 2
    11 INSTITUTION_NAME Character(40) VARCHAR2(40) NOT NULL Institution Name
    12 AV_DEGREE_DESCR Character(30) VARCHAR2(30) NOT NULL Description of a degree that was conferred to a person.
    13 AV_MAJOR1_NAME Character(50) VARCHAR2(50) NOT NULL Major1 description of a person
    14 AV_MAJOR2_NAME Character(50) VARCHAR2(50) NOT NULL Major2 description
    15 AV_MINOR1_NAME Character(50) VARCHAR2(50) NOT NULL Minor1 description
    16 AV_MINOR2_NAME Character(50) VARCHAR2(50) NOT NULL Minor2 description