USP_REVENUE_GETGIFTINKINDSALEGLDISTRIBUTION_CUSTOMUPDATEFROMXML

            CREATE procedure dbo.USP_REVENUE_GETGIFTINKINDSALEGLDISTRIBUTION_CUSTOMUPDATEFROMXML
            (
                @GIFTINKINDSALEID uniqueidentifier,
                @XML xml,
                @POSTDATE datetime,
                @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()

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

            declare @TempTbl table (
                [ACCOUNT] nvarchar(100),
                [AMOUNT] money,
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID] uniqueidentifier,
                [ID] uniqueidentifier,
                [PROJECT] nvarchar(100),
                [REFERENCE] nvarchar(100),
                [TRANSACTIONTYPECODE] tinyint,
                [ORGANIZATIONAMOUNT] money,
                [BASECURRENCYID] uniqueidentifier,
                [TRANSACTIONAMOUNT] money,
                [TRANSACTIONCURRENCYID] uniqueidentifier,
                [BASEEXCHANGERATEID] uniqueidentifier,
                [ORGANIZATIONEXCHANGERATEID] uniqueidentifier
            )

            insert into @TempTbl select 
                [ACCOUNT],
                [AMOUNT],
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID],
                [PROJECT],
                [REFERENCE],
                [TRANSACTIONTYPECODE],
                [ORGANIZATIONAMOUNT],
                [BASECURRENCYID],
                [TRANSACTIONAMOUNT],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID],
                [ORGANIZATIONEXCHANGERATEID]
            from dbo.UFN_REVENUE_GETGIFTINKINDSALEGLDISTRIBUTION_FROMITEMLISTXML(@XML)

            declare @REVENUEID uniqueidentifier;

            select top 1 
                @REVENUEID = GIFTINKINDSALEGLDISTRIBUTION.REVENUEID
            from 
                @TempTbl as [temp]
            inner join
                dbo.GIFTINKINDSALEGLDISTRIBUTION on [temp].ID = GIFTINKINDSALEGLDISTRIBUTION.ID
            inner join 
                dbo.GLTRANSACTION on GIFTINKINDSALEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID;

            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.[GIFTINKINDSALEGLDISTRIBUTION] where [GIFTINKINDSALEGLDISTRIBUTION].ID in 
                (select ID from dbo.UFN_REVENUE_GETGIFTINKINDSALEGLDISTRIBUTION
                (
                    @GIFTINKINDSALEID
                )
                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

            merge into  dbo.[GIFTINKINDSALEGLDISTRIBUTION]
            using  @TempTbl as [temp] on [GIFTINKINDSALEGLDISTRIBUTION].ID = [temp].ID
            when matched 
                and (    
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ACCOUNT]<>temp.[ACCOUNT]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ACCOUNT] is null and temp.[ACCOUNT] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ACCOUNT] is not null and temp.[ACCOUNT] is null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[AMOUNT]<>temp.[AMOUNT]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]<>temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ID]<>temp.[ID]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ID] is null and temp.[ID] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ID] is not null and temp.[ID] is null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[PROJECT]<>temp.[PROJECT]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[PROJECT] is null and temp.[PROJECT] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[PROJECT] is not null and temp.[PROJECT] is null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[REFERENCE]<>temp.[REFERENCE]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[REFERENCE] is null and temp.[REFERENCE] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[REFERENCE] is not null and temp.[REFERENCE] is null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[TRANSACTIONTYPECODE]<>temp.[TRANSACTIONTYPECODE]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[TRANSACTIONTYPECODE] is null and temp.[TRANSACTIONTYPECODE] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[TRANSACTIONTYPECODE] is not null and temp.[TRANSACTIONTYPECODE] is null) or
                    ([GIFTINKINDSALEGLDISTRIBUTION].ORGANIZATIONAMOUNT <> temp.ORGANIZATIONAMOUNT) or
                    ([GIFTINKINDSALEGLDISTRIBUTION].TRANSACTIONAMOUNT <> temp.TRANSACTIONAMOUNT) or
                    ([GIFTINKINDSALEGLDISTRIBUTION].TRANSACTIONCURRENCYID <> temp.TRANSACTIONCURRENCYID) or
                    ([GIFTINKINDSALEGLDISTRIBUTION].BASECURRENCYID <> temp.BASECURRENCYID) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[BASEEXCHANGERATEID]<>temp.[BASEEXCHANGERATEID]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[BASEEXCHANGERATEID] is null and temp.[BASEEXCHANGERATEID] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[BASEEXCHANGERATEID] is not null and temp.[BASEEXCHANGERATEID] is null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID]<>temp.[ORGANIZATIONEXCHANGERATEID]) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID] is null and temp.[ORGANIZATIONEXCHANGERATEID] is not null) or 
                    ([GIFTINKINDSALEGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID] is not null and temp.[ORGANIZATIONEXCHANGERATEID] is null)                    
                    )
            then update 
                    set                     
                        [GIFTINKINDSALEGLDISTRIBUTION].[ACCOUNT]=temp.[ACCOUNT],
                        [GIFTINKINDSALEGLDISTRIBUTION].[AMOUNT]=temp.[AMOUNT],
                        [GIFTINKINDSALEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]=temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                        [GIFTINKINDSALEGLDISTRIBUTION].[ID]=temp.[ID],
                        [GIFTINKINDSALEGLDISTRIBUTION].[PROJECT]=temp.[PROJECT],
                        [GIFTINKINDSALEGLDISTRIBUTION].[REFERENCE]=temp.[REFERENCE],
                        [GIFTINKINDSALEGLDISTRIBUTION].[TRANSACTIONTYPECODE]=temp.[TRANSACTIONTYPECODE],
                        [GIFTINKINDSALEGLDISTRIBUTION].CHANGEDBYID = @CHANGEAGENTID,
                        [GIFTINKINDSALEGLDISTRIBUTION].DATECHANGED = @CHANGEDATE,
                        [GIFTINKINDSALEGLDISTRIBUTION].ORGANIZATIONAMOUNT = temp.ORGANIZATIONAMOUNT,
                        [GIFTINKINDSALEGLDISTRIBUTION].BASECURRENCYID = temp.BASECURRENCYID,
                        [GIFTINKINDSALEGLDISTRIBUTION].TRANSACTIONAMOUNT = temp.TRANSACTIONAMOUNT,
                        [GIFTINKINDSALEGLDISTRIBUTION].TRANSACTIONCURRENCYID = temp.TRANSACTIONCURRENCYID,
                        [GIFTINKINDSALEGLDISTRIBUTION].BASEEXCHANGERATEID = temp.BASEEXCHANGERATEID,
                        [GIFTINKINDSALEGLDISTRIBUTION].ORGANIZATIONEXCHANGERATEID = temp.ORGANIZATIONEXCHANGERATEID;

            --Update the corresponding rows in the GLTRANSACTION table

            merge into  dbo.[GLTRANSACTION]
            using  @TempTbl as [temp] 
                inner join dbo.GIFTINKINDSALEGLDISTRIBUTION on GIFTINKINDSALEGLDISTRIBUTION.ID = [temp].ID
                on [GLTRANSACTION].ID = [temp].ID
            when matched 
            then update
                set             
                    GLTRANSACTION.ACCOUNT = GIFTINKINDSALEGLDISTRIBUTION.ACCOUNT,
                    GLTRANSACTION.AMOUNT = GIFTINKINDSALEGLDISTRIBUTION.AMOUNT,
                    GLTRANSACTION.PROJECT = GIFTINKINDSALEGLDISTRIBUTION.PROJECT,
                    GLTRANSACTION.REFERENCE = GIFTINKINDSALEGLDISTRIBUTION.REFERENCE,
                    GLTRANSACTION.TRANSACTIONTYPECODE = GIFTINKINDSALEGLDISTRIBUTION.TRANSACTIONTYPECODE,
                    GLTRANSACTION.POSTDATE = @POSTDATE,
                    GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID,
                    GLTRANSACTION.DATECHANGED = @CHANGEDATE,
                    [GLTRANSACTION].ORGANIZATIONAMOUNT = GIFTINKINDSALEGLDISTRIBUTION.ORGANIZATIONAMOUNT,
                    [GLTRANSACTION].BASECURRENCYID = GIFTINKINDSALEGLDISTRIBUTION.BASECURRENCYID,
                    [GLTRANSACTION].TRANSACTIONAMOUNT = GIFTINKINDSALEGLDISTRIBUTION.TRANSACTIONAMOUNT,
                    [GLTRANSACTION].TRANSACTIONCURRENCYID = GIFTINKINDSALEGLDISTRIBUTION.TRANSACTIONCURRENCYID,
                    [GLTRANSACTION].BASEEXCHANGERATEID = GIFTINKINDSALEGLDISTRIBUTION.BASEEXCHANGERATEID,
                    [GLTRANSACTION].ORGANIZATIONEXCHANGERATEID = GIFTINKINDSALEGLDISTRIBUTION.ORGANIZATIONEXCHANGERATEID;

            if @@Error <> 0
                return 3;    

            -- insert new items

            declare @DISTRIBUTIONS table(
                GLTRANSACTIONID uniqueidentifier,
                ACCOUNT nvarchar(100),
                AMOUNT money,
                PROJECT nvarchar(100),
                REFERENCE nvarchar(255),
                TRANSACTIONTYPECODE tinyint,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                GIFTINKINDSALEGLDISTRIBUTIONID uniqueidentifier,
                [ORGANIZATIONAMOUNT] money,
                [BASECURRENCYID] uniqueidentifier,
                [TRANSACTIONAMOUNT] money,
                [TRANSACTIONCURRENCYID] uniqueidentifier,
                [BASEEXCHANGERATEID] uniqueidentifier,
                [ORGANIZATIONEXCHANGERATEID] uniqueidentifier
            );

            insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, GIFTINKINDSALEGLDISTRIBUTIONID, [ORGANIZATIONAMOUNT], [BASECURRENCYID], [TRANSACTIONAMOUNT], [TRANSACTIONCURRENCYID], [BASEEXCHANGERATEID], [ORGANIZATIONEXCHANGERATEID])
            select 
                newid(), 
                [ACCOUNT],
                [AMOUNT],
                [PROJECT],
                [REFERENCE],
                [TRANSACTIONTYPECODE], 
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID],
                [ORGANIZATIONAMOUNT],
                [BASECURRENCYID],
                [TRANSACTIONAMOUNT],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID],
                [ORGANIZATIONEXCHANGERATEID]
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[GIFTINKINDSALEGLDISTRIBUTION] as data where data.ID = [temp].ID)

            -- Insert rows in GLTRANSACTION table

            insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, [ORGANIZATIONAMOUNT], [BASECURRENCYID], [TRANSACTIONAMOUNT], [TRANSACTIONCURRENCYID], [BASEEXCHANGERATEID], [ORGANIZATIONEXCHANGERATEID])
            select
                GLTRANSACTIONID,
                TRANSACTIONTYPECODE,
                ACCOUNT,
                AMOUNT,
                PROJECT,
                REFERENCE,
                @POSTDATE,
                'Blackbaud Enterprise',
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE,
                [ORGANIZATIONAMOUNT],
                [BASECURRENCYID],
                [TRANSACTIONAMOUNT],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID],
                [ORGANIZATIONEXCHANGERATEID]
            from 
                @DISTRIBUTIONS;

            -- Insert rows in GIFTINKINDSALEGLDISTRIBUTION table

            insert into dbo.GIFTINKINDSALEGLDISTRIBUTION
                (ID, GIFTINKINDSALEID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, [ORGANIZATIONAMOUNT], [BASECURRENCYID], [TRANSACTIONAMOUNT], [TRANSACTIONCURRENCYID], [BASEEXCHANGERATEID], [ORGANIZATIONEXCHANGERATEID])
            select
                GIFTINKINDSALEGLDISTRIBUTIONID,
                @GIFTINKINDSALEID,
                @REVENUEID,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                PROJECT,
                REFERENCE, 
                AMOUNT,
                ACCOUNT,
                TRANSACTIONTYPECODE,
                GLTRANSACTIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE,
                [ORGANIZATIONAMOUNT],
                [BASECURRENCYID],
                [TRANSACTIONAMOUNT],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID],
                [ORGANIZATIONEXCHANGERATEID]
            from 
                @DISTRIBUTIONS;

            if @@Error <> 0
                return 4;

            return 0;