CU_BAR_AD_00003_2

SELECT DISTINCT CASE

WHEN  E.SCORE > 69 THEN ' '

WHEN  G.SCORE > 69 THEN ' '

WHEN  I.SCORE > 54 THEN ' '

ELSE 'NOEN'

END, A.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, K.NATIONAL_ID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, E.SCORE, TO_CHAR(E.TEST_DT,'YYYY-MM-DD'), E.SCORE_LETTER, G.SCORE, TO_CHAR(G.TEST_DT,'YYYY-MM-DD'), G.SCORE_LETTER, TO_CHAR(I.TEST_DT,'YYYY-MM-DD'), I.SCORE, I.SCORE_LETTER

  FROM PS_ADM_MC_VW A, PS_CU_BIO_VW C, PS_STDNT_TEST_COMP E, PS_STDNT_TEST_COMP G, PS_SCC_PERS_NI_QVW K, PS_STDNT_TEST_COMP I

  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 = :1

     AND A.ACAD_CAREER = :2

     AND A.ADMIT_TERM BETWEEN :3 AND :4

     AND ( A.ADMIT_TYPE = :5

     OR :5 IS NULL)

     AND A.ACAD_PROG = 'UGRD'

     AND A.EMPLID = C.EMPLID

     AND A.EMPLID = K.EMPLID

     AND NOT EXISTS (SELECT DISTINCT B.EMPLID

  FROM PS_STDNT_TEST_COMP B

  WHERE B.EMPLID = A.EMPLID

     AND B.TEST_ID = 'READ EXEMPT')

     AND NOT EXISTS (SELECT DISTINCT D.EMPLID

  FROM PS_STDNT_GRPS_HIST D

  WHERE D.EFFDT =

        (SELECT MAX(D_ED.EFFDT) FROM PS_STDNT_GRPS_HIST D_ED

        WHERE D.EMPLID = D_ED.EMPLID

          AND D.INSTITUTION = D_ED.INSTITUTION

          AND D.STDNT_GROUP = D_ED.STDNT_GROUP

          AND D_ED.EFFDT <= SYSDATE)

     AND D.EMPLID = A.EMPLID

     AND D.INSTITUTION = A.INSTITUTION

     AND D.EFF_STATUS = 'A'

     AND D.STDNT_GROUP = 'NOEN')

     AND A.EMPLID =  E.EMPLID (+)

     AND 'ACT-READING' =  E.TEST_ID (+)

     AND 'RAF' =  E.TEST_COMPONENT (+)

     AND A.EMPLID =  G.EMPLID (+)

     AND 'CNY-READING' =  G.TEST_ID (+)

     AND 'REF' =  G.TEST_COMPONENT (+)

     AND A.EMPLID =  I.EMPLID (+)

     AND 'ACC-READING' = I.TEST_ID

     AND 'ARF' = I.TEST_COMPONENT

     AND EXISTS (SELECT DISTINCT F.EMPLID

  FROM PS_STDNT_TEST_COMP F

  WHERE F.EMPLID = A.EMPLID

     AND F.TEST_COMPONENT IN ('RAF','REF','ARF'))

     AND NOT EXISTS (SELECT DISTINCT H.EMPLID

  FROM PS_STDNT_TEST_COMP H

  WHERE H.EMPLID = A.EMPLID

     AND H.TEST_COMPONENT IN ('RAF','REF','ARF')

     AND H.SCORE_LETTER = 'PASS'))

  ORDER BY 3, 4, 5