SELECT DISTINCT A.STDNT_GROUP, B.LAST_NAME, B.FIRST_NAME, A.EMPLID, C.ADMIT_TERM, E.SRVC_IND_CD, E.SRVC_IND_REASON, F.SRVC_IND_CD, F.SRVC_IND_REASON, D.TOT_PASSD_GPA, D.UNT_TAKEN_GPA, D.TOT_TAKEN_GPA, D.CUR_GPA, D.CUM_GPA, G.EMAIL_ADDR, B.EMAIL_ADDR, count( H.CLASS_NBR), count( I.CLASS_NBR) FROM PS_STDNT_GRPS_HIST A, PS_CU_BIO_VW B, PS_ACAD_PROG C, PS_STDNT_CAR_TERM D, PS_SRVC_IND_DATA E, PS_SRVC_IND_DATA F, PS_EMAIL_FERPA_VW G, PS_CLASS_TBL_SE_VW H, PS_CLASS_TBL_SE_VW I WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_GRPS_HIST A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.INSTITUTION = A_ED.INSTITUTION AND A.STDNT_GROUP = A_ED.STDNT_GROUP AND A_ED.EFFDT <= SYSDATE) AND A.INSTITUTION = :1 AND A.EFF_STATUS = 'A' AND A.STDNT_GROUP IN ('MHC','BSCH','PROV') AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_ACAD_PROG C_ED WHERE C.EMPLID = C_ED.EMPLID AND C.ACAD_CAREER = C_ED.ACAD_CAREER AND C.STDNT_CAR_NBR = C_ED.STDNT_CAR_NBR AND C_ED.EFFDT <= SYSDATE) AND C.EFFSEQ = (SELECT MAX(C_ES.EFFSEQ) FROM PS_ACAD_PROG C_ES WHERE C.EMPLID = C_ES.EMPLID AND C.ACAD_CAREER = C_ES.ACAD_CAREER AND C.STDNT_CAR_NBR = C_ES.STDNT_CAR_NBR AND C.EFFDT = C_ES.EFFDT) AND C.EMPLID = D.EMPLID AND C.ACAD_CAREER = D.ACAD_CAREER AND C.STDNT_CAR_NBR = D.STDNT_CAR_NBR AND D.INSTITUTION = C.INSTITUTION AND D.STRM = :2 AND A.EMPLID = E.EMPLID (+) AND A.INSTITUTION = E.INSTITUTION (+) AND 'ACP' = E.SRVC_IND_CD (+) AND A.EMPLID = F.EMPLID (+) AND A.INSTITUTION = F.INSTITUTION (+) AND 'APS' = F.SRVC_IND_CD (+) AND A.EMPLID = G.EMPLID (+) AND 'CAMP' = G.E_ADDR_TYPE (+) AND D.EMPLID = H.EMPLID AND D.ACAD_CAREER = H.ACAD_CAREER AND D.INSTITUTION = H.INSTITUTION AND H.SUBJECT <> 'FRO' AND H.CATALOG_NBR LIKE '%H' AND H.STDNT_ENRL_STATUS = 'E' AND H.GRADE_POINTS > 0 AND D.EMPLID = I.EMPLID AND D.ACAD_CAREER = I.ACAD_CAREER AND D.INSTITUTION = I.INSTITUTION AND D.STRM = I.STRM AND I.SUBJECT <> 'FRO' AND I.CATALOG_NBR LIKE '%H' AND I.STDNT_ENRL_STATUS = 'E' GROUP BY A.STDNT_GROUP, B.LAST_NAME, B.FIRST_NAME, A.EMPLID, C.ADMIT_TERM, E.SRVC_IND_CD, E.SRVC_IND_REASON, F.SRVC_IND_CD, F.SRVC_IND_REASON, D.TOT_PASSD_GPA, D.UNT_TAKEN_GPA, D.TOT_TAKEN_GPA, D.CUR_GPA, D.CUM_GPA, G.EMAIL_ADDR, B.EMAIL_ADDR ORDER BY 2, 3 |