Stored Procedure - usp_DimCurriculum

Property	Value
Name	usp_DimCurriculum_Select
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name

Data Type

Direction

Description

Name	Type
ASSIGNMENT_A	Table
FAC192_SITE_A	Table
FAC199_LOCATION_A	Table
LookupTimeBlock	Table
ORG_CHART_A	Table
ORG_LEVEL_CODE_A	Table
POSITION_A	Table
ST_CLASS_A	Table
ST_CLASS_INSTR_A	Table
ST_COURSE_A	Table
ST_INSTR_RSRC_A	Table
ST_SESSION_A	Table
ST_SPCL_DSGNTR_A	Table
ST_TERM_A	Table
UTL_CODE_TABLE	Table
UTL_CODE_TABLE_GENERIC	Table

Name	Type
UTL_CODE_TABLE	TABLE
UTL_CODE_TABLE_GENERIC	TABLE
ASSIGNMENT_A	TABLE
FAC192_SITE_A	TABLE
FAC199_LOCATION_A	TABLE
LookupTimeBlock	TABLE
ORG_CHART_A	TABLE
ORG_LEVEL_CODE_A	TABLE
POSITION_A	TABLE
ST_CLASS_A	TABLE
ST_CLASS_INSTR_A	TABLE
ST_COURSE_A	TABLE
ST_INSTR_RSRC_A	TABLE
ST_SESSION_A	TABLE
ST_SPCL_DSGNTR_A	TABLE
ST_TERM_A	TABLE
udf_NameParser	USERDEFINEDFUNCTION
udf_StringToDate	USERDEFINEDFUNCTION

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimCurriculum_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimCurriculum_Select
Title:            Primary Select for the Curriculum Dimension
Date:            11/1/2012
System/Project:    Executive Dashboard - Enrollment
Description:    This procedure will combine the Class and Course information from DWStaging for
the creation of the Curriculum dimension for the Data Warehouse.
Revision History:
05/02/2013 Hariett Stoddard    Modified all varchar attributes to include a left and right trim function.
05/09/2013 Ian Peterson        Added columns for PrimaryInstructorType, ICSCategoryCode, InstructionalClassificationCategory,
                                TermCourseIdCount, IsTermCourseIdCountGE10, IsCourseRepeatable, and
                                CourseNumberTimesForCredit.
                                This also included outer join statements to get the data.
