UFN_RECURRINGGIFT_GETREMAININGBALANCE

            CREATE function dbo.UFN_RECURRINGGIFT_GETREMAININGBALANCE
            (
                @ID uniqueidentifier
            )
            returns money
            with execute as caller
            as begin
              declare @CURRENTINSTALLMENTDATE datetime;
              declare @RGENDDATE datetime;
              declare @BALANCE money = 0.00
              declare @INSTALLMENTID uniqueidentifier;
              declare @RGAMOUNT money = 0.00

              select @RGENDDATE = dbo.UFN_DATE_GETLATESTTIME(ENDDATE)
              from dbo.REVENUESCHEDULE
              where ID = @ID

              if @RGENDDATE is null
                return null;

              select @RGAMOUNT = TRANSACTIONAMOUNT
              from dbo.FINANCIALTRANSACTION
              where ID = @ID

              select top 1 @INSTALLMENTID = ID,
                           @CURRENTINSTALLMENTDATE = DATE
              from dbo.RECURRINGGIFTINSTALLMENT
              where REVENUEID = @ID
              and STATUSCODE in(0,1)
              order by DATE;

              set @BALANCE = dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(@INSTALLMENTID)

              set @CURRENTINSTALLMENTDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@ID, @CURRENTINSTALLMENTDATE)

              while @CURRENTINSTALLMENTDATE <= @RGENDDATE
              begin
                select top 1 @INSTALLMENTID = ID
                from dbo.RECURRINGGIFTINSTALLMENT
                where REVENUEID = @ID
                and STATUSCODE in(0,1)
                and DATE >= @CURRENTINSTALLMENTDATE
                order by DATE;

                if @INSTALLMENTID is not null
                  set @BALANCE = @BALANCE + dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(@INSTALLMENTID)
                else
                  set @BALANCE = @BALANCE + @RGAMOUNT

                set @CURRENTINSTALLMENTDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@ID, @CURRENTINSTALLMENTDATE)
              end

              return @BALANCE
            end