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 |