10/10/2013 Sophia Cowan Added @DaUppl, CourseLevel to Main Select from ST_CLASS-A
12/09/2013 Sophia Cowan Add ISNULL to Min and Max Class Size
05/01/2014 Sophia Cowan Added AND sess.SESSION_KEY IS NOT NULL to ST_SESSION_A join
05/15/2014 Sophia Cowan Added CreditNonCreditVocationalTest
08/13/2014 Sophia Cowan Increased Division and Department to varchar(60)
Added DivisionDescription DepartmentDescription
11/05/2015 Sophia Cowan Moved IsFaculty Select to a Temporary Table to improve efficiency
*/
/* Primary Curriculum Source SELECT Statement*/
CREATE PROC [dbo].[usp_DimCurriculum_Select]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
     CAST(999999 AS int) AS ReferenceNumberAK
    ,CAST(999999 AS int) AS ReferenceNumber
    ,CAST('aaa' AS varchar(6)) AS Term
    ,CAST('aaa' AS varchar(30)) AS TermTitle
    ,CAST('aaa' AS varchar(10)) AS CourseId
    ,CAST('aaa' AS varchar(10)) AS CurrentCourseId
    ,CAST('aaa' AS varchar(6)) AS CurrentTerm
    ,CAST('aaa' AS varchar(3)) AS CourseIdPrefix
    ,CAST('aaa' AS varchar(25)) AS ClassTitle
    ,CAST('aaa' AS varchar(3)) AS CourseTypeCode
    ,CAST('aaa' AS varchar(30)) AS CourseType
    ,CAST('aaa' AS varchar(15)) AS CreditNonCreditVocational
    ,CAST('aaa' AS varchar(3)) AS CreditTypeCode
    ,CAST('aaa' AS varchar(30)) AS CreditType
    ,CAST('aaa' AS varchar(15)) AS CreditNonCredit
    ,CAST('aaa' AS varchar(5)) AS ICSCode
    ,CAST('aaa' AS varchar(30)) AS InstructionalClassification
    ,CAST('aaa' AS varchar(4)) AS DivisionCode
    ,CAST('aaa' AS varchar(4)) AS DepartmentCode
    ,CAST('aaa' AS varchar(60)) AS Division
    ,CAST('aaa' AS varchar(60)) AS Department
    ,CAST('aaa' AS varchar(3)) AS CampusCode
    ,CAST('aaa' AS varchar(4)) AS CenterCode
    ,CAST('aaa' AS varchar(50)) AS Campus
    ,CAST('aaa' AS varchar(50)) AS Center
    ,CAST(9999 AS smallint) AS MinimumClassSize
    ,CAST(9999 AS smallint) AS MaximumClassSize
    ,CAST('aaa' AS varchar(3)) AS ClassStatusCode
    ,CAST('aaa' AS varchar(30)) AS ClassStatus
    ,CAST('aaa' AS varchar(3)) AS TimeBlockCode
    ,CAST('aaa' AS varchar(30)) AS     TimeBlock
    ,CAST('aaa' AS varchar(30)) AS DeliveryMethod
    ,CAST('aaa' AS varchar(3)) AS Honors
    ,CAST('aaa' AS varchar(3)) AS WeekendCollege
    ,CAST('aaa' AS varchar(3)) AS DualEnrollment
    ,CAST('aaa' AS varchar(3)) AS IndependentStudy
    ,CAST('aaa' AS varchar(3)) AS Apprenticeship
    ,CAST('aaa' AS varchar(3)) AS VirtualCollege
    ,CAST('aaa' AS varchar(3)) AS Online100Percent
    ,CAST('aaa' AS varchar(3)) AS WebEnabled
    ,CAST('aaa' AS varchar(3)) AS WebBlended
    ,CAST('aaa' AS varchar(250)) AS ScheduleInformation
    ,CAST('aaa' AS varchar(4)) AS SessionCode
    ,CAST('aaa' AS varchar(30)) AS Session
    ,CAST(999999 AS numeric(9)) AS PrimaryInstructorID
    ,CAST('aaa' AS varchar(50)) AS PrimaryInstructorFullName
    ,CAST('aaa' AS varchar(50)) AS PrimaryInstructorLastName
    ,CAST('aaa' AS varchar(30)) AS PrimaryInstructorFirstName
    ,CAST('aaa' AS varchar(20)) AS PrimaryInstructorMiddleInitial
    ,CAST('aaa' AS varchar(10)) AS PrimaryInstructorType
    ,CAST('aaa' AS varchar(3)) AS ICSCategoryCode
    ,CAST('aaa' AS varchar(30)) AS InstructionalClassificationCategory
    ,CAST(0 AS smallint) AS TermCourseIdCount
    ,CAST('aaa' AS varchar(3)) AS IsTermCourseIdCountGE10
    ,CAST('aaa' AS varchar(3)) AS IsCourseRepeatable
    ,CAST(0 AS smallint) AS CourseNumberTimesForCredit
    ,CAST('aaa' AS VARCHAR(10)) AS CourseLevel
    ,CAST('aaa' AS VARCHAR(10)) AS CreditNonCreditVocationalTest
    ,CAST('aaa' AS varchar(50)) AS DivisionDescription
    ,CAST('aaa' AS varchar(50)) AS DepartmentDescription

    END
/* Get String that matched DA-APPL description from SITE-PARAM Table */

DECLARE @DaUppl VARCHAR(30)
SET @DaUppl =
(
  SELECT
     CAST(CASE
       WHEN SiteParam.DESCRIPTION IS NULL THEN '$'
       ELSE SUBSTRING(DESCRIPTION,3,10)
       END
        AS VARCHAR(10))
  FROM UTL_CODE_TABLE SiteParam
  WHERE SiteParam.TABLE_NAME = 'SITE-PARAM'
  AND SiteParam.CODE = 'DA-UPPL' AND SiteParam.STATUS = 'A'
)

CREATE TABLE #instr_rsrc_temp
    (
    [INSTR_ID] VARCHAR(9)  NOT NULL
    ,[SEARCH_NAME] VARCHAR(25) NOT NULL
    ,Max_CAL_YR INT NULL
    )
INSERT INTO #instr_rsrc_temp
    (
     [INSTR_ID]
    ,[SEARCH_NAME]
    ,Max_CAL_YR
    )
    SELECT   instr_rsrc1.INSTR_ID
             ,LTRIM(RTRIM(instr_rsrc2.SEARCH_NAME))
             ,instr_rsrc2.CAL_YR
    FROM (
           SELECT instr_rsrc2.INSTR_ID
                  ,MAX(instr_rsrc2.CAL_YR) AS Max_CAL_YR
            FROM ST_INSTR_RSRC_A instr_rsrc2
            GROUP BY INSTR_ID
          )
         AS instr_rsrc1
         INNER JOIN ST_INSTR_RSRC_A AS instr_rsrc2
           ON (
                instr_rsrc1.INSTR_ID = instr_rsrc2.INSTR_ID and
                instr_rsrc2.CAL_YR = instr_rsrc1.Max_CAL_YR
               )
     ORDER BY instr_rsrc1.INSTR_ID
