SELECT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, C.PROG_ACTION, C.PROG_STATUS, C.ADMIT_TERM, D.ADMIT_TYPE, A.SRVC_IND_REASON, A.POS_SRVC_INDICATOR, B.PHONE, B.EMAIL_ADDR, E.EMAIL_ADDR FROM PS_SRVC_IND_DATA A, PS_CU_BIO_VW B, PS_ACAD_PROG C, (SELECT TD.ADMIT_TYPE, TD.EMPLID, TD.ACAD_CAREER, TD.ADM_APPL_NBR, TD.INSTITUTION FROM PS_ADM_APPL_DATA TD,PS_ADM_APPL_SCTY D1 WHERE TD.EMPLID = D1.EMPLID AND TD.ACAD_CAREER = D1.ACAD_CAREER AND TD.ADM_APPL_NBR = D1.ADM_APPL_NBR AND D1.OPRCLASS = 'CUHCPPBAR01' AND D1.OPRID = '11002650' ) D, PS_SCC_EMAIL_QVW E WHERE ( A.INSTITUTION = :1 AND A.SRVC_IND_CD = :2 AND A.EMPLID = B.EMPLID AND ( A.SRVC_IND_REASON = :3 OR :3 IS NULL) AND A.EMPLID = C.EMPLID 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 A.INSTITUTION = C.INSTITUTION AND C.EMPLID = D.EMPLID (+) AND C.ACAD_CAREER = D.ACAD_CAREER (+) AND C.ADM_APPL_NBR = D.ADM_APPL_NBR (+) AND C.INSTITUTION = D.INSTITUTION (+) AND A.EMPLID = E.EMPLID (+) AND 'CAMP' = E.E_ADDR_TYPE (+) ) UNION SELECT G.LAST_NAME, G.FIRST_NAME, G.MIDDLE_NAME, F.EMPLID, H.PROG_ACTION, H.PROG_STATUS, H.ADMIT_TERM, H.ADMIT_TYPE, F.SRVC_IND_REASON, F.POS_SRVC_INDICATOR, G.PHONE, G.EMAIL_ADDR, I.EMAIL_ADDR FROM PS_SRVC_IND_DATA F, PS_CU_BIO_VW G, PS_ADM_MC_VW H, PS_SCC_EMAIL_QVW I WHERE ( F.EMPLID = G.EMPLID AND F.EMPLID = H.EMPLID AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_ADM_MC_VW H_ED WHERE H.EMPLID = H_ED.EMPLID AND H.ACAD_CAREER = H_ED.ACAD_CAREER AND H.STDNT_CAR_NBR = H_ED.STDNT_CAR_NBR AND H.ADM_APPL_NBR = H_ED.ADM_APPL_NBR AND H.APPL_PROG_NBR = H_ED.APPL_PROG_NBR AND H_ED.EFFDT <= SYSDATE) AND H.EFFSEQ = (SELECT MAX(H_ES.EFFSEQ) FROM PS_ADM_MC_VW H_ES WHERE H.EMPLID = H_ES.EMPLID AND H.ACAD_CAREER = H_ES.ACAD_CAREER AND H.STDNT_CAR_NBR = H_ES.STDNT_CAR_NBR AND H.ADM_APPL_NBR = H_ES.ADM_APPL_NBR AND H.APPL_PROG_NBR = H_ES.APPL_PROG_NBR AND H.EFFDT = H_ES.EFFDT) AND F.EMPLID = I.EMPLID (+) AND 'CAMP' = I.E_ADDR_TYPE (+) AND F.INSTITUTION = :1 AND F.SRVC_IND_CD = :2 AND ( F.SRVC_IND_REASON = :3 OR :3 IS NULL) AND H.PROG_ACTION <> 'MATR' AND F.INSTITUTION = H.INSTITUTION ) ORDER BY 1, 2, 3, 9 |