Created by Corina C Larsen, last modified by Miki Yanagi on Jan 03, 2020

This view contains ACAD_PROG with up to 5 ACAD_PLANs, each with up to 3 of their associated ACAD_SUB_PLNs, as of the maximum effective date of each that is less than or equal to the current date. 

This is one of four similar views. See UM_FLAT_PRGPLAN, UM_PRGPLAN_CENS & UM_PRGPLAN_STRM, as well as this comparison chart of the four.

KeyRecord.FieldnameFormatXLATHeading Text
→ACAD_CAREER - Academic CareerChar4NCareer
→ACAD_PROG - Academic ProgramChar5 Acad Prog
→EMPLID - Empl IDChar11 ID
→INSTITUTION - Academic InstitutionChar5 Institution
 PROG_STATUS - Academic Program StatusChar4NStatus
→STDNT_CAR_NBR - Student Career NbrNum3.0 Career Nbr
 STRM - TermChar4 Term
 UM_ACAD_PLAN1 - UM_ACAD_PLAN1Char10 UM_ACAD_PLAN1
 UM_ACAD_PLAN2 - UM_ACAD_PLAN2Char10 UM_ACAD_PLAN2
 UM_ACAD_PLAN3 - UM_ACAD_PLAN3Char10 UM_ACAD_PLAN3
 UM_ACAD_PLAN4 - UM_ACAD_PLAN4Char10 UM_ACAD_PLAN4
 UM_ACAD_PLAN5 - UM_ACAD_PLAN5Char10 UM_ACAD_PLAN5
 UM_ACAD_SUB_PLN1A - Acad Sub Plan 1AChar10 Acad SubPlan 1A
 UM_ACAD_SUB_PLN1B - Acad Sub Plan 1BChar10 Acad SubPlan 1B
 UM_ACAD_SUB_PLN1C - Acad Sub Plan 1CChar10 Acad SubPlan 1C
 UM_ACAD_SUB_PLN2A - Acad Sub Plan 2AChar10 Acad SubPlan 2A
 UM_ACAD_SUB_PLN2B - Acad Sub Plan 2BChar10 Acad SubPlan 2B
 UM_ACAD_SUB_PLN2C - Acad Sub Plan 2CChar10 Acad SubPlan 2C
 UM_ACAD_SUB_PLN3A - Acad Sub Plan 3AChar10 Acad SubPlan 3A
 UM_ACAD_SUB_PLN3B - Acad Sub Plan 3BChar10 Acad SubPlan 3B
 UM_ACAD_SUB_PLN3C - Acad Sub Plan 3CChar10 Acad SubPlan 3C
 UM_ACAD_SUB_PLN4A - Acad Sub Plan 4AChar10 Acad SubPlan 4A
 UM_ACAD_SUB_PLN4B - Acads Sub Plan 4BChar10 Acad SubPlan 4B
 UM_ACAD_SUB_PLN4C - Acad Sub Plan 4CChar10 Acad SubPlan 4C
 UM_ACAD_SUB_PLN5A - Acad Sub Plan 5AChar10 Acad SubPlan 5A
 UM_ACAD_SUB_PLN5B - Acad Sub Plan 5BChar10 Acad SubPlan 5B
 UM_ACAD_SUB_PLN5C - Acad Sub Plan 5CChar10 Acad SubPlan 5C
 UM_CIP_CODE1 - CIP Code 1Char13 CIP Code 1
 UM_CIP_CODE2 - CIP Code 2Char13 CIP Code 2
 UM_CIP_CODE3 - CIP Code 3Char13 CIP Code 3
 UM_CIP_CODE4 - CIP Code 4Char13 CIP Code 4
 UM_CIP_CODE5 - CIP Code 5Char13 CIP Code 5
 UM_DECLARE_DT1 - Declare Date 1Date Decl Date1
 UM_DECLARE_DT2 - Declare Date 2Date Decl Date2
 UM_DECLARE_DT3 - Declare Date 3Date Decl Date3
 UM_DECLARE_DT4 - Declare Date 4Date DeclDate4
 UM_DECLARE_DT5 - Declare Date 5Date DeclDate5
 UM_DEGREE1 - DEGREE1Char8 DEGREE1
 UM_DEGREE2 - DEGREE2Char8 DEGREE2
 UM_DEGREE3 - DEGREE3Char8 DEGREE3
 UM_DEGREE4 - DEGREE4Char8 DEGREE4
 UM_DEGREE5 - DEGREE5Char8 DEGREE5
 UM_PLAN_DESCR1 - Plan Description 1Char50 Plan Descr 1
 UM_PLAN_DESCR2 - Plan Description 2Char50 Plan Descr 2
 UM_PLAN_DESCR3 - Plan Description 3Char50 Plan Descr 3
 UM_PLAN_DESCR4 - Plan Description 4Char50 Plan Descr 4
 UM_PLAN_DESCR5 - Plan Description 5Char50 Plan Descr 5
 UM_PLAN_TYPE1 - Plan Type 1Char3 Plan Type 1
 UM_PLAN_TYPE2 - Plan Type 2Char3 Plan Type 2
 UM_PLAN_TYPE3 - Plan Type 3Char3 Plan Type 3
 UM_PLAN_TYPE4 - Plan Type 4Char3 Plan Type 4
 UM_PLAN_TYPE5 - Plan Type 5Char3 Plan Type 5
 UM_SUBPLN_DESCR_1A - Academic Subplan Descr 1AChar30 Subpln Descr 1A
 UM_SUBPLN_DESCR_1B - Academic Subplan Descr 1BChar30 Subpln Descr 1B
 UM_SUBPLN_DESCR_1C - Academic Subplan Descr 1CChar30 Subpln Descr 1C
 UM_SUBPLN_DESCR_2A - Academic Subplan Descr 2AChar30 Subpln Descr 2A
 UM_SUBPLN_DESCR_2B - Academic Subplan Descr 2BChar30 Subpln Descr 2B
 UM_SUBPLN_DESCR_2C - Academic Subplan Descr 2CChar30 Subpln Descr 2C
 UM_SUBPLN_DESCR_3A - Academic Subplan Descr 3AChar30 Subpln Descr 3A
 UM_SUBPLN_DESCR_3B - Academic Subplan Descr 3BChar30 Subpln Descr 3B
 UM_SUBPLN_DESCR_3C - Academic Subplan Descr 3CChar30 Subpln Descr 3C
 UM_SUBPLN_DESCR_4A - Academic Subplan Descr 4AChar30 Subpln Descr 4A
 UM_SUBPLN_DESCR_4B - Academic Subplan Descr 4BChar30 Subpln Descr 4B
 UM_SUBPLN_DESCR_4C - Academic Subplan Descr 4CChar30 Subpln Descr 4C
 UM_SUBPLN_DESCR_5A - Academic Subplan Descr 5AChar30 Subpln Descr 5A
 UM_SUBPLN_DESCR_5B - Academic Subplan Descr 5BChar30 Subpln Descr 5B
 UM_SUBPLN_DESCR_5C - Academic Subplan Descr 5CChar30 Subpln Descr 5C

