USP_REVENUE_GETWRITEOFFGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2

            CREATE procedure dbo.USP_REVENUE_GETWRITEOFFGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2
            (
                @WRITEOFFID 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,
                [GLACCOUNTID] uniqueidentifier
            )

            declare @PDACCOUNTSYSTEMID uniqueidentifier;
            select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
            from dbo.FINANCIALTRANSACTION
            where ID = @WRITEOFFID

            insert into @TempTbl select 
                GL.[ACCOUNT],
                GL.[AMOUNT],
                GL.[GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                GL.[ID],
                GL.[PROJECT],
                GL.[REFERENCE],
                GL.[TRANSACTIONTYPECODE],
                GL.[ORGANIZATIONAMOUNT],
                GL.[BASECURRENCYID],
                GL.[TRANSACTIONAMOUNT],
                GL.[TRANSACTIONCURRENCYID],
                GL.[BASEEXCHANGERATEID],
                GL.[ORGANIZATIONEXCHANGERATEID],
                GLACCOUNT.ID [GLACCOUNTID]
            from dbo.UFN_REVENUE_GETWRITEOFFGLDISTRIBUTION_FROMITEMLISTXML(@XML)GL
            left outer join dbo.GLACCOUNT on GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and GLACCOUNT.ACCOUNTNUMBER = GL.ACCOUNT


            declare @REVENUEID uniqueidentifier;
            select @REVENUEID =  WRITEOFF.REVENUEID from WRITEOFF where ID = @WRITEOFFID

            if @REVENUEID is null
                select top 1 
                    @REVENUEID = WRITEOFFGLDISTRIBUTION.LOGICALREVENUEID
                from 
                    @TempTbl as [temp]
                inner join
                    dbo.JOURNALENTRY_EXT WRITEOFFGLDISTRIBUTION on [temp].ID = isnull(WRITEOFFGLDISTRIBUTION.DISTRIBUTIONTABLEID,WRITEOFFGLDISTRIBUTION.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.[WRITEOFFGLDISTRIBUTION] where [WRITEOFFGLDISTRIBUTION].ID in 
                (select ID from dbo.UFN_REVENUE_GETWRITEOFFGLDISTRIBUTION
                (
                    @WRITEOFFID
                )
                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;

            if exists(select 1 from @TempTbl where not LEN(ACCOUNT)>0)
                raiserror ('CK_WRITEOFFGLDISTRIBUTION_ACCOUNT', 16, 1);
            if exists(select 1 from @TempTbl where AMOUNT<0)
                raiserror('CK_WRITEOFFGLDISTRIBUTION_VALIDAMOUNT', 16, 1);

            -- 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 = temp.GLACCOUNTID
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @CHANGEDATE
            from @TempTbl [temp]
            inner join dbo.JOURNALENTRY_EXT JEX on JEX.DISTRIBUTIONTABLEID = temp.ID and JEX.OUTDATED = 0
            inner join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
            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] <> temp.GLACCOUNTID) or
                ([JE].[GLACCOUNTID] is null and temp.GLACCOUNTID is not null) or
                ([JE].[GLACCOUNTID] is not null and temp.GLACCOUNTID 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 and JEX.OUTDATED = 0
            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 dbo.FINANCIALTRANSACTIONLINEITEM LI
            where LI.FINANCIALTRANSACTIONID = @WRITEOFFID 
                and LI.TYPECODE in (0,1,3,5,6,7,8,98)
                and LI.POSTSTATUSCODE != 2;

            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,
                WRITEOFFGLDISTRIBUTIONID uniqueidentifier,
                [ORGANIZATIONAMOUNT] money,
                [BASECURRENCYID] uniqueidentifier,
                [TRANSACTIONAMOUNT] money,
                [TRANSACTIONCURRENCYID] uniqueidentifier,
                [BASEEXCHANGERATEID] uniqueidentifier,
                [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
                GLACCOUNTID uniqueidentifier
            );

            insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFGLDISTRIBUTIONID, [ORGANIZATIONAMOUNT], [BASECURRENCYID], [TRANSACTIONAMOUNT], [TRANSACTIONCURRENCYID], [BASEEXCHANGERATEID], [ORGANIZATIONEXCHANGERATEID],GLACCOUNTID)
            select 
                newid(), 
                [ACCOUNT],
                [AMOUNT],
                [PROJECT],
                [REFERENCE],
                [TRANSACTIONTYPECODE], 
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID],
                [ORGANIZATIONAMOUNT],
                [BASECURRENCYID],
                [TRANSACTIONAMOUNT],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID],
                [ORGANIZATIONEXCHANGERATEID],
                temp.GLACCOUNTID
            from @TempTbl as [temp]
            where not exists (select ID from dbo.JOURNALENTRY_EXT as data where data.DISTRIBUTIONTABLEID = [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 = @WRITEOFFID and LI.TYPECODE = 98 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 = @WRITEOFFID and LI.DELETEDON is NULL and LI.POSTSTATUSCODE != 2
                having COUNT(LI.ID) = 1;

                if @FTLIID is null
                begin
                    set @FTLIID = newid();

                    insert into dbo.FINANCIALTRANSACTIONLINEITEM
                    (
                        ID
                        ,FINANCIALTRANSACTIONID
                        ,TRANSACTIONAMOUNT
                        ,BASEAMOUNT
                        ,ORGAMOUNT
                        ,VISIBLE
                        ,[DESCRIPTION]
                        ,SEQUENCE
                        ,TYPECODE
                        ,POSTSTATUSCODE
                        ,POSTDATE
                        -- Boilerplate
                        ,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
                    )
                    select
                        @FTLIID
                        ,@WRITEOFFID
                        ,0
                        ,0
                        ,0
                        ,0
                        ,''
                        ,0
                        ,98
                        ,case FT.POSTSTATUSCODE when 2 then 1 when 1 then 1 else 3 end
                        ,@POSTDATE
                        ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                    from dbo.FINANCIALTRANSACTION FT
                    where FT.ID = @WRITEOFFID;
                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
                ,T.GLACCOUNTID
                ,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

            insert into dbo.JOURNALENTRY_EXT
            (
                ID 
                ,DISTRIBUTIONTABLEID
                ,GLPAYMENTMETHODREVENUETYPEMAPPINGID
                ,PROJECT
                ,JOURNAL
                ,OUTDATED
                ,TABLENAMECODE
                ,WRITEOFFID
                ,REVENUEID
                ,ACCOUNT
                ,PRECALCORGANIZATIONEXCHANGERATEID
                ,PRECALCBASEEXCHANGERATEID
                -- Boilerplate
                ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                GLTRANSACTIONID
                ,WRITEOFFGLDISTRIBUTIONID
                ,GLPAYMENTMETHODREVENUETYPEMAPPINGID
                ,PROJECT
                ,'Blackbaud Enterprise'
                ,0
                ,12 -- WRITEOFFGLDISTRIBUTION
                ,@WRITEOFFID
                ,@REVENUEID
                ,ACCOUNT
                ,ORGANIZATIONEXCHANGERATEID
                ,BASEEXCHANGERATEID
                ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from @DISTRIBUTIONS T
*/
            if @@Error <> 0
                return 4;

            return 0;