UFN_FACULTY_GETOTHERCLASSESWITHOUTCATEGORIES

create function dbo.UFN_FACULTY_GETOTHERCLASSESWITHOUTCATEGORIES
(
    @CURRENTAPPUSERID uniqueidentifier,
    @CLASSID uniqueidentifier
)
returns @CLASSES table(
    ID uniqueidentifier null,
    NAME nvarchar(100) not null,
    COURSEID uniqueidentifier not null,
    ISPARENTROW bit not null
)
as 
begin
    declare @FACULTYID uniqueidentifier = (select CONSTITUENTID from dbo.APPUSER where ID = @CURRENTAPPUSERID)
    declare @ACADEMICYEARNAMECODEID uniqueidentifier
    declare @SESSIONNAMECODEID uniqueidentifier

    select 
        @SESSIONNAMECODEID=SESSIONNAMECODEID, 
        @ACADEMICYEARNAMECODEID=ACADEMICYEARNAMECODEID 
    from 
        dbo.SESSION
        inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
        inner join dbo.GRADINGAPPUSERSESSION on SESSION.ID = GRADINGAPPUSERSESSION.SESSIONID
    where 
        GRADINGAPPUSERSESSION.ID = @CURRENTAPPUSERID

    insert into @CLASSES
    (
        ID, 
        NAME,
        COURSEID,
        ISPARENTROW
    )
    select 
        DISTINCT CLASSID, 
        T1.NAME,
        CLASS.COURSEID,
        0
    from 
        dbo.UFN_FACULTY_GETCLASSMEETINGGROUPS
        (
            @FACULTYID,
            @ACADEMICYEARNAMECODEID,
            @SESSIONNAMECODEID,
            null
        ) T1
        inner join dbo.CLASS on T1.CLASSID = CLASS.ID
    where
        not exists(select ID from dbo.CLASSCATEGORY where CLASSID = T1.CLASSID)
        and CLASSID <> @CLASSID

    update 
        CL
    set 
        ISPARENTROW = 1
    from 
        @CLASSES CL
    where 
        (select COUNT(ID) from @CLASSES where CL.COURSEID = COURSEID) = 1

    insert into @CLASSES
    (
        NAME, 
        COURSEID, 
        ISPARENTROW
    )
    select 
        DISTINCT COURSE.COURSEID, 
        CL.COURSEID, 
        1
    from 
        @CLASSES CL
        inner join dbo.COURSE on CL.COURSEID = COURSE.ID
    where 
        ISPARENTROW = 0

    return
end