USP_REFUND_CREATEGLDISTRIBUTION

CREATE procedure dbo.USP_REFUND_CREATEGLDISTRIBUTION
(
    @CREDITID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null,
    @CREDITPAYMENTID uniqueidentifier = 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 @POSTDATE datetime;
    select @POSTDATE = POSTDATE
    from dbo.FINANCIALTRANSACTION where ID = @CREDITID;

    --first, go get the distributions that we need to reverse...


    declare @SOURCESPLITS table (
        REVENUESPLITID uniqueidentifier,
        FINANCIALTRANSACTIONID uniqueidentifier,
        [TYPE] nvarchar(100),
        REFUNDPAYMENTTOTAL money,
        REFUNDSPLITAMOUNT money,
        CREDITAMOUNT money,
        CREDITPAYMENTID uniqueidentifier,
        TYPECODE tinyint,
        PAYMENTMETHODCODE tinyint,
        CREDITITEMID uniqueidentifier
    );

    insert into @SOURCESPLITS
    select
        REVENUESPLITID,
        FINANCIALTRANSACTIONID,
        [TYPE],
        REFUNDPAYMENTTOTAL,
        REFUNDSPLITAMOUNT,
        CREDITAMOUNT,
        CREDITPAYMENTID,
        TYPECODE,
        PAYMENTMETHODCODE,
        CREDITITEMID
    from UFN_REFUND_GETITEMAMOUNTSFORGL(@CREDITID);


    -- Prorate the credit payments across the items.  TODO: a top-down distribution would probably be cleaner.

    declare @PRORATEDSPLITS table (
        AMOUNT money,
        ID uniqueidentifier,
        CREDITITEMID uniqueidentifier,
        CREDITPAYMENTID uniqueidentifier
    );

    declare @CURRENTCREDITPAYMENTID uniqueidentifier, @CREDITAMOUNT money, @REFUNDPAYMENTTOTAL money;
    declare cp_cur cursor local fast_forward for (select distinct CREDITPAYMENTID, CREDITAMOUNT, REFUNDPAYMENTTOTAL from @SOURCESPLITS);

    open cp_cur;
    fetch next from cp_cur into @CURRENTCREDITPAYMENTID, @CREDITAMOUNT, @REFUNDPAYMENTTOTAL;
    while @@FETCH_STATUS = 0
    begin
        declare @AMOUNTSTOPRORATE xml = (
            select
                REVENUESPLITID as [@ID], 
                REFUNDSPLITAMOUNT as [@AMOUNT],
                CREDITITEMID as [@CREDITITEMID]
            from @SOURCESPLITS
            where CREDITPAYMENTID = @CURRENTCREDITPAYMENTID
            for xml path('ITEM'), type, elements, root('AMOUNTSTOPRORATE'), binary base64
        );

        -- Changed to a procedure due to a bug in SQL Server's optimization for insert-select from XML.

        -- The bug does not affect "select into", but that's DDL, so the whole call chain needs to be converted to procedures.

        insert into @PRORATEDSPLITS
        exec dbo.USP_SPLITS_GETPRORATEDSPLITS @CREDITAMOUNT, @REFUNDPAYMENTTOTAL, @AMOUNTSTOPRORATE, @CURRENTCREDITPAYMENTID;

        fetch next from cp_cur into @CURRENTCREDITPAYMENTID, @CREDITAMOUNT, @REFUNDPAYMENTTOTAL;
        --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

        close cp_cur;
        deallocate cp_cur;
    end


    declare @DISTRIBUTIONS table(
        REVENUESPLITID uniqueidentifier,
        REVENUEID uniqueidentifier,
        GLTRANSACTIONID uniqueidentifier,
        ACCOUNT nvarchar(100),
        AMOUNT money,
        PROJECT nvarchar(100),
        TRANSACTIONTYPECODE tinyint,
        REFERENCE nvarchar(255),
        POSTDATE datetime,
        GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
        ACCOUNTID uniqueidentifier,
        CREDITPAYMENTID uniqueidentifier,
        ERRORMESSAGE nvarchar(255),
        MAPPEDVALUES xml,
        CREDITITEMID uniqueidentifier
    );

    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    declare @DEFAULTACCOUNT nvarchar(100);
    declare @DEFAULTACCOUNTID uniqueidentifier;

    select
        @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID,
        @DEFAULTACCOUNTID = GLACCOUNT.ID,
        @DEFAULTACCOUNT = GLACCOUNT.ACCOUNTNUMBER
    from
        dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM() as PDACCOUNTSYSTEM
    left join
        dbo.GLACCOUNT on GLACCOUNT.ID = PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID;

    -- Generate GL distributions for the discount record.

    insert into @DISTRIBUTIONS(GLTRANSACTIONID, REVENUESPLITID, REVENUEID, ACCOUNT, AMOUNT, TRANSACTIONTYPECODE, REFERENCE, POSTDATE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, ACCOUNTID, CREDITPAYMENTID, CREDITITEMID)
    select
        newid()
        ,SPLITS.REVENUESPLITID
        ,LI.FINANCIALTRANSACTIONID [REVENUEID]
        ,case when JE.TRANSACTIONTYPECODE = 0 then
            dbo.UFN_REFUND_GETCREDITACCOUNT(JE.GLACCOUNTID,SPLITS.CREDITPAYMENTID,SPLITS.REVENUESPLITID)
        else
            A.ACCOUNTNUMBER
        end
        ,PRORATEDSPLITS.AMOUNT
        ,case when JE.TRANSACTIONTYPECODE = 0 then 1 else 0 end
        ,'Refund-' + SPLITS.[TYPE] as REFERENCE
        ,@POSTDATE
        ,JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID
        ,case when JE.TRANSACTIONTYPECODE = 0 then
            null
        else
            JE.GLACCOUNTID
        end
        ,SPLITS.CREDITPAYMENTID
        ,SPLITS.CREDITITEMID
    from @SOURCESPLITS as SPLITS
    inner join @PRORATEDSPLITS as PRORATEDSPLITS on PRORATEDSPLITS.CREDITPAYMENTID = SPLITS.CREDITPAYMENTID and PRORATEDSPLITS.CREDITITEMID = SPLITS.CREDITITEMID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = SPLITS.REVENUESPLITID
    inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
    inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
    inner join dbo.GLACCOUNT A on A.ID = JE.GLACCOUNTID
    where (@CREDITPAYMENTID is null or SPLITS.CREDITPAYMENTID = @CREDITPAYMENTID)
        and LI.TYPECODE != 1 and LI.DELETEDON is null
        and JEX.TABLENAMECODE = 1

    update @DISTRIBUTIONS set ACCOUNTID = (select GLACCOUNT.ID from dbo.GLACCOUNT where GLACCOUNT.ACCOUNTNUMBER = ACCOUNT and GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
    where ACCOUNTID is null;

    declare @ERRORMESSAGE nvarchar(255);
    if exists(select 1 from @DISTRIBUTIONS where ACCOUNTID is null)
    begin
        set @ERRORMESSAGE = 'An account code has not been mapped for one or more payment methods.';

        declare @MAPPEDVALUES xml;
        set @MAPPEDVALUES = (select tv1.*, @ERRORMESSAGE as ERRORMESSAGE, 1 as ERRORCODE from dbo.UFN_GETGENERICACCOUNTMAPPINGSTRUCTURE(@PDACCOUNTSYSTEMID, 2, null, null, null, null) tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)

        if @DEFAULTACCOUNTID is null
            raiserror(@ERRORMESSAGE, 13, 1);
        else
        begin
            update @DISTRIBUTIONS set
                ACCOUNT = @DEFAULTACCOUNT
                ,ACCOUNTID = @DEFAULTACCOUNTID
                ,ERRORMESSAGE = @ERRORMESSAGE
                ,MAPPEDVALUES = convert(nvarchar(max), @MAPPEDVALUES)
            where ACCOUNTID is null;

            insert into dbo.GLACCOUNTMAPPINGERROR ([TRANSACTIONID],[TRANSACTIONTYPECODE],[ERRORMESSAGE], [MAPPEDVALUES],[ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
            select distinct
                @CREDITID
                ,103
                ,D.ERRORMESSAGE
                ,convert(nvarchar(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 = @CREDITID and DELETED = 0)
        end
    end

    set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
    if @ERRORMESSAGE <> ''
        raiserror(@ERRORMESSAGE, 13, 1);

    declare @JOURNAL nvarchar(50);
    set @JOURNAL = 'Blackbaud Enterprise';

    declare @CURRENCYID uniqueidentifier;
    select @CURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;

    insert into dbo.JOURNALENTRY (
        ID
        ,FINANCIALTRANSACTIONLINEITEMID
        ,TRANSACTIONTYPECODE
        ,CLASSCODE
        ,SEQUENCE
        ,TRANSACTIONAMOUNT
        ,COMMENT
        ,POSTDATE
        ,GLACCOUNTID
        ,SUBLEDGERTYPECODE
        ,BASEAMOUNT
        ,ORGAMOUNT
        ,TYPECODE
        ,TRANSACTIONCURRENCYID
        ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        GLTRANSACTIONID
        ,CREDITITEMID
        ,TRANSACTIONTYPECODE
        ,0
        ,row_number() over (partition by CREDITITEMID order by AMOUNT)
        ,AMOUNT
        ,REFERENCE
        ,POSTDATE
        ,ACCOUNTID
        ,TRANSACTIONTYPECODE
        ,AMOUNT
        ,AMOUNT
        ,0
        ,@CURRENCYID
        ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
    from @DISTRIBUTIONS;

    insert into dbo.JOURNALENTRY_EXT(
        ID
        ,TABLENAMECODE
        ,OUTDATED
        ,CREDITITEMID
        ,CREDITPAYMENTID
        ,JOURNAL
        ,ACCOUNT
        ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        GLTRANSACTIONID
        ,6
        ,0
        ,CREDITITEMID
        ,CREDITPAYMENTID
        ,@JOURNAL
        ,ACCOUNT
        ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
    from @DISTRIBUTIONS;

    declare @REVENUESPLITIDS UDT_GENERICID;

    insert into @REVENUESPLITIDS (ID)
    select distinct REVENUESPLITID from @DISTRIBUTIONS;

    exec dbo.USP_REFUND_ADDBENEFITREVERSALS @REVENUESPLITIDS, @CREDITID, @POSTDATE, @CHANGEAGENTID, @CHANGEDATE;

end