USP_DONORCHALLENGEENCUMBEREDFUND_ENCUMBER

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

  begin try

    --Delete individual pledge payments if the removed Pledge is being encumbered

    delete DCE from
      dbo.DONORCHALLENGEENCUMBERED DCE
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = DCE.REVENUESPLITID
    inner join
      dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
    inner join
      dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
    inner join
      dbo.REVENUE on INSTALLMENTSPLIT.PLEDGEID = REVENUE.ID
    inner join
      dbo.REVENUESPLIT PLEDGESPLIT on REVENUE.ID = PLEDGESPLIT.REVENUEID and INSTALLMENTSPLIT.DESIGNATIONID = PLEDGESPLIT.DESIGNATIONID
    inner join 
      dbo.DONORCHALLENGEENCUMBERED PLEDGE_TO_ENCUMBER on PLEDGESPLIT.ID = PLEDGE_TO_ENCUMBER.REVENUESPLITID
    where
      PLEDGE_TO_ENCUMBER.ID = @ID
      and PLEDGE_TO_ENCUMBER.STATUSTYPECODE = 2 --Pledge is removed

      and REVENUE.TRANSACTIONTYPECODE = 1 --Pledge

      and DCE.STATUSTYPECODE = 0 --Pledge payment is encumbered

      and REVENUESPLIT.APPLICATIONCODE = 2 --Pledge payment


    exec dbo.USP_DONORCHALLENGEENCUMBERED_VALIDATEAMOUNT @DONORCHALLENGEENCUMBEREDID = @ID;

    declare @CURRENTDATE datetime = getdate();

    if @CHANGEAGENTID is null  
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    update dbo.DONORCHALLENGEENCUMBERED set
      STATUSTYPECODE = 0,
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID
    where
      ID = @ID
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch