USP_SPONSORSHIP_CLOSECOMMITMENT

CREATE procedure dbo.USP_SPONSORSHIP_CLOSECOMMITMENT (
  @SPONSORSHIPID uniqueidentifier,
  @ENDDATE date,
  @ACTIONCODE tinyint,
  @SPONSORSHIPREASONID uniqueidentifier = null,
  @REASSIGNTOSPONSORSHIPID uniqueidentifier = null,
  @DECLINEDSPONSORSHIPID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  set nocount on;

  declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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


  -- get information off the sponsorship

  declare @COMMITMENTID uniqueidentifier
  declare @CONSTITUENTID uniqueidentifier
  declare @STATUSCODE tinyint

  select @COMMITMENTID = SPONSORSHIPCOMMITMENTID,
         @CONSTITUENTID = CONSTITUENTID,
       @STATUSCODE = STATUSCODE
  from dbo.SPONSORSHIP
  where ID = @SPONSORSHIPID;

  begin try
    -- Inactivate the sponsorship and return the opportunity to available if appropriate.

    declare @UPDATEAVAILABILITY bit
    set @UPDATEAVAILABILITY = case when @ACTIONCODE = 5 then 0 else 1 end
    -- if the sponsorship is pending, null out the enddate

    set @ENDDATE = case when @STATUSCODE = 0 then null else  @ENDDATE end

    exec dbo.USP_SPONSORSHIP_INACTIVATE
      @SPONSORSHIPID,
      @ENDDATE,
      @UPDATEAVAILABILITY,
      @CHANGEAGENTID

    --------------------------------------------------------------

    -- Terminate the recurring gift    

    declare @REVENUEID uniqueidentifier;
    declare @REVENUECONSTITUENTID uniqueidentifier;
    declare @RGSTATUSCODE tinyint;
    declare @RGSTATUSCHANGETYPECODE tinyint;

    select @REVENUEID = REVENUESPLIT.REVENUEID,
           @REVENUECONSTITUENTID = REVENUE.CONSTITUENTID,
           @RGSTATUSCODE = case SPONSORSHIPREASON.REASONTYPECODE when 7 then 2 else 3 end,
           @RGSTATUSCHANGETYPECODE = case SPONSORSHIPREASON.REASONTYPECODE when 6 then 9 when 7 then 13 when 9 then 12 when 11 then 10 end
    from dbo.SPONSORSHIP
    inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    left join dbo.SPONSORSHIPREASON on SPONSORSHIPREASON.ID = @SPONSORSHIPREASONID
    where SPONSORSHIP.ID = @SPONSORSHIPID;

    if @REVENUEID is not null
    begin
      exec dbo.USP_RECURRINGGIFT_EDITSTATUS
        @ID = @REVENUEID,
        @STATUSCODE = @RGSTATUSCODE,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @STATUSCHANGETYPECODE = @RGSTATUSCHANGETYPECODE;

      update dbo.REVENUESCHEDULE set ENDDATE=@ENDDATE where ID=@REVENUEID
    end

   --terminate the active recurring additional gift if any if action is not reassign

   if @ACTIONCODE <> 5 
   begin
     set @REVENUEID = NULL;

     select @REVENUEID = REVENUEID
     from dbo.SPONSORSHIPRECURRINGADDITIONALGIFT
     where SPONSORSHIPID = @SPONSORSHIPID
     and STATUSCODE = 0;

     if @REVENUEID is not null
     begin
      exec dbo.USP_RECURRINGGIFT_EDITSTATUS
        @ID = @REVENUEID,
        @STATUSCODE = @RGSTATUSCODE,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @STATUSCHANGETYPECODE = @RGSTATUSCHANGETYPECODE;

      update dbo.REVENUESCHEDULE set ENDDATE=@ENDDATE where ID=@REVENUEID
     end
   end
    --------------------------------------------------------------

    -- Inactivate the sponsorship constituency if no more active sponsorships.

    if isnull(@REVENUECONSTITUENTID,@CONSTITUENTID) = @CONSTITUENTID
      exec dbo.USP_SPONSOR_CHECKANDMAKEINACTIVE @CONSTITUENTID, @ENDDATE, 0, @CHANGEAGENTID, @CURRENTDATE
    else
    begin
      exec dbo.USP_SPONSOR_CHECKANDMAKEINACTIVE @CONSTITUENTID, @ENDDATE, 1, @CHANGEAGENTID, @CURRENTDATE
      exec dbo.USP_SPONSOR_CHECKANDMAKEINACTIVE @REVENUECONSTITUENTID, @ENDDATE, 2, @CHANGEAGENTID, @CURRENTDATE
    end

    --------------------------------------------------------------

    -- Insert the inactivation transaction row.

    insert into dbo.SPONSORSHIPTRANSACTION
    (
      ID,
      SPONSORSHIPCOMMITMENTID,
      TRANSACTIONSEQUENCE,
      ACTIONCODE,
      SPONSORSHIPREASONID,
      CONTEXTSPONSORSHIPID,
      TARGETSPONSORSHIPID,
      DECLINEDSPONSORSHIPID,
      TRANSACTIONDATE,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      newid(),
      @COMMITMENTID,
      (select max(TRANSACTIONSEQUENCE)+1 from dbo.SPONSORSHIPTRANSACTION where SPONSORSHIPCOMMITMENTID = @COMMITMENTID),
      @ACTIONCODE,
      @SPONSORSHIPREASONID,
      @SPONSORSHIPID,
      @REASSIGNTOSPONSORSHIPID,
      @DECLINEDSPONSORSHIPID,
      @CURRENTDATE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    )    


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

  return 0
end