SELECT DISTINCT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, C.EMAIL_ADDR, C.PHONE, A.EMPLID, G.NATIONAL_ID, A.ADMIT_TERM, A.ACAD_PROG, B.ACAD_PLAN, E.UNT_TAKEN_PRGRSS, E.TOT_TAKEN_GPA, E.TOT_PASSD_GPA, E.TOT_CUMULATIVE, E.CUR_GPA, E.CUM_GPA, F.COUNTRY, F.ADDRESS1, F.ADDRESS2, F.ADDRESS3, F.CITY, F.STATE, F.POSTAL, H.SEX, I.EFFECTIVE_TERM, I.RESIDENCY, K.COUNTRY, L.DESCRSHORT, M.COUNTRY, N.DESCRSHORT, O.ETHNIC_GROUP, P.ETHNIC_GROUP, Q.ETHNIC_GROUP, R.ETHNIC_GROUP, S.ETHNIC_GROUP, T.ETHNIC_GROUP, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),L.COUNTRY,L.CITIZENSHIP_STATUS,N.COUNTRY,N.CITIZENSHIP_STATUS
FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_CU_BIO_VW C, PS_STDNT_CAR_TERM E, PS_ADDRESSES4_VW F, PS_SCC_PERS_NI_QVW G, PS_SF_STDNT_PRS_VW H, PS_RESIDENCY_OFF I, PS_SCC_CITIZEN_QVW K, PS_CITIZEN_STS_TBL L, PS_SCC_CITIZEN_QVW M, PS_CITIZEN_STS_TBL N, PS_SCC_ETH_GRP_VW O, PS_SCC_ETH_GRP_VW P, PS_SCC_ETH_GRP_VW Q, PS_SCC_ETH_GRP_VW R, PS_SCC_ETH_GRP_VW S, PS_SCC_ETH_GRP_VW T
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 = :2
AND A.ACAD_PROG IN ('MS','MA')
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.EFFSEQ = B.EFFSEQ
AND A.EFFDT = B.EFFDT
AND A.PROG_STATUS = 'AC'
AND B.ACAD_PLAN IN ('CRPCOMM-MA','MEHCR-MA','IOP-MS','FINENGR-MS')
AND A.EMPLID = C.EMPLID
AND NOT EXISTS (SELECT 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 IN ('IESN','IETI'))
AND A.EMPLID = E.EMPLID
AND A.ACAD_CAREER = E.ACAD_CAREER
AND A.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND E.INSTITUTION = A.INSTITUTION
AND E.STRM = :3
AND A.EMPLID = F.EMPLID (+)
AND 'MAIL' = F.ADDRESS_TYPE (+)
AND A.EMPLID = G.EMPLID
AND A.EMPLID = H.EMPLID
AND A.EMPLID = I.EMPLID
AND A.ACAD_CAREER = I.ACAD_CAREER
AND I.INSTITUTION = A.INSTITUTION
AND I.EFFECTIVE_TERM = (SELECT MAX ( J.EFFECTIVE_TERM)
FROM PS_RESIDENCY_OFF J
WHERE J.EMPLID = I.EMPLID
AND J.ACAD_CAREER = I.ACAD_CAREER
AND J.INSTITUTION = I.INSTITUTION)
AND A.EMPLID = K.EMPLID (+)
AND 'USA' = K.COUNTRY (+)
AND K.COUNTRY = L.COUNTRY (+)
AND K.CITIZENSHIP_STATUS = L.CITIZENSHIP_STATUS (+)
AND A.EMPLID = M.EMPLID (+)
AND 'USA' <> M.COUNTRY (+)
AND M.COUNTRY = N.COUNTRY (+)
AND M.CITIZENSHIP_STATUS = N.CITIZENSHIP_STATUS (+)
AND A.EMPLID = O.EMPLID (+)
AND '1' = O.ETHNIC_GROUP (+)
AND A.EMPLID = P.EMPLID (+)
AND '2' = P.ETHNIC_GROUP (+)
AND A.EMPLID = Q.EMPLID (+)
AND '3' = Q.ETHNIC_GROUP (+)
AND A.EMPLID = R.EMPLID (+)
AND '4' = R.ETHNIC_GROUP (+)
AND A.EMPLID = S.EMPLID (+)
AND '5' = S.ETHNIC_GROUP (+)
AND A.EMPLID = T.EMPLID (+)
AND '6' = T.ETHNIC_GROUP (+)
ORDER BY 1, 2, 3
  • No labels

1 Comment

  1. Tabassum Lodhi

    Sep 27, 2016

    Stephanie Govan from Weissman School of Enrollment data management has requested to add 'ARTADM-MA' major_plan along with other MA, GRADUATE plans.  It has been added to the query CU_BAR_SR_00065.

    Criteria :