USP_MKTSEGMENTATIONACTIVATE_REMOVEPREACTIVATEPROCESSES

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_REMOVEPREACTIVATEPROCESSES]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID uniqueidentifier;
  declare @MKTMAILINGPREACTIVATIONPROCESSID uniqueidentifier;
  declare @MKTSEGMENTATIONEXCLUSIONSPROCESSID uniqueidentifier;
  declare @MKTSEGMENTATIONSEGMENTCALCULATEPROCESSID uniqueidentifier;
  declare @SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN datetime;
  declare @CURRENTDATE datetime = getdate();

  begin try
    --Only run this if the mailing is activated...

    if (select [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) = 1
      begin
        if @CHANGEAGENTID is null
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;


        /* Remove the Marketing Effort Selection Refresh Process business process parameter set */
        select
          @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID = [ID]
        from dbo.[MKTSEGMENTATIONSEGMENTREFRESHPROCESS]
        where [SEGMENTATIONID] = @SEGMENTATIONID;

        /* First store the last date the selections were refreshed */
        select
          @SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN = max([BUSINESSPROCESSSTATUS].[ENDEDON])
        from
          dbo.[BUSINESSPROCESSSTATUS]
        where
          [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID
          and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0;

        update
          dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
        set
          [SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN] = @SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where
          [SEGMENTATIONID] = @SEGMENTATIONID;

        if @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID is not null
          exec dbo.[USP_MKTSEGMENTATIONSEGMENTREFRESHPROCESS_DELETEBYID_WITHCHANGEAGENTID] @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID, @CHANGEAGENTID; 


        /* Remove the mailing from the PreActivationProcess table */
        select
          @MKTMAILINGPREACTIVATIONPROCESSID = [ID]
        from dbo.[MKTMAILINGPREACTIVATIONPROCESS]
        where [SEGMENTATIONID] = @SEGMENTATIONID;

        if @MKTMAILINGPREACTIVATIONPROCESSID is not null
          exec dbo.[USP_MKTMAILINGPREACTIVATIONPROCESS_DELETEBYID_WITHCHANGEAGENTID] @MKTMAILINGPREACTIVATIONPROCESSID, @CHANGEAGENTID;


        /* Remove the effort from the EffortExclusionsProcess table */
        select
          @MKTSEGMENTATIONEXCLUSIONSPROCESSID = [ID]
        from dbo.[MKTSEGMENTATIONEXCLUSIONSPROCESS]
        where [SEGMENTATIONID] = @SEGMENTATIONID;

        if @MKTSEGMENTATIONEXCLUSIONSPROCESSID is not null
          exec dbo.[USP_MKTSEGMENTATIONEXCLUSIONSPROCESS_DELETEBYID_WITHCHANGEAGENTID] @MKTSEGMENTATIONEXCLUSIONSPROCESSID, @CHANGEAGENTID;


        /* Remove all but the latest business status row for the segment record count calculation process.  We need */
        /* the latest status to stay around so that its output tables can be used in reports after activation.      */
        select
          @MKTSEGMENTATIONSEGMENTCALCULATEPROCESSID = [ID]
        from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
        where [SEGMENTATIONID] = @SEGMENTATIONID;

        if @MKTSEGMENTATIONSEGMENTCALCULATEPROCESSID is not null
          begin
            declare @BUSINESSPROCESSSTATUSID uniqueidentifier;
            declare @STATUSID uniqueidentifier;

            --Get the latest status record so we don't delete it....

            select top 1
              @BUSINESSPROCESSSTATUSID = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID]
            from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS]
            inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID]
            where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[PARAMETERSETID] = @MKTSEGMENTATIONSEGMENTCALCULATEPROCESSID
            and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0
            order by [BUSINESSPROCESSSTATUS].[ENDEDON] desc;

            --Delete all the status records except for the one we are keeping.  This will automatically

            --clean up all the output tables associated with these status records.

            declare STATUSCURSOR cursor local fast_forward for
              select [ID]
              from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS]
              where [PARAMETERSETID] = @MKTSEGMENTATIONSEGMENTCALCULATEPROCESSID
              and [ID] <> @BUSINESSPROCESSSTATUSID;

            open STATUSCURSOR;
            fetch next from STATUSCURSOR into @STATUSID;

            while (@@FETCH_STATUS = 0)
            begin
              exec dbo.[USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS_DELETEBYID_WITHCHANGEAGENTID] @STATUSID, @CHANGEAGENTID;
              fetch next from STATUSCURSOR into @STATUSID;
            end

            close STATUSCURSOR;
            deallocate STATUSCURSOR;
          end
      end
  end try

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

  return 0;