USP_REFUND_ADDBENEFITREVERSALS

CREATE procedure dbo.USP_REFUND_ADDBENEFITREVERSALS
(
    @REVENUESPLITIDS dbo.UDT_GENERICID readonly,
    @CREDITID uniqueidentifier,
    @POSTDATE datetime,
    @CHANGEAGENTID uniqueidentifier,
    @CHANGEDATE datetime
)
as
    set nocount on;

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate();

    declare @BENEFITIDS UDT_GENERICID;

    insert into @BENEFITIDS (
        ID
    )
    select distinct
        L.ID
    from
        dbo.FINANCIALTRANSACTIONLINEITEM L 
    left outer join
        dbo.REVENUEBENEFIT_EXT RE on RE.ID = L.ID 
    inner join
        dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
    inner join
        dbo.JOURNALENTRY_EXT X on J.ID = X.ID 
    where
        X.TABLENAMECODE = 5  -- BENEFITGLDISTRIBUTION

        and X.OUTDATED = 0
        and X.BENEFITTYPECODE = 1  -- Expense

        and (
            RE.REVENUESPLITID in (
                select FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID 
                from dbo.FINANCIALTRANSACTIONLINEITEM 
                    left join dbo.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.ID = FINANCIALTRANSACTIONLINEITEM.ID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @CREDITID
                    and CREDITITEMMEMBERSHIP.WILLBECANCELLED = 1
            )
            or (
                -- This refunds memberships and event registrations added through the back office.

                -- These benefits currently don't have a link to the membership or registration that created

                -- them so this code tries to refund any benefit from the payment.  This needs to be changed

                -- because of the potential of refunding multiple benefits if a payment is applied to multiple

                -- items, each one with their own benefits.

                RE.REVENUESPLITID is null and L.ID in (
                    select
                        ALLASSOCIATEDITEMSFROMREFUNDEDTRANSACTION.ID
                    from
                        dbo.FINANCIALTRANSACTIONLINEITEM as CREDITLINEITEMS
                    inner join
                        dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLINEITEMS on REFUNDEDLINEITEMS.ID = CREDITLINEITEMS.SOURCELINEITEMID
                    inner join
                        dbo.FINANCIALTRANSACTIONLINEITEM as ALLASSOCIATEDITEMSFROMREFUNDEDTRANSACTION on ALLASSOCIATEDITEMSFROMREFUNDEDTRANSACTION.FINANCIALTRANSACTIONID = REFUNDEDLINEITEMS.FINANCIALTRANSACTIONID and ALLASSOCIATEDITEMSFROMREFUNDEDTRANSACTION.TYPECODE = 3
                    inner join
                        dbo.REVENUESPLIT_EXT as REFUNDEDLINEITEMSPLITEXTENSION on REFUNDEDLINEITEMSPLITEXTENSION.ID = REFUNDEDLINEITEMS.ID
                    left join
                        dbo.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.ID = CREDITLINEITEMS.ID
                    where
                        CREDITLINEITEMS.FINANCIALTRANSACTIONID = @CREDITID
                        and REFUNDEDLINEITEMSPLITEXTENSION.TYPECODE <> 18  -- Membership add-on

                        and CREDITITEMMEMBERSHIP.WILLBECANCELLED = 1
                )
            )
        )

    if exists(select top 1 ID from @BENEFITIDS) begin

        exec dbo.USP_FINANCIALTRANSACTIONLINEITEMS_REVERSE @CHANGEAGENTID, @BENEFITIDS, @POSTDATE, @CHANGEDATE, 0;

        declare @LIAID uniqueidentifier = newid();

        insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT
            (ID, ADJUSTMENTREASONCODEID, REASON, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, CONSTITUENTID)
        values
            (@LIAID, null, '', @CHANGEDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, null)

        update FINANCIALTRANSACTIONLINEITEM
        set FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @LIAID
        from dbo.FINANCIALTRANSACTIONLINEITEM LI
        inner join @BENEFITIDS B on B.ID = LI.REVERSEDLINEITEMID
        where LI.TYPECODE = 1
            and LI.DELETEDON is null

    end

    return 0;