UFN_MKTSOURCECODE_BUILDCODEWITHSEGMENTINFO
Builds the full source code for a given segment ID, making use of the given segment info.
Return
| Return Type |
|---|
| nvarchar(50) |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONSEGMENTID | uniqueidentifier | IN | |
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @SEGMENTATIONACTIVE | bit | IN | |
| @SOURCECODEID | uniqueidentifier | IN | |
| @SOURCECODEMAXSEQUENCE | tinyint | IN | |
| @LISTCODE | nvarchar(10) | IN | |
| @LISTPARTDEFINITIONVALUESID | uniqueidentifier | IN | |
| @ISCONSOLIDATEDSEGMENT | bit | IN | |
| @SOURCECODEITEMID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.[UFN_MKTSOURCECODE_BUILDCODEWITHSEGMENTINFO]
(
@SEGMENTATIONSEGMENTID uniqueidentifier,
@SEGMENTATIONID uniqueidentifier,
@SEGMENTATIONACTIVE bit,
@SOURCECODEID uniqueidentifier,
@SOURCECODEMAXSEQUENCE tinyint,
@LISTCODE nvarchar(10),
@LISTPARTDEFINITIONVALUESID uniqueidentifier,
@ISCONSOLIDATEDSEGMENT bit,
@SOURCECODEITEMID uniqueidentifier
)
returns nvarchar(50)
as
begin
declare @DELIM nvarchar(1);
declare @SOURCECODE nvarchar(50) = '';
declare @PACKAGEID uniqueidentifier;
declare @CHANNELCODE tinyint;
declare @SEGMENTID uniqueidentifier;
declare @ITEMID uniqueidentifier;
declare @TYPECODE tinyint;
declare @LENGTH tinyint;
declare @SOURCECODEPARTDEFINITIONID uniqueidentifier;
declare @SEQUENCE tinyint;
declare @EXCLUDE bit = 0;
declare @ISTESTSEGMENT bit = 0;
select
@EXCLUDE = [EXCLUDE]
from dbo.[MKTSEGMENTATIONSEGMENT]
where [ID] = @SEGMENTATIONSEGMENTID;
-- no source code ID means we've got nothing to do
-- inline exclusions have no source code
if @SOURCECODEID is not null and isnull(@EXCLUDE, 0) = 0
begin
-- try to get a test segment and its info
select
@PACKAGEID = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
@CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
@SEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
@ISTESTSEGMENT = 1
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;
-- if we can't get a test segment, we are dealing with a regular segment
if @ISTESTSEGMENT <> 1
select
@PACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
@CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
@SEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
@ISTESTSEGMENT = 0
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;
declare @PARTDEFINITIONVALUESID uniqueidentifier;
declare @DEFAULTUSERDEFINEDCODE nvarchar(50);
declare @USEDEFAULTUSERDEFINEDCODE bit;
-- we will be iterating over each part in the given source code
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
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 = '';
--Adding local parameters to avoid parameter sniffing
declare @LOCALITEMID uniqueidentifier = @ITEMID;
declare @LOCALSEGMENTATIONID uniqueidentifier = @SEGMENTATIONID;
declare @LOCALSEGMENTATIONSEGMENTID uniqueidentifier = @SEGMENTATIONSEGMENTID;
-- if this is a user-defined part type, get default information
if @TYPECODE = 5
begin
if @ISTESTSEGMENT = 0
begin
-- does this segment use the default
set @USEDEFAULTUSERDEFINEDCODE =
case when exists (select top 1 1
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @LOCALSEGMENTATIONID
and [SOURCECODEITEMID] = @LOCALITEMID
and [SEGMENTATIONSEGMENTID] = @LOCALSEGMENTATIONSEGMENTID)
then 0 else 1 end;
-- get the value for the segment based on whether or not it uses the default
if @USEDEFAULTUSERDEFINEDCODE = 1
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @LOCALSEGMENTATIONID
and [SOURCECODEITEMID] = @LOCALITEMID
and [SEGMENTATIONSEGMENTID] is null
and [SEGMENTATIONTESTSEGMENTID] is null
else -- @USEDEFAULTUSERDEFINEDCODE = 0
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @LOCALSEGMENTATIONID
and [SOURCECODEITEMID] = @LOCALITEMID
and [SEGMENTATIONSEGMENTID] = @LOCALSEGMENTATIONSEGMENTID
end
else -- @ISTESTSEGMENT = 1
begin
-- does this test segment use the default
set @USEDEFAULTUSERDEFINEDCODE =
case when exists (select top 1 1
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @LOCALSEGMENTATIONID
and [SOURCECODEITEMID] = @LOCALITEMID
and [SEGMENTATIONTESTSEGMENTID] = @LOCALSEGMENTATIONSEGMENTID)
then 0 else 1 end;
-- get the value for the test segment based on whether or not it uses the default
if @USEDEFAULTUSERDEFINEDCODE = 1
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @LOCALSEGMENTATIONID
and [SOURCECODEITEMID] = @LOCALITEMID
and [SEGMENTATIONSEGMENTID] is null
and [SEGMENTATIONTESTSEGMENTID] is null
else -- @USEDEFAULTUSERDEFINEDCODE = 0
select
@DEFAULTUSERDEFINEDCODE = [CODE],
@PARTDEFINITIONVALUESID = [PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @LOCALSEGMENTATIONID
and [SOURCECODEITEMID] = @LOCALITEMID
and [SEGMENTATIONTESTSEGMENTID] = @LOCALSEGMENTATIONSEGMENTID
end
end
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] = @LOCALSEGMENTATIONID)
when @TYPECODE = 1 then case @ISTESTSEGMENT
when 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] = @LOCALSEGMENTATIONSEGMENTID)
end
when @TYPECODE = 2 and @SEGMENTATIONACTIVE = 0 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when @TYPECODE = 2 and @SEGMENTATIONACTIVE = 1 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @LOCALSEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
when @TYPECODE = 3 and @SEGMENTATIONACTIVE = 0 then (select [CHANNELPARTDEFINITIONVALUESID] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when @TYPECODE = 3 and @SEGMENTATIONACTIVE = 1 then (select [CHANNELPARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @LOCALSEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
when @TYPECODE = 6 then (select @LISTPARTDEFINITIONVALUESID)
when @TYPECODE = 7 then case @ISTESTSEGMENT
when 1 then (select [TESTPARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @LOCALSEGMENTATIONSEGMENTID)
else (select [TESTPARTDEFINITIONVALUESID] from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @LOCALSEGMENTATIONSEGMENTID)
end
end
end
-- set true if value is a valid value for the given source code
declare @VALIDVALUE bit;
set @VALIDVALUE =
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;
-- appead the current source code part on the source code
set @SOURCECODE =
@SOURCECODE
+ (select right(replicate('_', @LENGTH)
+ case when @TYPECODE = 0 -- 0 = mailing
then (select isnull([CODE], '') from dbo.[MKTSEGMENTATION] where [ID] = @LOCALSEGMENTATIONID)
when @TYPECODE = 1 and @SEGMENTATIONACTIVE = 0 and @VALIDVALUE = 1 -- 1 = segment or test segment
then case @ISTESTSEGMENT
when 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] = @LOCALSEGMENTATIONSEGMENTID)
end
when @TYPECODE = 1 and @SEGMENTATIONACTIVE = 1
then case @ISTESTSEGMENT
when 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] = @LOCALSEGMENTATIONSEGMENTID)
end
when @TYPECODE = 2 and @SEGMENTATIONACTIVE = 0 and @VALIDVALUE = 1 -- 2 = package, inactive
then (select isnull([CODE], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when (@TYPECODE = 2 and @SEGMENTATIONACTIVE = 1) -- 2 = package, active
then (select isnull([CODE], '') from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @LOCALSEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
when @TYPECODE = 3 and @SEGMENTATIONACTIVE = 0 and @VALIDVALUE = 1 -- 3 = channel, inactive
then (select isnull([CHANNELSOURCECODE], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
when @TYPECODE = 3 and @SEGMENTATIONACTIVE = 1 -- 3 = channel, active
then (select isnull([CHANNELSOURCECODE], '') from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
when @TYPECODE = 4 -- 4 = date
then (select isnull([CODE], '') from dbo.[MKTSOURCECODEPART] where [SEGMENTATIONID] = @LOCALSEGMENTATIONID and [SOURCECODEITEMID] = @LOCALITEMID and [SEGMENTATIONSEGMENTID] = @LOCALSEGMENTATIONSEGMENTID)
when @TYPECODE = 5 and @ISTESTSEGMENT = 0 -- 5 = user defined
then case @USEDEFAULTUSERDEFINEDCODE
when 0 then (select isnull([MKTSOURCECODEPART].[CODE], '') from [MKTSOURCECODEPART] where [SEGMENTATIONID] = @LOCALSEGMENTATIONID and [SOURCECODEITEMID] = @LOCALITEMID and [SEGMENTATIONSEGMENTID] = @SEGMENTATIONSEGMENTID)
else @DEFAULTUSERDEFINEDCODE
end
when @TYPECODE = 5 and @ISTESTSEGMENT = 1
then case @USEDEFAULTUSERDEFINEDCODE
when 0 then (select isnull([MKTSOURCECODEPART].[CODE], '') from [MKTSOURCECODEPART] where [SEGMENTATIONID] = @LOCALSEGMENTATIONID and [SOURCECODEITEMID] = @LOCALITEMID and [SEGMENTATIONTESTSEGMENTID] = @LOCALSEGMENTATIONSEGMENTID)
else @DEFAULTUSERDEFINEDCODE
end
when @TYPECODE = 6 and @ISCONSOLIDATEDSEGMENT = 1 -- 6 = list
then '**********' -- consolidated list
when @TYPECODE = 6 and @SEGMENTATIONACTIVE = 0 and @VALIDVALUE = 1
then @LISTCODE
when @TYPECODE = 6 and @SEGMENTATIONACTIVE = 1
then @LISTCODE
when @TYPECODE = 7 -- 7 = test segment
then case @ISTESTSEGMENT
when 1 then (select isnull([TESTSEGMENTCODE], '') from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @LOCALSEGMENTATIONSEGMENTID)
else (select isnull([TESTSEGMENTCODE], '') from dbo.[MKTSEGMENTATIONSEGMENT] where [ID] = @LOCALSEGMENTATIONSEGMENTID)
end
else replicate('_', @LENGTH)
end, @LENGTH))
-- add the part's delimiter, if needed
if @SEQUENCE < @SOURCECODEMAXSEQUENCE
select @SOURCECODE = @SOURCECODE + isnull([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM], '')
from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES]
where [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = @PARTDEFINITIONVALUESID;
fetch next from PARTCURSOR into @ITEMID, @TYPECODE, @LENGTH, @SOURCECODEPARTDEFINITIONID, @SEQUENCE;
end
close PARTCURSOR;
deallocate PARTCURSOR;
end
return @SOURCECODE;
end