SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, 'Missing M1' FROM PS_ADM_MC_VW A, PS_STDNT_GRPS_HIST B, PS_CU_BIO_VW C 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 = 'BAR01' AND A.ACAD_CAREER = 'UGRD' AND A.EMPLID = B.EMPLID AND B.INSTITUTION = A.INSTITUTION AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.INSTITUTION = B_ED.INSTITUTION AND B.STDNT_GROUP = B_ED.STDNT_GROUP AND B_ED.EFFDT <= SYSDATE) AND B.STDNT_GROUP = 'SEEK' AND B.EFF_STATUS = 'A' AND A.ADMIT_TERM BETWEEN :1 AND :2 AND A.EMPLID = C.EMPLID AND NOT EXISTS (SELECT D.EMPLID FROM PS_STDNT_TEST_COMP D WHERE D.EMPLID = A.EMPLID AND D.TEST_ID = 'ACT-MATH-1' AND D.TEST_COMPONENT = 'M1') UNION SELECT F.LAST_NAME, F.FIRST_NAME, F.MIDDLE_NAME, E.EMPLID, E.ADMIT_TYPE, F.PHONE, F.EMAIL_ADDR, 'Missing M2' FROM PS_ADM_MC_VW E, PS_CU_BIO_VW F, PS_STDNT_GRPS_HIST G WHERE E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_ADM_MC_VW E_ED WHERE E.EMPLID = E_ED.EMPLID AND E.ACAD_CAREER = E_ED.ACAD_CAREER AND E.STDNT_CAR_NBR = E_ED.STDNT_CAR_NBR AND E.ADM_APPL_NBR = E_ED.ADM_APPL_NBR AND E.APPL_PROG_NBR = E_ED.APPL_PROG_NBR AND E_ED.EFFDT <= SYSDATE) AND E.EFFSEQ = (SELECT MAX(E_ES.EFFSEQ) FROM PS_ADM_MC_VW E_ES WHERE E.EMPLID = E_ES.EMPLID AND E.ACAD_CAREER = E_ES.ACAD_CAREER AND E.STDNT_CAR_NBR = E_ES.STDNT_CAR_NBR AND E.ADM_APPL_NBR = E_ES.ADM_APPL_NBR AND E.APPL_PROG_NBR = E_ES.APPL_PROG_NBR AND E.EFFDT = E_ES.EFFDT) AND E.INSTITUTION = 'BAR01' AND E.ACAD_CAREER = 'UGRD' AND E.ADMIT_TERM BETWEEN :1 AND :2 AND E.EMPLID = F.EMPLID AND E.EMPLID = G.EMPLID AND G.INSTITUTION = E.INSTITUTION AND G.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_STDNT_GRPS_HIST G_ED WHERE G.EMPLID = G_ED.EMPLID AND G.INSTITUTION = G_ED.INSTITUTION AND G.STDNT_GROUP = G_ED.STDNT_GROUP AND G_ED.EFFDT <= SYSDATE) AND G.STDNT_GROUP = 'SEEK' AND G.EFF_STATUS = 'A' AND NOT EXISTS (SELECT H.EMPLID FROM PS_STDNT_TEST_COMP H WHERE H.EMPLID = E.EMPLID AND H.TEST_ID = 'ACT-MATH-2' AND H.TEST_COMPONENT = 'M2') UNION SELECT J.LAST_NAME, J.FIRST_NAME, J.MIDDLE_NAME, I.EMPLID, I.ADMIT_TYPE, J.PHONE, J.EMAIL_ADDR, 'Missing CATW' FROM PS_ADM_MC_VW I, PS_CU_BIO_VW J, PS_STDNT_GRPS_HIST K WHERE I.EFFDT = (SELECT MAX(I_ED.EFFDT) FROM PS_ADM_MC_VW I_ED WHERE I.EMPLID = I_ED.EMPLID AND I.ACAD_CAREER = I_ED.ACAD_CAREER AND I.STDNT_CAR_NBR = I_ED.STDNT_CAR_NBR AND I.ADM_APPL_NBR = I_ED.ADM_APPL_NBR AND I.APPL_PROG_NBR = I_ED.APPL_PROG_NBR AND I_ED.EFFDT <= SYSDATE) AND I.EFFSEQ = (SELECT MAX(I_ES.EFFSEQ) FROM PS_ADM_MC_VW I_ES WHERE I.EMPLID = I_ES.EMPLID AND I.ACAD_CAREER = I_ES.ACAD_CAREER AND I.STDNT_CAR_NBR = I_ES.STDNT_CAR_NBR AND I.ADM_APPL_NBR = I_ES.ADM_APPL_NBR AND I.APPL_PROG_NBR = I_ES.APPL_PROG_NBR AND I.EFFDT = I_ES.EFFDT) AND I.EMPLID = J.EMPLID AND I.EMPLID = K.EMPLID AND K.INSTITUTION = I.INSTITUTION AND K.EFFDT = (SELECT MAX(K_ED.EFFDT) FROM PS_STDNT_GRPS_HIST K_ED WHERE K.EMPLID = K_ED.EMPLID AND K.INSTITUTION = K_ED.INSTITUTION AND K.STDNT_GROUP = K_ED.STDNT_GROUP AND K_ED.EFFDT <= SYSDATE) AND K.EFF_STATUS = 'A' AND K.STDNT_GROUP = 'SEEK' AND I.INSTITUTION = 'BAR01' AND I.ACAD_CAREER = 'UGRD' AND NOT EXISTS (SELECT L.EMPLID FROM PS_STDNT_TEST_COMP L WHERE L.EMPLID = I.EMPLID AND L.TEST_ID = 'CATW') AND I.ADMIT_TERM BETWEEN :1 AND :2 UNION SELECT N.LAST_NAME, N.FIRST_NAME, N.MIDDLE_NAME, M.EMPLID, M.ADMIT_TYPE, N.PHONE, N.EMAIL_ADDR, 'Missing CNY-READING' FROM PS_ADM_MC_VW M, PS_CU_BIO_VW N, PS_STDNT_GRPS_HIST O WHERE M.EFFDT = (SELECT MAX(M_ED.EFFDT) FROM PS_ADM_MC_VW M_ED WHERE M.EMPLID = M_ED.EMPLID AND M.ACAD_CAREER = M_ED.ACAD_CAREER AND M.STDNT_CAR_NBR = M_ED.STDNT_CAR_NBR AND M.ADM_APPL_NBR = M_ED.ADM_APPL_NBR AND M.APPL_PROG_NBR = M_ED.APPL_PROG_NBR AND M_ED.EFFDT <= SYSDATE) AND M.EFFSEQ = (SELECT MAX(M_ES.EFFSEQ) FROM PS_ADM_MC_VW M_ES WHERE M.EMPLID = M_ES.EMPLID AND M.ACAD_CAREER = M_ES.ACAD_CAREER AND M.STDNT_CAR_NBR = M_ES.STDNT_CAR_NBR AND M.ADM_APPL_NBR = M_ES.ADM_APPL_NBR AND M.APPL_PROG_NBR = M_ES.APPL_PROG_NBR AND M.EFFDT = M_ES.EFFDT) AND M.EMPLID = N.EMPLID AND M.EMPLID = O.EMPLID AND O.INSTITUTION = M.INSTITUTION AND O.EFFDT = (SELECT MAX(O_ED.EFFDT) FROM PS_STDNT_GRPS_HIST O_ED WHERE O.EMPLID = O_ED.EMPLID AND O.INSTITUTION = O_ED.INSTITUTION AND O.STDNT_GROUP = O_ED.STDNT_GROUP AND O_ED.EFFDT <= SYSDATE) AND O.STDNT_GROUP = 'SEEK' AND O.EFF_STATUS = 'A' AND M.INSTITUTION = 'BAR01' AND M.ACAD_CAREER = 'UGRD' AND M.ADMIT_TERM BETWEEN :1 AND :2 AND NOT EXISTS (SELECT P.EMPLID FROM PS_STDNT_TEST_COMP P WHERE P.EMPLID = M.EMPLID AND P.TEST_ID = 'CNY-READING') ORDER BY 1, 2, 3 |