USP_REVENUESPLIT_GETBUSINESSSUNIT_UPDATEFROMXML

            CREATE procedure dbo.USP_REVENUESPLIT_GETBUSINESSSUNIT_UPDATEFROMXML
            (
                @REVENUESPLITID 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 @BASECURRENCYID uniqueidentifier;
        declare @ORGANIZATIONAMOUNT money;
        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

        select
          @BASECURRENCYID = BASECURRENCYID,
          @ORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT,
          @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
        from dbo.REVENUESPLIT where ID = @REVENUESPLITID 

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

                declare @TempTbl table (
                   [AMOUNT] money,
                   [BUSINESSUNITCODEID] uniqueidentifier,
                   [ID] uniqueidentifier,
                   [BASECURRENCYID] uniqueidentifier,
                   [ORGANIZATIONAMOUNT] money,
                   [ORGANIZATIONEXCHANGERATEID] uniqueidentifier)

                insert into @TempTbl([AMOUNT], [BUSINESSUNITCODEID], [ID],[BASECURRENCYID], [ORGANIZATIONAMOUNT], [ORGANIZATIONEXCHANGERATEID]) select 
                    [AMOUNT],
                    [BUSINESSUNITCODEID],
                    [ID],
                    @BASECURRENCYID,
                    @ORGANIZATIONAMOUNT,
                    @ORGANIZATIONEXCHANGERATEID
                from dbo.UFN_REVENUESPLITBUSINESSUNIT_GETBUSINESSUNITS_FROMITEMLISTXML(@XML)

                update @TempTbl set ID = newid() where (ID is null) or (ID = '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 any items that no longer exist in the XML table

                delete from dbo.[REVENUESPLITBUSINESSUNIT] where [REVENUESPLITBUSINESSUNIT].ID in 
                    (select ID from dbo.UFN_REVENUESPLITBUSINESSUNIT_GETBUSINESSUNITS
                    (
                        @REVENUESPLITID
                    )
                    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 [REVENUESPLITBUSINESSUNIT]
                    set [REVENUESPLITBUSINESSUNIT].[AMOUNT] = temp.[AMOUNT],
                        [REVENUESPLITBUSINESSUNIT].[BUSINESSUNITCODEID] = temp.[BUSINESSUNITCODEID],
                        [REVENUESPLITBUSINESSUNIT].[BASECURRENCYID] = temp.[BASECURRENCYID],
                        [REVENUESPLITBUSINESSUNIT].[ORGANIZATIONAMOUNT] = temp.[ORGANIZATIONAMOUNT],
                        [REVENUESPLITBUSINESSUNIT].[ORGANIZATIONEXCHANGERATEID] = temp.[ORGANIZATIONEXCHANGERATEID],
                        [REVENUESPLITBUSINESSUNIT].CHANGEDBYID = @CHANGEAGENTID,
                        [REVENUESPLITBUSINESSUNIT].DATECHANGED = @CHANGEDATE
                    from dbo.[REVENUESPLITBUSINESSUNIT] inner join @TempTbl as [temp] on [REVENUESPLITBUSINESSUNIT].ID = [temp].ID
                    where 
                        ([REVENUESPLITBUSINESSUNIT].[AMOUNT] <> temp.[AMOUNT]) or 
                        ([REVENUESPLITBUSINESSUNIT].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
                        ([REVENUESPLITBUSINESSUNIT].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
                        ([REVENUESPLITBUSINESSUNIT].[BUSINESSUNITCODEID] <> temp.[BUSINESSUNITCODEID]) or 
                        ([REVENUESPLITBUSINESSUNIT].[BUSINESSUNITCODEID] is null and temp.[BUSINESSUNITCODEID] is not null) or 
                        ([REVENUESPLITBUSINESSUNIT].[BUSINESSUNITCODEID] is not null and temp.[BUSINESSUNITCODEID] is null) or
                        ([REVENUESPLITBUSINESSUNIT].[BASECURRENCYID] <> temp.[BASECURRENCYID]) or 
                        ([REVENUESPLITBUSINESSUNIT].[BASECURRENCYID] is null and temp.[BASECURRENCYID] is not null) or 
                        ([REVENUESPLITBUSINESSUNIT].[BASECURRENCYID] is not null and temp.[BASECURRENCYID] is null) or
                        ([REVENUESPLITBUSINESSUNIT].[ORGANIZATIONAMOUNT] <> temp.[ORGANIZATIONAMOUNT]) or 
                        ([REVENUESPLITBUSINESSUNIT].[ORGANIZATIONAMOUNT] is null and temp.[ORGANIZATIONAMOUNT] is not null) or 
                        ([REVENUESPLITBUSINESSUNIT].[ORGANIZATIONAMOUNT] is not null and temp.[ORGANIZATIONAMOUNT] is null) or
                        ([REVENUESPLITBUSINESSUNIT].[ORGANIZATIONEXCHANGERATEID] <> temp.[ORGANIZATIONEXCHANGERATEID]) or 
                        ([REVENUESPLITBUSINESSUNIT].[ORGANIZATIONEXCHANGERATEID] is null and temp.[ORGANIZATIONEXCHANGERATEID] is not null) or 
                        ([REVENUESPLITBUSINESSUNIT].[ORGANIZATIONEXCHANGERATEID] is not null and temp.[ORGANIZATIONEXCHANGERATEID] is null)

                if @@Error <> 0
                    return 3;    

                -- insert new items

                insert into [REVENUESPLITBUSINESSUNIT] 
                    ([REVENUESPLITID], 
                    [AMOUNT],
                    [ID],
                    [BUSINESSUNITCODEID],
                    [BASECURRENCYID],
                    [ORGANIZATIONAMOUNT],
                    [ORGANIZATIONEXCHANGERATEID],                
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select @REVENUESPLITID, 
                    [AMOUNT],
                    [ID],
                    [BUSINESSUNITCODEID],
                    [BASECURRENCYID],
                    isnull([ORGANIZATIONAMOUNT], [AMOUNT]),
                    [ORGANIZATIONEXCHANGERATEID],
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from @TempTbl as [temp]
                where not exists (select ID from dbo.[REVENUESPLITBUSINESSUNIT] as data where data.ID = [temp].ID)

                if @@Error <> 0
                    return 4;

                return 0;