Description:
Generates READING placements for students who have taken the CUNY Assessment Test in Reading. Updated for the new AccuPlacer based CUNY Assessment Test in Reading
Query SQL:
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