USP_DATAFORMTEMPLATE_ADD_STUDENTSCHEDULE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_STUDENTSCHEDULE
(
    @ID uniqueidentifier = null output,    --STUDENTID

    @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SESSIONID uniqueidentifier = null,
    @STUDENTID uniqueidentifier = null,
    @COURSEREQUESTS xml = null,
    @COURSETERMS xml = null,
    @OVERRIDEEXCEPTIONS bit = 0
)
as
    set nocount on

    if @OVERRIDEEXCEPTIONS = 0 and (select count(COURSEID) from dbo.UFN_STUDENTCOURSEREQUESTS_GETEXCEPTIONS(@STUDENTID,@SESSIONID,@COURSEREQUESTS)) > 0
        raiserror('BBERR_STUDENTCOURSEREQUEST_EXCEPTIONS', 13, 1)

    declare @CHANGEDATE datetime = null

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    if @CHANGEDATE is null 
        set @CHANGEDATE = getdate()

    exec dbo.USP_STUDENTCOURSEREQUEST_UPDATEFROMXML @CURRENTAPPUSERID, @STUDENTID, @COURSEREQUESTS, @CHANGEAGENTID, @CHANGEDATE

    -- build a temporary table containing the values from the XML

    declare @TempTbl table (
       [ID] uniqueidentifier,
       [COURSEID] uniqueidentifier,
       [CLASSID] uniqueidentifier,
       [TERMID] uniqueidentifier)

    insert into @TempTbl
    select 
        T.c.value('(ID)[1]','uniqueidentifier') AS [ID],
        T.c.value('(COURSEID)[1]','uniqueidentifier') AS [COURSEID],
        T.c.value('(CLASSID)[1]','uniqueidentifier') AS [CLASSID],
        T.c.value('(TERMID)[1]','uniqueidentifier') AS [TERMID]
    from @COURSETERMS.nodes('/COURSETERMS/ITEM') T(c)

    declare @contextCache varbinary(128);
    declare @e int;

    -- cache current context information 

    set @contextCache = CONTEXT_INFO();

    -- set CONTEXT_INFO to @CHANGEAGENTID 

    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

    -- delete any items that no longer exist in the XML table

    delete from dbo.[STUDENTCLASSMEETINGGROUP]
    from dbo.STUDENTCLASSMEETINGGROUP
        inner join dbo.STUDENTCOURSE on STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID
        inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
        inner join dbo.TERM on
            CLASSMEETINGGROUP.STARTDATE = TERM.STARTDATE and
            CLASSMEETINGGROUP.ENDDATE = TERM.ENDDATE
        left join @TempTbl COURSETERMS on
            STUDENTCOURSE.ID = COURSETERMS.ID and
            STUDENTCOURSE.COURSEID = COURSETERMS.COURSEID and
            CLASSMEETINGGROUP.CLASSID = COURSETERMS.CLASSID and
            TERM.ID = COURSETERMS.TERMID
    where COURSETERMS.ID is null
        and STUDENTCOURSE.STUDENTID = @STUDENTID
        and TERM.SESSIONID = @SESSIONID
        and STUDENTCLASSMEETINGGROUP.STATUSCODE = 0

    select @e=@@error;

    -- reset CONTEXT_INFO to previous value 

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    if @e <> 0
        return 2;

    -- insert new items

    insert into [STUDENTCLASSMEETINGGROUP]
        ([ID],
        [STUDENTCOURSEID],
        [CLASSMEETINGGROUPID],
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED)
    select
        newid(),
        COURSETERMS.ID,
        CLASSMEETINGGROUP.ID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CHANGEDATE, 
        @CHANGEDATE
    from @TempTbl as COURSETERMS
        inner join dbo.STUDENTCOURSE on COURSETERMS.ID = STUDENTCOURSE.ID
        inner join dbo.TERM on COURSETERMS.TERMID = TERM.ID
        inner join dbo.CLASSMEETINGGROUP on
            CLASSMEETINGGROUP.CLASSID = COURSETERMS.CLASSID and 
            CLASSMEETINGGROUP.STARTDATE = TERM.STARTDATE and
            CLASSMEETINGGROUP.ENDDATE = TERM.ENDDATE
        left join dbo.STUDENTCLASSMEETINGGROUP on
            CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID and
            STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = COURSETERMS.ID
    where STUDENTCLASSMEETINGGROUP.ID is null

    return 0