USP_CREDIT_ADDMEMBERSHIPREFUND

CREATE procedure dbo.USP_CREDIT_ADDMEMBERSHIPREFUND
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEMBERSHIPID uniqueidentifier,
    @MEMBERSHIPTRANSACTIONID uniqueidentifier = null,
    @ITEMS xml = null,
    @REFUNDMETHODS xml = null,
    @COMMENT nvarchar(500) = '',
    @CREDITREASONCODEID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @TOTAL money = null,  -- Maximum refundable amount (i.e. total value of the membership transaction including add-ons)

    @TIMESTAMP bigint = null,
    @ADDRESSID uniqueidentifier = null
)
as
begin
    set nocount on;

    if @ID is null
        set @ID = newid();

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

    declare @CURRENTDATE datetime = getdate();
    declare @POSTSTATUSCODE tinyint;

    --Make sure nobody else has done a refund since the form was opened.

    if @TIMESTAMP <> coalesce((select TSLONG from dbo.MEMBERSHIPTRANSACTION where ID = @MEMBERSHIPTRANSACTIONID), -1) begin
        if @MEMBERSHIPTRANSACTIONID is null or @MEMBERSHIPTRANSACTIONID = '00000000-0000-0000-0000-000000000000'
            raiserror('ERR_TRANSACTIONID_MEMBERSHIPNOTSELECTED', 13, 1);
        else
            raiserror('ERR_ITEMS_ORDERREFUND_HASNEWERMEMBERSHIPTRANSACTIONS', 13, 1);

        return 1;
    end
    else begin
        update dbo.MEMBERSHIPTRANSACTION
        set DATECHANGED = getdate(), CHANGEDBYID = @CHANGEAGENTID
        where ID = @MEMBERSHIPTRANSACTIONID;

        select @POSTSTATUSCODE = LI.POSTSTATUSCODE
        from dbo.MEMBERSHIPTRANSACTION MT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = MT.REVENUESPLITID
        where MT.ID = @MEMBERSHIPTRANSACTIONID;
    end

    -- Get refund items from input parameter @ITEMS

    declare @CREDITITEMS table(
        ID uniqueidentifier,
        SALESORDERITEMID uniqueidentifier,
        PRICE money,
        ITEMDISCOUNTS money,  -- This represents promos to reverse, which is not included in the @ITEMS xml (determined later).

        AMOUNTTOREFUND money,  -- This will be the BASEAMOUNT on the refund line item.

        TYPECODE tinyint,
        [DESCRIPTION] nvarchar(255),
        ITEMID uniqueidentifier,
        REVENUESPLITID uniqueidentifier,
        CONTRIBUTEDREVENUE money,
        WILLBECANCELLED bit
    );

    insert into @CREDITITEMS
    select
        newid(),
        case
            when T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                then null
            else
                T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier')
        end SALESORDERITEMID,
        T.item.value('(@PRICE)[1]','money') PRICE,
        0 ITEMDISCOUNTS,
        T.item.value('(@AMOUNTTOREFUND)[1]','money') AMOUNTTOREFUND,
        T.item.value('(@TYPECODE)[1]','tinyint') TYPECODE,
        T.item.value('(@DESCRIPTION)[1]','nvarchar(100)') [DESCRIPTION],
        case
            when T.item.value('(@ITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                then null
            else
                T.item.value('(@ITEMID)[1]','uniqueidentifier')
        end ITEMID,
        case
            when T.item.value('(@REVENUESPLITID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                then null
            else
                T.item.value('(@REVENUESPLITID)[1]','uniqueidentifier')
        end REVENUESPLITID,
        0 CONTRIBUTEDREVENUE,
        T.item.value('(@WILLBECANCELLED)[1]', 'bit') WILLBECANCELLED
    from @ITEMS.nodes('/ITEMS/ITEM') T(item)
    where T.item.value('(@INCLUDE)[1]','bit') = 1;

    -- If the membership was sold through Sales, we may have discounts to reverse.

    -- This must be a sum, because tickets may be applied along with a normal promotion.

    update CREDITITEMS
    set
        ITEMDISCOUNTS = coalesce(PROMOS.AMOUNT, 0)
    from @CREDITITEMS CREDITITEMS
    inner join dbo.SALESORDERITEM MEMBERSHIPITEM on MEMBERSHIPITEM.ID = CREDITITEMS.SALESORDERITEMID
    cross apply (
        select sum(AMOUNT) AMOUNT
        from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
        where SALESORDERITEMID = MEMBERSHIPITEM.ID
    ) PROMOS
    where MEMBERSHIPITEM.PRICE = CREDITITEMS.AMOUNTTOREFUND + PROMOS.AMOUNT;


    /******** Validate refund items and refund payments *******/

    declare @REFUNDEDITEMTOTAL money = (select sum(AMOUNTTOREFUND) from @CREDITITEMS);

    -- Error if no items were selected

    if not exists (select 1 from @CREDITITEMS) begin
        raiserror('ERR_ITEMS_NONESELECTED', 13, 1);
        return 1;
    end

    -- Error if the quantity refunding for any item is greater than the quantity available

    -- -- (original quantity - already refunded quantity - quantity we're refunding right now)

    if exists (
        select 1
        from (
            select ITEMID, count(ID) NUMREFUNDING
            from @CREDITITEMS
            group by ITEMID
        ) CREDITITEMS_GROUPED
        inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.ID = CREDITITEMS_GROUPED.ITEMID
        outer apply (
            select coalesce(sum(PREEXISTINGREFUNDLI.QUANTITY),0) QUANTITY
            from dbo.FINANCIALTRANSACTIONLINEITEM PREEXISTINGREFUNDLI
            inner join dbo.FINANCIALTRANSACTION REFUND on REFUND.ID = PREEXISTINGREFUNDLI.FINANCIALTRANSACTIONID
            where PREEXISTINGREFUNDLI.SOURCELINEITEMID = MEMBERSHIPADDON.REVENUESPLITID
                and REFUND.TYPECODE = 23
        ) PREEXISTINGREFUNDS
        where MEMBERSHIPADDON.QUANTITY - PREEXISTINGREFUNDS.QUANTITY - CREDITITEMS_GROUPED.NUMREFUNDING < 0
    ) or exists (
        select 1
        from @CREDITITEMS CREDITITEMS
        inner join dbo.MEMBERSHIPTRANSACTION MT on MT.ID = CREDITITEMS.ITEMID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM MEMBERSHIPREFUNDLI on MEMBERSHIPREFUNDLI.SOURCELINEITEMID = MT.REVENUESPLITID
        inner join dbo.FINANCIALTRANSACTION REFUND on REFUND.ID = MEMBERSHIPREFUNDLI.FINANCIALTRANSACTIONID
        where REFUND.TYPECODE = 23
    )
    begin
        raiserror('ERR_ITEMS_EXCEEDSQUANTITYAVAILABLE', 13, 1);
        return 1;
    end

    -- Error if the amount refunding is less than zero for any of the items

    if exists (select 1 from @CREDITITEMS where AMOUNTTOREFUND < 0)
    begin
        raiserror('ERR_ITEMS_NEGATIVEAMOUNT', 13, 1);
        return 1;
    end

    -- Error if the payments do not add up to the refund total

    if coalesce((select sum(T.item.value('(@AMOUNTREFUNDING)[1]','money')) from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)),0) <> @REFUNDEDITEMTOTAL
    begin
        raiserror('ERR_REFUNDMETHODS_DIFFERENTFROMTOTALAMOUNT', 13, 1);
        return 1;
    end

    -- Error if the refund amount is greater than the total available to refund

    if @REFUNDEDITEMTOTAL > @TOTAL
    begin
        raiserror('ERR_REFUNDAMOUNT_EXCEEDSLIMIT', 13, 1);
        return 1;
    end

    -- Error if there are any later membership transactions

    if @MEMBERSHIPTRANSACTIONID <> (
        select top 1 ID
        from dbo.MEMBERSHIPTRANSACTION
        where MEMBERSHIPID = @MEMBERSHIPID
        order by DATEADDED desc
    )
    begin
        raiserror('ERR_ITEMS_MEMBERSHIPREFUND_HASNEWERMEMBERSHIPTRANSACTIONS', 13, 1);
        return 1;
    end

    -- Error if there are memberships with contributed revenue that are not being fully refunded

    if exists (
        select 1 from @CREDITITEMS MEMBERSHIPITEM
        inner join dbo.MEMBERSHIPTRANSACTION REFUNDINGTRANSACTION on REFUNDINGTRANSACTION.ID = MEMBERSHIPITEM.ITEMID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM MEMBERSHIPLI on MEMBERSHIPLI.ID = REFUNDINGTRANSACTION.REVENUESPLITID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDLI on CONTRIBUTEDLI.SOURCELINEITEMID = MEMBERSHIPLI.ID
        inner join dbo.REVENUESPLIT_EXT EXT
            on (EXT.ID = CONTRIBUTEDLI.ID and EXT.TYPECODE = 0 and EXT.APPLICATIONCODE = 0 and CONTRIBUTEDLI.TYPECODE = 0)  -- Standard donation line item whose source is the membership line item

        group by MEMBERSHIPLI.ID, MEMBERSHIPLI.BASEAMOUNT, MEMBERSHIPITEM.AMOUNTTOREFUND, MEMBERSHIPITEM.ITEMDISCOUNTS
        having MEMBERSHIPLI.BASEAMOUNT + sum(CONTRIBUTEDLI.BASEAMOUNT) <> MEMBERSHIPITEM.AMOUNTTOREFUND + MEMBERSHIPITEM.ITEMDISCOUNTS
    )
    begin
        raiserror('ERR_ITEMS_MEMBERSHIPPARTIALREFUND_HASCONTRIBUTEDREVENUE', 13, 1);
        return 1;
    end

    -- If there are memberships with add-ons that are also on this transaction and the addons are not all being refunded

    if exists (
        select 1 from @CREDITITEMS MEMBERSHIPITEM
        inner join dbo.MEMBERSHIPTRANSACTION REFUNDINGTRANSACTION on REFUNDINGTRANSACTION.ID = MEMBERSHIPITEM.ITEMID
        inner join dbo.MEMBERSHIPADDON ADDONSINTRANSACTION on ADDONSINTRANSACTION.MEMBERSHIPTRANSACTIONID = REFUNDINGTRANSACTION.ID
        where 
            MEMBERSHIPITEM.WILLBECANCELLED = 1
            and (select count(*) from @CREDITITEMS where ITEMID = ADDONSINTRANSACTION.ID) <> (ADDONSINTRANSACTION.QUANTITY - ADDONSINTRANSACTION.NUMCANCELLED)
    )
    begin
        raiserror('BBERR_ITEMS_MEMBERSHIPCANCEL_NOTREFUNDINGADDONS', 13, 1);
        return 1;
    end

    -- Find contributed revenue:

    -- 1. Find the contributory line items (donation line items whose sourcelineitemid is the revenuesplitid of the membership we're trying to refund).

    -- 2. For discounted memberships sold through Sales, prorate the discount across the contributory line items and subtract it from the membership line item.

    -- 3. Subtract the contributory amount from the amount to refund of the membership item.


    declare @CONTRIBUTEDREVENUE table (
        CONTRIBUTEDLINEITEMID uniqueidentifier,
        SOURCELINEITEMID uniqueidentifier,
        TYPECODE tinyint,
        [DESCRIPTION] nvarchar(200),
        ORIGINALAMOUNT money,
        DISCOUNTS money
    );

    -- Find contributed revenue.

    insert into @CONTRIBUTEDREVENUE
    select
        CONTRIBUTEDLI.ID CONTRIBUTEDLINEITEMID,
        CONTRIBUTEDLI.SOURCELINEITEMID,
        2 TYPECODE,
        CONTRIBUTEDLI.[DESCRIPTION],
        CONTRIBUTEDLI.BASEAMOUNT ORIGINALAMOUNT,
        null DISCOUNTS
    from @CREDITITEMS CREDITITEMS
    inner join dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDLI on CONTRIBUTEDLI.SOURCELINEITEMID = CREDITITEMS.REVENUESPLITID
    inner join dbo.REVENUESPLIT_EXT CONTRIBUTEDLI_EXT on CONTRIBUTEDLI_EXT.ID = CONTRIBUTEDLI.ID
    where
        CONTRIBUTEDLI_EXT.TYPECODE = 0  -- Gift

        and CONTRIBUTEDLI_EXT.APPLICATIONCODE = 0  -- Application type is "Donation" (not "Membership", even though ER's contributed revenue applies to "Event Registration")

        and CONTRIBUTEDLI.TYPECODE = 0;  -- Standard


    if @@ROWCOUNT > 0
    begin
        -- Set the contributed amount on items with partially contributed revenue.

        update @CREDITITEMS
        set CONTRIBUTEDREVENUE = coalesce((select sum(ORIGINALAMOUNT) from @CONTRIBUTEDREVENUE where SOURCELINEITEMID = CI.REVENUESPLITID),0)
        from @CREDITITEMS CI;

        -- Prorate discounts across contributed items.

        -- This could be more efficient (when discount is more than contributed amount, no proration is necessary, because the full contributory amount is discounted), but that's not important.

        update @CONTRIBUTEDREVENUE
        set DISCOUNTS = PRORATEDAMOUNTS.AMOUNT
        from @CREDITITEMS CREDITITEMS
        cross apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
            CREDITITEMS.CONTRIBUTEDREVENUE,
            (select case when CREDITITEMS.ITEMDISCOUNTS > CREDITITEMS.CONTRIBUTEDREVENUE then CREDITITEMS.CONTRIBUTEDREVENUE else CREDITITEMS.ITEMDISCOUNTS end),
            2,
            (
                select CONTRIBUTEDREVENUE.CONTRIBUTEDLINEITEMID ID, CONTRIBUTEDREVENUE.ORIGINALAMOUNT AMOUNT
                from @CONTRIBUTEDREVENUE CONTRIBUTEDREVENUE
                where CONTRIBUTEDREVENUE.SOURCELINEITEMID = CREDITITEMS.REVENUESPLITID
                for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
            )
        ) PRORATEDAMOUNTS
        where PRORATEDAMOUNTS.ID = CONTRIBUTEDLINEITEMID;

        -- Reduce the discount amount for partially contributed items (since the discounts have been moved to the contributory line items).

        -- Also reduce the refund amount to account for discounts in excess of the contributory revenue.

        update @CREDITITEMS
        set
            AMOUNTTOREFUND = case when CONTRIBUTEDREVENUE > ITEMDISCOUNTS then PRICE - CONTRIBUTEDREVENUE else AMOUNTTOREFUND end,
            ITEMDISCOUNTS = case when CONTRIBUTEDREVENUE > ITEMDISCOUNTS then 0 else ITEMDISCOUNTS - CONTRIBUTEDREVENUE end
        where CONTRIBUTEDREVENUE > 0;

        -- At this point:

        -- -- 1. The ITEMDISCOUNTS of a partially contributed credit item will be max(0, discounted amount - contributed amount)

        -- -- 2. The AMOUNTTOREFUND of a partially contributed credit item will be PRICE - max(contributed amount, discounted amount).

        -- Otherwise the refund GL will not be generated correctly.

    end


    declare @INS table
    (
        ID uniqueidentifier,
        CREDITID uniqueidentifier,
        SALESORDERITEMID uniqueidentifier,
        PRICE money,
        DISCOUNTS money,
        TYPECODE tinyint,
        [DESCRIPTION] nvarchar(700),
        SOURCELINEITEMID uniqueidentifier,
        REFUNDAMOUNT money,
        UNITVALUE money
    );

    -- This may not be the most intuitive data set-up in the world (particularly the roles that UNITVALUE and DISCOUNTS play in GL creation).

    -- However, the alternatives are breaking backward-compatibility of refund data or converting all existing refunds into something that makes more sense,

    -- neither of which we have time to do right now.

    insert into @INS
    select
        ID,
        @ID,
        SALESORDERITEMID,
        PRICE,
        ITEMDISCOUNTS,
        TYPECODE,
        [DESCRIPTION],
        REVENUESPLITID,
        AMOUNTTOREFUND,
        AMOUNTTOREFUND + CONTRIBUTEDREVENUE + ITEMDISCOUNTS  -- GL is wack.

    from @CREDITITEMS

    union all

    -- Contributory portions of memberships

    select
        newid(),
        @ID,
        null SALESORDERITEMID,
        ORIGINALAMOUNT PRICE,
        DISCOUNTS,
        TYPECODE,
        [DESCRIPTION],
        CONTRIBUTEDLINEITEMID,
        ORIGINALAMOUNT - DISCOUNTS REFUNDAMOUNT,
        ORIGINALAMOUNT UNITVALUE
    from @CONTRIBUTEDREVENUE
    where ORIGINALAMOUNT - DISCOUNTS <> 0;


    begin try
        -- Bug #167166 - MDC

        -- Weird that the @TRANSACTIONDATE is being inserted into a datetimeoffset column. Always has offset 0.

        declare @TRANSACTIONDATE datetime =  dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

        declare @CURRENCYID uniqueidentifier = (select ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1);
        declare @PDACCOUNTSYSTEMID uniqueidentifier;
        declare @ALLOWGLDISTRIBUTIONS bit;

        select
            @PDACCOUNTSYSTEMID = ID,
            @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS
        from
            dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();

        declare @CONSTITUENTID uniqueidentifier = (
            select top 1 FT.CONSTITUENTID
            from dbo.MEMBERSHIPTRANSACTION MT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = MT.REVENUESPLITID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
            where MT.MEMBERSHIPID = @MEMBERSHIPID
            order by MT.TRANSACTIONDATE, MT.DATEADDED desc
        );

        -- Get sales order ID for refunded item if it is in a sales order

        declare @ORDERID uniqueidentifier = (
            select top 1 SALESORDER.ID
            from @CREDITITEMS CREDITITEMS
            inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = CREDITITEMS.SALESORDERITEMID
            inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        );


        insert into dbo.FINANCIALTRANSACTION
        (
            ID,
            TRANSACTIONAMOUNT,
            BASEAMOUNT,
            ORGAMOUNT,
            TRANSACTIONCURRENCYID,
            TYPECODE,
            PDACCOUNTSYSTEMID,
            [DESCRIPTION],
            [DATE],
            POSTDATE,
            POSTSTATUSCODE,
            PARENTID,
            APPUSERID,
            CONSTITUENTID,
            DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID
        )
        values
        (
            @ID,
            @REFUNDEDITEMTOTAL,
            @REFUNDEDITEMTOTAL,
            @REFUNDEDITEMTOTAL,
            @CURRENCYID,
            23, --Refund

            @PDACCOUNTSYSTEMID,
            @COMMENT,
            @TRANSACTIONDATE,
            case when @ALLOWGLDISTRIBUTIONS = 1 and isnull(@POSTSTATUSCODE, 1) != 3 then cast(@TRANSACTIONDATE as date) else null end,
            case when @ALLOWGLDISTRIBUTIONS = 1 and isnull(@POSTSTATUSCODE, 1) != 3 then 1 else 3 end,
            (select REVENUEID from dbo.SALESORDER where ID = @ORDERID),
            @CURRENTAPPUSERID,
            @CONSTITUENTID,
            @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
        );

        insert into dbo.CREDIT_EXT
        (
            ID,
            SALESORDERID,
            CREDITREASONCODEID,
            DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID
        )
        values
        (
            @ID,
            @ORDERID,
            case @CREDITREASONCODEID
                when '00000000-0000-0000-0000-000000000000' then null
                else @CREDITREASONCODEID
            end,
            @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
        );

        exec dbo.USP_CREDIT_ADDCREDITPAYMENTS @ID, @REFUNDMETHODS, @CHANGEAGENTID, @CURRENTAPPUSERID, @ADDRESSID;

        insert into dbo.FINANCIALTRANSACTIONLINEITEM
        (
            ID,
            FINANCIALTRANSACTIONID,
            UNITVALUE,
            QUANTITY,
            [DESCRIPTION],
            TRANSACTIONAMOUNT,
            BASEAMOUNT,
            ORGAMOUNT,
            POSTDATE,
            POSTSTATUSCODE,
            TYPECODE,
            SOURCELINEITEMID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
            I.ID,
            I.CREDITID,
            I.UNITVALUE,
            1 QUANTITY,
            I.[DESCRIPTION],
            I.REFUNDAMOUNT,
            I.REFUNDAMOUNT,
            I.REFUNDAMOUNT,
            FT.POSTDATE,
            FT.POSTSTATUSCODE,
            case I.TYPECODE
                when 3 then 7 --Fee

                when 4 then 8 --Tax

                when 5 then 5 --Discount

                else 0
            end,
            I.SOURCELINEITEMID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @INS I
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = I.CREDITID;

        insert into dbo.CREDITITEM_EXT
        (
            ID,
            CREDITID,
            DISCOUNTS,
            FEES,
            GROUPID,
            GROUPTYPECODE,
            SALESORDERITEMID,
            TYPECODE,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
            ID,
            CREDITID,
            DISCOUNTS,
            0 FEES,
            null GROUPID,
            0 GROUPTYPECODE,
            SALESORDERITEMID,
            TYPECODE,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @INS;

        -- Update ticket applied to membership fields for tickets that were applied to a fully refunded membership.

        update dbo.TICKET
        set
            TICKET.APPLIEDTOMEMBERSHIP = 0,
            TICKET.APPLIEDTOMEMBERSHIPSALESORDERID = null
        from dbo.TICKET
        inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMTICKET APPLIEDTICKETS on TICKET.ID = APPLIEDTICKETS.TICKETID
        inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM APPLIEDITEM on APPLIEDTICKETS.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMID = APPLIEDITEM.ID
        inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION PROMOTION on APPLIEDITEM.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = PROMOTION.ID
        inner join @CREDITITEMS CREDITITEMS on PROMOTION.SALESORDERITEMID = CREDITITEMS.SALESORDERITEMID
        where CREDITITEMS.AMOUNTTOREFUND = CREDITITEMS.PRICE - CREDITITEMS.ITEMDISCOUNTS
            or CREDITITEMS.CONTRIBUTEDREVENUE > 0;  -- hacky; memberships with contributed revenue must be fully refunded, so we know to unmark the ticket as applied.



        --Add credit membership items

        insert into dbo.CREDITITEMMEMBERSHIP
        (
            ID,
            MEMBERSHIPID,
            MEMBERSHIPPROGRAMID,
            MEMBERSHIPPROGRAMNAME,
            MEMBERSHIPLEVELID,
            MEMBERSHIPLEVELTERMID,
            MEMBERSHIPLEVELTYPECODEID,
            NUMBEROFCHILDREN,
            COMMENTS,
            ISGIFT,
            GIVENBYID,
            EXPIRATIONDATE,
            MEMBERS,
            ACTIONCODE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED,
            WILLBECANCELLED
        )
        select
            CREDITITEMS.ID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPID,
            MEMBERSHIP.MEMBERSHIPPROGRAMID,
            (select NAME from dbo.MEMBERSHIPPROGRAM where ID = MEMBERSHIP.MEMBERSHIPPROGRAMID),
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTYPECODEID,
            MEMBERSHIPTRANSACTION.NUMBEROFCHILDREN,
            MEMBERSHIPTRANSACTION.COMMENTS,
            MEMBERSHIPTRANSACTION.ISGIFT,
            MEMBERSHIPTRANSACTION.DONORID,
            MEMBERSHIPTRANSACTION.EXPIRATIONDATE,
            (
                select dbo.UFN_CONSTITUENT_BUILDNAME(MEMBER.CONSTITUENTID) MEMBERNAME
                from dbo.MEMBER
                where MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                for xml raw('ITEM'),type,elements,root('MEMBERSHIPMEMBERS'),binary base64
            ),
            MEMBERSHIPTRANSACTION.ACTIONCODE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
            CREDITITEMS.WILLBECANCELLED
        from dbo.MEMBERSHIPTRANSACTION
        inner join dbo.MEMBERSHIP on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
        inner join @CREDITITEMS CREDITITEMS on CREDITITEMS.ITEMID = MEMBERSHIPTRANSACTION.ID
        where MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID;

        -- Add membership promotions (if the full amount paid for the membership is being returned)

        insert into dbo.CREDITITEMMEMBERSHIPITEMPROMOTION
        (
            CREDITITEMID,
            SALESORDERITEMMEMBERSHIPITEMPROMOTIONID,
            AMOUNT,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select distinct
            REFUNDEDMEMBERSHIPLI.ID,
            SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID,
            SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT, --Right now, the entire promotion has to be returned

            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
        inner join dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDMEMBERSHIPLI on REFUNDEDMEMBERSHIPLI.ID = EXT.ID
        where REFUNDEDMEMBERSHIPLI.FINANCIALTRANSACTIONID = @ID
            and (
                EXT.DISCOUNTS > 0  -- Unfortunately, this is 0 when the discount has moved to contributed revenue.

                or exists (        -- Instead, link to the original thing we were trying to refund to determine whether we reversed discounts.

                    select 1
                    from @CONTRIBUTEDREVENUE CR
                    where CR.SOURCELINEITEMID = REFUNDEDMEMBERSHIPLI.SOURCELINEITEMID
                        and DISCOUNTS > 0
                )
            );


        if @ALLOWGLDISTRIBUTIONS = 1 and isnull(@POSTSTATUSCODE, 1) != 3 begin
            exec dbo.USP_REFUND_CREATEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
            exec dbo.USP_REFUND_CREATEDISCOUNTGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
        end

        -- (Updating the membership transaction, members etc. now occurs after the refund and depends on user interaction).


    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end