CREATE TABLE #Faculty
(
     PERSON_ID INT NOT NULL
    ,START_DATE VARCHAR(8) NOT NULL
    ,END_DATE VARCHAR(8) NULL
    ,IsFaculty VARCHAR(1) NOT NULL
)
INSERT INTO #Faculty
(
     PERSON_ID
    ,START_DATE
    ,END_DATE
    ,IsFaculty
)
        SELECT DISTINCT
            ASSIGNMENT_A.PERSON_ID
            ,ASSIGNMENT_A.[START_DATE]
            ,ASSIGNMENT_A.END_DATE
            ,jobgroupDetail.FIELD_VALUE AS IsFaculty
        FROM ASSIGNMENT_A

        /* Get Position Details *//
        LEFT OUTER JOIN POSITION_A pos
            ON (
                pos.POSITION_CODE = ASSIGNMENT_A.POSITION_CODE
                )
        /* Get Job Group Table record */
        LEFT OUTER JOIN UTL_CODE_TABLE jobgroup
            on (
                jobgroup.TABLE_NAME = 'JOB-GROUP' AND
                jobgroup.CODE = pos.JOB_GROUP_CODE AND
                jobgroup.[STATUS] = 'A'
                )
        /* Get job group table - faculty attribute *//
        LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC jobgroupDetail
           ON (
                jobgroup.ISN_UTL_CODE_TABLE = jobgroupDetail.ISN_UTL_CODE_TABLE
                AND jobgroupDetail.cnxarraycolumn =     2/* Third sub-attribute of table */
               )
        WHERE jobgroupDetail.FIELD_VALUE = 'Y'    /* Assignment has a Faculty Job Group *//

