CU_BAR_AD_00006C

SELECT DISTINCT CASE

WHEN  D.SCORE BETWEEN 100 AND 120 THEN 'M261'

WHEN  D.SCORE BETWEEN 80 AND 99 THEN 'M227'

WHEN  D.SCORE BETWEEN 60 AND 79 THEN 'M203'

WHEN  D.SCORE BETWEEN 48 AND 59 THEN 'M103'

WHEN  D.SCORE BETWEEN 26 AND 47 THEN 'M120'

WHEN  D.SCORE BETWEEN 20 AND 25 THEN 'MATH'

ELSE 'EROR'

END, A.EMPLID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, K.NATIONAL_ID, A.ADMIT_TYPE, C.PHONE, C.EMAIL_ADDR, D.TEST_ID, TO_CHAR(D.TEST_DT,'YYYY-MM-DD'),  D12X.XLATSHORTNAME, D.SCORE

  FROM PS_ADM_MC_VW A, PS_CU_BIO_VW C, PS_SCC_PERS_NI_QVW K, PS_STDNT_TEST_COMP D LEFT OUTER JOIN PSXLATITEM D12X ON D12X.FIELDNAME='LS_DATA_SOURCE' AND D12X.FIELDVALUE=D.LS_DATA_SOURCE AND D12X.EFF_STATUS = 'A' AND D12X.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME=D12X.FIELDNAME AND TB.FIELDVALUE=D12X.FIELDVALUE AND  TB.EFF_STATUS = 'A' AND TB.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') )

  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 A.EMPLID = D.EMPLID

     AND D.TEST_ID = 'ACC-MATH-6'

     AND D.LS_DATA_SOURCE IN ('S02')

     AND D.TEST_COMPONENT IN ('M6')

     AND D.TEST_DT = (SELECT max ( G.TEST_DT)

  FROM PS_STDNT_TEST_COMP G

  WHERE G.EMPLID = D.EMPLID

     AND G.TEST_DT > SYSDATE - 730

     AND G.TEST_ID = 'ACC-MATH-6'

     AND G.TEST_COMPONENT IN ('M6')

     AND G.LS_DATA_SOURCE = D.LS_DATA_SOURCE)

     AND NOT EXISTS (SELECT B.EMPLID

  FROM PS_STDNT_GRPS_HIST B

  WHERE 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.EMPLID = A.EMPLID

     AND B.INSTITUTION = A.INSTITUTION

     AND B.STDNT_GROUP IN ('S100','M120','M103','M203','M227','M261','M100','NTMT')

     AND B.EFF_STATUS = 'A')

     AND NOT EXISTS (SELECT E.EMPLID

  FROM PS_STDNT_TEST_COMP E

  WHERE A.EMPLID = E.EMPLID

     AND E.TEST_ID = 'RGNTS'

     AND E.TEST_COMPONENT IN ('TRGCC','TRIG'))

     AND ( EXISTS (SELECT F.EMPLID

  FROM PS_STDNT_TEST_COMP F

  WHERE F.EMPLID = A.EMPLID

     AND F.TEST_ID = 'MATH EXEMPT'

     AND F.TEST_COMPONENT IN ('B','C','Q','R','S','T','E')

     AND F.LS_DATA_SOURCE IN ('S02'))

     OR EXISTS (SELECT I.EMPLID

  FROM PS_STDNT_TEST_COMP I

  WHERE I.EMPLID = A.EMPLID

     AND I.TEST_ID = 'CUNY-MATH'

     AND I.TEST_COMPONENT = 'MAF'

     AND I.LS_DATA_SOURCE IN ('S02')

     AND I.SCORE_LETTER = 'PASS')

     OR EXISTS (SELECT N.EMPLID

  FROM PS_STDNT_TEST_COMP N

  WHERE A.EMPLID = N.EMPLID

     AND N.TEST_ID = 'ACC-MATH-5'

     AND N.TEST_COMPONENT = 'M5'

     AND N.LS_DATA_SOURCE IN ('S02')

     AND N.SCORE_LETTER = 'PASS')

     OR ( EXISTS (SELECT H.EMPLID

  FROM PS_STDNT_TEST_COMP H

  WHERE H.EMPLID = A.EMPLID

     AND H.TEST_ID = 'ACT-MATH-2'

     AND H.TEST_COMPONENT = 'M2'

     AND H.LS_DATA_SOURCE IN ('S02')

     AND H.SCORE_LETTER = 'PASS'))))

  GROUP BY  A.EMPLID,  C.LAST_NAME,  C.FIRST_NAME,  C.MIDDLE_NAME,  K.NATIONAL_ID,  A.ADMIT_TYPE,  C.PHONE,  C.EMAIL_ADDR,  D.TEST_ID,  TO_CHAR(D.TEST_DT,'YYYY-MM-DD'),   D12X.XLATSHORTNAME,  D.SCORE

  ORDER BY 1, 3, 4, 5