CU_BAR_SR_00073_2 is a version of the CU_BAR_SR_00073_1 query to provide the same information as the SR_00073_1 query but for one single section.
This version should be used when the SmartEvals@Baruch Tool indicates that a section is missing from the class table. This record will only return one section's information at a time.
Query Shared with: Denyse Ramkaran, Barry Ho, Arthur Downing, Patrick Ackerman, Tabassum Lodhi
Base | |||||||||||||||||||||
SELECT A.STRM, A.SESSION_CODE, A.INSTITUTION, A.ACAD_CAREER, A.SUBJECT, A.CATALOG_NBR, A.CLASS_SECTION, A.CLASS_NBR, A.DESCR, A.ENRL_TOT, A.ENRL_STAT, A.CLASS_STAT, A.CLASS_TYPE, A.ENRL_CAP, A.WAIT_CAP, A.WAIT_TOT, A.MIN_ENRL, A.SSR_COMPONENT, A.CRSE_ID, A.CRSE_OFFER_NBR, C.LAST_NAME, C.FIRST_NAME, TO_CHAR(A.START_DT,'YYYY-MM-DD'), TO_CHAR(A.END_DT,'YYYY-MM-DD'), '', '', '', '', CASE WHEN INSTR(UPPER( A.DESCR),'IND STUD') > 0 THEN 'REMOVE' WHEN INSTR(UPPER( A.DESCR),'THESIS') > 0 THEN 'REMOVE' WHEN INSTR(UPPER( A.DESCR),'INTERNSHIP') > 0 THEN 'REMOVE' WHEN INSTR(UPPER( A.DESCR),'FELLOWSHIP') > 0 THEN 'REMOVE' WHEN INSTR(UPPER( A.DESCR),'GRAD INTERN MODULE') > 0 THEN 'REMOVE' WHEN INSTR(UPPER( A.DESCR),'INTERNSHP') > 0 THEN 'REMOVE' WHEN INSTR(UPPER( A.DESCR),'INTRNSHP') > 0 THEN 'REMOVE' WHEN UPPER( A.SUBJECT) = 'MAM' THEN 'REMOVE' WHEN UPPER( A.SUBJECT) = 'CUNBA' THEN 'REMOVE' WHEN UPPER( A.SUBJECT) = 'STABD' THEN 'REMOVE' WHEN UPPER( A.SUBJECT) = 'PERM' THEN 'REMOVE' WHEN UPPER( A.SUBJECT) = 'FSPE' THEN 'REMOVE' WHEN UPPER( A.SUBJECT) = 'FSPM' THEN 'REMOVE' WHEN UPPER( A.SUBJECT) = 'IBSIP' THEN 'REMOVE' ELSE '' END, CASE WHEN INSTR(UPPER( A.DESCR),'IND STUD') > 0 THEN 'IND STUDY' WHEN INSTR(UPPER( A.DESCR),'THESIS') > 0 THEN 'THESIS' WHEN INSTR(UPPER( A.DESCR),'INTERNSHIP') > 0 THEN 'INTERNSHIP' WHEN INSTR(UPPER( A.DESCR),'FELLOWSHIP') > 0 THEN 'INTERNSHIP' WHEN INSTR(UPPER( A.DESCR),'GRAD INTERN MODULE') > 0 THEN 'INTERNSHIP' WHEN INSTR(UPPER( A.DESCR),'INTERNSHP') > 0 THEN 'INTERNSHIP' WHEN INSTR(UPPER( A.DESCR),'INTRNSHP') > 0 THEN 'INTERNSHIP' WHEN UPPER( A.SUBJECT) = 'MAM' THEN 'MAINT. MATRIC' WHEN UPPER( A.SUBJECT) = 'CUNBA' THEN 'CUNYBA' WHEN UPPER( A.SUBJECT) = 'STABD' THEN 'STUDY ABROAD' WHEN UPPER( A.SUBJECT) = 'PERM' THEN 'EPERMIT' WHEN UPPER( A.SUBJECT) = 'FSPE' THEN 'IMMERSION CLASS' WHEN UPPER( A.SUBJECT) = 'FSPM' THEN 'IMMERSION CLASS' WHEN UPPER( A.SUBJECT) = 'IBSIP' THEN 'IMMERSION CLASS' ELSE '' END, CASE WHEN SUBSTR ( A.STRM, 4, 1) = '2' AND A.SESSION_CODE = 'WIN' THEN 'Wn-' || SUBSTR ( A.STRM, 2, 2) || '-' || A.CRSE_ID || '-' || A.CLASS_SECTION WHEN SUBSTR ( A.STRM, 4, 1) = '2' AND NOT( A.SESSION_CODE = 'WIN') 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 FROM PS_CLASS_TBL A, PS_CLASS_INSTR B, PS_CU_BIO_VW C WHERE A.INSTITUTION = :1 AND A.STRM = :2 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 B.EMPLID = C.EMPLID (+) AND ( A.CLASS_NBR = :4) ORDER BY 1, 2, 3, 4, 5, 6, 7 |