USP_SAVE_REVENUEGLDISTRIBUTION

    CREATE procedure [dbo].[USP_SAVE_REVENUEGLDISTRIBUTION]
    (
        @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_REVENUEGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE

        if @CUSTOMIZED = 0
            begin
                declare @JOURNAL nvarchar(50);
                set @JOURNAL = 'Blackbaud Enterprise';

                declare @DISTRIBUTIONS table(
                    GLTRANSACTIONID uniqueidentifier,
                    ACCOUNT nvarchar(100),
                    AMOUNT money,
                    PROJECT nvarchar(100),
                    REFERENCE nvarchar(255),
                    TRANSACTIONTYPECODE tinyint,
                    POSTDATE datetime,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                    ACCOUNTID uniqueidentifier,
                    ERRORMESSAGE nvarchar(max),
                    REVENUESPLITID uniqueidentifier,
                    REVENUEGLDISTRIBUTIONID uniqueidentifier,
                    BASECURRENCYID uniqueidentifier,
                    TRANSACTIONAMOUNT money,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONAMOUNT money,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                    SYSTEMDISTRIBUTION bit default 0,
                    MAPPEDVALUES xml,
                    PAYMENTMETHODCODE tinyint
                );

                -- Generate GL distributions for the revenue record.

                insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, 
                            GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,ERRORMESSAGE,REVENUESPLITID,REVENUEGLDISTRIBUTIONID,
                            BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, MAPPEDVALUES, PAYMENTMETHODCODE)
                select 
                    newid(),
                    REVDISTRIBUTIONS.ACCOUNTSTRING,
                    REVDISTRIBUTIONS.AMOUNT,
                    REVDISTRIBUTIONS.PROJECT,
                    REVDISTRIBUTIONS.REFERENCE, 
                    REVDISTRIBUTIONS.TRANSACTIONTYPECODE,
                    REVDISTRIBUTIONS.POSTDATE,
                    REVDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    REVDISTRIBUTIONS.ACCOUNTID,
                    REVDISTRIBUTIONS.ERRORMESSAGE,
                    REVDISTRIBUTIONS.REVENUESPLITID,
                    newid(),
                    REVDISTRIBUTIONS.BASECURRENCYID,
                    REVDISTRIBUTIONS.TRANSACTIONAMOUNT,
                    REVDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                    REVDISTRIBUTIONS.BASEEXCHANGERATEID,
                    REVDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                    REVDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                    REVDISTRIBUTIONS.MAPPEDVALUES,
                    REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                from 
                    dbo.UFN_REVENUE_GENERATEGLDISTRIBUTION_FORSINGLEREVENUE(@REVENUEID) as REVDISTRIBUTIONS 
                inner join 
                    dbo.FINANCIALTRANSACTION with (nolock) on REVDISTRIBUTIONS.REVENUEID = FINANCIALTRANSACTION.ID
                left join
                    dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
                where not exists
                    (
                        select JE.ID 
                        from dbo.JOURNALENTRY as JE with (nolock) 
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                        inner join dbo.JOURNALENTRY_EXT as JEX on JE.ID = JEX.ID
                        where LI.FINANCIALTRANSACTIONID = REVDISTRIBUTIONS.REVENUEID 
                            and LI.ID = REVDISTRIBUTIONS.REVENUESPLITID
                            and (LI.DELETEDON is null and LI.REVERSEDLINEITEMID is null)
                            and JEX.OUTDATED = 0 and JEX.TABLENAMECODE = 1
                    )
                    and REVDISTRIBUTIONS.POSTSTATUSCODE > 0
                    and FINANCIALTRANSACTION.TYPECODE <> 2  -- is not a recurring gift

                    and FINANCIALTRANSACTION.TYPECODE <> 3; -- matching gift claim


                declare @ERRORMESSAGE nvarchar(max);
                --Only throw an error if the account ID is null because we want to allow the record to save but also trap the error

                -- when a holding account exists.

                select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where nullif(ERRORMESSAGE,'') is not null and ACCOUNTID is null;

                if nullif(@ERRORMESSAGE,'') is not null
                    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
                        isnull(S.ID, R.ID)
                        ,R.TYPECODE
                        ,D.ERRORMESSAGE
                        ,convert(varchar(max),D.MAPPEDVALUES)
                        ,@CHANGEAGENTID
                        ,@CHANGEAGENTID
                        ,@CHANGEDATE
                        ,@CHANGEDATE
                    from @DISTRIBUTIONS D 
                    inner join dbo.FINANCIALTRANSACTION R on R.ID = @REVENUEID
                    left join dbo.SALESORDER S on S.REVENUEID = R.ID
                    where nullif(D.ERRORMESSAGE,'') is not null 
                        and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = isnull(S.ID, R.ID) and DELETED = 0)

                --Bug 70136 - Need adjustment post status code to determine if date should be checked. Will be null if DNP.

                --Check to see if there is an adjustment, if so, use adjustment poststatuscode

                DECLARE @ADJUSTMENTPOSTSTATUSCODE tinyint
                -- Bug 82359 ~ No need to check post date, if Revenue record is Do Not Post, unless Adjustment needs posting.

                declare @POSTSTATUSCODE tinyint;
                select @POSTSTATUSCODE = POSTSTATUSCODE from dbo.FINANCIALTRANSACTION with (nolock) where ID = @REVENUEID
                ;
                if @POSTSTATUSCODE = 3                
                    SET @ADJUSTMENTPOSTSTATUSCODE = 2
                else
                    SET @ADJUSTMENTPOSTSTATUSCODE = 1
                ;        
                if EXISTS (SELECT ID FROM ADJUSTMENT with (nolock) WHERE REVENUEID = @REVENUEID)
                    BEGIN
                     SELECT @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE FROM ADJUSTMENT with (nolock) WHERE REVENUEID = @REVENUEID
                    END

                if exists(select * from @DISTRIBUTIONS) and @ADJUSTMENTPOSTSTATUSCODE <> 2
                    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.ACCOUNTID
                    ,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)

                --Change to FTM.  First change is just do a single insert into JournalEntry                 

                insert into dbo.JOURNALENTRY (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, BASEAMOUNT, COMMENT, 
                    POSTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLACCOUNTID, ORGAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, TYPECODE, SUBLEDGERTYPECODE)        
                select GLTRANSACTIONID, REVENUESPLITID, TRANSACTIONTYPECODE, AMOUNT, REFERENCE,
                    POSTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, ACCOUNTID, ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, case when SYSTEMDISTRIBUTION = 1 then 1 else 0 end, TRANSACTIONTYPECODE
                from @DISTRIBUTIONS

                insert into dbo.JOURNALENTRY_EXT (ID, DISTRIBUTIONTABLEID, TABLENAMECODE, PROJECT, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PRECALCPOSTSTATUSCODE, PRECALCORGANIZATIONEXCHANGERATEID,
                    PRECALCBASEEXCHANGERATEID,  ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PAYMENTMETHODCODE)
                select GLTRANSACTIONID,GLTRANSACTIONID,1,PROJECT, @JOURNAL, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @ADJUSTMENTPOSTSTATUSCODE, ORGANIZATIONEXCHANGERATEID,
                    BASEEXCHANGERATEID,  ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID, coalesce(PAYMENTMETHODCODE, 1) --default is 1 for check

                from @DISTRIBUTIONS

                -- Update distribution for multicurrency gain/loss.

                if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency') = 1
                    exec dbo.USP_PAYMENTAPPLICATION_MULTICURRENCYGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CHANGEDATE;

            end