UFN_RECURRINGGIFT_GETNEXTINSTALLMENT

CREATE function dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENT
(
    @REVENUEID uniqueidentifier,
    @ASOFDATE date = null
)
returns uniqueidentifier
as begin
  declare @NEXTINSTALLMENTID uniqueidentifier;
  declare @NEXTINSTALLMENTDATE date;

  select @NEXTINSTALLMENTID = ID,
         @NEXTINSTALLMENTDATE = DATE
  from dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(@REVENUEID,@ASOFDATE);

  -- This function was modified to call GETNEXTINSTALLMENTINFO for cases where the recurring gift payment handling rules

  -- are set to apply money to the most recent installment first.  The most recent installment might not exist in the

  -- database at the time of the call.  Callers of this function that don't actually need the ID of the installment

  -- have been modified to call GETNEXTINSTALLMENTINFO directly to get the information they need (DATE and/or BALANCE).

  -- Callers that need to work with an actual installment will still call this UFN to get the installment's ID.

  -- They should call USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS before calling this function in order to ensure that the

  -- next installment exists in the database.  If they do not, this function could return null even though there should

  -- be a next installment to work with - that would be a bug.  I'm raising an error in that case to make it easier

  -- to catch such bugs.

  -- You're not allowed to raise errors in functions explicitly, so I'm using a trick to raise an error...

  if @NEXTINSTALLMENTID is null and @NEXTINSTALLMENTDATE is not null
    declare @ERR int = 'The next installment ID cannot be returned because the record has not been created in the database yet.  The calling code needs to call USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS to add them.  (Ignore the datatype conversion error.)';

  return @NEXTINSTALLMENTID;
end