Data Dictionary Links

Clicking the linked (blue) fields in any table will take you to the Data Dictionary and provide you with more information about that field as well as field values if they are available.

UM_FLT_PRGPLN SQL Code 
WITH PLN_M AS
 (SELECT A.EMPLID,
         A.INSTITUTION,
         A.ACAD_CAREER,
         A.STDNT_CAR_NBR,
         A.ACAD_PROG,
         A.EFFDT,
         A.EFFSEQ,
         A.PROG_STATUS,
         B.ACAD_PLAN,
         NVL(B.DECLARE_DT, to_date('01/01/1901', 'MM/DD/YYYY')) PLAN_DECLARE_DT,
         B.PLAN_SEQUENCE,
         C.DESCR,
         C.ACAD_PLAN_TYPE,
         C.DEGREE,
         C.CIP_CODE
    FROM SYSADM.PS_ACAD_PROG     A,
         SYSADM.PS_ACAD_PLAN     B,
         SYSADM.PS_ACAD_PLAN_TBL C
   WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT)
                      FROM SYSADM.PS_ACAD_PLAN A_ED
                     WHERE A.EMPLID = A_ED.EMPLID
                       AND A.ACAD_CAREER = A_ED.ACAD_CAREER
                       AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
                       AND A_ED.EFFDT <= SYSDATE)
     AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ)
                       FROM SYSADM.PS_ACAD_PLAN A_ES
                      WHERE A.EMPLID = A_ES.EMPLID
                        AND A.ACAD_CAREER = A_ES.ACAD_CAREER
                        AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
                        AND A.EFFDT = A_ES.EFFDT)
     AND 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
     AND B.ACAD_PLAN = C.ACAD_PLAN
     AND A.INSTITUTION = C.INSTITUTION
     AND C.EFFDT = (SELECT MAX(C1.EFFDT)
                      FROM SYSADM.PS_ACAD_PLAN_TBL C1
                     WHERE C1.INSTITUTION = C.INSTITUTION
                       AND C1.ACAD_PLAN = C.ACAD_PLAN
                       AND C1.EFFDT <= SYSDATE)),
