USP_MKTSEGMENTATIONACTIVATE_GETSEGMENTSOURCECODEPARTS
Retrieves a list of source code parts to be validated for the marketing effort segment.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONSEGMENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_GETSEGMENTSOURCECODEPARTS]
(
@SEGMENTATIONSEGMENTID uniqueidentifier
)
as
set nocount on;
declare @SEGMENTATIONID uniqueidentifier;
declare @SOURCECODEID uniqueidentifier;
declare @ISTESTSEGMENT bit;
declare @PACKAGEID uniqueidentifier;
declare @CHANNELCODE tinyint;
declare @SEGMENTID uniqueidentifier;
declare @MARKETINGRECORDTYPE tinyint;
declare @LISTCODE nvarchar(50);
declare @LISTCODEVALUEID uniqueidentifier;
declare @LISTNAME nvarchar(100);
begin try
if object_id('tempdb..#SOURCECODETABLE') is not null
drop table #SOURCECODETABLE;
create table #SOURCECODETABLE (
[ITEMTYPECODE] tinyint not null,
[ITEMTYPE] nvarchar(255) COLLATE database_default not null,
[ITEMNAME] nvarchar(50) COLLATE database_default not null,
[LENGTH] tinyint not null,
[CODE] nvarchar(50) COLLATE database_default,
[NAME] nvarchar(100) COLLATE database_default,
[CODEVALUEID] uniqueidentifier,
[SOURCECODEID] uniqueidentifier
);
create clustered index [IX_TMPSOURCECODETABLE_ITEMTYPECODE] on #SOURCECODETABLE ([ITEMTYPECODE]) with (PAD_INDEX = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off);
create nonclustered index [IX_TMPSOURCECODETABLE_ITEMNAME] on #SOURCECODETABLE ([ITEMNAME]) with (PAD_INDEX = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off);
select
@SEGMENTATIONID = [MKTSEGMENTATION].[ID],
@SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
@ISTESTSEGMENT = 1,
@PACKAGEID = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
@CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
@SEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
@MARKETINGRECORDTYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENTATIONSEGMENT].[SEGMENTID])
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;
if @SEGMENTATIONID is null
select
@SEGMENTATIONID = [MKTSEGMENTATION].[ID],
@SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
@ISTESTSEGMENT = 0,
@PACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
@CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
@SEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
@MARKETINGRECORDTYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENTATIONSEGMENT].[SEGMENTID])
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;
if @SOURCECODEID is not null
begin
-- get all the parts of the source code for the segment or test segment
insert into #SOURCECODETABLE
select
[MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE],
[MKTSOURCECODEPARTDEFINITION].[ITEMTYPE],
[MKTSOURCECODEPARTDEFINITION].[NAME],
[MKTSOURCECODEITEM].[LENGTH],
(case [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE]
when 0 then -- mailing
(select [CODE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)
when 1 then -- segment or test segment
(case when @ISTESTSEGMENT = 1 then
(select isnull([MKTSEGMENTATIONSEGMENT].[CODE], '') from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @SEGMENTATIONSEGMENTID)
else
(select isnull([CODE], '') from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID)
end)
when 2 then -- package
(select isnull([CODE], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when 3 then -- channel
(select isnull([CHANNELSOURCECODE], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when 5 then -- user defined
null -- updated below
when 6 then /* List */
null -- updated below
when 7 then /* test segment code */
(case when @ISTESTSEGMENT = 1 then (select isnull([TESTSEGMENTCODE], '') from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID) else (select isnull([TESTSEGMENTCODE], '') from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID) end)
end) as [CODE],
(case [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE]
when 0 then -- mailing
(select isnull([NAME], '') from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)
when 1 then -- segment or test segment
(case when @ISTESTSEGMENT = 1 then
(select isnull([NAME], '') from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID)
else
(dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETNAME](@SEGMENTATIONSEGMENTID))
end)
when 2 then -- package
(select isnull([NAME], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when 3 then -- channel
(select isnull([NAME], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when 4 then -- date
null
when 5 then -- user defined
null
when 6 then -- list
null -- updated below...
when 7 then -- test segment
(case when @ISTESTSEGMENT = 1 then
(select isnull([NAME], '') from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID)
else
(dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETNAME](@SEGMENTATIONSEGMENTID))
end)
end) as [NAME],
(case [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE]
when 0 then -- mailing
(select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)
when 1 then -- segment or test segment
(case when @ISTESTSEGMENT = 1 then
(select [MKTSEGMENTATIONSEGMENT].[PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @SEGMENTATIONSEGMENTID)
else
(select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID)
end)
when 2 then -- package
(select [PARTDEFINITIONVALUESID] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when 3 then -- channel
(select [CHANNELPARTDEFINITIONVALUESID] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when 5 then -- user defined
null
when 6 then /* List */
null -- updated below
when 7 then /* test segment code */
(case when @ISTESTSEGMENT = 1 then (select [TESTPARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID) else (select [TESTPARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @SEGMENTATIONSEGMENTID) end)
end) as [CODEVALUEID],
@SOURCECODEID as [SOURCECODEID]
from dbo.[MKTSOURCECODEITEM]
left join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
order by [MKTSOURCECODEITEM].[SEQUENCE];
/* Update user defined source code part codes */
declare @USERDEFINEDPARTNAME nvarchar(100);
declare @USERDEFINEDCODE nvarchar(50);
declare @USERCODEVALUEID uniqueidentifier;
-- Insert user defined parts into table
if @ISTESTSEGMENT = 0
begin
if object_id('tempdb..#SEGMENTATIONSEGMENTPARTS') is not null
drop table #SEGMENTATIONSEGMENTPARTS;
create table #SEGMENTATIONSEGMENTPARTS ([ID] uniqueidentifier);
insert into #SEGMENTATIONSEGMENTPARTS
select [ID] from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID and [SEGMENTATIONSEGMENTID] is null and [SOURCECODEITEMID] in (select [PARTS].[SOURCECODEITEMID] from (select SOURCECODEITEMID, SEGMENTATIONSEGMENTID from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID group by [SOURCECODEITEMID], SEGMENTATIONSEGMENTID) [PARTS]group by [PARTS].[SOURCECODEITEMID] having COUNT([PARTS].[SOURCECODEITEMID]) > 1);
create clustered index [IX_TMPSEGMENTATIONSEGMENTPARTS_ID] on #SEGMENTATIONSEGMENTPARTS ([ID]) with (PAD_INDEX = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off);
update #SOURCECODETABLE set
[NAME] = '',
[CODE] = isnull([MKTSOURCECODEPART].[CODE], ''),
[CODEVALUEID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[ID] = [MKTSOURCECODEPART].[SOURCECODEITEMID]
inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
where
[MKTSOURCECODEPART].[SEGMENTATIONID] = @SEGMENTATIONID and
/* ID not an overrided user defined part */
[MKTSOURCECODEPART].[ID] not in (select [ID] from #SEGMENTATIONSEGMENTPARTS) and
[ITEMNAME] = [MKTSOURCECODEPARTDEFINITION].[NAME];
if object_id('tempdb..#SEGMENTATIONSEGMENTPARTS') is not null
drop table #SEGMENTATIONSEGMENTPARTS;
end
else
begin
if object_id('tempdb..#SEGMENTATIONTESTSEGMENTPARTS') is not null
drop table #SEGMENTATIONTESTSEGMENTPARTS;
create table #SEGMENTATIONTESTSEGMENTPARTS ([ID] uniqueidentifier);
insert into #SEGMENTATIONTESTSEGMENTPARTS
select [ID] from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID and [SEGMENTATIONTESTSEGMENTID] is null and [SOURCECODEITEMID] in (select [PARTS].[SOURCECODEITEMID] from (select SOURCECODEITEMID, SEGMENTATIONTESTSEGMENTID from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID group by [SOURCECODEITEMID], SEGMENTATIONTESTSEGMENTID) [PARTS] group by [PARTS].[SOURCECODEITEMID] having COUNT([PARTS].[SOURCECODEITEMID]) > 1);
create clustered index [IX_TMPSEGMENTATIONTESTSEGMENTPARTS_ID] on #SEGMENTATIONTESTSEGMENTPARTS ([ID]) with (PAD_INDEX = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off);
update #SOURCECODETABLE set
[NAME] = '',
[CODE] = isnull([MKTSOURCECODEPART].[CODE], ''),
[CODEVALUEID] = [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[ID] = [MKTSOURCECODEPART].[SOURCECODEITEMID]
inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
where
[MKTSOURCECODEPART].[SEGMENTATIONID] = @SEGMENTATIONID and
/* ID not an overrided user defined part */
[MKTSOURCECODEPART].[ID] not in (select [ID] from #SEGMENTATIONTESTSEGMENTPARTS) and
[ITEMNAME] = [MKTSOURCECODEPARTDEFINITION].[NAME];
if object_id('tempdb..#SEGMENTATIONTESTSEGMENTPARTS') is not null
drop table #SEGMENTATIONTESTSEGMENTPARTS;
end
-- get the list code (or codes for a consolidated list), if we need it...
if exists (select top 1 1
from dbo.[MKTSOURCECODEITEM]
inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID]
inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 6)
begin
if @MARKETINGRECORDTYPE = 1 -- record source
begin
-- get the list code from the record source
select
@LISTCODE = [MKTRECORDSOURCE].[CODE],
@LISTNAME = [QUERYVIEWCATALOG].[DISPLAYNAME],
@LISTCODEVALUEID = [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID]
from dbo.[MKTRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
where [MKTRECORDSOURCE].[ID] = (select [QUERYVIEWCATALOGID] from dbo.[MKTSEGMENT] where [ID] = @SEGMENTID)
and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;
-- update the list code part in our return table
update #SOURCECODETABLE set
[CODE] = isnull(@LISTCODE, ''),
[NAME] = isnull(@LISTNAME, ''),
[CODEVALUEID] = @LISTCODEVALUEID
where [ITEMTYPECODE] = 6;
end
else
begin
-- remove the list part, if it exists
delete #SOURCECODETABLE where [ITEMTYPECODE] = 6;
if @MARKETINGRECORDTYPE = 2 -- list
begin
-- get the list code from the list
select
@LISTCODE = [MKTLIST].[CODE],
@LISTNAME = [MKTLIST].[NAME],
@LISTCODEVALUEID = [MKTLIST].[PARTDEFINITIONVALUESID]
from dbo.[MKTSEGMENT]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
where [MKTSEGMENT].[ID] = @SEGMENTID;
-- insert the list code part into our return table
insert into #SOURCECODETABLE
select
6,
[MKTSOURCECODEPARTDEFINITION].[ITEMTYPE],
[MKTSOURCECODEPARTDEFINITION].[NAME],
[MKTSOURCECODEITEM].[LENGTH],
isnull(@LISTCODE,''),
isnull(@LISTNAME,''),
@LISTCODEVALUEID,
@SOURCECODEID
from dbo.[MKTSOURCECODEITEM]
left join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 6;
end
else -- consolidated list
begin
-- get all the record source and list codes for the consolidated list (only get codes for people actually in the segment)
declare @SEGMENTVIEWNAME nvarchar(128);
declare @CONSOLIDATEDVIEWNAME nvarchar(128);
declare @SQL nvarchar(max);
if object_id('tempdb..#CONSOLCODESTABLE') is not null
drop table #CONSOLCODESTABLE;
create table #CONSOLCODESTABLE (
[SOURCEQUERYVIEWID] uniqueidentifier not null,
[SOURCELISTCODE] nvarchar(50) COLLATE database_default,
[SOURCELISTNAME] nvarchar(100) COLLATE database_default,
[SOURCELISTCODEVALUEID] uniqueidentifier
);
select
@SEGMENTVIEWNAME = dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME]([MKTSEGMENT].[ID]),
@CONSOLIDATEDVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME]
from dbo.[MKTSEGMENT]
inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID]
where [MKTSEGMENT].[ID] = @SEGMENTID;
set @SQL = 'select distinct [CONSOL].[SOURCEQUERYVIEWID], [CONSOL].[SOURCELISTCODE], [CONSOL].[SOURCELISTNAME], [CONSOL].[SOURCELISTCODEVALUEID]' + char(13) +
'from dbo.[' + @SEGMENTVIEWNAME + '] as [SEG]' + char(13) +
'inner join dbo.[' + @CONSOLIDATEDVIEWNAME + '] as [CONSOL] on [CONSOL].[ID] = [SEG].[ID]';
insert into #CONSOLCODESTABLE
exec (@SQL);
if exists(select top 1 1 from #CONSOLCODESTABLE)
begin
-- add all the list codes to our source code temp table to return
declare @CONSOLITEMTYPE nvarchar(255);
declare @CONSOLITEMNAME nvarchar(50);
declare @CONSOLREGEX nvarchar(255);
declare @CONSOLLENGTH tinyint;
-- get the list code info
select
@CONSOLITEMTYPE = [MKTSOURCECODEPARTDEFINITION].[ITEMTYPE],
@CONSOLITEMNAME = [MKTSOURCECODEPARTDEFINITION].[NAME],
@CONSOLLENGTH = [MKTSOURCECODEITEM].[LENGTH]
from dbo.[MKTSOURCECODEITEM]
left join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
where [SOURCECODEID] = @SOURCECODEID
and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 6;
-- insert all the consolidated list codes that the segment is actually using
insert into #SOURCECODETABLE
select
6,
@CONSOLITEMTYPE,
@CONSOLITEMNAME,
@CONSOLLENGTH,
isnull([SOURCELISTCODE], ''),
isnull([SOURCELISTNAME],''),
[SOURCELISTCODEVALUEID],
@SOURCECODEID
from #CONSOLCODESTABLE;
end
end
end
end
end
-- return the source code parts for the segment
select
[ITEMTYPECODE],
[ITEMTYPE],
[ITEMNAME],
[LENGTH],
[CODE],
[NAME],
[CODEVALUEID],
[SOURCECODEID]
from #SOURCECODETABLE;
if object_id('tempdb..#SOURCECODETABLE') is not null
drop table #SOURCECODETABLE;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;