UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT

      CREATE function dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT
      (
        @INSTALLMENTID uniqueidentifier,
        @PAYMENTID uniqueidentifier
      ) 
      returns money
      with execute as caller
      as begin
        declare @BALANCE money;

        with WRITEOFF as (
          select max(w.TYPECODE) TYPECODE, sum(iw.TRANSACTIONAMOUNT) AMOUNT
          from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF iw
          inner join dbo.RECURRINGGIFTWRITEOFF w on w.ID = iw.WRITEOFFID
          where iw.RECURRINGGIFTINSTALLMENTID = @INSTALLMENTID
          and (w.PAYMENTID <> @PAYMENTID or w.PAYMENTID is null)
        )
        select 
          @BALANCE = case when WRITEOFF.TYPECODE = 1 then 0 else
            isnull(e.OLDAMOUNT,i.TRANSACTIONAMOUNT)
              - coalesce(
                (
                  select sum(RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT) 
                  from dbo.RECURRINGGIFTINSTALLMENTPAYMENT 
                  where RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = @INSTALLMENTID
                    and RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID <> @PAYMENTID
                )
              , 0) 
              - coalesce(WRITEOFF.AMOUNT, 0) end
        from dbo.RECURRINGGIFTINSTALLMENT i
        left join dbo.RECURRINGGIFTINSTALLMENTEVENT e on e.RECURRINGGIFTINSTALLMENTID = i.ID and e.PAYMENTID = @PAYMENTID
        outer apply WRITEOFF
        where i.ID = @INSTALLMENTID;

        return @BALANCE;
      end