|
SELECT B.ACAD_GROUP, B.SUBJECT, B.CATALOG_NBR, A.CRSE_ID, A.COURSE_TITLE_LONG, A.DESCRLONG, A.COMPONENT_PRIMARY, E.DESCR, C.DESCRLONG, B.SSR_CRSE_TYPOFF_CD, B.CAMPUS, CASE
WHEN A.UNITS_MINIMUM = A.UNITS_MAXIMUM THEN TO_CHAR( A.UNITS_MINIMUM | | '.00')
WHEN A.UNITS_MINIMUM <> A.UNITS_MAXIMUM THEN TO_CHAR( A.UNITS_MINIMUM | | '.00' | | ' - ' | | A.UNITS_MAXIMUM | | '.00')
ELSE ' '
END, F.SSR_COMPONENT, CASE WHEN TO_CHAR( F.CONTACT_HOURS) <> '0' THEN TO_CHAR( F.CONTACT_HOURS | | '.00' ) ELSE ' '
END, CASE
WHEN A.CONSENT = 'D' THEN 'Department Consent Required'
WHEN A.CONSENT = 'I' THEN 'Instructor Consent Required'
ELSE ' '
END, CASE
WHEN A.SSR_DROP_CONSENT = 'D' THEN 'Department Consent Required'
WHEN A.SSR_DROP_CONSENT = 'I' THEN 'Instructor Consent Required'
ELSE ' '
END, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), 'View', G.CRSE_ATTR_VALUE, Listagg( G.CRSE_ATTR_VALUE,',') within group (order by G.CRSE_ATTR_VALUE) as CRSE_ATTR_VALUES, TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_CRSE_CATALOG A, (PS_CRSE_OFFER B LEFT OUTER JOIN PS_RQ_GRP_TBL C ON C.RQRMNT_GROUP = B.RQRMNT_GROUP ), (PS_CRSE_CATALOG D LEFT OUTER JOIN PS_CRSE_EQUIV_TBL E ON E.EQUIV_CRSE_ID = D.EQUIV_CRSE_ID ), PS_CRSE_COMPONENT F, PS_CRSE_ATTRIBUTES G
WHERE ( A.CRSE_ID = B.CRSE_ID
AND B.EFFDT = A.EFFDT
AND B.INSTITUTION = 'RIT01'
AND B.ACAD_GROUP LIKE :1
AND ( B.SUBJECT LIKE :2
OR :3 = 'Y')
AND ( C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_RQ_GRP_TBL C_ED
WHERE C.RQRMNT_GROUP = C_ED.RQRMNT_GROUP
AND C_ED.EFFDT <= B.EFFDT)
OR C.EFFDT IS NULL)
AND A.CRSE_ID = D.CRSE_ID
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_CRSE_CATALOG D_ED
WHERE D.CRSE_ID = D_ED.CRSE_ID
AND D_ED.EFFDT <= A.EFFDT)
AND ( E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_CRSE_EQUIV_TBL E_ED
WHERE E.EQUIV_CRSE_ID = E_ED.EQUIV_CRSE_ID
AND E_ED.EFFDT <= D.EFFDT)
OR E.EFFDT IS NULL)
AND A.EFF_STATUS = 'A'
AND A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_CRSE_CATALOG A_ED
WHERE A.CRSE_ID = A_ED.CRSE_ID)
AND B.SUBJECT NOT BETWEEN '0000' AND '9999'
AND B.CATALOG_PRINT = 'Y'
AND D.CRSE_ID = F.CRSE_ID
AND F.EFFDT = A.EFFDT
AND A.CRSE_ID = G.CRSE_ID
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_CRSE_ATTRIBUTES G_ED
WHERE G.CRSE_ID = G_ED.CRSE_ID
AND G_ED.EFFDT <= SYSDATE)
AND G.CRSE_ATTR = 'PERS')
GROUP BY B.ACAD_GROUP, B.SUBJECT, B.CATALOG_NBR, A.CRSE_ID, A.COURSE_TITLE_LONG, A.DESCRLONG, A.COMPONENT_PRIMARY, E.DESCR, C.DESCRLONG, B.SSR_CRSE_TYPOFF_CD, B.CAMPUS, CASE
WHEN A.UNITS_MINIMUM = A.UNITS_MAXIMUM THEN TO_CHAR( A.UNITS_MINIMUM | | '.00')
WHEN A.UNITS_MINIMUM <> A.UNITS_MAXIMUM THEN TO_CHAR( A.UNITS_MINIMUM | | '.00' | | ' - ' | | A.UNITS_MAXIMUM | | '.00')
ELSE ' '
END, F.SSR_COMPONENT, CASE WHEN TO_CHAR( F.CONTACT_HOURS) <> '0' THEN TO_CHAR( F.CONTACT_HOURS | | '.00' ) ELSE ' '
END, CASE
WHEN A.CONSENT = 'D' THEN 'Department Consent Required'
WHEN A.CONSENT = 'I' THEN 'Instructor Consent Required'
ELSE ' '
END, CASE
WHEN A.SSR_DROP_CONSENT = 'D' THEN 'Department Consent Required'
WHEN A.SSR_DROP_CONSENT = 'I' THEN 'Instructor Consent Required'
ELSE ' '
END, A.EFFDT, 'View', G.CRSE_ATTR_VALUE
ORDER BY 1, 2, 3
|