Note: Same Logic as CU_BAR_SR_00034_5 except delivers all enrollments for all classes in one output. This query should not be ran on demand due to possibility of time-out due to large data set returned; this query should be ran via a scheduled query.
Query SQL:
SELECT CASE
WHEN SUBSTR ( A.STRM, 4, 1) = '2' THEN 'Sp-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
WHEN SUBSTR ( A.STRM, 4, 1) = '6' THEN 'Su-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
WHEN SUBSTR ( A.STRM, 4, 1) = '9' THEN 'Fa-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
end, C.EMPLID, A.SUBJECT, A.CATALOG_NBR, CASE
WHEN SUBSTR ( A.STRM, 4, 1) = '2' THEN 'Sp-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
WHEN SUBSTR ( A.STRM, 4, 1) = '6' THEN 'Su-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
WHEN SUBSTR ( A.STRM, 4, 1) = '9' THEN 'Fa-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION
end, A.DESCR, A.SSR_COMPONENT, A.SESSION_CODE, A.CLASS_SECTION, A.ACAD_CAREER, 'Yes', B.EMPLID, B.FIRST_NAME, B.LAST_NAME, D.EMPLID, D.LAST_NAME, D.FIRST_NAME, E.EMAIL_ADDR, F.EMAIL_ADDR,A.CRSE_ID,A.CRSE_OFFER_NBR,A.STRM
FROM PS_CLASS_TBL A, PS_CLASS_INSTR_VW5 B, PS_STDNT_ENRL C, PS_CLASS_INSTR_VW5 D, PS_CU_BIO_VW E, PS_CU_BIO_VW F
WHERE ( A.INSTITUTION = :1
AND A.STRM = :2
AND A.CLASS_STAT = 'A'
AND A.ENRL_TOT > 0
AND A.STRM = C.STRM
AND A.SESSION_CODE = C.SESSION_CODE
AND C.ACAD_CAREER = A.ACAD_CAREER
AND C.INSTITUTION = A.INSTITUTION
AND C.CLASS_NBR = A.CLASS_NBR
AND A.CRSE_ID = B.CRSE_ID (+)
AND A.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR (+)
AND A.STRM = B.STRM (+)
AND A.SESSION_CODE = B.SESSION_CODE (+)
AND A.CLASS_SECTION = B.CLASS_SECTION (+)
AND 'PI' = B.INSTR_ROLE (+)
AND A.CRSE_ID = D.CRSE_ID (+)
AND A.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR (+)
AND A.STRM = D.STRM (+)
AND A.SESSION_CODE = D.SESSION_CODE (+)
AND A.CLASS_SECTION = D.CLASS_SECTION (+)
AND 'SI' = D.INSTR_ROLE (+)
AND C.CRSE_GRADE_OFF NOT IN ('*W','W','WU','WN','WA')
AND B.EMPLID = E.EMPLID (+)
AND D.EMPLID = F.EMPLID (+)
AND C.ENRL_STATUS_REASON IN ('ENRL','EWAT') )
ORDER BY 3, 4, 9, 2