USP_SPONSORSHIP_TERMINATE_PROCESS

CREATE procedure dbo.USP_SPONSORSHIP_TERMINATE_PROCESS
(
    @SPONSORSHIPID uniqueidentifier,
    @SPONSORSHIPREASONID uniqueidentifier = null,
    @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT int = 0 output,
  @OUTPUTTABLE nvarchar(128)
)
as
begin
    set nocount on

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

    declare @ENDDATE date;
    set @ENDDATE = @CURRENTDATE;

  declare @STATUSCODE tinyint;
  declare @ACTIONCODE tinyint;
  declare @RELATEDSPONSORSHIPID uniqueidentifier;
  declare @CONTEXTSPONSORSHIPID uniqueidentifier;
  declare @DECLINEDSPONSORSHIPID uniqueidentifier;

    declare @LOGOUTPUTSQL nvarchar(200);
    declare @LOGOUTPUTPARAMS nvarchar(75);


    set @LOGOUTPUTSQL = N'insert into dbo.' + @OUTPUTTABLE + N' (TERMINATESPONSORSHIPID, ERRORMESSAGE) ' +
                           N'values (@TERMINATESPONSORSHIPID,@ERRORMESSAGE)'
    set @LOGOUTPUTPARAMS = N'@TERMINATESPONSORSHIPID uniqueidentifier,' +
                              N'@ERRORMESSAGE nvarchar(255)'

    select @STATUSCODE= SPONSORSHIP.STATUSCODE,
        @RELATEDSPONSORSHIPID = LASTTRANSACTION.TARGETSPONSORSHIPID, 
    @CONTEXTSPONSORSHIPID = LASTTRANSACTION.CONTEXTSPONSORSHIPID,
    @DECLINEDSPONSORSHIPID = LASTTRANSACTION.DECLINEDSPONSORSHIPID,
        @ACTIONCODE = LASTTRANSACTION.ACTIONCODE
    from dbo.SPONSORSHIP  
    inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and SPONSORSHIP.ID in(MAXSEQUENCE.CONTEXTSPONSORSHIPID,MAXSEQUENCE.TARGETSPONSORSHIPID,MAXSEQUENCE.DECLINEDSPONSORSHIPID) and MAXSEQUENCE.ACTIONCODE <> 9)
    where SPONSORSHIP.ID = @SPONSORSHIPID

    set @SUCCESSCOUNT = 0    
    set @EXCEPTIONCOUNT = 0

    begin try

  save transaction TERMINATE
    begin
       if @STATUSCODE = 1
          if @ACTIONCODE = 6
          begin
          -- cancel pending transfer.

              exec dbo.USP_SPONSORSHIP_CANCELTRANSFER
              @SPONSORSHIPID,
        @RELATEDSPONSORSHIPID,
              @SPONSORSHIPREASONID,
              null, 
        3
           end
          else
          begin
              -- Close the commitment.

              exec dbo.USP_SPONSORSHIP_CLOSECOMMITMENT
              @SPONSORSHIPID,
              @ENDDATE,
              3,
              @SPONSORSHIPREASONID
          end    
        else if @STATUSCODE = 0
              raiserror('BBERR_PENDINGSPONSORSHIPTERMINATE',13,1)
          else if @STATUSCODE = 2
              raiserror('BBERR_INACTIVESPONSORSHIPTERMINATE',13,1)          
    end

        -- log the successful transfer

      exec sp_executesql @LOGOUTPUTSQL,
                         @LOGOUTPUTPARAMS,
               @SPONSORSHIPID,
               'SUCCESS' 

      set @SUCCESSCOUNT = @SUCCESSCOUNT + 1
    end try
    begin catch
    rollback transaction TERMINATE

      declare @MSG nvarchar(255)
      set @MSG = ERROR_MESSAGE()

      exec sp_executesql @LOGOUTPUTSQL,
                         @LOGOUTPUTPARAMS,
                         @SPONSORSHIPID,
                         @MSG

      set @EXCEPTIONCOUNT = @EXCEPTIONCOUNT + 1
    end catch

    return 0

end