UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS_ASOFDATE

CREATE function dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS_ASOFDATE(
        @ID uniqueidentifier,
    @ASOFDATE date
)
returns @INSTALLMENTS table (DATE date)
as begin
    declare @LASTINSTALLMENTDATE date;
    declare @NEXTINSTALLMENTDATE date;
    declare @ENDDATE date;
    declare @STATUSCODE tinyint;

  if @ASOFDATE is null
    set @ASOFDATE = getdate();

    select @ENDDATE = ENDDATE,
           @STATUSCODE = STATUSCODE
    from dbo.REVENUESCHEDULE
    where ID = @ID;

    -- return missing installments if RG is active or Held or Lapsed

  -- or if the status is Canceled and the end date is null - that means we're in the middle of the edit removing the end date

    if @STATUSCODE in(0,1,5) or (@ENDDATE is null and @STATUSCODE = 3)
    begin
        select @LASTINSTALLMENTDATE = max(RECURRINGGIFTINSTALLMENT.DATE)
        from dbo.RECURRINGGIFTINSTALLMENT
        where REVENUEID = @ID;

        while @LASTINSTALLMENTDATE < @ASOFDATE
        begin
            set @NEXTINSTALLMENTDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@ID,@LASTINSTALLMENTDATE)

            if @NEXTINSTALLMENTDATE > @ENDDATE
                break;

            insert into @INSTALLMENTS (DATE)
            values (@NEXTINSTALLMENTDATE);

            set @LASTINSTALLMENTDATE = @NEXTINSTALLMENTDATE
        end
    end

    return;
end