UFN_MKTSOURCECODE_BUILDCODE
Builds the full source code for a given segment ID.
Return
| Return Type |
|---|
| nvarchar(50) |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @QUERYVIEWCATALOGID | uniqueidentifier | IN | |
| @SOURCECODEITEMID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.[UFN_MKTSOURCECODE_BUILDCODE]
(
@ID uniqueidentifier,
@QUERYVIEWCATALOGID uniqueidentifier = null,
@SOURCECODEITEMID uniqueidentifier = null
)
/*
builds the full source code for a given segment ID
if a query view catalog id is passed in, then it is used instead to get the list code (not used for vendor managed segments)
*/
returns nvarchar(50)
as
begin
declare @ACTIVE bit;
declare @TYPECODE tinyint;
declare @VALUE nvarchar(10);
declare @LISTCODE nvarchar(10);
declare @LISTPARTDEFINITIONVALUESID uniqueidentifier;
declare @DELIMITER nvarchar(1);
declare @LENGTH tinyint;
declare @SOURCECODE nvarchar(50);
declare @ITEMID uniqueidentifier;
declare @SEGMENTATIONID uniqueidentifier;
declare @PACKAGEID uniqueidentifier;
declare @CHANNELCODE tinyint;
declare @SEGMENTID uniqueidentifier;
declare @SOURCECODEID uniqueidentifier;
declare @SOURCECODEISHISTORICAL bit;
declare @RECORDTYPEID uniqueidentifier;
declare @ISTESTSEGMENT bit;
declare @ISWHITEMAILSEGMENT bit;
declare @LISTID uniqueidentifier;
declare @SOURCECODEPARTDEFINITIONID uniqueidentifier;
declare @SEQUENCE tinyint;
declare @MAXSEQUENCE tinyint;
set @SOURCECODE = '';
set @LISTCODE = '';
set @ISWHITEMAILSEGMENT = 0;
set @ISTESTSEGMENT = 0;
-- check to see if the ID is a white mail segment
-- (note that associating white mail segments with marketing efforts was ultimately scrapped and thus this will never return a row)
select
@ACTIVE = isnull([MKTSEGMENTATION].[ACTIVE], 0),
@SEGMENTATIONID = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID],
@PACKAGEID = null,
@SEGMENTID = null,
@CHANNELCODE = null,
@SOURCECODEID = [MKTSEGMENTWHITEMAIL].[SOURCECODEID],
@SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0),
@ISWHITEMAILSEGMENT = 1
from dbo.[MKTSEGMENTWHITEMAIL]
left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID]
left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
where [MKTSEGMENTWHITEMAIL].[ID] = @ID;
if @ISWHITEMAILSEGMENT = 0
-- check to see if the ID is a test segment
select
@ACTIVE = [MKTSEGMENTATION].[ACTIVE],
@SEGMENTATIONID = [MKTSEGMENTATION].[ID],
@PACKAGEID = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
@SEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
@CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
@SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
@SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0),
@ISTESTSEGMENT = 1
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @ID;
if @ISWHITEMAILSEGMENT = 0 and @ISTESTSEGMENT = 0
-- not a test segment, so grab the info from the segment
select
@ACTIVE = [MKTSEGMENTATION].[ACTIVE],
@SEGMENTATIONID = [MKTSEGMENTATION].[ID],
@PACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
@CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
@SEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
@SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
@SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0)
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;
if @SOURCECODEID is not null
if @SOURCECODEISHISTORICAL = 0
begin
if @ISWHITEMAILSEGMENT = 0
-- get the list code
if @QUERYVIEWCATALOGID is null
if @ACTIVE = 0
-- get the list code from the record source, list, or consolidated list
select
@LISTCODE = (case when exists (select [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] inner join dbo.[QUERYVIEWCATALOG] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID] where [QUERYVIEWCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID])
then ''
else isnull([MKTLIST].[CODE], (select [MKTRECORDSOURCE].[CODE] from dbo.[MKTRECORDSOURCE] where [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID] and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1))
end),
@LISTPARTDEFINITIONVALUESID = (case when exists (select [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] inner join dbo.[QUERYVIEWCATALOG] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID] where [QUERYVIEWCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID])
then null
else isnull([MKTLIST].[PARTDEFINITIONVALUESID], (select [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID] from dbo.[MKTRECORDSOURCE] where [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID] and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1))
end)
from dbo.[MKTSEGMENT]
left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
left join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
left join dbo.[IDSETREGISTER] on [MKTSEGMENT].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
left join dbo.[RECORDTYPE] on [IDSETREGISTER].[RECORDTYPEID] = [RECORDTYPE].[ID]
where [MKTSEGMENT].[ID] = case when @ISWHITEMAILSEGMENT = 1 then @ID else @SEGMENTID end;
else
begin
select
@LISTID = (case when [CQV].[ID] is null
then isnull([MKTSEGMENTLIST].[LISTID], [MKTRECORDSOURCE].[ID])
else null
end)
from dbo.[MKTSEGMENT]
inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
-- to figure out if the segment is based on the consolidated list or not...
left join dbo.[QUERYVIEWCATALOG] as [CQV] on [CQV].[RECORDTYPEID] = [IDSETREGISTER].[RECORDTYPEID] and [CQV].[ID] = dbo.[UFN_MKTRECORDSOURCE_GETCONSOLIDATEDQUERYVIEWID]([MKTRECORDSOURCE].[ID])
where [MKTSEGMENT].[ID] = case when @ISWHITEMAILSEGMENT = 1 then @ID else @SEGMENTID end
and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;
if @LISTID is not null
select
@LISTCODE = isnull([CODE], ''),
@LISTPARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [LISTID] = @LISTID;
else
begin
set @LISTCODE = '';
set @LISTPARTDEFINITIONVALUESID = null;
end
end
else
begin
-- get the list code from the QueryViewCatalogID passed in
set @LISTCODE = isnull((select [CODE] from dbo.[MKTLIST] inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[LISTID] = [MKTLIST].[ID] where [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID),
(select [CODE] from dbo.[MKTRECORDSOURCE] where [ID] = @QUERYVIEWCATALOGID and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1));
set @LISTPARTDEFINITIONVALUESID = isnull((select [PARTDEFINITIONVALUESID] from dbo.[MKTLIST] inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[LISTID] = [MKTLIST].[ID] where [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID),
(select [PARTDEFINITIONVALUESID] from dbo.[MKTRECORDSOURCE] where [ID] = @QUERYVIEWCATALOGID and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1));
end
select @MAXSEQUENCE = max([SEQUENCE]) from [MKTSOURCECODEITEM] where [SOURCECODEID] = @SOURCECODEID;
declare @PARTDEFINITIONVALUESID uniqueidentifier;
declare @DEFAULTUSERDEFINEDCODE nvarchar(50);
declare @USEDEFAULTUSERDEFINEDCODE bit;
declare PARTCURSOR cursor local fast_forward for
select
[MKTSOURCECODEITEM].[ID],
[MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE],
[MKTSOURCECODEITEM].[LENGTH],
[MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID],
[MKTSOURCECODEITEM].[SEQUENCE]
from dbo.[MKTSOURCECODEITEM]
inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
where [SOURCECODEID] = @SOURCECODEID
-- BTR CR292456-013008 1/30/2008
-- added this as an optional parameter allowing the caller to get the code for an
-- individual source code item, so that code needn't be duplicated in the
-- source code performance report
and (@SOURCECODEITEMID is null or [MKTSOURCECODEITEM].[ID] = @SOURCECODEITEMID)
order by [SEQUENCE];
open PARTCURSOR;
fetch next from PARTCURSOR into @ITEMID, @TYPECODE, @LENGTH, @SOURCECODEPARTDEFINITIONID, @SEQUENCE;
while (@@FETCH_STATUS = 0)
begin
set @PARTDEFINITIONVALUESID = null;
set @DEFAULTUSERDEFINEDCODE = '';
if @ISWHITEMAILSEGMENT = 1 and @TYPECODE in (0, 2, 3, 5, 6, 7)
begin
if @TYPECODE in (0, 5)
set @USEDEFAULTUSERDEFINEDCODE =
case when exists(select top 1 1 from dbo.[MKTSOURCECODEPART] where [SOURCECODEITEMID] = @ITEMID and [WHITEMAILSEGMENTID] = @ID) then 0 else 1 end;
if @TYPECODE = 0 and @USEDEFAULTUSERDEFINEDCODE = 1
begin
if @SEGMENTATIONID is not null
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSEGMENTATION]
where [ID] = @SEGMENTATIONID
end
else if @TYPECODE = 5 and @USEDEFAULTUSERDEFINEDCODE = 1
begin
if @SEGMENTATIONID is not null
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [SOURCECODEITEMID] = @ITEMID
and [SEGMENTATIONSEGMENTID] is null
and [SEGMENTATIONTESTSEGMENTID] is null
and [WHITEMAILSEGMENTID] is null
end
else
select
@DEFAULTUSERDEFINEDCODE = isnull([CODE], ''),
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SOURCECODEITEMID] = @ITEMID
and [WHITEMAILSEGMENTID] = @ID
end
else if @ISTESTSEGMENT = 1 and @TYPECODE = 5
begin
set @USEDEFAULTUSERDEFINEDCODE =
case when exists(select top 1 1 from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID and [SOURCECODEITEMID] = @ITEMID and [SEGMENTATIONTESTSEGMENTID] = @ID) then 0 else 1 end;
if @USEDEFAULTUSERDEFINEDCODE = 1
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [SOURCECODEITEMID] = @ITEMID
and [SEGMENTATIONSEGMENTID] is null
and [SEGMENTATIONTESTSEGMENTID] is null
and [WHITEMAILSEGMENTID] is null
else
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [SOURCECODEITEMID] = @ITEMID
and [SEGMENTATIONTESTSEGMENTID] = @ID
end
else if @TYPECODE = 5
begin
set @USEDEFAULTUSERDEFINEDCODE =
case when exists(select top 1 1 from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID and [SOURCECODEITEMID] = @ITEMID and [SEGMENTATIONSEGMENTID] = @ID) then 0 else 1 end;
if @USEDEFAULTUSERDEFINEDCODE = 1
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [SOURCECODEITEMID] = @ITEMID
and [SEGMENTATIONSEGMENTID] is null
and [SEGMENTATIONTESTSEGMENTID] is null
and [WHITEMAILSEGMENTID] is null
else
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @SEGMENTATIONID
and [SOURCECODEITEMID] = @ITEMID
and [SEGMENTATIONSEGMENTID] = @ID
end
set @DELIMITER = '';
if @PARTDEFINITIONVALUESID is null
begin
-- get the part definition values ID for itemtype if not already set
set @PARTDEFINITIONVALUESID =
case
when @TYPECODE = 0 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)
when @TYPECODE = 1 then (case when @ISWHITEMAILSEGMENT = 1 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENT] where [ID] = @ID) when @ISTESTSEGMENT = 1 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @ID) else (select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @ID) end)
when @TYPECODE = 2 and @ACTIVE = 0 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when @TYPECODE = 2 and @ACTIVE = 1 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
when @TYPECODE = 3 and @ACTIVE = 0 then (select [CHANNELPARTDEFINITIONVALUESID] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when @TYPECODE = 3 and @ACTIVE = 1 then (select [CHANNELPARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
when @TYPECODE = 6 then (select @LISTPARTDEFINITIONVALUESID)
when @TYPECODE = 7 then (case when @ISTESTSEGMENT = 1 then (select [TESTPARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @ID) else (select [TESTPARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @ID) end)
end
end
-- set true if value is a valid value for the given source code
declare @VALIDVALUE bit;
set @VALIDVALUE =
case when @ISWHITEMAILSEGMENT = 1 and (@TYPECODE in (0, 5) and @SEGMENTATIONID is null)
then 1 -- white mail segments will have blank values for these parts (i.e. @PARTDEFINITIONVALUESID will be null)
else
case when exists(select top 1 1
from dbo.[MKTSOURCECODEVALIDPARTVALUES]
inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID]
where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
and [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID)
then 1 else 0 end
end;
set @SOURCECODE = @SOURCECODE +
(select right(replicate('_', @LENGTH) +
(case
-- 0 = mailing
when @TYPECODE = 0
then (case when @ISWHITEMAILSEGMENT = 1 then @DEFAULTUSERDEFINEDCODE else (select isnull([CODE], '') from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) end)
-- 1 = segment or test segment
when (@TYPECODE = 1 and @ACTIVE = 0 and @VALIDVALUE = 1)
then (case when @ISWHITEMAILSEGMENT = 1 then (select isnull([CODE], '') from dbo.[MKTSEGMENT] where [ID] = @ID) when @ISTESTSEGMENT = 1 then (select isnull([CODE], '') from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @ID) else (select isnull([CODE], '') from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @ID) end)
when (@TYPECODE = 1 and @ACTIVE = 1)
then (case when @ISWHITEMAILSEGMENT = 1 then (select isnull([CODE], '') from dbo.[MKTSEGMENT] where [ID] = @ID) when @ISTESTSEGMENT = 1 then (select isnull([CODE], '') from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @ID) else (select isnull([CODE], '') from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @ID) end)
-- 2 = package, inactive
when (@TYPECODE = 2 and @ACTIVE = 0 and @VALIDVALUE = 1)
then (case when @ISWHITEMAILSEGMENT = 1 then @DEFAULTUSERDEFINEDCODE else (select isnull([CODE], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID) end)
-- 2 = package, active
when (@TYPECODE = 2 and @ACTIVE = 1)
then (case when @ISWHITEMAILSEGMENT = 1 then @DEFAULTUSERDEFINEDCODE else (select isnull([CODE], '') from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID) end)
-- 3 = channel
when (@TYPECODE = 3 and @ACTIVE = 0 and @VALIDVALUE = 1)
then (case when @ISWHITEMAILSEGMENT = 1 then @DEFAULTUSERDEFINEDCODE else (select isnull([CHANNELSOURCECODE], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID) end)
when (@TYPECODE = 3 and @ACTIVE = 1)
then (case when @ISWHITEMAILSEGMENT = 1 then @DEFAULTUSERDEFINEDCODE else (select isnull([CHANNELSOURCECODE], '') from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID) end)
-- 4 = date
when (@TYPECODE = 4)
then (select isnull([CODE], '') from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID and [SOURCECODEITEMID] = @ITEMID and [SEGMENTATIONSEGMENTID] = @ID)
-- 5 = user defined
when (@TYPECODE = 5 and @ISWHITEMAILSEGMENT = 1)
then (select case when @USEDEFAULTUSERDEFINEDCODE = 0 then (select isnull([MKTSOURCECODEPART].[CODE], '') from [MKTSOURCECODEPART] where [SOURCECODEITEMID] = @ITEMID and [WHITEMAILSEGMENTID] = @ID) else @DEFAULTUSERDEFINEDCODE end)
when (@TYPECODE = 5 and @ISTESTSEGMENT = 1)
then (select case when @USEDEFAULTUSERDEFINEDCODE = 0 then (select isnull([MKTSOURCECODEPART].[CODE], '') from [MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID and [SOURCECODEITEMID] = @ITEMID and [SEGMENTATIONTESTSEGMENTID] = @ID) else @DEFAULTUSERDEFINEDCODE end)
when (@TYPECODE = 5 and @ISWHITEMAILSEGMENT = 0 and @ISTESTSEGMENT = 0)
then (select case when @USEDEFAULTUSERDEFINEDCODE = 0 then (select isnull([MKTSOURCECODEPART].[CODE], '') from [MKTSOURCECODEPART] where [SEGMENTATIONID] = @SEGMENTATIONID and [SOURCECODEITEMID] = @ITEMID and [SEGMENTATIONSEGMENTID] = @ID) else @DEFAULTUSERDEFINEDCODE end)
-- 6 = list
when @TYPECODE = 6
then '**********' + case when @ISWHITEMAILSEGMENT = 1 then @DEFAULTUSERDEFINEDCODE else @LISTCODE end
-- 7 = test segment
when @TYPECODE = 7
then (case when @ISWHITEMAILSEGMENT = 1 then @DEFAULTUSERDEFINEDCODE when @ISTESTSEGMENT = 1 then (select isnull([TESTSEGMENTCODE], '') from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @ID) else (select isnull([TESTSEGMENTCODE], '') from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @ID) end)
else
replicate('_', @LENGTH)
end), @LENGTH))
select
@DELIMITER = isnull([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM], '')
from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES]
where [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = @PARTDEFINITIONVALUESID;
-- don't add the delimiter on the end of the source code
if @SEQUENCE < @MAXSEQUENCE
set @SOURCECODE = @SOURCECODE + @DELIMITER;
fetch next from PARTCURSOR into @ITEMID, @TYPECODE, @LENGTH, @SOURCECODEPARTDEFINITIONID, @SEQUENCE;
end
close PARTCURSOR;
deallocate PARTCURSOR;
end
else -- @SOURCECODEISHISTORICAL = 1
if @ISWHITEMAILSEGMENT = 1
select @SOURCECODE = [SOURCECODE]
from dbo.[MKTSOURCECODEMAP]
where [SEGMENTATIONID] = @SEGMENTATIONID and [WHITEMAILSEGMENTID] = @ID;
else if @ISTESTSEGMENT = 1
select @SOURCECODE = [SOURCECODE]
from dbo.[MKTSOURCECODEMAP]
where [SEGMENTATIONID] = @SEGMENTATIONID and [SEGMENTATIONTESTSEGMENTID] = @ID;
else
select @SOURCECODE = [SOURCECODE]
from dbo.[MKTSOURCECODEMAP]
where [SEGMENTATIONID] = @SEGMENTATIONID and [SEGMENTATIONSEGMENTID] = @ID and [SEGMENTATIONTESTSEGMENTID] is null;
return @SOURCECODE;
end