SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, F.CUM_GPA, B.ADMIT_TERM, H.ADMIT_TYPE, B.ACAD_CAREER, B.ACAD_PROG, C.ACAD_PLAN, E.DESCR, D.EMAIL_ADDR,E.INSTITUTION,E.ACAD_PLAN,TO_CHAR(E.EFFDT,'YYYY-MM-DD') FROM PS_STDNT_GRPS_HIST A, PS_ACAD_PROG B, PS_ACAD_PLAN C, PS_CU_BIO_VW D, PS_ACAD_PLAN_TBL E, PS_STDNT_CAR_TERM F, (SELECT TH.ADMIT_TYPE, TH.EMPLID, TH.ACAD_CAREER, TH.STDNT_CAR_NBR, TH.ADM_APPL_NBR, TH.INSTITUTION FROM PS_ADM_APPL_DATA TH,PS_ADM_APPL_SCTY H1 WHERE TH.EMPLID = H1.EMPLID AND TH.ACAD_CAREER = H1.ACAD_CAREER AND TH.ADM_APPL_NBR = H1.ADM_APPL_NBR AND H1.OPRCLASS = 'CUHCPPBAR01' AND H1.OPRID = '11002650' ) H WHERE ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_GRPS_HIST A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.INSTITUTION = A_ED.INSTITUTION AND A.STDNT_GROUP = A_ED.STDNT_GROUP AND A_ED.EFFDT <= SYSDATE) AND A.INSTITUTION = :1 AND A.STDNT_GROUP = :2 AND A.EFF_STATUS = 'A' AND A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PROG B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.ACAD_CAREER = B_ED.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR AND B_ED.EFFDT <= SYSDATE) AND B.EFFSEQ = (SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PROG B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.ACAD_CAREER = B_ES.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR AND B.EFFDT = B_ES.EFFDT) AND B.PROG_STATUS = 'AC' AND B.EMPLID = C.EMPLID AND B.ACAD_CAREER = C.ACAD_CAREER AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND B.EFFSEQ = C.EFFSEQ AND B.EFFDT = C.EFFDT AND A.EMPLID = D.EMPLID AND C.ACAD_PLAN = E.ACAD_PLAN AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_ACAD_PLAN_TBL E_ED WHERE E.INSTITUTION = E_ED.INSTITUTION AND E.ACAD_PLAN = E_ED.ACAD_PLAN AND E_ED.EFFDT <= SYSDATE) AND B.INSTITUTION = E.INSTITUTION AND B.EMPLID = F.EMPLID AND B.ACAD_CAREER = F.ACAD_CAREER AND B.STDNT_CAR_NBR = F.STDNT_CAR_NBR AND F.INSTITUTION = B.INSTITUTION AND F.STRM = :3 AND NOT EXISTS (SELECT G.EMPLID FROM PS_STDNT_ENRL G WHERE ( G.EMPLID = F.EMPLID AND G.ACAD_CAREER = F.ACAD_CAREER AND G.INSTITUTION = F.INSTITUTION AND G.STRM = F.STRM AND G.STDNT_ENRL_STATUS = 'E' )) AND B.EMPLID = H.EMPLID (+) AND B.ACAD_CAREER = H.ACAD_CAREER (+) AND B.STDNT_CAR_NBR = H.STDNT_CAR_NBR (+) AND B.ADM_APPL_NBR = H.ADM_APPL_NBR (+) AND B.INSTITUTION = H.INSTITUTION (+) ) ORDER BY 1, 2, 3 |