USP_RECURRINGGIFT_APPLYPAYMENTTOINSTALLMENTS

CREATE procedure dbo.USP_RECURRINGGIFT_APPLYPAYMENTTOINSTALLMENTS (
  @APPLICATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTDATE datetime,
  @PAYMENTID uniqueidentifier,
  @APPLIEDAMOUNT money,
  @NEXTTRANSACTIONDATE date output,
  @APPLICATIONCURRENCYID uniqueidentifier = null,
  @APPLICATIONEXCHANGERATEID uniqueidentifier = null,
  @PREVIOUSLYAPPLIEDAMOUNT money = null,
  @PREVIOUSLYAPPLIEDAMOUNTCONVERTED money = null,  --deprecated

  @PAYMENTDATE date = null,
  @APPLICATIONEFFECTIVEDATETIME datetime = null
)
as
begin
  set nocount on;

  if @APPLICATIONCURRENCYID is null
    set @APPLICATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  if not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @APPLICATIONID)
  begin
    exec dbo.USP_RECURRINGGIFT_FIXMISSINGINSTALLMENTSCHEDULE @APPLICATIONID, @CHANGEAGENTID, @CURRENTDATE
  end

  set @NEXTTRANSACTIONDATE = null;

  declare @RGAMOUNT money;
  declare @RGTRANSACTIONAMOUNT money;
  declare @RGORGANIZATIONAMOUNT money;
  declare @RGTRANSACTIONCURRENCYID uniqueidentifier;
  declare @RGBASECURRENCYID uniqueidentifier;
  declare @RGBASEEXCHANGERATEID uniqueidentifier;
  declare @RGORGANIZATIONEXCHANGERATEID uniqueidentifier;

  declare @LASTINSTALLMENTDATE date;

  select
    @RGAMOUNT = AMOUNT,
    @RGTRANSACTIONAMOUNT = TRANSACTIONAMOUNT,
    @RGORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT,
    @RGTRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
    @RGBASECURRENCYID = BASECURRENCYID,
    @RGBASEEXCHANGERATEID = BASEEXCHANGERATEID,
    @RGORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
  from dbo.REVENUE
  where ID = @APPLICATIONID;

  exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS
    @APPLICATIONID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @RGAMOUNT,
    @LASTINSTALLMENTDATE output,
    @RGBASECURRENCYID,
    @RGORGANIZATIONAMOUNT,
    @RGORGANIZATIONEXCHANGERATEID,
    @RGTRANSACTIONAMOUNT,
    @RGTRANSACTIONCURRENCYID,
    @RGBASEEXCHANGERATEID;

  declare @INSTALLMENTID uniqueidentifier;
  declare @INSTALLMENTBALANCE money;
  declare @INSTALLMENTAMOUNT money;
  declare @INSTALLMENTDATE date;
  declare @EXISTINGPAYMENTID uniqueidentifier;
  declare @EXISTINGWRITEOFFID uniqueidentifier;
  declare @EXISTINGADJUSTMENTID uniqueidentifier;
  declare @APPLYREMAINING bit;

  declare @NEXTINSTALLMENTEXISTS bit;
  declare @FETCHROW smallint;

  declare @APPLIEDAMOUNTCONVERTED money;
  declare @AMOUNTPAIDCONVERTED money;
  declare @AMOUNTLEFTCONVERTED money;
  declare @PAYAMOUNTCONVERTED money;

  declare @PAYMENTBASECURRENCYID uniqueidentifier;
  declare @PAYMENTTRANSACTIONCURRENCYID uniqueidentifier;
  declare @PAYMENTBASEEXCHANGERATEID uniqueidentifier;
  declare @PAYMENTORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @PAYMENTRANSACTIONCURRENCYDECIMALDIGITS tinyint;

  declare @REVENUEDATE datetime;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  -- The APPLICATIONEFFECTIVEDATE is the date we are treating as "today" for the payment handling rules.

  -- On a new application, this date will always be the current date.

  -- When adding money to an existing application, this will be the date the application was first created.

  -- The only place where the behavior will be inconsistent regarding the use of this date to delineate past vs. future

  -- is the installment underpayment rule - we will still write-off or adjust an installment in the past (in relation to today),

  -- even if it's after the application effective date.  This is noted again below.

  if @APPLICATIONEFFECTIVEDATETIME is null
    set @APPLICATIONEFFECTIVEDATETIME = @CURRENTDATE;

  declare @APPLICATIONEFFECTIVEDATE date = @APPLICATIONEFFECTIVEDATETIME;

  -- payment handling rules

  declare @FIRSTINSTALLMENTCODE tinyint,
          @INSTALLMENTUNDERPAYMENTCODE tinyint,
     @APPLYTOPASTINSTALLMENTS bit,
          @PASTBALANCEUNDERPAYMENTCODE tinyint,
          @OVERPAYMENTCODE tinyint;

  if dbo.UFN_RECURRINGGIFT_ISMEMBERSHIP(@APPLICATIONID) = 1
    select @FIRSTINSTALLMENTCODE = 0,                --oldest first

           @INSTALLMENTUNDERPAYMENTCODE = 0,         --leave balance

           @APPLYTOPASTINSTALLMENTS = 1,          
           @PASTBALANCEUNDERPAYMENTCODE = 0,         --leave balance

           @OVERPAYMENTCODE = 0                      --apply to future

  else
  begin
    declare @USEEFFECTIVEDATE datetime;

    if @PREVIOUSLYAPPLIEDAMOUNT is not null
      set @USEEFFECTIVEDATE = @APPLICATIONEFFECTIVEDATETIME;

    select @FIRSTINSTALLMENTCODE = FIRSTINSTALLMENTCODE,                --0=oldest first, 1=most recent first

           @INSTALLMENTUNDERPAYMENTCODE = INSTALLMENTUNDERPAYMENTCODE,  --0=leave balance, 1=write-off, 2=adjust

           @APPLYTOPASTINSTALLMENTS = APPLYTOPASTINSTALLMENTS,          
           @PASTBALANCEUNDERPAYMENTCODE = PASTBALANCEUNDERPAYMENTCODE,  --0=leave balance, 1=write-off, 2=adjust

           @OVERPAYMENTCODE = OVERPAYMENTCODE                           --0=apply to future, 1=adjust, 2=donation(handled at a higher level)

    from dbo.UFN_RECURRINGGIFTSETTING(@USEEFFECTIVEDATE);
  end

  select
    @REVENUEDATE = cast(FINANCIALTRANSACTION.DATE as datetime),
    @PAYMENTBASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
    @PAYMENTTRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    @PAYMENTBASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
    @PAYMENTORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID,
    @PAYMENTRANSACTIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS
  from
    dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
    inner join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID
  where
    FINANCIALTRANSACTION.ID = @PAYMENTID
        and FINANCIALTRANSACTION.DELETEDON is null;

  if @APPLICATIONEXCHANGERATEID is null
    set @APPLIEDAMOUNTCONVERTED = (isnull(@PREVIOUSLYAPPLIEDAMOUNT,0) + @APPLIEDAMOUNT);
  else
    set @APPLIEDAMOUNTCONVERTED = dbo.UFN_CURRENCY_CONVERT((isnull(@PREVIOUSLYAPPLIEDAMOUNT,0) + @APPLIEDAMOUNT), @APPLICATIONEXCHANGERATEID);

  set @PAYAMOUNTCONVERTED =
    coalesce(
      (
        select
          sum(RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT)
        from
          dbo.RECURRINGGIFTINSTALLMENT
          inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on
            RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
            and RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = @PAYMENTID
        where
          RECURRINGGIFTINSTALLMENT.REVENUEID = @APPLICATIONID
      ), 0);

  set @AMOUNTPAIDCONVERTED = @PAYAMOUNTCONVERTED;
  set @AMOUNTLEFTCONVERTED = @APPLIEDAMOUNTCONVERTED - @PAYAMOUNTCONVERTED;

  -- Update installments with no balance to use the new exchange rate and account for their amounts before handling installments that

  -- have a remaining balance.

  -- Since the transaction currency of the payment and the recurring gift cannot change we know

  -- that edited payments previously used a null application exchange rate if the current rate is null

  if @APPLICATIONEXCHANGERATEID is not null
    update dbo.RECURRINGGIFTINSTALLMENTPAYMENT
    set
      APPLICATIONEXCHANGERATEID = @APPLICATIONEXCHANGERATEID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    from
      dbo.RECURRINGGIFTINSTALLMENT
      left join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on
        RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
        and RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = @PAYMENTID
    where
      RECURRINGGIFTINSTALLMENT.REVENUEID = @APPLICATIONID
      and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RECURRINGGIFTINSTALLMENT.ID) <= 0
      and RECURRINGGIFTINSTALLMENTPAYMENT.APPLICATIONEXCHANGERATEID <> @APPLICATIONEXCHANGERATEID;

  declare @WRITEOFFAMOUNT money;
  declare @ADJUSTINSTALLMENT bit;
  declare @NEWINSTALLMENTAMOUNT money;

  set @NEXTINSTALLMENTEXISTS = 0;
  set @FETCHROW = 0;

  declare INSTALLMENTCURSOR cursor local fast_forward for
    with INSTALLMENTS as (
      select RGI.ID,
             dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RGI.ID) +
              isnull(W.TRANSACTIONAMOUNT,0) +                           -- add any payment-handling writeoff to the balance

              case when E.OLDAMOUNT > E.NEWAMOUNT then E.OLDAMOUNT - E.NEWAMOUNT else 0 end BALANCE,  -- add any payment-handling adjustment to the balance

             -- if there is an underpayment adjustment, use the original amount, otherwise use the current installment amount

             isnull(case when RGI.TRANSACTIONAMOUNT < E.OLDAMOUNT then E.OLDAMOUNT end,RGI.TRANSACTIONAMOUNT) INSTALLMENTAMOUNT,
             RGI.DATE,
             -- for edits

             RGIP.ID PAYMENTID,
             W.ID WRITEOFFID,
             E.ID ADJUSTMENTID,
             case when RGI.STATUSCODE in(0,1) or W.TRANSACTIONAMOUNT > 0 or E.OLDAMOUNT > E.NEWAMOUNT then 1 else 0 end HASBALANCE
      from dbo.RECURRINGGIFTINSTALLMENT RGI
      left join dbo.RECURRINGGIFTINSTALLMENTPAYMENT RGIP on RGIP.RECURRINGGIFTINSTALLMENTID = RGI.ID and RGIP.PAYMENTID = @PAYMENTID
      outer apply (select W.ID, IW.TRANSACTIONAMOUNT
                   from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF IW 
                   inner join dbo.RECURRINGGIFTWRITEOFF W on W.ID = IW.WRITEOFFID
                   where IW.RECURRINGGIFTINSTALLMENTID = RGI.ID
                   and W.PAYMENTID = @PAYMENTID) W
      left join dbo.RECURRINGGIFTINSTALLMENTEVENT E on E.RECURRINGGIFTINSTALLMENTID = RGI.ID and E.PAYMENTID = @PAYMENTID
      where RGI.REVENUEID = @APPLICATIONID
    )
    select ID, BALANCE, INSTALLMENTAMOUNT, DATE, PAYMENTID, WRITEOFFID, ADJUSTMENTID, MOSTRECENTINSTALLMENTREPEATED
    from (select ID, BALANCE, INSTALLMENTAMOUNT, DATE, PAYMENTID, WRITEOFFID, ADJUSTMENTID, 0 MOSTRECENTINSTALLMENTREPEATED
          from INSTALLMENTS
          where HASBALANCE = 1
          -- we're increasing an existing payment amount, the previous amount fully paid an installment, and the overpayment should adjust that installment

          or ((@APPLYTOPASTINSTALLMENTS = 0 or DATE > @APPLICATIONEFFECTIVEDATE) and @OVERPAYMENTCODE = 1 and PAYMENTID is not null)
          union all
          -- Repeat the most recent installment if the payment handling rules dictate that money

          -- should be applied to the most recent installment first, then other past installments,

          -- then any remaining should be applied to the most recent installment with an amount adjustment.

          -- The balance of this installment will be 0 at the time it is accessed again.

          select ID, 0, INSTALLMENTAMOUNT, DATE, PAYMENTID, WRITEOFFID, ADJUSTMENTID, 1
          from (select top 1 ID, INSTALLMENTAMOUNT, DATE, PAYMENTID, WRITEOFFID, ADJUSTMENTID
                from INSTALLMENTS
                where DATE <= @APPLICATIONEFFECTIVEDATE
                and @APPLYTOPASTINSTALLMENTS = 1
                and @OVERPAYMENTCODE = 1
                and (HASBALANCE = 1 or PAYMENTID is not null)
                order by DATE desc) m) i
    order by case when DATE <= @APPLICATIONEFFECTIVEDATE then 1 else 2 end,                                 -- today/past, then future

             MOSTRECENTINSTALLMENTREPEATED,                                                                 -- repeated most recent installment after other today/past installments

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

             case when DATE <= @APPLICATIONEFFECTIVEDATE and @FIRSTINSTALLMENTCODE = 1 then DATE end desc;  -- date desc if appropriate


  open INSTALLMENTCURSOR;
  fetch next from INSTALLMENTCURSOR into @INSTALLMENTID, @INSTALLMENTBALANCE, @INSTALLMENTAMOUNT, @INSTALLMENTDATE, @EXISTINGPAYMENTID, @EXISTINGWRITEOFFID, @EXISTINGADJUSTMENTID, @APPLYREMAINING;

  while @@FETCH_STATUS = 0
  begin
    set @FETCHROW = @FETCHROW + 1;
    set @WRITEOFFAMOUNT = 0;
    set @ADJUSTINSTALLMENT = 0;

    -- exit the loop if we have applied all the money

    if @AMOUNTLEFTCONVERTED = 0 and
       @FETCHROW > 1 and                                    -- we will apply a $0 payment to the first installment, so don't exit yet

       (@INSTALLMENTDATE > @APPLICATIONEFFECTIVEDATE or
        @PASTBALANCEUNDERPAYMENTCODE = 0 or
        @APPLYREMAINING = 1)                                -- we don't need to write-off or adjust this installment

    begin
      set @NEXTINSTALLMENTEXISTS = 1;
      set @NEXTTRANSACTIONDATE = coalesce(@NEXTTRANSACTIONDATE,@INSTALLMENTDATE);
      break;
    end

    -- we have money left to apply (@FETCHROW=1 is to apply a $0 payment to the first installment)

    if @AMOUNTLEFTCONVERTED > 0 or @FETCHROW = 1
    begin
      -- check for donation error (we have money left after applying money to all appropriate installments per the rules)

      if @OVERPAYMENTCODE = 2 and
         @AMOUNTLEFTCONVERTED > 0 and
         @FETCHROW > 1 and
         (@APPLYTOPASTINSTALLMENTS = 0 or
          (@APPLYTOPASTINSTALLMENTS = 1 and @INSTALLMENTDATE > @APPLICATIONEFFECTIVEDATE))
        raiserror('More money is being applied to the recurring gift than is allowed according to the payment handling rules; the excess should have been split off as a donation.', 13, 1);

      -- determine amount to apply, and any write-off or adjustment actions needed for this installment

      if @AMOUNTLEFTCONVERTED <= @INSTALLMENTBALANCE or           -- exact amount or underpayment of current installment

         ((@APPLYTOPASTINSTALLMENTS = 0 or @INSTALLMENTDATE > @APPLICATIONEFFECTIVEDATE) and @OVERPAYMENTCODE = 1)  -- adjust amount for overpayment (thus making this an exact payment)

      begin
        set @PAYAMOUNTCONVERTED = @AMOUNTLEFTCONVERTED;

        if @AMOUNTLEFTCONVERTED < @INSTALLMENTBALANCE
        begin
          set @NEXTINSTALLMENTEXISTS = 1;
          set @NEXTTRANSACTIONDATE = @INSTALLMENTDATE;

          if @INSTALLMENTUNDERPAYMENTCODE = 2  -- adjust amount

            set @ADJUSTINSTALLMENT = 1;

          -- write-off rule only applies to past/today installments

          -- this is intentionally @CURRENTDATE, not @APPLICATIONEFFECTIVEDATE - we still want to apply the installment underpayment rule for

          -- any past installment, even if it's after the application effective date

          else if @INSTALLMENTUNDERPAYMENTCODE = 1 and @INSTALLMENTDATE <= @CURRENTDATE  -- write-off

            set @WRITEOFFAMOUNT = @INSTALLMENTBALANCE - @AMOUNTLEFTCONVERTED;
        end
        else if @AMOUNTLEFTCONVERTED > @INSTALLMENTBALANCE
          set @ADJUSTINSTALLMENT = 1;
      end
      else if @APPLYREMAINING = 1
      begin
        -- to be here:

        -- the overpayment rule is "adjust"

        -- apply to past installments is true

        -- We've applied the payment to all the past installments but still have more money.

        -- Thus we need to apply it to the most recent installment, which has been fetched again from the cursor at this point.

        -- The balance on this installment is 0, so the full remainder will be added to the installment amount below.

        set @PAYAMOUNTCONVERTED = @AMOUNTLEFTCONVERTED;
     set @ADJUSTINSTALLMENT = 1;

        -- add the remaining money to the payment record created for this installment on an earlier loop iteration

        select @EXISTINGPAYMENTID = ID
        from dbo.RECURRINGGIFTINSTALLMENTPAYMENT
        where RECURRINGGIFTINSTALLMENTID = @INSTALLMENTID
        and PAYMENTID = @PAYMENTID;
      end
      else
        -- only apply up to the balance of the installment

        set @PAYAMOUNTCONVERTED = @INSTALLMENTBALANCE;

      set @AMOUNTPAIDCONVERTED = @AMOUNTPAIDCONVERTED + @PAYAMOUNTCONVERTED;
      set @AMOUNTLEFTCONVERTED = @AMOUNTLEFTCONVERTED - @PAYAMOUNTCONVERTED;

      -- delete any existing write-off to make room for more money

      if @EXISTINGWRITEOFFID is not null
      begin
          delete from dbo.RECURRINGGIFTWRITEOFF
          where ID = @EXISTINGWRITEOFFID;

          set @EXISTINGWRITEOFFID = null;
      end

      -- adjust the installment amount if appropriate

      if @ADJUSTINSTALLMENT = 1 or @EXISTINGADJUSTMENTID is not null
      begin
        set @NEWINSTALLMENTAMOUNT = @INSTALLMENTAMOUNT - @INSTALLMENTBALANCE + @PAYAMOUNTCONVERTED;

        exec dbo.USP_RECURRINGGIFT_ADJUSTINSTALLMENTAMOUNT
          @INSTALLMENTID,
          @INSTALLMENTAMOUNT,
          @NEWINSTALLMENTAMOUNT,
          @RGTRANSACTIONCURRENCYID,
          @RGBASECURRENCYID,
          @RGBASEEXCHANGERATEID,
          @RGORGANIZATIONEXCHANGERATEID,
          0,
          @PAYMENTID,
          @APPLICATIONEFFECTIVEDATETIME,
          @CHANGEAGENTID,
          @CURRENTDATE
      end

      -- apply the payment

      if @EXISTINGPAYMENTID is null
        insert into dbo.RECURRINGGIFTINSTALLMENTPAYMENT (
          ID,
          RECURRINGGIFTINSTALLMENTID,
          PAYMENTID,
          AMOUNT,
          APPLICATIONCURRENCYID,
          APPLICATIONEXCHANGERATEID,
          ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (
          newid(),
          @INSTALLMENTID,
          @PAYMENTID,
          @PAYAMOUNTCONVERTED,
          @APPLICATIONCURRENCYID,
          @APPLICATIONEXCHANGERATEID,
          @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
      else
        update dbo.RECURRINGGIFTINSTALLMENTPAYMENT
        set AMOUNT = (AMOUNT + @PAYAMOUNTCONVERTED),
          APPLICATIONEXCHANGERATEID = @APPLICATIONEXCHANGERATEID,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where ID = @EXISTINGPAYMENTID;
    end
    else if @PASTBALANCEUNDERPAYMENTCODE = 1
      -- We don't have any money left to apply, we are still looping through installments in order to write them off.

      set @WRITEOFFAMOUNT = @INSTALLMENTBALANCE;

    else if @PASTBALANCEUNDERPAYMENTCODE = 2
    begin
      -- We don't have any money left to apply, we are still looping through installments in order to adjust their amounts so that the balance is $0.

      set @NEWINSTALLMENTAMOUNT = @INSTALLMENTAMOUNT - @INSTALLMENTBALANCE;

      exec dbo.USP_RECURRINGGIFT_ADJUSTINSTALLMENTAMOUNT
        @INSTALLMENTID,
        @INSTALLMENTAMOUNT,
        @NEWINSTALLMENTAMOUNT,
        @RGTRANSACTIONCURRENCYID,
        @RGBASECURRENCYID,
        @RGBASEEXCHANGERATEID,
        @RGORGANIZATIONEXCHANGERATEID,
        2,
        @PAYMENTID,
        @APPLICATIONEFFECTIVEDATETIME,
        @CHANGEAGENTID,
        @CURRENTDATE
    end

    -- write-off the remaining installment balance if appropriate

    if @WRITEOFFAMOUNT > 0 and @EXISTINGWRITEOFFID is null
      exec dbo.USP_RECURRINGGIFT_ADDPAYMENTHANDLINGWRITEOFF
        @APPLICATIONID,
        @INSTALLMENTID,
        @PAYMENTID,
        @PAYMENTDATE,
        @WRITEOFFAMOUNT,
        @RGBASECURRENCYID,
        @RGBASEEXCHANGERATEID,
        @RGTRANSACTIONCURRENCYID,
        @RGORGANIZATIONEXCHANGERATEID,
        @CHANGEAGENTID,
        @CURRENTDATE

    fetch next from INSTALLMENTCURSOR into @INSTALLMENTID, @INSTALLMENTBALANCE, @INSTALLMENTAMOUNT, @INSTALLMENTDATE, @EXISTINGPAYMENTID, @EXISTINGWRITEOFFID, @EXISTINGADJUSTMENTID, @APPLYREMAINING;
  end
  --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

  close INSTALLMENTCURSOR;
  deallocate INSTALLMENTCURSOR;

  -- check for donation error (we have money left after applying the payment to all available installments and we are not applying money to future installments)

  if @OVERPAYMENTCODE = 2 and
     @AMOUNTLEFTCONVERTED > 0
    raiserror('More money is being applied to the recurring gift than is allowed according to the payment handling rules; the excess should have been split off as a donation.', 13, 1);

  -- create additional (future) installments for any remaining amount

  while @AMOUNTLEFTCONVERTED > 0
  begin
    set @INSTALLMENTID = newid();
    set @LASTINSTALLMENTDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@APPLICATIONID,@LASTINSTALLMENTDATE);

    if @AMOUNTLEFTCONVERTED <= @RGTRANSACTIONAMOUNT
    begin
      set @PAYAMOUNTCONVERTED = @AMOUNTLEFTCONVERTED;
      if @AMOUNTLEFTCONVERTED < @RGTRANSACTIONAMOUNT
      begin
        set @NEXTINSTALLMENTEXISTS = 1;
        set @NEXTTRANSACTIONDATE = @LASTINSTALLMENTDATE;
      end                                
    end
    else
      set @PAYAMOUNTCONVERTED = @RGTRANSACTIONAMOUNT;

    set @AMOUNTPAIDCONVERTED = @AMOUNTPAIDCONVERTED + @PAYAMOUNTCONVERTED;
    set @AMOUNTLEFTCONVERTED = @AMOUNTLEFTCONVERTED - @PAYAMOUNTCONVERTED;            

    if @RGAMOUNT is NULL
      raiserror('The payment could not be saved. The associated recurring gift has been deleted.', 13, 1)

    insert into dbo.RECURRINGGIFTINSTALLMENT (
      ID,
      REVENUEID,
      AMOUNT,
      DATE,
      BASECURRENCYID,
      ORGANIZATIONAMOUNT,
      ORGANIZATIONEXCHANGERATEID,
      TRANSACTIONAMOUNT,
      TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values (
      @INSTALLMENTID,
      @APPLICATIONID,
      @RGAMOUNT,
      @LASTINSTALLMENTDATE,
      @RGBASECURRENCYID,
      @RGORGANIZATIONAMOUNT,
      @RGORGANIZATIONEXCHANGERATEID,
      @RGTRANSACTIONAMOUNT,
      @RGTRANSACTIONCURRENCYID,
      @RGBASEEXCHANGERATEID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    if @INSTALLMENTUNDERPAYMENTCODE = 2  -- adjust amount

      exec dbo.USP_RECURRINGGIFT_ADJUSTINSTALLMENTAMOUNT
        @INSTALLMENTID,
        @RGTRANSACTIONAMOUNT,
        @PAYAMOUNTCONVERTED,
        @RGTRANSACTIONCURRENCYID,
        @RGBASECURRENCYID,
        @RGBASEEXCHANGERATEID,
        @RGORGANIZATIONEXCHANGERATEID,
        0,
        @PAYMENTID,
        @APPLICATIONEFFECTIVEDATETIME,
        @CHANGEAGENTID,
        @CURRENTDATE

    insert into dbo.RECURRINGGIFTINSTALLMENTPAYMENT (
      ID,
      RECURRINGGIFTINSTALLMENTID,
      PAYMENTID,
      AMOUNT,
      APPLICATIONCURRENCYID,
      APPLICATIONEXCHANGERATEID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values (
      newid(),
      @INSTALLMENTID,
      @PAYMENTID,
      @PAYAMOUNTCONVERTED,
      @APPLICATIONCURRENCYID,
      @APPLICATIONEXCHANGERATEID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);                    
  end

  -- if no installments remain w/ a balance, create next installment

  if @NEXTINSTALLMENTEXISTS = 0
  begin
    set @NEXTTRANSACTIONDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@APPLICATIONID,@LASTINSTALLMENTDATE)

    if @NEXTTRANSACTIONDATE <= coalesce((select ENDDATE from dbo.REVENUESCHEDULE where ID = @APPLICATIONID),@NEXTTRANSACTIONDATE)
      insert into dbo.RECURRINGGIFTINSTALLMENT (
        ID,
        REVENUEID,
        AMOUNT,
        DATE,
        BASECURRENCYID,
        ORGANIZATIONAMOUNT,
        ORGANIZATIONEXCHANGERATEID,
        TRANSACTIONAMOUNT,
      TRANSACTIONCURRENCYID,
        BASEEXCHANGERATEID,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      values (
        newid(),
        @APPLICATIONID,
        @RGAMOUNT,
        @NEXTTRANSACTIONDATE,
        @RGBASECURRENCYID,
        @RGORGANIZATIONAMOUNT,
        @RGORGANIZATIONEXCHANGERATEID,
        @RGTRANSACTIONAMOUNT,
        @RGTRANSACTIONCURRENCYID,
        @RGBASEEXCHANGERATEID,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
  end

  exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
    @APPLICATIONID,
    1,
    null,
    null,
    null,
    @RGAMOUNT,
    @RGBASECURRENCYID,
    @RGORGANIZATIONAMOUNT,
    @RGORGANIZATIONEXCHANGERATEID,
    @RGTRANSACTIONAMOUNT,
    @RGTRANSACTIONCURRENCYID,
    @RGBASEEXCHANGERATEID,
    @CHANGEAGENTID,
    @CURRENTDATE
end