SELECT A.STDNT_GROUP, M.LAST_NAME, 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, I.ACAD_PLAN, J.ACAD_PLAN, K.ACAD_PLAN, D.TOT_PASSD_GPA, D.UNT_TAKEN_GPA, D.TOT_TAKEN_GPA, D.CUR_GPA, H.CUR_GPA, D.CUM_GPA, G.EMAIL_ADDR, B.EMAIL_ADDR, count( O.EMPLID) 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_STDNT_CAR_TERM H, PS_ACAD_PLAN I, PS_ACAD_PLAN J, PS_ACAD_PLAN K, PS_STDNT_ADVR_HIST L, PS_NAMES_FERPA_VW M, PS_CLASS_TBL_SE_VW O 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 C.PROG_STATUS = 'AC' AND D.EMPLID = H.EMPLID (+) AND D.ACAD_CAREER = H.ACAD_CAREER (+) AND D.INSTITUTION = H.INSTITUTION (+) AND :3 = H.STRM (+) AND C.EMPLID = I.EMPLID (+) AND C.ACAD_CAREER = I.ACAD_CAREER (+) AND C.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+) AND C.EFFSEQ = I.EFFSEQ (+) AND C.EFFDT = I.EFFDT (+) AND 10 = I.PLAN_SEQUENCE (+) AND C.EMPLID = J.EMPLID (+) AND C.ACAD_CAREER = J.ACAD_CAREER (+) AND C.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+) AND C.EFFSEQ = J.EFFSEQ (+) AND C.EFFDT = J.EFFDT (+) AND 20 = J.PLAN_SEQUENCE (+) AND C.EMPLID = K.EMPLID (+) AND C.ACAD_CAREER = K.ACAD_CAREER (+) AND C.STDNT_CAR_NBR = K.STDNT_CAR_NBR (+) AND C.EFFSEQ = K.EFFSEQ (+) AND C.EFFDT = K.EFFDT (+) AND 30 = K.PLAN_SEQUENCE (+) AND A.EMPLID = L.EMPLID AND A.INSTITUTION = L.INSTITUTION AND L.EFFDT = (SELECT MAX(L_ED.EFFDT) FROM PS_STDNT_ADVR_HIST L_ED WHERE L.EMPLID = L_ED.EMPLID AND L.INSTITUTION = L_ED.INSTITUTION AND L_ED.EFFDT <= SYSDATE) AND L.ADVISOR_ID = M.EMPLID AND L.ADVISOR_ROLE = 'ADVR' AND A.EMPLID = O.EMPLID (+) AND A.INSTITUTION = O.INSTITUTION (+) AND D.STRM <> O.STRM AND 'E' = O.STDNT_ENRL_STATUS (+) AND O.CATALOG_NBR LIKE '%H' AND NOT ( O.SUBJECT = 'FRO' AND O.CATALOG_NBR = '1000H') ) GROUP BY A.STDNT_GROUP, M.LAST_NAME, 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, I.ACAD_PLAN, J.ACAD_PLAN, K.ACAD_PLAN, D.TOT_PASSD_GPA, D.UNT_TAKEN_GPA, D.TOT_TAKEN_GPA, D.CUR_GPA, H.CUR_GPA, D.CUM_GPA, G.EMAIL_ADDR, B.EMAIL_ADDR ORDER BY 3, 4 |