CU_BAR_AA_00001

SELECT E.FIRST_NAME, E.LAST_NAME, A.EMPLID, F.EMAIL_ADDR
FROM PS_ADM_MC_VW A, PS_EXT_ORG_TBL B, PS_CU_BIO_VW E, PS_SCC_EMAIL_QVW F
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ADM_MC_VW A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.ACAD_CAREER = A_ED.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
AND A.ADM_APPL_NBR = A_ED.ADM_APPL_NBR
AND A.APPL_PROG_NBR = A_ED.APPL_PROG_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ADM_MC_VW A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.ACAD_CAREER = A_ES.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
AND A.ADM_APPL_NBR = A_ES.ADM_APPL_NBR
AND A.APPL_PROG_NBR = A_ES.APPL_PROG_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.INSTITUTION = :1
AND A.ACAD_CAREER = :2
AND A.ADMIT_TERM = :3
AND A.ADMIT_TYPE = '3'
AND A.PROG_ACTION = 'MATR'
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_EXT_ORG_TBL B_ED
WHERE B.EXT_ORG_ID = B_ED.EXT_ORG_ID
AND B_ED.EFFDT <= SYSDATE)
AND A.LAST_SCH_ATTEND = B.EXT_ORG_ID
AND substr ( B.OTH_NAME_SORT_SRCH, 1, 4) <> 'CUNY'
AND NOT EXISTS (SELECT C.EMPLID
FROM PS_CLASS_TBL_SE_VW C
WHERE C.EMPLID = A.EMPLID
AND C.ACAD_CAREER = A.ACAD_CAREER
AND C.INSTITUTION = A.INSTITUTION
AND C.STRM >= A.ADMIT_TERM
AND C.STDNT_ENRL_STATUS = 'E'
AND C.CRSE_ID IN ('090524','090525','091697','091698','091873','091874','092297','092330','092331','094431','094432','094440','094441','094443','094444'))
AND NOT EXISTS (SELECT D.EMPLID
FROM PS_TRNS_CRSE_DTL D
WHERE D.EMPLID = A.EMPLID
AND D.ACAD_CAREER = A.ACAD_CAREER
AND D.INSTITUTION = A.INSTITUTION
AND D.CRSE_ID IN ('090524','090525','091697','091698','091873','091874','092297','092298','092330','092331','094431','094432','094440','094441','094443','094444'))
AND A.EMPLID = E.EMPLID
AND A.EMPLID = F.EMPLID (+)
AND 'CAMP' = F.E_ADDR_TYPE (+)
ORDER BY 2, 1