SELECT A.EMPLID, F.FIRST_NAME, F.MIDDLE_NAME, F.LAST_NAME, F.NAME_SUFFIX, S.FIRST_NAME, S.MIDDLE_NAME, S.LAST_NAME, S.NAME_SUFFIX, P.ADDRESS1, P.ADDRESS2, P.CITY, P.STATE, P.POSTAL, P.COUNTRY, F.PHONE, R.EMAIL_ADDR, Q.SEX, TO_CHAR(D.BIRTHDATE,'YYYY-MM-DD'), E.CUM_GPA, T.GPA_DEGREE, A.EXP_GRAD_TERM, T.COMPLETION_TERM, TO_CHAR(T.DEGR_CONFER_DT,'YYYY-MM-DD'), A.DEGR_CHKOUT_STAT, T.DEGREE, U.HONORS_CODE, A.ACAD_CAREER, case
when A.ACAD_PROG in ('ACVCT', 'MPA', 'MSED') THEN 'SPA'
WHEN A.ACAD_PROG = 'MBA' THEN 'Zicklin'
WHEN A.ACAD_PROG = 'MA' THEN 'Weissman'
WHEN B.ACAD_PLAN LIKE '%-BA' THEN 'Weissman'
WHEN B.ACAD_PLAN LIKE '%-BBA' THEN 'Zicklin'
WHEN B.ACAD_PLAN LIKE '%-BS' THEN 'SPA'
WHEN B.ACAD_PLAN IN ('FINENGR-MS', 'IOP-MS') THEN 'Weissman' ELSE 'Zicklin'
end, A.ACAD_PROG, B.ACAD_PLAN, C.ACAD_SUB_PLAN, G.ACAD_PLAN, H.ACAD_SUB_PLAN, I.ACAD_PLAN, J.ACAD_SUB_PLAN, K.ACAD_PLAN, L.ACAD_SUB_PLAN, M.ACAD_PLAN, N.ACAD_SUB_PLAN, A.ADMIT_TERM, E.UNT_TAKEN_PRGRSS, E.TOT_PASSD_PRGRSS, E.TOT_TRNSFR, E.TOT_CUMULATIVE, O.STDNT_GROUP
FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_STDNT_CAR_TERM E, PS_ACAD_SUBPLAN C, PS_ACAD_PLAN G, PS_ACAD_SUBPLAN H, PS_ACAD_PLAN I, PS_ACAD_SUBPLAN J, PS_ACAD_PLAN K, PS_ACAD_SUBPLAN L, PS_ACAD_PLAN M, PS_ACAD_SUBPLAN N, PS_STDNT_GRPS O, PS_ADDRESSES4_VW P, PS_SF_STDNT_PRS_VW Q, PS_ACAD_DEGR T, PS_ACAD_DEGR_HONS U, PS_CU_BIO_VW F, PS_EMAIL_FERPA_VW R, PS_NAMES_FERPA_VW S, PS_PERSNL_FERPA_VW D
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG 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 PS_ACAD_PROG 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.INSTITUTION = :1
AND A.EXP_GRAD_TERM = :2
AND ( A.ACAD_CAREER = :3
OR :3 IS NULL)
AND ( A.DEGR_CHKOUT_STAT = :4
OR :4 IS NULL)
AND A.EMPLID = E.EMPLID
AND A.ACAD_CAREER = E.ACAD_CAREER
AND A.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND E.INSTITUTION = A.INSTITUTION
AND A.EXP_GRAD_TERM = E.STRM
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.EFFSEQ = B.EFFSEQ
AND A.EFFDT = B.EFFDT
AND B.PLAN_SEQUENCE = 10
AND B.EMPLID = C.EMPLID (+)
AND B.ACAD_CAREER = C.ACAD_CAREER (+)
AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR (+)
AND B.EFFSEQ = C.EFFSEQ (+)
AND B.ACAD_PLAN = C.ACAD_PLAN (+)
AND B.EFFDT = C.EFFDT (+)
AND A.EMPLID = G.EMPLID (+)
AND A.ACAD_CAREER = G.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = G.STDNT_CAR_NBR (+)
AND A.EFFSEQ = G.EFFSEQ (+)
AND A.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 A.EMPLID = I.EMPLID (+)
AND A.ACAD_CAREER = I.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND A.EFFSEQ = I.EFFSEQ (+)
AND A.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 (+)
AND A.ACAD_CAREER = K.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = K.STDNT_CAR_NBR (+)
AND A.EFFSEQ = K.EFFSEQ (+)
AND A.EFFDT = K.EFFDT (+)
AND 40 = K.PLAN_SEQUENCE (+)
AND K.EMPLID = L.EMPLID (+)
AND K.ACAD_CAREER = L.ACAD_CAREER (+)
AND K.STDNT_CAR_NBR = L.STDNT_CAR_NBR (+)
AND K.EFFSEQ = L.EFFSEQ (+)
AND K.ACAD_PLAN = L.ACAD_PLAN (+)
AND K.EFFDT = L.EFFDT (+)
AND A.EMPLID = M.EMPLID (+)
AND A.ACAD_CAREER = M.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = M.STDNT_CAR_NBR (+)
AND A.EFFSEQ = M.EFFSEQ (+)
AND A.EFFDT = M.EFFDT (+)
AND 50 = M.PLAN_SEQUENCE (+)
AND M.EMPLID = N.EMPLID (+)
AND M.ACAD_CAREER = N.ACAD_CAREER (+)
AND M.STDNT_CAR_NBR = N.STDNT_CAR_NBR (+)
AND M.EFFSEQ = N.EFFSEQ (+)
AND M.ACAD_PLAN = N.ACAD_PLAN (+)
AND M.EFFDT = N.EFFDT (+)
AND A.EMPLID = O.EMPLID (+)
AND A.INSTITUTION = O.INSTITUTION (+)
AND ( 'SEEK' = O.STDNT_GROUP (+)
OR 'IEXP' = O.STDNT_GROUP (+))
AND A.EMPLID = P.EMPLID (+)
AND 'HOME' = P.ADDRESS_TYPE (+)
AND A.EMPLID = Q.EMPLID
AND A.EMPLID = T.EMPLID (+)
AND A.ACAD_CAREER = T.ACAD_CAREER (+)
AND A.INSTITUTION = T.INSTITUTION (+)
AND :2 = T.COMPLETION_TERM (+)
AND T.EMPLID = U.EMPLID (+)
AND T.STDNT_DEGR = U.STDNT_DEGR (+)
AND A.EMPLID = F.EMPLID (+)
AND A.EMPLID = R.EMPLID (+)
AND 'CAMP' = R.E_ADDR_TYPE (+)
AND A.EMPLID = S.EMPLID (+)
AND 'DEG' = S.NAME_TYPE (+)
AND A.EMPLID = D.EMPLID (+) )
ORDER BY 4, 2, 3