SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PS_CER_ROS_SCH_VW] (CAMPUS, STRM, CLASS_NBR, CLASS_SECTION,
 CLASS_MTG_NBR, EMPLID, DESCR, CRSE_ID, SUBJECT, CATALOG_NBR, TITLE,
 ACAD_GROUP, ACAD_ORG, SESSION_CODE, SESSION_DESCRSHORT, UNITS_MINIMUM
, UNITS_MAXIMUM, CONTACT_HOURS, SSR_COMPONENT, START_DT, END_DT, MON,
 TUES, WED, THURS, FRI, SAT, SUN, MEETING_TIME_START, MEETING_TIME_END
, FACILITY_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ENRL_CAP, ENRL_TOT,
 CRSE_ATTR_VALUE, CLASS_TYPE, CLASS_STAT, ACAD_CAREER, GRADING_BASIS,
 CER_GRADABLE) AS SELECT DISTINCT C.CAMPUS , C.STRM , C.CLASS_NBR ,
 C.CLASS_SECTION , P.CLASS_MTG_NBR , INST.EMPLID , T.DESCR , C.CRSE_ID
 , C.SUBJECT , C.CATALOG_NBR , C.DESCR AS TITLE , C.ACAD_GROUP ,
 C.ACAD_ORG , C.SESSION_CODE , ( SELECT XLATSHORTNAME FROM PSXLATITEM
 AS X WHERE(FIELDNAME = 'SESSION_CODE') AND (FIELDVALUE =
 C.SESSION_CODE) AND (EFF_STATUS = 'A') AND EFFDT = ( SELECT
 MAX(EFFDT) AS EXPR1 FROM PSXLATITEM AS X2 WHERE (X.FIELDNAME =
 FIELDNAME) AND (X.FIELDVALUE = FIELDVALUE) AND (EFF_STATUS = 'A') AND
 (EFFDT <= C.START_DT))) AS SESSION_DESCRSHORT, CAT.UNITS_MINIMUM,
 CAT.UNITS_MAXIMUM, COM.CONTACT_HOURS, C.SSR_COMPONENT, C.START_DT,
 C.END_DT, P.MON, P.TUES, P.WED, P.THURS, P.FRI, P.SAT, P.SUN,
 ISNULL(P.MEETING_TIME_START, '00:00:00') AS MEETING_TIME_START,
 ISNULL(P.MEETING_TIME_END, '00:00:00') AS MEETING_TIME_END,
 P.FACILITY_ID, PER.LAST_NAME, PER.FIRST_NAME, PER.MIDDLE_NAME,
 C.ENRL_CAP, C.ENRL_TOT, ISNULL(ATT.CRSE_ATTR_VALUE, 'N') AS
 CRSE_ATTR_VALUE, C.CLASS_TYPE, C.CLASS_STAT, C.ACAD_CAREER,
 ISNULL(ASS.GRADING_BASIS, 'NON') AS GRADING_BASIS, CASE WHEN
 (C.SSR_COMPONENT = ASS.SSR_COMPONENT AND C.CRSE_ID = ASS.CRSE_ID AND
 ASS.GRADING_BASIS NOT IN ('NON', 'NOG')) THEN 'Y' ELSE 'N' END AS
 GRADABLE FROM PS_CLASS_TBL AS C INNER JOIN PS_TERM_TBL AS T ON C.STRM
 = T.STRM AND C.ACAD_CAREER = T.ACAD_CAREER LEFT OUTER JOIN
 PS_CLASS_MTG_PAT AS P ON C.STRM = P.STRM AND C.CRSE_ID = P.CRSE_ID
 AND C.CRSE_OFFER_NBR = P.CRSE_OFFER_NBR AND C.CLASS_SECTION =
 P.CLASS_SECTION AND C.SESSION_CODE = P.SESSION_CODE LEFT OUTER JOIN
 PS_CRSE_CATALOG AS CAT ON CAT.CRSE_ID = C.CRSE_ID AND CAT.EFF_STATUS
 = 'A' AND CAT.EFFDT = ( SELECT MAX(EFFDT) AS EXPR1 FROM
 PS_CRSE_CATALOG AS CAT2 WHERE (CRSE_ID = CAT.CRSE_ID) AND (EFFDT <=
 C.START_DT) AND (EFF_STATUS = 'A')) LEFT OUTER JOIN PS_CRSE_COMPONENT
 AS COM ON COM.CRSE_ID = C.CRSE_ID AND COM.SSR_COMPONENT =
 C.SSR_COMPONENT AND COM.EFFDT = ( SELECT MAX(EFFDT) AS EXPR1 FROM
 PS_CRSE_COMPONENT AS COM2 WHERE (CRSE_ID = COM.CRSE_ID) AND (EFFDT <=
 C.START_DT)) LEFT OUTER JOIN PS_CLASS_INSTR AS INST ON INST.CRSE_ID =
 C.CRSE_ID AND INST.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR AND INST.STRM =
 C.STRM AND INST.SESSION_CODE = C.SESSION_CODE AND INST.CLASS_SECTION
 = C.CLASS_SECTION AND INST.CLASS_MTG_NBR = P.CLASS_MTG_NBR LEFT OUTER
 JOIN PS_PERSON_NAME AS PER ON PER.EMPLID = INST.EMPLID LEFT OUTER
 JOIN PS_CLASS_ATTRIBUTE AS ATT ON ATT.CRSE_ID = C.CRSE_ID AND
 ATT.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR AND ATT.STRM = C.STRM AND
 ATT.SESSION_CODE = C.SESSION_CODE AND ATT.CLASS_SECTION =
 C.CLASS_SECTION AND ATT.CRSE_ATTR = 'XB01' AND ATT.CRSE_ATTR_VALUE IN
 ('E', 'P') LEFT OUTER JOIN PS_CLASS_ASSOC AS ASS ON ASS.CRSE_ID =
 C.CRSE_ID AND ASS.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR AND ASS.STRM =
 C.STRM AND ASS.SESSION_CODE = C.SESSION_CODE AND ASS.ASSOCIATED_CLASS
 = C.ASSOCIATED_CLASS WHERE (C.CLASS_STAT IN ('A','S','T')) AND
 (C.SUBJECT NOT IN ('OHRS', 'REL', 'DSPS'))