Created by Miki Yanagi, last modified on Jul 23, 2019
In an effort to obtain consistency across system reports and each campus's IPEDS reporting, a new view for cohort retention has been proposed. The most notable change from former retention views is that this view--UM_RETEN_COHT_VW--does not filter out any students; the number of students in the view matches the number of students in the UM_STDNT_COHORT table. (Prior retention views, such as UM_RETENTION_FS, set several conditions to eliminate some students).
The below is the proposed list of fields on UM_RETEN_COHT_VW:
| Field Name | Description | Source table/ View | Field used or calculation |
| INSTITUTION | institution | UM_STDNT_COHORT | INSTITUTION |
| UM_COHORT_STRM | cohort term (summer entrants included in fall) | UM_STDNT_COHORT | UM_COHORT_STRM |
| UM_COHORT_TYPE | cohort type | UM_STDNT_COHORT | UM_COHORT_TYPE |
| UM_COHORT_DESCR | description for cohort type | UM_STDNT_COHORT UM_STUD_CENS_VW | ((case when co.um_cohort_type in ('FYR', 'EFY', 'GR6', 'GRD') then 'First-time ' when co.um_cohort_type ='TRF' then 'Transfer ' when co.um_cohort_type ='REA' then 'Readmit ' else '' end) || cen.um_full_part || ' ' || cen.um_student_level) |
| EMPLID | emplid | UM_STDNT_COHORT | EMPLID |
| NAME | full name | UM_STUD_CENS_VW | NAME |
| ADMIT_TERM | admit term | UM_STUD_CENS_VW | ADMIT_TERM |
| ADMIT_TYPE | admit type | UM_STUD_CENS_VW | ADMIT_TYPE |
| UM_LAST_SCH_TYPE | last school type | UM_STUD_CENS_VW | UM_LAST_SCH_TYPE |
| CENSUS_DT | census date for the cohort term | UM_STUD_CENS_VW | CENSUS_DT |
| UM_UNT_TAK_PRG_RC | Unit taken for progress as of first census | UM_STUD_CENS_VW | UM_UNT_TAK_PRG_RC |
| UM_FULL_PART | full-time or part-time as of first census | UM_STUD_CENS_VW | UM_FULL_PART
calculation - full-time/ part-time Expand source
CASE WHEN A.Acad_Level_Bot = 'GR' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN 'Full-Time' WHEN A.Acad_Level_Bot = 'MAS' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN 'Full-Time' WHEN A.Acad_Level_Bot = 'PHD' AND A.Um_Unt_Tak_Prg_Rc >= 6 THEN 'Full-Time' WHEN A.Um_Unt_Tak_Prg_Rc >= 12 THEN 'Full-Time' ELSE 'Part-Time' END |
| LEVEL_LOAD_RULE | level load rule (from ps_acad_prog_tbl) | UM_STUD_CENS_VW | LEVEL_LOAD_RULE |
| UM_STUDENT_LEVEL | student level | UM_STUD_CENS_VW | UM_STUDENT_LEVEL
calculation - student level Expand source
CASE WHEN A.ACAD_PROG_PRIMARY LIKE '%UN' THEN 'Non-Degree Undergraduate' WHEN A.ACAD_PROG_PRIMARY LIKE '%GN' THEN 'Non-Degree Graduate' WHEN A.ACAD_PROG_PRIMARY LIKE '%PN' THEN 'Non-Degree Law' WHEN A.ACAD_PROG_PRIMARY LIKE '%EU' THEN 'Non-Degree Undergraduate' WHEN A.ACAD_PROG_PRIMARY = 'NDUG' THEN 'Non-Degree Undergraduate' WHEN A.ACAD_PROG_PRIMARY = 'DIST' THEN 'Non-Degree Undergraduate' WHEN A.ACAD_PROG_PRIMARY = 'P-NON' THEN 'Non-Degree Undergraduate' WHEN A.ACAD_PROG_PRIMARY = 'NONCR' THEN 'Non-Degree Undergraduate' WHEN A.ACAD_PROG_PRIMARY = 'NDGR' THEN 'Non-Degree Graduate' WHEN A.ACAD_PROG_PRIMARY = 'GRNON' THEN 'Non-Degree Graduate' WHEN A.ACAD_PROG_PRIMARY = 'NDPR' AND A.Institution = 'UMS06' THEN 'Non-Degree Law' WHEN A.ACAD_PROG_PRIMARY = 'NDPR' THEN 'Non-Degree Undergraduate' WHEN A.LEVEL_LOAD_RULE = 'UG2YR' THEN 'Associate' WHEN A.LEVEL_LOAD_RULE = 'UG4YR' THEN 'Baccalaureate' WHEN A.LEVEL_LOAD_RULE = 'UG' THEN 'Baccalaureate' WHEN A.LEVEL_LOAD_RULE = 'UGAS' THEN 'Associate' WHEN A.LEVEL_LOAD_RULE = 'UGA' THEN 'Associate' WHEN A.LEVEL_LOAD_RULE = 'AA' THEN 'Associate' WHEN A.LEVEL_LOAD_RULE = 'GR' THEN 'Graduate' WHEN A.LEVEL_LOAD_RULE = 'GRAD' THEN 'Graduate' WHEN A.LEVEL_LOAD_RULE = 'LAW' THEN 'Law' WHEN A.ACAD_CAREER = 'UGRD' THEN 'Baccalaureate' WHEN A.ACAD_CAREER = 'GRAD' THEN 'Graduate' WHEN A.ACAD_CAREER = 'LAW' THEN 'Law' WHEN A.ACAD_CAREER = 'PBAC' THEN 'Baccalaureate' ELSE 'Other' END STUDENT_LEVEL |
| ACAD_CAREER | academic career as of first census | UM_STUD_CENS_VW | ACAD_CAREER |
| UM_PLANNED_DEGREE | degree as of first census | UM_STUD_CENS_VW | DEGREE |
| ACAD_PROG | academic program c | UM_STUD_CENS_VW | ACAD_PROG |
| ACAD_PLAN | academic plan as of first census | UM_STUD_CENS_VW | ACAD_PLAN |
| UM_ACAD_PLAN_DESCR | academic plan description | UM_STUD_CENS_VW | UM_ACAD_PLAN_DESCR |
| ACAD_ORG | academic organization of the first census | ACAD_PLAN_OWNER | ACAD_ORG (most recent value regardless of history) |
| SEX | gender as of first census | UM_STUD_CENS_VW | SEX |
| BIRTHDATE | birthdate | UM_STUD_CENS_VW | BIRTHDATE |
| UM_CENSUS_AGE | age as of first census | UM_STUD_CENS_VW | UM_CENSUS_AGE |
| UM_CENSUS_AGE_RNG | age range as of first census (based on IPEDS age range) | UM_STUD_CENS_VW | UM_CENSUS_AGE_RNG
calculation - census age range Expand source
CASE WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 14 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 17 THEN 'Under 18' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 18 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 19 THEN '18 - 19' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 20 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 21 THEN '20 - 21' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 22 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 24 THEN '22 - 24' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 25 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 29 THEN '25 - 29' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 30 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 34 THEN '30 - 34' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 35 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 39 THEN '35 - 39' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 40 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 49 THEN '40 - 49' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 50 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 64 THEN '50 - 64' WHEN TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) >= 65 AND TRUNC(( A.CENSUS_DT - A.birthdate )/365.25) <= 105 THEN '65 and Over' ELSE 'Unknown' END |
| UM_TRAD_STUDENT | traditional student (age under 25 as of first census) | UM_STUD_CENS_VW | case when cen.um_census_age < 25 then 'Y' else 'N' end |
| UM_ETHNIC_GRP_DESC | IPEDS ethnicity category as of first census | UM_STUD_CENS_VW | UM_ETHNIC_GRP_DESC
calculation- UM_ETHNIC_GRP_DESC (Alias A is UM_STUD_EXTRACT) Expand source
CASE WHEN A.Citizenship_Status = '4' THEN 'Nonresident Alien' WHEN A.Ethnic_Group = '3' THEN 'Hispanic/Latino' WHEN A.Hisp_Latino = 'Y' THEN 'Hispanic/Latino' WHEN A.Um_Multiple_Ethnic = 'Y' THEN '2 or More Races' WHEN A.Ethnic_Group = '1' THEN 'White' WHEN A.Ethnic_Group = '2' THEN 'Black/African American' WHEN A.Ethnic_Group = '4' THEN 'Asian' WHEN A.Ethnic_Group = '5' THEN 'American Indian/Alaska Native' WHEN A.Ethnic_Group = '7' THEN 'Native Hawaiian/Pacific Islander' WHEN A.Ethnic_Group = '6' THEN 'Unknown' ELSE 'Unknown' END |
| MAR_STATUS | marital status as of first census | UM_STUD_CENS_VW | MAR_STATUS |
| UM_TUITION_RES | tuition residency as of first census | UM_STUD_CENS_VW | UM_TUITION_RES |
| UM_FIRST_GEN | first generation as of first census | UM_STUD_CENS_VW | UM_FIRST_GEN
calculation- first generation Expand source
CASE WHEN A.EMPLID IN (( SELECT AA.COMMON_ID AS FGEN_EMPLID FROM PS_PERSON_COMMENT AA
WHERE AA.INSTITUTION = 'UMS01' AND AA.CMNT_CATEGORY = 'CS1' AND (AA.COMMENTS LIKE '%1st gen%' OR AA.COMMENTS LIKE '%1st Gen%')
UNION SELECT BB.EMPLID FROM PS_STDNT_GRPS BB WHERE BB.STDNT_GROUP = 'FGEN'
UNION SELECT CC.EMPLID FROM PS_ISIR_PARENT CC
WHERE CC.EFFDT = ( SELECT MAX(CC_ED.EFFDT) FROM PS_ISIR_PARENT CC_ED WHERE CC.EMPLID = CC_ED.EMPLID
AND CC.INSTITUTION = CC_ED.INSTITUTION AND CC.AID_YEAR = CC_ED.AID_YEAR AND CC_ED.EFFDT <= SYSDATE)
AND CC.EFFSEQ = ( SELECT MAX(CC_ES.EFFSEQ) FROM PS_ISIR_PARENT CC_ES WHERE CC.EMPLID = CC_ES.EMPLID
AND CC.INSTITUTION = CC_ES.INSTITUTION AND CC.AID_YEAR = CC_ES.AID_YEAR AND CC.EFFDT = CC_ES.EFFDT)
AND CC.FATHER_GRADE_LVL IN ('1', '2', '4') AND CC.MOTHER_GRADE_LVL IN ('1', '2', '4')
UNION SELECT DD.EMPLID FROM PS_RELATION_VW DD
WHERE (DD.PEOPLE_RELATION IN ('M', 'FA', 'PR', 'SF', 'SM') OR DD.GUARDIAN_RELATIONS IN ('LG', 'PG'))
AND DD.HIGHEST_EDUC_LVL IN ('B','C')
AND DECODE(DD.HIGHEST_EDUC_LVL,' ',1, 'A',2, 'B',3, 'C',4, 'D',5, 'E',6, 'F',7, 'G',8, 'H',9, 'I',10, 'J',11, 'K',12, 'L',13)
=( SELECT MAX(DECODE(EE.HIGHEST_EDUC_LVL ,' ' ,1 ,'A' ,2 ,'B' ,3 ,'C' ,4 ,'D' ,5 ,'E' ,6 ,'F' ,7 ,'G' ,8 ,'H' ,9 ,'I' ,10 ,'J' ,11 ,'K' ,12 ,'L' ,13))
FROM PS_RELATION_VW EE WHERE DD.EMPLID = EE.EMPLID AND (EE.PEOPLE_RELATION IN ('M', 'FA', 'PR', 'SF', 'SM')
OR EE.GUARDIAN_RELATIONS IN ('LG', 'PG'))))) THEN 'Y' ELSE 'N' END
|
| DT_OF_DEATH | date of death | PS_PERSONAL_DATA | DT_OF_DEATH |
| UM_STUDY_ABROAD | Study abroad Term | PS_STDNT_CAR_TERM | select acad_career, institution, emplid, LISTAGG(strm, ',') WITHIN GROUP (ORDER BY emplid) as UM_STUDY_ABROAD group by acad_career, institution, emplid |
| UM_ENROLLED_TERM | Enrolled term | UM_STUD_CENS_VW | select acad_career, institution, emplid, LISTAGG(strm, ',') WITHIN GROUP (ORDER BY emplid) as UM_ENROLLED_TERM |
| UM_RET_TERM_2 | retention to the next term | UM_STUD_CENS_VW UM_STDNT_COHORT PS_STDNT_CAR_TERM | CASE WHEN SUBSTR(co.um_cohort_strm,3,2) = '20' and INSTR(e.UM_ENROLLED_TERM , LTRIM(TO_CHAR(TO_NUMBER(co.um_cohort_strm , '9999') + 90 , '9999'))) > 0 |
| ACAD_PLAN_T2 | academic plan as of the second term census | UM_STUD_CENS_VW | ACAD_PLAN |
| UM_RET_YR_2 | retention to the second year | UM_STUD_CENS_VW UM_STDNT_COHORT PS_STDNT_CAR_TERM | CASE WHEN INSTR(e.UM_ENROLLED_TERM , LTRIM(TO_CHAR(TO_NUMBER(co.um_cohort_strm , '9999') + 100 , '9999'))) > 0 |
| ACAD_PLAN_Y2 | academic plan as of the second year census | UM_STUD_CENS_VW | ACAD_PLAN |
| UM_RET_YR_3 | retention to the third year | UM_STUD_CENS_VW UM_STDNT_COHORT PS_STDNT_CAR_TERM | CASE WHEN INSTR(e.UM_ENROLLED_TERM , LTRIM(TO_CHAR(TO_NUMBER(co.um_cohort_strm , '9999') + 200 , '9999'))) > 0 |
| ACAD_PLAN_Y3 | academic plan as of the third year census | UM_STUD_CENS_VW | ACAD_PLAN |
| UM_RET_YR_4 | retention to the 4th year | UM_STUD_CENS_VW UM_STDNT_COHORT PS_STDNT_CAR_TERM | CASE WHEN INSTR(e.UM_ENROLLED_TERM , LTRIM(TO_CHAR(TO_NUMBER(co.um_cohort_strm , '9999') + 300 , '9999'))) > 0 |
| ACAD_PLAN_Y4 | academic plan as of the fourth year census | UM_STUD_CENS_VW | ACAD_PLAN |
| UM_RET_YR_5 | retention to the 5th year | UM_STUD_CENS_VW UM_STDNT_COHORT PS_STDNT_CAR_TERM | CASE WHEN INSTR(e.UM_ENROLLED_TERM , LTRIM(TO_CHAR(TO_NUMBER(co.um_cohort_strm , '9999') + 400 , '9999'))) > 0 |
| ACAD_PLAN_Y5 | academic plan as of the 5th year census | UM_STUD_CENS_VW | ACAD_PLAN |
| UM_RET_YR_6 | retention to the 6th year | UM_STUD_CENS_VW UM_STDNT_COHORT PS_STDNT_CAR_TERM | CASE WHEN INSTR(e.UM_ENROLLED_TERM , LTRIM(TO_CHAR(TO_NUMBER(co.um_cohort_strm , '9999') + 500 , '9999'))) > 0 |
| ACAD_PLAN_Y6 | academic plan as of the 6th year census | UM_STUD_CENS_VW | ACAD_PLAN |
| UM_RET_YR_7 | retention to the 7th year | UM_STUD_CENS_VW UM_STDNT_COHORT PS_STDNT_CAR_TERM | CASE WHEN INSTR(e.UM_ENROLLED_TERM , LTRIM(TO_CHAR(TO_NUMBER(co.um_cohort_strm , '9999') + 600 , '9999'))) > 0 |
| ACAD_PLAN_Y7 | academic plan as of the 7th year census | UM_STUD_CENS_VW | ACAD_PLAN |
| UM_RET_YR_8 | retention to the 8th year | UM_STUD_CENS_VW UM_STDNT_COHORT PS_STDNT_CAR_TERM | CASE WHEN INSTR(e.UM_ENROLLED_TERM , LTRIM(TO_CHAR(TO_NUMBER(co.um_cohort_strm , '9999') + 700 , '9999'))) > 0 |
| ACAD_PLAN_Y8 | academic plan as of the 8th year census | UM_STUD_CENS_VW | ACAD_PLAN |
| COMPLETION_TERM | completion term | UM_DEGREES_COMP | COMPLETION_TERM |
| DEGREE_DT | degree date | UM_DEGREES_COMP | DEGREE_DT |
| DEGREE | degree | UM_DEGREES_COMP | DEGREE |
| UM_ACAD_PLAN1 | academic plan 1 for the degree | UM_DEGREES_COMP | UM_ACAD_PLAN1 |
| UM_ACAD_PLAN_DESC1 | academic plan 1 description for the degree | UM_DEGREES_COMP | UM_ACAD_PLAN_DESC1 |
| UM_ACAD_PLAN2 | academic plan 2 for the degree | UM_DEGREES_COMP | UM_ACAD_PLAN2 |
| UM_ACAD_PLAN_DESC2 | academic plan 2 description for the degree | UM_DEGREES_COMP | UM_ACAD_PLAN_DESC2 |
| UM_ACAD_PLAN3 | academic plan 3 for the degree | UM_DEGREES_COMP | UM_ACAD_PLAN3 |
| UM_ACAD_PLAN_DESC3 | academic plan 3 description for the degree | UM_DEGREES_COMP | UM_ACAD_PLAN_DESC3 |
| UM_GRAD_IN_2 | graduation within 2 years | UM_DEGREES_COMP UM_STDNT_COHORT | CASE WHEN (SUBSTR(co.um_cohort_strm,3,2) = '10' AND (d.completion_term - co.um_cohort_strm) <= 120) |
| UM_GRAD_IN_3 | graduation within 3 years | UM_DEGREES_COMP UM_STDNT_COHORT | CASE WHEN (SUBSTR(co.um_cohort_strm,3,2) = '10' AND (d.completion_term - co.um_cohort_strm) <= 220) |
| UM_GRAD_IN_4 | graduation within 4 years | UM_DEGREES_COMP UM_STDNT_COHORT | CASE WHEN (SUBSTR(co.um_cohort_strm,3,2) = '10' AND (d.completion_term - co.um_cohort_strm) <= 320) |
| UM_GRAD_IN_5 | graduation within 5 years | UM_DEGREES_COMP UM_STDNT_COHORT | CASE WHEN (SUBSTR(co.um_cohort_strm,3,2) = '10' AND (d.completion_term - co.um_cohort_strm) <= 420) |
| UM_GRAD_IN_6 | graduation within 6 years | UM_DEGREES_COMP UM_STDNT_COHORT | CASE WHEN (SUBSTR(co.um_cohort_strm,3,2) = '10' AND (d.completion_term - co.um_cohort_strm) <= 520) |
| UM_GRAD_IN_7 | graduation within 7 years | UM_DEGREES_COMP UM_STDNT_COHORT | CASE WHEN (SUBSTR(co.um_cohort_strm,3,2) = '10' AND (d.completion_term - co.um_cohort_strm) <= 620) |
| UM_GRAD_IN_8 | graduation within 8 years | UM_DEGREES_COMP UM_STDNT_COHORT | CASE WHEN (SUBSTR(co.um_cohort_strm,3,2) = '10' AND (d.completion_term - co.um_cohort_strm) <= 720) |
Alias for new view:
- co- UM_STDNT_COHORT
- cen- UM_STUD_CENS_VW
- d- UM_DEGREES_COMP
More Information:
SQL for UM_DEGREES_COMP Expand source
CREATE OR REPLACE VIEW SYSADM.PS_UM_DEGREES_COMP (institution, emplid, sex, acad_career, completion_term, um_term_descr, degree_dt, stdnt_degr, degree, descrformal, um_acad_plan1, um_plan_seq1, um_acad_plan_typ1, um_acad_plan_desc1, um_pln_trnscr_dsc1, um_cip_code1, um_cip_descr1, um_long_cip_descr1, um_subplan1a, um_subplan_type_1a, um_subpln_descr_1a, um_subplan1b, um_subplan_type_1b, um_subpln_descr_1b, um_subplan1c, um_subplan_type_1c, um_subpln_descr_1c, um_acad_plan2, um_plan_seq2, um_acad_plan_typ2, um_acad_plan_desc2, um_pln_trnscr_dsc2, um_cip_code2, um_cip_descr2, um_long_cip_descr2, um_subplan2a, um_subplan_type_2a, um_subpln_descr_2a, um_subplan2b, um_subplan_type_2b, um_subpln_descr_2b, um_subplan2c, um_subplan_type_2c, um_subpln_descr_2c, um_acad_plan3, um_plan_seq3, um_acad_plan_typ3, um_acad_plan_desc3, um_pln_trnscr_dsc3, um_cip_code3, um_cip_descr3, um_long_cip_descr3, um_subplan3a, um_subplan_type_3a, um_subpln_descr_3a, um_subplan3b, um_subplan_type_3b, um_subpln_descr_3b, um_subplan3c, um_subplan_type_3c, um_subpln_descr_3c, um_acad_plan4, um_plan_seq4, um_acad_plan_typ4, um_acad_plan_desc4, um_pln_trnscr_dsc4, um_cip_code4, um_cip_descr4, um_long_cip_descr4, um_subplan4a, um_subplan_type_4a, um_subpln_descr_4a, um_subplan4b, um_subplan_type_4b, um_subpln_descr_4b, um_subplan4c, um_subplan_type_4c, um_subpln_descr_4c, um_acad_plan5, um_plan_seq5, um_acad_plan_typ5, um_acad_plan_desc5, um_pln_trnscr_dsc5, um_cip_code5, um_cip_descr5, um_long_cip_descr5, um_subplan5a, um_subplan_type_5a, um_subpln_descr_5a, um_subplan5b, um_subplan_type_5b, um_subpln_descr_5b, um_subplan5c, um_subplan_type_5c, um_subpln_descr_5c, um_honors_code1, um_hon_descr1, um_honors_code2, um_hon_descr2, um_honors_code3, um_hon_descr3) AS 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);