USP_SAVE_BENEFITGLDISTRIBUTION_LINEITEMS

CREATE procedure dbo.USP_SAVE_BENEFITGLDISTRIBUTION_LINEITEMS
(
    @LINEITEMS UDT_GENERICID readonly,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
begin

    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_BENEFITGLDISTRIBUTION_LINEITEMS_CUSTOMIZE @LINEITEMS,@CHANGEAGENTID,@CHANGEDATE

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

        declare @DISTRIBUTIONS table(
            ID uniqueidentifier,
            FINANCIALTRANSACTIONID uniqueidentifier,
            FINANCIALTRANSACTIONLINEITEMID uniqueidentifier,
            TRANSACTIONAMOUNT money,
            PROJECT nvarchar(100),
            REFERENCE nvarchar(255),
            TRANSACTIONTYPECODE tinyint,
            POSTDATE datetime,
            GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
            ACCOUNTID uniqueidentifier,
            ERRORMESSAGE nvarchar(max),
            REVENUESPLITID uniqueidentifier,
            TRANSACTIONCURRENCYID uniqueidentifier,
            BASECURRENCYID uniqueidentifier,
            BASEAMOUNT money,
            BASEEXCHANGERATEID uniqueidentifier,
            ORGANIZATIONAMOUNT money,
            ORGANIZATIONEXCHANGERATEID uniqueidentifier,
            MAPPEDVALUES xml,
            FULLYPAIDSTATUSCODE tinyint,
            BENEFITTYPECODE tinyint
        );

        -- Generate GL distributions for the revenue record.

        insert into @DISTRIBUTIONS(ID, FINANCIALTRANSACTIONID,FINANCIALTRANSACTIONLINEITEMID,TRANSACTIONAMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, POSTDATE, 
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,ERRORMESSAGE,REVENUESPLITID,TRANSACTIONCURRENCYID,BASECURRENCYID, BASEAMOUNT, 
                    BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, MAPPEDVALUES, FULLYPAIDSTATUSCODE, BENEFITTYPECODE)
        select
            NEWID(),
            FT.ID,
            LI.ID,
            LI.TRANSACTIONAMOUNT,
            tf.PROJECTCODE,
            case FT.TYPECODE
                when 0 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (RSX.ID, 'Benefit expense' ,'Payment')
                when 1 then
                    case RBX.BENEFITTYPECODE
                        when 1 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (RSX.ID, 'Benefit expense' ,'Pledge')
                        when 2 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (RSX.ID, 'Benefit liability' ,'Pledge')
                        when 3 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (RSX.ID, 'Liability backout' ,'Pledge')
                    end
                when 5 then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (RSX.ID, 'Benefit expense' ,'Order')
            end as REFERENCE,
            case RBX.BENEFITTYPECODE --switch debit and credit for backouts

                when 3 then
                    case tf.TRANSACTIONTYPECODE
                        when 1 then 0
                        else 1
                    end
                else
                    tf.TRANSACTIONTYPECODE
            end as TRANSACTIONTYPECODE,
            LI.POSTDATE,
            dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID_FORBENEFIT(
                isnull(RPM.PAYMENTMETHODCODE, 9), 
                FT.TYPECODE,
                RSX.TYPECODE,
                RSX.APPLICATIONCODE,
                tf.TRANSACTIONTYPECODE
            ) as GLPAYMENTMETHODREVENUETYPEMAPPINGID, 
            tf.ACCOUNTID,
            tf.ERRORMESSAGE,
            RSX.ID,
            FT.TRANSACTIONCURRENCYID,
            V.BASECURRENCYID,
            LI.BASEAMOUNT,
            FT.BASEEXCHANGERATEID,
            LI.ORGAMOUNT,
            FT.ORGEXCHANGERATEID,
            tf.MAPPEDVALUES,
            RBX.SENDBENEFIT,
            RBX.BENEFITTYPECODE
        from
            @LINEITEMS SOURCELI
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = SOURCELI.ID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
            inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V on V.FINANCIALTRANSACTIONID = FT.ID
            inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = LI.ID
            left join dbo.REVENUEPAYMENTMETHOD RPM on FT.ID = RPM.REVENUEID
            cross apply (
                select top 1 
                    RSX.ID,
                    RSX.TYPECODE,
                    RSX.APPLICATIONCODE
                from dbo.REVENUESPLIT_EXT RSX
                inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RSX.ID = RS.ID
                where RS.FINANCIALTRANSACTIONID = FT.ID  and RS.DELETEDON is null
                --Order by: Giving preference to membership installment plan splits if they exist on transaction

                order by case when RSX.APPLICATIONCODE = 19 then 0 else 1 end asc
            ) as RSX
            cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(RSX.ID, FT.TYPECODE,RSX.TYPECODE,
                case RBX.BENEFITTYPECODE when 1 then 86 else 87 end,isnull(RPM.PAYMENTMETHODCODE, 9), RBX.BENEFITID) as tf
            where LI.DELETEDON is null

        if exists (select 1 from @DISTRIBUTIONS)
        begin
            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
                    D.FINANCIALTRANSACTIONID
                    ,(select TRANSACTIONTYPECODE from dbo.REVENUE where ID = D.FINANCIALTRANSACTIONID)
                    ,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 = D.FINANCIALTRANSACTIONID 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 into dbo.JOURNALENTRY
                (ID,FINANCIALTRANSACTIONLINEITEMID,TRANSACTIONTYPECODE,SUBLEDGERTYPECODE,TRANSACTIONAMOUNT,
                BASEAMOUNT,ORGAMOUNT,COMMENT,POSTDATE,GLACCOUNTID,SEQUENCE,TYPECODE,TRANSACTIONCURRENCYID,
                ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            select 
                ID,FINANCIALTRANSACTIONLINEITEMID,TRANSACTIONTYPECODE,TRANSACTIONTYPECODE,TRANSACTIONAMOUNT,
                BASEAMOUNT,ORGANIZATIONAMOUNT,REFERENCE,POSTDATE,ACCOUNTID,row_number() over (order by ID),0,TRANSACTIONCURRENCYID,
                @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from
                @DISTRIBUTIONS;

            insert into JOURNALENTRY_EXT
                (ID,DISTRIBUTIONTABLEID,PROJECT,ACCOUNT,JOURNAL,PRECALCORGANIZATIONEXCHANGERATEID,
                PRECALCBASEEXCHANGERATEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,TABLENAMECODE,OUTDATED,
                LOGICALREVENUEID,FULLYPAIDSTATUSCODE,BENEFITTYPECODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            select
                D.ID,NEWID(),coalesce(PROJECT, ''),GL.ACCOUNTNUMBER,@JOURNAL,ORGANIZATIONEXCHANGERATEID,
                BASEEXCHANGERATEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,5,0,
                D.FINANCIALTRANSACTIONID,FULLYPAIDSTATUSCODE,BENEFITTYPECODE,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from 
                @DISTRIBUTIONS D
                inner join dbo.GLACCOUNT GL on GL.ID = D.ACCOUNTID
        end
    end
end