USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE

CREATE procedure dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE (
  @REVENUEID uniqueidentifier,
  @STATUSCHANGETYPECODE tinyint = 0,    -- All from RECURRINGGIFTAMENDMENT, 98=Skip

  @STATUSREASONCODEID uniqueidentifier = null,
  -- For manual status changes

  @STATUSCODE_BEFOREEDIT tinyint = null,
  @NEWSTATUSCODE tinyint = null,
  -- Installment fields

  @RGAMOUNT money = null,
  @BASECURRENCYID uniqueidentifier = null,
  @RGORGANIZATIONAMOUNT money = null,
  @ORGANIZATIONEXCHANGERATEID uniqueidentifier = null,
  @RGTRANSACTIONAMOUNT money = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  -- Audit fields

  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTDATETIME datetime = null
)
as
begin
  if @CHANGEAGENTID is null
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

  declare @CURRENTDATE date = cast(@CURRENTDATETIME as date);

  declare @FUTUREINSTALLMENTEXISTS bit;
  declare @LASTINSTALLMENTPLUS1DATE date;
  declare @NEXTTRANSACTIONDATE date;
  declare @NEXTFUTUREINSTALLMENTDATE date;

  declare @ENDDATE date;
  declare @OLDSTATUSCODE tinyint;

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

  -- No additional changes are needed if the RG is Terminated or being set to Canceled/Terminated.

  -- (On a status change, OLDSTATUSCODE is the new status, so checking OLDSTATUSCODE means the current

  --  value for non-status-changes, or the new value for status changes.  Checking NEWSTATUSCODE

  --  limits to just status changes.)

  if @OLDSTATUSCODE <> 2 and
     isnull(@NEWSTATUSCODE,0) <> 3 and
     -- Suppress changes of end date on a Canceled RG from reactivating the RG.

     (@OLDSTATUSCODE <> 3 or @STATUSCHANGETYPECODE <> 8 or @ENDDATE is null)
  begin
    if @ENDDATE <= @CURRENTDATE
      -- End date is in the past (or today), set status to Canceled.

      set @NEWSTATUSCODE = 3;

    else
    begin
      select @FUTUREINSTALLMENTEXISTS = isnull(max(case when STATUSCODE in(0,1) and DATE >= @CURRENTDATE then 1 else 0 end),0),
             @LASTINSTALLMENTPLUS1DATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@REVENUEID,max(DATE)),
             @NEXTTRANSACTIONDATE = min(case when STATUSCODE in(0,1) and (@ENDDATE is null or DATE <= @ENDDATE) then DATE end),
             @NEXTFUTUREINSTALLMENTDATE = min(case when DATE >= @CURRENTDATE and STATUSCODE in(0,1,3) then DATE end)
      from dbo.RECURRINGGIFTINSTALLMENT
      where REVENUEID = @REVENUEID;

      if @FUTUREINSTALLMENTEXISTS = 0 and
         (@OLDSTATUSCODE in(0,1,5) or (@OLDSTATUSCODE = 3 and (@ENDDATE > @CURRENTDATE or @ENDDATE is null)))
      begin
        if (@ENDDATE is null or @LASTINSTALLMENTPLUS1DATE <= @ENDDATE)
        begin
          -- create future installment if it's missing but needed

          if @RGAMOUNT is null
            select @RGAMOUNT = AMOUNT,
                   @BASECURRENCYID = BASECURRENCYID,
                   @RGORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT,
                   @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID,
                   @RGTRANSACTIONAMOUNT = TRANSACTIONAMOUNT,
                   @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
                   @BASEEXCHANGERATEID = BASEEXCHANGERATEID
            from dbo.REVENUE
            where ID = @REVENUEID;

          insert into dbo.RECURRINGGIFTINSTALLMENT (
            ID,
            REVENUEID,
            AMOUNT,
            DATE,
            BASECURRENCYID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID,
            TRANSACTIONAMOUNT,
            TRANSACTIONCURRENCYID,
            BASEEXCHANGERATEID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (
            newid(),
            @REVENUEID,
            @RGAMOUNT,
            @LASTINSTALLMENTPLUS1DATE,
            @BASECURRENCYID,
            @RGORGANIZATIONAMOUNT,
            @ORGANIZATIONEXCHANGERATEID,
            @RGTRANSACTIONAMOUNT,
            @TRANSACTIONCURRENCYID,
            @BASEEXCHANGERATEID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATETIME, @CURRENTDATETIME);

          if @NEXTTRANSACTIONDATE is null
            set @NEXTTRANSACTIONDATE = @LASTINSTALLMENTPLUS1DATE;
        end

        else if @NEXTTRANSACTIONDATE is null
        begin
          -- The RG is currently active/lapsed/held, or it's canceled but the end date hasn't passed

          -- The next future installment would be after the end date

          -- There are no Expected/Past due installments remaining

          -- ==> Set RG status to Canceled

          set @NEWSTATUSCODE = 3;
          set @STATUSCHANGETYPECODE = 7;
          set @STATUSREASONCODEID = null;
        end
      end
    end

    ----------------------------------------


    -- Set RG status to Active or Held if appropriate

    if @NEWSTATUSCODE is null and
        -- The RG is currently Canceled, but the end date is in the future and

        -- now there's an Expected/Past due installment that might get paid

       ((@OLDSTATUSCODE = 3 and @NEXTTRANSACTIONDATE is not null and (@ENDDATE > @CURRENTDATE or @ENDDATE is null)) or

        -- the RG is currently Lapsed and we're applying a payment/write-off/skip or editing the schedule

        (@OLDSTATUSCODE = 5 and @STATUSCHANGETYPECODE in(1,3,8,98)) or

        -- the RG is currently Active and we're applying a payment/write-off/skip

        (@OLDSTATUSCODE = 0 and @STATUSCHANGETYPECODE in(1,3,98)) or

        -- the RG is currently Held and we're undoing a skip or deleting/decreasing a payment/write-off

        (@OLDSTATUSCODE = 1 and @STATUSCHANGETYPECODE in(2,5))
       )
      -- Set RG status to Active, or Held if next installment is skipped

      set @NEWSTATUSCODE = case (select STATUSCODE from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @REVENUEID and DATE = @NEXTFUTUREINSTALLMENTDATE) when 3 then 1 else 0 end;

    ----------------------------------------


    update dbo.REVENUESCHEDULE
    set NEXTTRANSACTIONDATE = @NEXTTRANSACTIONDATE,
        STATUSCODE = isnull(@NEWSTATUSCODE,STATUSCODE),
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATETIME
    where ID = @REVENUEID;
  end

  ----------------------------------------

  -- create status change amendment if status changed

  if @NEWSTATUSCODE <> isnull(@STATUSCODE_BEFOREEDIT,@OLDSTATUSCODE)
  begin
    -- get the skip reason if updating to Held b/c a payment takes us to a future skip

    if @NEWSTATUSCODE = 1 and @OLDSTATUSCODE <> 1 and @STATUSCHANGETYPECODE = 1
    begin
      select @STATUSREASONCODEID = w.SKIPREASONCODEID,
             @STATUSCHANGETYPECODE = 0
      from dbo.RECURRINGGIFTINSTALLMENT i
      inner join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF iw on iw.RECURRINGGIFTINSTALLMENTID = i.ID
      inner join dbo.RECURRINGGIFTWRITEOFF w on w.ID = iw.WRITEOFFID
      where i.REVENUEID = @REVENUEID
      and i.DATE = @NEXTFUTUREINSTALLMENTDATE;
    end

    -- create status change amendment

    insert into dbo.RECURRINGGIFTAMENDMENT(
      ID,
      FINANCIALTRANSACTIONID,
      AMENDMENTTYPECODE,
      DATE,
      STATUSCODE,
      PREVIOUSSTATUSCODE,
      RECURRINGGIFTSTATUSREASONCODEID,
      STATUSCHANGETYPECODE,
      ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
    values (
      newid(),
      @REVENUEID,
      1,
      @CURRENTDATE,
      @NEWSTATUSCODE,
      isnull(@STATUSCODE_BEFOREEDIT,@OLDSTATUSCODE),
      @STATUSREASONCODEID,
      isnull(nullif(@STATUSCHANGETYPECODE,98),0),
      @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATETIME,@CURRENTDATETIME);                    
  end
end