SELECT A.EMPLID, D.FIRST_NAME, D.LAST_NAME, A.ADMIT_TERM, A.ACAD_CAREER, A.INSTITUTION, A.ACAD_PROG, B.STRM, B.ACADEMIC_LOAD, B.UNT_TAKEN_GPA, B.UNT_PASSD_GPA, B.TOT_TAKEN_PRGRSS, B.CUR_GPA, B.CUM_GPA, D.EMAIL_ADDR FROM PS_ACAD_PROG A, PS_STDNT_CAR_TERM B, PS_CU_BIO_VW D 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 = 'BAR01' AND A.ACAD_CAREER = 'UGRD' AND A.ACAD_PROG = 'UGRD' AND A.PROG_STATUS = 'AC' 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 = :2 AND B.CUM_GPA >= 3.4 AND NOT EXISTS (SELECT C.EMPLID FROM PS_STDNT_GRPS_HIST C WHERE ( C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_GRPS_HIST C_ED WHERE C.EMPLID = C_ED.EMPLID AND C.INSTITUTION = C_ED.INSTITUTION AND C.STDNT_GROUP = C_ED.STDNT_GROUP AND C_ED.EFFDT <= SYSDATE) AND C.EMPLID = A.EMPLID AND C.INSTITUTION = A.INSTITUTION AND C.EFF_STATUS = 'A' AND C.STDNT_GROUP IN ('MHC','BSCH','PROV') )) AND A.ADMIT_TERM BETWEEN :1 AND :2 AND A.EMPLID = D.EMPLID ) |