USP_REVENUE_GETGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2

            CREATE procedure dbo.USP_REVENUE_GETGLDISTRIBUTION_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(255),
               [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_GETGLDISTRIBUTION_FROMITEMLISTXML(@XML)


            if exists( select * from @TempTbl where (AMOUNT< 0) )
       raiserror('CK_REVENUEGLDISTRIBUTION_VALIDAMOUNT', 16, 1);

      /* 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);
                declare @BANKACCOUNTCURRENCYID uniqueidentifier;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @DEPOSITID uniqueidentifier;
          select @ACCOUNT = CASE WHEN BANKACCOUNT.GLACCOUNTID IS NOT NULL THEN GLACCOUNT.ACCOUNTNUMBER END,
          @ACCOUNTCODE = PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION
                    ,@BANKACCOUNTCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID
                    ,@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
                    ,@DEPOSITID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
          from dbo.BANKACCOUNTDEPOSITPAYMENT
                inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
          inner join dbo.BANKACCOUNTTRANSACTION_EXT 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 @BANKACCOUNTCURRENCYID = @TRANSACTIONCURRENCYID
                begin
                    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
            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;

            declare @CURRENTENTRIES table (
                GLTRANSACTIONID uniqueidentifier
            )
            insert into @CURRENTENTRIES
            select GLTRANSACTIONID from dbo.UFN_REVENUE_GETGLDISTRIBUTION(@REVENUEID)

            -- delete any links that might exist for the deposit distributions

            delete DEPOSITGLDISTRIBUTIONLINK
            from dbo.DEPOSITGLDISTRIBUTIONLINK
            inner join dbo.JOURNALENTRY on
                DEPOSITGLDISTRIBUTIONLINK.ID = JOURNALENTRY.ID
            inner join @CURRENTENTRIES GLDIST on
                GLDIST.GLTRANSACTIONID = JOURNALENTRY.ID
            where 
                JOURNALENTRY.TYPECODE = 0 and
                not exists(select 1 from @TempTbl where ID = JOURNALENTRY.ID)

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

            delete [JOURNALENTRY]
            from dbo.[JOURNALENTRY]
            inner join @CURRENTENTRIES GLDIST on
                GLDIST.GLTRANSACTIONID = JOURNALENTRY.ID
            where 
                JOURNALENTRY.TYPECODE = 0 and
                not exists(select 1 from @TempTbl [TempTbl] where TempTbl.ID = JOURNALENTRY.ID)

            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 JE set
                TRANSACTIONAMOUNT = temp.[TRANSACTIONAMOUNT]
                ,BASEAMOUNT = temp.AMOUNT
                ,ORGAMOUNT = temp.[ORGANIZATIONAMOUNT]
                ,COMMENT = temp.[REFERENCE]
                ,TRANSACTIONTYPECODE = temp.[TRANSACTIONTYPECODE]
                ,SUBLEDGERTYPECODE = temp.[TRANSACTIONTYPECODE]
                ,TRANSACTIONCURRENCYID = temp.[TRANSACTIONCURRENCYID]
                ,GLACCOUNTID = GLACCOUNT.ID
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @CHANGEDATE
            from @TempTbl [temp]
            inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = temp.ID
            inner join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = @REVENUEID
            left join dbo.GLACCOUNT on GLACCOUNT.ACCOUNTNUMBER = temp.ACCOUNT and GLACCOUNT.PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
            where 
                ([JE].[TRANSACTIONAMOUNT]<>temp.[TRANSACTIONAMOUNT]) or 
                ([JE].[TRANSACTIONAMOUNT] is null and temp.[TRANSACTIONAMOUNT] is not null) or 
                ([JE].[TRANSACTIONAMOUNT] is not null and temp.[TRANSACTIONAMOUNT] is null) or
                ([JE].[BASEAMOUNT]<>temp.[AMOUNT]) or 
                ([JE].[BASEAMOUNT] is null and temp.[AMOUNT] is not null) or 
                ([JE].[BASEAMOUNT] is not null and temp.[AMOUNT] is null) or
                ([JE].[ORGAMOUNT]<>temp.[ORGANIZATIONAMOUNT]) or 
                ([JE].[ORGAMOUNT] is null and temp.[ORGANIZATIONAMOUNT] is not null) or 
                ([JE].[ORGAMOUNT] is not null and temp.[ORGANIZATIONAMOUNT] is null) or
                ([JE].[TRANSACTIONTYPECODE]<>temp.[TRANSACTIONTYPECODE]) or 
                ([JE].[TRANSACTIONTYPECODE] is null and temp.[TRANSACTIONTYPECODE] is not null) or 
                ([JE].[TRANSACTIONTYPECODE] is not null and temp.[TRANSACTIONTYPECODE] is null) or
                ([JE].[TRANSACTIONCURRENCYID]<>temp.[TRANSACTIONCURRENCYID]) or 
                ([JE].[TRANSACTIONCURRENCYID] is null and temp.[TRANSACTIONCURRENCYID] is not null) or 
                ([JE].[TRANSACTIONCURRENCYID] is not null and temp.[TRANSACTIONCURRENCYID] is null) or
                ([JE].[GLACCOUNTID] <> GLACCOUNT.ID) or
                ([JE].[GLACCOUNTID] is null and GLACCOUNT.ID is not null) or
                ([JE].[GLACCOUNTID] is not null and GLACCOUNT.ID is null) or
                ([JE].[COMMENT]<>temp.[REFERENCE]) or 
                ([JE].[COMMENT] is null and temp.[REFERENCE] is not null) or 
                ([JE].[COMMENT] is not null and temp.[REFERENCE] is null);

            update JEX set
                ACCOUNT = temp.[ACCOUNT]
                ,GLPAYMENTMETHODREVENUETYPEMAPPINGID = temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID]
                ,PROJECT = temp.[PROJECT]
                ,PRECALCBASEEXCHANGERATEID = temp.[BASEEXCHANGERATEID]
                ,PRECALCORGANIZATIONEXCHANGERATEID = temp.[ORGANIZATIONEXCHANGERATEID]
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @CHANGEDATE
            from @TempTbl [temp]
            inner join dbo.JOURNALENTRY_EXT JEX on JEX.DISTRIBUTIONTABLEID = temp.ID
            where
                ([JEX].[ACCOUNT]<>temp.[ACCOUNT]) or 
                ([JEX].[ACCOUNT] is null and temp.[ACCOUNT] is not null) or 
                ([JEX].[ACCOUNT] is not null and temp.[ACCOUNT] is null) or
                ([JEX].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]<>temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID]) or 
                ([JEX].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null) or 
                ([JEX].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null) or
                ([JEX].[PROJECT]<>temp.[PROJECT]) or 
                ([JEX].[PROJECT] is null and temp.[PROJECT] is not null) or 
                ([JEX].[PROJECT] is not null and temp.[PROJECT] is null) or
                ([JEX].[PRECALCBASEEXCHANGERATEID]<>temp.[BASEEXCHANGERATEID]) or 
                ([JEX].[PRECALCBASEEXCHANGERATEID] is null and temp.[BASEEXCHANGERATEID] is not null) or 
                ([JEX].[PRECALCBASEEXCHANGERATEID] is not null and temp.[BASEEXCHANGERATEID] is null) or 
                ([JEX].[PRECALCORGANIZATIONEXCHANGERATEID]<>temp.[ORGANIZATIONEXCHANGERATEID]) or 
                ([JEX].[PRECALCORGANIZATIONEXCHANGERATEID] is null and temp.[ORGANIZATIONEXCHANGERATEID] is not null) or 
                ([JEX].[PRECALCORGANIZATIONEXCHANGERATEID] is not null and temp.[ORGANIZATIONEXCHANGERATEID] is null);

            update LI set
                POSTDATE = @POSTDATE
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @CHANGEDATE
            from @TempTbl [temp]
            inner join dbo.JOURNALENTRY_EXT JEX on JEX.DISTRIBUTIONTABLEID = temp.ID
            inner join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID and LI.POSTSTATUSCODE != 2
            where 
                LI.POSTDATE != @POSTDATE or
                LI.POSTDATE is null and @POSTDATE is not null or
                LI.POSTDATE is not null and @POSTDATE is null;

            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.[JOURNALENTRY_EXT] as data where data.DISTRIBUTIONTABLEID = [temp].ID or data.ID = temp.ID)

            declare @FTLIID uniqueidentifier;
            declare @sequenceBase int;
            select @sequenceBase = isnull(max(SEQUENCE),0) from dbo.JOURNALENTRY where FINANCIALTRANSACTIONLINEITEMID is null and FINANCIALBATCHID is null;

            select @FTLIID = LI.ID
            from dbo.FINANCIALTRANSACTIONLINEITEM LI
            where LI.FINANCIALTRANSACTIONID = @REVENUEID and LI.TYPECODE in (98, 0) and LI.DELETEDON is NULL and LI.POSTSTATUSCODE != 2;

            if @FTLIID is null
            begin
                select @FTLIID = MAX(cast(LI.ID as nvarchar(36)))
                from dbo.FINANCIALTRANSACTIONLINEITEM LI
                where LI.FINANCIALTRANSACTIONID = @REVENUEID and LI.DELETEDON is NULL and LI.POSTSTATUSCODE != 2
                having COUNT(LI.ID) = 1;

                if @FTLIID is null
                begin
                    declare @ADJUSTMENTID uniqueidentifier;
                    select @ADJUSTMENTID = A.ID 
                    from dbo.ADJUSTMENT A
                    inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = A.ID
                    where A.REVENUEID = @REVENUEID and A.POSTSTATUSCODE = 1;

                    set @FTLIID = newid();

                    insert into dbo.FINANCIALTRANSACTIONLINEITEM
                    (
                        ID
                        ,FINANCIALTRANSACTIONID
                        ,TRANSACTIONAMOUNT
                        ,BASEAMOUNT
                        ,ORGAMOUNT
                        ,VISIBLE
                        ,[DESCRIPTION]
                        ,SEQUENCE
                        ,TYPECODE
                        ,POSTSTATUSCODE
                        ,POSTDATE
                        ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
                        -- Boilerplate

                        ,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
                    )
                    select
                        @FTLIID
                        ,@REVENUEID
                        ,0
                        ,0
                        ,0
                        ,0
                        ,''
                        ,0
                        ,98
                        ,case FT.POSTSTATUSCODE when 2 then 1 when 1 then 1 else 3 end
                        ,FT.POSTDATE
                        ,@ADJUSTMENTID
                        ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                    from dbo.FINANCIALTRANSACTION FT
                    where FT.ID = @REVENUEID;
                end
            end

            -- Insert rows in JOURNALENTRY table

            insert into dbo.JOURNALENTRY 
            (
                ID
                ,FINANCIALTRANSACTIONLINEITEMID
                ,TRANSACTIONTYPECODE
                ,SUBLEDGERTYPECODE 
                ,TRANSACTIONAMOUNT
                ,BASEAMOUNT
                ,ORGAMOUNT
                ,COMMENT
                ,POSTDATE
                ,GLACCOUNTID    
                ,SEQUENCE          
                ,TYPECODE
                ,TRANSACTIONCURRENCYID
                -- Boilerplate

                ,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            select
                T.GLTRANSACTIONID
                ,@FTLIID
                ,T.TRANSACTIONTYPECODE
                ,T.TRANSACTIONTYPECODE
                ,T.TRANSACTIONAMOUNT
                ,T.AMOUNT
                ,T.ORGANIZATIONAMOUNT
                ,T.REFERENCE
                ,@POSTDATE
                ,GLACCOUNT.ID
                ,row_number() over (order by ACCOUNT) + @sequenceBase
                ,case when T.TRANSACTIONCURRENCYID is null then 1 else 0 end
                ,T.TRANSACTIONCURRENCYID
                ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from @DISTRIBUTIONS T
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = @REVENUEID
            left join dbo.GLACCOUNT on GLACCOUNT.ACCOUNTNUMBER = T.ACCOUNT and GLACCOUNT.PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID;

            insert into dbo.JOURNALENTRY_EXT
            (
                ID 
        ,DISTRIBUTIONTABLEID
        ,GLPAYMENTMETHODREVENUETYPEMAPPINGID
        ,PROJECT
        ,JOURNAL
        ,OUTDATED
        ,TABLENAMECODE
        ,REVENUEID
        ,ACCOUNT
                ,PRECALCORGANIZATIONEXCHANGERATEID
                ,PRECALCBASEEXCHANGERATEID
        -- Boilerplate

        ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
            select
                GLTRANSACTIONID
                ,REVENUEGLDISTRIBUTIONID
                ,GLPAYMENTMETHODREVENUETYPEMAPPINGID
                ,PROJECT
                ,'Blackbaud Enterprise'
                ,0
                ,1 -- REVENUEGLDISTRIBUTION

                ,@REVENUEID
                ,ACCOUNT
                ,ORGANIZATIONEXCHANGERATEID
                ,BASEEXCHANGERATEID
                ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from @DISTRIBUTIONS T

            if @DEPOSITID is not null
                insert into dbo.DEPOSITGLDISTRIBUTIONLINK(ID, DEPOSITID)
                select GLTRANSACTIONID, @DEPOSITID
                from @DISTRIBUTIONS
                where TRANSACTIONTYPECODE = 0;

            if @@Error <> 0
                return 4;

            return 0;