USP_DATAFORMTEMPLATE_EDIT_ITINERARYITEMSTAFFRESOURCESTAFFBOARDSCHEDULE

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ITINERARYITEMSTAFFRESOURCESTAFFBOARDSCHEDULE
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @ASSIGNEDSTAFF 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 
                            ITINERARYITEM.ITINERARYID = ITINERARY.ID
                        inner join dbo.ITINERARYITEMSTAFFRESOURCE on
                            ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
                        where 
                            ITINERARYITEMSTAFFRESOURCE.ID = @ID

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

                        declare @ASSIGNEDSTAFFTABLE table
                        (
                            ID uniqueidentifier,
                            CONSTITUENTID uniqueidentifier
                        );

                        insert into @ASSIGNEDSTAFFTABLE
                        (
                            ID, 
                            CONSTITUENTID
                        )
                        select 
                            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                            T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID'
                        from @ASSIGNEDSTAFF.nodes('/ASSIGNEDSTAFF/ITEM') T(c);

                        update @ASSIGNEDSTAFFTABLE set 
                            ID = case when ID = '00000000-0000-0000-0000-000000000000' then null else ID end,
                            CONSTITUENTID = case when CONSTITUENTID = '00000000-0000-0000-0000-000000000000' then null else CONSTITUENTID end;

                        /* 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;

                        /* Second delete the itinerary item staff resource assignments */
                        delete from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
                        where 
                            exists 
                            (
                                select 1 from @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE
                                where 
                                    ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.ID and
                                    ASSIGNEDSTAFFTABLE.CONSTITUENTID is null
                            );

                        select @e=@@error;

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

                        if @e <> 0
                            return 2;

                        /* Update any existing records to have the correct people assigned. */
                        update dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT set 
                            CONSTITUENTID = ASSIGNEDSTAFFTABLE.CONSTITUENTID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        from @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE
                        where
                            ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.ID and
                            ASSIGNEDSTAFFTABLE.CONSTITUENTID is not null;

                        /* Finally add any new records for staff, or board. */
                        insert into dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
                        (
                            ID,
                            CONSTITUENTID, 
                            ITINERARYITEMSTAFFRESOURCEID, 
                            ADDEDBYID, 
                            CHANGEDBYID, 
                            DATEADDED, 
                            DATECHANGED
                        )
                        select 
                            coalesce(ASSIGNEDSTAFFTABLE.ID, newid()),
                            ASSIGNEDSTAFFTABLE.CONSTITUENTID,
                            @ID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        from @ASSIGNEDSTAFFTABLE as ASSIGNEDSTAFFTABLE
                        where 
                            ASSIGNEDSTAFFTABLE.CONSTITUENTID is not null and
                            (
                                ASSIGNEDSTAFFTABLE.ID is null or
                                not exists 
                                (
                                    select 1 from dbo.ITINERARYITEMSTAFFRESOURCEASSIGNMENT
                                    where ITINERARYITEMSTAFFRESOURCEASSIGNMENT.ID = ASSIGNEDSTAFFTABLE.ID
                                )
                            );

                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;