CU_BAR_SR_00024_1

SELECT A.EMPLID, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.ACAD_PROG, C.ACAD_PLAN, D.ACAD_SUB_PLAN, E.UNT_TAKEN_PRGRSS, A.ADMIT_TERM, F.EMAIL_ADDR, K.PHONE, A.EXP_GRAD_TERM, A.DEGR_CHKOUT_STAT, E.CUR_GPA, E.CUM_GPA, A.PROG_STATUS, G.ADDRESS1, G.ADDRESS2, G.CITY, G.STATE, G.POSTAL, H.RESIDENCY, E.TOT_PASSD_PRGRSS, E.TOT_TAKEN_PRGRSS, J.SEX, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')

  FROM PS_ACAD_PROG A, PS_ACAD_PLAN C, PS_ACAD_SUBPLAN D, PS_STDNT_CAR_TERM E, PS_SCC_EMAIL_QVW F, PS_ADDRESSES4_VW G, PS_RESIDENCY_OFF H, PS_SF_STDNT_PRS_VW J, PS_SCC_PERS_PH_QVW K, PS_NAME_CURR_VW B

  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.ACAD_CAREER = 'GRAD'

     AND A.INSTITUTION = 'BAR01'

     AND A.PROG_STATUS = 'AC'

     AND A.ACAD_PROG IN ('ADVCT','MPA','MSED')

     AND A.EMPLID = C.EMPLID

     AND A.ACAD_CAREER = C.ACAD_CAREER

     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR

     AND A.EFFSEQ = C.EFFSEQ

     AND A.EFFDT = C.EFFDT

     AND C.EMPLID =  D.EMPLID (+)

     AND C.ACAD_CAREER =  D.ACAD_CAREER (+)

     AND C.STDNT_CAR_NBR =  D.STDNT_CAR_NBR (+)

     AND C.EFFSEQ =  D.EFFSEQ (+)

     AND C.ACAD_PLAN =  D.ACAD_PLAN (+)

     AND C.EFFDT =  D.EFFDT (+)

     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 = :1

     AND A.EMPLID =  F.EMPLID (+)

     AND 'CAMP' =  F.E_ADDR_TYPE (+)

     AND A.EMPLID =  G.EMPLID (+)

     AND 'MAIL' =  G.ADDRESS_TYPE (+)

     AND A.EMPLID = H.EMPLID

     AND A.ACAD_CAREER = H.ACAD_CAREER

     AND H.INSTITUTION = A.INSTITUTION

     AND H.EFFECTIVE_TERM = (SELECT MAX ( I.EFFECTIVE_TERM)

  FROM PS_RESIDENCY_OFF I

  WHERE I.EMPLID = H.EMPLID

     AND I.ACAD_CAREER = H.ACAD_CAREER

     AND I.INSTITUTION = H.INSTITUTION

     AND I.EFFECTIVE_TERM <= :1)

     AND A.EMPLID = J.EMPLID

     AND A.EMPLID =  K.EMPLID (+)

     AND 'Y' =  K.PREF_PHONE_FLAG (+)

     AND A.EMPLID =  B.EMPLID (+)

     AND 'PRI' =  B.NAME_TYPE (+)

  ORDER BY 2, 3, 4