CU_BAR_SR_00077_3

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 =