USP_DATAFORMTEMPLATE_EDIT_ITINERARYITEMSTAFFRESOURCEVOLUNTEERSCHEDULE

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ITINERARYITEMSTAFFRESOURCEVOLUNTEERSCHEDULE
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @VOLUNTEERS xml
                )
                as

                    set nocount on;

                    if @CHANGEAGENTID is null  
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                    begin try
                        declare @STATUSCODE tinyint;

                        select @STATUSCODE = SALESORDER.STATUSCODE
                        from dbo.SALESORDER
                        inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = SALESORDER.ID
                        inner join dbo.ITINERARYITEM on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                        inner join dbo.ITINERARYITEMSTAFFRESOURCE on ITINERARYITEM.ID = ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
                        where ITINERARYITEMSTAFFRESOURCE.ID = @ID

                        if @STATUSCODE in (1, 5)
                            raiserror('BBERR_INVALIDSTATUS', 13, 1);

                        declare @JOBOCCURRENCEID uniqueidentifier
                        declare @DATE datetime

                        select
                            @JOBOCCURRENCEID = JOBOCCURRENCEID,
                            @DATE = ITINERARYITEM.STARTDATE
                        from dbo.ITINERARYITEMSTAFFRESOURCE
                        inner join dbo.ITINERARYITEM on
                            ITINERARYITEM.ID = ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
                        where 
                            ITINERARYITEMSTAFFRESOURCE.ID = @ID;

                        declare @VOLUNTEERTABLE table
                        (
                            FILLEDBYCODE tinyint,
                            JOBOCCURRENCEID uniqueidentifier,
                            VOLUNTEERASSIGNMENTID uniqueidentifier,
                            VOLUNTEERID uniqueidentifier,
                            DATE datetime
                        );

                        insert into @VOLUNTEERTABLE
                        (
                            VOLUNTEERASSIGNMENTID, 
                            VOLUNTEERID
                        )
                        select 
                            T.c.value('(ID)[1]','uniqueidentifier') AS 'VOLUNTEERASSIGNMENTID',
                            T.c.value('(VOLUNTEERID)[1]','uniqueidentifier') AS 'VOLUNTEERID'
                        from @VOLUNTEERS.nodes('/VOLUNTEERS/ITEM') T(c);

                        update @VOLUNTEERTABLE set 
                            VOLUNTEERASSIGNMENTID = case when VOLUNTEERASSIGNMENTID = '00000000-0000-0000-0000-000000000000' then null else VOLUNTEERASSIGNMENTID end,
                            VOLUNTEERID = case when VOLUNTEERID = '00000000-0000-0000-0000-000000000000' then null else VOLUNTEERID end

                        if exists
                        (
                            select 1 from @VOLUNTEERTABLE as VT
                            left outer join dbo.VOLUNTEER on
                                VOLUNTEER.ID = VT.VOLUNTEERID
                            where
                                VT.VOLUNTEERID is not null and
                                VOLUNTEER.ID is null
                        )
                            raiserror('BBERR_INVALIDVOLUNTEER', 13, 1);

                        /* First delete any records that no longer have a constituent associated with them */
                        /* Need to setup the context cache first so that the delete audit will work */

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

                        set @contextCache = CONTEXT_INFO();

                        /* set CONTEXT_INFO to @CHANGEAGENTID */
                        if not @CHANGEAGENTID is null
                            set CONTEXT_INFO @CHANGEAGENTID;

                        /* First delete from the volunteer assignments */
                        delete from dbo.VOLUNTEERASSIGNMENT
                        where 
                            VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = @JOBOCCURRENCEID and
                            not exists 
                            (
                                select 1 from @VOLUNTEERTABLE as VOLUNTEERTABLE
                                where 
                                    VOLUNTEERASSIGNMENT.ID = VOLUNTEERTABLE.VOLUNTEERASSIGNMENTID
                            );


                        select @e=@@error;

                        /* reset CONTEXT_INFO to previous value  */
                        if not @contextCache is null
                            set CONTEXT_INFO @contextCache;

                        if @e <> 0
                            return 2;

                        insert into dbo.VOLUNTEERASSIGNMENT
                        (
                            ID,
                            VOLUNTEERID, 
                            JOBOCCURRENCEID, 
                            DATE, 
                            ADDEDBYID, 
                            CHANGEDBYID, 
                            DATEADDED, 
                            DATECHANGED
                        )
                        select 
                            coalesce(VOLUNTEERTABLE.VOLUNTEERASSIGNMENTID, newid()),
                            VOLUNTEERTABLE.VOLUNTEERID,
                            @JOBOCCURRENCEID,
                            @DATE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        from @VOLUNTEERTABLE as VOLUNTEERTABLE
                        where 
                            VOLUNTEERTABLE.VOLUNTEERID is not null and
                            (
                                VOLUNTEERTABLE.VOLUNTEERASSIGNMENTID is null or
                                not exists 
                                (
                                    select 1 from dbo.VOLUNTEERASSIGNMENT
                                    where 
                                        VOLUNTEERASSIGNMENT.ID = VOLUNTEERTABLE.VOLUNTEERASSIGNMENTID
                                )
                            ) and
                            not exists
                            (
                                select 1 from dbo.VOLUNTEERASSIGNMENT
                                where
                                    VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = @JOBOCCURRENCEID and
                                    VOLUNTEERASSIGNMENT.VOLUNTEERID = VOLUNTEERTABLE.VOLUNTEERID and
                                    VOLUNTEERASSIGNMENT.DATE = @DATE
                            )
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;