USP_DATAFORMTEMPLATE_EDITSAVE_SPONSORSHIPREACTIVATE

CREATE procedure USP_DATAFORMTEMPLATE_EDITSAVE_SPONSORSHIPREACTIVATE
(    
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @REASONID uniqueidentifier
)
as
  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  declare @STARTDATE date;

  declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier
  declare @ISSOLESPONSORSHIP bit
  declare @SPONSORSHIPCONSTITUENTID uniqueidentifier
  declare @COMMITMENTID uniqueidentifier
  declare @REVENUEID uniqueidentifier
  declare @REVENUECONSTITUENTID uniqueidentifier;

  select @SPONSORSHIPOPPORTUNITYID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID,
         @ISSOLESPONSORSHIP = SPONSORSHIP.ISSOLESPONSORSHIP,
         @SPONSORSHIPCONSTITUENTID = SPONSORSHIP.CONSTITUENTID,
         @COMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID,
         @REVENUEID = REVENUESPLIT.REVENUEID,
         @REVENUECONSTITUENTID = REVENUE.CONSTITUENTID,
         @STARTDATE= SPONSORSHIP.STARTDATE
  from dbo.SPONSORSHIP
  left outer join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
  left outer join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
  where SPONSORSHIP.ID = @ID

  begin try
    -- reactivate the sponsorship

    update dbo.SPONSORSHIP
    set STATUSCODE = 1,
        ENDDATE = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where ID = @ID;

    -- reopen the recurring gift

    if @REVENUEID is not null
      exec dbo.USP_RECURRINGGIFT_EDITSTATUS
        @ID = @REVENUEID,
        @STATUSCODE = 0,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @STATUSCHANGETYPECODE = 11;

    -- record the reactivation

    insert into dbo.SPONSORSHIPTRANSACTION
    (
      ID,
      SPONSORSHIPCOMMITMENTID,
      TRANSACTIONSEQUENCE,
      ACTIONCODE,
      SPONSORSHIPREASONID,
      CONTEXTSPONSORSHIPID,
      TARGETSPONSORSHIPID,
      TRANSACTIONDATE,
      GIFTFINANCIALSPONSORID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      newid(),
      @COMMITMENTID,
      (select max(TRANSACTIONSEQUENCE)+1 from dbo.SPONSORSHIPTRANSACTION where SPONSORSHIPCOMMITMENTID = @COMMITMENTID),
      4,
      @REASONID,
      @ID,
      @ID,
      @CURRENTDATE,
      case when @REVENUECONSTITUENTID <> @SPONSORSHIPCONSTITUENTID then @REVENUECONSTITUENTID end,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    )

    -- update the sponsor constituency

    if isnull(@REVENUECONSTITUENTID,@SPONSORSHIPCONSTITUENTID) = @SPONSORSHIPCONSTITUENTID
      exec dbo.USP_SPONSOR_MAKEACTIVE @SPONSORSHIPCONSTITUENTID, @STARTDATE, 0, @CHANGEAGENTID
    else
    begin
      exec dbo.USP_SPONSOR_MAKEACTIVE @SPONSORSHIPCONSTITUENTID, @STARTDATE, 1, @CHANGEAGENTID
      exec dbo.USP_SPONSOR_MAKEACTIVE @REVENUECONSTITUENTID, @STARTDATE, 2, @CHANGEAGENTID
    end

    -- Update and unlock the opportunity.

    exec dbo.USP_SPONSORSHIPOPPORTUNITY_SPONSORSHIPADDED
      @SPONSORSHIPOPPORTUNITYID,
      @ISSOLESPONSORSHIP,
      @CHANGEAGENTID,
      @CURRENTDATE,
      1

  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;