AV_EXT_DEG_VW

(SQL View)
 Index Back

External Degrees

Retrieves all External Degrees. One row for every degree.

SELECT A.EMPLID , 'E' , A.EXT_ORG_ID , %Substring(CAST(%Dateout(A.DEGREE_DT) as CHAR(10)),1,4) , A.DEGREE , A.EXT_SUBJ_AREA_1 , A.EXT_SUBJ_AREA_2 , ' ' , ' ' , B.DESCR , C.DESCR , A.FIELD_OF_STUDY_1 , A.FIELD_OF_STUDY_2 , ' ' , ' ' FROM PS_EXT_DEGREE A , PS_EXT_ORG_TBL B , PS_DEGREE_TBL C WHERE B.EXT_ORG_ID = A.EXT_ORG_ID AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_EXT_ORG_TBL B1 WHERE B.EXT_ORG_ID = B1.EXT_ORG_ID AND B1.EFFDT <= %CurrentDateIn) AND B.EFF_STATUS = 'A' AND A.DEGREE = C.DEGREE AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_DEGREE_TBL C1 WHERE C.DEGREE = C1.DEGREE AND C1.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A'

  • Related Language Record: AV_EXT_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