UFN_REMINDER_GETPAIDTHROUGHDATE

CREATE function dbo.UFN_REMINDER_GETPAIDTHROUGHDATE(
@REVENUEID uniqueidentifier,
@TRANSACTIONTYPECODE tinyint
)
returns datetime
with execute as caller
as begin
    declare @PAIDTHROUGH datetime

  if @TRANSACTIONTYPECODE = 2
  begin
    select @PAIDTHROUGH = max(RECURRINGGIFTINSTALLMENT.DATE) from dbo.RECURRINGGIFTINSTALLMENT
    where REVENUEID = @REVENUEID and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID) = 0

    return @PAIDTHROUGH
  end

  --Reworked to account for pledge overpayment to balance

  --Find the first unpaid installment. If the installment is not the first one, return the date of the previous installment. 

  --If the first unpaid installment is null, return the date of the last installment (it is fully paid off).

  --Else, return null.

  declare @FIRSTUNPAIDINSTALLMENTID uniqueidentifier;
  declare @FIRSTUNPAIDINSTALLMENTSEQUENCE int;

  select top 1 @FIRSTUNPAIDINSTALLMENTID = ID, @FIRSTUNPAIDINSTALLMENTSEQUENCE = SEQUENCE
  from dbo.INSTALLMENT
  where REVENUEID = @REVENUEID
  and dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(ID) > 0
  order by DATE asc;

  if @FIRSTUNPAIDINSTALLMENTID is not null and @FIRSTUNPAIDINSTALLMENTSEQUENCE > 1
        select @PAIDTHROUGH = DATE
        from dbo.INSTALLMENT
        where REVENUEID = @REVENUEID and SEQUENCE = (@FIRSTUNPAIDINSTALLMENTSEQUENCE - 1)
  else if @FIRSTUNPAIDINSTALLMENTID is null
        select @PAIDTHROUGH = max(DATE)
        from dbo.INSTALLMENT
        where REVENUEID = @REVENUEID
  else
        select @PAIDTHROUGH = null;

  return @PAIDTHROUGH;

end