CU_BAR_CM_00027_1

SELECT A.EMPLID, D.FIRST_NAME, D.MIDDLE_NAME, D.LAST_NAME, F.SUBJECT, F.CATALOG_NBR, F.CLASS_SECTION, COUNT( A.CRSE_ID), listagg( A.STRM || ' ' ||  A.SUBJECT || ' ' ||  A.CATALOG_NBR || ' ' ||  A.CLASS_SECTION || ' ' ||  A.CRSE_GRADE_OFF, ', ') within group (order by  A.STRM desc)

  FROM PS_CLASS_TBL_SE_VW A, PS_STDNT_CAR_TERM B, PS_CU_BIO_VW D, PS_CLASS_TBL_SE_VW F

  WHERE ( A.CRSE_ID IN ('090828','091787')

     AND A.STRM <= :1

     AND A.EMPLID = B.EMPLID

     AND A.ACAD_CAREER = B.ACAD_CAREER

     AND A.INSTITUTION = B.INSTITUTION

     AND B.STRM = :1

     AND EXISTS (SELECT C.EMPLID

  FROM PS_CLASS_TBL_SE_VW C

  WHERE C.STRM = :1

     AND C.CRSE_ID IN ('090828','091787')

     AND A.EMPLID = C.EMPLID

     AND A.CRSE_GRADE_OFF IN ('R','W','WU','WN','FIN','INC'))

     AND A.EMPLID = D.EMPLID

     AND A.INSTITUTION = :2

     AND NOT EXISTS (SELECT E.EMPLID

  FROM PS_CLASS_TBL_SE_VW E

  WHERE A.CRSE_ID IN ('090828','091787')

     AND A.EMPLID = E.EMPLID

     AND E.CRSE_GRADE_OFF = 'P'

     AND E.STRM = :1)

     AND B.EMPLID = F.EMPLID

     AND B.ACAD_CAREER = F.ACAD_CAREER

     AND B.INSTITUTION = F.INSTITUTION

     AND B.STRM = F.STRM

     AND F.CRSE_ID IN ('090828','091787'))

  GROUP BY  A.EMPLID,  D.FIRST_NAME,  D.MIDDLE_NAME,  D.LAST_NAME,  F.SUBJECT,  F.CATALOG_NBR,  F.CLASS_SECTION

  ORDER BY 4, 2, 3