USP_SAVE_BENEFITGLDISTRIBUTION_PLEDGE_PAYMENT

CREATE procedure dbo.USP_SAVE_BENEFITGLDISTRIBUTION_PLEDGE_PAYMENT
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @POSTDATE 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_PLEDGE_PAYMENT_CUSTOMIZE @REVENUEID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE

                if @CUSTOMIZED = 0
                begin

                    --Get the postdate for REVERSAL from the last adjustment posted

                    if @POSTDATE is null
                        select top 1 @POSTDATE = POSTDATE from dbo.BENEFITADJUSTMENT where REVENUEID = @REVENUEID and POSTSTATUSCODE = 0 order by DATEADDED desc;

                    --If there are no posted adjustments then get the postdate for REVERSAL from the REVENUE record

                    if @POSTDATE is null
                        select @POSTDATE = POSTDATE from dbo.FINANCIALTRANSACTION where ID = @REVENUEID;

                    declare @REVENUESPLITID uniqueidentifier
                    select top 1 @REVENUESPLITID = REVENUESPLIT.ID from FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                    where REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID
                    and REVENUESPLIT.DELETEDON is null
                    and REVENUESPLIT.TYPECODE <> 1


                    declare @DISTRIBUTIONS table(
                        GLTRANSACTIONID uniqueidentifier,
                        PLEDGEID 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,PLEDGEID,ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, 
                                GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,ERRORMESSAGE,REVENUESPLITID,
                                BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, MAPPEDVALUES)
                    select 
                        newid(),
                        REVDISTRIBUTIONS.REVENUEBENEFITID,
                        REVDISTRIBUTIONS.PLEDGEID,
                        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 
                        T1.REVENUEBENEFITID as REVENUEBENEFITID, 
                        PLEDGEID,
                        dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(9, 1, 0, 0, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        tf.TRANSACTIONTYPECODE, 
                        case when BENEFITADJUSTMENT.ID is null then T1.REVENUEPOSTDATE        
                             else BENEFITADJUSTMENT.POSTDATE
                        end as POSTDATE,
                        case when BENEFITADJUSTMENT.ID is null then (case when T1.REVENUEDONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)        
                             else BENEFITADJUSTMENT.POSTSTATUSCODE
                        end as POSTSTATUSCODE,
                        tf.ACCOUNTSTRING, 
                        tf.PROJECTCODE as PROJECT, 
                        T1.amount as amount, 
                        dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (T1.PAYMENTID, 'liability backout' ,'Pledge') as REFERENCE,
                        tf.ERRORMESSAGE,
                        t1.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                        tf.ACCOUNTID as ACCOUNTID,
                        T1.PAYMENTID as REVENUESPLITID,
                        T1.BASECURRENCYID,
                        T1.TRANSACTIONAMOUNT,
                        T1.TRANSACTIONCURRENCYID,
                        T1.BASEEXCHANGERATEID,
                        T1.ORGANIZATIONAMOUNT,
                        T1.ORGANIZATIONEXCHANGERATEID,
                        tf.MAPPEDVALUES
                    from 
                        ( 
                            select 
                                ROW_NUMBER() over (partition by pledgeid, REVENUEBENEFIT.ID order by INSTALLMENTSPLITPAYMENT.ID)as rownumber, 
                                INSTALLMENTSPLITPAYMENT.PLEDGEID,
                                INSTALLMENTSPLITPAYMENT.PAYMENTID,
                                REVENUEBENEFIT.ID as REVENUEBENEFITID,
                                BENEFITLINEITEM.QUANTITY * BENEFITLINEITEM.UNITVALUE as AMOUNT, --Why is this not TOTALVALUE?

                                REVENUE.ID as REVENUEID,
                                REVENUE.TYPECODE as TRANSACTIONTYPECODE,
                                REVENUESPLIT_EXT.TYPECODE as TYPECODE,
                                REVENUESPLIT_EXT.DESIGNATIONID as DESIGNATIONID,
                                REVENUE.POSTDATE as REVENUEPOSTDATE,
                                case REVENUE.POSTSTATUSCODE when 3 then 1 else 0 end as REVENUEDONOTPOST,
                                isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID,
                                REVENUESPLIT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT,
                                REVENUE.TRANSACTIONCURRENCYID,
                                REVENUE.BASEEXCHANGERATEID,
                                REVENUESPLIT.ORGAMOUNT as ORGANIZATIONAMOUNT,
                                REVENUE.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
                            from 
                                dbo.INSTALLMENTSPLITPAYMENT
                                join FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID and dbo.UFN_PLEDGE_GETBALANCE(INSTALLMENTSPLITPAYMENT.PLEDGEID) <= 0 
                                join REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                                join FINANCIALTRANSACTION REVENUE on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                                join REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID                            
                                join FINANCIALTRANSACTIONLINEITEM BENEFITLINEITEM on BENEFITLINEITEM.FINANCIALTRANSACTIONID = INSTALLMENTSPLITPAYMENT.PLEDGEID
                                join dbo.REVENUEBENEFIT_EXT REVENUEBENEFIT on BENEFITLINEITEM.ID = REVENUEBENEFIT.ID
                                join dbo.PDACCOUNTSYSTEM on REVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                                join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                            where 
                                REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID
                                and dbo.UFN_PLEDGE_GETBALANCE(INSTALLMENTSPLITPAYMENT.PLEDGEID) <= 0
                                and REVENUEBENEFIT.SENDBENEFIT = 1
                                and REVENUESPLIT.DELETEDON is null
                                and REVENUE.DELETEDON is null
                                and REVENUESPLIT.TYPECODE <> 1
                                and BENEFITLINEITEM.DELETEDON is null
                                and BENEFITLINEITEM.TYPECODE <> 1
                        ) T1    
                        left join dbo.REVENUEPOSTED  on REVENUEPOSTED.ID = T1.REVENUEID    
                        left join dbo.BENEFITADJUSTMENT  on T1.PLEDGEID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE <> 0                
                        cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(T1.PAYMENTID, T1.TRANSACTIONTYPECODE,T1.TYPECODE,87, 9, T1.PLEDGEID) as tf                    
                    where  
                        rownumber = 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)

                    -- Insert rows in GLTRANSACTION table

                    insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, 
                                    JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,GLACCOUNTID,
                                    BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID)
                    select
                        GLTRANSACTIONID,
                        case TRANSACTIONTYPECODE when 0 then 1 else 0 end, 
                        ACCOUNT,
                        AMOUNT,
                        PROJECT,
                        REFERENCE,
                        POSTDATE,
                        @JOURNAL,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        ACCOUNTID,
                        BASECURRENCYID,
                        TRANSACTIONAMOUNT,
                        TRANSACTIONCURRENCYID,
                        BASEEXCHANGERATEID,
                        ORGANIZATIONAMOUNT,
                        ORGANIZATIONEXCHANGERATEID
                    from 
                        @DISTRIBUTIONS;

                    -- Insert rows in BENEFITGLDISTRIBUTION table

                    insert into dbo.BENEFITGLDISTRIBUTION
                        (ID, REVENUEBENEFITID,REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, 
                            GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,FULLYPAIDSTATUS,
                            BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID,BENEFITTYPECODE)
                    select
                        newid(),
                        REVENUEBENEFITID,
                        PLEDGEID,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        PROJECT,
                        REFERENCE, 
                        AMOUNT,
                        ACCOUNT,
                        case TRANSACTIONTYPECODE when 0 then 1 else 0 end, 
                        GLTRANSACTIONID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        2,
                        BASECURRENCYID,
                        TRANSACTIONAMOUNT,
                        TRANSACTIONCURRENCYID,
                        BASEEXCHANGERATEID,
                        ORGANIZATIONAMOUNT,
                        ORGANIZATIONEXCHANGERATEID,
                        3
                    from 
                        @DISTRIBUTIONS;                

                    update 
                        dbo.REVENUEBENEFIT_EXT 
                    set 
                        SENDBENEFIT = 3 
                    where 
                        REVENUEBENEFIT_EXT.ID in 
                        (
                            select 
                                REVENUEBENEFIT_EXT.ID 
                            from 
                                dbo.JOURNALENTRY_EXT
                                join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = JOURNALENTRY_EXT.REVENUEID
                                join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID                            
                                join dbo.REVENUEBENEFIT_EXT on REVENUEBENEFIT_EXT.ID = REVENUESPLIT.ID                        
                                join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = FINANCIALTRANSACTION.ID                          
                            where
                                JOURNALENTRY_EXT.TABLENAMECODE = 5
                                and REVENUEBENEFIT_EXT.SENDBENEFIT = 1 
                                and dbo.UFN_PLEDGE_GETBALANCE(INSTALLMENTSPLITPAYMENT.PLEDGEID) <= 0 
                                and JOURNALENTRY_EXT.FULLYPAIDSTATUSCODE = 1 
                                and REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID
                                and REVENUESPLIT.DELETEDON is null
                                and FINANCIALTRANSACTION.DELETEDON is null
                                and REVENUESPLIT.TYPECODE <> 1
                        )                
            end