USP_RE7INTEGRATION_GETSELECTEDFIELDS_UPDATEFROMXML

            create procedure dbo.USP_RE7INTEGRATION_GETSELECTEDFIELDS_UPDATEFROMXML    (
                @DIRECTIONCODE tinyint,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            ) as begin
                set nocount on;

                if @CHANGEAGENTID is null
                    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

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

                declare @TEMPTBL table ([ID] uniqueidentifier);

                insert into @TEMPTBL
                select 
                    [ID] 
                from
                    dbo.UFN_RE7INTEGRATION_GETSELECTEDTORE7FIELDS_FROMITEMLISTXML(@XML);

                if @@ERROR <> 0
                    return 1;

                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.RE7INTEGRATIONSELECTEDFIELD where RE7INTEGRATIONSELECTEDFIELD.ID in 
                    (select ID from dbo.UFN_RE7INTEGRATION_GETSELECTEDTORE7FIELDS(@DIRECTIONCODE)
                        except select ID from @TEMPTBL);

                select @E = @@ERROR;

                -- reset CONTEXT_INFO to previous value 

                if not @CONTEXTCACHE is null
                    set CONTEXT_INFO @CONTEXTCACHE;

                if @e <> 0
                    return 2;

                -- update the items that exist in the XML table and the db

                update dbo.RE7INTEGRATIONSELECTEDFIELD
                    set
                        RE7INTEGRATIONSELECTEDFIELD.ID = TEMP.ID,
                        RE7INTEGRATIONSELECTEDFIELD.CHANGEDBYID = @CHANGEAGENTID,
                        RE7INTEGRATIONSELECTEDFIELD.DATECHANGED = @CHANGEDATE
                    from
                        dbo.RE7INTEGRATIONSELECTEDFIELD
                    inner join
                        @TEMPTBL as TEMP
                    on
                        RE7INTEGRATIONSELECTEDFIELD.ID = TEMP.ID
                    where
                        (RE7INTEGRATIONSELECTEDFIELD.ID <> TEMP.ID) or 
                        (RE7INTEGRATIONSELECTEDFIELD.ID is null and TEMP.ID is not null) or 
                        (RE7INTEGRATIONSELECTEDFIELD.ID is not null and TEMP.ID is null)

                if @@Error <> 0
                    return 3;

                -- insert new items

                insert into dbo.RE7INTEGRATIONSELECTEDFIELD
                (
                    ID,
                    ADDEDBYID,
                    CHANGEDBYID, 
                    DATEADDED,
                    DATECHANGED
                )
                select
                    TEMP.ID,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from
                    @TEMPTBL as TEMP
                where
                    not exists (select ID from dbo.RE7INTEGRATIONSELECTEDFIELD as DATA where DATA.ID = TEMP.ID)

                if @@Error <> 0
                    return 4;

                return 0;
            end