CU_BAR_AD_00006D

SELECT DISTINCT CASE

WHEN  J.SCORE BETWEEN 95 AND 100  AND  D.SCORE BETWEEN 20 AND 120 THEN 'M261'

WHEN  J.SCORE BETWEEN 93 AND 95  AND  D.SCORE BETWEEN 93 AND 120 THEN 'M261'

WHEN  J.SCORE BETWEEN 85 AND 92 AND  D.SCORE BETWEEN 95 AND 120 THEN 'M261'

WHEN  J.SCORE BETWEEN 76 AND 84 AND  D.SCORE BETWEEN 100 AND 120 THEN 'M261'

WHEN  J.SCORE BETWEEN 70 AND 75 AND  D.SCORE BETWEEN 100 AND 120 THEN 'M261'

WHEN  J.SCORE BETWEEN 60 AND 69 AND  D.SCORE BETWEEN 100 AND 120 THEN 'M261'

WHEN  J.SCORE BETWEEN 0 AND 59 AND  D.SCORE BETWEEN 100 AND 120 THEN 'M261'

WHEN  J.SCORE BETWEEN 93 AND 95 AND  D.SCORE BETWEEN 20 AND 92 THEN 'M227'

WHEN  J.SCORE BETWEEN 85 AND 92 AND  D.SCORE BETWEEN 20 AND 94 THEN 'M227'

WHEN  J.SCORE BETWEEN 76 AND 84 AND  D.SCORE BETWEEN 75 AND 99 THEN 'M227'

WHEN  J.SCORE BETWEEN 70 AND 75 AND  D.SCORE BETWEEN 80 AND 99 THEN 'M227'

WHEN  J.SCORE BETWEEN 60 AND 69 AND  D.SCORE BETWEEN 80 AND 99 THEN 'M227'

WHEN  J.SCORE BETWEEN 0 AND 59 AND  D.SCORE BETWEEN 80 AND 99 THEN 'M227'

WHEN  J.SCORE BETWEEN 76 AND 84 AND  D.SCORE BETWEEN 20 AND 74 THEN 'M203'

WHEN  J.SCORE BETWEEN 70 AND 75 AND  D.SCORE BETWEEN 20 AND 79 THEN 'M203'

WHEN  J.SCORE BETWEEN 60 AND 69 AND  D.SCORE BETWEEN 55 AND 79 THEN 'M203'

WHEN  J.SCORE BETWEEN 0 AND 59 AND  D.SCORE BETWEEN 60 AND 79 THEN 'M203'

WHEN  J.SCORE BETWEEN 60 AND 69 AND  D.SCORE BETWEEN 36 AND 54 THEN 'M103'

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

WHEN  J.SCORE BETWEEN 60 AND 69 AND  D.SCORE BETWEEN 20 AND 35 THEN 'M120'

WHEN  J.SCORE BETWEEN 0 AND 59 AND  D.SCORE BETWEEN 26 AND 47 THEN 'M120'

WHEN  J.SCORE BETWEEN 0 AND 59 AND  D.SCORE BETWEEN 20 AND 25 THEN 'MATH'

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, J.TEST_ID, J.TEST_COMPONENT, J.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') ), PS_STDNT_TEST_COMP J

  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 EXISTS (SELECT 'X'

  FROM PS_STDNT_TEST_COMP E

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

     AND A.EMPLID = J.EMPLID

     AND J.TEST_ID = 'RGNTS'

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

     AND J.SCORE = (SELECT max ( L.SCORE)

  FROM PS_STDNT_TEST_COMP L

  WHERE L.EMPLID = J.EMPLID

     AND L.TEST_ID = J.TEST_ID

     AND L.TEST_COMPONENT = J.TEST_COMPONENT))

  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,  J.TEST_ID,  J.TEST_COMPONENT,  J.SCORE

  ORDER BY 1, 3, 4, 5