Query SQL:
SELECT A.SUBJECT, A.CATALOG_NBR, A.DESCR, A.CLASS_SECTION, A.SESSION_CODE, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, A.CRSE_GRADE_OFF, 'Yes'
FROM PS_CLASS_TBL_SE_VW A, PS_CU_BIO_VW B, PS_STDNT_GRPS_HIST C, PS_CLASS_ATTRIBUTE G
WHERE A.INSTITUTION = :1
AND A.STRM = :2
AND ( A.SUBJECT = :3
OR :3 IS NULL)
AND B.EMPLID = A.EMPLID
AND A.STDNT_ENRL_STATUS = 'E'
AND A.EMPLID = C.EMPLID
AND A.INSTITUTION = C.INSTITUTION
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_GRPS_HIST C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.INSTITUTION = C_ED.INSTITUTION
AND C.STDNT_GROUP = C_ED.STDNT_GROUP
AND C_ED.EFFDT <= A.START_DT)
AND C.STDNT_GROUP = 'SEEK'
AND C.EFF_STATUS = 'A'
AND A.STRM = G.STRM
AND G.CRSE_ID = A.CRSE_ID
AND G.CRSE_OFFER_NBR = A.CRSE_OFFER_NBR
AND G.SESSION_CODE = A.SESSION_CODE
AND G.CLASS_SECTION = A.CLASS_SECTION
AND G.CRSE_ATTR = 'SEEK'
UNION
SELECT D.SUBJECT, D.CATALOG_NBR, D.DESCR, D.CLASS_SECTION, D.SESSION_CODE, E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME, D.EMPLID, D.CRSE_GRADE_OFF, 'No'
FROM PS_CLASS_TBL_SE_VW D, PS_CU_BIO_VW E, PS_CLASS_ATTRIBUTE H
WHERE D.INSTITUTION = :1
AND D.STRM = :2
AND ( D.SUBJECT = :3
OR :3 IS NULL)
AND D.EMPLID = E.EMPLID
AND NOT EXISTS (SELECT F.EMPLID
FROM PS_STDNT_GRPS_HIST F
WHERE F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_STDNT_GRPS_HIST F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.INSTITUTION = F_ED.INSTITUTION
AND F.STDNT_GROUP = F_ED.STDNT_GROUP
AND F_ED.EFFDT <= D.START_DT)
AND F.EMPLID = D.EMPLID
AND F.INSTITUTION = D.INSTITUTION
AND F.STDNT_GROUP = 'SEEK'
AND F.EFF_STATUS = 'A')
AND D.STDNT_ENRL_STATUS = 'E'
AND D.STRM = H.STRM
AND H.CRSE_ID = D.CRSE_ID
AND H.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR
AND H.SESSION_CODE = D.SESSION_CODE
AND H.CLASS_SECTION = D.CLASS_SECTION
AND H.CRSE_ATTR = 'SEEK'
ORDER BY 1, 2, 4, 6, 7, 8