UFN_CREDIT_REFUND_GETTICKETTOTAL2

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

        -- Old-style refunds. We know the order-level discount was reversed if the ticket 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 = 0  -- Ticket

            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 > TICKET.AMOUNTPAID then TICKET.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.TICKET on TICKET.ID = EXT.SALESORDERITEMIZEDITEMID
        where 
            FTLI.FINANCIALTRANSACTIONID = @CREDITID
            and EXT.TYPECODE = 0  -- Ticket


    ),0)
)
end