SELECT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, A.STDNT_CAR_NBR, A.ACAD_PROG, K.ACAD_PLAN, M.SRVC_IND_CD, M.SRVC_IND_REASON, P.SRVC_IND_CD, P.SRVC_IND_REASON, A.ADMIT_TERM, A.EXP_GRAD_TERM, C.STRM, C.UNT_TAKEN_PRGRSS, C.UNT_PASSD_PRGRSS, C.TOT_TAKEN_PRGRSS, C.TOT_PASSD_PRGRSS, C.CUR_GPA, C.CUM_GPA, to_char ( E.STDNT_CAR_NBR, '99.'), E.ACAD_CAREER, E.ACAD_PROG, E.PROG_STATUS FROM PS_ACAD_PROG A, PS_CU_BIO_VW B, PS_STDNT_CAR_TERM C, PS_ACAD_PROG E, PS_ACAD_PLAN K, PS_SRVC_IND_DATA M, PS_SRVC_IND_DATA P 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.ACAD_CAREER = :2 AND A.EMPLID = B.EMPLID AND A.PROG_STATUS = 'DC' AND A.ACAD_PROG = :3 AND A.EMPLID = C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND C.INSTITUTION = A.INSTITUTION AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND C.STRM = (SELECT max ( D.STRM) FROM PS_STDNT_CAR_TERM D WHERE ( D.EMPLID = C.EMPLID AND D.ACAD_CAREER = C.ACAD_CAREER AND D.INSTITUTION = C.INSTITUTION AND D.STDNT_CAR_NBR = C.STDNT_CAR_NBR )) AND A.EMPLID = E.EMPLID AND A.ACAD_CAREER = E.ACAD_CAREER AND A.STDNT_CAR_NBR <> E.STDNT_CAR_NBR AND A.INSTITUTION = E.INSTITUTION AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_ACAD_PROG E_ED WHERE E.EMPLID = E_ED.EMPLID AND E.ACAD_CAREER = E_ED.ACAD_CAREER AND E.STDNT_CAR_NBR = E_ED.STDNT_CAR_NBR AND E_ED.EFFDT <= SYSDATE) AND E.EFFSEQ = (SELECT MAX(E_ES.EFFSEQ) FROM PS_ACAD_PROG E_ES WHERE E.EMPLID = E_ES.EMPLID AND E.ACAD_CAREER = E_ES.ACAD_CAREER AND E.STDNT_CAR_NBR = E_ES.STDNT_CAR_NBR AND E.EFFDT = E_ES.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 E.PROG_STATUS <> 'CM' AND K.ACAD_PLAN NOT LIKE '%-MIN' AND A.EMPLID = M.EMPLID (+) AND A.INSTITUTION = M.INSTITUTION (+) AND 'APS' = M.SRVC_IND_CD (+) AND A.EMPLID = P.EMPLID (+) AND A.INSTITUTION = P.INSTITUTION (+) AND 'ACP' = P.SRVC_IND_CD (+) AND substr ( B.LAST_NAME, 1, 5) <> 'ZZZZZ' AND ( K.ACAD_PLAN = :4 OR :4 IS NULL) ) UNION SELECT G.LAST_NAME, G.FIRST_NAME, G.MIDDLE_NAME, F.EMPLID, F.ACAD_CAREER, F.STDNT_CAR_NBR, F.ACAD_PROG, L.ACAD_PLAN, N.SRVC_IND_CD, N.SRVC_IND_REASON, O.SRVC_IND_CD, O.SRVC_IND_REASON, F.ADMIT_TERM, F.EXP_GRAD_TERM, H.STRM, H.UNT_TAKEN_PRGRSS, H.UNT_PASSD_PRGRSS, H.TOT_TAKEN_PRGRSS, H.TOT_PASSD_PRGRSS, H.CUR_GPA, H.CUM_GPA, ' ', ' ', ' ', ' ' FROM PS_ACAD_PROG F, PS_CU_BIO_VW G, PS_STDNT_CAR_TERM H, PS_ACAD_PLAN L, PS_SRVC_IND_DATA N, PS_SRVC_IND_DATA O WHERE ( F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_ACAD_PROG F_ED WHERE F.EMPLID = F_ED.EMPLID AND F.ACAD_CAREER = F_ED.ACAD_CAREER AND F.STDNT_CAR_NBR = F_ED.STDNT_CAR_NBR AND F_ED.EFFDT <= SYSDATE) AND F.EFFSEQ = (SELECT MAX(F_ES.EFFSEQ) FROM PS_ACAD_PROG F_ES WHERE F.EMPLID = F_ES.EMPLID AND F.ACAD_CAREER = F_ES.ACAD_CAREER AND F.STDNT_CAR_NBR = F_ES.STDNT_CAR_NBR AND F.EFFDT = F_ES.EFFDT) AND F.EMPLID = G.EMPLID AND F.INSTITUTION = :1 AND F.ACAD_CAREER = :2 AND F.ACAD_PROG = :3 AND F.PROG_STATUS = 'DC' AND F.EMPLID = H.EMPLID AND F.ACAD_CAREER = H.ACAD_CAREER AND F.STDNT_CAR_NBR = H.STDNT_CAR_NBR AND F.INSTITUTION = H.INSTITUTION AND H.STRM = (SELECT max( I.STRM) FROM PS_STDNT_CAR_TERM I WHERE ( I.EMPLID = H.EMPLID AND I.ACAD_CAREER = H.ACAD_CAREER AND I.INSTITUTION = H.INSTITUTION AND I.STDNT_CAR_NBR = H.STDNT_CAR_NBR )) AND NOT EXISTS (SELECT J.EMPLID FROM PS_ACAD_PROG J WHERE ( J.EFFDT = (SELECT MAX(J_ED.EFFDT) FROM PS_ACAD_PROG J_ED WHERE J.EMPLID = J_ED.EMPLID AND J.ACAD_CAREER = J_ED.ACAD_CAREER AND J.STDNT_CAR_NBR = J_ED.STDNT_CAR_NBR AND J_ED.EFFDT <= SYSDATE) AND J.EFFSEQ = (SELECT MAX(J_ES.EFFSEQ) FROM PS_ACAD_PROG J_ES WHERE J.EMPLID = J_ES.EMPLID AND J.ACAD_CAREER = J_ES.ACAD_CAREER AND J.STDNT_CAR_NBR = J_ES.STDNT_CAR_NBR AND J.EFFDT = J_ES.EFFDT) AND J.EMPLID = F.EMPLID AND J.ACAD_CAREER = F.ACAD_CAREER AND J.STDNT_CAR_NBR <> F.STDNT_CAR_NBR AND J.INSTITUTION = F.INSTITUTION )) AND F.EMPLID = L.EMPLID AND F.ACAD_CAREER = L.ACAD_CAREER AND F.STDNT_CAR_NBR = L.STDNT_CAR_NBR AND F.EFFSEQ = L.EFFSEQ AND F.EFFDT = L.EFFDT AND L.ACAD_PLAN NOT LIKE '%-MIN' AND F.EMPLID = N.EMPLID (+) AND F.INSTITUTION = N.INSTITUTION (+) AND 'APS' = N.SRVC_IND_CD (+) AND F.EMPLID = O.EMPLID (+) AND F.INSTITUTION = O.INSTITUTION (+) AND 'ACP' = O.SRVC_IND_CD (+) AND substr ( G.LAST_NAME, 1, 5) <> 'ZZZZZ' AND ( L.ACAD_PLAN = :4 OR :4 IS NULL) ) ORDER BY 1, 2, 3 |