UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCEBYDESIGNATION

create function dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCEBYDESIGNATION(
  @INSTALLMENTSPLITID uniqueidentifier
  ,@DESIGNATIONID uniqueidentifier
  )
returns money
  with execute as caller
as
begin
  declare @BALANCE money;

  select @BALANCE = INSTALLMENTSPLIT.TRANSACTIONAMOUNT - (
      coalesce((
          select sum(ISP.AMOUNT)
          from dbo.INSTALLMENTSPLITPAYMENT ISP
          inner join INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = ISP.INSTALLMENTSPLITID
          where ISP.INSTALLMENTSPLITID = @INSTALLMENTSPLITID
            and @DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
          ), 0) + coalesce((
          select sum(ISWO.TRANSACTIONAMOUNT)
          from dbo.INSTALLMENTSPLITWRITEOFF ISWO
          inner join INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = ISWO.INSTALLMENTSPLITID
          where ISWO.INSTALLMENTSPLITID = @INSTALLMENTSPLITID
            and @DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
          ), 0)
      )
  from dbo.INSTALLMENTSPLIT
  where INSTALLMENTSPLIT.ID = @INSTALLMENTSPLITID;

  return @BALANCE;
end