/* Curriculum Source Select Statement */
SELECT
     cls.REF_NUM AS ReferenceNumberAK
    ,cls.REF_NUM AS ReferenceNumber
    ,LTRIM(RTRIM(SUBSTRING(cls.CLASS_KEY,12,6))) AS Term
    ,LTRIM(RTRIM(ISNULL(Term.TRM_TTL, 'N/A'))) AS TermTitle
    ,LTRIM(RTRIM(SUBSTRING(cls.CLASS_KEY,2,10))) AS CourseId
    ,LTRIM(RTRIM(ISNULL(crs.CRNT_CRS, 'N/A'))) AS CurrentCourseId
    ,LTRIM(RTRIM(ISNULL(crs.CRNT_CRS_EFF_TRM, 'N/A'))) AS CurrentTerm
    ,LTRIM(RTRIM(SUBSTRING(cls.CLASS_KEY,2,3))) AS CourseIdPrefix
    ,LTRIM(RTRIM(ISNULL(cls.CRS_TTL, 'N/A'))) AS ClassTitle
    ,LTRIM(RTRIM(ISNULL(CAST(crs.CRS_TY AS varchar(3)), 'N/A'))) AS CourseTypeCode
    ,LTRIM(RTRIM(ISNULL(crsty.DESCRIPTION, 'N/A'))) AS CourseType
    ,CAST(CASE
        WHEN crs.CRED_TY = '05' then 'Vocational'
        WHEN LEFT(cls.CLASS_KEY,1) = 'N' then 'NonCredit'
        WHEN LEFT(cls.CLASS_KEY,1) = 'T' then 'NonCredit'
        WHEN LEFT(cls.CLASS_KEY,1) = 'C' then 'Credit'
        ELSE 'N/A'
      END
        as varchar(15)) AS CreditNonCreditVocational
    ,LTRIM(RTRIM(ISNULL(CAST(crs.CRED_TY AS varchar(3)), 'N/A'))) AS CreditTypeCode
    ,LTRIM(RTRIM(ISNULL(credty.DESCRIPTION, 'N/A'))) AS CreditType
    ,CAST(CASE
        WHEN LEFT(cls.CLASS_KEY,1) = 'C' then 'Credit'
        WHEN LEFT(cls.CLASS_KEY,1) = 'N' then 'NonCredit'
        WHEN LEFT(cls.CLASS_KEY,1) = 'T' then 'NonCredit'
        ELSE 'N/A'
      END
        as varchar(15)) AS CreditNonCredit
    ,LTRIM(RTRIM(ISNULL(crs.ICS_NUM, 'N/A'))) AS ICSCode
    ,LTRIM(RTRIM(ISNULL(icscd.DESCRIPTION, 'N/A'))) AS InstructionalClassification
    ,LTRIM(RTRIM(SUBSTRING(cls.DIV_DEPT_CD,1,4))) AS DivisionCode
    ,LTRIM(RTRIM(SUBSTRING(cls.DIV_DEPT_CD,5,4))) AS DepartmentCode
    ,LTRIM(RTRIM(SUBSTRING(cls.DIV_DEPT_CD,1,4))) + ' - ' + LTRIM(RTRIM(CAST(ISNULL(div.ORG_LEVEL_CODE_DESC, 'N/A') AS varchar(60)))) AS Division
    ,LTRIM(RTRIM(SUBSTRING(cls.DIV_DEPT_CD,5,4))) + ' - ' + LTRIM(RTRIM(CAST(ISNULL(orgChart.ORG_CHART_DESCRIPTION, 'N/A') AS varchar(60)))) AS Department
    ,LTRIM(RTRIM(SUBSTRING(cls.CLASS_KEY,18,1))) AS CampusCode
    ,LTRIM(RTRIM(SUBSTRING(cls.CLASS_KEY,19,4))) AS CenterCode
    ,LTRIM(RTRIM(ISNULL(site.SITE_NAME, 'N/A'))) AS Campus
    ,LTRIM(RTRIM(ISNULL(location.LOCATION_NAME, 'N/A'))) AS Center
    ,CAST(ISNULL(cls.MIN_CLS_SIZE,1) AS smallint) AS MinimumClassSize
    ,CAST(ISNULL(cls.MAX_CLS_SIZE,1) AS smallint) AS MaximumClassSize
    ,LTRIM(RTRIM(ISNULL(CAST(cls.CLS_STAT AS varchar(3)), 'N/A'))) AS ClassStatusCode
    ,LTRIM(RTRIM(ISNULL(clsstat.DESCRIPTION, 'N/A'))) AS ClassStatus
    ,LTRIM(RTRIM(ISNULL(CAST(cls.CLS_TM_BLK as varchar(3)), 'N/A'))) AS TimeBlockCode
    ,CAST(CASE
        WHEN cls.CLS_TM_BLK = 'D' then 'Day Time'
        WHEN cls.CLS_TM_BLK = 'E' then 'Evening'
        WHEN cls.CLS_TM_BLK = 'W' then 'Weekend'
        WHEN cls.CLS_TM_BLK = 'V' then 'Virtual'
        WHEN cls.CLS_TM_BLK = 'O' then 'Other'
        ELSE 'N/A'
      END
        as varchar(30)) AS     TimeBlock
    ,CAST(ISNULL(DistanceLearning, 'Conventional') AS varchar(30)) AS DeliveryMethod
    ,CAST(ISNULL(honors.Honors, 'No') AS varchar(3)) AS Honors
    ,CAST(ISNULL(weekcol.WeekCol, 'No') AS varchar(3)) AS WeekendCollege
    ,CAST(ISNULL(dualenrl.DualEnrl, 'No') AS varchar(3)) AS DualEnrollment
    ,CAST(ISNULL(indstudy.IndStudy, 'No') AS varchar(3)) AS IndependentStudy
    ,CAST(ISNULL(apptship.Apptship, 'No') AS varchar(3)) AS Apprenticeship
    ,CAST(ISNULL(virtcol.VirtCol, 'No') AS varchar(3)) AS VirtualCollege
    ,CAST(ISNULL(wonline.WOnLine, 'No')  AS varchar(3)) AS Online100Percent
    ,CAST(ISNULL(wenabled.WEnabled, 'No') AS varchar(3)) AS WebEnabled
    ,CAST(ISNULL(wblended.WBlended, 'No') AS varchar(3)) AS WebBlended
    ,CAST('TBA' AS varchar(250)) AS ScheduleInformation
    ,LTRIM(RTRIM(ISNULL(cls.SESSION_CD, 'N/A'))) AS SessionCode
    ,LTRIM(RTRIM(ISNULL(sess.SESSION_TTL, 'N/A'))) AS Session
    ,CAST
        (
        CASE
            WHEN instr.INSTR_ID = 'TBA' THEN '999999999'
            WHEN ISNUMERIC(instr.INSTR_ID) = 1 THEN instr.INSTR_ID
            ELSE '999999999'    -- HANLDES NULL AND ANYTHING ELSE
        END
        AS numeric(9)
        )
        AS PrimaryInstructorID
    ,LTRIM(RTRIM(CAST(ISNULL(employee.SEARCH_NAME, 'TBA') AS varchar(50)))) AS PrimaryInstructorFullName
    ,LTRIM(RTRIM(CAST(ISNULL(dbo.udf_NameParser(employee.SEARCH_NAME,'1'), 'N/A') AS varchar(50)))) AS PrimaryInstructorLastName
    ,LTRIM(RTRIM(CAST(ISNULL(dbo.udf_NameParser(Employee.SEARCH_NAME,'2'), 'N/A') AS varchar(30)))) AS PrimaryInstructorFirstName
    ,LTRIM(RTRIM(CAST(ISNULL(dbo.udf_NameParser(Employee.SEARCH_NAME,'3'), 'N/A') AS varchar(20)))) AS PrimaryInstructorMiddleInitial
    ,CASE
        WHEN FacultyAssignments.IsFaculty = 'Y' THEN 'Faculty'
        ELSE 'Adjunct'
     END AS PrimaryInstructorType
    ,CASE
        WHEN crs.ICS_NUM IS NULL THEN 'N/A'
        WHEN LTRIM(RTRIM(crs.ICS_NUM)) = '' THEN 'N/A'
        ELSE SUBSTRING(crs.ICS_NUM,1,3)
     END AS ICSCategoryCode
    ,ISNULL(icscatcd.DESCRIPTION, 'N/A') AS InstructionalClassificationCategory
    ,CrsCount.NumCoursesInTerm AS TermCourseIdCount
    ,CASE
        WHEN CrsCount.NumCoursesInTerm >= 10 THEN 'Yes'
        ELSE 'No'
     END AS IsTermCourseIdCountGE10
    ,CASE
        WHEN LEFT(cls.CLASS_KEY,1) = 'N' THEN 'Yes'
        WHEN LEFT(cls.CLASS_KEY,1) = 'T' THEN 'Yes'
        WHEN CAST(ISNULL(crs.TIMES_FOR_CRED,'0') AS NUMERIC(3)) > 1 THEN 'Yes'
        ELSE 'No'
     END AS IsCourseRepeatable
    ,CAST(ISNULL(crs.TIMES_FOR_CRED,'0') AS NUMERIC(3)) AS CourseNumberTimesForCredit
    ,CAST(CASE
      WHEN crs.CRED_TY >= '04' AND crs.CRED_TY <= '13' THEN 'Lower'
      WHEN LEFT(cls.CLASS_KEY,1) = 'N' or LEFT(cls.CLASS_KEY,1) = 'T' THEN 'Lower'
      WHEN CHARINDEX(SUBSTRING(cls.CLASS_KEY,5,1),@DaUppl)  > 0 THEN 'Upper'
      ELSE 'Lower'
      END AS VARCHAR(10)) AS CourseLevel
    ,CAST(CASE
        WHEN crs.CRED_TY = '05' then 'Vocational'
        WHEN LEFT(cls.CLASS_KEY,1) = 'N' then 'NonCredit'
        WHEN LEFT(cls.CLASS_KEY,1) = 'T' then 'Test'
        WHEN LEFT(cls.CLASS_KEY,1) = 'C' then 'Credit'
        ELSE 'N/A'
      END
        as varchar(15)) AS CreditNonCreditVocationalTest
    ,LTRIM(RTRIM(CAST(ISNULL(div.ORG_LEVEL_CODE_DESC, 'N/A') AS varchar(50)))) AS DivisionDescription
    ,LTRIM(RTRIM(CAST(ISNULL(orgChart.ORG_CHART_DESCRIPTION, 'N/A') AS varchar(50)))) AS         DepartmentDescription
