UFN_FINANCIALTRANSACTIONSCHEDULE_GETBALANCE

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

  select 
    @BALANCE = FTS.AMOUNT - COALESCE(sum(FTA.AMOUNT) ,0)
  from 
    dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
    left outer join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA 
      on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID 
      and FTA.STATUSCODE = 1 -- applied  

  where
    FTS.ID = @ID and FTS.DELETED = 0
  group by 
    FTS.ID, FTS.AMOUNT;   

    return coalesce(@BALANCE,0);
end;