USP_DATALIST_CREDIT_MERCHANDISE2

CREATE procedure dbo.USP_DATALIST_CREDIT_MERCHANDISE2
(
    @CREDITID uniqueidentifier
)
as
    set nocount on;

    -- Old-style refunds (Pre-2014 S1)

    select
        EXT.SALESORDERITEMID ID,
        SALESORDERITEM.[DESCRIPTION] + ' (' + convert(varchar, convert(int, FTLI.QUANTITY)) + ')' as [DESCRIPTION],
        MERCHANDISEPRODUCTINSTANCE.BARCODE,
        SALESORDERITEM.PRICE * FTLI.QUANTITY as PRICE,
        EXT.DISCOUNTS + isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) as DISCOUNTS,  -- EXT.DISCOUNTS only includes item-level discounts

        FTLI.BASEAMOUNT - isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) as AMOUNTPAID,  -- Hacky. Old-style refunds forced you to refund the whole discounted item, so PAID and REFUNDED are the same.

        FTLI.BASEAMOUNT - isnull(ORDERLEVELDISCOUNTS.AMOUNT, 0) as AMOUNTREFUNDED,
        SALESORDERITEM.SALESORDERID
    from dbo.CREDITITEM_EXT EXT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = EXT.ID
    inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    inner join dbo.SALESORDERITEMMERCHANDISE MERCHITEM on MERCHITEM.ID = SALESORDERITEM.ID
    inner join dbo.MERCHANDISEPRODUCTINSTANCE on MERCHANDISEPRODUCTINSTANCE.ID = MERCHITEM.MERCHANDISEPRODUCTINSTANCEID
    outer apply (
        select sum(AMOUNT) AMOUNT
        from dbo.SALESORDERITEMORDERDISCOUNTDETAIL ODD
        where ODD.SALESORDERITEMID = EXT.SALESORDERITEMID
    ) ORDERLEVELDISCOUNTS
    where CREDITID = @CREDITID
        and EXT.SALESORDERITEMIZEDITEMID is null

    union all

    -- New-style refunds (Post-2014 S1)

    select
        EXT.SALESORDERITEMID ID,
        SALESORDERITEM.[DESCRIPTION],
        MERCHANDISEPRODUCTINSTANCE.BARCODE,
        SALESORDERITEM.PRICE * FTLI.QUANTITY as PRICE,
        MERCHUNIT.ITEMLEVELDISCOUNTSAPPLIED + ORDERLEVELDISCOUNTSAPPLIED as DISCOUNTS,
        MERCHUNIT.AMOUNTPAID,
        case when FTLI.BASEAMOUNT > MERCHUNIT.AMOUNTPAID then MERCHUNIT.AMOUNTPAID else FTLI.BASEAMOUNT end AMOUNTREFUNDED,  -- Accounting for order-level discounts.

        SALESORDERITEM.SALESORDERID
    from dbo.CREDITITEM_EXT EXT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = EXT.ID
    inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    inner join dbo.SALESORDERITEMMERCHANDISE MERCHITEM on MERCHITEM.ID = SALESORDERITEM.ID
    inner join dbo.MERCHANDISEPRODUCTINSTANCE on MERCHANDISEPRODUCTINSTANCE.ID = MERCHITEM.MERCHANDISEPRODUCTINSTANCEID
    inner join dbo.SALESORDERITEMMERCHANDISEUNIT MERCHUNIT on MERCHUNIT.ID = EXT.SALESORDERITEMIZEDITEMID
    where CREDITID = @CREDITID;