FROM ST_CLASS_A cls
/* Get Term Information */
LEFT OUTER JOIN ST_TERM_A Term
   ON (
       Term.TRM_YR = SUBSTRING(cls.CLASS_KEY,12,6)
      )
/* Get Course Information */
LEFT OUTER JOIN ST_COURSE_A crs
   ON (
       SUBSTRING(cls.CLASS_KEY,2,10) = crs.CRS_ID
        AND SUBSTRING(cls.CLASS_KEY,12,6) >= crs.EFF_TRM AND
            SUBSTRING(cls.CLASS_KEY,12,6) <= ISNULL(crs.END_TRM, '999999')
       )
/* Determine Campus Site Physical *//
LEFT OUTER JOIN FAC192_SITE_A sitelocal
   ON (
       sitelocal.SITE_LOCAL_NUM = SUBSTRING(cls.CLASS_KEY,18,1)
       AND sitelocal.REC_TYPE = 'S'
       )
/* Get Campus - Site Name *//
LEFT OUTER JOIN FAC192_SITE_A site
   ON (
       site.SITE_NUM = sitelocal.SITE_NUM
       AND site.REC_TYPE = 'I'
       )
/* Get Center - Location Name *//
LEFT OUTER JOIN FAC199_LOCATION_A location
   ON (
       location.SITE_LOCAL_NUM = SUBSTRING(cls.CLASS_KEY,18,1)
       AND location.LOCATION_NUM =
            CASE
                WHEN SUBSTRING(cls.CLASS_KEY,19,4) = ' ' THEN '0000'
                ELSE SUBSTRING(cls.CLASS_KEY,19,4)
            END
       )
