CU_BAR_SR_00078_1

SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, B.ACAD_PLAN, G.ACAD_PLAN, A.ACAD_CAREER, A.ADMIT_TERM, E.UNT_TAKEN_PRGRSS, E.TOT_TAKEN_PRGRSS, E.TOT_PASSD_PRGRSS, E.CUM_GPA, H.CUR_GPA, I.SEX, J.ACAD_SUB_PLAN

  FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, PS_CU_BIO_VW C, PS_STDNT_CAR_TERM E, PS_ACAD_PLAN G, PS_STDNT_CAR_TERM H, PS_SF_STDNT_PRS_VW I, PS_ACAD_SUBPLAN J

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

     AND A.PROG_STATUS = 'AC'

     AND E.STRM = :2

     AND A.ACAD_PROG IN ('MBA','MS')

     AND A.EMPLID = C.EMPLID

     AND B.ACAD_PLAN NOT IN ('IOP-MS','FINENGR-MS','HCA-MBA','ILR-MS')

     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 NOT EXISTS (SELECT D.EMPLID

  FROM PS_CU_STDNTGRP_VW D

  WHERE ( D.EMPLID = A.EMPLID

     AND D.INSTITUTION = A.INSTITUTION

     AND D.STDNT_GROUP = 'IEXP' ))

     AND E.UNT_TAKEN_PRGRSS > 0

     AND B.PLAN_SEQUENCE = (SELECT min ( F.PLAN_SEQUENCE)

  FROM PS_ACAD_PLAN F

  WHERE ( F.EMPLID = B.EMPLID

     AND F.ACAD_CAREER = B.ACAD_CAREER

     AND F.STDNT_CAR_NBR = B.STDNT_CAR_NBR

     AND F.EFFDT = B.EFFDT

     AND F.EFFSEQ = B.EFFSEQ ))

     AND B.EMPLID =  G.EMPLID (+)

     AND B.ACAD_CAREER =  G.ACAD_CAREER (+)

     AND B.STDNT_CAR_NBR =  G.STDNT_CAR_NBR (+)

     AND B.EFFSEQ =  G.EFFSEQ (+)

     AND B.EFFDT =  G.EFFDT  (+)

     AND B.PLAN_SEQUENCE <  G.PLAN_SEQUENCE (+)

     AND E.EMPLID =  H.EMPLID (+)

     AND E.ACAD_CAREER =  H.ACAD_CAREER (+)

     AND E.INSTITUTION =  H.INSTITUTION (+)

     AND :3 =  H.STRM (+)

     AND E.STDNT_CAR_NBR =  H.STDNT_CAR_NBR (+)

     AND A.EMPLID = I.EMPLID

     AND B.EMPLID =  J.EMPLID (+)

     AND B.ACAD_CAREER =  J.ACAD_CAREER (+)

     AND B.STDNT_CAR_NBR =  J.STDNT_CAR_NBR (+)

     AND B.EFFSEQ =  J.EFFSEQ (+)

     AND B.ACAD_PLAN =  J.ACAD_PLAN (+)

     AND B.EFFDT =  J.EFFDT (+)

     AND NOT EXISTS (SELECT K.EMPLID

  FROM PS_ACAD_SUBPLAN K

  WHERE ( K.EFFDT = J.EFFDT

     AND K.EMPLID = A.EMPLID

     AND K.ACAD_CAREER = J.ACAD_CAREER

     AND K.STDNT_CAR_NBR = A.STDNT_CAR_NBR

     AND K.EFFSEQ = J.EFFSEQ

     AND K.ACAD_SUB_PLAN = 'MGTEXE-MBA' )) )

  ORDER BY 1, 2, 3