USP_CREDIT_ADDORDERREFUND

CREATE procedure dbo.USP_CREDIT_ADDORDERREFUND
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @ORDERID uniqueidentifier,
    @ITEMS xml = null,
    @TAXES xml = null,
    @REFUNDMETHODS xml = null,
    @COMMENT nvarchar(500) = '',
    @CREDITREASONCODEID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @TOTAL money = null,  -- OBSOLETE - Maximum refundable amount (i.e. total value of the order including discounts).

    @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;
    declare @ORDERREVENUEID uniqueidentifier;

    --Make sure nobody else has done a refund since the form was opened (coalescing the TSLONG in case of null does nothing. x <> null is never true).

    if @TIMESTAMP <> (select TSLONG from dbo.SALESORDER where ID = @ORDERID) begin
        if @ORDERID is null or @ORDERID = '00000000-0000-0000-0000-000000000000'
            raiserror('ERR_TRANSACTIONID_ORDERNOTSELECTED', 13, 1);
        else
            raiserror('ERR_CREDITITEMS_CONFLICTINGREFUND', 13, 1);

        return 1;
    end
    else begin
        update dbo.SALESORDER
        set DATECHANGED = @CURRENTDATE, CHANGEDBYID = @CHANGEAGENTID
        where ID = @ORDERID;

        select @ORDERREVENUEID = FT.ID, @POSTSTATUSCODE = FT.POSTSTATUSCODE
        from dbo.SALESORDER SO
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = SO.REVENUEID
        where SO.ID = @ORDERID;
    end

    -- Get refund items from input parameter @ITEMS

    declare @CREDITITEMS table(
        ID uniqueidentifier,
        SALESORDERITEMID uniqueidentifier,
        PRICE money,
        ITEMDISCOUNTS money,  -- Item-level discounts being reversed for a particular item (not included in the @ITEMS xml).

        ORDERDISCOUNTS money,  -- Order-level discounts being reversed for a particular item (not included in the @ITEMS xml).

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

        TYPECODE tinyint,
        [DESCRIPTION] nvarchar(255),
        EVENTREGISTRANTID uniqueidentifier,
        ITEMID uniqueidentifier,  -- membership transaction ID, membership addon ID, or itemized item ID (ticket/merch unit/item-level fee)

        REVENUESPLITID uniqueidentifier,
        CONTRIBUTEDREVENUE money,
        WILLBECANCELLED bit
    );

    insert into @CREDITITEMS
    select
        newid(),
        T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier') SALESORDERITEMID,
        T.item.value('(@PRICE)[1]','money') PRICE,
        0 ITEMDISCOUNTS,
        0 ORDERDISCOUNTS,
        T.item.value('(@AMOUNTPAID)[1]','money') AMOUNTPAID,
        T.item.value('(@AMOUNTTOREFUND)[1]','money') AMOUNTTOREFUND,
        T.item.value('(@TYPECODE)[1]','tinyint') TYPECODE,
        (select [DESCRIPTION] from dbo.SALESORDERITEM where ID = T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier')) [DESCRIPTION],
        case
            when T.item.value('(@EVENTREGISTRANTID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                then null
            else
                T.item.value('(@EVENTREGISTRANTID)[1]','uniqueidentifier')
        end EVENTREGISTRANTID,
        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;

    declare @TAXTABLE table (
        TAXID uniqueidentifier,  -- actually refers to SALESORDERITEM, not TAX.

        [DESCRIPTION] nvarchar(200),
        REVENUESPLITID uniqueidentifier,
        CURRENTTAX money  -- amount of tax to refund

    );

    insert into @TAXTABLE
    select
        T.item.value('(@TAXID)[1]','uniqueidentifier'),
        (select [DESCRIPTION] from dbo.SALESORDERITEM where ID = T.item.value('(@TAXID)[1]','uniqueidentifier')),
        T.item.value('(@REVENUESPLITID)[1]','uniqueidentifier'),
        round(T.item.value('(@CURRENTTAX)[1]','money'), 2)
    from @TAXES.nodes('/TAXES/ITEM') T(item)
    where T.item.value('(@CURRENTTAX)[1]','money') > 0;


    -- As of 2013/09/20, we have decided that order-level discounts will be reversed only if everything they apply to is fully refunded.

    -- In other words, if there is a discounted item that is *not* fully refunded, then we *do not* reverse order discounts.

    declare @SHOULDREVERSEORDERDISCOUNTS bit = case
        when (
            -- the number of discounted ticket and merch items that are being fully refunded in this transaction

            select count(ID)
            from @CREDITITEMS CREDITITEMS
            where TYPECODE in (0,14)  -- Only tickets and merch can have order-level discounts (although merch might not)

                and AMOUNTPAID < PRICE  -- Ticket or merch item was discounted => order-level discount was applicable (this excludes non-discountable merch)

                and AMOUNTTOREFUND = AMOUNTPAID  -- fully refunding the item (because if any like this exist, we do NOT reverse the order-level discount)

        ) = (
            -- the number of discounted ticket and merch items on the entire order

            select count(TICKET.ID) + count(MERCH.ID)
            from dbo.SALESORDERITEM SOI
            left join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SOI.ID
            left join dbo.SALESORDERITEMMERCHANDISEUNIT MERCH on MERCH.SALESORDERITEMMERCHANDISEID = SOI.ID
            where SOI.SALESORDERID = @ORDERID
                and (
                    coalesce(TICKET.ORDERLEVELDISCOUNTSAPPLIED, MERCH.ORDERLEVELDISCOUNTSAPPLIED) > 0
                    or coalesce(TICKET.ITEMLEVELDISCOUNTSAPPLIED, MERCH.ITEMLEVELDISCOUNTSAPPLIED) > 0
                )
        ) then 1
        else 0
    end;

    -- Determine which item-level and order-level discounts will be reversed (only if the full amount paid for the ticket/merch unit is being refunded).

    update @CREDITITEMS
    set
        ITEMDISCOUNTS = TICKET.ITEMLEVELDISCOUNTSAPPLIED,
        ORDERDISCOUNTS = case when @SHOULDREVERSEORDERDISCOUNTS = 1 then TICKET.ORDERLEVELDISCOUNTSAPPLIED else 0 end
    from @CREDITITEMS CREDITITEMS
    inner join dbo.TICKET on TICKET.ID = CREDITITEMS.ITEMID
    where TICKET.AMOUNTPAID = CREDITITEMS.AMOUNTTOREFUND;

    update @CREDITITEMS
    set
        ITEMDISCOUNTS = MERCHANDISE.ITEMLEVELDISCOUNTSAPPLIED,
        ORDERDISCOUNTS = case when @SHOULDREVERSEORDERDISCOUNTS = 1 then MERCHANDISE.ORDERLEVELDISCOUNTSAPPLIED else 0 end
    from @CREDITITEMS CREDITITEMS
    inner join dbo.SALESORDERITEMMERCHANDISEUNIT MERCHANDISE on MERCHANDISE.ID = CREDITITEMS.ITEMID
    where MERCHANDISE.AMOUNTPAID = CREDITITEMS.AMOUNTTOREFUND;

    -- 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(case when TYPECODE = 5 then 0 else AMOUNTTOREFUND end)
            from @CREDITITEMS
        )
        + coalesce((select sum(CURRENTTAX) from @TAXTABLE),0)
    );

    -- 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 SALESORDERITEMID, count(ID) NUMREFUNDING
            from @CREDITITEMS
            where
                not TYPECODE in (0,3,14) -- tickets, item fees, and merch items each have their own ITEMID, which will be validated roughly 30 lines below

                or (TYPECODE = 3 and ITEMID is null)
            group by SALESORDERITEMID
        ) CREDITITEMS_GROUPED
        inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = CREDITITEMS_GROUPED.SALESORDERITEMID
        left join dbo.CREDITITEM_EXT on CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
        left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = CREDITITEM_EXT.ID
        group by SALESORDERITEM.ID, SALESORDERITEM.QUANTITY, CREDITITEMS_GROUPED.NUMREFUNDING
        having SALESORDERITEM.QUANTITY - coalesce(sum(FTLI.QUANTITY),0) - CREDITITEMS_GROUPED.NUMREFUNDING < 0
    ) 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 payment amount remaining

    if @REFUNDEDITEMTOTAL > (
        select sum(PAYMENTAMOUNTREMAINING)
        from (
            select
                FINANCIALTRANSACTION.BASEAMOUNT - coalesce((
                    select sum(coalesce(CREDITPAYMENT.AMOUNT, 0)) from dbo.CREDITPAYMENT where REVENUEID = FINANCIALTRANSACTION.ID
                ),0) as PAYMENTAMOUNTREMAINING -- (original amount - refunded amount)

            from dbo.SALESORDER
            inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
            inner join dbo.FINANCIALTRANSACTION on SALESORDERPAYMENT.PAYMENTID = FINANCIALTRANSACTION.ID
            where SALESORDER.ID = @ORDERID
        ) PAYMENTS
    )
    begin
        raiserror('ERR_REFUNDAMOUNT_EXCEEDSLIMIT', 13, 1);
        return 1;
    end

    -- Error if an item has already been refunded

    if exists (
        select 1 from @CREDITITEMS ITEMS
        inner join dbo.CREDITITEM_EXT on ITEMS.ITEMID = CREDITITEM_EXT.SALESORDERITEMIZEDITEMID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = CREDITITEM_EXT.ID
        where FTLI.BASEAMOUNT > 0
    )
    begin
        raiserror('ERR_ITEMALREADYREFUNDED', 13, 1);
        return 1;
    end

    -- Error if an itemized item or membership item has no ITEMID

    if exists (
        select 1
        from @CREDITITEMS ITEMS
        where TYPECODE in (0,1,14,16)  -- ticket, membership, merch, add-on

            and ITEMID is null

        union all

        -- item-level fees

        select 1
        from @CREDITITEMS ITEMS
        inner join dbo.SALESORDERITEMFEE FEEITEM on FEEITEM.ID = ITEMS.SALESORDERITEMID
        where FEEITEM.APPLIESTOCODE = 1  -- item-level

            and ITEMS.ITEMID is null
    )
    begin
        raiserror('ERR_ITEMWITHOUTITEMID', 13, 1);
        return 1;
    end

    -- Error if there are any later membership transactions

    if exists (
        select 1 from @CREDITITEMS MEMBERSHIPITEM
        inner join dbo.MEMBERSHIPTRANSACTION REFUNDINGTRANSACTION on REFUNDINGTRANSACTION.ID = MEMBERSHIPITEM.ITEMID
        outer apply (
            select top 1 ID
            from dbo.MEMBERSHIPTRANSACTION
            where MEMBERSHIPID = REFUNDINGTRANSACTION.MEMBERSHIPID
            order by DATEADDED desc
        ) LATESTTRANSACTION
        where MEMBERSHIPITEM.ITEMID <> LATESTTRANSACTION.ID
    )
    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

    -- Reduce or delete recognition credits for refunded donations.

    if exists
    (
        select 1
        from @CREDITITEMS ITEMS
        where ITEMS.TYPECODE = 2 -- Donation

    )
    begin
        -- Recognition credits have no significant financial implications, so we'll just cut them by the same percentage as the donation.

        declare @DONATIONREFUNDS table (REVENUESPLITID uniqueidentifier, PERCENTREFUNDED real);
        insert into @DONATIONREFUNDS
        select
            SALESORDERITEMDONATION.REVENUESPLITID,
            case
                when SALESORDERITEMDONATION.AMOUNT = 0.0 or CREDITITEMS.AMOUNTTOREFUND = SALESORDERITEMDONATION.AMOUNT
                    then 1.0
                else CREDITITEMS.AMOUNTTOREFUND / SALESORDERITEMDONATION.AMOUNT
            end
        from dbo.SALESORDERITEMDONATION
        inner join @CREDITITEMS CREDITITEMS on CREDITITEMS.SALESORDERITEMID = SALESORDERITEMDONATION.ID
        where CREDITITEMS.TYPECODE = 2;

        -- Delete if we're refunding the entire donation

        delete from dbo.REVENUERECOGNITION
        where REVENUERECOGNITION.REVENUESPLITID in
            (
                select ITEMS.REVENUESPLITID
                from @DONATIONREFUNDS ITEMS
                where PERCENTREFUNDED = 1.0
            );

        -- Reduce if we're only refunding part of the donation.

        update dbo.REVENUERECOGNITION
        set AMOUNT = AMOUNT * (1.0 - DONATIONREFUNDS.PERCENTREFUNDED)
        from dbo.REVENUERECOGNITION
        inner join @DONATIONREFUNDS DONATIONREFUNDS on REVENUERECOGNITION.REVENUESPLITID = DONATIONREFUNDS.REVENUESPLITID
        where DONATIONREFUNDS.PERCENTREFUNDED <> 1.0;
    end


    -- Find contributed revenue:

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

    -- 2. For discounted memberships, 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 or event registration item.


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

    -- Find contributed revenue (for memberships and event registrations).

    insert into @CONTRIBUTEDREVENUE
    select
        CONTRIBUTEDLI.ID CONTRIBUTEDLINEITEMID,
        CONTRIBUTEDLI.SOURCELINEITEMID,
        case
            when CONTRIBUTEDLI_EXT.APPLICATIONCODE = 1 then 6
            else 2
        end 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 in (0,1)  -- Application type is "Donation" or "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 item-level 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),
        GROUPID uniqueidentifier,
        GROUPTYPECODE tinyint,
        SOURCELINEITEMID uniqueidentifier,
        REFUNDAMOUNT money,
        UNITVALUE money,
        ITEMID uniqueidentifier
    );

    -- 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],
        EVENTREGISTRANTID,
        case when TYPECODE = 6 then 1 else 0 end GROUPTYPECODE,
        REVENUESPLITID,
        AMOUNTTOREFUND + ORDERDISCOUNTS,  -- Refund amount needs to include order discounts (reversed in separate line item)

        AMOUNTTOREFUND + CONTRIBUTEDREVENUE + ITEMDISCOUNTS + ORDERDISCOUNTS,  -- Unit value needs to include discounts and contributed revenue (for GL generation)

        ITEMID
    from @CREDITITEMS

    union all

    --taxes

    select
        newid(),
        @ID,
        TAXES.TAXID,
        TAXES.CURRENTTAX PRICE,
        0 DISCOUNTS,
        4 TYPECODE,
        (select [DESCRIPTION] from dbo.SALESORDERITEM where ID = TAXES.TAXID),
        null GROUPID,
        0 GROUPTYPECODE,
        TAXES.REVENUESPLITID,
        TAXES.CURRENTTAX,
        TAXES.CURRENTTAX UNITVALUE,
        null ITEMID
    from @TAXTABLE TAXES

    union all

    -- Contributory portions of memberships and event registrations

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

    if @SHOULDREVERSEORDERDISCOUNTS = 1
    begin
        insert into @INS
        select
            newid() as ID,
            @ID as CREDITID,
            SALESORDERITEM.ID as SALESORDERITEMID,
            -DISCOUNTLI.BASEAMOUNT as PRICE,
            0 DISCOUNTS,
            5 TYPECODE,
            SALESORDERITEM.[DESCRIPTION],
            null GROUPID,
            0 GROUPTYPECODE,
            DISCOUNTLI.ID as SOURCELINEITEMID,  -- Formerly the source ID here was always null. Having it makes GL a lot easier, though.

            -DISCOUNTLI.BASEAMOUNT as REFUNDAMOUNT,
            -DISCOUNTLI.BASEAMOUNT as UNITVALUE,
            null ITEMID
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMORDERDISCOUNT ORDERDISCOUNT on ORDERDISCOUNT.ID = SALESORDERITEM.ID
        inner join dbo.CREDITITEM_EXT DISCOUNTLI_EXT on (DISCOUNTLI_EXT.DISCOUNTID = ORDERDISCOUNT.DISCOUNTID or (DISCOUNTLI_EXT.DISCOUNTID is null and ORDERDISCOUNT.ISADJUSTABLEDISCOUNT = 1))
        inner join dbo.FINANCIALTRANSACTIONLINEITEM DISCOUNTLI on DISCOUNTLI.ID = DISCOUNTLI_EXT.ID and DISCOUNTLI.FINANCIALTRANSACTIONID = @ORDERREVENUEID
        where SALESORDERITEM.TYPECODE = 5
            and SALESORDERITEM.SALESORDERID = @ORDERID;
    end


    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 CONSTITUENTID from dbo.SALESORDER where ID = @ORDERID);

        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,
            @ORDERREVENUEID,
            @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,
            SALESORDERITEMIZEDITEMID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
            ID,
            CREDITID,
            DISCOUNTS,
            0 FEES,
            GROUPID,
            GROUPTYPECODE,
            SALESORDERITEMID,
            TYPECODE,
            case when TYPECODE in (1,16) then null else ITEMID end,  -- Membership and Add-on are not itemized

            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @INS;

        -- Update ISREFUNDED for refunded tickets.

        -- Whether the ticket is cancelled as a result of this refund is handled elsewhere (likely near RefundAddViewDataFormUIModel).

        update dbo.TICKET
        set ISREFUNDED = 1
        where ID in (select ITEMID from @CREDITITEMS where TYPECODE = 0);

        -- 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 MEMBERSHIPCREDITITEMS on PROMOTION.SALESORDERITEMID = MEMBERSHIPCREDITITEMS.SALESORDERITEMID
        where MEMBERSHIPCREDITITEMS.AMOUNTTOREFUND = MEMBERSHIPCREDITITEMS.AMOUNTPAID
            or MEMBERSHIPCREDITITEMS.CONTRIBUTEDREVENUE > 0;  -- hacky; memberships with contributed revenue must be fully refunded, so we know to unmark the ticket as applied.


        -- Update refunded amount for refunded merchandise item (whether the item is returned is determined by the user after the refund is complete).

        update dbo.SALESORDERITEMMERCHANDISEUNIT
        set REFUNDEDAMOUNT = coalesce(REFUNDEDAMOUNT,0) + ITEMS.AMOUNTTOREFUND
        from dbo.SALESORDERITEMMERCHANDISEUNIT
        inner join @CREDITITEMS ITEMS on ITEMS.ITEMID = SALESORDERITEMMERCHANDISEUNIT.ID;

        -- Update refunded amount for refunded ticket fees

        update dbo.SALESORDERITEMTICKETFEE
        set REFUNDEDAMOUNT = coalesce(REFUNDEDAMOUNT,0) + ITEMS.AMOUNTTOREFUND
        from dbo.SALESORDERITEMTICKETFEE
        inner join @CREDITITEMS ITEMS on ITEMS.ITEMID = SALESORDERITEMTICKETFEE.ID;

        --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,
            SALESORDERITEMMEMBERSHIP.MEMBERSHIPID,
            SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID,
            SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMNAME,
            SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID,
            SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID,
            SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
            SALESORDERITEMMEMBERSHIP.NUMBEROFCHILDREN,
            SALESORDERITEMMEMBERSHIP.COMMENTS,
            SALESORDERITEMMEMBERSHIP.ISGIFT,
            SALESORDERITEMMEMBERSHIP.GIVENBYID,
            SALESORDERITEMMEMBERSHIP.EXPIRATIONDATE,
            (
                select dbo.UFN_CONSTITUENT_BUILDNAME(SOIM.CONSTITUENTID) MEMBERNAME
                from dbo.SALESORDERITEMMEMBER as SOIM
                inner join dbo.SALESORDERITEMMEMBERSHIP as SOIMS on SOIM.SALESORDERITEMMEMBERSHIPID = SOIMS.ID
                inner join dbo.MEMBER on MEMBER.CONSTITUENTID = SOIM.CONSTITUENTID and MEMBER.MEMBERSHIPID = SOIMS.MEMBERSHIPID
                where SALESORDERITEMMEMBERSHIP.ID = SOIM.SALESORDERITEMMEMBERSHIPID and MEMBER.ISDROPPED = 0
                for xml raw('ITEM'),type,elements,root('MEMBERSHIPMEMBERS'),binary base64
            ),
            coalesce(
                MEMBERSHIPTRANSACTION.ACTIONCODE,
                (select MEMBERSHIPTRANSACTION.ACTIONCODE from dbo.MEMBERSHIPTRANSACTION where ID = dbo.UFN_MEMBERSHIP_GETLASTMEMBERSHIPTRANSACTIONID(SALESORDERITEMMEMBERSHIP.MEMBERSHIPID))
            ),
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
            CREDITITEMS.WILLBECANCELLED
        from @CREDITITEMS CREDITITEMS
        inner join dbo.SALESORDERITEM on CREDITITEMS.SALESORDERITEMID = SALESORDERITEM.ID
        inner join dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
        left join dbo.MEMBERSHIPTRANSACTION on SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID

        --Add credit event registration items

        insert into dbo.CREDITITEMEVENTREGISTRATION
        (
            ID,
            REGISTRANTID,
            EVENTNAME,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            CREDITITEMS.ID,
            SALESORDERITEMEVENTREGISTRATION.REGISTRANTID,
            SALESORDERITEMEVENTREGISTRATION.EVENTNAME,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from @CREDITITEMS as CREDITITEMS
        inner join dbo.SALESORDERITEM on CREDITITEMS.SALESORDERITEMID = SALESORDERITEM.ID
        inner join dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID

        -- Add item level discounts (via the DISCOUNTS column on the CREDITITEM_EXT records we already created)

        insert into dbo.CREDITITEMITEMDISCOUNT
        (
            CREDITITEMID,
            SALESORDERITEMITEMDISCOUNTID,
            AMOUNT,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select distinct  -- TODO: why distinct?

            FTLI.ID,
            SALESORDERITEMITEMDISCOUNT.ID,
            EXT.DISCOUNTS,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from dbo.SALESORDERITEMITEMDISCOUNT
        inner join dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = EXT.ID
        where FTLI.FINANCIALTRANSACTIONID = @ID
            and EXT.DISCOUNTS > 0;


        -- 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  -- TODO: why 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 membership transactions, members etc. for refunded memberships 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