Created by Corina C Larsen, last modified by Miki Yanagi on Dec 27, 2019
These two views have exactly the same fields and reference the same tables. The difference is the logic for the effective date. _STRM uses the term end date, and _CENS uses census date. These views do not use _SUBPLAN tables. UM_FLT_PRGPLN is the only _PRGPLAN view that uses ACAD_SUBPLAN and ACAD_SUBPLAN_TBL tables. This is one of four similar views. See UM_FLAT_PRGPLAN, UM_FLT_PRGPLN, as well as this comparison chart of the four.
REPORT-461
-
UM_FLT_PRGPLN - PROG/ PLAN by STRM incorrect
Developer Wait
| Key | Record.Fieldname | Format | XLAT | Heading Text |
|---|---|---|---|---|
| → | ACAD_CAREER - Academic Career | Char4 | N | Career |
| → | EMPLID - Empl ID | Char11 | ID | |
| → | INSTITUTION - Academic Institution | Char5 | Institution | |
| → | STDNT_CAR_NBR - Student Career Nbr | Num3.0 | Career Nbr | |
| → | STRM - Term | Char4 | Term | |
| 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_ACAD_PLAN6 - UM_ACAD_PLAN6 | Char10 | UM_ACAD_PLAN6 | ||
| UM_ACAD_PLAN7 - UM_ACAD_PLAN7 | Char10 | UM_ACAD_PLAN7 | ||
| 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_DESC6 - UM_ACAD_PLAN_DESC6 | Char30 | UM_ACAD_PLAN_D6 | ||
| UM_ACAD_PLAN_DESC7 - UM_ACAD_PLAN_DESC7 | Char30 | UM_ACAD_PLAN_D7 | ||
| 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_PLAN_TYP6 - UM_ACAD_PLAN_TYP6 | Char3 | UM_ACAD_PLAN_T6 | ||
| UM_ACAD_PLAN_TYP7 - UM_ACAD_PLAN_TYP7 | Char3 | UM_ACAD_PLAN_T7 | ||
| UM_ACAD_PROG1 - UM_ACAD_PROG1 | Char5 | UM_ACAD_PROG1 | ||
| UM_ACAD_PROG2 - UM_ACAD_PROG2 | Char5 | UM_ACAD_PROG2 | ||
| UM_ACAD_PROG3 - UM_ACAD_PROG3 | Char5 | UM_ACAD_PROG3 | ||
| UM_ACAD_PROG4 - UM_ACAD_PROG4 | Char5 | UM_ACAD_PROG4 | ||
| UM_ACAD_PROG5 - UM_ACAD_PROG5 | Char5 | UM_ACAD_PROG5 | ||
| UM_ACAD_PROG6 - UM_ACAD_PROG6 | Char5 | UM_ACAD_PROG6 | ||
| UM_ACAD_PROG7 - UM_ACAD_PROG7 | Char5 | UM_ACAD_PROG7 | ||
| 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_PLAN_SEQ6 - UM_PLAN_SEQ6 | Num2.0 | UM_PLAN_SEQ6 | ||
| UM_PLAN_SEQ7 - UM_PLAN_SEQ7 | Num2.0 | UM_PLAN_SEQ7 | ||
| UM_PROG_STATUS1 - PROG_STATUS1 | Char4 | N | PROG_STATUS1 | |
| UM_PROG_STATUS2 - PROG_STATUS2 | Char4 | N | PROG_STATUS2 | |
| UM_PROG_STATUS3 - PROG_STATUS3 | Char4 | N | PROG_STATUS3 | |
| UM_PROG_STATUS4 - PROG_STATUS4 | Char4 | N | PROG_STATUS4 | |
| UM_PROG_STATUS5 - PROG_STATUS5 | Char4 | N | PROG_STATUS5 | |
| UM_PROG_STATUS6 - PROG_STATUS6 | Char4 | N | PROG_STATUS6 | |
| UM_PROG_STATUS7 - PROG_STATUS7 | Char4 | N | PROG_STATUS7 |
SQL code for um_prgplan_cens Expand source
SELECT X3.EMPLID,
X3.INSTITUTION,
X3.ACAD_CAREER,
X3.STDNT_CAR_NBR,
X3.STRM,
SUBSTR(X3.S1, 3, 6),
SUBSTR(X3.S1, 10, 10),
SUBSTR(X3.S1, 21, 3),
TO_NUMBER(SUBSTR(X3.S1, 25, 3)),
SUBSTR(X3.S1, 29, 30),
SUBSTR(X3.S1, 60),
SUBSTR(X3.S2, 3, 6),
SUBSTR(X3.S2, 10, 10),
SUBSTR(X3.S2, 21, 3),
TO_NUMBER(SUBSTR(X3.S2, 25, 3)),
SUBSTR(X3.S2, 29, 30),
SUBSTR(X3.S2, 60),
SUBSTR(X3.S3, 3, 6),
SUBSTR(X3.S3, 10, 10),
SUBSTR(X3.S3, 21, 3),
TO_NUMBER(SUBSTR(X3.S3, 25, 3)),
SUBSTR(X3.S3, 29, 30),
SUBSTR(X3.S3, 60),
SUBSTR(X3.S4, 3, 6),
SUBSTR(X3.S4, 10, 10),
SUBSTR(X3.S4, 21, 3),
TO_NUMBER(SUBSTR(X3.S4, 25, 3)),
SUBSTR(X3.S4, 29, 30),
SUBSTR(X3.S4, 60),
SUBSTR(X3.S5, 3, 6),
SUBSTR(X3.S5, 10, 10),
SUBSTR(X3.S5, 21, 3),
TO_NUMBER(SUBSTR(X3.S5, 25, 3)),
SUBSTR(X3.S5, 29, 30),
SUBSTR(X3.S5, 60),
SUBSTR(X3.S6, 3, 6),
SUBSTR(X3.S6, 10, 10),
SUBSTR(X3.S6, 21, 3),
TO_NUMBER(SUBSTR(X3.S6, 25, 3)),
SUBSTR(X3.S6, 29, 30),
SUBSTR(X3.S6, 60),
SUBSTR(X3.S7, 3, 6),
SUBSTR(X3.S7, 10, 10),
SUBSTR(X3.S7, 21, 3),
TO_NUMBER(SUBSTR(X3.S7, 25, 3)),
SUBSTR(X3.S7, 29, 30),
SUBSTR(X3.S7, 60)
FROM (SELECT X2.EMPLID,
X2.INSTITUTION,
X2.ACAD_CAREER,
X2.STDNT_CAR_NBR,
X2.STRM,
MIN(X2.LEAD1) AS S1,
MIN(X2.LEAD2) AS S2,
MIN(X2.LEAD3) AS S3,
MIN(X2.LEAD4) AS S4,
MIN(X2.LEAD5) AS S5,
MIN(X2.LEAD6) AS S6,
MIN(X2.LEAD7) AS S7
FROM (SELECT X.EMPLID,
X.INSTITUTION,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 1 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD1,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 2 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD2,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 3 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD3,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 4 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD4,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 5 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD5,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 6 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD6,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 7 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD7
FROM (SELECT A.EMPLID,
A.INSTITUTION,
A.ACAD_CAREER,
D.STRM,
A.STDNT_CAR_NBR,
A.ACAD_PROG,
B.ACAD_PLAN,
B.PLAN_SEQUENCE,
C.DESCR,
C.ACAD_PLAN_TYPE,
A.PROG_STATUS
FROM PS_ACAD_PROG A,
PS_ACAD_PLAN B,
PS_ACAD_PLAN_TBL C,
PS_STDNT_CAR_TERM D,
PS_TERM_TBL E
WHERE 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.EFFDT =
(SELECT MAX(B_ED.EFFDT)
FROM PS_ACAD_PLAN B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.ACAD_CAREER = B_ED.ACAD_CAREER
AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
AND B_ED.EFFDT <=
TO_DATE(CASE
WHEN TO_CHAR(E.TERM_END_DT, 'MM') = '08' THEN
'08/15/'
WHEN TO_CHAR(E.TERM_END_DT, 'MM') = '12' THEN
'10/15/'
ELSE
'02/15/'
END ||
TO_CHAR(E.TERM_END_DT, 'YYYY'),
'MM/DD/YYYY'))
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ)
FROM PS_ACAD_PLAN B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.ACAD_CAREER = B_ES.ACAD_CAREER
AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
AND B.EFFDT = B_ES.EFFDT)
AND C.INSTITUTION = A.INSTITUTION
AND C.ACAD_PLAN = B.ACAD_PLAN
AND C.EFFDT =
(SELECT MAX(C1.EFFDT)
FROM PS_ACAD_PLAN_TBL C1
WHERE C1.INSTITUTION = C.INSTITUTION
AND C1.ACAD_PLAN = C.ACAD_PLAN
AND C1.EFFDT <=
TO_DATE(CASE
WHEN TO_CHAR(E.TERM_END_DT, 'MM') = '08' THEN
'08/15/'
WHEN TO_CHAR(E.TERM_END_DT, 'MM') = '12' THEN
'10/15/'
ELSE
'02/15/'
END ||
TO_CHAR(E.TERM_END_DT, 'YYYY'),
'MM/DD/YYYY'))
AND D.EMPLID = A.EMPLID
AND D.ACAD_CAREER = A.ACAD_CAREER
AND D.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND D.INSTITUTION = A.INSTITUTION
AND E.ACAD_CAREER = D.ACAD_CAREER
AND E.INSTITUTION = D.INSTITUTION
AND E.STRM = D.STRM) X
ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.ACAD_PROG,
X.ACAD_PLAN) X2
GROUP BY X2.EMPLID,
X2.INSTITUTION,
X2.ACAD_CAREER,
X2.STDNT_CAR_NBR,
X2.STRM) X3;
SQL code for um_prgplan_strm Expand source
SELECT X3.EMPLID,
X3.INSTITUTION,
X3.ACAD_CAREER,
X3.STDNT_CAR_NBR,
X3.STRM,
SUBSTR(X3.S1, 3, 6),
SUBSTR(X3.S1, 10, 10),
SUBSTR(X3.S1, 21, 3),
TO_NUMBER(SUBSTR(X3.S1, 25, 3)),
SUBSTR(X3.S1, 29, 30),
SUBSTR(X3.S1, 60),
SUBSTR(X3.S2, 3, 6),
SUBSTR(X3.S2, 10, 10),
SUBSTR(X3.S2, 21, 3),
TO_NUMBER(SUBSTR(X3.S2, 25, 3)),
SUBSTR(X3.S2, 29, 30),
SUBSTR(X3.S2, 60),
SUBSTR(X3.S3, 3, 6),
SUBSTR(X3.S3, 10, 10),
SUBSTR(X3.S3, 21, 3),
TO_NUMBER(SUBSTR(X3.S3, 25, 3)),
SUBSTR(X3.S3, 29, 30),
SUBSTR(X3.S3, 60),
SUBSTR(X3.S4, 3, 6),
SUBSTR(X3.S4, 10, 10),
SUBSTR(X3.S4, 21, 3),
TO_NUMBER(SUBSTR(X3.S4, 25, 3)),
SUBSTR(X3.S4, 29, 30),
SUBSTR(X3.S4, 60),
SUBSTR(X3.S5, 3, 6),
SUBSTR(X3.S5, 10, 10),
SUBSTR(X3.S5, 21, 3),
TO_NUMBER(SUBSTR(X3.S5, 25, 3)),
SUBSTR(X3.S5, 29, 30),
SUBSTR(X3.S5, 60),
SUBSTR(X3.S6, 3, 6),
SUBSTR(X3.S6, 10, 10),
SUBSTR(X3.S6, 21, 3),
TO_NUMBER(SUBSTR(X3.S6, 25, 3)),
SUBSTR(X3.S6, 29, 30),
SUBSTR(X3.S6, 60),
SUBSTR(X3.S7, 3, 6),
SUBSTR(X3.S7, 10, 10),
SUBSTR(X3.S7, 21, 3),
TO_NUMBER(SUBSTR(X3.S7, 25, 3)),
SUBSTR(X3.S7, 29, 30),
SUBSTR(X3.S7, 60)
FROM (SELECT X2.EMPLID,
X2.INSTITUTION,
X2.ACAD_CAREER,
X2.STDNT_CAR_NBR,
X2.STRM,
MIN(X2.LEAD1) AS S1,
MIN(X2.LEAD2) AS S2,
MIN(X2.LEAD3) AS S3,
MIN(X2.LEAD4) AS S4,
MIN(X2.LEAD5) AS S5,
MIN(X2.LEAD6) AS S6,
MIN(X2.LEAD7) AS S7
FROM (SELECT X.EMPLID,
X.INSTITUTION,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 1 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD1,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 2 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD2,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 3 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD3,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 4 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD4,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 5 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD5,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 6 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD6,
CASE
WHEN RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN) = 7 THEN
TO_CHAR(RANK() OVER(PARTITION BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.STRM,
X.PLAN_SEQUENCE,
X.ACAD_PROG,
X.ACAD_PLAN)) || ',' ||
RPAD(X.ACAD_PROG, 6, ' ') || ',' ||
RPAD(X.ACAD_PLAN, 10, ' ') || ',' ||
RPAD(X.ACAD_PLAN_TYPE, 3, ' ') || ',' ||
TO_CHAR(X.PLAN_SEQUENCE, '99') || ',' ||
RPAD(X.DESCR, 30, ' ') || ',' || X.PROG_STATUS
END AS LEAD7
FROM (SELECT A.EMPLID,
A.INSTITUTION,
A.ACAD_CAREER,
D.STRM,
A.STDNT_CAR_NBR,
A.ACAD_PROG,
B.ACAD_PLAN,
B.PLAN_SEQUENCE,
C.DESCR,
C.ACAD_PLAN_TYPE,
A.PROG_STATUS
FROM PS_ACAD_PROG A,
PS_ACAD_PLAN B,
PS_ACAD_PLAN_TBL C,
PS_STDNT_CAR_TERM D,
PS_TERM_TBL E
WHERE 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.EFFDT =
(SELECT MAX(B_ED.EFFDT)
FROM PS_ACAD_PLAN B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.ACAD_CAREER = B_ED.ACAD_CAREER
AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
AND B_ED.EFFDT <= TERM_END_DT)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ)
FROM PS_ACAD_PLAN B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.ACAD_CAREER = B_ES.ACAD_CAREER
AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
AND B.EFFDT = B_ES.EFFDT)
AND C.INSTITUTION = A.INSTITUTION
AND C.ACAD_PLAN = B.ACAD_PLAN
AND C.EFFDT =
(SELECT MAX(C1.EFFDT)
FROM PS_ACAD_PLAN_TBL C1
WHERE C1.INSTITUTION = C.INSTITUTION
AND C1.ACAD_PLAN = C.ACAD_PLAN
AND C1.EFFDT <= TERM_END_DT)
AND D.EMPLID = A.EMPLID
AND D.ACAD_CAREER = A.ACAD_CAREER
AND D.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND D.INSTITUTION = A.INSTITUTION
AND E.ACAD_CAREER = D.ACAD_CAREER
AND E.INSTITUTION = D.INSTITUTION
AND E.STRM = D.STRM) X
ORDER BY X.INSTITUTION,
X.EMPLID,
X.ACAD_CAREER,
X.STDNT_CAR_NBR,
X.ACAD_PROG,
X.ACAD_PLAN) X2
GROUP BY X2.EMPLID,
X2.INSTITUTION,
X2.ACAD_CAREER,
X2.STDNT_CAR_NBR,
X2.STRM) X3;