UFN_CREDIT_REFUND_GETADJUSTMENTTOTAL

CREATE function dbo.UFN_CREDIT_REFUND_GETADJUSTMENTTOTAL
(
    @CREDITID uniqueidentifier
)
returns money
as begin
    declare @ADJUSTMENTTOTAL money = 0;

    -- Refunded Fees

    select
        @ADJUSTMENTTOTAL = coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0)
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.SALESORDERITEMFEE on SALESORDERITEMFEE.ID = EXT.SALESORDERITEMID
    where
        FT.ID = @CREDITID
        and FT.TYPECODE = 23  -- Refund

        and SALESORDERITEMFEE.APPLIESTOCODE = 0;

    -- Refunded Taxes, Discounts

    select
        @ADJUSTMENTTOTAL += coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0)
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    where
        FT.ID = @CREDITID
        and FT.TYPECODE = 23  -- Refund

        and SALESORDERITEM.TYPECODE in (4,5);

    return @ADJUSTMENTTOTAL;
end