SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, A.ACAD_PROG, B.ACAD_PLAN, C.ADMIT_TYPE, D.EMAIL_ADDR, E.EMAIL_ADDR, ' ' FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, (SELECT TC.ADMIT_TYPE, TC.EMPLID, TC.ACAD_CAREER, TC.ADM_APPL_NBR, TC.INSTITUTION FROM PS_ADM_APPL_DATA TC,PS_ADM_APPL_SCTY C1 WHERE TC.EMPLID = C1.EMPLID AND TC.ACAD_CAREER = C1.ACAD_CAREER AND TC.ADM_APPL_NBR = C1.ADM_APPL_NBR AND C1.OPRCLASS = 'CUHCPPBAR01' AND C1.OPRID = '11002650' ) C, PS_CU_BIO_VW D, PS_EMAIL_FERPA_VW E WHERE ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG 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_ED.EFFDT <= SYSDATE) AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG 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.EFFDT = A_ES.EFFDT) AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.EFFSEQ = B.EFFSEQ AND A.EFFDT = B.EFFDT AND A.INSTITUTION = :1 AND A.ACAD_CAREER = :2 AND A.PROG_STATUS = 'AC' AND A.ADMIT_TERM BETWEEN :3 AND :4 AND A.EMPLID = D.EMPLID AND ( C.ADMIT_TYPE = :5 OR :5 IS NULL) AND A.EMPLID = C.EMPLID (+) AND A.ACAD_CAREER = C.ACAD_CAREER (+) AND A.ADM_APPL_NBR = C.ADM_APPL_NBR (+) AND A.INSTITUTION = C.INSTITUTION (+) AND A.ACAD_PROG <> 'NDEGU' 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.STDNT_GROUP FROM PS_ACAD_PROG F, PS_CU_BIO_VW G, PS_STDNT_GRPS_HIST H, (SELECT TI.ADMIT_TYPE, TI.EMPLID, TI.ACAD_CAREER, TI.ADM_APPL_NBR, TI.INSTITUTION FROM PS_ADM_APPL_DATA TI,PS_ADM_APPL_SCTY I1 WHERE TI.EMPLID = I1.EMPLID AND TI.ACAD_CAREER = I1.ACAD_CAREER AND TI.ADM_APPL_NBR = I1.ADM_APPL_NBR AND I1.OPRCLASS = 'CUHCPPBAR01' AND I1.OPRID = '11002650' ) I WHERE ( F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_ACAD_PROG F_ED WHERE F.EMPLID = F_ED.EMPLID AND F.ACAD_CAREER = F_ED.ACAD_CAREER AND F.STDNT_CAR_NBR = F_ED.STDNT_CAR_NBR AND F_ED.EFFDT <= SYSDATE) AND F.EFFSEQ = (SELECT MAX(F_ES.EFFSEQ) FROM PS_ACAD_PROG F_ES WHERE F.EMPLID = F_ES.EMPLID AND F.ACAD_CAREER = F_ES.ACAD_CAREER AND F.STDNT_CAR_NBR = F_ES.STDNT_CAR_NBR AND F.EFFDT = F_ES.EFFDT) AND F.EMPLID = G.EMPLID AND F.EMPLID = H.EMPLID AND H.INSTITUTION = F.INSTITUTION AND H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_STDNT_GRPS_HIST H_ED WHERE H.EMPLID = H_ED.EMPLID AND H.INSTITUTION = H_ED.INSTITUTION AND H.STDNT_GROUP = H_ED.STDNT_GROUP AND H_ED.EFFDT <= SYSDATE) AND H.EFF_STATUS = 'A' AND F.INSTITUTION = :1 AND F.ACAD_CAREER = :2 AND F.ADMIT_TERM BETWEEN :3 AND :4 AND F.EMPLID = I.EMPLID (+) AND F.ACAD_CAREER = I.ACAD_CAREER (+) AND F.ADM_APPL_NBR = I.ADM_APPL_NBR (+) AND F.INSTITUTION = I.INSTITUTION (+) AND ( I.ADMIT_TYPE = :5 OR :5 IS NULL) AND F.PROG_STATUS = 'AC' AND F.ACAD_PROG <> 'NDEGU' ) ORDER BY 1, 2, 3, 10 |