UFN_PLEDGE_GETBALANCE

CREATE function [dbo].[UFN_PLEDGE_GETBALANCE]
(
  @PLEDGEID uniqueidentifier
) 
returns money
with execute as caller
as begin
  declare @RESULT money;

  /****************************************************************************/
  --NOTE: The following objects inline balance calculations, so any updates

  --to the way pledge balances are calculated must be made in these as well.

  --

  --UFN_REVENUE_CONSTITUENTSWITHCOMMITMENTS

  --UFN_QUERY_PLEDGEREMINDEROUTPUT

  --UFN_QUERY_PLEDGEREMINDEROUTPUTDETAILS2

  --V_QUERY_PLEDGEREMINDERPROCESS

  --V_QUERY_PLEDGEREMINDERPROCESS_SEASONALADDRESSOUTPUT

  /****************************************************************************/

  select 
    @RESULT = 
      case when FINANCIALTRANSACTION.TYPECODE = 7 then --Auction donations do not have installments

        FINANCIALTRANSACTION.TRANSACTIONAMOUNT - 
        isnull((select 
                  sum(FTLI.TRANSACTIONAMOUNT) 
                from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
                inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
                where FT.PARENTID = FINANCIALTRANSACTION.ID
                and FT.TYPECODE = 20 and FTLI.DELETEDON is null
               ), 0)
      else
        FINANCIALTRANSACTION.TRANSACTIONAMOUNT - 
        (
          isnull((select 
                    sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                  from dbo.INSTALLMENTSPLITPAYMENT 
                  where INSTALLMENTSPLITPAYMENT.PLEDGEID = @PLEDGEID), 0) + 
          isnull((select
                    sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) 
                  from dbo.INSTALLMENTSPLITWRITEOFF 
                  inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                  where INSTALLMENTSPLIT.PLEDGEID = @PLEDGEID), 0)
        )
      end
  from dbo.FINANCIALTRANSACTION
  where FINANCIALTRANSACTION.ID = @PLEDGEID;

  return @RESULT;
end