Created by Miki Yanagi on Apr 07, 2022
This view was created to show all degrees completed for the students.
| Key | Record.Fieldname | Format | XLAT | Heading Text |
|---|---|---|---|---|
| → | ACAD_CAREER - Academic Career | Char4 | N | Career |
| → | COMPLETION_TERM - Completion Term | Char4 | Compl Term | |
| → | DEGREE - Degree | Char8 | Degree | |
| DEGREE_DT - Degree Date | Date | Deg Dt | ||
| DESCRFORMAL - Formal Description | Char50 | FormalDesc | ||
| → | EMPLID - Empl ID | Char11 | ID | |
| → | INSTITUTION - Academic Institution | Char5 | Institution | |
| SEX - Gender | Char1 | N | Sex | |
| → | STDNT_DEGR - Student Degree Nbr | Char2 | Degree Nbr | |
| UM_ACAD_PLAN_DESC1 - UM_ACAD_PLAN_DESCR1 | Char30 | UM_ACAD_PLAN_D1 | ||
| UM_ACAD_PLAN_DESC2 - UM_ACAD_PLAN_DESCR2 | Char30 | UM_ACAD_PLAN_D2 | ||
| UM_ACAD_PLAN_DESC3 - UM_ACAD_PLAN_DESCR3 | Char30 | UM_ACAD_PLAN_D3 | ||
| UM_ACAD_PLAN_DESC4 - UM_ACAD_PLAN_DESCR4 | Char30 | UM_ACAD_PLAN_D4 | ||
| UM_ACAD_PLAN_DESC5 - UM_ACAD_PLAN_DESCR5 | Char30 | UM_ACAD_PLAN_D5 | ||
| UM_ACAD_PLAN_TYP1 - UM_ACAD_PLAN_TYPE1 | Char3 | UM_ACAD_PLAN_T1 | ||
| UM_ACAD_PLAN_TYP2 - UM_ACAD_PLAN_TYPE2 | Char3 | UM_ACAD_PLAN_T2 | ||
| UM_ACAD_PLAN_TYP3 - UM_ACAD_PLAN_TYPE3 | Char3 | UM_ACAD_PLAN_T3 | ||
| UM_ACAD_PLAN_TYP4 - UM_ACAD_PLAN_TYPE4 | Char3 | UM_ACAD_PLAN_T4 | ||
| UM_ACAD_PLAN_TYP5 - UM_ACAD_PLAN_TYPE5 | Char3 | UM_ACAD_PLAN_T5 | ||
| UM_ACAD_PLAN1 - UM_ACAD_PLAN1 | Char10 | UM_ACAD_PLAN1 | ||
| UM_ACAD_PLAN2 - UM_ACAD_PLAN2 | Char10 | UM_ACAD_PLAN2 | ||
| UM_ACAD_PLAN3 - UM_ACAD_PLAN3 | Char10 | UM_ACAD_PLAN3 | ||
| UM_ACAD_PLAN4 - UM_ACAD_PLAN4 | Char10 | UM_ACAD_PLAN4 | ||
| UM_ACAD_PLAN5 - UM_ACAD_PLAN5 | Char10 | UM_ACAD_PLAN5 | ||
| UM_CIP_CODE1 - CIP Code 1 | Char13 | CIP Code 1 | ||
| UM_CIP_CODE2 - CIP Code 2 | Char13 | CIP Code 2 | ||
| UM_CIP_CODE3 - CIP Code 3 | Char13 | CIP Code 3 | ||
| UM_CIP_CODE4 - CIP Code 4 | Char13 | CIP Code 4 | ||
| UM_CIP_CODE5 - CIP Code 5 | Char13 | CIP Code 5 | ||
| UM_CIP_DESCR1 - CIP Description 1 | Char30 | CIP Descr 1 | ||
| UM_CIP_DESCR2 - CIP Description 2 | Char30 | CIP Descr 2 | ||
| UM_CIP_DESCR3 - CIP Description 3 | Char30 | CIP Descr 3 | ||
| UM_CIP_DESCR4 - CIP Description 4 | Char30 | CIP Descr 4 | ||
| UM_CIP_DESCR5 - CIP Description 5 | Char30 | CIP Descr 5 | ||
| UM_HON_DESCR1 - Honors Code Description 1 | Char30 | Honors Descr 1 | ||
| UM_HON_DESCR2 - Honors Code Description 2 | Char30 | Honors Descr 2 | ||
| UM_HON_DESCR3 - Honors Code Description 3 | Char30 | Honors Descr 3 | ||
| UM_HONORS_CODE1 - Honors Code1 | Char3 | Hon Code1 | ||
| UM_HONORS_CODE2 - Honors Code2 | Char3 | Hon Code2 | ||
| UM_HONORS_CODE3 - Honors Code3 | Char3 | Hon Code3 | ||
| UM_LONG_CIP_DESCR1 - CIP Long Description 1 | Char254 | CIP Long Desc1 | ||
| UM_LONG_CIP_DESCR2 - CIP Long Description 2 | Char254 | CIP Long Desc2 | ||
| UM_LONG_CIP_DESCR3 - CIP Long Description 2 | Char254 | CIP Long Desc2 | ||
| UM_LONG_CIP_DESCR4 - cip Long Description 4 | Char254 | CIP Long Desc4 | ||
| UM_LONG_CIP_DESCR5 - CIP Long Description 5 | Char254 | CIP Long Desc5 | ||
| UM_PLAN_SEQ1 - UM_PLAN_SEQ1 | Num2.0 | UM_PLAN_SEQ1 | ||
| UM_PLAN_SEQ2 - UM_PLAN_SEQ2 | Num2.0 | UM_PLAN_SEQ2 | ||
| UM_PLAN_SEQ3 - UM_PLAN_SEQ3 | Num2.0 | UM_PLAN_SEQ3 | ||
| UM_PLAN_SEQ4 - UM_PLAN_SEQ4 | Num2.0 | UM_PLAN_SEQ4 | ||
| UM_PLAN_SEQ5 - UM_PLAN_SEQ5 | Num2.0 | UM_PLAN_SEQ5 | ||
| UM_PLN_TRNSCR_DSC1 - Transcript Description 1 | Char100 | Trns Descr1 | ||
| UM_PLN_TRNSCR_DSC2 - Transcript Description2 | Char100 | Trns Descr2 | ||
| UM_PLN_TRNSCR_DSC3 - Transcript Description3 | Char100 | Trns Descr3 | ||
| UM_PLN_TRNSCR_DSC4 - Transcript Description 4 | Char100 | Trns Descr 4 | ||
| UM_PLN_TRNSCR_DSC5 - Transcript Description 5 | Char100 | Trns Descr 5 | ||
| UM_SUBPLAN_TYPE_1A - Academic Subplan Type 1a | Char3 | Subplan Type 1a | ||
| UM_SUBPLAN_TYPE_1B - Academic Subplan Type 1B | Char3 | Subplan Type 1b | ||
| UM_SUBPLAN_TYPE_1C - Academic Subplan Type 1C | Char3 | Subplan Type 1C | ||
| UM_SUBPLAN_TYPE_2A - Academic Subplan Type 2A | Char3 | Subplan Type 2A | ||
| UM_SUBPLAN_TYPE_2B - Acdemic Subplan Type 2B | Char3 | Subplan Type 2B | ||
| UM_SUBPLAN_TYPE_2C - Academic Subplan Type 2C | Char3 | Subplan Type 2C | ||
| UM_SUBPLAN_TYPE_3A - Academic Subplan Type 3A | Char3 | Subplan Type 3A | ||
| UM_SUBPLAN_TYPE_3B - Academic Subplan Type 3B | Char3 | Subplan Type 3B | ||
| UM_SUBPLAN_TYPE_3C - Academic Subplan Type 3C | Char3 | Subplan Type 3C | ||
| UM_SUBPLAN_TYPE_4A - Academic Subplan Type 4A | Char3 | Subplan Type 4A | ||
| UM_SUBPLAN_TYPE_4B - Academic Subplan Type 4B | Char3 | Subplan Type 4B | ||
| UM_SUBPLAN_TYPE_4C - Academic Subplan Type 4C | Char3 | Subplan Type 4C | ||
| UM_SUBPLAN_TYPE_5A - Academic Subplan Type 5A | Char3 | Subplan Type 5a | ||
| UM_SUBPLAN_TYPE_5B - Academic Subplan Type 5B | Char3 | Subplan Type 5B | ||
| UM_SUBPLAN_TYPE_5C - Academic Subplan Type 5C | Char3 | Subplan Type 5C | ||
| UM_SUBPLAN1A - Academic Sub-Plan1A | Char10 | Sub-Plan1A | ||
| UM_SUBPLAN1B - Academic Sub-Plan1B | Char10 | Sub-Plan1B | ||
| UM_SUBPLAN1C - Academic Sub-Plan1C | Char10 | Sub-Plan1C | ||
| UM_SUBPLAN2A - Academic Sub-Plan2A | Char10 | Sub-Plan2A | ||
| UM_SUBPLAN2B - Academic Sub-Plan2B | Char10 | Sub-Plan2B | ||
| UM_SUBPLAN2C - Academic_sub-Plan2C | Char10 | Sub-Plan2C | ||
| UM_SUBPLAN3A - Academic Sub-Plan3A | Char10 | Sub-Plan3A | ||
| UM_SUBPLAN3B - Academic Sub-Plan3B | Char10 | Sub-Plan3B | ||
| UM_SUBPLAN3C - Academic Sub-Plan3C | Char10 | Sub-Plan3C | ||
| UM_SUBPLAN4A - Academic Sub-Plan4A | Char10 | Sub-Plan4A | ||
| UM_SUBPLAN4B - Academic Sub-Plan4B | Char10 | Sub-Plan4B | ||
| UM_SUBPLAN4C - Academic Sub-Plan4C | Char10 | Sub-Plan4C | ||
| UM_SUBPLAN5A - Academic Sub-Plan5A | Char10 | Sub-Plan5A | ||
| UM_SUBPLAN5B - Academic Sub-Plan5B | Char10 | Sub-Plan5B | ||
| UM_SUBPLAN5C - Academic Sub-Plan5C | Char10 | Sub-Plan5C | ||
| UM_SUBPLN_DESCR_1A - Academic Subplan Descr 1A | Char30 | Subpln Descr 1A | ||
| UM_SUBPLN_DESCR_1B - Academic Subplan Descr 1B | Char30 | Subpln Descr 1B | ||
| UM_SUBPLN_DESCR_1C - Academic Subplan Descr 1C | Char30 | Subpln Descr 1C | ||
| UM_SUBPLN_DESCR_2A - Academic Subplan Descr 2A | Char30 | Subpln Descr 2A | ||
| UM_SUBPLN_DESCR_2B - Academic Subplan Descr 2B | Char30 | Subpln Descr 2B | ||
| UM_SUBPLN_DESCR_2C - Academic Subplan Descr 2C | Char30 | Subpln Descr 2C | ||
| UM_SUBPLN_DESCR_3A - Academic Subplan Descr 3A | Char30 | Subpln Descr 3A | ||
| UM_SUBPLN_DESCR_3B - Academic Subplan Descr 3B | Char30 | Subpln Descr 3B | ||
| UM_SUBPLN_DESCR_3C - Academic Subplan Descr 3C | Char30 | Subpln Descr 3C | ||
| UM_SUBPLN_DESCR_4A - Academic Subplan Descr 4A | Char30 | Subpln Descr 4A | ||
| UM_SUBPLN_DESCR_4B - Academic Subplan Descr 4B | Char30 | Subpln Descr 4B | ||
| UM_SUBPLN_DESCR_4C - Academic Subplan Descr 4C | Char30 | Subpln Descr 4C | ||
| UM_SUBPLN_DESCR_5A - Academic Subplan Descr 5A | Char30 | Subpln Descr 5A | ||
| UM_SUBPLN_DESCR_5B - Academic Subplan Descr 5B | Char30 | Subpln Descr 5B | ||
| UM_SUBPLN_DESCR_5C - Academic Subplan Descr 5C | Char30 | Subpln Descr 5C | ||
| UM_TERM_DESCR - Term Description | Char30 | Term Descr |
SQL for UM_DEGREES_COMP Expand source
SELECT X.INSTITUTION,
X.EMPLID,
PD.SEX,
X.ACAD_CAREER,
X.COMPLETION_TERM,
X.TERM_DESCR,
X.DEGR_CONFER_DT,
X.STDNT_DEGR,
X.DEGREE,
DEG.DESCRFORMAL,
X.ACAD_PLAN1,
X.UM_PLAN_SEQ1,
X.UM_ACAD_PLAN_TYP1,
X.UM_ACAD_PLAN_DESC1,
X.UM_PLN_TRNSCR_DSC1,
X.UM_CIP_CODE1,
X.UM_CIP_DESCR1,
X.UM_LONG_CIP_DESCR1,
X.UM_SUBPLAN_1A,
X.UM_SUBPLAN_TYPE_1A,
X.UM_SUBPLAN_DESCR_1A,
X.UM_SUBPLAN_1B,
X.UM_SUBPLAN_TYPE_1B,
X.UM_SUBPLAN_DESCR_1B,
X.UM_SUBPLAN_1C,
X.UM_SUBPLAN_TYPE_1C,
X.UM_SUBPLAN_DESCR_1C,
X.ACAD_PLAN2,
X.UM_PLAN_SEQ2,
X.UM_ACAD_PLAN_TYP2,
X.UM_ACAD_PLAN_DESC2,
X.UM_PLN_TRNSCR_DSC2,
X.UM_CIP_CODE2,
X.UM_CIP_DESCR2,
X.UM_LONG_CIP_DESCR2,
X.UM_SUBPLAN_2A,
X.UM_SUBPLAN_TYPE_2A,
X.UM_SUBPLAN_DESCR_2A,
X.UM_SUBPLAN_2B,
X.UM_SUBPLAN_TYPE_2B,
X.UM_SUBPLAN_DESCR_2B,
X.UM_SUBPLAN_2C,
X.UM_SUBPLAN_TYPE_2C,
X.UM_SUBPLAN_DESCR_2C,
X.ACAD_PLAN3,
X.UM_PLAN_SEQ3,
X.UM_ACAD_PLAN_TYP3,
X.UM_ACAD_PLAN_DESC3,
X.UM_PLN_TRNSCR_DSC3,
X.UM_CIP_CODE3,
X.UM_CIP_DESCR3,
X.UM_LONG_CIP_DESCR3,
X.UM_SUBPLAN_3A,
X.UM_SUBPLAN_TYPE_3A,
X.UM_SUBPLAN_DESCR_3A,
X.UM_SUBPLAN_3B,
X.UM_SUBPLAN_TYPE_3B,
X.UM_SUBPLAN_DESCR_3B,
X.UM_SUBPLAN_3C,
X.UM_SUBPLAN_TYPE_3C,
X.UM_SUBPLAN_DESCR_3C,
X.ACAD_PLAN4,
X.UM_PLAN_SEQ4,
X.UM_ACAD_PLAN_TYP4,
X.UM_ACAD_PLAN_DESC4,
X.UM_PLN_TRNSCR_DSC4,
X.UM_CIP_CODE4,
X.UM_CIP_DESCR4,
X.UM_LONG_CIP_DESCR4,
X.UM_SUBPLAN_4A,
X.UM_SUBPLAN_TYPE_4A,
X.UM_SUBPLAN_DESCR_4A,
X.UM_SUBPLAN_4B,
X.UM_SUBPLAN_TYPE_4B,
X.UM_SUBPLAN_DESCR_4B,
X.UM_SUBPLAN_4C,
X.UM_SUBPLAN_TYPE_4C,
X.UM_SUBPLAN_DESCR_4C,
X.ACAD_PLAN5,
X.UM_PLAN_SEQ5,
X.UM_ACAD_PLAN_TYP5,
X.UM_ACAD_PLAN_DESC5,
X.UM_PLN_TRNSCR_DSC5,
X.UM_CIP_CODE5,
X.UM_CIP_DESCR5,
X.UM_LONG_CIP_DESCR5,
X.UM_SUBPLAN_5A,
X.UM_SUBPLAN_TYPE_5A,
X.UM_SUBPLAN_DESCR_5A,
X.UM_SUBPLAN_5B,
X.UM_SUBPLAN_TYPE_5B,
X.UM_SUBPLAN_DESCR_5B,
X.UM_SUBPLAN_5C,
X.UM_SUBPLAN_TYPE_5C,
X.UM_SUBPLAN_DESCR_5C,
X.HONORS_CODE1,
X.HON_DESCR1,
X.HONORS_CODE2,
X.HON_DESCR2,
X.HONORS_CODE3,
X.HON_DESCR3
FROM (SELECT X2.INSTITUTION,
X2.EMPLID,
X2.ACAD_CAREER,
X2.COMPLETION_TERM,
X2.TERM_DESCR,
X2.DEGR_CONFER_DT,
X2.STDNT_DEGR,
X2.DEGREE,
MAX(X2.UM_ACAD_PLAN1) ACAD_PLAN1,
MAX(X2.UM_PLAN_SEQ1) UM_PLAN_SEQ1,
MAX(X2.UM_ACAD_PLAN_TYP1) UM_ACAD_PLAN_TYP1,
MAX(X2.UM_ACAD_PLAN_DESC1) UM_ACAD_PLAN_DESC1,
MAX(X2.UM_PLN_TRNSCR_DSC1) UM_PLN_TRNSCR_DSC1,
MAX(X2.UM_CIP_CODE1) UM_CIP_CODE1,
MAX(X2.UM_CIP_DESCR1) UM_CIP_DESCR1,
MAX(X2.UM_LONG_CIP_DESCR1) UM_LONG_CIP_DESCR1,
MAX(X2.UM_SUBPLAN_1A) UM_SUBPLAN_1A,
MAX(X2.UM_SUBPLAN_TYPE_1A) UM_SUBPLAN_TYPE_1A,
MAX(X2.UM_SUBPLAN_DESCR_1A) UM_SUBPLAN_DESCR_1A,
MAX(X2.UM_SUBPLAN_1B) UM_SUBPLAN_1B,
MAX(X2.UM_SUBPLAN_TYPE_1B) UM_SUBPLAN_TYPE_1B,
MAX(X2.UM_SUBPLAN_DESCR_1B) UM_SUBPLAN_DESCR_1B,
MAX(X2.UM_SUBPLAN_1C) UM_SUBPLAN_1C,
MAX(X2.UM_SUBPLAN_TYPE_1C) UM_SUBPLAN_TYPE_1C,
MAX(X2.UM_SUBPLAN_DESCR_1C) UM_SUBPLAN_DESCR_1C,
MAX(X2.UM_ACAD_PLAN2) ACAD_PLAN2,
MAX(X2.UM_PLAN_SEQ2) UM_PLAN_SEQ2,
MAX(X2.UM_ACAD_PLAN_TYP2) UM_ACAD_PLAN_TYP2,
MAX(X2.UM_ACAD_PLAN_DESC2) UM_ACAD_PLAN_DESC2,
MAX(X2.UM_PLN_TRNSCR_DSC2) UM_PLN_TRNSCR_DSC2,
MAX(X2.UM_CIP_CODE2) UM_CIP_CODE2,
MAX(X2.UM_CIP_DESCR2) UM_CIP_DESCR2,
MAX(X2.UM_LONG_CIP_DESCR2) UM_LONG_CIP_DESCR2,
MAX(X2.UM_SUBPLAN_2A) UM_SUBPLAN_2A,
MAX(X2.UM_SUBPLAN_TYPE_2A) UM_SUBPLAN_TYPE_2A,
MAX(X2.UM_SUBPLAN_DESCR_2A) UM_SUBPLAN_DESCR_2A,
MAX(X2.UM_SUBPLAN_2B) UM_SUBPLAN_2B,
MAX(X2.UM_SUBPLAN_TYPE_2B) UM_SUBPLAN_TYPE_2B,
MAX(X2.UM_SUBPLAN_DESCR_2B) UM_SUBPLAN_DESCR_2B,
MAX(X2.UM_SUBPLAN_2C) UM_SUBPLAN_2C,
MAX(X2.UM_SUBPLAN_TYPE_2C) UM_SUBPLAN_TYPE_2C,
MAX(X2.UM_SUBPLAN_DESCR_2C) UM_SUBPLAN_DESCR_2C,
MAX(X2.UM_ACAD_PLAN3) ACAD_PLAN3,
MAX(X2.UM_PLAN_SEQ3) UM_PLAN_SEQ3,
MAX(X2.UM_ACAD_PLAN_TYP3) UM_ACAD_PLAN_TYP3,
MAX(X2.UM_ACAD_PLAN_DESC3) UM_ACAD_PLAN_DESC3,
MAX(X2.UM_PLN_TRNSCR_DSC3) UM_PLN_TRNSCR_DSC3,
MAX(X2.UM_CIP_CODE3) UM_CIP_CODE3,
MAX(X2.UM_CIP_DESCR3) UM_CIP_DESCR3,
MAX(X2.UM_LONG_CIP_DESCR3) UM_LONG_CIP_DESCR3,
MAX(X2.UM_SUBPLAN_3A) UM_SUBPLAN_3A,
MAX(X2.UM_SUBPLAN_TYPE_3A) UM_SUBPLAN_TYPE_3A,
MAX(X2.UM_SUBPLAN_DESCR_3A) UM_SUBPLAN_DESCR_3A,
MAX(X2.UM_SUBPLAN_3B) UM_SUBPLAN_3B,
MAX(X2.UM_SUBPLAN_TYPE_3B) UM_SUBPLAN_TYPE_3B,
MAX(X2.UM_SUBPLAN_DESCR_3B) UM_SUBPLAN_DESCR_3B,
MAX(X2.UM_SUBPLAN_3C) UM_SUBPLAN_3C,
MAX(X2.UM_SUBPLAN_TYPE_3C) UM_SUBPLAN_TYPE_3C,
MAX(X2.UM_SUBPLAN_DESCR_3C) UM_SUBPLAN_DESCR_3C,
MAX(X2.UM_ACAD_PLAN4) ACAD_PLAN4,
MAX(X2.UM_PLAN_SEQ4) UM_PLAN_SEQ4,
MAX(X2.UM_ACAD_PLAN_TYP4) UM_ACAD_PLAN_TYP4,
MAX(X2.UM_ACAD_PLAN_DESC4) UM_ACAD_PLAN_DESC4,
MAX(X2.UM_PLN_TRNSCR_DSC4) UM_PLN_TRNSCR_DSC4,
MAX(X2.UM_CIP_CODE4) UM_CIP_CODE4,
MAX(X2.UM_CIP_DESCR4) UM_CIP_DESCR4,
MAX(X2.UM_LONG_CIP_DESCR4) UM_LONG_CIP_DESCR4,
MAX(X2.UM_SUBPLAN_4A) UM_SUBPLAN_4A,
MAX(X2.UM_SUBPLAN_TYPE_4A) UM_SUBPLAN_TYPE_4A,
MAX(X2.UM_SUBPLAN_DESCR_4A) UM_SUBPLAN_DESCR_4A,
MAX(X2.UM_SUBPLAN_4B) UM_SUBPLAN_4B,
MAX(X2.UM_SUBPLAN_TYPE_4B) UM_SUBPLAN_TYPE_4B,
MAX(X2.UM_SUBPLAN_DESCR_4B) UM_SUBPLAN_DESCR_4B,
MAX(X2.UM_SUBPLAN_4C) UM_SUBPLAN_4C,
MAX(X2.UM_SUBPLAN_TYPE_4C) UM_SUBPLAN_TYPE_4C,
MAX(X2.UM_SUBPLAN_DESCR_4C) UM_SUBPLAN_DESCR_4C,
MAX(X2.UM_ACAD_PLAN5) ACAD_PLAN5,
MAX(X2.UM_PLAN_SEQ5) UM_PLAN_SEQ5,
MAX(X2.UM_ACAD_PLAN_TYP5) UM_ACAD_PLAN_TYP5,
MAX(X2.UM_ACAD_PLAN_DESC5) UM_ACAD_PLAN_DESC5,
MAX(X2.UM_PLN_TRNSCR_DSC5) UM_PLN_TRNSCR_DSC5,
MAX(X2.UM_CIP_CODE5) UM_CIP_CODE5,
MAX(X2.UM_CIP_DESCR5) UM_CIP_DESCR5,
MAX(X2.UM_LONG_CIP_DESCR5) UM_LONG_CIP_DESCR5,
MAX(X2.UM_SUBPLAN_5A) UM_SUBPLAN_5A,
MAX(X2.UM_SUBPLAN_TYPE_5A) UM_SUBPLAN_TYPE_5A,
MAX(X2.UM_SUBPLAN_DESCR_5A) UM_SUBPLAN_DESCR_5A,
MAX(X2.UM_SUBPLAN_5B) UM_SUBPLAN_5B,
MAX(X2.UM_SUBPLAN_TYPE_5B) UM_SUBPLAN_TYPE_5B,
MAX(X2.UM_SUBPLAN_DESCR_5B) UM_SUBPLAN_DESCR_5B,
MAX(X2.UM_SUBPLAN_5C) UM_SUBPLAN_5C,
MAX(X2.UM_SUBPLAN_TYPE_5C) UM_SUBPLAN_TYPE_5C,
MAX(X2.UM_SUBPLAN_DESCR_5C) UM_SUBPLAN_DESCR_5C,
MAX(X2.HONORS_CODE1) HONORS_CODE1,
MAX(X2.HON_DESCR1) HON_DESCR1,
MAX(X2.HONORS_CODE2) HONORS_CODE2,
MAX(X2.HON_DESCR2) HON_DESCR2,
MAX(X2.HONORS_CODE3) HONORS_CODE3,
MAX(X2.HON_DESCR3) HON_DESCR3
FROM (SELECT X3.INSTITUTION,
X3.EMPLID,
X3.ACAD_CAREER,
X3.COMPLETION_TERM,
X3.TERM_DESCR,
X3.DEGR_CONFER_DT,
X3.STDNT_DEGR,
X3.DEGREE,
CASE
WHEN X3.RANK3 = 1 THEN
X3.ACAD_PLAN
ELSE
' '
END UM_ACAD_PLAN1,
CASE
WHEN X3.RANK3 = 1 THEN
X3.PLAN_SEQUENCE
ELSE
0
END UM_PLAN_SEQ1,
CASE
WHEN X3.RANK3 = 1 THEN
X3.ACAD_PLAN_TYPE
ELSE
' '
END UM_ACAD_PLAN_TYP1,
CASE
WHEN X3.RANK3 = 1 THEN
X3.PLAN_DESCR3
ELSE
' '
END UM_ACAD_PLAN_DESC1,
CASE
WHEN X3.RANK3 = 1 THEN
X3.TRNSCR_DESCR
ELSE
' '
END UM_PLN_TRNSCR_DSC1,
CASE
WHEN X3.RANK3 = 1 THEN
X3.CIP_CODE
ELSE
' '
END UM_CIP_CODE1,
CASE
WHEN X3.RANK3 = 1 THEN
X3.CIP_DESCR3
ELSE
' '
END UM_CIP_DESCR1,
CASE
WHEN X3.RANK3 = 1 THEN
X3.DESCR254
ELSE
' '
END UM_LONG_CIP_DESCR1,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_1A,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_1A,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_1A,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_1B,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_1B,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_1B,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_1C,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_1C,
CASE
WHEN X3.RANK3 = 1 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_1C,
CASE
WHEN X3.RANK3 = 2 THEN
X3.ACAD_PLAN
ELSE
' '
END UM_ACAD_PLAN2,
CASE
WHEN X3.RANK3 = 2 THEN
X3.PLAN_SEQUENCE
ELSE
0
END UM_PLAN_SEQ2,
CASE
WHEN X3.RANK3 = 2 THEN
X3.ACAD_PLAN_TYPE
ELSE
' '
END UM_ACAD_PLAN_TYP2,
CASE
WHEN X3.RANK3 = 2 THEN
X3.PLAN_DESCR3
ELSE
' '
END UM_ACAD_PLAN_DESC2,
CASE
WHEN X3.RANK3 = 2 THEN
X3.TRNSCR_DESCR
ELSE
' '
END UM_PLN_TRNSCR_DSC2,
CASE
WHEN X3.RANK3 = 2 THEN
X3.CIP_CODE
ELSE
' '
END UM_CIP_CODE2,
CASE
WHEN X3.RANK3 = 2 THEN
X3.CIP_DESCR3
ELSE
' '
END UM_CIP_DESCR2,
CASE
WHEN X3.RANK3 = 2 THEN
X3.DESCR254
ELSE
' '
END UM_LONG_CIP_DESCR2,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_2A,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_2A,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_2A,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_2B,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_2B,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_2B,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_2C,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_2C,
CASE
WHEN X3.RANK3 = 2 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_2C,
CASE
WHEN X3.RANK3 = 3 THEN
X3.ACAD_PLAN
ELSE
' '
END UM_ACAD_PLAN3,
CASE
WHEN X3.RANK3 = 3 THEN
X3.PLAN_SEQUENCE
ELSE
0
END UM_PLAN_SEQ3,
CASE
WHEN X3.RANK3 = 3 THEN
X3.ACAD_PLAN_TYPE
ELSE
' '
END UM_ACAD_PLAN_TYP3,
CASE
WHEN X3.RANK3 = 3 THEN
X3.PLAN_DESCR3
ELSE
' '
END UM_ACAD_PLAN_DESC3,
CASE
WHEN X3.RANK3 = 3 THEN
X3.TRNSCR_DESCR
ELSE
' '
END UM_PLN_TRNSCR_DSC3,
CASE
WHEN X3.RANK3 = 3 THEN
X3.CIP_CODE
ELSE
' '
END UM_CIP_CODE3,
CASE
WHEN X3.RANK3 = 3 THEN
X3.CIP_DESCR3
ELSE
' '
END UM_CIP_DESCR3,
CASE
WHEN X3.RANK3 = 3 THEN
X3.DESCR254
ELSE
' '
END UM_LONG_CIP_DESCR3,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_3A,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_3A,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_3A,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_3B,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_3B,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_3B,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_3C,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_3C,
CASE
WHEN X3.RANK3 = 3 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_3C,
CASE
WHEN X3.RANK3 = 4 THEN
X3.ACAD_PLAN
ELSE
' '
END UM_ACAD_PLAN4,
CASE
WHEN X3.RANK3 = 4 THEN
X3.PLAN_SEQUENCE
ELSE
0
END UM_PLAN_SEQ4,
CASE
WHEN X3.RANK3 = 4 THEN
X3.ACAD_PLAN_TYPE
ELSE
' '
END UM_ACAD_PLAN_TYP4,
CASE
WHEN X3.RANK3 = 4 THEN
X3.PLAN_DESCR3
ELSE
' '
END UM_ACAD_PLAN_DESC4,
CASE
WHEN X3.RANK3 = 4 THEN
X3.TRNSCR_DESCR
ELSE
' '
END UM_PLN_TRNSCR_DSC4,
CASE
WHEN X3.RANK3 = 4 THEN
X3.CIP_CODE
ELSE
' '
END UM_CIP_CODE4,
CASE
WHEN X3.RANK3 = 4 THEN
X3.CIP_DESCR3
ELSE
' '
END UM_CIP_DESCR4,
CASE
WHEN X3.RANK3 = 4 THEN
X3.DESCR254
ELSE
' '
END UM_LONG_CIP_DESCR4,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_4A,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_4A,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_4A,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_4B,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_4B,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_4B,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_4C,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_4C,
CASE
WHEN X3.RANK3 = 4 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_4C,
CASE
WHEN X3.RANK3 = 5 THEN
X3.ACAD_PLAN
ELSE
' '
END UM_ACAD_PLAN5,
CASE
WHEN X3.RANK3 = 5 THEN
X3.PLAN_SEQUENCE
ELSE
0
END UM_PLAN_SEQ5,
CASE
WHEN X3.RANK3 = 5 THEN
X3.ACAD_PLAN_TYPE
ELSE
' '
END UM_ACAD_PLAN_TYP5,
CASE
WHEN X3.RANK3 = 5 THEN
X3.PLAN_DESCR3
ELSE
' '
END UM_ACAD_PLAN_DESC5,
CASE
WHEN X3.RANK3 = 5 THEN
X3.TRNSCR_DESCR
ELSE
' '
END UM_PLN_TRNSCR_DSC5,
CASE
WHEN X3.RANK3 = 5 THEN
X3.CIP_CODE
ELSE
' '
END UM_CIP_CODE5,
CASE
WHEN X3.RANK3 = 5 THEN
X3.CIP_DESCR3
ELSE
' '
END UM_CIP_DESCR5,
CASE
WHEN X3.RANK3 = 5 THEN
X3.DESCR254
ELSE
' '
END UM_LONG_CIP_DESCR5,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_5A,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_5A,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 1 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_5A,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_5B,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_5B,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 2 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_5B,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN
ELSE
' '
END UM_SUBPLAN_5C,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_TYPE
ELSE
' '
END UM_SUBPLAN_TYPE_5C,
CASE
WHEN X3.RANK3 = 5 AND X3.RANK3SUB = 3 THEN
X3.ACAD_SUB_PLAN_DESCR
ELSE
' '
END UM_SUBPLAN_DESCR_5C,
CASE
WHEN X3.HRANK = 1 THEN
NVL(X3.HONORS_CODE, ' ')
ELSE
' '
END HONORS_CODE1,
CASE
WHEN X3.HRANK = 1 THEN
X3.HON_DESCR
ELSE
' '
END HON_DESCR1,
CASE
WHEN X3.HRANK = 2 THEN
NVL(X3.HONORS_CODE, ' ')
ELSE
' '
END HONORS_CODE2,
CASE
WHEN X3.HRANK = 2 THEN
X3.HON_DESCR
ELSE
' '
END HON_DESCR2,
CASE
WHEN X3.HRANK = 3 THEN
NVL(X3.HONORS_CODE, ' ')
ELSE
' '
END HONORS_CODE3,
CASE
WHEN X3.HRANK = 3 THEN
X3.HON_DESCR
ELSE
' '
END HON_DESCR3
FROM (SELECT /*+ ORDERED */
AD3.EMPLID,
C3.ACAD_PLAN,
NVL(D4.ACAD_SUB_PLAN, ' ') ACAD_SUB_PLAN,
NVL(INSQ.ACAD_SUBPLAN_TYPE, ' ') ACAD_SUB_PLAN_TYPE,
NVL(INSQ.DESCR, ' ') ACAD_SUB_PLAN_DESCR,
C3.ACAD_CAREER,
C3.PLAN_SEQUENCE,
C3.STDNT_DEGR,
D3.ACAD_PLAN_TYPE,
D3.INSTITUTION,
AD3.COMPLETION_TERM,
TT3.DESCR AS TERM_DESCR,
AD3.DEGR_CONFER_DT,
AD3.DEGREE,
D3.DESCR AS PLAN_DESCR3,
D3.TRNSCR_DESCR,
D3.CIP_CODE,
F3.DESCR AS CIP_DESCR3,
F3.DESCR254,
H1.HONORS_CODE,
DECODE(AD3.INSTITUTION || H1.HONORS_CODE,
'UMS01CL',
'cum laude',
'UMS01DTC',
'With Distinction',
'UMS01HDS',
'With High Distinction',
'UMS01HST',
'With Highest Distinction',
'UMS01MCL',
'magna cum laude',
'UMS01SCL',
'summa cum laude',
'UMS02CUM',
'cum laude',
'UMS02MAG',
'magna cum laude',
'UMS02SUM',
'summa cum laude',
'UMS03C',
'Cum Laude',
'UMS03H',
'Honors Scholar',
'UMS03M',
'Magna Cum Laude',
'UMS03S',
'Summa Cum Laude',
'UMS04C',
'cum laude',
'UMS04M',
'magna cum laude',
'UMS04S',
'summa cum laude',
'UMS04W',
'with honors',
'UMS05CUM',
'cum laude',
'UMS05DIS',
'Distinction',
'UMS05HGD',
'High Distinction',
'UMS05HGH',
'High Honors',
'UMS05HON',
'Honors',
'UMS05HST',
'Highest Honors',
'UMS05HTD',
'Highest Distinction',
'UMS05MAG',
'magna cum laude',
'UMS05SAL',
'Salutatorian',
'UMS05SUM',
'summa cum laude',
'UMS05VAL',
'Valedictorian',
'UMS06CUM',
'cum laude',
'UMS06HON',
'General University Honors',
'UMS06HON',
'With Honors',
'UMS06MAG',
'magna cum laude',
'UMS06SUM',
'summa cum laude',
'UMS06UHO',
'Successful Completion of Honor',
'UMS06WD',
'With Distinction',
'UMS07CUM',
'Cum Laude',
'UMS07HH',
'Highest Honors in',
'UMS07MCL',
'Magna Cum Laude',
'UMS07SAL',
'Salutatorian',
'UMS07SCL',
'Summa Cum Laude',
'UMS07VAL',
'Valedictorian',
'UMS07WD',
'With Distinction',
'UMS07WD',
'with distinction',
' ') AS HON_DESCR,
DENSE_RANK() OVER(PARTITION BY C3.EMPLID, D3.INSTITUTION, C3.ACAD_CAREER, C3.STDNT_DEGR ORDER BY H1.HONORS_NBR, H1.HONORS_CODE) AS HRANK,
DENSE_RANK() OVER(PARTITION BY C3.EMPLID, D3.INSTITUTION, C3.ACAD_CAREER, C3.STDNT_DEGR ORDER BY DECODE(D3.ACAD_PLAN_TYPE, 'MAJ', 1, 'DMJ', 3, 'SP', 2, 'PRP', 2, 'CON', 2, 'COS', 2, 'HON', 2, 'RTC', 2, 'CER', 2, 5), C3.PLAN_SEQUENCE, C3.ACAD_PLAN) AS RANK3,
DENSE_RANK() OVER(PARTITION BY C3.EMPLID, D3.INSTITUTION, C3.ACAD_CAREER, C3.STDNT_DEGR, D4.ACAD_PLAN ORDER BY D4.ACAD_SUB_PLAN) AS RANK3SUB
FROM PS_ACAD_DEGR AD3,
PS_ACAD_DEGR_PLAN C3,
PS_ACAD_DEGR_SPLN D4,
PS_ACAD_DEGR_HONS H1,
PS_ACAD_PLAN_TBL D3,
PS_TERM_TBL TT3,
PS_CIP_CODE_TBL F3,
(SELECT SQ.ACAD_PLAN,
SQ.ACAD_SUB_PLAN,
SQ.ACAD_SUBPLAN_TYPE,
MIN(SQ.DESCR) DESCR
FROM PS_ACAD_SUBPLN_TBL SQ
WHERE SQ.EFFDT =
(SELECT MAX(SQ_ED.EFFDT)
FROM PS_ACAD_SUBPLN_TBL SQ_ED
WHERE SQ_ED.INSTITUTION =
SQ.INSTITUTION
AND SQ_ED.ACAD_PLAN = SQ.ACAD_PLAN
AND SQ_ED.ACAD_SUB_PLAN =
SQ.ACAD_SUB_PLAN)
GROUP BY SQ.ACAD_PLAN,
SQ.ACAD_SUB_PLAN,
SQ.ACAD_SUBPLAN_TYPE) INSQ
WHERE AD3.ACAD_DEGR_STATUS = 'A'
AND AD3.EMPLID = C3.EMPLID
AND AD3.STDNT_DEGR = C3.STDNT_DEGR
AND AD3.ACAD_CAREER = C3.ACAD_CAREER
AND C3.ACAD_PLAN = D3.ACAD_PLAN
AND AD3.INSTITUTION = D3.INSTITUTION
AND C3.EMPLID = D4.EMPLID(+)
AND C3.STDNT_DEGR = D4.STDNT_DEGR(+)
AND C3.ACAD_PLAN = D4.ACAD_PLAN(+)
AND D4.ACAD_PLAN = INSQ.ACAD_PLAN(+)
AND D4.ACAD_SUB_PLAN = INSQ.ACAD_SUB_PLAN(+)
AND AD3.INSTITUTION = TT3.INSTITUTION
AND AD3.ACAD_CAREER = TT3.ACAD_CAREER
AND AD3.COMPLETION_TERM = TT3.STRM
AND D3.CIP_CODE = F3.CIP_CODE(+)
AND AD3.EMPLID = H1.EMPLID(+)
AND AD3.STDNT_DEGR = H1.STDNT_DEGR(+)
AND D3.EFFDT =
(SELECT MAX(D3_ED.EFFDT)
FROM PS_ACAD_PLAN_TBL D3_ED
WHERE D3.INSTITUTION = D3_ED.INSTITUTION
AND D3.ACAD_PLAN = D3_ED.ACAD_PLAN
AND D3_ED.EFFDT <= TT3.TERM_END_DT)
AND D3.ACAD_PLAN_TYPE > ' '
AND (F3.EFFDT =
(SELECT MAX(F3_ED.EFFDT)
FROM PS_CIP_CODE_TBL F3_ED
WHERE F3.CIP_CODE = F3_ED.CIP_CODE
AND F3_ED.EFFDT <= SYSDATE) OR
F3.EFFDT IS NULL)) X3) X2
GROUP BY X2.INSTITUTION,
X2.EMPLID,
X2.ACAD_CAREER,
X2.COMPLETION_TERM,
X2.TERM_DESCR,
X2.DEGR_CONFER_DT,
X2.STDNT_DEGR,
X2.DEGREE) X,
PS_SCC_PERDATA_QVW PD,
PS_DEGREE_TBL DEG
WHERE X.EMPLID = PD.EMPLID
AND X.DEGREE = DEG.DEGREE
AND DEG.EFFDT = (SELECT MAX(DEG_ED.EFFDT)
FROM PS_DEGREE_TBL DEG_ED
WHERE DEG.DEGREE = DEG_ED.DEGREE
AND DEG_ED.EFFDT <= SYSDATE);