USP_SAVE_BENEFITGLDISTRIBUTION_TOTAL

CREATE procedure dbo.USP_SAVE_BENEFITGLDISTRIBUTION_TOTAL
            (
                @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 @REVENUESPLITID uniqueidentifier
                select top 1 @REVENUESPLITID = ID from REVENUESPLIT    where REVENUEID = @REVENUEID                

                --declare @REVENUEBENEFITID uniqueidentifier

                --select top 1 @REVENUEBENEFITID = ID from REVENUEBENEFIT    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,
                    MAPPEDVALUES xml
                );

                -- Generate GL distributions for the revenue record.

                insert into @DISTRIBUTIONS(GLTRANSACTIONID, REVENUEBENEFITID,ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, 
                            GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,ERRORMESSAGE,REVENUESPLITID, 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.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.QUANTITY * REVENUEBENEFIT.UNITVALUE as amount, 
                    dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Benefit' ,'Pledge') as REFERENCE,
                    tf.ERRORMESSAGE,
                    REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
                    tf.ACCOUNTID as ACCOUNTID,
                    REVENUESPLIT.ID as REVENUESPLITID,
                    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,85, 9, REVENUESPLIT.DESIGNATIONID) as tf                    
                where    REVENUE.ID = @REVENUEID and REVENUE.TRANSACTIONTYPECODE = 1 and REVENUEBENEFIT.SENDBENEFIT = 1 --in (0,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)

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

        DECLARE @ADJUSTMENTPOSTSTATUSCODE tinyint
        SET @ADJUSTMENTPOSTSTATUSCODE = 1
        if EXISTS (SELECT ID FROM BENEFITADJUSTMENT WHERE REVENUEID = @REVENUEID)
          BEGIN
             SELECT @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE FROM BENEFITADJUSTMENT WHERE REVENUEID = @REVENUEID
          END

                -- Insert rows in GLTRANSACTION table

                insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, 
                                JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,GLACCOUNTID, POSTSTATUSCODE)
                select
                    GLTRANSACTIONID,
                    TRANSACTIONTYPECODE,
                    ACCOUNT,
                    AMOUNT,
                    PROJECT,
                    REFERENCE,
                    POSTDATE,
                    @JOURNAL,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE,
                    ACCOUNTID,
          @ADJUSTMENTPOSTSTATUSCODE
                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)
                select
                    newid(),
                    REVENUEBENEFITID,
                    @REVENUEID,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                    PROJECT,
                    REFERENCE, 
                    AMOUNT,
                    ACCOUNT,
                    TRANSACTIONTYPECODE,
                    GLTRANSACTIONID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE
                from 
                    @DISTRIBUTIONS;