USP_SAVE_BENEFITGLDISTRIBUTION_SALESORDER

CREATE procedure dbo.USP_SAVE_BENEFITGLDISTRIBUTION_SALESORDER
(
    @REVENUEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as begin
    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);

    select top(1) @JOURNAL = CUSTOMJOURNAL
    from dbo.POSTTOGLPROCESSCUSTOMJOURNAL;

    if @JOURNAL = ''
        set @JOURNAL = 'Blackbaud Enterprise';

    declare @REVENUESPLITID uniqueidentifier;
    select top 1 @REVENUESPLITID = ID
    from dbo.FINANCIALTRANSACTIONLINEITEM
    where FINANCIALTRANSACTIONID = @REVENUEID and DELETEDON is null and TYPECODE <> 1;  -- Reversal


    --RSC - 05/02/12 - WI 206025.

    --Currently, the only code that calls this procedure is USP_SALESORDER_ADDREVENUE(), which sets the parent record's journal reference

    --in the REVENUEREFERENCE table. The journal reference of this benefit distribution should be the parent record's reference + "-Benefit Expense". 

    declare @REFERENCESUFFIX as nvarchar(16) = '-Benefit Expense';
    declare @JOURNALREFERENCE nvarchar(255) = dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (@REVENUESPLITID, @REFERENCESUFFIX,'Payment');

    if @JOURNALREFERENCE not like '%' + @REFERENCESUFFIX + '%' begin
        set @JOURNALREFERENCE += @REFERENCESUFFIX;
    end                

    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, 
            --REVENUEGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID,

            dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(9, FT.TYPECODE, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
            tf.TRANSACTIONTYPECODE, 
            case when BENEFITADJUSTMENT.ID is null then 
                cast(FT.POSTDATE as datetime)
            else 
                BENEFITADJUSTMENT.POSTDATE
            end as POSTDATE,
            case when BENEFITADJUSTMENT.ID is null then 
                (case when FT.POSTSTATUSCODE = 3 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, 
            REVENUEBENEFIT.TOTALVALUE as amount, 
            @JOURNALREFERENCE as REFERENCE,
            tf.ERRORMESSAGE,
            FT.TYPECODE as REVENUETRANSACTIONTYPECODE,
            tf.ACCOUNTID as ACCOUNTID,
            LI.ID as REVENUESPLITID,
            REVENUEBENEFIT.BASECURRENCYID,
            REVENUEBENEFIT.TRANSACTIONTOTALVALUE as TRANSACTIONAMOUNT,
            REVENUEBENEFIT.TRANSACTIONCURRENCYID,
            REVENUEBENEFIT.BASEEXCHANGERATEID,
            REVENUEBENEFIT.ORGANIZATIONTOTALVALUE as ORGANIZATIONAMOUNT,
            REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID,
            tf.MAPPEDVALUES
        from 
            dbo.FINANCIALTRANSACTION as FT
            inner join dbo.REVENUEBENEFIT on FT.ID = REVENUEBENEFIT.REVENUEID
            left join dbo.REVENUEPOSTED  on REVENUEPOSTED.ID = FT.ID 
            inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on FT.ID = LI.FINANCIALTRANSACTIONID and LI.ID = @REVENUESPLITID
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = LI.ID
            left join dbo.BENEFITADJUSTMENT  on FT.ID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE <> 0
            cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(LI.ID, 0,REVENUESPLIT_EXT.TYPECODE,86, 9, REVENUEBENEFIT.BENEFITID) as tf
            --inner join dbo.REVENUEGLDISTRIBUTION on REVENUE.ID = REVENUEGLDISTRIBUTION.REVENUEID and REVENUEGLDISTRIBUTION.ACCOUNT = tf.ACCOUNTSTRING

        where    
            FT.ID = @REVENUEID 
            and REVENUEBENEFIT.SENDBENEFIT = 0
            and FT.DELETEDON is null
            and LI.DELETEDON is null
            and LI.TYPECODE <> 1  -- Reversal

    ) as REVDISTRIBUTIONS

    if @@rowcount > 0 begin    
        declare @ERRORMESSAGE nvarchar(max);
        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
        begin
            declare @ORDERID uniqueidentifier
            select @ORDERID = SALESORDER.ID from dbo.SALESORDER with (nolock) where SALESORDER.REVENUEID = @REVENUEID
            --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
                @ORDERID
                ,(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 = @ORDERID and DELETED = 0)
        end

        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)
        select
            newid(),
            REVENUEBENEFITID,
            @REVENUEID,
            GLPAYMENTMETHODREVENUETYPEMAPPINGID,
            PROJECT,
            REFERENCE, 
            AMOUNT,
            ACCOUNT,
            TRANSACTIONTYPECODE,
            GLTRANSACTIONID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE,
            BASECURRENCYID,
            TRANSACTIONAMOUNT,
            TRANSACTIONCURRENCYID,
            BASEEXCHANGERATEID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID
        from 
            @DISTRIBUTIONS;
    end
end