USP_SAVE_BENEFITGLDISTRIBUTION_NONPLEDGE_PAYMENT

            CREATE procedure dbo.USP_SAVE_BENEFITGLDISTRIBUTION_NONPLEDGE_PAYMENT
            (
                @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 @JOURNAL nvarchar(50);
                set @JOURNAL = 'Blackbaud Enterprise';

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

                if @CUSTOMIZED = 0
                begin
                    declare @REVENUESPLITID uniqueidentifier
                    select top 1 @REVENUESPLITID = ID from FINANCIALTRANSACTIONLINEITEM    where FINANCIALTRANSACTIONID = @REVENUEID

                    declare @DISTRIBUTIONS table(
                        GLTRANSACTIONID uniqueidentifier,
                        REVENUEBENEFITID 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,
                        BASECURRENCYID uniqueidentifier,
                        TRANSACTIONAMOUNT money,
                        TRANSACTIONCURRENCYID uniqueidentifier,
                        BASEEXCHANGERATEID uniqueidentifier,
                        ORGANIZATIONAMOUNT money,
                        ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                        MAPPEDVALUES xml
                    );

                    -- Generate GL distributions for the revenue record.

                    insert into @DISTRIBUTIONS(GLTRANSACTIONID, REVENUEBENEFITID,ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, 
                                GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,ERRORMESSAGE,REVENUESPLITID,
                                BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, MAPPEDVALUES)
                    select 
                        newid(),
                        REVDISTRIBUTIONS.REVENUEBENEFITID,
                        REVDISTRIBUTIONS.ACCOUNTSTRING,
                        REVDISTRIBUTIONS.AMOUNT,
                        REVDISTRIBUTIONS.PROJECT,
                        REVDISTRIBUTIONS.REFERENCE, 
                        REVDISTRIBUTIONS.TRANSACTIONTYPECODE,
                        REVDISTRIBUTIONS.POSTDATE,
                        REVDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        REVDISTRIBUTIONS.ACCOUNTID,
                        REVDISTRIBUTIONS.ERRORMESSAGE,
                        REVDISTRIBUTIONS.REVENUESPLITID,
                        REVDISTRIBUTIONS.BASECURRENCYID,
                        REVDISTRIBUTIONS.TRANSACTIONAMOUNT,
                        REVDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                        REVDISTRIBUTIONS.BASEEXCHANGERATEID,
                        REVDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                        REVDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                        REVDISTRIBUTIONS.MAPPEDVALUES
                    from 
                    (
                        select 
                            REVENUEBENEFIT.ID as REVENUEBENEFITID, 
                            dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUE.TYPECODE, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                            tf.TRANSACTIONTYPECODE, 
                            case when BENEFITADJUSTMENT.ID is null then 
                                REVENUE.POSTDATE        
                            else 
                                BENEFITADJUSTMENT.POSTDATE
                            end as POSTDATE,
                            case when BENEFITADJUSTMENT.ID is null then 
                                REVENUE.POSTSTATUSCODE        
                            else 
                                BENEFITADJUSTMENT.POSTSTATUSCODE
                            end as POSTSTATUSCODE,
                            tf.ACCOUNTSTRING, 
                            tf.PROJECTCODE as PROJECT, 
                            case when BENEFITLINEITEM.DELETEDON is null then BENEFITLINEITEM.BASEAMOUNT else null end as AMOUNT, 
                            dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Benefit Expense' ,'Payment') as REFERENCE,
                            tf.ERRORMESSAGE,
                            REVENUE.TYPECODE as REVENUETRANSACTIONTYPECODE,
                            tf.ACCOUNTID as ACCOUNTID,
                            REVENUESPLIT.ID as REVENUESPLITID,
                            isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID,
                            BENEFITLINEITEM.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT,
                            REVENUE.TRANSACTIONCURRENCYID,
                            REVENUE.BASEEXCHANGERATEID,
                            BENEFITLINEITEM.ORGAMOUNT as ORGANIZATIONAMOUNT,
                            REVENUE.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
                            tf.MAPPEDVALUES
                        from 
                            dbo.FINANCIALTRANSACTION REVENUE
                            inner join REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID and REVENUESPLIT.ID = @REVENUESPLITID 
                            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM BENEFITLINEITEM on BENEFITLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID                        
                            inner join dbo.REVENUEBENEFIT_EXT REVENUEBENEFIT on BENEFITLINEITEM.ID = REVENUEBENEFIT.ID                     
                            inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID 
                            left join dbo.BENEFITADJUSTMENT on REVENUE.ID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE <> 0
                            inner join dbo.PDACCOUNTSYSTEM on REVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID                        
                            cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TYPECODE, REVENUESPLIT_EXT.TYPECODE, 86, 9, REVENUEBENEFIT.BENEFITID) as tf                    
                        where    
                            REVENUE.ID = @REVENUEID 
                            and REVENUE.TYPECODE = 0 
                            and REVENUEBENEFIT.SENDBENEFIT = 0
                            and REVENUESPLIT.DELETEDON is null
                            and REVENUE.DELETEDON is null
                            and REVENUESPLIT.TYPECODE <> 1
                            and BENEFITLINEITEM.DELETEDON is null
                            and BENEFITLINEITEM.TYPECODE <> 1
                    ) as REVDISTRIBUTIONS

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

                    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)

            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

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

                 ,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
                )
                select 
                GLTRANSACTIONID
                ,REVENUEBENEFITID
                ,TRANSACTIONTYPECODE
                ,TRANSACTIONTYPECODE
                ,TRANSACTIONAMOUNT
                ,AMOUNT
                ,ORGANIZATIONAMOUNT
                ,REFERENCE
                ,POSTDATE
                ,ACCOUNTID
                ,row_number() over (order by GLTRANSACTIONID)
                ,0 --default system distribution

                ,isnull(TRANSACTIONCURRENCYID,@ORGANIZATIONCURRENCYID)            
                -- Boilerplate

                ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                from 
                  @DISTRIBUTIONS
              ;

              insert into JOURNALENTRY_EXT(
                ID
                ,DISTRIBUTIONTABLEID
                ,PROJECT
                ,ACCOUNT
                ,JOURNAL
                --,REVERSEDGLTRANSACTIONID

                --,REVERSEDATE

                --,BATCHID

                ,PRECALCORGANIZATIONEXCHANGERATEID
                ,PRECALCBASEEXCHANGERATEID
                ,GLPAYMENTMETHODREVENUETYPEMAPPINGID
                ,TABLENAMECODE
                ,OUTDATED
                ,LOGICALREVENUEID
                ,FULLYPAIDSTATUSCODE
                ,BENEFITTYPECODE
                -- Boilerplate

                ,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
                )
                select
                GLTRANSACTIONID
                ,NEWID()            
                ,coalesce(PROJECT, '')
                ,ACCOUNT
                ,@JOURNAL
                --,source.REVERSEDGLTRANSACTIONID

                --,source.REVERSEDATE

                --,BATCHID

                ,ORGANIZATIONEXCHANGERATEID
                ,BASEEXCHANGERATEID
                ,GLPAYMENTMETHODREVENUETYPEMAPPINGID
                ,5 -- BENEFITGLDISTRIBUTION

                ,0 -- OUTDATED

                ,@REVENUEID
                ,0 --FULLYPAIDSTATUS

                ,1 --BENEFITTYPECODE

                -- Boilerplate

                ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                from 
                  @DISTRIBUTIONS
              ;
        end