USP_REVENUE_GETAUCTIONPURCHASEGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2

            CREATE procedure dbo.USP_REVENUE_GETAUCTIONPURCHASEGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2
            (
                @REVENUEID 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_GETAUCTIONPURCHASEGLDISTRIBUTION_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;

                declare @GLTRANSACTIONSTODELETE table (ID uniqueidentifier)

                -- delete any items that no longer exist in the XML table

                delete from dbo.[AUCTIONPURCHASEGLDISTRIBUTION] 
                output DELETED.GLTRANSACTIONID into @GLTRANSACTIONSTODELETE
                where [AUCTIONPURCHASEGLDISTRIBUTION].ID in 
                    (select ID from dbo.UFN_REVENUE_GETAUCTIONPURCHASEGLDISTRIBUTION
                    (
                        @REVENUEID
                    )
                    EXCEPT select ID from @TempTbl)    

                select @e=@@error;

                delete dbo.GLTRANSACTION
                where ID in (select ID from @GLTRANSACTIONSTODELETE)

                -- 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.[AUCTIONPURCHASEGLDISTRIBUTION]
                using  @TempTbl as [temp] on [AUCTIONPURCHASEGLDISTRIBUTION].ID = [temp].ID
                when matched 
                    and (
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ACCOUNT]<>temp.[ACCOUNT]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ACCOUNT] is null and temp.[ACCOUNT] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ACCOUNT] is not null and temp.[ACCOUNT] is null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[AMOUNT]<>temp.[AMOUNT]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]<>temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ID]<>temp.[ID]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ID] is null and temp.[ID] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ID] is not null and temp.[ID] is null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[PROJECT]<>temp.[PROJECT]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[PROJECT] is null and temp.[PROJECT] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[PROJECT] is not null and temp.[PROJECT] is null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[REFERENCE]<>temp.[REFERENCE]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[REFERENCE] is null and temp.[REFERENCE] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[REFERENCE] is not null and temp.[REFERENCE] is null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[TRANSACTIONTYPECODE]<>temp.[TRANSACTIONTYPECODE]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[TRANSACTIONTYPECODE] is null and temp.[TRANSACTIONTYPECODE] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[TRANSACTIONTYPECODE] is not null and temp.[TRANSACTIONTYPECODE] is null) or
                        ([AUCTIONPURCHASEGLDISTRIBUTION].ORGANIZATIONAMOUNT <> temp.ORGANIZATIONAMOUNT) or
                        ([AUCTIONPURCHASEGLDISTRIBUTION].TRANSACTIONAMOUNT <> temp.TRANSACTIONAMOUNT) or
                        ([AUCTIONPURCHASEGLDISTRIBUTION].TRANSACTIONCURRENCYID <> temp.TRANSACTIONCURRENCYID) or
                        ([AUCTIONPURCHASEGLDISTRIBUTION].BASECURRENCYID <> temp.BASECURRENCYID) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[BASEEXCHANGERATEID]<>temp.[BASEEXCHANGERATEID]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[BASEEXCHANGERATEID] is null and temp.[BASEEXCHANGERATEID] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[BASEEXCHANGERATEID] is not null and temp.[BASEEXCHANGERATEID] is null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID]<>temp.[ORGANIZATIONEXCHANGERATEID]) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID] is null and temp.[ORGANIZATIONEXCHANGERATEID] is not null) or 
                        ([AUCTIONPURCHASEGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID] is not null and temp.[ORGANIZATIONEXCHANGERATEID] is null)                    
                        )
                then update 
                    set    
                        [AUCTIONPURCHASEGLDISTRIBUTION].[ACCOUNT]=temp.[ACCOUNT],
                        [AUCTIONPURCHASEGLDISTRIBUTION].[AMOUNT]=temp.[AMOUNT],
                        [AUCTIONPURCHASEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]=temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                        [AUCTIONPURCHASEGLDISTRIBUTION].[ID]=temp.[ID],
                        [AUCTIONPURCHASEGLDISTRIBUTION].[PROJECT]=temp.[PROJECT],
                        [AUCTIONPURCHASEGLDISTRIBUTION].[REFERENCE]=temp.[REFERENCE],
                        [AUCTIONPURCHASEGLDISTRIBUTION].[TRANSACTIONTYPECODE]=temp.[TRANSACTIONTYPECODE],
                        [AUCTIONPURCHASEGLDISTRIBUTION].CHANGEDBYID = @CHANGEAGENTID,
                        [AUCTIONPURCHASEGLDISTRIBUTION].DATECHANGED = @CHANGEDATE,
                        [AUCTIONPURCHASEGLDISTRIBUTION].ORGANIZATIONAMOUNT = temp.ORGANIZATIONAMOUNT,
                        [AUCTIONPURCHASEGLDISTRIBUTION].BASECURRENCYID = temp.BASECURRENCYID,
                        [AUCTIONPURCHASEGLDISTRIBUTION].TRANSACTIONAMOUNT = temp.TRANSACTIONAMOUNT,
                        [AUCTIONPURCHASEGLDISTRIBUTION].TRANSACTIONCURRENCYID = temp.TRANSACTIONCURRENCYID,
                        [AUCTIONPURCHASEGLDISTRIBUTION].BASEEXCHANGERATEID = temp.BASEEXCHANGERATEID,
                        [AUCTIONPURCHASEGLDISTRIBUTION].ORGANIZATIONEXCHANGERATEID = temp.ORGANIZATIONEXCHANGERATEID;




            --Update the corresponding rows in the GLTRANSACTION table


            merge into  dbo.[GLTRANSACTION]
            using  @TempTbl as [temp] 
                inner join AUCTIONPURCHASEGLDISTRIBUTION on AUCTIONPURCHASEGLDISTRIBUTION.ID = [temp].ID
                on [GLTRANSACTION].ID = [temp].ID
            when matched 
            then update
                set 
                    GLTRANSACTION.ACCOUNT = AUCTIONPURCHASEGLDISTRIBUTION.ACCOUNT,
                    GLTRANSACTION.AMOUNT = AUCTIONPURCHASEGLDISTRIBUTION.AMOUNT,
                    GLTRANSACTION.PROJECT = AUCTIONPURCHASEGLDISTRIBUTION.PROJECT,
                    GLTRANSACTION.REFERENCE = AUCTIONPURCHASEGLDISTRIBUTION.REFERENCE,
                    GLTRANSACTION.TRANSACTIONTYPECODE = AUCTIONPURCHASEGLDISTRIBUTION.TRANSACTIONTYPECODE,
                    GLTRANSACTION.POSTDATE = @POSTDATE,
                    GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID,
                    GLTRANSACTION.DATECHANGED = @CHANGEDATE,
                    GLTRANSACTION.GLACCOUNTID = dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE(AUCTIONPURCHASEGLDISTRIBUTION.ACCOUNT,@REVENUEID),
                    GLTRANSACTION.ORGANIZATIONAMOUNT = AUCTIONPURCHASEGLDISTRIBUTION.ORGANIZATIONAMOUNT,
                    GLTRANSACTION.BASECURRENCYID = AUCTIONPURCHASEGLDISTRIBUTION.BASECURRENCYID,
                    GLTRANSACTION.TRANSACTIONAMOUNT = AUCTIONPURCHASEGLDISTRIBUTION.TRANSACTIONAMOUNT,
                    GLTRANSACTION.TRANSACTIONCURRENCYID = AUCTIONPURCHASEGLDISTRIBUTION.TRANSACTIONCURRENCYID,
                    GLTRANSACTION.BASEEXCHANGERATEID = AUCTIONPURCHASEGLDISTRIBUTION.BASEEXCHANGERATEID,
                    GLTRANSACTION.ORGANIZATIONEXCHANGERATEID = AUCTIONPURCHASEGLDISTRIBUTION.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,
                        AUCTIONPURCHASEGLDISTRIBUTIONID uniqueidentifier,
                        ORGANIZATIONAMOUNT money,
                        BASECURRENCYID uniqueidentifier,
                        TRANSACTIONAMOUNT money,
                        TRANSACTIONCURRENCYID uniqueidentifier,
                        BASEEXCHANGERATEID uniqueidentifier,
                        ORGANIZATIONEXCHANGERATEID uniqueidentifier
                );

                insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, AUCTIONPURCHASEGLDISTRIBUTIONID, 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.[AUCTIONPURCHASEGLDISTRIBUTION] 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, GLACCOUNTID, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID)
                select
                    GLTRANSACTIONID,
                    TRANSACTIONTYPECODE,
                    ACCOUNT,
                    AMOUNT,
                    PROJECT,
                    REFERENCE,
                    @POSTDATE,
                    'Blackbaud Enterprise',
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE,
                    dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE([ACCOUNT],@REVENUEID),
                    ORGANIZATIONAMOUNT,
                    BASECURRENCYID,
                    TRANSACTIONAMOUNT,
                    TRANSACTIONCURRENCYID,
                    BASEEXCHANGERATEID,
                    ORGANIZATIONEXCHANGERATEID
                from 
                    @DISTRIBUTIONS;

                -- Insert rows in AUCTIONPURCHASEGLDISTRIBUTION table

                insert into dbo.AUCTIONPURCHASEGLDISTRIBUTION
                    (ID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, REVENUEPURCHASEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID)
                select
                    AUCTIONPURCHASEGLDISTRIBUTIONID,
                    null,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    PROJECT,
                    REFERENCE, 
                    AMOUNT,
                    ACCOUNT,
                    TRANSACTIONTYPECODE,
                    GLTRANSACTIONID,
                    @REVENUEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE,
                    ORGANIZATIONAMOUNT,
                    BASECURRENCYID,
                    TRANSACTIONAMOUNT,
                    TRANSACTIONCURRENCYID,
                    BASEEXCHANGERATEID,
                    ORGANIZATIONEXCHANGERATEID
                from 
                    @DISTRIBUTIONS;

                if @@Error <> 0
                    return 4;

                return 0;