UFN_REFUND_GETPRORATEDDISCOUNTSPLITS

CREATE function dbo.UFN_REFUND_GETPRORATEDDISCOUNTSPLITS(@CREDITID uniqueidentifier)
returns table as
return

    -- Order-level discounts. They have their own refund line items, which makes this queery the simplest.

    select
        ORIGINALDISCOUNTLI.SOURCELINEITEMID as REVENUESPLITID,
        REFUNDEDDISCOUNTLI.ID as CREDITITEMID,
        ORIGINALDISCOUNTLI.ID as DISCOUNTCREDITITEMID,
        'Refund-Discount-Order-' + convert(nvarchar(50), SALESORDER.SEQUENCEID) as REFERENCE,
        ORIGINALDISCOUNTLI.BASEAMOUNT as AMOUNT
    from dbo.FINANCIALTRANSACTIONLINEITEM REFUNDEDDISCOUNTLI
    inner join dbo.FINANCIALTRANSACTIONLINEITEM ORIGINALDISCOUNTLI on ORIGINALDISCOUNTLI.ID = REFUNDEDDISCOUNTLI.SOURCELINEITEMID
    inner join dbo.SALESORDER on SALESORDER.REVENUEID = ORIGINALDISCOUNTLI.FINANCIALTRANSACTIONID
    where REFUNDEDDISCOUNTLI.FINANCIALTRANSACTIONID = @CREDITID
        and ORIGINALDISCOUNTLI.TYPECODE = 5  -- Discount

        and ORIGINALDISCOUNTLI.BASEAMOUNT <> 0

    union all

    -- Item-level discounts. Their refunds do not exist as line items, so we have to twist back through RSO.

    select
        ORDERLI.ID as REVENUESPLITID,
        REFUNDLI_EXT.ID as CREDITITEMID,
        DISCOUNTITEM.ID as DISCOUNTCREDITITEMID,
        'Refund-Discount-Order-' + convert(nvarchar(50),SALESORDER.SEQUENCEID) as REFERENCE,
        CREDITITEMITEMDISCOUNT.AMOUNT
    from dbo.CREDITITEM_EXT REFUNDLI_EXT  -- refunded ticket or merch item's ext

    inner join dbo.CREDITITEMITEMDISCOUNT on REFUNDLI_EXT.ID = CREDITITEMITEMDISCOUNT.CREDITITEMID  -- reversed item-level discount for the refunded ticket or merch item

    inner join dbo.SALESORDERITEMITEMDISCOUNT on CREDITITEMITEMDISCOUNT.SALESORDERITEMITEMDISCOUNTID = SALESORDERITEMITEMDISCOUNT.ID -- original item-level discount

    inner join dbo.SALESORDERITEM on REFUNDLI_EXT.SALESORDERITEMID = SALESORDERITEM.ID  -- original ticket or merch item

    left join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    left join dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEM.ID = SALESORDERITEMMERCHANDISE.ID
    inner join dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM as ORDERLI on ORDERLI.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID  -- ticket or merch item's order line item

    inner join dbo.REVENUESPLITORDER on ORDERLI.ID = REVENUESPLITORDER.ID
    cross apply (
        select top 1
            DISCOUNTLI.ID
        from dbo.FINANCIALTRANSACTIONLINEITEM DISCOUNTLI
        inner join dbo.CREDITITEM_EXT DISCOUNTLI_EXT on DISCOUNTLI_EXT.ID = DISCOUNTLI.ID
        where
            DISCOUNTLI.TYPECODE = 5
            and DISCOUNTLI_EXT.DISCOUNTID = SALESORDERITEMITEMDISCOUNT.DISCOUNTID
            and DISCOUNTLI.SOURCELINEITEMID = ORDERLI.ID
    ) DISCOUNTITEM
    where
        (
            (REVENUESPLITORDER.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID and SALESORDERITEMTICKET.EVENTID is null)
            or REVENUESPLITORDER.EVENTID = SALESORDERITEMTICKET.EVENTID
            or REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID = SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
        )
        and REFUNDLI_EXT.CREDITID = @CREDITID
        and CREDITITEMITEMDISCOUNT.AMOUNT > 0
        and ORDERLI.DELETEDON is null
        and ORDERLI.TYPECODE <> 1  -- Reversal


    union all

    -- Discounts for membership revenue. 

    select
        REFUNDMEMBERSHIPLI.SOURCELINEITEMID REVENUESPLITID,
        REFUNDMEMBERSHIPLI.ID as CREDITITEMID,
        DISCOUNTITEM.ID as DISCOUNTCREDITITEMID,
        'Refund-Discount-Order-' + convert(nvarchar(50),SALESORDER.SEQUENCEID) as REFERENCE,
        case
            when CONTRIBUTEDREVENUE.AMOUNT > 0 and CREDITITEMMEMBERSHIPITEMPROMOTION.AMOUNT > CONTRIBUTEDREVENUE.AMOUNT then
                CREDITITEMMEMBERSHIPITEMPROMOTION.AMOUNT - CONTRIBUTEDREVENUE.AMOUNT
            else
                CREDITITEMMEMBERSHIPITEMPROMOTION.AMOUNT
        end AMOUNT
    from dbo.FINANCIALTRANSACTIONLINEITEM REFUNDMEMBERSHIPLI
    inner join dbo.CREDITITEM_EXT REFUNDMEMBERSHIPLI_EXT on REFUNDMEMBERSHIPLI_EXT.ID = REFUNDMEMBERSHIPLI.ID
    inner join dbo.CREDITITEMMEMBERSHIPITEMPROMOTION on REFUNDMEMBERSHIPLI_EXT.ID = CREDITITEMMEMBERSHIPITEMPROMOTION.CREDITITEMID
    inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on CREDITITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID
    inner join dbo.SALESORDERITEM on REFUNDMEMBERSHIPLI_EXT.SALESORDERITEMID = SALESORDERITEM.ID
    inner join dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    cross apply (
        select top 1
            MEMBERSHIPPROMOLI_EXT.ID
        from dbo.FINANCIALTRANSACTIONLINEITEM MEMBERSHIPPROMOLI
        inner join dbo.CREDITITEM_EXT MEMBERSHIPPROMOLI_EXT on MEMBERSHIPPROMOLI_EXT.ID = MEMBERSHIPPROMOLI.ID
        where MEMBERSHIPPROMOLI.TYPECODE = 5
            and MEMBERSHIPPROMOLI_EXT.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID
            and MEMBERSHIPPROMOLI.SOURCELINEITEMID = REFUNDMEMBERSHIPLI.SOURCELINEITEMID
    ) DISCOUNTITEM
    outer apply (
        select
            sum(DONATIONLI.BASEAMOUNT) as AMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLI
        inner join dbo.REVENUESPLIT_EXT DONATIONLI_EXT on DONATIONLI_EXT.ID = DONATIONLI.ID
        where DONATIONLI.SOURCELINEITEMID = REFUNDMEMBERSHIPLI.SOURCELINEITEMID
            and DONATIONLI_EXT.TYPECODE = 0
            and DONATIONLI_EXT.APPLICATIONCODE = 0
    ) CONTRIBUTEDREVENUE
    where REFUNDMEMBERSHIPLI_EXT.CREDITID = @CREDITID
        and CREDITITEMMEMBERSHIPITEMPROMOTION.AMOUNT > 0
        and (isnull(CONTRIBUTEDREVENUE.AMOUNT, 0) <= 0 or CREDITITEMMEMBERSHIPITEMPROMOTION.AMOUNT > CONTRIBUTEDREVENUE.AMOUNT)

    union all

    -- Discounts for contributed revenue on memberships.

    select
        DONATIONLI.ID REVENUESPLITID,
        REFUNDMEMBERSHIPLI.ID CREDITITEMID,
        DISCOUNTLI_EXT.ID DISCOUNTCREDITITEMID,
        'Refund-' + DONATIONLI_EXT.[TYPE] as REFERENCE,
        DONATIONLI.ORGAMOUNT - isnull(PAYMENTDONATIONLI.ORGAMOUNT, 0) AMOUNT
    from dbo.CREDITITEM_EXT REFUNDMEMBERSHIPLI_EXT
    inner join dbo.CREDITITEMMEMBERSHIPITEMPROMOTION on REFUNDMEMBERSHIPLI_EXT.ID = CREDITITEMMEMBERSHIPITEMPROMOTION.CREDITITEMID
    inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on CREDITITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID
    inner join dbo.CREDITITEM_EXT DISCOUNTLI_EXT on DISCOUNTLI_EXT.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM REFUNDMEMBERSHIPLI on REFUNDMEMBERSHIPLI.ID = REFUNDMEMBERSHIPLI_EXT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM MEMBERSHIPLI on MEMBERSHIPLI.ID = REFUNDMEMBERSHIPLI.SOURCELINEITEMID
    inner join dbo.REVENUESPLIT_EXT MEMBERSHIPLI_EXT on MEMBERSHIPLI_EXT.ID = MEMBERSHIPLI.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLI on DONATIONLI.SOURCELINEITEMID = MEMBERSHIPLI.ID
    inner join dbo.REVENUESPLIT_EXT DONATIONLI_EXT on DONATIONLI_EXT.ID = DONATIONLI.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTDONATIONLI on PAYMENTDONATIONLI.SOURCELINEITEMID = DONATIONLI.ID
    left join dbo.REVENUESPLIT_EXT PAYMENTDONATIONLI_EXT on PAYMENTDONATIONLI_EXT.ID = PAYMENTDONATIONLI.ID
    left join dbo.FINANCIALTRANSACTION PAYMENTDONATIONFT on PAYMENTDONATIONFT.ID = PAYMENTDONATIONLI.FINANCIALTRANSACTIONID
    where REFUNDMEMBERSHIPLI_EXT.CREDITID = @CREDITID
        and CREDITITEMMEMBERSHIPITEMPROMOTION.AMOUNT > 0
        and MEMBERSHIPLI_EXT.TYPECODE = 2  --membership

        and MEMBERSHIPLI_EXT.APPLICATIONCODE = 5 --membership

        and DONATIONLI_EXT.TYPECODE = 0 --Gift

        and DONATIONLI_EXT.APPLICATIONCODE = 0 --Donation

        and (PAYMENTDONATIONFT.ID is null or PAYMENTDONATIONFT.TYPECODE = 0) --Standard (don't want to grab refund payments)

        and (PAYMENTDONATIONLI_EXT.ID is null or PAYMENTDONATIONLI_EXT.TYPECODE = 0) --gift

        and (PAYMENTDONATIONLI_EXT.ID is null or PAYMENTDONATIONLI_EXT.APPLICATIONCODE = 10);  --order (payments applied to order)