Query SQL:
SELECT K.LAST_NAME, K.FIRST_NAME, K.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, decode ( A.ACAD_LEVEL_BOT, '00', 'Not Set') ||
decode ( A.ACAD_LEVEL_BOT, '11', 'Lower Freshman') ||
decode ( A.ACAD_LEVEL_BOT, '15', 'Upper Freshman')||
decode ( A.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') ||
decode ( A.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( A.ACAD_LEVEL_BOT, '31', 'Lower Junior') ||
decode ( A.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( A.ACAD_LEVEL_BOT, '41', 'Lower Senior') ||
decode ( A.ACAD_LEVEL_BOT, '45', 'Upper Senior') ||
decode ( A.ACAD_LEVEL_BOT, '52', 'Second Degree') ||
decode ( A.ACAD_LEVEL_BOT, 'GR', 'Graduate'), B.ACAD_PROG, B.ADMIT_TERM, D.EMAIL_ADDR, E.ACAD_PLAN, F.ACAD_SUB_PLAN, G.ACAD_PLAN, H.ACAD_SUB_PLAN, I.ACAD_PLAN, J.ACAD_SUB_PLAN
FROM PS_STDNT_CAR_TERM A, PS_ACAD_PROG B, PS_SCC_EMAIL_QVW D, PS_ACAD_PLAN E, PS_ACAD_SUBPLAN F, PS_ACAD_PLAN G, PS_ACAD_SUBPLAN H, PS_ACAD_PLAN I, PS_ACAD_SUBPLAN J, PS_CU_BIO_VW K
WHERE ( A.INSTITUTION = :1
AND A.UNT_TAKEN_PRGRSS > 0
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.INSTITUTION = B.INSTITUTION
AND B.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PROG 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 <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PROG 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 A.ACAD_CAREER = :2
AND A.STRM = :3
AND A.EMPLID = D.EMPLID (+)
AND 'CAMP' = D.E_ADDR_TYPE (+)
AND B.EMPLID = E.EMPLID (+)
AND B.ACAD_CAREER = E.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = E.STDNT_CAR_NBR (+)
AND B.EFFSEQ = E.EFFSEQ (+)
AND B.EFFDT = E.EFFDT (+)
AND 10 = E.PLAN_SEQUENCE (+)
AND E.EMPLID = F.EMPLID (+)
AND E.ACAD_CAREER = F.ACAD_CAREER (+)
AND E.STDNT_CAR_NBR = F.STDNT_CAR_NBR (+)
AND E.EFFSEQ = F.EFFSEQ (+)
AND E.ACAD_PLAN = F.ACAD_PLAN (+)
AND E.EFFDT = F.EFFDT (+)
AND B.EMPLID = G.EMPLID (+)
AND B.ACAD_CAREER = G.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = G.STDNT_CAR_NBR (+)
AND B.EFFSEQ = G.EFFSEQ (+)
AND B.EFFDT = G.EFFDT (+)
AND 20 = G.PLAN_SEQUENCE (+)
AND G.EMPLID = H.EMPLID (+)
AND G.ACAD_CAREER = H.ACAD_CAREER (+)
AND G.STDNT_CAR_NBR = H.STDNT_CAR_NBR (+)
AND G.EFFSEQ = H.EFFSEQ (+)
AND G.ACAD_PLAN = H.ACAD_PLAN (+)
AND G.EFFDT = H.EFFDT (+)
AND B.EMPLID = I.EMPLID (+)
AND B.ACAD_CAREER = I.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND B.EFFSEQ = I.EFFSEQ (+)
AND B.EFFDT = I.EFFDT (+)
AND 30 = I.PLAN_SEQUENCE (+)
AND I.EMPLID = J.EMPLID (+)
AND I.ACAD_CAREER = J.ACAD_CAREER (+)
AND I.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+)
AND I.EFFSEQ = J.EFFSEQ (+)
AND I.ACAD_PLAN = J.ACAD_PLAN (+)
AND I.EFFDT = J.EFFDT (+)
AND A.EMPLID = K.EMPLID (+) )
ORDER BY 1, 2, 3
decode ( A.ACAD_LEVEL_BOT, '11', 'Lower Freshman') ||
decode ( A.ACAD_LEVEL_BOT, '15', 'Upper Freshman')||
decode ( A.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') ||
decode ( A.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( A.ACAD_LEVEL_BOT, '31', 'Lower Junior') ||
decode ( A.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( A.ACAD_LEVEL_BOT, '41', 'Lower Senior') ||
decode ( A.ACAD_LEVEL_BOT, '45', 'Upper Senior') ||
decode ( A.ACAD_LEVEL_BOT, '52', 'Second Degree') ||
decode ( A.ACAD_LEVEL_BOT, 'GR', 'Graduate'), B.ACAD_PROG, B.ADMIT_TERM, D.EMAIL_ADDR, E.ACAD_PLAN, F.ACAD_SUB_PLAN, G.ACAD_PLAN, H.ACAD_SUB_PLAN, I.ACAD_PLAN, J.ACAD_SUB_PLAN
FROM PS_STDNT_CAR_TERM A, PS_ACAD_PROG B, PS_SCC_EMAIL_QVW D, PS_ACAD_PLAN E, PS_ACAD_SUBPLAN F, PS_ACAD_PLAN G, PS_ACAD_SUBPLAN H, PS_ACAD_PLAN I, PS_ACAD_SUBPLAN J, PS_CU_BIO_VW K
WHERE ( A.INSTITUTION = :1
AND A.UNT_TAKEN_PRGRSS > 0
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.INSTITUTION = B.INSTITUTION
AND B.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PROG 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 <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PROG 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 A.ACAD_CAREER = :2
AND A.STRM = :3
AND A.EMPLID = D.EMPLID (+)
AND 'CAMP' = D.E_ADDR_TYPE (+)
AND B.EMPLID = E.EMPLID (+)
AND B.ACAD_CAREER = E.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = E.STDNT_CAR_NBR (+)
AND B.EFFSEQ = E.EFFSEQ (+)
AND B.EFFDT = E.EFFDT (+)
AND 10 = E.PLAN_SEQUENCE (+)
AND E.EMPLID = F.EMPLID (+)
AND E.ACAD_CAREER = F.ACAD_CAREER (+)
AND E.STDNT_CAR_NBR = F.STDNT_CAR_NBR (+)
AND E.EFFSEQ = F.EFFSEQ (+)
AND E.ACAD_PLAN = F.ACAD_PLAN (+)
AND E.EFFDT = F.EFFDT (+)
AND B.EMPLID = G.EMPLID (+)
AND B.ACAD_CAREER = G.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = G.STDNT_CAR_NBR (+)
AND B.EFFSEQ = G.EFFSEQ (+)
AND B.EFFDT = G.EFFDT (+)
AND 20 = G.PLAN_SEQUENCE (+)
AND G.EMPLID = H.EMPLID (+)
AND G.ACAD_CAREER = H.ACAD_CAREER (+)
AND G.STDNT_CAR_NBR = H.STDNT_CAR_NBR (+)
AND G.EFFSEQ = H.EFFSEQ (+)
AND G.ACAD_PLAN = H.ACAD_PLAN (+)
AND G.EFFDT = H.EFFDT (+)
AND B.EMPLID = I.EMPLID (+)
AND B.ACAD_CAREER = I.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND B.EFFSEQ = I.EFFSEQ (+)
AND B.EFFDT = I.EFFDT (+)
AND 30 = I.PLAN_SEQUENCE (+)
AND I.EMPLID = J.EMPLID (+)
AND I.ACAD_CAREER = J.ACAD_CAREER (+)
AND I.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+)
AND I.EFFSEQ = J.EFFSEQ (+)
AND I.ACAD_PLAN = J.ACAD_PLAN (+)
AND I.EFFDT = J.EFFDT (+)
AND A.EMPLID = K.EMPLID (+) )
ORDER BY 1, 2, 3