USP_MKTSEGMENTATIONACTIVATE_POPULATETEMPSEGMENTTABLE
Populates the temporary table for activating a segment.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_POPULATETEMPSEGMENTTABLE]
(
@SEGMENTID uniqueidentifier
)
as
set nocount on;
declare @SEGMENTATIONID uniqueidentifier;
declare @MAILINGREVENUEIDDATATYPE nvarchar(128);
declare @TEMPTABLENAME nvarchar(128);
declare @RECORDSOURCEID uniqueidentifier;
declare @EXCLUSIONSTABLENAME nvarchar(128);
declare @EXCLUSIONSIDFIELDNAME nvarchar(128);
declare @EXCLUSIONSIDDATATYPE nvarchar(128);
declare @HOUSEHOLDINGTYPECODE tinyint;
declare @IDDATATYPE nvarchar(128);
declare @REVENUEIDDATATYPE nvarchar(128);
declare @LISTDONORIDDATATYPE nvarchar(128);
declare @SQL nvarchar(max);
declare @MKTSEGMENTID uniqueidentifier;
declare @DONORSEXIST bit;
declare @LISTDONORSEXIST bit;
declare @MAILINGTYPECODE tinyint;
declare @USETOP bit;
declare @SAMPLESIZEEXCLUDEREMAINDER bit;
declare @REVENUELETTERTABLENAME nvarchar(128);
declare @ISBBEC bit;
declare @NEEDCAST bit = 0;
declare @PACKAGEID uniqueidentifier;
declare @CONSTITRANKVIEWNAME nvarchar(128);
declare @CONSTITRANKVALUECOLUMN nvarchar(128);
declare @CONSTITRANKORDERDESC bit;
declare @USETOPFORTEMPTABLEINSERT bit;
begin try
select
@SEGMENTATIONID = [MKTSEGMENTATION].[ID],
@TEMPTABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETEMPSEGMENTTABLENAME]([MKTSEGMENTATIONSEGMENT].[ID]),
@MKTSEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
@RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
@HOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
@MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
@USETOP = (case when [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEMETHODCODE] = 2 and not ([MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE] = 0 and [MKTSEGMENTATIONSEGMENT].[SAMPLESIZE] = 100) then 1 else 0 end),
@SAMPLESIZEEXCLUDEREMAINDER = [MKTSEGMENTATIONSEGMENT].[SAMPLESIZEEXCLUDEREMAINDER],
@ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID]),
@EXCLUSIONSTABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEXCLUSIONS_MAKETABLENAME]([MKTSEGMENT].[QUERYVIEWCATALOGID]),
@PACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
if @MAILINGTYPECODE = 1
begin
--Acknowledgement mailings...
set @MAILINGREVENUEIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID);
set @REVENUELETTERTABLENAME = dbo.[UFN_MKTREVENUELETTER_MAKETABLENAME](@RECORDSOURCEID);
set @LISTDONORSEXIST = 0;
if @ISBBEC = 1
--The same segment can be used across multiple acknowledgement rules, so we need to look for revenue letters with matching segments and packages.
set @SQL = 'select @DONORSEXIST = ' + char(13) +
'(case when exists' + char(13) +
' (select 1 from dbo.[' + @REVENUELETTERTABLENAME + '] as [RL]' + char(13) +
' inner join dbo.[REVENUELETTERMARKETING] as [RLM] on [RLM].[ID] = [RL].[ID]' + char(13) +
' where [RLM].[MKTSEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
' and [RLM].[MKTSEGMENTID] = @MKTSEGMENTID' + char(13) +
' and [RLM].[MKTPACKAGEID] = @PACKAGEID' + char(13) +
' ) then 1 else 0 end)';
else
set @SQL = 'select @DONORSEXIST = (case when exists(select 1 from dbo.[' + @REVENUELETTERTABLENAME + '] where [MKTSEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTID] = @MKTSEGMENTID) then 1 else 0 end)';
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @MKTSEGMENTID uniqueidentifier, @PACKAGEID uniqueidentifier, @DONORSEXIST bit output', @SEGMENTATIONID = @SEGMENTATIONID, @MKTSEGMENTID = @MKTSEGMENTID, @PACKAGEID = @PACKAGEID, @DONORSEXIST = @DONORSEXIST output;
end
else if @MAILINGTYPECODE = 2
begin
--Membership mailings
set @EXCLUSIONSIDFIELDNAME = 'MEMBERSHIPID';
set @EXCLUSIONSIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID);
set @LISTDONORSEXIST = 0;
set @SQL = 'select' + char(13) +
' @DONORSEXIST = (case when exists(select 1 from dbo.[' + @EXCLUSIONSTABLENAME + '] where [SEGMENTID] = @SEGMENTID and [MEMBERSHIPID] is not null) then 1 else 0 end)';
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @DONORSEXIST bit output', @SEGMENTID = @SEGMENTID, @DONORSEXIST = @DONORSEXIST output;
end
else if @MAILINGTYPECODE = 3
begin
--Sponsorship mailings
set @EXCLUSIONSIDFIELDNAME = 'SPONSORSHIPID';
set @EXCLUSIONSIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID);
set @LISTDONORSEXIST = 0;
set @SQL = 'select' + char(13) +
' @DONORSEXIST = (case when exists(select 1 from dbo.[' + @EXCLUSIONSTABLENAME + '] where [SEGMENTID] = @SEGMENTID and [SPONSORSHIPID] is not null) then 1 else 0 end)';
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @DONORSEXIST bit output', @SEGMENTID = @SEGMENTID, @DONORSEXIST = @DONORSEXIST output;
end
else if @MAILINGTYPECODE = 5
begin
-- communication revenue
set @EXCLUSIONSIDFIELDNAME = 'REVENUEID';
set @EXCLUSIONSIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETIDSETIDDATATYPE](@SEGMENTATIONID);
set @LISTDONORSEXIST = 0;
set @SQL = 'select' + char(13) +
' @DONORSEXIST = (case when exists(select 1 from dbo.[' + @EXCLUSIONSTABLENAME + '] where [SEGMENTID] = @SEGMENTID and [REVENUEID] is not null) then 1 else 0 end)';
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @DONORSEXIST bit output, @LISTDONORSEXIST bit output', @SEGMENTID = @SEGMENTID, @DONORSEXIST = @DONORSEXIST output, @LISTDONORSEXIST = @LISTDONORSEXIST output;
end
else
begin
--Standard mailings...
set @EXCLUSIONSIDFIELDNAME = 'DONORID';
set @EXCLUSIONSIDDATATYPE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_GETDONORIDDATATYPE](@SEGMENTATIONID);
set @SQL = 'select' + char(13) +
' @DONORSEXIST = (case when exists(select 1 from dbo.[' + @EXCLUSIONSTABLENAME + '] where [SEGMENTID] = @SEGMENTID and [DONORID] is not null) then 1 else 0 end),' + char(13) +
' @LISTDONORSEXIST = (case when exists(select 1 from dbo.[' + @EXCLUSIONSTABLENAME + '] where [SEGMENTID] = @SEGMENTID and [LISTDONORID] is not null) then 1 else 0 end)';
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @DONORSEXIST bit output, @LISTDONORSEXIST bit output', @SEGMENTID = @SEGMENTID, @DONORSEXIST = @DONORSEXIST output, @LISTDONORSEXIST = @LISTDONORSEXIST output;
end
if @DONORSEXIST = 1 or @LISTDONORSEXIST = 1
begin
--Insert all the segment donor IDs into the temp table...
set @SQL = 'insert into dbo.[' + @TEMPTABLENAME + '] ([ID]' + (case when @MAILINGTYPECODE in (1, 5) and @ISBBEC = 1 then ', [DONORID]' else '' end) + ')' + char(13);
if @DONORSEXIST = 1
begin
if @MAILINGTYPECODE = 1
begin
--Acknowledgement mailings...
select @REVENUEIDDATATYPE = (case when lower([DATA_TYPE]) in ('nvarchar', 'varchar', 'char') then '[varchar](' + cast([CHARACTER_MAXIMUM_LENGTH] as nvarchar(10)) + ')' else [DATA_TYPE] end)
from [INFORMATION_SCHEMA].[COLUMNS]
where [TABLE_SCHEMA] = 'dbo'
and [TABLE_NAME] = @REVENUELETTERTABLENAME
and [COLUMN_NAME] = 'REVENUEID';
set @SQL = @SQL + ' select ';
if lower(@REVENUEIDDATATYPE) <> lower(@MAILINGREVENUEIDDATATYPE)
begin
set @SQL = @SQL + 'cast([RL].[REVENUEID] as ' + @MAILINGREVENUEIDDATATYPE + ')';
set @NEEDCAST = 1;
end
else
set @SQL = @SQL + '[RL].[REVENUEID]';
if @ISBBEC = 1
set @SQL = @SQL + ', [RL].[ACKNOWLEDGEEID]';
set @SQL = @SQL + char(13) +
'from dbo.[' + @REVENUELETTERTABLENAME + '] as [RL]' + char(13);
if @ISBBEC = 1
set @SQL = @SQL +
'inner join dbo.[REVENUELETTERMARKETING] as [RLM] on [RLM].[ID] = [RL].[ID]' + char(13) +
'where [RLM].[MKTSEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
'and [RLM].[MKTSEGMENTID] = @MKTSEGMENTID' + char(13) +
'and [RLM].[MKTPACKAGEID] = @PACKAGEID' + char(13) +
'and [RL].[OUTOFDATE] = 0';
else
set @SQL = @SQL +
'where [RL].[MKTSEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
'and [RL].[MKTSEGMENTID] = @MKTSEGMENTID';
end
else
begin
--Standard/Membership/Sponsorship mailings
--Locate the ranking data in case a segment uses top (@PREVUSETOP)
--TODO: TommyVe 6/15/2009 We want to eventually extend this to not depend directly on a segmentation generator for the ranking data view
-- AAW 11/19/09: Removing references to segmented appeal mailings/segmentation generator. Leaving this code around in case DM
-- finds it useful.
--if @SAMPLESIZEEXCLUDEREMAINDER = 1 and @USETOP = 1 and @LISTDONORSEXIST = 0
-- select top 1
-- @CONSTITRANKVIEWNAME = ('SMARTFIELD' + replace(cast([SMARTFIELD].[TABLECATALOGID] as nvarchar(36)), '-', '')),
-- @CONSTITRANKVALUECOLUMN = [SMARTFIELD].[VALUECOLUMNNAME],
-- @CONSTITRANKORDERDESC = 1 --Always order by desc for smart field values
-- from
-- dbo.[SEGMENTEDAPPEALMAILING]
-- inner join dbo.[SEGMENTATIONGENERATORDIMENSION] on [SEGMENTEDAPPEALMAILING].[SEGMENTATIONGENERATORID] = [SEGMENTATIONGENERATORDIMENSION].[SEGMENTATIONGENERATORID]
-- inner join dbo.[SMARTFIELD] on [SEGMENTATIONGENERATORDIMENSION].[SMARTFIELDID] = [SMARTFIELD].[ID]
-- inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELD].[SMARTFIELDCATALOGID] = [SMARTFIELDCATALOG].[ID]
-- where
-- [SEGMENTEDAPPEALMAILING].[ID] = @SEGMENTATIONID
-- order by
-- [SEGMENTATIONGENERATORDIMENSION].[SEQUENCE];
set @USETOPFORTEMPTABLEINSERT = case when @SAMPLESIZEEXCLUDEREMAINDER = 1 and @USETOP = 1 and @LISTDONORSEXIST = 0 and @CONSTITRANKVIEWNAME is not null then 1 else 0 end;
select @IDDATATYPE = (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] = @EXCLUSIONSIDFIELDNAME;
-- (distinct is necessary with the addition of address processing to the mailing activation process:
-- if an address processing option set is selected that causes mail to be sent to more than one contact at an
-- organization, that organization's ID will appear more than once in the view from which this table is generated)
if @USETOPFORTEMPTABLEINSERT = 1
set @SQL = @SQL + ' select [RANKEDEXC].[' + @EXCLUSIONSIDFIELDNAME + ']' + char(13) +
' from' + char(13) +
' (select distinct [CONSTITSRANK].[' + @CONSTITRANKVALUECOLUMN + '] as [RANKVALUE], ';
else
set @SQL = @SQL + ' select distinct ';
if lower(@IDDATATYPE) <> lower(@EXCLUSIONSIDDATATYPE)
begin
set @SQL = @SQL + 'cast([EXC].[' + @EXCLUSIONSIDFIELDNAME + '] as ' + @EXCLUSIONSIDDATATYPE + ') as [' + @EXCLUSIONSIDFIELDNAME + ']';
set @NEEDCAST = 1;
end
else
set @SQL = @SQL + '[EXC].[' + @EXCLUSIONSIDFIELDNAME + '] as [' + @EXCLUSIONSIDFIELDNAME + ']';
if @MAILINGTYPECODE = 5
set @SQL = @SQL + ', [FINANCIALTRANSACTION].[CONSTITUENTID] ';
set @SQL = @SQL + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + '] as [EXC]' + char(13);
if @MAILINGTYPECODE = 5
set @SQL = @SQL + ' inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = ' + '[EXC].[' + @EXCLUSIONSIDFIELDNAME + '] ';
if @MAILINGTYPECODE = 0 and @HOUSEHOLDINGTYPECODE <> 0
set @SQL = @SQL + ' left join dbo.[' + dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEHOUSEHOLDEXCLUSIONS_MAKETABLENAME](@RECORDSOURCEID) + '] as [HHEXC] on [HHEXC].[SEGMENTID] = [EXC].[SEGMENTID] and [HHEXC].[DONORID] = [EXC].[DONORID]' + char(13);
if @SAMPLESIZEEXCLUDEREMAINDER = 1 and @USETOP = 1 and @LISTDONORSEXIST = 0 and @CONSTITRANKVIEWNAME is not null
set @SQL = @SQL + ' left join [' + @CONSTITRANKVIEWNAME + '] as [CONSTITSRANK] on [CONSTITSRANK].[ID] = [EXC].[DONORID]' + char(13);
set @SQL = @SQL + ' where [EXC].[SEGMENTID] = @SEGMENTID' + char(13) +
' and [EXC].[' + @EXCLUSIONSIDFIELDNAME + '] is not null';
if @MAILINGTYPECODE = 0 and @HOUSEHOLDINGTYPECODE <> 0
set @SQL = @SQL + char(13) + ' and [HHEXC].[DONORID] is null';
if @USETOPFORTEMPTABLEINSERT = 1
set @SQL = @SQL + char(13) + ' ) [RANKEDEXC]'+ char(13) +
' order by [RANKEDEXC].[RANKVALUE]' + (case when @CONSTITRANKORDERDESC = 1 then ' desc' else '' end);
end
end
if @MAILINGTYPECODE = 0 and @LISTDONORSEXIST = 1
begin
if @DONORSEXIST = 1
set @SQL = @SQL + char(13) + ' union' + char(13);
set @SQL = @SQL + ' select ';
select @LISTDONORIDDATATYPE = (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] = 'LISTDONORID';
if lower(@LISTDONORIDDATATYPE) <> lower(@EXCLUSIONSIDDATATYPE)
begin
set @SQL = @SQL + 'cast([LISTDONORID] as ' + @EXCLUSIONSIDDATATYPE + ')';
set @NEEDCAST = 1;
end
else
set @SQL = @SQL + '[LISTDONORID]';
set @SQL = @SQL + char(13) +
' from dbo.[' + @EXCLUSIONSTABLENAME + ']' + char(13) +
' where [SEGMENTID] = @SEGMENTID' + char(13) +
' and [LISTDONORID] is not null';
end
--print @SQL;
if @MAILINGTYPECODE = 1
begin
--Acknowledgement mailings...
exec sp_executesql @SQL, N'@SEGMENTATIONID uniqueidentifier, @MKTSEGMENTID uniqueidentifier, @PACKAGEID uniqueidentifier', @SEGMENTATIONID = @SEGMENTATIONID, @MKTSEGMENTID = @MKTSEGMENTID, @PACKAGEID = @PACKAGEID;
end
else
begin
--Standard/Membership/Sponsorship mailings
--Try to improve performance by adding a join hint to the sql. If the hint fails, then execute the sql without it.
begin try
declare @SQLWITHJOINHINT nvarchar(max);
if @NEEDCAST = 1
set @SQLWITHJOINHINT = @SQL + char(13) + ' option (hash join, merge join)';
else
set @SQLWITHJOINHINT = @SQL;
exec dbo.[USP_MKTSEGMENT_GETAPPLOCK] @MKTSEGMENTID, 0;
exec sp_executesql @SQLWITHJOINHINT, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;
exec dbo.[USP_MKTSEGMENT_RELEASEAPPLOCK] @MKTSEGMENTID, 0;
end try
begin catch
if ERROR_NUMBER() = 8622
begin
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID;
exec dbo.[USP_MKTSEGMENT_RELEASEAPPLOCK] @MKTSEGMENTID, 0;
end
else
begin
exec dbo.[USP_RAISE_ERROR];
exec dbo.[USP_MKTSEGMENT_RELEASEAPPLOCK] @MKTSEGMENTID, 0;
return 1;
end
end catch
end
if @MAILINGTYPECODE not in (1, 5)
begin
set @SQL = 'update statistics dbo.[' + @TEMPTABLENAME + '] [PK_' + replace(@TEMPTABLENAME, '#', '') + ']';
exec (@SQL);
end
end
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;