SELECT DISTINCT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, C.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, B.PLAN_SEQUENCE, G.DESCR, G.DEGREE, G.SSR_NSC_CRD_LVL, decode ( F.ACAD_LEVEL_BOT, '00', 'Not Set') || decode ( F.ACAD_LEVEL_BOT, '11', 'Lower Freshman') || decode ( F.ACAD_LEVEL_BOT, '15', 'Upper Freshman')|| decode ( F.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') || decode ( F.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( F.ACAD_LEVEL_BOT, '31', 'Lower Junior') || decode ( F.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( F.ACAD_LEVEL_BOT, '41', 'Lower Senior') || decode ( F.ACAD_LEVEL_BOT, '45', 'Upper Senior') || decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') || decode ( F.ACAD_LEVEL_BOT, 'GR', 'Graduate'), F.UNT_TAKEN_PRGRSS, F.TOT_CUMULATIVE, F.TOT_PASSD_GPA, F.CUM_GPA, D.PHONE, D.EMAIL_ADDR, E.SRVC_IND_CD || ' ' || E.SRVC_IND_REASON, H.SRVC_IND_CD || ' ' || H.SRVC_IND_REASON,G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD') FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, (SELECT TC.ADMIT_TYPE, TC.EMPLID, TC.ACAD_CAREER, TC.STDNT_CAR_NBR, 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_STDNT_CAR_TERM F, PS_ACAD_PLAN_TBL G, PS_SRVC_IND_DATA E, PS_SRVC_IND_DATA H 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.PROG_STATUS = 'AC' AND A.EMPLID = C.EMPLID (+) AND A.ACAD_CAREER = C.ACAD_CAREER (+) AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR (+) AND A.ADM_APPL_NBR = C.ADM_APPL_NBR (+) AND A.INSTITUTION = C.INSTITUTION (+) AND A.EMPLID = D.EMPLID AND A.EMPLID = F.EMPLID AND A.ACAD_CAREER = F.ACAD_CAREER AND A.STDNT_CAR_NBR = F.STDNT_CAR_NBR AND A.INSTITUTION = F.INSTITUTION AND B.ACAD_PLAN = G.ACAD_PLAN AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED WHERE G.INSTITUTION = G_ED.INSTITUTION AND G.ACAD_PLAN = G_ED.ACAD_PLAN AND G_ED.EFFDT <= B.EFFDT) AND A.INSTITUTION = G.INSTITUTION AND A.ACAD_CAREER = :2 AND F.STRM = :3 AND F.UNT_TAKEN_PRGRSS > 0 AND ( G.DEGREE = :4 OR :4 IS NULL) AND ( A.ACAD_PROG = :5 OR :5 IS NULL) AND ( B.ACAD_PLAN = :6 OR :6 IS NULL) AND A.EMPLID = E.EMPLID (+) AND 'APS' = E.SRVC_IND_CD (+) AND A.INSTITUTION = E.INSTITUTION (+) AND A.EMPLID = H.EMPLID (+) AND A.INSTITUTION = H.INSTITUTION (+) AND 'ACP' = H.SRVC_IND_CD (+) ) ORDER BY 1, 2, 3, 10 |