SUBPLN AS
 (SELECT DISTINCT D.EMPLID,
                  D.ACAD_CAREER,
                  D.STDNT_CAR_NBR,
                  E.INSTITUTION,
                  D.ACAD_PLAN,
                  D.EFFDT,
                  D.EFFSEQ,
                  REGEXP_SUBSTR(LISTAGG(D.ACAD_SUB_PLAN, ';') within
                                GROUP(ORDER BY D.ACAD_SUB_PLAN)
                                over(partition BY D.EMPLID,
                                     D.ACAD_CAREER,
                                     D.STDNT_CAR_NBR,
                                     E.INSTITUTION,
                                     D.ACAD_PLAN,
                                     D.EFFDT,
                                     D.EFFSEQ),
                                '[^;]+',
                                1,
                                1) ACAD_SUB_PLAN_A,
                  REGEXP_SUBSTR(LISTAGG(D.ACAD_SUB_PLAN, ';') within
                                GROUP(ORDER BY D.ACAD_SUB_PLAN)
                                over(partition BY D.EMPLID,
                                     D.ACAD_CAREER,
                                     D.STDNT_CAR_NBR,
                                     E.INSTITUTION,
                                     D.ACAD_PLAN,
                                     D.EFFDT,
                                     D.EFFSEQ),
                                '[^;]+',
                                1,
                                2) ACAD_SUB_PLAN_B,
                  REGEXP_SUBSTR(LISTAGG(D.ACAD_SUB_PLAN, ';') within
                                GROUP(ORDER BY D.ACAD_SUB_PLAN)
                                over(partition BY D.EMPLID,
                                     D.ACAD_CAREER,
                                     D.STDNT_CAR_NBR,
                                     E.INSTITUTION,
                                     D.ACAD_PLAN,
                                     D.EFFDT,
                                     D.EFFSEQ),
                                '[^;]+',
                                1,
                                3) ACAD_SUB_PLAN_C,
                  REGEXP_SUBSTR(LISTAGG(REGEXP_REPLACE(E.DESCR,
                                                       '[\+\;]+',
                                                       ''),
                                        ';') within
                                GROUP(ORDER BY D.ACAD_SUB_PLAN)
                                over(partition BY D.EMPLID,
                                     D.ACAD_CAREER,
                                     D.STDNT_CAR_NBR,
                                     E.INSTITUTION,
                                     D.ACAD_PLAN,
                                     D.EFFDT,
                                     D.EFFSEQ),
                                '[^;]+',
                                1,
                                1) ACAD_SUB_PLAN_DESCR_A,
                  REGEXP_SUBSTR(LISTAGG(REGEXP_REPLACE(E.DESCR,
                                                       '[\+\;]+',
                                                       ''),
                                        ';') within
                                GROUP(ORDER BY D.ACAD_SUB_PLAN)
                                over(partition BY D.EMPLID,
                                     D.ACAD_CAREER,
                                     D.STDNT_CAR_NBR,
                                     E.INSTITUTION,
                                     D.ACAD_PLAN,
                                     D.EFFDT,
                                     D.EFFSEQ),
                                '[^;]+',
                                1,
                                2) ACAD_SUB_PLAN_DESCR_B,
                  REGEXP_SUBSTR(LISTAGG(REGEXP_REPLACE(E.DESCR,
                                                       '[\+\;]+',
                                                       ''),
                                        ';') within
                                GROUP(ORDER BY D.ACAD_SUB_PLAN)
                                over(partition BY D.EMPLID,
                                     D.ACAD_CAREER,
                                     D.STDNT_CAR_NBR,
                                     E.INSTITUTION,
                                     D.ACAD_PLAN,
                                     D.EFFDT,
                                     D.EFFSEQ),
                                '[^;]+',
                                1,
                                3) ACAD_SUB_PLAN_DESCR_C,
                  LISTAGG(REGEXP_REPLACE(E.DESCR, '[\+\;]+', ''), ';') within GROUP(ORDER BY D.ACAD_SUB_PLAN) over(partition BY D.EMPLID, D.ACAD_CAREER, D.STDNT_CAR_NBR, E.INSTITUTION, D.ACAD_PLAN, D.EFFDT, D.EFFSEQ)
    FROM SYSADM.PS_ACAD_SUBPLAN D, SYSADM.PS_ACAD_SUBPLN_TBL E
   WHERE D.ACAD_PLAN = E.ACAD_PLAN
     AND D.ACAD_SUB_PLAN = E.ACAD_SUB_PLAN
     AND D.EFFDT = (SELECT MAX(DD.EFFDT)
                      FROM SYSADM.PS_ACAD_SUBPLAN DD
                     WHERE DD.EMPLID = D.EMPLID
                       AND DD.ACAD_CAREER = D.ACAD_CAREER
                       AND DD.STDNT_CAR_NBR = D.STDNT_CAR_NBR
                       AND DD.ACAD_PLAN = D.ACAD_PLAN
                       AND DD.ACAD_SUB_PLAN = D.ACAD_SUB_PLAN
                       AND DD.EFFDT <= SYSDATE)
     AND D.EFFSEQ = (SELECT MAX(DS.EFFSEQ)
                       FROM SYSADM.PS_ACAD_SUBPLAN DS
                      WHERE DS.EMPLID = D.EMPLID
                        AND DS.ACAD_CAREER = D.ACAD_CAREER
                        AND DS.STDNT_CAR_NBR = D.STDNT_CAR_NBR
                        AND DS.ACAD_PLAN = D.ACAD_PLAN
                        AND DS.ACAD_SUB_PLAN = D.ACAD_SUB_PLAN
                        AND DS.EFFDT = D.EFFDT)
     AND E.EFFDT = (SELECT MAX(ED.EFFDT)
                      FROM SYSADM.PS_ACAD_SUBPLN_TBL ED
                     WHERE ED.INSTITUTION = E.INSTITUTION
                       AND ED.ACAD_PLAN = E.ACAD_PLAN
                       AND ED.ACAD_SUB_PLAN = E.ACAD_SUB_PLAN
                       AND ED.EFFDT <= SYSDATE)
     AND E.Eff_Status = 'A')
