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
  • No labels