SELECT DISTINCT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, B.EMPLID, D.ADMIT_TERM, D.EXP_GRAD_TERM, to_char ( G.UNT_TAKEN_FA, '999.9999'), to_char ( G.CUM_GPA, '99.9999'), D.PROG_STATUS, TO_CHAR(D.EFFDT,'YYYY-MM-DD'), D.ACAD_PROG, E.ACAD_PLAN, F.EMAIL_ADDR, ' ', ' ', ' ', ' ', ' ' FROM PS_TERM_TBL A, PS_STDNT_GRPS_HIST B, PS_CU_BIO_VW C, PS_ACAD_PROG D, PS_ACAD_PLAN E, PS_EMAIL_FERPA_VW F, PS_STDNT_CAR_TERM G WHERE A.INSTITUTION = :1 AND A.ACAD_CAREER = :2 AND A.STRM = :3 AND B.INSTITUTION = A.INSTITUTION AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.INSTITUTION = B_ED.INSTITUTION AND B.STDNT_GROUP = B_ED.STDNT_GROUP AND B_ED.EFFDT <= A.TERM_BEGIN_DT) AND B.EFF_STATUS = 'A' AND B.STDNT_GROUP = :4 AND B.EMPLID = C.EMPLID AND B.EMPLID = D.EMPLID AND B.INSTITUTION = D.INSTITUTION AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_ACAD_PROG D_ED WHERE D.EMPLID = D_ED.EMPLID AND D.ACAD_CAREER = D_ED.ACAD_CAREER AND D.STDNT_CAR_NBR = D_ED.STDNT_CAR_NBR AND D_ED.EFFDT <= A.TERM_BEGIN_DT) AND D.EFFSEQ = (SELECT MAX(D_ES.EFFSEQ) FROM PS_ACAD_PROG D_ES WHERE D.EMPLID = D_ES.EMPLID AND D.ACAD_CAREER = D_ES.ACAD_CAREER AND D.STDNT_CAR_NBR = D_ES.STDNT_CAR_NBR AND D.EFFDT = D_ES.EFFDT) AND D.EMPLID = E.EMPLID AND D.ACAD_CAREER = E.ACAD_CAREER AND D.STDNT_CAR_NBR = E.STDNT_CAR_NBR AND D.EFFSEQ = E.EFFSEQ AND D.EFFDT = E.EFFDT AND B.EMPLID = F.EMPLID (+) AND 'CAMP' = F.E_ADDR_TYPE (+) AND D.EMPLID = G.EMPLID AND D.ACAD_CAREER = G.ACAD_CAREER AND D.STDNT_CAR_NBR = G.STDNT_CAR_NBR AND G.INSTITUTION = D.INSTITUTION AND A.STRM = G.STRM UNION SELECT DISTINCT J.LAST_NAME, J.FIRST_NAME, J.MIDDLE_NAME, I.EMPLID, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', L.SUBJECT, L.CATALOG_NBR, L.CLASS_SECTION, L.SESSION_CODE, L.CRSE_GRADE_OFF FROM PS_TERM_TBL H, PS_STDNT_GRPS_HIST I, PS_CU_BIO_VW J, PS_ACAD_PROG K, PS_CLASS_TBL_SE_VW L WHERE H.INSTITUTION = :1 AND H.ACAD_CAREER = :2 AND H.STRM = :3 AND H.INSTITUTION = I.INSTITUTION AND I.EFFDT = (SELECT MAX(I_ED.EFFDT) FROM PS_STDNT_GRPS_HIST I_ED WHERE I.EMPLID = I_ED.EMPLID AND I.INSTITUTION = I_ED.INSTITUTION AND I.STDNT_GROUP = I_ED.STDNT_GROUP AND I_ED.EFFDT <= H.TERM_BEGIN_DT) AND I.EFF_STATUS = 'A' AND I.STDNT_GROUP = :4 AND I.EMPLID = J.EMPLID AND I.EMPLID = K.EMPLID AND I.INSTITUTION = K.INSTITUTION AND K.EFFDT = (SELECT MAX(K_ED.EFFDT) FROM PS_ACAD_PROG K_ED WHERE K.EMPLID = K_ED.EMPLID AND K.ACAD_CAREER = K_ED.ACAD_CAREER AND K.STDNT_CAR_NBR = K_ED.STDNT_CAR_NBR AND K_ED.EFFDT <= H.TERM_BEGIN_DT) AND K.EFFSEQ = (SELECT MAX(K_ES.EFFSEQ) FROM PS_ACAD_PROG K_ES WHERE K.EMPLID = K_ES.EMPLID AND K.ACAD_CAREER = K_ES.ACAD_CAREER AND K.STDNT_CAR_NBR = K_ES.STDNT_CAR_NBR AND K.EFFDT = K_ES.EFFDT) AND K.EMPLID = L.EMPLID AND K.ACAD_CAREER = L.ACAD_CAREER AND L.INSTITUTION = K.INSTITUTION AND L.STRM = H.STRM AND L.STDNT_ENRL_STATUS = 'E' ORDER BY 1, 2, 3, 14, 15, 16 |