USP_REVENUE_GETGLDISTRIBUTION_CUSTOMUPDATEFROMXML

            CREATE procedure dbo.USP_REVENUE_GETGLDISTRIBUTION_CUSTOMUPDATEFROMXML
            (
                @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(255),
               [TRANSACTIONTYPECODE] tinyint)

            insert into @TempTbl select 
                [ACCOUNT],
                [AMOUNT],
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID],
                [PROJECT],
                [REFERENCE],
                [TRANSACTIONTYPECODE] 
            from dbo.UFN_REVENUE_GETGLDISTRIBUTION_FROMITEMLISTXML(@XML)

      /* For payments that are linked to deposits there are limitation for what you can change
         about the debit account for the gl distribution.
         - If the deposit is linked to a bank account that has a cash account then you can
           not change anything about the debit account for the gl distribution.
         - If the deposit is linked to a bank account that has a cash code then you can
           change anything about the debit account except for the account code for the gl distribution.
      */

            if exists(select * from dbo.BANKACCOUNTDEPOSITPAYMENT where ID = @REVENUEID and DEPOSITID is not null)
            begin
                declare @ACCOUNT nvarchar(100);
        declare @ACCOUNTCODE nvarchar(30);
          select @ACCOUNT = CASE WHEN BANKACCOUNT.GLACCOUNTID IS NOT NULL THEN GLACCOUNT.ACCOUNTNUMBER END,
          @ACCOUNTCODE = PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION
          from dbo.BANKACCOUNTDEPOSITPAYMENT
          inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
          inner join dbo.BANKACCOUNT on BANKACCOUNT.ID = BANKACCOUNTTRANSACTION.BANKACCOUNTID
          left outer join dbo.GLACCOUNT on BANKACCOUNT.GLACCOUNTID = GLACCOUNT.ID
        left outer join dbo.PDACCOUNTSEGMENTVALUE on PDACCOUNTSEGMENTVALUE.ID = BANKACCOUNT.PDACCOUNTSEGMENTVALUEID
          where BANKACCOUNTDEPOSITPAYMENT.ID = @REVENUEID;

        if @ACCOUNT is not null
        begin
            if exists(select * from @TempTbl where ACCOUNT <> @ACCOUNT and TRANSACTIONTYPECODE = 0)
                raiserror('ERR_DEBITACCOUNT_MUST_BE_SAME_AS_BANK.', 13, 1)
        end
        else
        begin
          declare @BANKCASHACCOUNTS table (GLACCOUNTID uniqueidentifier,
                                           ACCOUNT nvarchar(100),
                                           GENERATEDACCOUNT nvarchar(100));
          declare @PDACCOUNTSYSTEMID uniqueidentifier;                
          select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.FINANCIALTRANSACTION where ID = @REVENUEID;                                           
          insert into @BANKCASHACCOUNTS (GLACCOUNTID, ACCOUNT, GENERATEDACCOUNT)
          select GLACCOUNT.ID, GLACCOUNT.ACCOUNTNUMBER, dbo.UFN_BANKACCOUNT_PAYMENT_COMBINEACCOUNTCODE_BYACCOUNTSYSTEM(GLACCOUNT.ID, @ACCOUNTCODE,@PDACCOUNTSYSTEMID)
          from @TempTbl DIST
          inner join dbo.GLACCOUNT on GLACCOUNT.ACCOUNTNUMBER = DIST.ACCOUNT
          where DIST.TRANSACTIONTYPECODE = 0

          if exists(select * from @BANKCASHACCOUNTS where ACCOUNT != GENERATEDACCOUNT)
            raiserror('ERR_DEBITACCOUNTCODE_MUST_BE_SAME_AS_BANK.', 13, 1)
        end
            end

            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.[REVENUEGLDISTRIBUTION] where [REVENUEGLDISTRIBUTION].ID in 
                (select ID from dbo.UFN_REVENUE_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.[REVENUEGLDISTRIBUTION]
            using  @TempTbl as [temp] on [REVENUEGLDISTRIBUTION].ID = [temp].ID
            when matched 
                and (
                    ([REVENUEGLDISTRIBUTION].[ACCOUNT]<>temp.[ACCOUNT]) or 
                    ([REVENUEGLDISTRIBUTION].[ACCOUNT] is null and temp.[ACCOUNT] is not null) or 
                    ([REVENUEGLDISTRIBUTION].[ACCOUNT] is not null and temp.[ACCOUNT] is null) or 
                    ([REVENUEGLDISTRIBUTION].[AMOUNT]<>temp.[AMOUNT]) or 
                    ([REVENUEGLDISTRIBUTION].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
                    ([REVENUEGLDISTRIBUTION].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
                    ([REVENUEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]<>temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID]) or 
                    ([REVENUEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null) or 
                    ([REVENUEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null) or 
                    ([REVENUEGLDISTRIBUTION].[ID]<>temp.[ID]) or 
                    ([REVENUEGLDISTRIBUTION].[ID] is null and temp.[ID] is not null) or 
                    ([REVENUEGLDISTRIBUTION].[ID] is not null and temp.[ID] is null) or 
                    ([REVENUEGLDISTRIBUTION].[PROJECT]<>temp.[PROJECT]) or 
                    ([REVENUEGLDISTRIBUTION].[PROJECT] is null and temp.[PROJECT] is not null) or 
                    ([REVENUEGLDISTRIBUTION].[PROJECT] is not null and temp.[PROJECT] is null) or 
                    ([REVENUEGLDISTRIBUTION].[REFERENCE]<>temp.[REFERENCE]) or 
                    ([REVENUEGLDISTRIBUTION].[REFERENCE] is null and temp.[REFERENCE] is not null) or 
                    ([REVENUEGLDISTRIBUTION].[REFERENCE] is not null and temp.[REFERENCE] is null) or 
                    ([REVENUEGLDISTRIBUTION].[TRANSACTIONTYPECODE]<>temp.[TRANSACTIONTYPECODE]) or 
                    ([REVENUEGLDISTRIBUTION].[TRANSACTIONTYPECODE] is null and temp.[TRANSACTIONTYPECODE] is not null) or 
                    ([REVENUEGLDISTRIBUTION].[TRANSACTIONTYPECODE] is not null and temp.[TRANSACTIONTYPECODE] is null)
                    )
            then update 
                    set 
                        [REVENUEGLDISTRIBUTION].[ACCOUNT]=temp.[ACCOUNT],
                        [REVENUEGLDISTRIBUTION].[AMOUNT]=temp.[AMOUNT],
                        [REVENUEGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]=temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                        [REVENUEGLDISTRIBUTION].[ID]=temp.[ID],
                        [REVENUEGLDISTRIBUTION].[PROJECT]=temp.[PROJECT],
                        [REVENUEGLDISTRIBUTION].[REFERENCE]=temp.[REFERENCE],
                        [REVENUEGLDISTRIBUTION].[TRANSACTIONTYPECODE]=temp.[TRANSACTIONTYPECODE],
                        [REVENUEGLDISTRIBUTION].CHANGEDBYID = @CHANGEAGENTID,
                        [REVENUEGLDISTRIBUTION].DATECHANGED = @CHANGEDATE;                    



            --Update the corresponding rows in the GLTRANSACTION table

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

            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
            );

            insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REVENUEGLDISTRIBUTIONID)
            select 
                newid(), 
                [ACCOUNT],
                [AMOUNT],
                [PROJECT],
                [REFERENCE],
                [TRANSACTIONTYPECODE], 
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID]
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[REVENUEGLDISTRIBUTION] 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)
            select
                GLTRANSACTIONID,
                TRANSACTIONTYPECODE,
                ACCOUNT,
                AMOUNT,
                PROJECT,
                REFERENCE,
                @POSTDATE,
                'Blackbaud Enterprise',
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE,
                dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE([ACCOUNT],@REVENUEID)                
            from 
                @DISTRIBUTIONS;


            -- Insert rows in REVENUEGLDISTRIBUTION table

            insert into dbo.REVENUEGLDISTRIBUTION
                (ID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                REVENUEGLDISTRIBUTIONID,
                @REVENUEID,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                PROJECT,
                REFERENCE, 
                AMOUNT,
                ACCOUNT,
                TRANSACTIONTYPECODE,
                GLTRANSACTIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE
            from 
                @DISTRIBUTIONS;    

            if @@Error <> 0
                return 4;

            return 0;