Results
SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.EMPLID, B.ACAD_PROG, C.ACAD_PLAN, B.PROG_ACTION, TO_CHAR(B.ACTION_DT,'YYYY-MM-DD'), A.ADMIT_TYPE, B.ADMIT_TERM, D.PHONE, D.EMAIL_ADDR, E.TEST_ID, E.TEST_COMPONENT, TO_CHAR(E.TEST_DT,'YYYY-MM-DD'), to_char ( E.SCORE, '999,999.99') FROM PS_ADM_APPL_DATA A, PS_ADM_APPL_SCTY A1, PS_ADM_APPL_PROG B, PS_ADM_MAINT_SCTY B1, PS_ADM_APPL_PLAN C, PS_CU_BIO_VW D, PS_STDNT_TEST_COMP E WHERE A.EMPLID = A1.EMPLID AND A.ACAD_CAREER = A1.ACAD_CAREER AND A.ADM_APPL_NBR = A1.ADM_APPL_NBR AND A1.OPRCLASS = 'CUHCPPBAR01' AND A1.OPRID = '11002650' AND B.EMPLID = B1.EMPLID AND B.ACAD_CAREER = B1.ACAD_CAREER AND B.STDNT_CAR_NBR = B1.STDNT_CAR_NBR AND B.ADM_APPL_NBR = B1.ADM_APPL_NBR AND B.APPL_PROG_NBR = B1.APPL_PROG_NBR AND B1.OPRCLASS = 'CUHCPPBAR01' AND B1.OPRID = '11002650' AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.ADM_APPL_NBR = B.ADM_APPL_NBR AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ADM_APPL_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.ADM_APPL_NBR = B_ED.ADM_APPL_NBR AND B.APPL_PROG_NBR = B_ED.APPL_PROG_NBR AND B_ED.EFFDT <= SYSDATE) AND B.EFFSEQ = (SELECT MAX(B_ES.EFFSEQ) FROM PS_ADM_APPL_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.ADM_APPL_NBR = B_ES.ADM_APPL_NBR AND B.APPL_PROG_NBR = B_ES.APPL_PROG_NBR AND B.EFFDT = B_ES.EFFDT) AND B.EMPLID = C.EMPLID AND B.ACAD_CAREER = C.ACAD_CAREER AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND B.ADM_APPL_NBR = C.ADM_APPL_NBR AND B.APPL_PROG_NBR = C.APPL_PROG_NBR AND B.EFFSEQ = C.EFFSEQ AND B.EFFDT = C.EFFDT AND A.INSTITUTION = :1 AND B.ACAD_CAREER = :2 AND B.ADMIT_TERM BETWEEN :3 AND :4 AND ( A.ADMIT_TYPE = :5 OR :5 IS NULL) AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND ( E.TEST_ID IN ('ACT-MATH-1','ACT-MATH-2','ACT-MATH-3','BAR-MATH','CNY-MATH','CNY-MATH-PL','CPT-MA','MATH-EXEMPT') OR E.TEST_COMPONENT IN ('1C','2C','BARMA','GEOM','M1','M2','MA1','MA2','MA3','MA4','MA5','MAB','MAF','MAPF','MAT','MATH','MATHA','MATHB','MBC','MBCSC','MPL1','MPL2','MPL3','MPL4','MPL5','MR')) UNION SELECT I.LAST_NAME, I.FIRST_NAME, I.MIDDLE_NAME, F.EMPLID, G.ACAD_PROG, H.ACAD_PLAN, G.PROG_ACTION, TO_CHAR(G.ACTION_DT,'YYYY-MM-DD'), F.ADMIT_TYPE, G.ADMIT_TERM, I.PHONE, I.EMAIL_ADDR, ' ', ' ', ' ', ' ' FROM PS_ADM_APPL_DATA F, PS_ADM_APPL_SCTY F1, PS_ADM_APPL_PROG G, PS_ADM_MAINT_SCTY G1, PS_ADM_APPL_PLAN H, PS_CU_BIO_VW I WHERE F.EMPLID = F1.EMPLID AND F.ACAD_CAREER = F1.ACAD_CAREER AND F.ADM_APPL_NBR = F1.ADM_APPL_NBR AND F1.OPRCLASS = 'CUHCPPBAR01' AND F1.OPRID = '11002650' AND G.EMPLID = G1.EMPLID AND G.ACAD_CAREER = G1.ACAD_CAREER AND G.STDNT_CAR_NBR = G1.STDNT_CAR_NBR AND G.ADM_APPL_NBR = G1.ADM_APPL_NBR AND G.APPL_PROG_NBR = G1.APPL_PROG_NBR AND G1.OPRCLASS = 'CUHCPPBAR01' AND G1.OPRID = '11002650' AND F.EMPLID = G.EMPLID AND F.ACAD_CAREER = G.ACAD_CAREER AND F.STDNT_CAR_NBR = G.STDNT_CAR_NBR AND F.ADM_APPL_NBR = G.ADM_APPL_NBR AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_ADM_APPL_PROG G_ED WHERE G.EMPLID = G_ED.EMPLID AND G.ACAD_CAREER = G_ED.ACAD_CAREER AND G.STDNT_CAR_NBR = G_ED.STDNT_CAR_NBR AND G.ADM_APPL_NBR = G_ED.ADM_APPL_NBR AND G.APPL_PROG_NBR = G_ED.APPL_PROG_NBR AND G_ED.EFFDT <= SYSDATE) AND G.EFFSEQ = (SELECT MAX(G_ES.EFFSEQ) FROM PS_ADM_APPL_PROG G_ES WHERE G.EMPLID = G_ES.EMPLID AND G.ACAD_CAREER = G_ES.ACAD_CAREER AND G.STDNT_CAR_NBR = G_ES.STDNT_CAR_NBR AND G.ADM_APPL_NBR = G_ES.ADM_APPL_NBR AND G.APPL_PROG_NBR = G_ES.APPL_PROG_NBR AND G.EFFDT = G_ES.EFFDT) AND G.EMPLID = H.EMPLID AND G.ACAD_CAREER = H.ACAD_CAREER AND G.STDNT_CAR_NBR = H.STDNT_CAR_NBR AND G.ADM_APPL_NBR = H.ADM_APPL_NBR AND G.APPL_PROG_NBR = H.APPL_PROG_NBR AND G.EFFSEQ = H.EFFSEQ AND G.EFFDT = H.EFFDT AND F.INSTITUTION = :1 AND F.ACAD_CAREER = :2 AND G.ADMIT_TERM BETWEEN :3 AND :4 AND ( F.ADMIT_TYPE = :5 OR :5 IS NULL) AND F.EMPLID = I.EMPLID AND NOT EXISTS (SELECT J.EMPLID FROM PS_STDNT_TEST_COMP J WHERE J.EMPLID = F.EMPLID AND ( J.TEST_ID IN ('ACT-MATH-1','ACT-MATH-2','ACT-MATH-3','BAR-MATH','CNY-MATH','CNY-MATH-PL','CPT-MA','MATH-EXEMPT') OR J.TEST_COMPONENT IN ('1C','2C','BARMA','GEOM','M1','M2','MA1','MA2','MA3','MA4','MA5','MAB','MAF','MAPF','MAT','MATH','MATHA','MATHB','MBC','MBCSC','MPL1','MPL2','MPL3','MPL4','MPL5','MR'))) ORDER BY 1, 2, 3, 13, 14 | |||||||||||||||||||||||