UFN_INVOICE_GETBALANCE

CREATE function dbo.UFN_INVOICE_GETBALANCE( @ID uniqueidentifier
)
returns money
with execute as caller
as begin
    declare @BALANCE money;

    select 
        @BALANCE = max(FT.TRANSACTIONAMOUNT) - COALESCE(sum(FTA.AMOUNT),0)
    from FINANCIALTRANSACTION as FT 
    inner join INVOICE as I 
        on FT.ID = I.ID
    inner join FINANCIALTRANSACTIONSCHEDULE as FTS 
        on FTS.FINANCIALTRANSACTIONID = FT.ID and FTS.DELETED = 0
    left outer join FINANCIALTRANSACTIONAPPLICATION as FTA
        on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID and FTA.STATUSCODE = 1
    where
        FTS.FINANCIALTRANSACTIONID = @ID
        and I.ZEROBALANCE=0;

    return coalesce(@BALANCE,0);
end