USP_RECURRINGGIFT_UNDOINSTALLMENTCHANGESONDELETEPAYMENT

CREATE procedure dbo.USP_RECURRINGGIFT_UNDOINSTALLMENTCHANGESONDELETEPAYMENT (
  @PAYMENTID uniqueidentifier,
  @RECURRINGGIFTID uniqueidentifier = null,  -- null if deleting the entire payment (which could apply to multiple RGs), specified if only deleting a specific application

  @RECURRINGGIFTINSTALLMENTID uniqueidentifier = null,  -- null if deleting entire applied payment amount, specified to remove payment for a specific installment

  @CHANGEAGENTID uniqueidentifier,
  @CURRENTDATE datetime
)
as
begin
  -- if the installment amount was adjusted, revert it

  update I
  set AMOUNT = CURRENCYVALUES.BASEAMOUNT,
      ORGANIZATIONAMOUNT = CURRENCYVALUES.ORGANIZATIONAMOUNT,
      TRANSACTIONAMOUNT = E.OLDAMOUNT,
      -- revert the installment status to Expected/Past due

      STATUSCODE = case when I.PASTDUEDATE is not null then 1 else 0 end,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
  from dbo.RECURRINGGIFTINSTALLMENTEVENT E
  inner join dbo.RECURRINGGIFTINSTALLMENT I on I.ID = E.RECURRINGGIFTINSTALLMENTID
  outer apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2
    (
      E.OLDAMOUNT,
      null,
      I.BASECURRENCYID,
      I.BASEEXCHANGERATEID,
      I.TRANSACTIONCURRENCYID,
      null,
      null,
      null,
      I.ORGANIZATIONEXCHANGERATEID,
      0
    ) as CURRENCYVALUES
  where E.PAYMENTID = @PAYMENTID
  and I.REVENUEID = isnull(@RECURRINGGIFTID,I.REVENUEID)
  and I.ID = isnull(@RECURRINGGIFTINSTALLMENTID,I.ID);

  -- delete the installment event associated with the amount adjustment

  if @@ROWCOUNT > 0
    delete E
    from dbo.RECURRINGGIFTINSTALLMENTEVENT E
    inner join dbo.RECURRINGGIFTINSTALLMENT I on I.ID = E.RECURRINGGIFTINSTALLMENTID
    where E.PAYMENTID = @PAYMENTID
    and I.REVENUEID = isnull(@RECURRINGGIFTID,I.REVENUEID)
    and I.ID = isnull(@RECURRINGGIFTINSTALLMENTID,I.ID);

  -- adjust installments that were paid before the payment handling features were added

  declare @INSTALLMENTMODECODE tinyint,
          @UPGRADEDATE datetime;

  select @INSTALLMENTMODECODE = INSTALLMENTMODECODE,
         @UPGRADEDATE = UPGRADEDATE
  from dbo.UFN_RECURRINGGIFTSETTING_GETCURRENT();

  if @INSTALLMENTMODECODE = 0
    update RGI
    set AMOUNT = FINANCIALTRANSACTION.BASEAMOUNT,
        ORGANIZATIONAMOUNT = FINANCIALTRANSACTION.ORGAMOUNT,
        TRANSACTIONAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from dbo.RECURRINGGIFTINSTALLMENT RGI
    inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT RGIP on RGIP.RECURRINGGIFTINSTALLMENTID = RGI.ID
    cross apply (select min(I.DATEADDED) PAYMENTDATEADDED
                 from dbo.FINANCIALTRANSACTIONLINEITEM I
                 inner join dbo.RECURRINGGIFTACTIVITY A on A.PAYMENTREVENUEID = I.ID
                 where I.FINANCIALTRANSACTIONID = @PAYMENTID
                 and A.SOURCEREVENUEID = RGI.REVENUEID) PDA
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = RGI.REVENUEID
    where RGIP.PAYMENTID = @PAYMENTID
    and FINANCIALTRANSACTION.DELETEDON is null
    and PDA.PAYMENTDATEADDED < @UPGRADEDATE;

  -- if the remaining balance of the installment was written off per payment handling rules, delete the write-off

  delete W
  from dbo.RECURRINGGIFTWRITEOFF W
  where W.PAYMENTID = @PAYMENTID
  and W.REVENUEID = isnull(@RECURRINGGIFTID,REVENUEID)
  and (@RECURRINGGIFTINSTALLMENTID is null or
       exists(select 'x'
              from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF IW
              where IW.WRITEOFFID = W.ID
              and IW.RECURRINGGIFTINSTALLMENTID = @RECURRINGGIFTINSTALLMENTID));
end