USP_MKTSEGMENTATIONACTIVATE_ROLLBACK
Rolls back the changes made while activating a marketing effort, if the activation fails.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @DELETEAPPEALINFO | bit | IN | |
| @DEACTIVATE | bit | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_ROLLBACK]
(
@SEGMENTATIONID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@DELETEAPPEALINFO bit = null,
@DEACTIVATE bit = null
)
as
set nocount on;
declare @SQL nvarchar(max);
declare @DATATABLENAME nvarchar(255);
declare @MAILINGTYPECODE tinyint;
declare @SOURCECODEISHISTORICAL bit;
declare @MAILINGFAMILYTYPECODE tinyint;
declare @ACTIVE bit;
declare @CURRENTDATE datetime;
declare @ISBBEC bit;
declare @BBECAPPEALSYSTEMID nvarchar(36);
declare @BBECRECORDSOURCEID uniqueidentifier;
begin try
if @CHANGEAGENTID is null
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
select
@MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
@MAILINGFAMILYTYPECODE = [MKTSEGMENTATION].[MAILINGFAMILYTYPECODE],
@ACTIVE = [MKTSEGMENTATION].[ACTIVE],
@SOURCECODEISHISTORICAL = [MKTSOURCECODE].[ISHISTORICAL],
@DATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATION].[ID])
from dbo.[MKTSEGMENTATION]
left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;
if @MAILINGFAMILYTYPECODE = 3 --Other communications
set @DELETEAPPEALINFO = 0;
else if @DELETEAPPEALINFO is null
set @DELETEAPPEALINFO = 1;
if @DEACTIVATE is null
set @DEACTIVATE = 0;
set @BBECRECORDSOURCEID = dbo.[UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID]();
set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);
if @ISBBEC = 1
select @BBECAPPEALSYSTEMID = [APPEALSYSTEMID]
from dbo.[MKTSEGMENTATIONACTIVATE]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [RECORDSOURCEID] = @BBECRECORDSOURCEID;
/*********************************************************************/
/* 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
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],
[MKTSEGMENTATIONACTIVATE].[UNRESOLVEDGIFTIDSETREGISTERID],
[UNRESOLVEDIDSET].[DBOBJECTNAME]
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;
/*********************************************************************************/
/* Delete all records from the sponsorship activated table. */
/*********************************************************************************/
delete from dbo.MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED where MKTSEGMENTATIONID = @SEGMENTATIONID
if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLENAME)
begin
/**********************************************************************/
/* Delete all constituent appeals for BBEC that may have been created */
/**********************************************************************/
if @ISBBEC = 1 and @BBECAPPEALSYSTEMID is not null and @BBECAPPEALSYSTEMID <> ''
begin
--Cache current context information
declare @CONTEXTCACHE varbinary(128);
set @CONTEXTCACHE = CONTEXT_INFO();
--Set CONTEXT_INFO to @CHANGEAGENTID
if @CHANGEAGENTID is not null
set CONTEXT_INFO @CHANGEAGENTID;
--Delete the constit appeals for this mailing
delete from dbo.[CONSTITUENTAPPEAL]
where [APPEALID] = @BBECAPPEALSYSTEMID
and [MKTSEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENTATIONSEGMENTID] is not null;
--Reset CONTEXT_INFO to previous value
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
end
/*************************************************************************/
/* Delete all list matchback data for imported and vendor managed lists. */
/*************************************************************************/
declare @NEEDSMATCHBACK bit;
exec dbo.[USP_MKTSEGMENTATION_NEEDSMATCHBACK] @SEGMENTATIONID, @NEEDSMATCHBACK output;
if @NEEDSMATCHBACK = 1
exec dbo.[USP_MKTSEGMENTATION_MATCHBACKDELETE] @SEGMENTATIONID;
end
if @ACTIVE = 0 or @DEACTIVATE = 1
begin
/********************************************************/
/* Remove any package cache information for the mailing */
/********************************************************/
update dbo.[MKTSEGMENTATIONPACKAGE] set
[UNITCOST] = 0,
[COSTDISTRIBUTIONMETHODCODE] = 0,
[INSERTCOSTPERPIECE] = 0,
[INSERTCOSTPERRESPONSE] = 0,
[INSERTCOSTPEREFFORT] = 0,
[CODE] = '',
[CHANGEDBYID] = @CHANGEAGENTID
where [SEGMENTATIONID] = @SEGMENTATIONID;
/**************************************************************************************************/
/* Remove any list segment snapshot information for the mailing created at the time of activation */
/**************************************************************************************************/
declare @SEGMENTATIONSEGMENTLISTID uniqueidentifier;
declare SEGMENTLISTCURSOR cursor local fast_forward for
select [MKTSEGMENTATIONSEGMENT].[ID]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS] = 0
and [MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS] = 0;
open SEGMENTLISTCURSOR;
fetch next from SEGMENTLISTCURSOR into @SEGMENTATIONSEGMENTLISTID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_MKTSEGMENTATIONSEGMENTLIST_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTATIONSEGMENTLISTID, @CHANGEAGENTID;
fetch next from SEGMENTLISTCURSOR into @SEGMENTATIONSEGMENTLISTID;
end
close SEGMENTLISTCURSOR;
deallocate SEGMENTLISTCURSOR;
/************************************************/
/* Remove any KPIs associated with this mailing */
/************************************************/
declare @KPIRECORDTYPE uniqueidentifier;
declare @KPIINSTANCEID uniqueidentifier;
set @KPIRECORDTYPE = dbo.[UFN_RECORDTYPE_GETIDBYNAME]('Marketing Effort');
declare KPICURSOR cursor local fast_forward for
select [KPIINSTANCE].[ID]
from dbo.[KPIINSTANCE]
inner join dbo.[KPICATALOG] on [KPICATALOG].[ID] = [KPIINSTANCE].[KPICATALOGID]
where [KPICATALOG].[CONTEXTRECORDTYPEID] = @KPIRECORDTYPE
and [KPIINSTANCE].[CONTEXTRECORDID] = cast(@SEGMENTATIONID as nvarchar(100));
open KPICURSOR;
fetch next from KPICURSOR into @KPIINSTANCEID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_KPIINSTANCE_DELETE] @KPIINSTANCEID, @CHANGEAGENTID;
fetch next from KPICURSOR into @KPIINSTANCEID;
end
close KPICURSOR;
deallocate KPICURSOR;
/************************************************************************/
/* Remove any cached activated numbers for the mailing and its segments */
/************************************************************************/
--Delete the mailing cached numbers...
exec dbo.[USP_MKTSEGMENTATIONACTIVE_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTATIONID, @CHANGEAGENTID;
if @MAILINGTYPECODE = 2 --Membership cache
exec dbo.[USP_MKTMEMBERSHIPMAILINGACTIVE_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTATIONID, @CHANGEAGENTID;
--Delete the mailing segment's cached numbers...
if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = 'MKTSEGMENTATIONSEGMENTACTIVE')
begin
declare @SEGMENTCACHEID uniqueidentifier;
declare SEGMENTCACHECURSOR cursor local fast_forward for
select [MKTSEGMENTATIONSEGMENTACTIVE].[ID]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
where [SEGMENTATIONID] = @SEGMENTATIONID;
open SEGMENTCACHECURSOR;
fetch next from SEGMENTCACHECURSOR into @SEGMENTCACHEID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_MKTSEGMENTATIONSEGMENTACTIVE_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTCACHEID, @CHANGEAGENTID;
if @MAILINGTYPECODE = 2
exec dbo.[USP_MKTMEMBERSHIPMAILINGSEGMENTACTIVE_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTCACHEID, @CHANGEAGENTID;
fetch next from SEGMENTCACHECURSOR into @SEGMENTCACHEID;
end
close SEGMENTCACHECURSOR;
deallocate SEGMENTCACHECURSOR;
end
--Delete the list cache for the mailing...
if @MAILINGTYPECODE = 0 --Only standard/appeal mailings can have lists
begin
declare @LISTCACHEID uniqueidentifier;
declare LISTCACHECURSOR cursor local fast_forward for
select [MKTSEGMENTATIONLISTACTIVE].[ID]
from dbo.[MKTSEGMENTATIONLISTACTIVE]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONLISTACTIVE].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;
open LISTCACHECURSOR;
fetch next from LISTCACHECURSOR into @LISTCACHEID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_MKTSEGMENTATIONLISTACTIVE_DELETEBYID_WITHCHANGEAGENTID] @LISTCACHEID, @CHANGEAGENTID;
fetch next from LISTCACHECURSOR into @LISTCACHEID;
end
close LISTCACHECURSOR;
deallocate LISTCACHECURSOR;
end
/****************************************************************************************************/
/* Remove any cached revenue or constituent IDs in the REVENUESEGMENT and CONSTITUENTSEGMENT tables */
/* Also remove any source analysis rule data for this mailing */
/****************************************************************************************************/
--Loop through each record source in this mailing because we have a separate REVENUESEGMENT and CONSTITUENTSEGMENT table for each record source
declare @REVENUESEGMENTTABLE nvarchar(128);
declare @CONSTITUENTSEGMENTTABLE nvarchar(128);
declare @SARTABLE nvarchar(128);
declare @CACHESOURCEANALYSISRULEDATA bit;
declare @SOURCEANALYSISRULEDATALASTCACHED datetime;
declare RECORDSOURCECURSOR cursor local fast_forward for
select [ID]
from @SEGMENTRECORDSOURCES;
open RECORDSOURCECURSOR;
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
while (@@FETCH_STATUS = 0)
begin
set @REVENUESEGMENTTABLE = dbo.[UFN_REVENUESEGMENT_MAKETABLENAME](@RECORDSOURCEID);
if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @REVENUESEGMENTTABLE)
begin
--Remove all the existing revenue IDs from the REVENUESEGMENT table for this mailing...
set @SQL = 'delete from dbo.[' + @REVENUESEGMENTTABLE + ']' + char(13) +
'where [SEGMENTID] in (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID)';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
end
set @CONSTITUENTSEGMENTTABLE = dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME](@RECORDSOURCEID);
if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @CONSTITUENTSEGMENTTABLE)
begin
--Remove all the existing constituent IDs from the CONSTITUENTSEGMENT table for this mailing...
set @SQL = 'delete from dbo.[' + @CONSTITUENTSEGMENTTABLE + ']' + char(13) +
'where [SEGMENTID] in (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID)';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
end
set @SARTABLE = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID);
if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @SARTABLE)
begin
--Because SAR can be calculated before activation now, we cannot blindly wipe out the SAR table during this rollback.
--Instead, we can only do this delete if the SAR options allows us to.
select
@CACHESOURCEANALYSISRULEDATA = [CACHESOURCEANALYSISRULEDATA]
from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
where [SEGMENTATIONID] = @SEGMENTATIONID;
select
@SOURCEANALYSISRULEDATALASTCACHED = [SOURCEANALYSISRULEDATALASTCACHED]
from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
where [SEGMENTATIONID] = @SEGMENTATIONID;
if @CACHESOURCEANALYSISRULEDATA = 1 or @SOURCEANALYSISRULEDATALASTCACHED is null
begin
--Remove all the existing source analysis data for this mailing...
set @SQL = 'delete from dbo.[' + @SARTABLE + ']' + char(13) +
'where [MAILINGID] = @SEGMENTATIONID';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
end
end
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
end
close RECORDSOURCECURSOR;
deallocate RECORDSOURCECURSOR;
if @SOURCECODEISHISTORICAL = 0
begin
/************************************************/
/* Remove the cached list codes for the mailing */
/************************************************/
declare @SOURCECODEPARTID uniqueidentifier;
declare SOURCECODEPARTCURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [LISTID] is not null;
open SOURCECODEPARTCURSOR;
fetch next from SOURCECODEPARTCURSOR into @SOURCECODEPARTID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_MKTSOURCECODEPART_DELETEBYID_WITHCHANGEAGENTID] @SOURCECODEPARTID, @CHANGEAGENTID;
fetch next from SOURCECODEPARTCURSOR into @SOURCECODEPARTID;
end
close SOURCECODEPARTCURSOR;
deallocate SOURCECODEPARTCURSOR;
/**********************************************************/
/* Remove the cached source code mappings for the mailing */
/**********************************************************/
declare @SOURCECODEMAPID uniqueidentifier;
declare SOURCECODEMAPCURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTSOURCECODEMAP]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [WHITEMAILSEGMENTID] is null;
open SOURCECODEMAPCURSOR;
fetch next from SOURCECODEMAPCURSOR into @SOURCECODEMAPID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_MKTSOURCECODEMAP_DELETEBYID_WITHCHANGEAGENTID] @SOURCECODEMAPID, @CHANGEAGENTID;
fetch next from SOURCECODEMAPCURSOR into @SOURCECODEMAPID;
end
close SOURCECODEMAPCURSOR;
deallocate SOURCECODEMAPCURSOR;
end
/**********************************************************************************************************/
/* Deactivate Mailing */
/* The code inside this block is meant to completely deactivate the mailing (if possible). Only add code */
/* below if it is specific to deactivating a mailing and not simply rolling back a failed activation. */
/**********************************************************************************************************/
if @DEACTIVATE = 1
begin
/****************************************************/
/* Delete the mailing refresh process, if it exists */
/****************************************************/
declare @MKTSEGMENTATIONREFRESHPROCESSID uniqueidentifier;
select
@MKTSEGMENTATIONREFRESHPROCESSID = [ID]
from dbo.[MKTSEGMENTATIONREFRESHPROCESS]
where [SEGMENTATIONID] = @SEGMENTATIONID;
if @MKTSEGMENTATIONREFRESHPROCESSID is not null
exec dbo.[USP_MKTSEGMENTATIONREFRESHPROCESS_DELETEBYID_WITHCHANGEAGENTID] @MKTSEGMENTATIONREFRESHPROCESSID, @CHANGEAGENTID;
/********************************************************************************************/
/* Add the mailing preactivation process back (a.k.a. Mailing Record Counts report process) */
/********************************************************************************************/
if not exists(select 1 from dbo.[MKTMAILINGPREACTIVATIONPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID)
insert into dbo.[MKTMAILINGPREACTIVATIONPROCESS] (
[ID],
[SEGMENTATIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
) values (
NewID(),
@SEGMENTATIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
/**************************************************/
/* Add the mailing selection refresh process back */
/**************************************************/
if @MAILINGTYPECODE not in (1, 4)
exec dbo.[USP_MKTSEGMENTATIONSEGMENTREFRESHPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;
/************************************************************************************/
/* Add the mailing EffortExclusions process back (a.k.a. Exclusions report process) */
/************************************************************************************/
exec dbo.[USP_MKTSEGMENTATION_EFFORTEXCLUSIONSPROCESS_SAVE] @SEGMENTATIONID, @CHANGEAGENTID;
/*****************************************************************************************************/
/* If this is a test mailing, then try to remove the test mailing from all other mailing's exclusion */
/* filters. This can only work if the test mailing is not being used by another activated mailing. */
/*****************************************************************************************************/
declare @PARENTSEGMENTATIONID uniqueidentifier;
declare @MKTSEGMENTATIONFILTERSEGMENTATIONID uniqueidentifier;
select
@PARENTSEGMENTATIONID = [PARENTSEGMENTATIONID]
from dbo.[MKTSEGMENTATION]
where [ID] = @SEGMENTATIONID;
if @PARENTSEGMENTATIONID is not null
begin
if exists(select 1 from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION] inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONFILTERSEGMENTATION].[SEGMENTATIONID] where [MKTSEGMENTATIONFILTERSEGMENTATION].[PREVIOUSSEGMENTATIONID] = @PARENTSEGMENTATIONID and [MKTSEGMENTATION].[ACTIVE] = 1)
raiserror('The test marketing effort cannot be deactivated because one or more marketing efforts are activated using this test marketing effort as an exclusion.', 13, 1);
declare PREVIOUSMAILINGCURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION]
where [PREVIOUSSEGMENTATIONID] = @PARENTSEGMENTATIONID;
open PREVIOUSMAILINGCURSOR;
fetch next from PREVIOUSMAILINGCURSOR into @MKTSEGMENTATIONFILTERSEGMENTATIONID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_MKTSEGMENTATIONFILTERSEGMENTATION_DELETEBYID_WITHCHANGEAGENTID] @MKTSEGMENTATIONFILTERSEGMENTATIONID, @CHANGEAGENTID;
fetch next from PREVIOUSMAILINGCURSOR into @MKTSEGMENTATIONFILTERSEGMENTATIONID;
end
close PREVIOUSMAILINGCURSOR;
deallocate PREVIOUSMAILINGCURSOR;
end
/***********************************************/
/* Truncate the address processing cache table */
/***********************************************/
declare @ADDRESSCACHETABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
if exists(select * from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @ADDRESSCACHETABLENAME)
begin
set @SQL = 'truncate table dbo.[' + @ADDRESSCACHETABLENAME + ']';
exec (@SQL);
end
/*****************************************************/
/* Truncate the email address processing cache table */
/*****************************************************/
declare @EMAILADDRESSCACHETABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
if exists(select * from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @EMAILADDRESSCACHETABLENAME)
begin
set @SQL = 'truncate table dbo.[' + @EMAILADDRESSCACHETABLENAME + ']';
exec (@SQL);
end
/***********************************/
/* Truncate the mailing data table */
/***********************************/
exec dbo.[USP_MKTSEGMENTATION_TRUNCATEDATATABLE] @SEGMENTATIONID;
/*************************************************/
/* Remove all system-defined findernumber ranges */
/*************************************************/
declare @MKTSEGMENTATIONFINDERNUMBERID uniqueidentifier;
declare FINDERNUMBERRANGECURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTSEGMENTATIONFINDERNUMBER]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [ISRESERVED] = 0;
open FINDERNUMBERRANGECURSOR;
fetch next from FINDERNUMBERRANGECURSOR into @MKTSEGMENTATIONFINDERNUMBERID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_MKTSEGMENTATIONFINDERNUMBER_DELETE] @MKTSEGMENTATIONFINDERNUMBERID, @CHANGEAGENTID;
fetch next from FINDERNUMBERRANGECURSOR into @MKTSEGMENTATIONFINDERNUMBERID;
end
close FINDERNUMBERRANGECURSOR;
deallocate FINDERNUMBERRANGECURSOR;
/*******************************/
/* Reset fields on the mailing */
/*******************************/
update dbo.[MKTSEGMENTATION] set
[ACTIVE] = 0,
[ACTIVATEDATE] = null,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [ID] = @SEGMENTATIONID;
end
end
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;