USP_RECURRINGGIFTINSTALLMENTUNDOSKIP

CREATE procedure dbo.USP_RECURRINGGIFTINSTALLMENTUNDOSKIP (
  @REVENUEID uniqueidentifier,
  @INSTALLMENTDATE date,
  @INSTALLMENTID uniqueidentifier,
  @EDITSTATUS bit,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  set nocount on

  -- retrieve all skipped installments

  declare @SKIPPEDINSTALLMENTS table (ID uniqueidentifier,
                                      WRITEOFFID uniqueidentifier,
                                      DATE date);

  insert into @SKIPPEDINSTALLMENTS(ID, DATE, WRITEOFFID)
  select I.ID,
          I.DATE,
          W.ID
  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 W.TYPECODE = 1

    declare @contextCache varbinary(128);
    set @contextCache = CONTEXT_INFO();

    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

  -- delete all the RECURRINGGIFTINSTALLMENTWRITEOFF installments that have a date greater or equal than the installment that's being undone

  delete from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF 
  where RECURRINGGIFTINSTALLMENTID in (select ID from @SKIPPEDINSTALLMENTS where DATE >= @INSTALLMENTDATE);

  -- delete only the write-off entries that no longer have a RECURRINGGIFTINSTALLMENTWRITEOFF entry

  delete from dbo.RECURRINGGIFTWRITEOFF
  where ID in (select distinct WRITEOFFID from @SKIPPEDINSTALLMENTS where WRITEOFFID not in (select WRITEOFFID from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF));

  -- delete the installments from the RECURRINGGIFTINSTALLMENT table newer than the installment which is being undone

  -- this installment will become the next expected 'Expected' installment

  delete from dbo.RECURRINGGIFTINSTALLMENT 
  where REVENUEID = @REVENUEID
  and DATE > @INSTALLMENTDATE
  and ID not in (select RECURRINGGIFTINSTALLMENTID from dbo.RECURRINGGIFTINSTALLMENTPAYMENT);

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

  if @EDITSTATUS = 1
    exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
      @REVENUEID = @REVENUEID,
      @STATUSCHANGETYPECODE = 5,
      @CHANGEAGENTID = @CHANGEAGENTID

  return 0;
end