USP_PLANNEDGIFTPAYOUT_GETGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2

            CREATE procedure dbo.USP_PLANNEDGIFTPAYOUT_GETGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2
            (
                @REVENUEID uniqueidentifier,
                @XML xml,
                @POSTDATE datetime,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )

            as

            set nocount on;

            declare @PLANNEDGIFTPAYOUTID uniqueidentifier;
            select @PLANNEDGIFTPAYOUTID = ID from PLANNEDGIFTPAYOUT where REVENUEID = @REVENUEID

            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_PLANNEDGIFTPAYOUT_GETGLDISTRIBUTION_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.[PLANNEDGIFTPAYOUTGLDISTRIBUTION] where [PLANNEDGIFTPAYOUTGLDISTRIBUTION].ID in 
                (select ID from dbo.UFN_PLANNEDGIFTPAYOUT_GETGLDISTRIBUTION
                (
                    @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

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


            --Update the corresponding rows in the GLTRANSACTION table


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

            --ensure JournalEntry is updated properly for account and reference

            update JE set
                GLACCOUNTID = GLACCOUNT.ID,
                COMMENT = temp.REFERENCE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CHANGEDATE
            from @TempTbl temp
                inner join dbo.JOURNALENTRY_EXT JEX on JEX.GLTRANSACTIONID = temp.ID
                inner join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
                inner join dbo.GLACCOUNT on GLACCOUNT.ACCOUNTNUMBER = temp.ACCOUNT


            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,
                    REVENUEGLDISTRIBUTIONID uniqueidentifier,
                    ORGANIZATIONAMOUNT money,
                    BASECURRENCYID uniqueidentifier,
                    TRANSACTIONAMOUNT money,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier
            );

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



            insert into dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION
                (ID, PLANNEDGIFTPAYOUTID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
                ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID)
            select
                REVENUEGLDISTRIBUTIONID,
                @PLANNEDGIFTPAYOUTID,
                @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;