|
SELECT DISTINCT A.CRSE_ID , A.CRSE_OFFER_NBR , A.STRM , A.SESSION_CODE , A.CLASS_SECTION , A.INSTITUTION , A.ACAD_CAREER , A.CLASS_NBR , A.SSR_COMPONENT , B.FIELDVALUE , E.DESCR1 , E.DESCR2 , E.DESCR3 , G.DESCR , I.RQMNT_DESIGNTN , D.CRSE_ATTR , D.CRSE_ATTR_VALUE , CASE WHEN D.DESCR1 <> ' ' THEN CASE WHEN J.SSR_CLSCFG_ATTRVAL = 'Y' THEN D.DESCR1 || '/' || D.DESCR2 ELSE D.DESCR1 END END , F.SSR_CAF_DESCR1 , F.SSR_CAF_DESCR2 , F.SSR_CAF_DESCR3 , F.SSR_CAF_DESCR4 , F.SSR_CAF_DESCR5 , K.SSR_SHIFT_ES , L.DESCR , M.DESCR , H.XLATLONGNAME , A.ASSOCIATED_CLASS , A.INSTRUCTION_MODE , C.WAIT_LIST_OKAY FROM PS_SSR_CTBL_AUD_ES A LEFT OUTER JOIN PS_SSR_CATR_AUD_VW D ON A.CRSE_ID = D.CRSE_ID AND A.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND A.STRM = D.STRM AND A.SESSION_CODE = D.SESSION_CODE AND A.CLASS_SECTION = D.CLASS_SECTION LEFT OUTER JOIN PS_SSR_MTG_AUD_VW E ON A.CRSE_ID = E.CRSE_ID AND A.CRSE_OFFER_NBR = E.CRSE_OFFER_NBR AND A.STRM = E.STRM AND A.SESSION_CODE = E.SESSION_CODE AND A.CLASS_SECTION = E.CLASS_SECTION LEFT OUTER JOIN PS_SSR_CASC_AUD_ES F ON A.CRSE_ID = F.CRSE_ID AND A.CRSE_OFFER_NBR = F.CRSE_OFFER_NBR AND A.STRM = F.STRM AND A.SESSION_CODE = F.SESSION_CODE AND ((F.ASSOCIATED_CLASS = A.ASSOCIATED_CLASS AND F.CLASS_SECTION = A.CLASS_SECTION) OR A.ASSOCIATED_CLASS = 9999) LEFT OUTER JOIN PS_SSR_RQDS_AUD_VW I ON A.CRSE_ID = I.CRSE_ID AND A.CRSE_OFFER_NBR = I.CRSE_OFFER_NBR AND A.STRM = I.STRM AND A.SESSION_CODE = I.SESSION_CODE AND (I.ASSOCIATED_CLASS = A.ASSOCIATED_CLASS OR I.ASSOCIATED_CLASS = 9999) LEFT OUTER JOIN PS_SSR_SHFT_AUD_VW K ON A.CRSE_ID = K.CRSE_ID AND A.CRSE_OFFER_NBR = K.CRSE_OFFER_NBR AND A.STRM = K.STRM AND A.SESSION_CODE = K.SESSION_CODE AND A.CLASS_SECTION = K.CLASS_SECTION , PSXLATITEM B , PS_SSR_ENRL_OPT_VW C , PS_SSR_UNIT_AUD_VW G , PSXLATITEM H , PS_SSR_CS_GEN_OPTS J , PS_CAMPUS_TBL L , PS_LOCATION_TBL M , PS_SET_CNTRL_REC S , PS_TERM_TBL T WHERE T.INSTITUTION = A.INSTITUTION AND T.ACAD_CAREER = A.ACAD_CAREER AND T.STRM = A.STRM AND ( (( B.FIELDNAME = 'SSR_CLSRCH_QRY_VAL' AND B.FIELDVALUE IN ('O','C','W') AND C.WAIT_LIST_OKAY = 'Y') OR ( B.FIELDNAME = 'SSR_CLSRCH_QRY_VAL' AND B.FIELDVALUE IN ('O','C') AND C.WAIT_LIST_OKAY <> 'Y')) AND G.CRSE_ID = A.CRSE_ID AND G.CRSE_OFFER_NBR = A.CRSE_OFFER_NBR AND G.STRM = A.STRM AND G.SESSION_CODE = A.SESSION_CODE AND G.ASSOCIATED_CLASS = A.ASSOCIATED_CLASS AND G.CLASS_SECTION = A.CLASS_SECTION AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PSXLATITEM B_ED WHERE B.FIELDNAME = B_ED.FIELDNAME AND B.FIELDVALUE = B_ED.FIELDVALUE AND B_ED.EFFDT <= %CurrentDateIn) AND B.EFF_STATUS = 'A' AND H.FIELDNAME = 'SSR_COMPONENT' AND H.FIELDVALUE = A.SSR_COMPONENT AND H.EFFDT = ( SELECT MAX(H_ED.EFFDT) FROM PSXLATITEM H_ED WHERE H.FIELDNAME = H_ED.FIELDNAME AND H.FIELDVALUE = H_ED.FIELDVALUE AND H_ED.EFFDT <= %CurrentDateIn) AND H.EFF_STATUS = 'A' AND L.INSTITUTION = A.INSTITUTION AND L.CAMPUS = A.CAMPUS AND L.EFFDT = ( SELECT MAX(L_ED.EFFDT) FROM PS_CAMPUS_TBL L_ED WHERE L.INSTITUTION = L_ED.INSTITUTION AND L.CAMPUS = L_ED.CAMPUS AND L_ED.EFFDT <= T.TERM_BEGIN_DT) AND L.EFF_STATUS = 'A' AND S.RECNAME = 'LOCATION_TBL' AND S.SETCNTRLVALUE = A.INSTITUTION AND M.SETID = S.SETID AND M.LOCATION = A.LOCATION AND M.EFFDT = ( SELECT MAX(M_ED.EFFDT) FROM PS_LOCATION_TBL M_ED WHERE M.SETID = M_ED.SETID AND M.LOCATION = M_ED.LOCATION AND M_ED.EFFDT <= T.TERM_BEGIN_DT) AND M.EFF_STATUS = 'A' )
|
CRSE_ID