USP_SAVE_BENEFITGLDISTRIBUTION_COMMITTED

CREATE procedure dbo.USP_SAVE_BENEFITGLDISTRIBUTION_COMMITTED
            (
                @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_COMMITTED_CUSTOMIZE @REVENUEID, @CHANGEAGENTID, @CHANGEDATE

                if @CUSTOMIZED = 0
                begin
                    declare @REVENUESPLITID uniqueidentifier
                    select top 1 @REVENUESPLITID = ID from REVENUESPLIT    where REVENUEID = @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(9, REVENUE.TRANSACTIONTYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.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 (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end)        -- same reason as above. 

                             else BENEFITADJUSTMENT.POSTSTATUSCODE
                        end as POSTSTATUSCODE,
                        tf.ACCOUNTSTRING, 
                        tf.PROJECTCODE as PROJECT, 
                        REVENUEBENEFIT.TOTALVALUE as AMOUNT, 
                        dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Benefit Expense' ,'Pledge') as REFERENCE,
                        tf.ERRORMESSAGE,
                        REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                        tf.ACCOUNTID as ACCOUNTID,
                        REVENUESPLIT.ID as REVENUESPLITID,
                        REVENUEBENEFIT.BASECURRENCYID,
                        REVENUEBENEFIT.TRANSACTIONTOTALVALUE as TRANSACTIONAMOUNT,
                        REVENUEBENEFIT.TRANSACTIONCURRENCYID,
                        REVENUEBENEFIT.BASEEXCHANGERATEID,
                        REVENUEBENEFIT.ORGANIZATIONTOTALVALUE as ORGANIZATIONAMOUNT,
                        REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID,
                        tf.MAPPEDVALUES
                    from dbo.REVENUE 
                    inner join dbo.REVENUEBENEFIT  on REVENUE.ID = REVENUEBENEFIT.REVENUEID                     
                    left join dbo.REVENUEPOSTED  on REVENUEPOSTED.ID = REVENUE.ID 
                    inner join dbo.REVENUESPLIT  on REVENUE.ID = REVENUESPLIT.REVENUEID and REVENUESPLIT.ID = @REVENUESPLITID 
                    left join dbo.BENEFITADJUSTMENT  on REVENUE.ID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE <> 0
                    cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE,REVENUESPLIT.TYPECODE,86, 9, REVENUEBENEFIT.BENEFITID) as tf                    
                    where    REVENUE.ID = @REVENUEID and REVENUE.TRANSACTIONTYPECODE = 1 and REVENUEBENEFIT.SENDBENEFIT = 0                                            
                            ) 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,
                        TRANSACTIONTYPECODE,
                        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,
                            BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID,BENEFITTYPECODE)
                    select
                        newid(),
                        REVENUEBENEFITID,
                        @REVENUEID,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        PROJECT,
                        REFERENCE, 
                        AMOUNT,
                        ACCOUNT,
                        TRANSACTIONTYPECODE,
                        GLTRANSACTIONID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        BASECURRENCYID,
                        TRANSACTIONAMOUNT,
                        TRANSACTIONCURRENCYID,
                        BASEEXCHANGERATEID,
                        ORGANIZATIONAMOUNT,
                        ORGANIZATIONEXCHANGERATEID,
                        1
                    from 
                        @DISTRIBUTIONS;
            end