UFN_PAYMENT_AMOUNTS

CREATE function dbo.UFN_PAYMENT_AMOUNTS(@FINANCIALTRANSACTIONID uniqueidentifier)
returns table
as return (
    with REFUNDS_CTE as (
        select isnull(sum(AMOUNT), 0) as REFUNDED
        from dbo.CREDITPAYMENT
        where REVENUEID = @FINANCIALTRANSACTIONID
    ),
    APPLIED_CTE as (
        select
            isnull(
                sum(
                    case REVENUESPLIT_EXT.TYPECODE
                        when 20 then 0  -- Overage

                        else LI.BASEAMOUNT
                    end
                ), 0) as APPLIED,
            isnull(
                sum(
                    case REVENUESPLIT_EXT.TYPECODE
                        when 20 then LI.BASEAMOUNT  -- Overage

                        else 0
                    end
                ), 0) as OVERAGEKEPT
        from
            dbo.FINANCIALTRANSACTIONLINEITEM as LI
        inner join
            dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = LI.ID
        where
            LI.FINANCIALTRANSACTIONID = @FINANCIALTRANSACTIONID
            and LI.DELETEDON is null
            and LI.TYPECODE = 0  -- Standard

            and REVENUESPLIT_EXT.TYPECODE <> 19  -- Unearned revenue

    )
    select
        isnull(FT.BASEAMOUNT, 0) as AMOUNT,
        REFUNDS_CTE.REFUNDED as REFUNDED,
        APPLIED_CTE.APPLIED as APPLIED,
        APPLIED_CTE.OVERAGEKEPT as OVERAGEKEPT,
        isnull((FT.BASEAMOUNT - REFUNDS_CTE.REFUNDED - APPLIED_CTE.APPLIED - APPLIED_CTE.OVERAGEKEPT), 0) as OVERAGE
    from
        dbo.FINANCIALTRANSACTION as FT
    outer apply
        REFUNDS_CTE
    outer apply
        APPLIED_CTE
    where
        FT.ID = @FINANCIALTRANSACTIONID
)