USP_DONORCHALLENGEMATCHEDFUND_REMOVE

CREATE procedure dbo.USP_DONORCHALLENGEMATCHEDFUND_REMOVE
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on

  declare @CURRENTDATE datetime = getdate();
  declare @MATCHEDREVENUERECOGNITIONID uniqueidentifier;
  declare @MATCHEDREVENUEID uniqueidentifier;
  declare @REVENUESPLITID uniqueidentifier;
  declare @PLEDGESPLITID uniqueidentifier;
  declare @DONORCHALLENGEID uniqueidentifier;

  begin try

    select 
      @MATCHEDREVENUERECOGNITIONID = MATCHEDREVENUERECOGNITIONID,
      @MATCHEDREVENUEID = MATCHEDREVENUEID,
      @REVENUESPLITID = REVENUESPLITID,
      @DONORCHALLENGEID = DONORCHALLENGEID
    from dbo.DONORCHALLENGEENCUMBERED
    where ID = @ID;

    --if this is a pledge payment then just delete the row

    if exists(select DONORCHALLENGEENCUMBERED.ID from dbo.DONORCHALLENGEENCUMBERED inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID where DONORCHALLENGEENCUMBERED.ID = @ID and REVENUESPLIT.APPLICATIONCODE = 2)
    begin
      select @PLEDGESPLITID = REVENUESPLIT.ID 
        from dbo.INSTALLMENTSPLITPAYMENT 
        inner join dbo.INSTALLMENTSPLIT
          on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
        inner join dbo.REVENUESPLIT 
          on INSTALLMENTSPLIT.PLEDGEID = REVENUESPLIT.REVENUEID and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
        where INSTALLMENTSPLITPAYMENT.PAYMENTID = @REVENUESPLITID;


      update dbo.DONORCHALLENGEENCUMBERED set
        STATUSTYPECODE = 0,
        MATCHEDREVENUERECOGNITIONID = null,
        MATCHEDREVENUEID = null,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      where
        DONORCHALLENGEID = @DONORCHALLENGEID
        and REVENUESPLITID = @PLEDGESPLITID;

      exec dbo.USP_DONORCHALLENGEENCUMBERED_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
    end
    else
      update dbo.DONORCHALLENGEENCUMBERED set
        STATUSTYPECODE = 0,
        MATCHEDREVENUERECOGNITIONID = null,
        MATCHEDREVENUEID = null,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      where
        ID = @ID;

    --now external sponsored recognition credit is stored on the donor challenge claim so revenue recognitions credits will be deleted by cascade delete   

    --exec USP_REVENUERECOGNITION_DELETEBYID_WITHCHANGEAGENTID @MATCHEDREVENUERECOGNITIONID, @CHANGEAGENTID;


    --but internal sponsored recognition credits need to be deleted    

    declare @contextCache varbinary(128);
    /* cache current context information */
    set @contextCache = CONTEXT_INFO();

    /* set CONTEXT_INFO to @CHANGEAGENTID */
    if not @CHANGEAGENTID is null
      set CONTEXT_INFO @CHANGEAGENTID

    -- Delete any Recognition program revenue records associated to the credit.

    delete from dbo.CONSTITUENTRECOGNITIONREVENUE
    from dbo.CONSTITUENTRECOGNITIONREVENUE
        inner join dbo.RECOGNITIONCREDIT on CONSTITUENTRECOGNITIONREVENUE.RECOGNITIONCREDITID = RECOGNITIONCREDIT.ID
    where DONORCHALLENGEENCUMBEREDID=@ID;

    delete from dbo.RECOGNITIONCREDIT where DONORCHALLENGEENCUMBEREDID=@ID;

    /* reset CONTEXT_INFO to previous value */
    if not @contextCache is null
      set CONTEXT_INFO @contextCache

    exec USP_REVENUE_DELETE @MATCHEDREVENUEID, @CHANGEAGENTID;
  end try
  begin catch
  exec dbo.USP_RAISE_ERROR;
  return 1;
  end catch