USP_SAVE_GIFTFEEADJUSTMENTGLDISTRIBUTION

            CREATE procedure [dbo].[USP_SAVE_GIFTFEEADJUSTMENTGLDISTRIBUTION]
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
                set nocount on;

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

                declare @CUSTOMIZED integer = 0;
                exec  @CUSTOMIZED = dbo.USP_SAVE_GIFTFEEADJUSTMENTGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE

                if @CUSTOMIZED = 0
                begin

                    declare @JOURNAL nvarchar(50);
                    set @JOURNAL = 'Blackbaud Enterprise';

                    declare @DISTRIBUTIONS table(
                        ID uniqueidentifier,
                        FINANCIALTRANSACTIONLINEITEMID uniqueidentifier,
                        TRANSACTIONTYPECODE tinyint,
                        TRANSACTIONAMOUNT money,
                        BASEAMOUNT money,
                        ORGAMOUNT money,
                        COMMENT nvarchar(255),
                        GLACCOUNTID uniqueidentifier,
                        POSTDATE datetime,
                        SEQUENCE int,
                        TYPECODE tinyint,
                        TRANSACTIONCURRENCYID uniqueidentifier,
                        PROJECT nvarchar(500),
                        ACCOUNT nvarchar(500),
                        PRECALCORGANIZATIONEXCHANGERATEID uniqueidentifier,
                        PRECALCBASEEXCHANGERATEID uniqueidentifier,
                        ERRORMESSAGE nvarchar(max),
                        MAPPEDVALUES xml,
                        REVENUESPLITGIFTFEEID uniqueidentifier,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier
                    );

                    insert into @DISTRIBUTIONS (
                        ID,
                        FINANCIALTRANSACTIONLINEITEMID,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONAMOUNT,
                        BASEAMOUNT,
                        ORGAMOUNT,
                        COMMENT,
                        GLACCOUNTID,
                        POSTDATE,
                        SEQUENCE,
                        TYPECODE,
                        TRANSACTIONCURRENCYID,
                        PROJECT,
                        ACCOUNT,
                        PRECALCORGANIZATIONEXCHANGERATEID,
                        PRECALCBASEEXCHANGERATEID,
                        MAPPEDVALUES,
                        ERRORMESSAGE,
                        REVENUESPLITGIFTFEEID,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID
                    )
                    select
                        newid(),
                        FTLI.ID,
                        GIFTFEEDISTRIBUTIONS.TRANSACTIONTYPECODE,
                        GIFTFEEDISTRIBUTIONS.TRANSACTIONAMOUNT,
                        GIFTFEEDISTRIBUTIONS.AMOUNT,
                        GIFTFEEDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                        GIFTFEEDISTRIBUTIONS.REFERENCE,
                        GIFTFEEDISTRIBUTIONS.ACCOUNTID,
                        GIFTFEEDISTRIBUTIONS.POSTDATE,
                        row_number() over (order by GIFTFEEDISTRIBUTIONS.TRANSACTIONTYPECODE),
                        case when GIFTFEEDISTRIBUTIONS.TRANSACTIONCURRENCYID is null then 1 else 0 end,
                        GIFTFEEDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                        GIFTFEEDISTRIBUTIONS.PROJECT,
                        GIFTFEEDISTRIBUTIONS.ACCOUNTSTRING,
                        GIFTFEEDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                        GIFTFEEDISTRIBUTIONS.BASEEXCHANGERATEID,
                        GIFTFEEDISTRIBUTIONS.MAPPEDVALUES,
                        GIFTFEEDISTRIBUTIONS.ERRORMESSAGE,
                        GIFTFEEDISTRIBUTIONS.REVENUESPLITID,
                        GIFTFEEDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID
                    from dbo.UFN_REVENUE_GENERATEGIFTFEEGLDISTRIBUTION(@REVENUEID) as GIFTFEEDISTRIBUTIONS
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI
                        on FTLI.FINANCIALTRANSACTIONID = GIFTFEEDISTRIBUTIONS.REVENUEID
                        and FTLI.SOURCELINEITEMID = GIFTFEEDISTRIBUTIONS.REVENUESPLITID
                    where FTLI.TYPECODE = 7 --gift fee

                        and FTLI.POSTSTATUSCODE != 2 

                    declare @ERRORMESSAGE nvarchar(max);

                    select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where GLACCOUNTID is null

                    if @ERRORMESSAGE <> ''
                        raiserror('%s', 13, 1, @ERRORMESSAGE);
                    else
                        --Trap the error when a holding account exists so we can show it to the user later.

                        insert into dbo.GLACCOUNTMAPPINGERROR (
                            [TRANSACTIONID]
                            ,[TRANSACTIONTYPECODE]
                            ,[ERRORMESSAGE]
                            ,[MAPPEDVALUES]
                            ,[ADDEDBYID]
                            ,[CHANGEDBYID]
                            ,[DATEADDED]
                            ,[DATECHANGED])
                        select distinct
                            @REVENUEID
                            ,(select TRANSACTIONTYPECODE from dbo.REVENUE where ID = @REVENUEID)
                            ,D.ERRORMESSAGE
                            ,convert(varchar(max),D.MAPPEDVALUES)
                            ,@CHANGEAGENTID
                            ,@CHANGEAGENTID
                            ,@CHANGEDATE
                            ,@CHANGEDATE
                        from @DISTRIBUTIONS D 
                        where nullif(D.ERRORMESSAGE,'') is not null 
                            and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @REVENUEID and DELETED = 0)

                    if (select count(*) from @DISTRIBUTIONS) > 0  
                        set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE((select top 1 POSTDATE from @DISTRIBUTIONS));

                    if @ERRORMESSAGE <> ''
                        raiserror(@ERRORMESSAGE, 13, 1);    

                    declare @Cache table (HashCode uniqueidentifier, ErrorMessage nvarchar(max), GLAccountID uniqueidentifier, TransactionTypeCode tinyint, ErrorNumber tinyint, MappedValues nvarchar(max), PDAccountSystemID uniqueidentifier)
                    insert into @Cache(HashCode, ErrorMessage, GLAccountID, TransactionTypeCode, ErrorNumber, MappedValues, PDAccountSystemID)
                    select
                        v.MD5HASHCODE
                        ,d.ERRORMESSAGE
                        ,d.GLACCOUNTID
                        ,d.TRANSACTIONTYPECODE
                        ,v.ERRORCODE
                        ,v.MAPPEDVALUES
                        ,v.PDACCOUNTSYSTEMID
                    from @DISTRIBUTIONS d
                    cross apply dbo.UFN_PDACCOUNTLOOKUPCACHE_PARSE_MAPPEDVALUES(MAPPEDVALUES) v
                    where v.MD5HASHCODE is not null

                    insert into dbo.PDACCOUNTLOOKUPCACHE (MD5HASHCODE, ERRORMESSAGE, GLACCOUNTID, TRANSACTIONTYPECODE, ERRORNUMBER, MAPPEDVALUES, PDACCOUNTSYSTEMID)
                    select HashCode, ErrorMessage, GLAccountID, TransactionTypeCode, ErrorNumber, MappedValues, PDAccountSystemID
                    from @Cache tt
                    where not exists (select MD5HASHCODE from dbo.PDACCOUNTLOOKUPCACHE with (NOLOCK) where MD5HASHCODE = tt.HashCode and TRANSACTIONTYPECODE = tt.TransactionTypeCode and MAPPEDVALUES = tt.MappedValues)

                    insert into dbo.JOURNALENTRY(
                        ID,
                        FINANCIALTRANSACTIONLINEITEMID,
                        TRANSACTIONTYPECODE,
                        SUBLEDGERTYPECODE,
                        TRANSACTIONAMOUNT,
                        BASEAMOUNT,
                        ORGAMOUNT,
                        COMMENT,
                        POSTDATE,
                        GLACCOUNTID,
                        SEQUENCE,
                        TYPECODE,
                        TRANSACTIONCURRENCYID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                    select
                        ID,
                        FINANCIALTRANSACTIONLINEITEMID,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONAMOUNT,
                        BASEAMOUNT,
                        ORGAMOUNT,
                        COMMENT,
                        POSTDATE,
                        GLACCOUNTID,
                        SEQUENCE,
                        TYPECODE,
                        TRANSACTIONCURRENCYID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        getdate(),
                        getdate()
                    from @DISTRIBUTIONS

                    insert into dbo.JOURNALENTRY_EXT (
                        ID,
                        PROJECT,
                        ACCOUNT,
                        JOURNAL,
                        TABLENAMECODE,
                        PRECALCORGANIZATIONEXCHANGERATEID,
                        PRECALCBASEEXCHANGERATEID,
                        REVENUESPLITGIFTFEEID,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        LOGICALREVENUEID,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED,
                        OUTDATED
                    )
                    select
                        ID,
                        PROJECT,
                        ACCOUNT,
                        @JOURNAL,
                        8, -- GIFTFEEGLDISTRIBUTION table

                        PRECALCORGANIZATIONEXCHANGERATEID,
                        PRECALCBASEEXCHANGERATEID,
                        REVENUESPLITGIFTFEEID,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        @REVENUEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        getdate(),
                        getdate(),
                        0
                    from @DISTRIBUTIONS


                    update LI set
                        TRANSACTIONAMOUNT = T.[TRANAMOUNT]
                        ,BASEAMOUNT = T.[BASEAMOUNT]
                        ,ORGAMOUNT =  T.[ORGAMOUNT]
                        ,CHANGEDBYID = @CHANGEAGENTID
                        ,DATECHANGED = @CHANGEDATE
                    from (select SUM(D.TRANSACTIONAMOUNT)/2 [TRANAMOUNT]
                            ,SUM(D.BASEAMOUNT)/2 [BASEAMOUNT]
                            ,SUM(D.ORGAMOUNT)/2 [ORGAMOUNT]
                            ,LI.ID
                        from dbo.FINANCIALTRANSACTIONLINEITEM LI
                        inner join dbo.JOURNALENTRY_EXT JEX on LI.SOURCELINEITEMID = JEX.REVENUESPLITGIFTFEEID
                        inner join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
                        inner join @DISTRIBUTIONS D on D.ID = JEX.ID
                        where JE.FINANCIALTRANSACTIONLINEITEMID is null and LI.DELETEDON is null and LI.TYPECODE = 7
                        group by LI.ID) T
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = T.ID;

                end