USP_MKTSEGMENTATIONACTIVATE_ADDREFRESHSTATUS

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_ADDREFRESHSTATUS]
(
  @ID uniqueidentifier output,
  @SEGMENTATIONID uniqueidentifier,
  @PARAMETERSETID uniqueidentifier
)
as
  set nocount on;

  declare @STARTEDON datetime;
  declare @STARTEDBYUSERID uniqueidentifier;
  declare @SERVERNAME nvarchar(255);
  declare @CHANGEAGENTID uniqueidentifier;
  declare @SEGMENTATIONREFRESHPROCESSID uniqueidentifier;

  begin try
    if @ID is null
      set @ID = NewID();

    select
      @SEGMENTATIONREFRESHPROCESSID = [ID]
    from dbo.[MKTSEGMENTATIONREFRESHPROCESS]
    where [SEGMENTATIONID] = @SEGMENTATIONID;

    --Grab some information from the currently running activate mailing process...

    select top 1
      @STARTEDON = [BUSINESSPROCESSSTATUS].[STARTEDON],
      @STARTEDBYUSERID = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID],
      @SERVERNAME = [BUSINESSPROCESSSTATUS].[SERVERNAME],
      @CHANGEAGENTID = [BUSINESSPROCESSSTATUS].[ADDEDBYID]
    from dbo.[MKTSEGMENTATIONACTIVATEPROCESSSTATUS]
    inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONACTIVATEPROCESSSTATUS].[ID]
    where [MKTSEGMENTATIONACTIVATEPROCESSSTATUS].[PARAMETERSETID] = @PARAMETERSETID
    order by [MKTSEGMENTATIONACTIVATEPROCESSSTATUS].[DATEADDED] desc;

    --Add a new status for the mailing refresh...

    exec dbo.[USP_BUSINESSPROCESSSTATUS_ADDPROCESS]
      @ID,
      'F49185FF-9F54-43EA-B798-8C13E659CD8F',
      @SEGMENTATIONREFRESHPROCESSID,
      'MKTSEGMENTATIONREFRESHPROCESSSTATUS',
      @STARTEDBYUSERID,
      @SERVERNAME,
      @CHANGEAGENTID;

    --Update the mailing refresh start date so it coincides with the activate date and duration...

    update dbo.[BUSINESSPROCESSSTATUS] set
      [STARTEDON] = @STARTEDON
    where [ID] = @ID;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;