SELECT E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, A.ACAD_PROG, G.ACAD_PLAN, H.ACAD_PLAN, A.ADMIT_TERM, B.STRM, B.CUM_GPA, B.TOT_TAKEN_GPA, B.TOT_CUMULATIVE, E.PHONE, F.EMAIL_ADDR FROM PS_ACAD_PROG A, PS_STDNT_CAR_TERM B, PS_CU_BIO_VW E, PS_EMAIL_FERPA_VW F, PS_ACAD_PLAN G, PS_ACAD_PLAN H 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 = 'UGRD' AND A.ACAD_PROG = 'UGRD' AND A.PROG_STATUS = 'AC' AND A.ADMIT_TERM BETWEEN :2 AND :3 AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND B.INSTITUTION = A.INSTITUTION AND B.STRM = (SELECT max ( C.STRM) FROM PS_STDNT_CAR_TERM C WHERE C.EMPLID = A.EMPLID AND C.ACAD_CAREER = B.ACAD_CAREER AND C.INSTITUTION = B.INSTITUTION AND C.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND C.STRM < :2) AND B.CUM_GPA < 2.0 AND NOT EXISTS (SELECT D.EMPLID FROM PS_STDNT_GRPS_HIST D WHERE D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_STDNT_GRPS_HIST D_ED WHERE D.EMPLID = D_ED.EMPLID AND D.INSTITUTION = D_ED.INSTITUTION AND D.STDNT_GROUP = D_ED.STDNT_GROUP AND D_ED.EFFDT <= SYSDATE) AND D.EMPLID = A.EMPLID AND D.INSTITUTION = A.INSTITUTION AND D.EFF_STATUS = 'A' AND D.STDNT_GROUP IN ('BSCH','CPIN','MHC','SEEK')) AND A.EMPLID = E.EMPLID AND A.EMPLID = F.EMPLID (+) AND 'CAMP' = F.E_ADDR_TYPE (+) 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 G.PLAN_SEQUENCE = 10 AND A.EMPLID = H.EMPLID (+) AND A.ACAD_CAREER = H.ACAD_CAREER (+) AND A.STDNT_CAR_NBR = H.STDNT_CAR_NBR (+) AND A.EFFSEQ = H.EFFSEQ (+) AND A.EFFDT = H.EFFDT (+) AND 20 = H.PLAN_SEQUENCE (+) AND B.TOT_TAKEN_GPA > 0 ORDER BY 1, 2, 3 |