Background:
Margo has had CU_BAR_SR_00077_2 the query which doesn't pick up the currently enrolled students. Therefore, the existed have modified to pick up the current enrolled students. After modification the query has been delivered to Margo and Patrick. Please see the Kace ticket for detail reference: https://helpdesk.baruch.cuny.edu/admin 37529.
Criteria:
In the sub-query the Residency record has been picking up the latest student residency information but effective _term should be less than prompted term. Otherwise, it will not pick up the current student.
FROM PS_RESIDENCY_OFF O WHERE ( O.EMPLID = F.EMPLID AND O.ACAD_CAREER = F.ACAD_CAREER AND O.INSTITUTION = F.INSTITUTION AND O.EFFECTIVE_TERM <= :2 ) |
---|
The image of CU_BAR)SR_00077_3
Query SQL:
SELECT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, C.SEX, F.RESIDENCY, P.VISA_PERMIT_TYPE, A.ACAD_PROG, M.ACAD_PLAN, R.ACAD_SUB_PLAN, E.ACAD_PLAN, Q.ACAD_SUB_PLAN, A.ADMIT_TERM, D.UNT_TAKEN_PRGRSS, D.TOT_CUMULATIVE, D.CUM_GPA, decode ( G.ETHNIC_GROUP, '1', 'White ') ||
decode ( H.ETHNIC_GROUP, '2', 'Black ') ||
decode ( I.ETHNIC_GROUP, '3', 'Hispanic ') ||
decode ( J.ETHNIC_GROUP, '4', 'Asian ') ||
decode ( K.ETHNIC_GROUP, '5', 'AmerInd ') ||
decode ( L.ETHNIC_GROUP, '6', 'Non-Hispanic/Not-Specified '), B.PHONE, B.EMAIL_ADDR, S.STDNT_GROUP
FROM PS_ACAD_PROG A, PS_CU_BIO_VW B, PS_SF_STDNT_PRS_VW C, PS_STDNT_CAR_TERM D, PS_SCC_ETH_GRP_VW G, PS_SCC_ETH_GRP_VW H, PS_SCC_ETH_GRP_VW I, PS_SCC_ETH_GRP_VW J, PS_SCC_ETH_GRP_VW K, PS_SCC_ETH_GRP_VW L, PS_ACAD_PLAN M, PS_ACAD_PLAN E, PS_RESIDENCY_OFF F, PS_SCC_VISA_P_QVW P, PS_ACAD_SUBPLAN Q, PS_ACAD_SUBPLAN R, PS_STDNT_GRPS_HIST S
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG 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_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG 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.EFFDT = A_ES.EFFDT)
AND A.INSTITUTION = :1
AND A.ACAD_CAREER = :3
AND D.STRM = :2
AND A.EMPLID = B.EMPLID
AND A.EMPLID = C.EMPLID
AND A.EMPLID = D.EMPLID
AND A.ACAD_CAREER = D.ACAD_CAREER
AND A.STDNT_CAR_NBR = D.STDNT_CAR_NBR
AND D.INSTITUTION = A.INSTITUTION
AND A.EMPLID = G.EMPLID (+)
AND '1' = G.ETHNIC_GROUP (+)
AND A.EMPLID = H.EMPLID (+)
AND '2' = H.ETHNIC_GROUP (+)
AND A.EMPLID = I.EMPLID (+)
AND '3' = I.ETHNIC_GROUP (+)
AND A.EMPLID = J.EMPLID (+)
AND '4' = J.ETHNIC_GROUP (+)
AND A.EMPLID = K.EMPLID (+)
AND '5' = K.ETHNIC_GROUP (+)
AND A.EMPLID = L.EMPLID (+)
AND '6' = L.ETHNIC_GROUP (+)
AND A.EMPLID = M.EMPLID
AND A.ACAD_CAREER = M.ACAD_CAREER
AND A.STDNT_CAR_NBR = M.STDNT_CAR_NBR
AND A.EFFSEQ = M.EFFSEQ
AND A.EFFDT = M.EFFDT
AND M.EMPLID = E.EMPLID (+)
AND M.ACAD_CAREER = E.ACAD_CAREER (+)
AND M.STDNT_CAR_NBR = E.STDNT_CAR_NBR (+)
AND M.EFFSEQ = E.EFFSEQ (+)
AND M.EFFDT = E.EFFDT (+)
AND M.ACAD_PLAN <> E.ACAD_PLAN (+)
AND A.EMPLID = F.EMPLID
AND A.ACAD_CAREER = F.ACAD_CAREER
AND F.INSTITUTION = A.INSTITUTION
AND A.PROG_STATUS = 'AC'
AND F.EFFECTIVE_TERM = (SELECT MAX ( O.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF O
WHERE ( O.EMPLID = F.EMPLID
AND O.ACAD_CAREER = F.ACAD_CAREER
AND O.INSTITUTION = F.INSTITUTION
AND O.EFFECTIVE_TERM <= :2 ))
AND A.EMPLID = P.EMPLID (+)
AND 'USA' = P.COUNTRY (+)
AND E.EMPLID = Q.EMPLID (+)
AND E.ACAD_CAREER = Q.ACAD_CAREER (+)
AND E.STDNT_CAR_NBR = Q.STDNT_CAR_NBR (+)
AND E.EFFSEQ = Q.EFFSEQ (+)
AND E.ACAD_PLAN = Q.ACAD_PLAN (+)
AND E.EFFDT = Q.EFFDT (+)
AND M.EMPLID = R.EMPLID (+)
AND M.ACAD_CAREER = R.ACAD_CAREER (+)
AND M.STDNT_CAR_NBR = R.STDNT_CAR_NBR (+)
AND M.EFFSEQ = R.EFFSEQ (+)
AND M.ACAD_PLAN = R.ACAD_PLAN (+)
AND M.EFFDT = R.EFFDT (+)
AND A.EMPLID = S.EMPLID(+)
AND A.INSTITUTION = S.INSTITUTION (+)
AND A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG 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_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG 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.EFFDT = A_ES.EFFDT)
AND 'A' = S.EFF_STATUS(+)
AND M.ACAD_PLAN = :4 )
ORDER BY 1, 2, 3