SELECT G.LAST_NAME, G.FIRST_NAME, G.MIDDLE_NAME, D.EMPLID, E.ACAD_PROG, D.CUM_GPA, E.ADMIT_TERM, D.CUM_RESIDENT_TERMS, G.PHONE, G.EMAIL_ADDR FROM PS_TERM_TBL A, PS_TERM_TBL B, PS_STDNT_CAR_TERM D, PS_ACAD_PROG E, PS_CU_BIO_VW G, PS_STDNT_GRPS_HIST F WHERE A.INSTITUTION = :1 AND A.ACAD_CAREER = :2 AND A.STRM = :3 AND A.INSTITUTION = B.INSTITUTION AND A.ACAD_CAREER = B.ACAD_CAREER AND B.STRM = (SELECT max ( C.STRM) FROM PS_TERM_TBL C WHERE C.INSTITUTION = B.INSTITUTION AND C.ACAD_CAREER = B.ACAD_CAREER AND C.STRM < A.STRM AND substr ( C.STRM, 4, 1) <> '6') AND A.INSTITUTION = D.INSTITUTION AND A.ACAD_CAREER = D.ACAD_CAREER AND A.STRM = D.STRM AND D.EMPLID = E.EMPLID AND D.ACAD_CAREER = E.ACAD_CAREER AND D.INSTITUTION = E.INSTITUTION AND E.STDNT_CAR_NBR = D.STDNT_CAR_NBR 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 <= A.TERM_BEGIN_DT) 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 E.ACAD_PROG = 'UGRD' AND E.PROG_STATUS = 'AC' AND E.ADMIT_TERM <= B.STRM AND D.EMPLID = G.EMPLID AND ( EXISTS (SELECT H.EMPLID FROM PS_STDNT_CAR_TERM H WHERE H.EMPLID = E.EMPLID AND H.ACAD_CAREER = E.ACAD_CAREER AND H.STDNT_CAR_NBR = E.STDNT_CAR_NBR AND H.INSTITUTION = E.INSTITUTION AND H.STRM = B.STRM AND H.UNT_TAKEN_GPA = 0 AND H.UNT_TAKEN_PRGRSS = 0) OR NOT EXISTS (SELECT I.EMPLID FROM PS_STDNT_CAR_TERM I WHERE I.EMPLID = E.EMPLID AND I.ACAD_CAREER = E.ACAD_CAREER AND I.INSTITUTION = E.INSTITUTION AND I.STDNT_CAR_NBR = E.STDNT_CAR_NBR AND I.STRM = B.STRM)) AND ( D.UNT_TAKEN_PRGRSS > 0 OR D.UNT_TAKEN_GPA > 0) AND E.EMPLID = F.EMPLID AND F.INSTITUTION = E.INSTITUTION AND F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_STDNT_GRPS_HIST F_ED WHERE F.EMPLID = F_ED.EMPLID AND F.INSTITUTION = F_ED.INSTITUTION AND F.STDNT_GROUP = F_ED.STDNT_GROUP AND F_ED.EFFDT <= A.TERM_BEGIN_DT) AND F.EFF_STATUS = 'A' AND F.STDNT_GROUP = :4 ORDER BY 1, 2, 3 |