UFN_CREDIT_REFUND_GETMERCHANDISETOTAL2

CREATE function dbo.UFN_CREDIT_REFUND_GETMERCHANDISETOTAL2
(
    @CREDITID uniqueidentifier = null
)
returns money
as begin
return (
    select coalesce((

        -- Old-style refunds. We know the order-level discount was reversed if the merchandise was returned.


        select coalesce(sum(FTLI.BASEAMOUNT - coalesce(ORDERLEVELDISCOUNTS.AMOUNT, 0)), 0)
        from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
        inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = FTLI.ID
        outer apply (
            select sum(AMOUNT) AMOUNT
            from dbo.SALESORDERITEMORDERDISCOUNTDETAIL ODD
            where ODD.SALESORDERITEMID = EXT.SALESORDERITEMID
        ) ORDERLEVELDISCOUNTS
        where 
            FTLI.FINANCIALTRANSACTIONID = @CREDITID
            and EXT.TYPECODE = 14  -- Merchandise

            and EXT.SALESORDERITEMIZEDITEMID is null

    ),0) + coalesce((

        -- New-style (itemized) refunds. We don't automatically know whether order-level discounts were returned.


        select coalesce(sum(case when FTLI.BASEAMOUNT > MERCHUNIT.AMOUNTPAID then MERCHUNIT.AMOUNTPAID else FTLI.BASEAMOUNT end), 0)
        from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
        inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = FTLI.ID
        inner join dbo.SALESORDERITEMMERCHANDISEUNIT MERCHUNIT on MERCHUNIT.ID = EXT.SALESORDERITEMIZEDITEMID
        where 
            FTLI.FINANCIALTRANSACTIONID = @CREDITID
            and EXT.TYPECODE = 14  -- Merchandise


    ),0)
)
end