UFN_RECURRINGGIFT_GETNEXTINSTALLMENTIGNOREPAYMENT_2

create function dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTIGNOREPAYMENT_2(
  @REVENUEID uniqueidentifier,
  @PAYMENTID uniqueidentifier,
  @ASOFDATE date,
  @EFFECTIVEDATETIME datetime
)
returns uniqueidentifier
as begin
  set @EFFECTIVEDATETIME = isnull(@EFFECTIVEDATETIME,getdate());

  declare @EFFECTIVEDATE date = cast(@EFFECTIVEDATETIME as date);

  return (
    select top 1 i.ID
    from dbo.RECURRINGGIFTINSTALLMENT i
    cross apply dbo.UFN_RECURRINGGIFTSETTING_GETFOREFFECTIVEDATE(@EFFECTIVEDATETIME) s
    where i.REVENUEID = @REVENUEID
    and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT(i.ID,@PAYMENTID) > 0
    and (i.DATE >= isnull(@ASOFDATE,i.DATE) or s.FIRSTINSTALLMENTCODE = 1)
    order by case when i.DATE <= @EFFECTIVEDATE then 1 else 2 end,                                    -- today/past, then future

             case when i.DATE > @EFFECTIVEDATE or s.FIRSTINSTALLMENTCODE = 0 then i.DATE end,         -- date asc if appropriate (past installments by rule, future installments always)

             case when i.DATE <= @EFFECTIVEDATE and s.FIRSTINSTALLMENTCODE = 1 then i.DATE end desc   -- date desc if appropriate

  )
end