SELECT B.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, TO_CHAR(B.EFFDT,'YYYY-MM-DD'), C.EMAIL_ADDR, D.EMAIL_ADDR, E.ADMIT_TERM, F.ADMIT_TYPE, G.TOT_TRNSFR, G.TOT_CUMULATIVE FROM PS_TERM_TBL A, PS_STDNT_GRPS_HIST B, PS_CU_BIO_VW C, PS_EMAIL_FERPA_VW D, PS_ACAD_PROG E, (SELECT TF.ADMIT_TYPE, TF.EMPLID, TF.ACAD_CAREER, TF.ADM_APPL_NBR FROM PS_ADM_APPL_DATA TF,PS_ADM_APPL_SCTY F1 WHERE TF.EMPLID = F1.EMPLID AND TF.ACAD_CAREER = F1.ACAD_CAREER AND TF.ADM_APPL_NBR = F1.ADM_APPL_NBR AND F1.OPRCLASS = 'CUHCPPBAR01' AND F1.OPRID = '11002650' ) F, PS_STDNT_CAR_TERM G WHERE ( A.INSTITUTION = :1 AND A.ACAD_CAREER = :2 AND A.STRM = :3 AND A.INSTITUTION = B.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 'CAMP' = D.E_ADDR_TYPE (+) AND B.EMPLID = E.EMPLID AND B.INSTITUTION = E.INSTITUTION 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 A.ACAD_CAREER = E.ACAD_CAREER AND E.EMPLID = F.EMPLID (+) AND E.ACAD_CAREER = F.ACAD_CAREER (+) AND E.ADM_APPL_NBR = F.ADM_APPL_NBR (+) AND E.EMPLID = G.EMPLID AND E.ACAD_CAREER = G.ACAD_CAREER AND E.STDNT_CAR_NBR = G.STDNT_CAR_NBR AND G.INSTITUTION = E.INSTITUTION AND G.STRM = :3 ) ORDER BY 2, 3, 4 |