UFN_REMINDER_LASTPAYMENTDATE

Returns the last date paid for a pledge or a recurring gift.

Return

Return Type
datetime

Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@ASOFDATE	datetime	IN
@TRANSACTIONTYPECODE	tinyint	IN

Definition

Copy



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

  if @TRANSACTIONTYPECODE = 2
  begin
      select @LASTDATE = MAX(R.DATE)
      from dbo.RECURRINGGIFTINSTALLMENTPAYMENT RGIP
      inner join dbo.RECURRINGGIFTINSTALLMENT RGI on RGI.ID = RGIP.RECURRINGGIFTINSTALLMENTID
      inner join dbo.FINANCIALTRANSACTION R on R.ID = RGIP.PAYMENTID
      where RGI.REVENUEID = @REVENUEID
      and R.DATE >= isnull(dbo.UFN_REMINDER_GETLASTREMINDERSENTDATE(@REVENUEID,@TRANSACTIONTYPECODE),R.DATE)
      and R.DATE < @ASOFDATE

      return @LASTDATE
  end

  select @LASTDATE = MAX(R.DATE)
  from dbo.INSTALLMENTSPLITPAYMENT ISP
  inner join dbo.INSTALLMENTSPLIT I on I.ID = ISP.INSTALLMENTSPLITID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.ID = ISP.PAYMENTID
  inner join dbo.FINANCIALTRANSACTION R on R.ID = RS.FINANCIALTRANSACTIONID
  where ISP.PLEDGEID = @REVENUEID
  and R.DATE >= isnull(dbo.UFN_REMINDER_GETLASTREMINDERSENTDATE(@REVENUEID,@TRANSACTIONTYPECODE),R.DATE)
  and R.DATE < @ASOFDATE

  return @LASTDATE
end