USP_MKTSEGMENTATIONACTIVATEEDIT_ROLLBACK

create procedure dbo.[USP_MKTSEGMENTATIONACTIVATEEDIT_ROLLBACK]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @DELETEAPPEALINFO bit = null
)
as
  set nocount on;

  /*
    This SP does the ID set work of USP_MKTSEGMENTATIONACTIVATE_ROLLBACK,
    but nothing beyond that.
  */

  declare @ACTIVE bit;
  declare @CURRENTDATE datetime;
  declare @ISAPPEALMAILING bit;

  begin try
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = GetDate();

    select
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @ISAPPEALMAILING = (case when [APPEALMAILING].[ID] is null then 0 else 1 end)
    from dbo.[MKTSEGMENTATION]
    left join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATION].[ID]
    where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

    if @ISAPPEALMAILING = 1
      set @DELETEAPPEALINFO = 0;
    else if @DELETEAPPEALINFO is null
      set @DELETEAPPEALINFO = 1;

    /*********************************************************************/
    /* Clean up the activate table that holds the appeal information and */ 
    /* delete the normal and unresolved gift ID sets for the mailing.    */
    /*********************************************************************/
    declare @ACTIVATEID uniqueidentifier;
    declare @RECORDSOURCEID uniqueidentifier;
    declare @NORMALIDSETID uniqueidentifier;
    declare @NORMALIDSETTABLENAME nvarchar(255);
    declare @NORMALIDSETFUNCTIONNAME nvarchar(255);
    declare @NORMALIDSETFUNCTIONNAMEEXISTS nvarchar(255);
    declare @UNRESOLVEDIDSETID uniqueidentifier;
    declare @UNRESOLVEDIDSETTABLENAME nvarchar(255);
    declare @UNRESOLVEDIDSETFUNCTIONNAME nvarchar(255);
    declare @UNRESOLVEDIDSETFUNCTIONNAMEEXISTS nvarchar(255);
    declare @SEGMENTRECORDSOURCES table([ID] uniqueidentifier primary key);

    --Get all the current segment record sources for this mailing...

    insert into @SEGMENTRECORDSOURCES (ID)
      select [SRS].[QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [SRS];

    declare ACTIVATECURSOR cursor local fast_forward for
      select
        [MKTSEGMENTATIONACTIVATE].[ID],
        [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID],
        [MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID],
        [NORMALIDSET].[DBOBJECTNAME] [NORMALDBOBJECTNAME],
        [MKTSEGMENTATIONACTIVATE].[UNRESOLVEDGIFTIDSETREGISTERID],
        [UNRESOLVEDIDSET].[DBOBJECTNAME] [UNRESOLVEDDBOBJECTNAME]
      from dbo.[MKTSEGMENTATIONACTIVATE]
      left join dbo.[IDSETREGISTER] as [NORMALIDSET] on [NORMALIDSET].[ID] = [MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]
      left join dbo.[IDSETREGISTER] as [UNRESOLVEDIDSET] on [UNRESOLVEDIDSET].[ID] = [MKTSEGMENTATIONACTIVATE].[UNRESOLVEDGIFTIDSETREGISTERID]
      where [SEGMENTATIONID] = @SEGMENTATIONID;

    open ACTIVATECURSOR;
    fetch next from ACTIVATECURSOR into @ACTIVATEID, @RECORDSOURCEID, @NORMALIDSETID, @NORMALIDSETTABLENAME, @UNRESOLVEDIDSETID, @UNRESOLVEDIDSETTABLENAME;

    while (@@FETCH_STATUS = 0)
    begin
      update dbo.[MKTSEGMENTATIONACTIVATE] set
        [NORMALGIFTIDSETREGISTERID] = null,
        [UNRESOLVEDGIFTIDSETREGISTERID] = null,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @ACTIVATEID;

      if @NORMALIDSETID is not null
        begin
          set @NORMALIDSETFUNCTIONNAME = 'UFN_' + @NORMALIDSETTABLENAME;
          set @NORMALIDSETFUNCTIONNAMEEXISTS = @NORMALIDSETFUNCTIONNAME + '_IDEXISTS';
          exec dbo.[USP_IDSET_DELETE] @NORMALIDSETFUNCTIONNAME, @NORMALIDSETFUNCTIONNAMEEXISTS;
          exec dbo.[USP_IDSET_DELETESTATICTABLE] @NORMALIDSETTABLENAME;
          exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @NORMALIDSETID, @CHANGEAGENTID;
        end

      if @UNRESOLVEDIDSETID is not null
        begin
          set @UNRESOLVEDIDSETFUNCTIONNAME = 'UFN_' + @UNRESOLVEDIDSETTABLENAME;
          set @UNRESOLVEDIDSETFUNCTIONNAMEEXISTS = @UNRESOLVEDIDSETFUNCTIONNAME + '_IDEXISTS';
          exec dbo.[USP_IDSET_DELETE] @UNRESOLVEDIDSETFUNCTIONNAME, @UNRESOLVEDIDSETFUNCTIONNAMEEXISTS;
          exec dbo.[USP_IDSET_DELETESTATICTABLE] @UNRESOLVEDIDSETTABLENAME;
          exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @UNRESOLVEDIDSETID, @CHANGEAGENTID;
        end

      if @ACTIVE = 0 and (@DELETEAPPEALINFO = 1 or not exists(select 1 from @SEGMENTRECORDSOURCES where [ID] = @RECORDSOURCEID))
        exec dbo.[USP_MKTSEGMENTATIONACTIVATE_DELETEBYID_WITHCHANGEAGENTID] @ACTIVATEID, @CHANGEAGENTID;

      fetch next from ACTIVATECURSOR into @ACTIVATEID, @RECORDSOURCEID, @NORMALIDSETID, @NORMALIDSETTABLENAME, @UNRESOLVEDIDSETID, @UNRESOLVEDIDSETTABLENAME;
    end

    close ACTIVATECURSOR;
    deallocate ACTIVATECURSOR;

  end try

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

  return 0;