/* Get Session Information *//
LEFT OUTER JOIN ST_SESSION_A sess
   ON (
       CAST(SUBSTRING(cls.CLASS_KEY,12,6) + cls.SESSION_CD AS varchar(10)) = sess.SESSION_KEY
       AND sess.SESSION_KEY IS NOT NULL
       )
/* Get Credit Type Description */
LEFT OUTER JOIN UTL_CODE_TABLE credty
    on (
        credty.TABLE_NAME = 'CRED-TYPE' AND
        credty.CODE = crs.CRED_TY AND
        credty.[STATUS] = 'A'
        )
/* Get Class Status Description */
LEFT OUTER JOIN UTL_CODE_TABLE clsstat
    on (
        clsstat.TABLE_NAME = 'CLS-STAT' AND
        clsstat.CODE = cls.CLS_STAT AND
        clsstat.[STATUS] = 'A'
        )
/* Get Course Type Description */
LEFT OUTER JOIN UTL_CODE_TABLE crsty
    on (
        crsty.TABLE_NAME = 'CRS-TYPE' AND
        crsty.CODE = crs.CRS_TY AND
        crsty.[STATUS] = 'A'
        )
/* Get ICS Description */
LEFT OUTER JOIN UTL_CODE_TABLE icscd
    on (
        icscd.TABLE_NAME = 'CRSICSNUM' AND
        icscd.CODE = crs.ICS_NUM AND
        icscd.[STATUS] = 'A'
        )
/* Get Time Block Description */
LEFT OUTER JOIN LookupTimeBlock tb
    on (
        tb.TimeBlockCode = cls.CLS_TM_BLK
        )
/* Get Division Description */
LEFT OUTER JOIN ORG_CHART_A orgChart
    on (
        orgChart.ORG_CHART_CODE = cls.DIV_DEPT_CD
        AND orgChart.ORG_TYPE_CODE = 'STUCLASS'
        )
/* Get Department Description *//
LEFT OUTER JOIN ORG_LEVEL_CODE_A div
    ON (
        div.ORG_LEVEL_PART = LEFT(cls.DIV_DEPT_CD,4)
        AND div.ORG_LEVEL_NAME_CHILD = 'DIVISION'
        )
/* Get Class Primary Instructor Description *//
LEFT OUTER JOIN ST_CLASS_INSTR_A instr
    ON (
        instr.CLASS_KEY = cls.CLASS_KEY
        AND instr.INSTR_PRIM = 'Y'
        )
/* Get Primary Instructor Name */
LEFT OUTER JOIN #instr_rsrc_temp employee
    ON (
        CASE
            WHEN instr.INSTR_ID = 'TBA' THEN -999999
            ELSE instr.INSTR_ID
        END
        = employee.INSTR_ID
        )

/* Get Distance Learning Flag */
LEFT OUTER JOIN
(
    SELECT spdl.CLASS_KEY
          ,COUNT(ctdl.CODE) AS countdl
          ,CAST(
                CASE
                WHEN COUNT(ctdl.CODE) > 0 THEN 'Distance Learning'
                WHEN COUNT(ctdl.CODE) IS NULL THEN 'Conventional'
                ELSE 'Conventional'
                END AS varchar(30)
        ) AS DistanceLearning

    FROM ST_SPCL_DSGNTR_A spdl
    LEFT OUTER JOIN UTL_CODE_TABLE ctdl
      ON (
          ctdl.TABLE_NAME = 'DWDISTLRN' AND
          ctdl.CODE = spdl.SPCL_DSGNTR AND
          ctdl.STATUS = 'A'
          )

   GROUP BY spdl.CLASS_KEY

)  distlearn

   ON cls.CLASS_KEY = distlearn.CLASS_KEY

