USP_MKTSEGMENTATION_DELETE
Executes the "Marketing Effort: Delete" record operation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | Input parameter indicating the ID of the record being deleted. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the delete. |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATION_DELETE]
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
set nocount on;
declare @ACTIVE bit;
declare @MAILINGTYPECODE tinyint;
declare @ISHISTORICAL bit;
declare @SOURCECODEISHISTORICAL bit;
declare @SQL nvarchar(max);
declare @DATATABLENAME nvarchar(128);
begin try
select
@ACTIVE = [MKTSEGMENTATION].[ACTIVE],
@MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
@ISHISTORICAL = [MKTSEGMENTATION].[ISHISTORICAL],
@SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0),
@DATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@ID)
from dbo.[MKTSEGMENTATION]
left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
where [MKTSEGMENTATION].[ID] = @ID;
if @ACTIVE = 0
begin
--Check if the mailing is currently being activated...
exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @ID;
/* If the mailing is NOT active, then run through the manual rollback process just to make sure that */
/* a previous failed activation did not leave some artifacts in the database. This should not */
/* affect anything if the mailing did NOT previously fail during an activation. */
exec dbo.[USP_MKTSEGMENTATIONACTIVATE_ROLLBACK] @ID, @CHANGEAGENTID;
/* Clear out the segment count cache tables */
exec dbo.[USP_MKTSEGMENTATION_CLEARCACHE] @ID, 0;
end
/* Check if the activated table exists before we try dropping it and matchback info, just a safety check */
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 dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0
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 [MKTSEGMENTATIONID] = @ID;
--Delete the finder number constit appeals for this mailing
delete from dbo.[MKTFINDERNUMBERCONSTITUENTAPPEAL]
where [MKTSEGMENTATIONID] = @ID;
--Delete the batch finder number constit appeals for this mailing
delete from dbo.[BATCHREVENUECONSTITUENTAPPEAL]
where [MKTSEGMENTATIONID] = @ID;
--Reset CONTEXT_INFO to previous value
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
end
/**********************************************************************/
/* Delete any finder file information that may have been created */
/**********************************************************************/
declare @FINDERFILETABLE nvarchar(256);
set @FINDERFILETABLE = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@ID);
--Drop the finder file table for the mailing
if exists(select 1 from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @FINDERFILETABLE)
begin
set @SQL = 'drop table dbo.[' + @FINDERFILETABLE + '];'
exec (@SQL);
end
--Delete finder file record
declare @FINDERFILEIMPORTPROCESSID uniqueidentifier;
declare FINDERFILECURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTFINDERFILEIMPORTPROCESS]
where [SEGMENTATIONID] = @ID;
open FINDERFILECURSOR;
fetch next from FINDERFILECURSOR into @FINDERFILEIMPORTPROCESSID;
while (@@fetch_status = 0)
begin
exec dbo.[USP_MKTFINDERFILEIMPORTPROCESS_DELETEBYID_WITHCHANGEAGENTID] @FINDERFILEIMPORTPROCESSID, @CHANGEAGENTID;
fetch next from FINDERFILECURSOR into @FINDERFILEIMPORTPROCESSID;
end
close FINDERFILECURSOR;
deallocate FINDERFILECURSOR;
/*********************************************************************/
/* Delete the activated exclusiontable and remove any selections */
/*********************************************************************/
if exists (select 1 from dbo.[MKTSEGMENTATIONEXCLUSION] where [SEGMENTATIONID] = @ID)
begin
/* Delete activated mailing exclusion table */
declare @EXCLUSIONTABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME](@ID);
set @SQL = 'drop table dbo.[' + @EXCLUSIONTABLENAME + ']';
exec (@SQL);
/* Delete any selections created for the post activation exclusions */
declare @SEGMENTATIONEXCLUSIONID uniqueidentifier;
declare MAILINGCURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTSEGMENTATIONEXCLUSION]
where [SEGMENTATIONID] = @ID;
open MAILINGCURSOR;
fetch next from MAILINGCURSOR into @SEGMENTATIONEXCLUSIONID;
while (@@FETCH_STATUS = 0)
begin
/* Delete selections */
declare @IDSETREGISTERID uniqueidentifier;
declare @IDSETTABLENAME nvarchar(255);
declare @IDSETFUNCTIONNAME nvarchar(255);
declare @IDSETFUNCTIONNAMEEXISTS nvarchar(255);
declare IDSETCURSOR cursor local fast_forward for
select [IDSETREGISTER].[ID], [IDSETREGISTER].[DBOBJECTNAME]
from dbo.[MKTSEGMENTATIONEXCLUSIONSELECTION] [SELECTION]
inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [SELECTION].[IDSETREGISTERID]
where [SELECTION].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID;
open IDSETCURSOR;
fetch next from IDSETCURSOR into @IDSETREGISTERID, @IDSETTABLENAME;
while (@@FETCH_STATUS = 0)
begin
--Check if the selection is in use by an ad-hoc query...
if exists(select * from dbo.[ADHOCQUERY] where dbo.[UFN_ADHOCQUERY_IDSETINUSE](@IDSETREGISTERID, [QUERYDEFINITIONXML]) = 1)
begin
raiserror('BBERR_IDSET_REMOVEMEMBERS_INUSEBYADHOCQUERY', 4, 43);
raiserror('The marketing effort cannot be deleted because one of its removed member selections is in use by an ad-hoc query.', 16, 1);
end
set @IDSETFUNCTIONNAME = 'UFN_' + @IDSETTABLENAME;
set @IDSETFUNCTIONNAMEEXISTS = @IDSETFUNCTIONNAME + '_IDEXISTS';
exec dbo.[USP_IDSET_DELETE] @IDSETFUNCTIONNAME, @IDSETFUNCTIONNAMEEXISTS;
exec dbo.[USP_IDSET_DELETESTATICTABLE] @IDSETTABLENAME;
exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @IDSETREGISTERID, @CHANGEAGENTID;
fetch next from IDSETCURSOR into @IDSETREGISTERID, @IDSETTABLENAME;
end
close IDSETCURSOR;
deallocate IDSETCURSOR;
/* Delete the post activation exclusions */
exec dbo.[USP_MKTSEGMENTATIONEXCLUSION_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTATIONEXCLUSIONID, @CHANGEAGENTID;
fetch next from MAILINGCURSOR into @SEGMENTATIONEXCLUSIONID;
end
close MAILINGCURSOR;
deallocate MAILINGCURSOR;
end
/*********************************************************************/
/* Delete the activated datatable and remove any list matchback info */
/*********************************************************************/
/* Delete the activated mailing table, must be done before deleting segments because of foreign key constraints. */
/* Also doing this before we delete matchback info will save some time too, because the matchback delete won't be */
/* removing single rows from the activated table. */
set @SQL = 'drop table dbo.[' + @DATATABLENAME + ']';
exec (@SQL);
/* Now delete the matchback info, after we have deleted the activated table. */
exec dbo.[USP_MKTSEGMENTATION_MATCHBACKDELETE] @ID;
end
/**********************************************************************/
/* Remove any cached revenue or constituent IDs in the REVENUESEGMENT */
/* and CONSTITUENTSEGMENT tables before we delete the segments below. */
/* Also delete source analysis rule data. */
/**********************************************************************/
--Loop through each record source in this mailing because we have a separate REVENUESEGMENT and CONSTITUENTSEGMENT table for each record source
declare @RECORDSOURCEID uniqueidentifier;
declare RECORDSOURCECURSOR cursor local fast_forward for
select [QUERYVIEWCATALOGID]
from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@ID);
open RECORDSOURCECURSOR;
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
while (@@FETCH_STATUS = 0)
begin
if @ACTIVE = 1
begin
--Remove all the existing revenue IDs from the REVENUESEGMENT table for this mailing...
set @SQL = 'delete from dbo.[' + dbo.[UFN_REVENUESEGMENT_MAKETABLENAME](@RECORDSOURCEID) + ']' + char(13) +
'where [SEGMENTID] in (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID)';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @ID;
--Remove all the existing constituent IDs from the CONSTITUENTSEGMENT table for this mailing...
set @SQL = 'delete from dbo.[' + dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME](@RECORDSOURCEID) + ']' + char(13) +
'where [SEGMENTID] in (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID)';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @ID;
end
--Remove all data from the source analysis rule data table for each segment in this mailing
if exists(select 1 from sys.tables where [name] = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID))
begin
set @SQL = 'delete from dbo.[' + dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID) + ']' + char(13) +
'where [MAILINGID] = @SEGMENTATIONID';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @ID;
end
fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID;
end
close RECORDSOURCECURSOR;
deallocate RECORDSOURCECURSOR;
if @ACTIVE = 1
begin
/************************************************/
/* 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(@ID 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 before we delete the segments below */
/************************************************************************************************************/
--Delete the mailing cached numbers...
exec dbo.[USP_MKTSEGMENTATIONACTIVE_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
if @MAILINGTYPECODE = 2 --Membership cache
exec dbo.[USP_MKTMEMBERSHIPMAILINGACTIVE_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
else if @MAILINGTYPECODE = 3
--TODO: Not sure this is the right place for this? Also needs to be written using a delete USP instead.
delete from dbo.MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED where MKTSEGMENTATIONID = @ID;
--Delete the mailing segment's cached numbers...
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] = @ID;
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;
else if @MAILINGTYPECODE = 3
--TODO: Not sure this is the right place for this? Also needs to be written using a delete USP instead.
delete from dbo.MKTSPONSORSHIPMAILINGTEMPLATEACTIVATED where MKTSEGMENTATIONID = @SEGMENTCACHEID;
fetch next from SEGMENTCACHECURSOR into @SEGMENTCACHEID;
end
close SEGMENTCACHECURSOR;
deallocate SEGMENTCACHECURSOR;
--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] = @ID;
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
/*****************************************************************/
/* Disassociate white mail segments associated with the mailing. */
/*****************************************************************/
declare @WHITEMAILSEGMENTID uniqueidentifier;
declare WHITEMAILSEGMENTCURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTSEGMENTWHITEMAIL]
where [SEGMENTATIONID] = @ID;
open WHITEMAILSEGMENTCURSOR;
fetch next from WHITEMAILSEGMENTCURSOR into @WHITEMAILSEGMENTID;
while (@@FETCH_STATUS = 0)
begin
update dbo.[MKTSEGMENTWHITEMAIL] set
[SEGMENTATIONID] = null,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @WHITEMAILSEGMENTID;
exec dbo.[USP_MKTSEGMENTWHITEMAIL_UPDATESOURCECODEMAP] @WHITEMAILSEGMENTID, @CHANGEAGENTID;
fetch next from WHITEMAILSEGMENTCURSOR into @WHITEMAILSEGMENTID;
end
close WHITEMAILSEGMENTCURSOR;
deallocate WHITEMAILSEGMENTCURSOR;
end
if @ACTIVE = 1 or (@ISHISTORICAL = 1 and @SOURCECODEISHISTORICAL = 1)
begin
/*********************************************/
/* Delete the mailing's cached source codes. */
/*********************************************/
declare @SOURCECODEMAPID uniqueidentifier;
declare SOURCECODEMAPCURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTSOURCECODEMAP]
where [SEGMENTATIONID] = @ID
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
-- delete the Marketing Effort Selection Refresh Process business process parameter set
declare @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID uniqueidentifier;
select @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID = [ID]
from dbo.[MKTSEGMENTATIONSEGMENTREFRESHPROCESS]
where [SEGMENTATIONID] = @ID;
if @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID is not null
exec dbo.[USP_MKTSEGMENTATIONSEGMENTREFRESHPROCESS_DELETEBYID_WITHCHANGEAGENTID] @MKTSEGMENTATIONSEGMENTREFRESHPROCESSID, @CHANGEAGENTID;
-- delete the Mailing Segment Record Count Calculation business process parameter set
declare @MKTSEGMENTATIONSEGMENTCALCULATEPROCESSID uniqueidentifier;
select @MKTSEGMENTATIONSEGMENTCALCULATEPROCESSID = [ID]
from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
where [SEGMENTATIONID] = @ID;
exec dbo.[USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_DELETEBYID_WITHCHANGEAGENTID] @MKTSEGMENTATIONSEGMENTCALCULATEPROCESSID, @CHANGEAGENTID;
/*****************************************************************************/
/* Delete each segment individually so the views get removed, and in reverse */
/* order so it doesn't have to regenerate other segment views. */
/*****************************************************************************/
declare @SEGMENTID uniqueidentifier;
declare SEGMENTCURSOR cursor local fast_forward for
select [ID]
from dbo.[MKTSEGMENTATIONSEGMENT]
where [SEGMENTATIONID] = @ID
order by [SEQUENCE] desc;
open SEGMENTCURSOR;
fetch next from SEGMENTCURSOR into @SEGMENTID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.[USP_MKTSEGMENTATIONSEGMENT_DELETE] @SEGMENTID, @CHANGEAGENTID;
fetch next from SEGMENTCURSOR into @SEGMENTID;
end;
close SEGMENTCURSOR;
deallocate SEGMENTCURSOR;
/************************************************************/
/* Remove references to sponsorship template if test effort */
/************************************************************/
declare @MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID uniqueidentifier;
declare @ISTESTEFFORT bit = (select case when [PARENTSEGMENTATIONID] is not null then 1 else 0 end from dbo.[MKTSEGMENTATION] where [ID] = @ID);
if @MAILINGTYPECODE = 3 and @ISTESTEFFORT = 1
begin
select
@MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID = [ID]
from dbo.[MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION] where [SEGMENTATIONID] = @ID;
exec dbo.[USP_MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION_DELETEBYID_WITHCHANGEAGENTID] @MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID, @CHANGEAGENTID;
end
/****************************/
/* Delete each test mailing */
/****************************/
declare @TESTID uniqueidentifier;
declare @TESTFILTERID uniqueidentifier;
declare TESTCURSOR cursor local fast_forward for
select
[MKTSEGMENTATION].[ID],
[MKTSEGMENTATIONFILTERSEGMENTATION].[ID]
from dbo.[MKTSEGMENTATION]
left join dbo.[MKTSEGMENTATIONFILTERSEGMENTATION] on [MKTSEGMENTATIONFILTERSEGMENTATION].[SEGMENTATIONID] = [MKTSEGMENTATION].[PARENTSEGMENTATIONID] and [MKTSEGMENTATIONFILTERSEGMENTATION].[PREVIOUSSEGMENTATIONID] = [MKTSEGMENTATION].[ID]
where [MKTSEGMENTATION].[PARENTSEGMENTATIONID] = @ID
order by [MKTSEGMENTATION].[ACTIVATEDATE] desc;
open TESTCURSOR;
fetch next from TESTCURSOR into @TESTID, @TESTFILTERID;
while (@@FETCH_STATUS = 0)
begin
-- clear out all the filter references --
if @TESTFILTERID is not null
exec dbo.[USP_MKTSEGMENTATIONFILTERSEGMENTATION_DELETEBYID_WITHCHANGEAGENTID] @TESTFILTERID, @CHANGEAGENTID;
if @MAILINGTYPECODE = 3
begin
-- remove references to the sponsorship template
select @MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID = [ID] from dbo.[MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION] where [SEGMENTATIONID] = @TESTID;
exec dbo.[USP_MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION_DELETEBYID_WITHCHANGEAGENTID] @MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID, @CHANGEAGENTID;
end
-- delete the test mailing
exec dbo.[USP_MKTSEGMENTATION_DELETE] @TESTID, @CHANGEAGENTID;
fetch next from TESTCURSOR into @TESTID, @TESTFILTERID;
end
close TESTCURSOR;
deallocate TESTCURSOR;
/*******************************************************************************/
/* Delete any segmented house file information that may have been created */
/*******************************************************************************/
if dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS](@ID) = 1
begin
set @SQL = 'drop table dbo.[' + dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@ID) + '];'
exec (@SQL);
end
if @ACTIVE = 1
begin
/*****************************************************/
/* Delete the gift ID sets for the activated mailing */
/*****************************************************/
declare @ACTIVATEID uniqueidentifier;
declare @NORMALGIFTIDSETREGISTERID uniqueidentifier;
declare @NORMALIDSETTABLENAME nvarchar(255);
declare @NORMALIDSETFUNCTIONNAME nvarchar(255);
declare @NORMALIDSETFUNCTIONNAMEEXISTS nvarchar(255);
declare @UNRESOLVEDGIFTIDSETREGISTERID uniqueidentifier;
declare @UNRESOLVEDIDSETTABLENAME nvarchar(255);
declare @UNRESOLVEDIDSETFUNCTIONNAME nvarchar(255);
declare @UNRESOLVEDIDSETFUNCTIONNAMEEXISTS nvarchar(255);
declare ACTIVATECURSOR cursor local fast_forward for
select
[MKTSEGMENTATIONACTIVATE].[ID],
[MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID],
[NORMALIDSET].[DBOBJECTNAME],
[MKTSEGMENTATIONACTIVATE].[UNRESOLVEDGIFTIDSETREGISTERID],
[UNRESOLVEDIDSET].[DBOBJECTNAME]
from dbo.[MKTSEGMENTATIONACTIVATE]
inner join dbo.[IDSETREGISTER] as [NORMALIDSET] on [NORMALIDSET].[ID] = [MKTSEGMENTATIONACTIVATE].[NORMALGIFTIDSETREGISTERID]
inner join dbo.[IDSETREGISTER] as [UNRESOLVEDIDSET] on [UNRESOLVEDIDSET].[ID] = [MKTSEGMENTATIONACTIVATE].[UNRESOLVEDGIFTIDSETREGISTERID]
where [SEGMENTATIONID] = @ID;
open ACTIVATECURSOR;
fetch next from ACTIVATECURSOR into @ACTIVATEID, @NORMALGIFTIDSETREGISTERID, @NORMALIDSETTABLENAME, @UNRESOLVEDGIFTIDSETREGISTERID, @UNRESOLVEDIDSETTABLENAME;
while (@@FETCH_STATUS = 0)
begin
update dbo.[MKTSEGMENTATIONACTIVATE] set
[NORMALGIFTIDSETREGISTERID] = null,
[UNRESOLVEDGIFTIDSETREGISTERID] = null,
[CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @ACTIVATEID;
/* Delete the gift ID sets for the activated mailing */
if @NORMALGIFTIDSETREGISTERID is not null
begin
--Check if the selection is in use by an ad-hoc query...
if exists(select * from dbo.[ADHOCQUERY] where dbo.[UFN_ADHOCQUERY_IDSETINUSE](@NORMALGIFTIDSETREGISTERID, [QUERYDEFINITIONXML]) = 1)
begin
raiserror('BBERR_IDSET_NORMALGIFTS_INUSEBYADHOCQUERY', 13, 1);
raiserror('The marketing effort cannot be deleted because the normal gifts selection is in use by an ad-hoc query.', 4, 44);
end
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] @NORMALGIFTIDSETREGISTERID, @CHANGEAGENTID;
end
if @UNRESOLVEDGIFTIDSETREGISTERID is not null
begin
--Check if the selection is in use by an ad-hoc query...
if exists(select * from dbo.[ADHOCQUERY] where dbo.[UFN_ADHOCQUERY_IDSETINUSE](@UNRESOLVEDGIFTIDSETREGISTERID, [QUERYDEFINITIONXML]) = 1)
begin
raiserror('BBERR_IDSET_UNRESOLVEDGIFTS_INUSEBYADHOCQUERY', 13, 1);
raiserror('The marketing effort cannot be deleted because the unresolved gifts selection is in use by an ad-hoc query.', 4, 45);
end
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] @UNRESOLVEDGIFTIDSETREGISTERID, @CHANGEAGENTID;
end
fetch next from ACTIVATECURSOR into @ACTIVATEID, @NORMALGIFTIDSETREGISTERID, @NORMALIDSETTABLENAME, @UNRESOLVEDGIFTIDSETREGISTERID, @UNRESOLVEDIDSETTABLENAME;
end
close ACTIVATECURSOR;
deallocate ACTIVATECURSOR;
end
/*********************************************/
/* Drop the address cache table if it exists */
/*********************************************/
declare @ADDRESSCACHETABLENAME nvarchar(128);
set @ADDRESSCACHETABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@ID);
if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @ADDRESSCACHETABLENAME)
exec ('drop table dbo.[' + @ADDRESSCACHETABLENAME + ']');
/***************************************************/
/* Drop the email address cache table if it exists */
/***************************************************/
declare @EMAILADDRESSCACHETABLENAME nvarchar(128);
set @EMAILADDRESSCACHETABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@ID);
if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @EMAILADDRESSCACHETABLENAME)
exec ('drop table dbo.[' + @EMAILADDRESSCACHETABLENAME + ']');
/********************************************************/
/* Delete any html to docx merge process parameter sets */
/* associated with this segmentation. */
/********************************************************/
delete from dbo.HTMLTEMPLATETODOCXMERGEPROCESS
where HTMLTEMPLATETODOCXMERGEPROCESS.ID in (
select HTMLTEMPLATETODOCXMERGEPROCESS.ID
from dbo.HTMLTEMPLATETODOCXMERGEPROCESS
inner join dbo.MKTSEGMENTATIONEXPORTPROCESSSTATUS on MKTSEGMENTATIONEXPORTPROCESSSTATUS.ID = HTMLTEMPLATETODOCXMERGEPROCESS.MKTSEGMENTATIONEXPORTPROCESSSTATUSID
inner join dbo.MKTSEGMENTATIONEXPORTPROCESS on MKTSEGMENTATIONEXPORTPROCESS.ID = MKTSEGMENTATIONEXPORTPROCESSSTATUS.PARAMETERSETID
where MKTSEGMENTATIONEXPORTPROCESS.SEGMENTATIONID = @ID
);
/***************************************************************************/
/* Clean up MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS table before delete */
/* the record in MKTSEGMENTATION table to prevent FK constraint error. */
/***************************************************************************/
declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();
update dbo.MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS set
SEGMENTATIONEXPORTPROCESSSTATUSID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.MKTMEMBERSHIPRENEWALEFFORTMAILINGPROCESS RENEWALPROCESS
inner join dbo.MKTSEGMENTATIONEXPORTPROCESSSTATUS EXPORTSTATUS on RENEWALPROCESS.SEGMENTATIONEXPORTPROCESSSTATUSID = EXPORTSTATUS.ID
inner join dbo.MKTSEGMENTATIONEXPORTPROCESS EXPORTPROCESS on EXPORTSTATUS.PARAMETERSETID = EXPORTPROCESS.ID
inner join dbo.MKTSEGMENTATION SEGMENTATION on EXPORTPROCESS.SEGMENTATIONID = SEGMENTATION.ID
where SEGMENTATION.ID = @ID;
/***************************************************************************/
/* Clean up the comm prefs related to activation - guid field so no cascade*/
/***************************************************************************/
declare @BPPARAMETERSETID uniqueidentifier;
select @BPPARAMETERSETID = ID from dbo.MKTSEGMENTATIONACTIVATEPROCESS where SEGMENTATIONID = @ID;
exec dbo.[USP_BUSINESSPROCESSCOMMPREF_DELETE] '22C3D75C-A956-4BFC-A5FD-4B866BAEF509', @BPPARAMETERSETID, @CHANGEAGENTID;
/***************************************************************************/
/* Delete the mailing, which will cascade delete a lot of other things too */
/***************************************************************************/
exec dbo.[USP_MKTSEGMENTATION_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;