USP_INSTALLMENT_GETINSTALLMENTS_2_UPDATEFROMXML

            CREATE procedure dbo.USP_INSTALLMENT_GETINSTALLMENTS_2_UPDATEFROMXML
            (
                @REVENUEID uniqueidentifier,
                @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 (
                    [AMOUNT] money,
                    [BASECURRENCYID] uniqueidentifier,
                    [BASEEXCHANGERATEID] uniqueidentifier,
                    [DATE] datetime,
                    [ID] uniqueidentifier,
                    [ORGANIZATIONAMOUNT] money,
                    [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
                    [ORGANIZATIONRECEIPTAMOUNT] money,
                    [RECEIPTAMOUNT] money,
                    [SEQUENCE] int,
                    [TRANSACTIONAMOUNT] money,
                    [TRANSACTIONCURRENCYID] uniqueidentifier,
                    [TRANSACTIONRECEIPTAMOUNT] money)

                insert into @TempTbl select
                    [AMOUNT],
                    [BASECURRENCYID],
                    [BASEEXCHANGERATEID],
                    [DATE],
                    [ID],
                    [ORGANIZATIONAMOUNT],
                    [ORGANIZATIONEXCHANGERATEID],
                    [ORGANIZATIONRECEIPTAMOUNT],
                    [RECEIPTAMOUNT],
                    [SEQUENCE],
                    [TRANSACTIONAMOUNT],
                    [TRANSACTIONCURRENCYID],
                    [TRANSACTIONRECEIPTAMOUNT] 
                from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_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.[INSTALLMENT] where [INSTALLMENT].ID in 
                    (select ID from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2
                    (
                        @REVENUEID
                    )
                    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 [INSTALLMENT]
                        set [INSTALLMENT].[AMOUNT]=temp.[AMOUNT],
                        [INSTALLMENT].[BASECURRENCYID]=temp.[BASECURRENCYID],
                        [INSTALLMENT].[BASEEXCHANGERATEID]=temp.[BASEEXCHANGERATEID],
                        [INSTALLMENT].[DATE]=temp.[DATE],
                        [INSTALLMENT].[ID]=temp.[ID],
                        [INSTALLMENT].[ORGANIZATIONAMOUNT]=temp.[ORGANIZATIONAMOUNT],
                        [INSTALLMENT].[ORGANIZATIONEXCHANGERATEID]=temp.[ORGANIZATIONEXCHANGERATEID],
                        [INSTALLMENT].[ORGANIZATIONRECEIPTAMOUNT]=temp.[ORGANIZATIONRECEIPTAMOUNT],
                        [INSTALLMENT].[RECEIPTAMOUNT]=temp.[RECEIPTAMOUNT],
                        [INSTALLMENT].[SEQUENCE]=temp.[SEQUENCE],
                        [INSTALLMENT].[TRANSACTIONAMOUNT]=temp.[TRANSACTIONAMOUNT],
                        [INSTALLMENT].[TRANSACTIONCURRENCYID]=temp.[TRANSACTIONCURRENCYID],
                        [INSTALLMENT].[TRANSACTIONRECEIPTAMOUNT]=temp.[TRANSACTIONRECEIPTAMOUNT],
                        [INSTALLMENT].CHANGEDBYID = @CHANGEAGENTID,
                        [INSTALLMENT].DATECHANGED = @CHANGEDATE

                    from dbo.[INSTALLMENT] inner join @TempTbl as [temp] on [INSTALLMENT].ID = [temp].ID
                    where ([INSTALLMENT].[AMOUNT]<>temp.[AMOUNT]) or 
                        ([INSTALLMENT].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
                        ([INSTALLMENT].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
                        ([INSTALLMENT].[BASECURRENCYID]<>temp.[BASECURRENCYID]) or 
                        ([INSTALLMENT].[BASECURRENCYID] is null and temp.[BASECURRENCYID] is not null) or 
                        ([INSTALLMENT].[BASECURRENCYID] is not null and temp.[BASECURRENCYID] is null) or 
                        ([INSTALLMENT].[BASEEXCHANGERATEID]<>temp.[BASEEXCHANGERATEID]) or 
                        ([INSTALLMENT].[BASEEXCHANGERATEID] is null and temp.[BASEEXCHANGERATEID] is not null) or 
                        ([INSTALLMENT].[BASEEXCHANGERATEID] is not null and temp.[BASEEXCHANGERATEID] is null) or 
                        ([INSTALLMENT].[DATE]<>temp.[DATE]) or 
                        ([INSTALLMENT].[DATE] is null and temp.[DATE] is not null) or 
                        ([INSTALLMENT].[DATE] is not null and temp.[DATE] is null) or 
                        ([INSTALLMENT].[ID]<>temp.[ID]) or 
                        ([INSTALLMENT].[ID] is null and temp.[ID] is not null) or 
                        ([INSTALLMENT].[ID] is not null and temp.[ID] is null) or 
                        ([INSTALLMENT].[ORGANIZATIONAMOUNT]<>temp.[ORGANIZATIONAMOUNT]) or 
                        ([INSTALLMENT].[ORGANIZATIONAMOUNT] is null and temp.[ORGANIZATIONAMOUNT] is not null) or 
                        ([INSTALLMENT].[ORGANIZATIONAMOUNT] is not null and temp.[ORGANIZATIONAMOUNT] is null) or 
                        ([INSTALLMENT].[ORGANIZATIONEXCHANGERATEID]<>temp.[ORGANIZATIONEXCHANGERATEID]) or 
                        ([INSTALLMENT].[ORGANIZATIONEXCHANGERATEID] is null and temp.[ORGANIZATIONEXCHANGERATEID] is not null) or 
                        ([INSTALLMENT].[ORGANIZATIONEXCHANGERATEID] is not null and temp.[ORGANIZATIONEXCHANGERATEID] is null) or 
                        ([INSTALLMENT].[ORGANIZATIONRECEIPTAMOUNT]<>temp.[ORGANIZATIONRECEIPTAMOUNT]) or 
                        ([INSTALLMENT].[ORGANIZATIONRECEIPTAMOUNT] is null and temp.[ORGANIZATIONRECEIPTAMOUNT] is not null) or 
                        ([INSTALLMENT].[ORGANIZATIONRECEIPTAMOUNT] is not null and temp.[ORGANIZATIONRECEIPTAMOUNT] is null) or 
                        ([INSTALLMENT].[RECEIPTAMOUNT]<>temp.[RECEIPTAMOUNT]) or 
                        ([INSTALLMENT].[RECEIPTAMOUNT] is null and temp.[RECEIPTAMOUNT] is not null) or 
                        ([INSTALLMENT].[RECEIPTAMOUNT] is not null and temp.[RECEIPTAMOUNT] is null) or 
                        ([INSTALLMENT].[SEQUENCE]<>temp.[SEQUENCE]) or 
                        ([INSTALLMENT].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or 
                        ([INSTALLMENT].[SEQUENCE] is not null and temp.[SEQUENCE] is null) or 
                        ([INSTALLMENT].[TRANSACTIONAMOUNT]<>temp.[TRANSACTIONAMOUNT]) or 
                        ([INSTALLMENT].[TRANSACTIONAMOUNT] is null and temp.[TRANSACTIONAMOUNT] is not null) or 
                        ([INSTALLMENT].[TRANSACTIONAMOUNT] is not null and temp.[TRANSACTIONAMOUNT] is null) or 
                        ([INSTALLMENT].[TRANSACTIONCURRENCYID]<>temp.[TRANSACTIONCURRENCYID]) or 
                        ([INSTALLMENT].[TRANSACTIONCURRENCYID] is null and temp.[TRANSACTIONCURRENCYID] is not null) or 
                        ([INSTALLMENT].[TRANSACTIONCURRENCYID] is not null and temp.[TRANSACTIONCURRENCYID] is null) or 
                        ([INSTALLMENT].[TRANSACTIONRECEIPTAMOUNT]<>temp.[TRANSACTIONRECEIPTAMOUNT]) or 
                        ([INSTALLMENT].[TRANSACTIONRECEIPTAMOUNT] is null and temp.[TRANSACTIONRECEIPTAMOUNT] is not null) or 
                        ([INSTALLMENT].[TRANSACTIONRECEIPTAMOUNT] is not null and temp.[TRANSACTIONRECEIPTAMOUNT] is null)

                if @@Error <> 0
                    return 3;

                -- insert new items

                insert into [INSTALLMENT] 
                    ([REVENUEID], 
                    [AMOUNT],
                    [BASECURRENCYID],
                    [BASEEXCHANGERATEID],
                    [DATE],
                    [ID],
                    [ORGANIZATIONAMOUNT],
                    [ORGANIZATIONEXCHANGERATEID],
                    [ORGANIZATIONRECEIPTAMOUNT],
                    [RECEIPTAMOUNT],
                    [SEQUENCE],
                    [TRANSACTIONAMOUNT],
                    [TRANSACTIONCURRENCYID],
                    [TRANSACTIONRECEIPTAMOUNT],
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select @REVENUEID, 
                    [AMOUNT],
                    [BASECURRENCYID],
                    [BASEEXCHANGERATEID],
                    [DATE],
                    [ID],
                    [ORGANIZATIONAMOUNT],
                    [ORGANIZATIONEXCHANGERATEID],
                    [ORGANIZATIONRECEIPTAMOUNT],
                    [RECEIPTAMOUNT],
                    [SEQUENCE],
                    [TRANSACTIONAMOUNT],
                    [TRANSACTIONCURRENCYID],
                    [TRANSACTIONRECEIPTAMOUNT], 
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from @TempTbl as [temp]
                where not exists (select ID from dbo.[INSTALLMENT] as data where data.ID = [temp].ID)

                if @@Error <> 0
                    return 4;

                return 0;
            end