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:
Panel |
---|
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 |