Page History

SELECT CASE

WHEN SUM ( D.SCORE ) > 240 THEN 'M261'

WHEN SUM ( D.SCORE) BETWEEN 211 AND 240 THEN 'M227'

WHEN SUM ( D.SCORE) BETWEEN 148 AND 210 THEN 'M203'

WHEN SUM ( D.SCORE) BETWEEN 66 AND 147 THEN 'M103'

WHEN SUM ( D.SCORE) BETWEEN 24 AND 65 THEN 'M120'

WHEN SUM ( D.SCORE) BETWEEN 0 AND 23 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, SUM ( 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 = 'ACT-MATH-3'

     AND D.LS_DATA_SOURCE = 'S02'

     AND D.TEST_COMPONENT IN ('MPL2','MPL3','MPL5')

     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_COMPONENT IN ('MPL2','MPL3','MPL5')

     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 ( substr ( B.STDNT_GROUP, 1, 2) IN ('M1','M2')

     OR B.STDNT_GROUP = 'NTMT'

     OR B.STDNT_GROUP = 'S100')

     AND B.EFF_STATUS = 'A')

     AND NOT EXISTS (SELECT 'X'

  FROM PS_STDNT_TEST_COMP E

  WHERE E.EMPLID = A.EMPLID

     AND E.TEST_ID = 'RGNTS'

     AND E.TEST_COMPONENT IN ('SMA3','MATHB'))

     AND NOT EXISTS (SELECT J.EMPLID

  FROM PS_STDNT_TEST_COMP J

  WHERE A.EMPLID = J.EMPLID

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

     AND J.TEST_COMPONENT = 'M6'

     AND J.LS_DATA_SOURCE IN ('S02'))

     AND ( EXISTS (SELECT F.EMPLID

  FROM PS_STDNT_TEST_COMP F

  WHERE F.EMPLID = A.EMPLID

     AND F.TEST_ID = 'MATH EXEMPT')

     OR EXISTS (SELECT I.EMPLID

  FROM PS_STDNT_TEST_COMP I

  WHERE I.EMPLID = A.EMPLID

     AND I.TEST_COMPONENT = 'MAF'

     AND I.SCORE >= 25)

     OR ( EXISTS (SELECT H.EMPLID

  FROM PS_STDNT_TEST_COMP H

  WHERE H.EMPLID = A.EMPLID

     AND H.TEST_COMPONENT = 'M2'

     AND H.SCORE >= 40))))

  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

  ORDER BY 1, 3, 4, 5