SELECT X.EMPLID,
       X.INSTITUTION,
       X.ACAD_CAREER,
       X.STDNT_CAR_NBR,
       X.ACAD_PROG,
       X.PROG_STATUS,
       X.STRM,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 3) ACAD_PLAN1,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 6) PLAN_DESCR1,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 7) ACAD_PLAN_TYPE1,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 8) DEGREE1,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 9) ACAD_SUB_PLAN1A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 10) SUB_PLAN_DESCR1A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 11) ACAD_SUB_PLAN1B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 12) SUB_PLAN_DESCR1B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 13) ACAD_SUB_PLAN1C,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 14) ACAD_SUB_PLAN_DESCR1C,
       to_date(nvl(REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1),
                                 '[^+]+',
                                 1,
                                 15),
                   '01/01/1901'),
               'MM/DD/YYYY') PLAN_DECLARE_DT1,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 1), '[^+]+', 1, 16) CIP_CODE1,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 3) ACAD_PLAN2,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 6) PLAN_DESCR2,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 7) ACAD_PLAN_TYPE2,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 8) DEGREE2,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 9) ACAD_SUB_PLAN2A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 10) SUB_PLAN_DESCR2A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 11) ACAD_SUB_PLAN2B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 12) SUB_PLAN_DESCR2B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 13) ACAD_SUB_PLAN2C,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 14) ACAD_SUB_PLAN_DESCR2C,
       to_date(nvl(REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2),
                                 '[^+]+',
                                 1,
                                 15),
                   '01/01/1901'),
               'MM/DD/YYYY') PLAN_DECLARE_DT2,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 2), '[^+]+', 1, 16) CIP_CODE2,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 3) ACAD_PLAN3,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 6) PLAN_DESCR3,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 7) ACAD_PLAN_TYPE3,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 8) DEGREE3,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 9) ACAD_SUB_PLAN3A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 10) SUB_PLAN_DESCR3A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 11) ACAD_SUB_PLAN3B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 12) SUB_PLAN_DESCR3B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 13) ACAD_SUB_PLAN3C,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 14) ACAD_SUB_PLAN_DESCR3C,
       to_date(nvl(REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3),
                                 '[^+]+',
                                 1,
                                 15),
                   '01/01/1901'),
               'MM/DD/YYYY') PLAN_DECLARE_DT3,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 3), '[^+]+', 1, 16) CIP_CODE3,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 3) ACAD_PLAN4,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 6) PLAN_DESCR4,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 7) ACAD_PLAN_TYPE4,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 8) DEGREE4,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 9) ACAD_SUB_PLAN4A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 10) SUB_PLAN_DESCR4A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 11) ACAD_SUB_PLAN4B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 12) SUB_PLAN_DESCR4B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 13) ACAD_SUB_PLAN4C,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 14) ACAD_SUB_PLAN_DESCR4C,
       to_date(nvl(REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4),
                                 '[^+]+',
                                 1,
                                 15),
                   '01/01/1901'),
               'MM/DD/YYYY') PLAN_DECLARE_DT4,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 4), '[^+]+', 1, 16) CIP_CODE4,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 3) ACAD_PLAN5,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 6) PLAN_DESCR5,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 7) ACAD_PLAN_TYPE5,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 8) DEGREE5,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 9) ACAD_SUB_PLAN5A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 10) SUB_PLAN_DESCR5A,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 11) ACAD_SUB_PLAN5B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 12) SUB_PLAN_DESCR5B,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 13) ACAD_SUB_PLAN5C,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 14) ACAD_SUB_PLAN_DESCR5C,
       to_date(nvl(REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5),
                                 '[^+]+',
                                 1,
                                 15),
                   '01/01/1901'),
               'MM/DD/YYYY') PLAN_DECLARE_DT5,
       REGEXP_SUBSTR(REGEXP_SUBSTR(x.fubar, '[^;]+', 1, 5), '[^+]+', 1, 16) CIP_CODE5
  FROM (SELECT DISTINCT B.EMPLID,
                        B.INSTITUTION,
                        B.ACAD_CAREER,
                        B.STDNT_CAR_NBR,
                        B.ACAD_PROG,
                        B.PROG_STATUS,
                        I.STRM,
                        listagg('START' || '+' || '01-JAN-1901' || '+' ||
                                nvl(REGEXP_REPLACE(B.ACAD_PLAN,
                                                   '[\+\;]+',
                                                   ''),
                                    ' ') || '+' || B.PLAN_SEQUENCE || '+' ||
                                '01-JAN-1901' || '+' ||
                                nvl(REGEXP_REPLACE(B.DESCR, '[\+\;]+', ''),
                                    ' ') || '+' ||
                                nvl(B.ACAD_PLAN_TYPE, ' ') || '+' ||
                                NVL(REGEXP_REPLACE(B.DEGREE, '[\+\;]+', ''),
                                    ' ') || '+' ||
                                NVL(REGEXP_REPLACE(C.ACAD_SUB_PLAN_A,
                                                   '[\+\;]+',
                                                   ''),
                                    ' ') || '+' ||
                                nvl(REGEXP_REPLACE(C.ACAD_SUB_PLAN_DESCR_A,
                                                   '[\+\;]+',
                                                   ''),
                                    ' ') || '+' ||
                                NVL(REGEXP_REPLACE(C.ACAD_SUB_PLAN_B,
                                                   '[\+\;]+',
                                                   ''),
                                    ' ') || '+' ||
                                nvl(REGEXP_REPLACE(C.ACAD_SUB_PLAN_DESCR_B,
                                                   '[\+\;]+',
                                                   ''),
                                    ' ') || '+' ||
                                NVL(REGEXP_REPLACE(C.ACAD_SUB_PLAN_C,
                                                   '[\+\;]+',
                                                   ''),
                                    ' ') || '+' ||
                                nvl(REGEXP_REPLACE(C.ACAD_SUB_PLAN_DESCR_C,
                                                   '[\+\;]+',
                                                   ''),
                                    ' ') || '+' ||
                                REGEXP_REPLACE(TO_CHAR(PLAN_DECLARE_DT,
                                                       'MM/DD/YYYY'),
                                               '[^/0-9]+',
                                               '') || '+' ||
                                nvl(REGEXP_REPLACE(B.CIP_CODE, '[\+\;]+', ''),
                                    ' ') || ' ',
                                ';') within GROUP(ORDER BY DECODE(B.ACAD_PLAN_TYPE, 'MAJ', '1', '9'), B.PLAN_DECLARE_DT) over(partition BY B.EMPLID, B.INSTITUTION, B.ACAD_CAREER, B.STDNT_CAR_NBR, B.ACAD_PROG, I.STRM) || '+' fubar
          FROM PLN_M B, SUBPLN C, SYSADM.PS_STDNT_CAR_TERM I
         WHERE B.EMPLID = I.EMPLID
           AND B.ACAD_CAREER = I.ACAD_CAREER
           AND B.STDNT_CAR_NBR = I.STDNT_CAR_NBR
           AND I.STRM >= '1210'
           AND B.EMPLID = C.EMPLID(+)
           AND B.ACAD_CAREER = C.ACAD_CAREER(+)
           AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR(+)
           AND B.ACAD_PLAN = C.ACAD_PLAN(+)
           AND B.EFFDT = C.EFFDT(+)
           AND B.EFFSEQ = C.EFFSEQ(+)) X

REPORT-461 - UM_FLT_PRGPLN - PROG/ PLAN by STRM incorrect Developer Wait REPORT-480 - identify the differences among those flat views Closed REPORT-483 - find out who else are using the flat views Closed