SELECT A.SUBJECT, A.CATALOG_NBR, A.CLASS_SECTION, A.DESCR, to_char ( A.CLASS_NBR, '09999'), A.SESSION_CODE, A.ACAD_GROUP, (to_char ( A.ENRL_CAP, '9990.')), to_char ( A.ENRL_TOT, '9990.'), A.INSTRUCTION_MODE, B.FACILITY_ID, to_char(DECODE(TO_CHAR(CAST((B.MEETING_TIME_START) AS TIMESTAMP),'HH24.MI.SS.FF'), NULL, TO_TIMESTAMP(NULL), TO_TIMESTAMP('1900-01-01-' || TO_CHAR(CAST((B.MEETING_TIME_START) AS TIMESTAMP),'HH24.MI.SS.FF'), 'YYYY-MM-DD-HH24.MI.SS.FF')), 'HH12:MI') || CASE when substr ( TO_CHAR(CAST((B.MEETING_TIME_START) AS TIMESTAMP),'HH24.MI.SS.FF'), 1, 2) < '12' then 'AM' when substr ( TO_CHAR(CAST((B.MEETING_TIME_START) AS TIMESTAMP),'HH24.MI.SS.FF'), 1, 2) between '12' and '23' then 'PM' else ' ' END, to_char (DECODE(TO_CHAR(CAST((B.MEETING_TIME_END) AS TIMESTAMP),'HH24.MI.SS.FF'), NULL, TO_TIMESTAMP(NULL), TO_TIMESTAMP('1900-01-01-' || TO_CHAR(CAST((B.MEETING_TIME_END) AS TIMESTAMP),'HH24.MI.SS.FF'), 'YYYY-MM-DD-HH24.MI.SS.FF')), 'HH12:MI') || CASE when substr ( TO_CHAR(CAST((B.MEETING_TIME_END) AS TIMESTAMP),'HH24.MI.SS.FF'), 1, 2) < '12' then 'AM' when substr ( TO_CHAR(CAST((B.MEETING_TIME_END) AS TIMESTAMP),'HH24.MI.SS.FF'), 1, 2) between '12' and '23' then 'PM' else ' ' END, DECODE ( B.MON, 'Y', 'Mo') || DECODE ( B.TUES, 'Y', 'Tu') || DECODE ( B.WED, 'Y', 'We') || DECODE ( B.THURS, 'Y', 'Th') || DECODE ( B.FRI, 'Y', 'Fr') || DECODE ( B.SAT, 'Y', 'Sa') || DECODE ( B.SUN, 'Y', 'Su'), C.NAME_DISPLAY, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ' FROM PS_CLASS_TBL A, PS_CLASS_MTG_PAT B, PS_CLASS_INSTR_VW5 C WHERE ( A.INSTITUTION = :1 AND A.STRM = :2 AND A.CLASS_STAT = 'A' AND A.ACAD_ORG = :3 AND ( A.SUBJECT = :4 OR :4 IS NULL) AND ( A.CATALOG_NBR = :5 OR :5 IS NULL) 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.CRSE_ID = C.CRSE_ID AND B.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR AND B.STRM = C.STRM AND B.SESSION_CODE = C.SESSION_CODE AND B.CLASS_SECTION = C.CLASS_SECTION AND B.CLASS_MTG_NBR = C.CLASS_MTG_NBR ) UNION SELECT D.SUBJECT, D.CATALOG_NBR, D.CLASS_SECTION, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', E.EMPLID, F.LAST_NAME, F.FIRST_NAME, F.MIDDLE_NAME, '-' || substr ( G.NATIONAL_ID, 6, 4), F.PHONE, F.EMAIL_ADDR, H.EMAIL_ADDR FROM PS_CLASS_TBL D, PS_STDNT_ENRL E, PS_CU_BIO_VW F, PS_SCC_PERS_NI_QVW G, PS_SCC_EMAIL_QVW H WHERE ( D.STRM = E.STRM AND D.SESSION_CODE = E.SESSION_CODE AND E.ACAD_CAREER = D.ACAD_CAREER AND E.INSTITUTION = D.INSTITUTION AND E.CLASS_NBR = D.CLASS_NBR AND E.STDNT_ENRL_STATUS = 'E' AND D.INSTITUTION = :1 AND D.STRM = :2 AND D.ACAD_ORG = :3 AND ( D.SUBJECT = :4 OR :4 IS NULL) AND ( D.CATALOG_NBR = :5 OR :5 IS NULL) AND E.EMPLID = F.EMPLID AND E.EMPLID = G.EMPLID AND E.EMPLID = H.EMPLID (+) AND 'CAMP' = H.E_ADDR_TYPE (+) AND D.CLASS_STAT = 'A' ) ORDER BY 1, 2, 3, 17, 18, 19 |