USP_VENDOR_1099DISTRIBUTION_UPDATEFROMXML

            CREATE procedure dbo.USP_VENDOR_1099DISTRIBUTION_UPDATEFROMXML(
                @VENDORID uniqueidentifier,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as

                set nocount on;

                if @CHANGEAGENTID is null
                    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

                declare @TempTbl table(
                    [ID] uniqueidentifier,
                    [BOXNUMBER1099ID] uniqueidentifier,
                    [STATEID] uniqueidentifier,
                    [PERCENT] numeric(20,4))

                insert into @TempTbl select
                    [ID],
                    [BOXNUMBER1099ID],
                    [STATEID],
                    [PERCENT]
                from dbo.UFN_VENDOR_1099DISTRIBUTION_FROMITEMLISTXML(@XML)

                update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
        update @TempTbl set STATEID = null where (STATEID = '00000000-0000-0000-0000-000000000000');

                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 from dbo.VENDOR1099DISTRIBUTION where VENDOR1099DISTRIBUTION.ID in
                    (select ID from dbo.VENDOR1099DISTRIBUTION where VENDOR1099DISTRIBUTION.VENDOR1099SETTINGID = @VENDORID
                    EXCEPT select ID from @TempTbl)

                -- reset CONTEXT_INFO to previous value 

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;

                if @@error <> 0
                return 2;

                update dbo.VENDOR1099DISTRIBUTION set
                    VENDOR1099DISTRIBUTION.ID = temp.ID,
                    VENDOR1099DISTRIBUTION.BOXNUMBER1099ID = temp.BOXNUMBER1099ID,
                    VENDOR1099DISTRIBUTION.STATEID = temp.STATEID,
                    VENDOR1099DISTRIBUTION.[PERCENT] = temp.[PERCENT],
                    VENDOR1099DISTRIBUTION.CHANGEDBYID = @CHANGEAGENTID,
                    VENDOR1099DISTRIBUTION.DATECHANGED = @CHANGEDATE
                from dbo.VENDOR1099DISTRIBUTION
                inner join @TempTbl as temp on VENDOR1099DISTRIBUTION.ID = temp.ID
                where (VENDOR1099DISTRIBUTION.ID <> temp.ID) or
                      (VENDOR1099DISTRIBUTION.ID is null and temp.ID is not null) or
                      (VENDOR1099DISTRIBUTION.ID is not null and temp.ID is null) or
                      (VENDOR1099DISTRIBUTION.BOXNUMBER1099ID <> temp.BOXNUMBER1099ID) or
                      (VENDOR1099DISTRIBUTION.BOXNUMBER1099ID is null and temp.BOXNUMBER1099ID is not null) or
                      (VENDOR1099DISTRIBUTION.BOXNUMBER1099ID is not null and temp.BOXNUMBER1099ID is null) or
                      (VENDOR1099DISTRIBUTION.STATEID <> temp.STATEID) or
                      (VENDOR1099DISTRIBUTION.STATEID is null and temp.STATEID is not null) or
                      (VENDOR1099DISTRIBUTION.STATEID is not null and temp.STATEID is null) or
                      (VENDOR1099DISTRIBUTION.[PERCENT] <> temp.[PERCENT]) or
                      (VENDOR1099DISTRIBUTION.[PERCENT] is null and temp.[PERCENT] is not null) or
                      (VENDOR1099DISTRIBUTION.[PERCENT] is not null and temp.[PERCENT] is null)

                if @@Error <> 0
                return 3;

                insert into dbo.VENDOR1099DISTRIBUTION(
                    ID,
                    VENDOR1099SETTINGID,
                    BOXNUMBER1099ID,
                    STATEID,
                    [PERCENT],
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select
                    ID,
                    @VENDORID,
                    BOXNUMBER1099ID,
                    STATEID,
                    [PERCENT],
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE
                from @TempTbl as temp
                where not exists (select ID from dbo.VENDOR1099DISTRIBUTION as data where data.ID = temp.ID)

                if @@Error <> 0
                return 4;

                return 0;