/* Get Honors Flag */
LEFT OUTER JOIN
(
    SELECT sphr.CLASS_KEY
          ,COUNT(cthr.CODE) AS counthr
          ,CAST(
                CASE
                WHEN COUNT(cthr.CODE) > 0 THEN 'Yes'
                WHEN COUNT(cthr.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS Honors

    FROM ST_SPCL_DSGNTR_A sphr
    LEFT OUTER JOIN UTL_CODE_TABLE cthr
      ON (
          cthr.TABLE_NAME = 'DWHONORS' AND
          cthr.CODE = sphr.SPCL_DSGNTR AND
          cthr.STATUS = 'A'
          )

   GROUP BY sphr.CLASS_KEY

)  honors

   ON cls.CLASS_KEY = honors.CLASS_KEY
/* Get Weekend College Flag */
LEFT OUTER JOIN
(
    SELECT spwk.CLASS_KEY
          ,COUNT(ctwk.CODE) AS countwk
          ,CAST(
                CASE
                WHEN COUNT(ctwk.CODE) > 0 THEN 'Yes'
                WHEN COUNT(ctwk.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS WeekCol

    FROM ST_SPCL_DSGNTR_A spwk
    LEFT OUTER JOIN UTL_CODE_TABLE ctwk
      ON (
          ctwk.TABLE_NAME = 'DWWKNDCOL' AND
          ctwk.CODE = spwk.SPCL_DSGNTR AND
          ctwk.STATUS = 'A'
          )

   GROUP BY spwk.CLASS_KEY

)  weekcol

   ON cls.CLASS_KEY = weekcol.CLASS_KEY

/* Get Dual Enrollment Flag */
LEFT OUTER JOIN
(
    SELECT spde.CLASS_KEY
          ,COUNT(ctde.CODE) AS countde
          ,CAST(
                CASE
                WHEN COUNT(ctde.CODE) > 0 THEN 'Yes'
                WHEN COUNT(ctde.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS DualEnrl

    FROM ST_SPCL_DSGNTR_A spde
    LEFT OUTER JOIN UTL_CODE_TABLE ctde
      ON (
          ctde.TABLE_NAME = 'DWDUALENRL' AND
          ctde.CODE = spde.SPCL_DSGNTR AND
          ctde.STATUS = 'A'
          )

   GROUP BY spde.CLASS_KEY

)  dualenrl

   ON cls.CLASS_KEY = dualenrl.CLASS_KEY
/* Get Indepedent Study Flag */
LEFT OUTER JOIN
(
    SELECT spis.CLASS_KEY
          ,COUNT(ctis.CODE) AS countis
          ,CAST(
                CASE
                WHEN COUNT(ctis.CODE) > 0 THEN 'Yes'
                WHEN COUNT(ctis.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS IndStudy

    FROM ST_SPCL_DSGNTR_A spis
    LEFT OUTER JOIN UTL_CODE_TABLE ctis
      ON (
          ctis.TABLE_NAME = 'DWINDSTDY' AND
          ctis.CODE = spis.SPCL_DSGNTR AND
          ctis.STATUS = 'A'
          )

   GROUP BY spis.CLASS_KEY

)  indstudy

   ON cls.CLASS_KEY = indstudy.CLASS_KEY
/* Get Appreticeship Flag */
LEFT OUTER JOIN
(
    SELECT spap.CLASS_KEY
          ,COUNT(ctap.CODE) AS countap
          ,CAST(
                CASE
                WHEN COUNT(ctap.CODE) > 0 THEN 'Yes'
                WHEN COUNT(ctap.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS Apptship

    FROM ST_SPCL_DSGNTR_A spap
    LEFT OUTER JOIN UTL_CODE_TABLE ctap
      ON (
          ctap.TABLE_NAME = 'DWAPPRTC' AND
          ctap.CODE = spap.SPCL_DSGNTR AND
          ctap.STATUS = 'A'
          )

   GROUP BY spap.CLASS_KEY

)  apptship

   ON cls.CLASS_KEY = apptship.CLASS_KEY
/* Get Virtual College Flag */
LEFT OUTER JOIN
(
    SELECT spvc.CLASS_KEY
          ,COUNT(ctvc.CODE) AS countvc
          ,CAST(
                CASE
                WHEN COUNT(ctvc.CODE) > 0 THEN 'Yes'
                WHEN COUNT(ctvc.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS VirtCol

    FROM ST_SPCL_DSGNTR_A spvc
    LEFT OUTER JOIN UTL_CODE_TABLE ctvc
      ON (
          ctvc.TABLE_NAME = 'DWVIRTCOL' AND
          ctvc.CODE = spvc.SPCL_DSGNTR AND
          ctvc.STATUS = 'A'
          )

   GROUP BY spvc.CLASS_KEY

)  virtcol

   ON cls.CLASS_KEY = virtcol.CLASS_KEY
/* Get Online100Percent Flag */
LEFT OUTER JOIN
(
    SELECT spon.CLASS_KEY
          ,COUNT(cton.CODE) AS counton
          ,CAST(
                CASE
                WHEN COUNT(cton.CODE) > 0 THEN 'Yes'
                WHEN COUNT(cton.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS WOnLine

    FROM ST_SPCL_DSGNTR_A spon
    LEFT OUTER JOIN UTL_CODE_TABLE cton
      ON (
          cton.TABLE_NAME = 'DW100WEB' AND
          cton.CODE = spon.SPCL_DSGNTR AND
          cton.STATUS = 'A'
          )

   GROUP BY spon.CLASS_KEY

)  wonline

   ON cls.CLASS_KEY = wonline.CLASS_KEY
/* Get Web Enhanced Flag */
LEFT OUTER JOIN
(
    SELECT spwe.CLASS_KEY
          ,COUNT(ctwe.CODE) AS countwe
          ,CAST(
                CASE
                WHEN COUNT(ctwe.CODE) > 0 THEN 'Yes'
                WHEN COUNT(ctwe.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS WEnabled

    FROM ST_SPCL_DSGNTR_A spwe
    LEFT OUTER JOIN UTL_CODE_TABLE ctwe
      ON (
          ctwe.TABLE_NAME = 'DWWEBENHC' AND
          ctwe.CODE = spwe.SPCL_DSGNTR AND
          ctwe.STATUS = 'A'
          )

   GROUP BY spwe.CLASS_KEY

)  wenabled

   ON cls.CLASS_KEY = wenabled.CLASS_KEY
/* Get Web Blended Flag */
LEFT OUTER JOIN
(
    SELECT spwb.CLASS_KEY
          ,COUNT(ctwb.CODE) AS countwb
          ,CAST(
                CASE
                WHEN COUNT(ctwb.CODE) > 0 THEN 'Yes'
                WHEN COUNT(ctwb.CODE) IS NULL THEN 'No'
                ELSE 'No'
                END AS varchar(30)
        ) AS WBlended

    FROM ST_SPCL_DSGNTR_A spwb
    LEFT OUTER JOIN UTL_CODE_TABLE ctwb
      ON (
          ctwb.TABLE_NAME = 'DWWEBBLND' AND
          ctwb.CODE = spwb.SPCL_DSGNTR AND
          ctwb.STATUS = 'A'
          )

   GROUP BY spwb.CLASS_KEY

)  wblended

   ON cls.CLASS_KEY = wblended.CLASS_KEY
/* Get Primary Instructor Assignment Details - the one(s) overlapping the class dates *//
LEFT OUTER JOIN #Faculty FacultyAssignments

    ON (
        FacultyAssignments.PERSON_ID = CAST(
            CASE
                WHEN instr.INSTR_ID = 'TBA' THEN '999999999'
                WHEN ISNUMERIC(instr.INSTR_ID) = 1 THEN instr.INSTR_ID
                ELSE '999999999'    -- HANLDES NULL AND ANYTHING ELSE
            END
            AS numeric(9))
        AND dbo.udf_StringToDate(FacultyAssignments.[START_DATE]) <= dbo.udf_StringToDate(sess.SESS_END_DT)
        AND (FacultyAssignments.[END_DATE] IS NULL OR
            LTRIM(RTRIM(FacultyAssignments.[END_DATE])) = '' OR
            dbo.udf_StringToDate(FacultyAssignments.[END_DATE]) >= dbo.udf_StringToDate(sess.SESS_BEG_DT))
        )
/* Get ICS category/Cluster Description */
LEFT OUTER JOIN UTL_CODE_TABLE icscatcd
    on (
        icscatcd.TABLE_NAME = 'DWICSCAT' AND
        icscatcd.CODE = SUBSTRING(crs.ICS_NUM,1,3) AND
        icscatcd.[STATUS] = 'A'
        )
/* Get Course Count Information for Term */
LEFT OUTER JOIN
    (
        SELECT COUNT(*) AS NumCoursesInTerm
            ,SUBSTRING(ClsCount.CLASS_KEY,12,6) AS Term
            ,SUBSTRING(ClsCount.CLASS_KEY,2,10) AS CourseId
        FROM ST_CLASS_A ClsCount
        GROUP BY SUBSTRING(ClsCount.CLASS_KEY,12,6), SUBSTRING(ClsCount.CLASS_KEY,2,10)
    ) AS CrsCount
   ON (
       SUBSTRING(cls.CLASS_KEY,12,6) = CrsCount.Term
       AND SUBSTRING(cls.CLASS_KEY,2,10) = CrsCount.CourseId
       )
ORDER BY cls.REF_NUM