SELECT DISTINCT E.CUM_GPA, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, H.SEX, A.COMPLETION_TERM, A.DEGR_CHKOUT_STAT, A.EXP_GRAD_TERM, E.TOT_PASSD_PRGRSS, B.ACAD_PLAN, C.ACAD_SUB_PLAN, I.ACAD_PLAN, K.ACAD_SUB_PLAN, J.ACAD_PLAN, L.ACAD_SUB_PLAN, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_NAME_CURR_VW D, PS_STDNT_CAR_TERM E, PS_SF_STDNT_PRS_VW H, PS_ACAD_SUBPLAN C, PS_ACAD_PLAN I, PS_ACAD_PLAN J, PS_ACAD_SUBPLAN K, PS_ACAD_SUBPLAN L 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.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 A.INSTITUTION = :1 AND ( A.ACAD_PROG IN ('MPA','MSED','ADVCT') OR ( A.ACAD_PROG = 'UGRD' AND EXISTS (SELECT DISTINCT G.EMPLID FROM PS_SRVC_IND_DATA G WHERE G.EMPLID = A.EMPLID AND G.INSTITUTION = A.INSTITUTION AND G.SRVC_IND_CD = 'ACP' AND G.SRVC_IND_REASON = 'SPAF'))) AND (( A.PROG_STATUS = 'CM' AND A.COMPLETION_TERM BETWEEN :2 AND :3) OR ( A.PROG_STATUS = 'AC' AND A.EXP_GRAD_TERM BETWEEN :2 AND :3)) AND A.EMPLID = D.EMPLID 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 E.STRM = (SELECT MAX ( F.STRM) FROM PS_STDNT_CAR_TERM F WHERE F.EMPLID = E.EMPLID AND F.ACAD_CAREER = E.ACAD_CAREER AND F.INSTITUTION = E.INSTITUTION AND F.STDNT_CAR_NBR = E.STDNT_CAR_NBR) AND A.EMPLID = H.EMPLID (+) 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 = 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 20 = I.PLAN_SEQUENCE (+) AND I.EMPLID = K.EMPLID (+) AND I.ACAD_CAREER = K.ACAD_CAREER (+) AND I.STDNT_CAR_NBR = K.STDNT_CAR_NBR (+) AND I.EFFSEQ = K.EFFSEQ (+) AND I.ACAD_PLAN = K.ACAD_PLAN (+) AND I.EFFDT = K.EFFDT (+) AND A.EMPLID = J.EMPLID (+) AND A.ACAD_CAREER = J.ACAD_CAREER (+) AND A.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+) AND A.EFFSEQ = J.EFFSEQ (+) AND A.EFFDT = J.EFFDT (+) AND 30 = J.PLAN_SEQUENCE (+) AND J.EMPLID = L.EMPLID (+) AND J.ACAD_CAREER = L.ACAD_CAREER (+) AND J.STDNT_CAR_NBR = L.STDNT_CAR_NBR (+) AND J.EFFSEQ = L.EFFSEQ (+) AND J.ACAD_PLAN = L.ACAD_PLAN (+) AND J.EFFDT = L.EFFDT (+) ORDER BY 1 DESC, 2, 3, 4 |