USP_MKTSEGMENTATION_CALCULATEEXCLUSIONS
Calculates the exclusions for a given marketing effort.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @RECORDSOURCEID | uniqueidentifier | IN | |
| @EXCLUDEDIDSTEMPTABLENAME | nvarchar(128) | IN | |
| @EXCLUSIONDATATABLE | nvarchar(128) | IN | |
| @EFFORTTYPECODE | tinyint | IN | |
| @RECORDTYPEID | uniqueidentifier | IN | |
| @ISBBEC | bit | IN | |
| @HOUSEHOLDINGTYPECODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATION_CALCULATEEXCLUSIONS]
(
@SEGMENTATIONID uniqueidentifier,
@RECORDSOURCEID uniqueidentifier,
@EXCLUDEDIDSTEMPTABLENAME nvarchar(128),
@EXCLUSIONDATATABLE nvarchar(128),
@EFFORTTYPECODE tinyint,
@RECORDTYPEID uniqueidentifier,
@ISBBEC bit,
@HOUSEHOLDINGTYPECODE tinyint
)
as
set nocount on;
/*
Exclusion type codes:
1 = Universe
2 = Exclusions
3 = Contact Rules
4 = Householding
5 = Address Processing
6 = Previous Segment
7 = Remove Members
8 = Inline Exclusions
*/
declare @MARKETINGRECORDTYPE tinyint;
declare @NEEDSDISTINCT bit = 0;
declare @DATATABLE nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
declare @TEMPUNIVERSETABLE nvarchar(128) = '#TEMP_UNIVERSE_' + replace(cast(@RECORDTYPEID as nvarchar(36)), '-', '_');
declare @TEMPEXCLUSIONSTABLE nvarchar(128) = '#TEMP_EXCLUSIONS_' + replace(cast(@RECORDTYPEID as nvarchar(36)), '-', '_');
declare @DONORTORECIPIENTCONVERSIONTABLENAME nvarchar(128);
declare @DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME nvarchar(128);
declare @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME nvarchar(128);
declare @DONORTORECIPIENTCONVERSIONEXTRAJOINCONDITION nvarchar(max);
declare @INSERTSQL nvarchar(max) = 'insert into dbo.[' + @EXCLUSIONDATATABLE + '] ([DONORID], [DONORNAME], [EXCLUDEDFROMSEGMENT], [INCLUDEDINSEGMENT], [EXCLUSIONREASON], [EXCLUSIONTYPE], [EXCLUSIONTYPECODE])' + char(13);
declare @SQL nvarchar(max);
declare @DONORIDDATATYPE nvarchar(128);
declare @DATATYPE nvarchar(128);
declare @EXCLUSIONSTABLENAME nvarchar(128);
declare @CASTBEGIN nvarchar(15) = '###CASTBEGIN###';
declare @CASTEND nvarchar(13) = '###CASTEND###';
declare @NEEDCAST bit;
begin try
/* Figure out the record source type of the current segment (ie - whether it is a root record source, list, or consolidated list) */
if @EFFORTTYPECODE = 0 -- appeal
begin
select
@MARKETINGRECORDTYPE = 1
from dbo.[MKTRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
left outer join dbo.[RECORDTYPECOMPATIBILITYMAP] on [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE1ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
where ([QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID or [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE2ID] = @RECORDTYPEID)
and (not exists(select 1 from dbo.[INSTALLEDPRODUCTLIST]) or dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1);
if @MARKETINGRECORDTYPE is null
begin
select
@MARKETINGRECORDTYPE = 2
from dbo.[MKTSEGMENTLIST]
where [IDSETRECORDTYPEID] = @RECORDTYPEID;
if @MARKETINGRECORDTYPE is null
begin
select
@MARKETINGRECORDTYPE = 3
from [MKTCONSOLIDATEDQUERYVIEWSPEC]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]
where [QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID;
if @MARKETINGRECORDTYPE is null
raiserror('Invalid record type specified.', 13, 1);
else
raiserror('The consolidated list record type is not supported in this stored procedure.', 13, 1);
end
end
end
else if @EFFORTTYPECODE = 1 -- acknowledgement
select
@NEEDSDISTINCT = 1, --needed because we join on DONORID and it could be in the set of records more than once
@MARKETINGRECORDTYPE = 1, -- no need to take lists or the consolidated list into account
--if donor selections are used in the mailing's universe/exclusions, the following is used to turn them into revenue...
@DONORTORECIPIENTCONVERSIONTABLENAME = (case when @ISBBEC = 1 then 'FINANCIALTRANSACTION' else [QUERYVIEWCATALOG].[OBJECTNAME] end),
@DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME = (case when @ISBBEC = 1 then 'CONSTITUENTID' else [MKTGIFTRECORDSOURCE].[DONORIDFIELD] end),
@DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME = (case when @ISBBEC = 1 then 'ID' else [QUERYVIEWCATALOG].[PRIMARYKEYFIELD] end),
@DONORTORECIPIENTCONVERSIONEXTRAJOINCONDITION = (case when @ISBBEC = 1 then ' and [FINANCIALTRANSACTION].[DELETEDON] is null' else '' end)
from dbo.[MKTGIFTRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTGIFTRECORDSOURCE].[ID] = @RECORDSOURCEID;
else if @EFFORTTYPECODE = 2 -- membership
select
@NEEDSDISTINCT = 1, --needed because we join on DONORID and it could be in the set of records more than once
@MARKETINGRECORDTYPE = 1, -- no need to take lists or the consolidated list into account
--if donor selections are used in the mailing's universe/exclusions, the following is used to turn them into memberships...
@DONORTORECIPIENTCONVERSIONTABLENAME = (case when @ISBBEC = 1 then 'MEMBER' else [QUERYVIEWCATALOG].[OBJECTNAME] end),
@DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME = (case when @ISBBEC = 1 then 'CONSTITUENTID' else [MKTMEMBERSHIPRECORDSOURCE].[MEMBERIDFIELD] end),
@DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME = (case when @ISBBEC = 1 then 'MEMBERSHIPID' else [MKTMEMBERSHIPRECORDSOURCE].[MEMBERSHIPSYSTEMIDFIELD] end),
@DONORTORECIPIENTCONVERSIONEXTRAJOINCONDITION = (case when @ISBBEC = 1 then ' and [MEMBER].[ISPRIMARY] = 1 and [MEMBER].[ISDROPPED] = 0' else '' end)
from dbo.[MKTMEMBERSHIPRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTMEMBERSHIPRECORDSOURCE].[ID] = @RECORDSOURCEID;
else if @EFFORTTYPECODE = 3 -- sponsorship
select
@NEEDSDISTINCT = 1, --needed because we join on DONORID and it could be in the set of records more than once
@MARKETINGRECORDTYPE = 1, -- no need to take lists or the consolidated list into account
--if donor selections are used in the mailing's universe/exclusions, the following is used to turn them into sponsorships...
@DONORTORECIPIENTCONVERSIONTABLENAME = (case when @ISBBEC = 1 then 'SPONSORSHIP' else [QUERYVIEWCATALOG].[OBJECTNAME] end),
@DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME = (case when @ISBBEC = 1 then 'CONSTITUENTID' else [MKTSPONSORSHIPRECORDSOURCE].[SPONSORIDFIELD] end),
@DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME = (case when @ISBBEC = 1 then 'ID' else [MKTSPONSORSHIPRECORDSOURCE].[SPONSORSHIPSYSTEMIDFIELD] end)
from dbo.[MKTSPONSORSHIPRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTSPONSORSHIPRECORDSOURCE].[ID] = @RECORDSOURCEID;
select
@DONORIDDATATYPE = (case when lower(t.[name]) in ('nvarchar', 'varchar', 'char') then 'varchar(' + cast(c.[max_length] as nvarchar(10)) + ')' else t.[name] end)
from tempdb.sys.columns c
inner join tempdb.sys.types t on t.[system_type_id] = c.[system_type_id] and t.[user_type_id] = c.[user_type_id]
where c.[object_id] = object_id('tempdb..' + @EXCLUDEDIDSTEMPTABLENAME)
and c.[name] = 'DONORID';
/**** Mailing Universe ****/
if object_id('tempdb..' + @TEMPUNIVERSETABLE) is not null
begin
select
@DATATYPE = (case when lower(t.[name]) in ('nvarchar', 'varchar', 'char') then 'varchar(' + cast(c.[max_length] as nvarchar(10)) + ')' else t.[name] end)
from tempdb.sys.columns c
inner join tempdb.sys.types t on t.[system_type_id] = c.[system_type_id] and t.[user_type_id] = c.[user_type_id]
where c.[object_id] = object_id('tempdb..' + @TEMPUNIVERSETABLE)
and c.[name] = 'ID';
--Check the data types of both tables to see if we need to cast the IDs...
set @NEEDCAST = (case when @DONORIDDATATYPE <> @DATATYPE then 1 else 0 end);
set @SQL = @INSERTSQL +
' select ' + (case when @NEEDSDISTINCT = 1 then 'distinct' else '' end) + char(13) +
' [EXCLUDEDIDS].[DONORID] as [DONORID],' + char(13) +
' [EXCLUDEDIDS].[NAME] as [DONORNAME],' + char(13) +
' [EXCLUDEDIDS].[SEGMENTNAME] as [EXCLUDEDFROMSEGMENT],' + char(13) +
' '''' as [INCLUDEDINSEGMENT],' + char(13) +
' ''Not in universe'' as [EXCLUSIONREASON],' + char(13) +
' ''Universe'' as [EXCLUSIONTYPE],' + char(13) +
' 1 as [EXCLUSIONTYPECODE]' + char(13) +
' from dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] as [EXCLUDEDIDS]' + char(13);
if @EFFORTTYPECODE = 0
set @SQL += ' where not exists(select * from ' + @TEMPUNIVERSETABLE + ' as [UNIVERSE] where ' + @CASTBEGIN + '[UNIVERSE].[ID]' + @CASTEND + ' = ' + @CASTBEGIN + '[EXCLUDEDIDS].[DONORID]' + @CASTEND + ')';
else
begin
set @SQL += ' inner join dbo.[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '] on ' + @CASTBEGIN + '[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME + ']' + @CASTEND + ' = ' + @CASTBEGIN + '[EXCLUDEDIDS].[DONORID]' + @CASTEND + isnull(@DONORTORECIPIENTCONVERSIONEXTRAJOINCONDITION, '') + char(13);
if @EFFORTTYPECODE = 2 -- membership
set @SQL += ' inner join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME + '] and [MEMBERSHIP].[STATUSCODE] <> 1' + char(13);
set @SQL += ' where not exists(select * from ' + @TEMPUNIVERSETABLE + ' as [UNIVERSE] where [UNIVERSE].[ID] = [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME + '])';
end
if @NEEDCAST = 1
set @SQL = replace(replace(@SQL, @CASTBEGIN, 'cast('), @CASTEND, ' as varchar(36))');
else
set @SQL = replace(replace(@SQL, @CASTBEGIN, ''), @CASTEND, '');
--print @SQL + char(13);
exec sp_executesql @SQL;
end
/**** Mailing Exclusions ****/
if object_id('tempdb..' + @TEMPEXCLUSIONSTABLE) is not null
begin
select
@DATATYPE = (case when lower(t.[name]) in ('nvarchar', 'varchar', 'char') then 'varchar(' + cast(c.[max_length] as nvarchar(10)) + ')' else t.[name] end)
from tempdb.sys.columns c
inner join tempdb.sys.types t on t.[system_type_id] = c.[system_type_id] and t.[user_type_id] = c.[user_type_id]
where c.[object_id] = object_id('tempdb..' + @TEMPEXCLUSIONSTABLE)
and c.[name] = 'ID';
--Check the data types of both tables to see if we need to cast the IDs...
set @NEEDCAST = (case when @DONORIDDATATYPE <> @DATATYPE then 1 else 0 end);
--Exclusion TypeCodes:
--1 = Exclusion selections
--2 = Previous Marketing Effort exclusions
--3 = Solicit codes
--4 = Inactive
--5 = Deceased
set @SQL = @INSERTSQL +
' select ' + (case when @NEEDSDISTINCT = 1 then 'distinct' else '' end) + char(13) +
' [EXCLUDEDIDS].[DONORID] as [DONORID],' + char(13) +
' [EXCLUDEDIDS].[NAME] as [DONORNAME],' + char(13) +
' [EXCLUDEDIDS].[SEGMENTNAME] as [EXCLUDEDFROMSEGMENT],' + char(13) +
' '''' as [INCLUDEDINSEGMENT],' + char(13) +
' (case [EXCLUSIONS].[TYPECODE]' + char(13) +
' when 1 then ''Included in "'' + [EXCLUSIONS].[REASON] + ''"''' + char(13) +
' when 2 then ''Included in effort "'' + [EXCLUSIONS].[REASON] + ''"''' + char(13) +
' when 3 then [EXCLUSIONS].[REASON]' + char(13) +
' when 4 then ''Inactive''' + char(13) +
' when 5 then ''Deceased''' + char(13) +
' end) as [EXCLUSIONREASON],' + char(13) +
' (case [EXCLUSIONS].[TYPECODE]' + char(13) +
' when 1 then ''Exclusions''' + char(13) +
' when 2 then ''Exclusions''' + char(13) +
' when 3 then ''Contact Rules''' + char(13) +
' when 4 then ''Contact Rules''' + char(13) +
' when 5 then ''Contact Rules''' + char(13) +
' end) as [EXCLUSIONTYPE],' + char(13) +
' (case [EXCLUSIONS].[TYPECODE]' + char(13) +
' when 1 then 2' + char(13) +
' when 2 then 2' + char(13) +
' when 3 then 3' + char(13) +
' when 4 then 3' + char(13) +
' when 5 then 3' + char(13) +
' end) as [EXCLUSIONTYPECODE]' + char(13) +
' from dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] as [EXCLUDEDIDS]' + char(13);
if @EFFORTTYPECODE = 0
set @SQL += ' inner join dbo.[' + @TEMPEXCLUSIONSTABLE + '] as [EXCLUSIONS] on ' + @CASTBEGIN + '[EXCLUSIONS].[ID]' + @CASTEND + ' = ' + @CASTBEGIN + '[EXCLUDEDIDS].[DONORID]' + @CASTEND;
else
begin
set @SQL += ' inner join dbo.[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '] on ' + @CASTBEGIN + '[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME + ']' + @CASTEND + ' = ' + @CASTBEGIN + '[EXCLUDEDIDS].[DONORID]' + @CASTEND + isnull(@DONORTORECIPIENTCONVERSIONEXTRAJOINCONDITION, '') + char(13);
if @EFFORTTYPECODE = 2 -- membership
set @SQL += ' inner join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME + '] and [MEMBERSHIP].[STATUSCODE] <> 1' + char(13);
set @SQL += ' inner join dbo.[' + @TEMPEXCLUSIONSTABLE + '] as [EXCLUSIONS] on [EXCLUSIONS].[ID] = [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME + ']';
end
if @NEEDCAST = 1
set @SQL = replace(replace(@SQL, @CASTBEGIN, 'cast('), @CASTEND, ' as varchar(36))');
else
set @SQL = replace(replace(@SQL, @CASTBEGIN, ''), @CASTEND, '');
--print @SQL + char(13);
exec sp_executesql @SQL;
end
/**** Household exclusions ****/
if @EFFORTTYPECODE = 0 and @HOUSEHOLDINGTYPECODE <> 0 and @MARKETINGRECORDTYPE <> 2
begin
declare @PARENTTABLENAME nvarchar(255);
declare @PRIMARYKEYFIELD nvarchar(255);
declare @HOUSEHOLDIDFIELD nvarchar(255);
declare @ISHOUSEHOLDFIELD nvarchar(255);
/* Get household ID field mappings */
select
@HOUSEHOLDIDFIELD = [MKTRECORDSOURCEFIELDMAPPINGS].[HOUSEHOLDIDFIELD],
@ISHOUSEHOLDFIELD = [MKTRECORDSOURCEFIELDMAPPINGS].[ISHOUSEHOLDFIELD],
@PARENTTABLENAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@PRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD]
from dbo.[MKTRECORDSOURCEFIELDMAPPINGS]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCEFIELDMAPPINGS].[ID]
where [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = @RECORDSOURCEID;
if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @HOUSEHOLDIDFIELD) and exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ISHOUSEHOLDFIELD)
begin
set @EXCLUSIONSTABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEHOUSEHOLDEXCLUSIONS_MAKETABLENAME](@RECORDSOURCEID);
select
@DATATYPE = (case when lower(t.[name]) in ('nvarchar', 'varchar', 'char') then 'varchar(' + cast(c.[max_length] as nvarchar(10)) + ')' else t.[name] end)
from tempdb.sys.columns c
inner join tempdb.sys.types t on t.[system_type_id] = c.[system_type_id] and t.[user_type_id] = c.[user_type_id]
where c.[object_id] = object_id('tempdb..' + @EXCLUSIONSTABLENAME)
and c.[name] = 'DONORID';
--Check the data types of both tables to see if we need to cast the IDs...
set @NEEDCAST = (case when @DONORIDDATATYPE <> @DATATYPE then 1 else 0 end);
if @HOUSEHOLDINGTYPECODE = 1
/* Household data with the segment containing the household member that made it into the effort */
set @SQL = 'with [HOUSEHOLD] ([HOUSEHOLDID], [SEGMENTNAME]) as' + char(13) +
'(' + char(13) +
' select distinct [QUERYVIEW].[' + @HOUSEHOLDIDFIELD + ']' + ', [MKTSEGMENT].[NAME]' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DATA]' + char(13) +
' inner join dbo.[' + @PARENTTABLENAME + '] as [QUERYVIEW] on ' + @CASTBEGIN + '[QUERYVIEW].['+ @PRIMARYKEYFIELD + ']' + @CASTEND + ' = ' + @CASTBEGIN + '[DATA].[DONORID]' + @CASTEND + char(13) +
' inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [DATA].[SEGMENTID]' + char(13) +
' inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
')' + char(13);
else
set @SQL = '';
set @SQL += @INSERTSQL +
' select distinct ' + char(13) +
' [EXCLUDEDIDS].[DONORID] as [DONORID],' + char(13) +
' [EXCLUDEDIDS].[NAME] as [DONORNAME],' + char(13) +
' [EXCLUDEDIDS].[SEGMENTNAME] as [EXCLUDEDFROMSEGMENT],' + char(13) +
' '''' as [INCLUDEDINSEGMENT],' + char(13);
if @HOUSEHOLDINGTYPECODE = 1 -- Qualifying individuals and organizations
set @SQL += ' (case when [QUERYVIEW].[' + @ISHOUSEHOLDFIELD + '] = 0 then' + char(13) +
' ''Household member in '' + ''"'' + [HOUSEHOLD].[SEGMENTNAME] + ''"''' + char(13) +
' else' + char(13) +
' ''Constituent is household''' + char(13) +
' end) as [EXCLUSIONREASON],' + char(13) +
' ''Householding: Qualifying individuals and organizations'' as [EXCLUSIONTYPE],' + char(13) +
' 4 as [EXCLUSIONTYPECODE]' + char(13);
else -- Qualifying households
set @SQL += ' ''Constituent is not household'' as [EXCLUSIONREASON],' + char(13) +
' ''Householding: Qualifying households'' as [EXCLUSIONTYPE],' + char(13) +
' 4 as [EXCLUSIONTYPECODE]' + char(13);
set @SQL += ' from dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] as [EXCLUDEDIDS]' + char(13) +
' inner join dbo.[' + @EXCLUSIONSTABLENAME + '] as [HHEXC] on ' + @CASTBEGIN + '[HHEXC].[DONORID]' + @CASTEND + ' = ' + @CASTBEGIN + '[EXCLUDEDIDS].[DONORID]' + @CASTEND + char(13);
if @HOUSEHOLDINGTYPECODE = 1
set @SQL += ' inner join dbo.[' + @PARENTTABLENAME + '] as [QUERYVIEW] on [QUERYVIEW].['+ @PRIMARYKEYFIELD + '] = [HHEXC].[DONORID]' + char(13) +
' left join [HOUSEHOLD] on [HOUSEHOLD].[HOUSEHOLDID] = [QUERYVIEW].[' + @HOUSEHOLDIDFIELD + ']' + char(13);
set @SQL += ' inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [HHEXC].[SEGMENTID]' + char(13) +
' inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
' left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]' + char(13) +
' where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
' and ([MKTSEGMENT].[SEGMENTTYPECODE] = 1 or ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and [MKTSEGMENTLIST].[TYPECODE] <> 1))' + char(13);
if @HOUSEHOLDINGTYPECODE = 1
/* These ids were excluded for other reasons and should not get marked as excluded for household reasons */
set @SQL += ' and not ([HOUSEHOLD].[SEGMENTNAME] is null and [QUERYVIEW].[' + @ISHOUSEHOLDFIELD + '] = 0)';
if @NEEDCAST = 1
set @SQL = replace(replace(@SQL, @CASTBEGIN, 'cast('), @CASTEND, ' as varchar(36))');
else
set @SQL = replace(replace(@SQL, @CASTBEGIN, ''), @CASTEND, '');
--print @SQL + char(13);
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
end
end
/**** Address processing ****/
if @EFFORTTYPECODE = 0 and @ISBBEC = 1 and @MARKETINGRECORDTYPE <> 2
begin
declare @ADDRESSPROCESSINGCACHETABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @ADDRESSPROCESSINGCACHETABLENAME)
begin
set @SQL = @INSERTSQL +
' /* Get only excluded IDs that were part of a mail channel segment that uses address processing */' + char(13) +
' select' + char(13) +
' [EXCLUDEDIDS].[DONORID],' + char(13) +
' [EXCLUDEDIDS].[NAME] as [DONORNAME],' + char(13) +
' [EXCLUDEDIDS].[SEGMENTNAME] as [EXCLUDEDFROMSEGMENT],' + char(13) +
' '''' as [INCLUDEDINSEGMENT],' + char(13) +
' ''Mailing address processing'' as [EXCLUSIONREASON],' + char(13) +
' ''Address Processing'' as [EXCLUSIONTYPE],' + char(13) +
' 5 as [EXCLUSIONTYPECODE]' + char(13) +
' from dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] as [EXCLUDEDIDS]' + char(13) +
' inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [EXCLUDEDIDS].[SEGMENTID] and [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = ''' + convert(nvarchar(36), @SEGMENTATIONID) + '''' + char(13) +
' inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]' + char(13) +
' inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]' + char(13) +
' left outer join dbo.[' + @ADDRESSPROCESSINGCACHETABLENAME + '] as [ADDRESSES] on [ADDRESSES].[CONSTITUENTID] = [EXCLUDEDIDS].[DONORID]' + char(13) +
' where (([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 and [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] = 1) or [MKTSEGMENTATION].[USEADDRESSPROCESSING] = 1)' + char(13) +
' and [MKTPACKAGE].[CHANNELCODE] = 0' + char(13) +
' and [ADDRESSES].[CONSTITUENTID] is null' + char(13);
--print @SQL + char(13);
exec sp_executesql @SQL;
end
/* Exclusions for email channel code */
declare @EMAILADDRESSPROCESSINGCACHETABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @EMAILADDRESSPROCESSINGCACHETABLENAME)
begin
set @SQL = @INSERTSQL +
' /* Get only excluded IDs that were part of an email channel segment that uses address processing */' + char(13) +
' select' + char(13) +
' [EXCLUDEDIDS].[DONORID],' + char(13) +
' [EXCLUDEDIDS].[NAME] as [DONORNAME],' + char(13) +
' [EXCLUDEDIDS].[SEGMENTNAME] as [EXCLUDEDFROMSEGMENT],' + char(13) +
' '''' as [INCLUDEDINSEGMENT],' + char(13) +
' ''Email address processing'' as [EXCLUSIONREASON],' + char(13) +
' ''Address Processing'' as [EXCLUSIONTYPE],' + char(13) +
' 5 as [EXCLUSIONTYPECODE]' + char(13) +
' from dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] as [EXCLUDEDIDS]' + char(13) +
' inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [EXCLUDEDIDS].[SEGMENTID] and [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = ''' + convert(nvarchar(36), @SEGMENTATIONID) + '''' + char(13) +
' inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]' + char(13) +
' inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]' + char(13) +
' left outer join dbo.[' + @EMAILADDRESSPROCESSINGCACHETABLENAME + '] as [EMAILADDRESSES] on [EMAILADDRESSES].[CONSTITUENTID] = [EXCLUDEDIDS].[DONORID]' + char(13) +
' where (([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 and [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] = 1) or [MKTSEGMENTATION].[USEADDRESSPROCESSING] = 1)' + char(13) +
' and [MKTPACKAGE].[CHANNELCODE] = 1' + char(13) +
' and [EMAILADDRESSES].[CONSTITUENTID] is null' + char(13);
--print @SQL + char(13);
exec sp_executesql @SQL;
end
end
/**** Previous Segment Exclusions ****/
if @EFFORTTYPECODE <> 1 -- acknowledgement
begin
declare @DONORIDSQL nvarchar(128);
declare @EXCLUSIONIDFIELDNAME nvarchar(128);
set @EXCLUSIONSTABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEXCLUSIONS_MAKETABLENAME](@RECORDSOURCEID);
if @EFFORTTYPECODE = 0
begin
if @MARKETINGRECORDTYPE = 1 or @MARKETINGRECORDTYPE = 3
set @EXCLUSIONIDFIELDNAME = 'DONORID'
else if @MARKETINGRECORDTYPE = 2
set @EXCLUSIONIDFIELDNAME = 'LISTDONORID';
set @DONORIDSQL = '[EXCLUSIONS].[' + @EXCLUSIONIDFIELDNAME + ']';
end
else
begin
set @EXCLUSIONIDFIELDNAME = case @EFFORTTYPECODE when 3 then 'SPONSORSHIPID' else @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME end;
set @DONORIDSQL = @CASTBEGIN + '[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME + ']' + @CASTEND;
end
select
@DATATYPE = (case when lower(t.[name]) in ('nvarchar', 'varchar', 'char') then 'varchar(' + cast(c.[max_length] as nvarchar(10)) + ')' else t.[name] end)
from tempdb.sys.columns c
inner join tempdb.sys.types t on t.[system_type_id] = c.[system_type_id] and t.[user_type_id] = c.[user_type_id]
where c.[object_id] = object_id('tempdb..' + @EXCLUSIONSTABLENAME)
and c.[name] = @EXCLUSIONIDFIELDNAME;
--Check the data types of both tables to see if we need to cast the IDs...
set @NEEDCAST = (case when @DONORIDDATATYPE <> @DATATYPE then 1 else 0 end);
set @SQL = @INSERTSQL +
' select ' + (case when @NEEDSDISTINCT = 1 then 'distinct' else '' end) + char(13) +
' ' + @DONORIDSQL + ' as [DONORID],' + char(13) +
' [EXCLUDEDIDS].[NAME] as [DONORNAME],' + char(13) +
' [EXCLUDEDIDS].[SEGMENTNAME] as [EXCLUDEDFROMSEGMENT],' + char(13) +
' [MKTSEGMENT].[NAME] as [INCLUDEDINSEGMENT],' + char(13) +
' case when [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 1 then ''Included in previous segment exclusion'' else ''Included in previous segment'' end as [EXCLUSIONREASON],' + char(13) +
' ''Segmentation'' as [EXCLUSIONTYPE],' + char(13) +
' case when [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 1 then 8 else 6 end as [EXCLUSIONTYPECODE]' + char(13) +
' from dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] as [EXCLUDEDIDS]' + char(13);
if @EFFORTTYPECODE = 0
set @SQL += ' inner join dbo.[' + @EXCLUSIONSTABLENAME + '] as [EXCLUSIONS] on ' + @CASTBEGIN + '[EXCLUSIONS].[' + @EXCLUSIONIDFIELDNAME + ']' + @CASTEND + ' = ' + @CASTBEGIN + '[EXCLUDEDIDS].[DONORID]' + @CASTEND + char(13)
else
begin
set @SQL += ' inner join dbo.[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '] on ' + @CASTBEGIN + '[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME + ']' + @CASTEND + ' = ' + @CASTBEGIN + '[EXCLUDEDIDS].[DONORID]' + @CASTEND + isnull(@DONORTORECIPIENTCONVERSIONEXTRAJOINCONDITION, '') + char(13);
if @EFFORTTYPECODE = 2 -- membership
set @SQL += ' inner join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME + '] and [MEMBERSHIP].[STATUSCODE] <> 1' + char(13);
set @SQL += ' inner join dbo.[' + @EXCLUSIONSTABLENAME + '] as [EXCLUSIONS] on [EXCLUSIONS].[' + @EXCLUSIONIDFIELDNAME + '] = [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME + ']' + char(13);
end
set @SQL += ' inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [EXCLUSIONS].[SEGMENTID]' + char(13) +
' inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
' left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]' + char(13) +
' where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
' and ([MKTSEGMENT].[SEGMENTTYPECODE] in (1, 3, 4, 5) or ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and [MKTSEGMENTLIST].[TYPECODE] <> 1))';
if @NEEDCAST = 1
set @SQL = replace(replace(@SQL, @CASTBEGIN, 'cast('), @CASTEND, ' as varchar(36))');
else
set @SQL = replace(replace(@SQL, @CASTBEGIN, ''), @CASTEND, '');
--print @SQL + char(13);
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID;
end
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;