USP_MKTSEGMENTATIONSEGMENT_COPYSEGMENTSFROMEFFORT
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @TARGETSEGMENTATIONID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SOURCESEGMENTATIONID | uniqueidentifier | IN |
Definition
Copy
create procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_COPYSEGMENTSFROMEFFORT]
(
@TARGETSEGMENTATIONID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@SOURCESEGMENTATIONID uniqueidentifier
)
as
begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;
/********************************/
/* Copy the segments via cursor */
/********************************/
declare @SOURCESEGMENTID uniqueidentifier;
declare @TARGETSEGMENTID uniqueidentifier;
declare @TARGETTESTSEGMENTID uniqueidentifier;
declare @SEGMENTID uniqueidentifier;
declare @EXCLUDE bit;
declare @SEGMENTTYPECODE tinyint;
declare @OVERRIDEQUANTITIESANDORSEGMENTCOSTS bit;
declare @OVERRIDELISTCOSTS bit;
declare @BASERENTALCOST money;
declare @BASERENTALCOSTBASISCODE tinyint;
declare @RENTALQUANTITY integer;
declare @RENTALCOSTADJUSTMENT money;
declare @RENTALCOSTADJUSTMENTBASISCODE tinyint;
declare @BASEEXCHANGECOST money;
declare @BASEEXCHANGECOSTBASISCODE tinyint;
declare @EXCHANGEQUANTITY integer;
declare @EXCHANGECOSTADJUSTMENT money;
declare @EXCHANGECOSTADJUSTMENTBASISCODE tinyint;
declare @PACKAGEID uniqueidentifier;
declare @PACKAGECODE nvarchar(10);
declare @PACKAGECODEVALUEID uniqueidentifier;
declare @CHANNELSOURCECODE nvarchar(10);
declare @CHANNELSOURCECODEVALUEID uniqueidentifier;
declare @RESPONSERATE decimal(5, 2);
declare @GIFTAMOUNT money;
declare @SAMPLESIZE int;
declare @SAMPLESIZETYPECODE tinyint;
declare @SAMPLESIZEMETHODCODE tinyint;
declare @SAMPLESIZEEXCLUDEREMAINDER bit;
declare @ASKLADDERID uniqueidentifier;
declare @USAGECODE tinyint;
declare @SEQUENCE int;
declare @TESTSEGMENTCODE nvarchar(10);
declare @TESTSEGMENTCODEVALUEID uniqueidentifier;
declare @OVERRIDEADDRESSPROCESSING bit;
declare @SEGMENTUSEADDRESSPROCESSING bit;
declare @SEGMENTADDRESSPROCESSINGOPTIONID uniqueidentifier;
declare @SEGMENTNAMEFORMATPARAMETERID uniqueidentifier;
declare @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
declare @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
declare @EXPOSURESTARTDATE datetime;
declare @EXPOSUREENDDATE datetime;
declare @PREFIXCODE tinyint;
declare @SOURCETESTSEGMENTID uniqueidentifier;
declare @FRACTION nvarchar(10);
declare @ORGANIZATIONGIFTAMOUNT money;
declare @MAILINGTYPECODE tinyint;
declare @CODE nvarchar(10);
declare @CODEVALUEID uniqueidentifier;
declare @NAME nvarchar(100);
declare @CURRENTDATE datetime = getdate();
declare @BASECURRENCYID uniqueidentifier;
declare @OVERRIDEBUSINESSUNITS bit;
declare @DESCRIPTION nvarchar(255);
declare @ORGANIZATIONBASERENTALCOST money;
declare @ORGANIZATIONBASEEXCHANGECOST money;
declare @ORGANIZATIONRENTALCOSTADJUSTMENT money;
declare @ORGANIZATIONEXCHANGECOSTADJUSTMENT money;
declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
--for the next two variables
--1 means it has a template that has the option checked or the effort does not have a template so it allows it by default
--0 means it has a template and is explicitly not allowed so we have to not copy over the information
declare @TARGETALLOWEFFORTBUSINESSUNITSOVERRIDE bit = 1;
declare @TARGETALLOWEFFORTADDRESSPROCESSINGOVERRIDE bit = 1;
select
@MAILINGTYPECODE = [MAILINGTYPECODE],
@BASECURRENCYID = [BASECURRENCYID]
from
dbo.[MKTSEGMENTATION]
where
[ID] = @SOURCESEGMENTATIONID;
select
@ORGANIZATIONCURRENCYEXCHANGERATEID = [ORGANIZATIONCURRENCYEXCHANGERATEID]
from
dbo.[MKTSEGMENTATIONBUDGET]
where
[ID] = @TARGETSEGMENTATIONID;
select
@SEQUENCE = isnull(max([SEQUENCE]), 0)
from
dbo.[MKTSEGMENTATIONSEGMENT]
where
[SEGMENTATIONID] = @TARGETSEGMENTATIONID;
--store the duplicate information so we can filter them out when copying
declare @DUPLICATESEGMENTATIONSEGMENTID table ([ID] uniqueidentifier);
with SOURCEEFFORTSEGMENTS_CTE as (
select
[MKTSEGMENTATIONSEGMENT].[ID] [SOURCESEGMENTATIONSEGMENTID],
[MKTSEGMENTATIONSEGMENT].[SEGMENTID],
[MKTSEGMENTATIONSEGMENT].[PACKAGEID]
from
dbo.[MKTSEGMENTATIONSEGMENT]
where
[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SOURCESEGMENTATIONID
)
insert into @DUPLICATESEGMENTATIONSEGMENTID
select
[SOURCEEFFORTSEGMENTS_CTE].[SOURCESEGMENTATIONSEGMENTID]
from
dbo.[MKTSEGMENTATIONSEGMENT]
inner join [SOURCEEFFORTSEGMENTS_CTE] on [SOURCEEFFORTSEGMENTS_CTE].[PACKAGEID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID] and [SOURCEEFFORTSEGMENTS_CTE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
where
[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @TARGETSEGMENTATIONID;
--get the business unit and address processing segment level override information for the target
select
@TARGETALLOWEFFORTBUSINESSUNITSOVERRIDE = [ALLOWEFFORTBUSINESSUNITSOVERRIDE],
@TARGETALLOWEFFORTADDRESSPROCESSINGOVERRIDE = [ALLOWEFFORTADDRESSPROCESSINGOVERRIDE]
from
dbo.[MKTCOMMUNICATIONTEMPLATE]
where
[MKTSEGMENTATIONID] = @TARGETSEGMENTATIONID;
declare SEGMENTCURSOR cursor local fast_forward for
select
[MKTSEGMENTATIONSEGMENT].[ID],
[MKTSEGMENTATIONSEGMENT].[SEGMENTID],
[MKTSEGMENTATIONSEGMENT].[EXCLUDE],
[MKTSEGMENT].[SEGMENTTYPECODE],
isnull((case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then [MKTSEGMENTATIONSEGMENT].[CODE] else [MKTSEGMENT].[CODE] end), ''),
(case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then [MKTSEGMENTATIONSEGMENT].[PARTDEFINITIONVALUESID] else [MKTSEGMENT].[PARTDEFINITIONVALUESID] end),
[MKTSEGMENTATIONSEGMENT].[PACKAGEID],
isnull((case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then [MKTSEGMENTATIONPACKAGE].[CODE] else [MKTPACKAGE].[CODE] end), ''),
(case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then [MKTSEGMENTATIONPACKAGE].[PARTDEFINITIONVALUESID] else [MKTPACKAGE].[PARTDEFINITIONVALUESID] end),
isnull((case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then [MKTSEGMENTATIONPACKAGE].[CHANNELSOURCECODE] else [MKTPACKAGE].[CHANNELSOURCECODE] end), ''),
(case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then [MKTSEGMENTATIONPACKAGE].[CHANNELPARTDEFINITIONVALUESID] else [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] end),
[MKTSEGMENTATIONSEGMENT].[RESPONSERATE],
[MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZE],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZETYPECODE],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZEMETHODCODE],
[MKTSEGMENTATIONSEGMENT].[SAMPLESIZEEXCLUDEREMAINDER],
[MKTSEGMENTATIONSEGMENT].[ASKLADDERID],
[MKTSEGMENTATIONSEGMENT].[USAGECODE],
[MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE],
[MKTSEGMENTATIONSEGMENT].[TESTPARTDEFINITIONVALUESID],
(case when @TARGETALLOWEFFORTADDRESSPROCESSINGOVERRIDE = 0 then 0 else [MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] end),
(case when @TARGETALLOWEFFORTADDRESSPROCESSINGOVERRIDE = 0 then 0 else [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] end),
(case when @TARGETALLOWEFFORTADDRESSPROCESSINGOVERRIDE = 0 then null else [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONID] end),
(case when @TARGETALLOWEFFORTADDRESSPROCESSINGOVERRIDE = 0 then null else [MKTSEGMENTATIONSEGMENT].[NAMEFORMATPARAMETERID] end),
(case when @TARGETALLOWEFFORTADDRESSPROCESSINGOVERRIDE = 0 then null else [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] end),
(case when @TARGETALLOWEFFORTADDRESSPROCESSINGOVERRIDE = 0 then null else [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] end),
[MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
[MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE],
(case when @TARGETALLOWEFFORTBUSINESSUNITSOVERRIDE = 0 then 0 else [MKTSEGMENTATIONSEGMENT].[OVERRIDEBUSINESSUNITS] end)
from
dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
left outer join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID]
where
[MKTSEGMENTATION].[ID] = @SOURCESEGMENTATIONID
and [MKTSEGMENTATIONSEGMENT].[ID] not in (select [ID] from @DUPLICATESEGMENTATIONSEGMENTID)
order by
[MKTSEGMENTATIONSEGMENT].[SEQUENCE] asc;
open SEGMENTCURSOR;
fetch next from SEGMENTCURSOR into @SOURCESEGMENTID, @SEGMENTID, @EXCLUDE, @SEGMENTTYPECODE, @CODE, @CODEVALUEID, @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @RESPONSERATE, @GIFTAMOUNT, @SAMPLESIZE, @SAMPLESIZETYPECODE, @SAMPLESIZEMETHODCODE, @SAMPLESIZEEXCLUDEREMAINDER, @ASKLADDERID, @USAGECODE, @TESTSEGMENTCODE, @TESTSEGMENTCODEVALUEID, @OVERRIDEADDRESSPROCESSING, @SEGMENTUSEADDRESSPROCESSING, @SEGMENTADDRESSPROCESSINGOPTIONID, @SEGMENTNAMEFORMATPARAMETERID, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @EXPOSURESTARTDATE, @EXPOSUREENDDATE, @OVERRIDEBUSINESSUNITS;
while (@@FETCH_STATUS = 0)
begin
set @TARGETSEGMENTID = null;
set @SEQUENCE = @SEQUENCE + 1;
if @MAILINGTYPECODE <> 4
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT]
@ID = @TARGETSEGMENTID output,
@CHANGEAGENTID = @CHANGEAGENTID,
@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE = @TARGETSEGMENTATIONID,
@MARKETINGPLANBRIEFID = null,
@SEGMENTID = @SEGMENTID,
@CODE = @CODE,
@TESTSEGMENTCODE = @TESTSEGMENTCODE,
@PACKAGEID = @PACKAGEID,
@PACKAGECODE = @PACKAGECODE,
@RESPONSERATE = @RESPONSERATE,
@GIFTAMOUNT = @GIFTAMOUNT,
@SAMPLESIZE = @SAMPLESIZE,
@SAMPLESIZETYPECODE = @SAMPLESIZETYPECODE,
@SAMPLESIZEMETHODCODE = @SAMPLESIZEMETHODCODE,
@SEQUENCE = @SEQUENCE,
@ASKLADDERID = @ASKLADDERID,
@SAMPLESIZEEXCLUDEREMAINDER = @SAMPLESIZEEXCLUDEREMAINDER,
@OVERRIDEADDRESSPROCESSING = @OVERRIDEADDRESSPROCESSING,
@USEADDRESSPROCESSING = @SEGMENTUSEADDRESSPROCESSING,
@ADDRESSPROCESSINGOPTIONID = @SEGMENTADDRESSPROCESSINGOPTIONID,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
@NAMEFORMATPARAMETERID = @SEGMENTNAMEFORMATPARAMETERID,
@CODEVALUEID = @CODEVALUEID,
@TESTSEGMENTCODEVALUEID = @TESTSEGMENTCODEVALUEID,
@PACKAGECODEVALUEID = @PACKAGECODEVALUEID,
@ITEMLIST = null,
@CHANNELSOURCECODE = @CHANNELSOURCECODE,
@CHANNELSOURCECODEVALUEID = @CHANNELSOURCECODEVALUEID,
@OVERRIDEBUSINESSUNITS = @OVERRIDEBUSINESSUNITS,
@BUSINESSUNITS = null,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@EXCLUDE = @EXCLUDE
else
begin
exec dbo.[USP_MKTSEGMENTATIONPASSIVESEGMENT_ADD]
@ID = @TARGETSEGMENTID output,
@CHANGEAGENTID = @CHANGEAGENTID,
@SEGMENTATIONID = @TARGETSEGMENTATIONID,
@SEGMENTID = @SEGMENTID,
@CODEVALUEID = @CODEVALUEID,
@CODE = @CODE,
@PACKAGEID = @PACKAGEID,
@PACKAGECODEVALUEID = @PACKAGECODEVALUEID,
@PACKAGECODE = @PACKAGECODE,
@CHANNELCODEVALUEID = @CHANNELSOURCECODEVALUEID,
@CHANNELCODE = @CHANNELSOURCECODE,
@EXPOSURESTARTDATE = @EXPOSURESTARTDATE,
@EXPOSUREENDDATE = @EXPOSUREENDDATE,
@RESPONSERATE = @RESPONSERATE,
@GIFTAMOUNT = @GIFTAMOUNT,
@ITEMLIST = null,
@CURRENTAPPUSERID = @CURRENTAPPUSERID;
end
-- BTR CR288047-111407 11/14/2007
-- altered this logic to work correctly for activated mailings with list segments
-- can't use USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTLIST
if @SEGMENTTYPECODE = 2 -- list segment
begin
-- save the usage code
update dbo.[MKTSEGMENTATIONSEGMENT] set
[USAGECODE] = @USAGECODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [ID] = @TARGETSEGMENTID;
-- copy any deliberately overridden costs
if exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENTLIST] where [ID] = @SOURCESEGMENTID and ([OVERRIDEQUANTITIESANDORSEGMENTCOSTS] = 1 or [OVERRIDELISTCOSTS] = 1))
begin
select
@OVERRIDEQUANTITIESANDORSEGMENTCOSTS = [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
@OVERRIDELISTCOSTS = [OVERRIDELISTCOSTS],
@BASERENTALCOST = [BASERENTALCOST],
@BASERENTALCOSTBASISCODE = [BASERENTALCOSTBASISCODE],
@RENTALQUANTITY = [RENTALQUANTITY],
@RENTALCOSTADJUSTMENT = [RENTALCOSTADJUSTMENT],
@RENTALCOSTADJUSTMENTBASISCODE = [RENTALCOSTADJUSTMENTBASISCODE],
@BASEEXCHANGECOST = [BASEEXCHANGECOST],
@BASEEXCHANGECOSTBASISCODE = [BASEEXCHANGECOSTBASISCODE],
@EXCHANGEQUANTITY = [EXCHANGEQUANTITY],
@EXCHANGECOSTADJUSTMENT = [EXCHANGECOSTADJUSTMENT],
@EXCHANGECOSTADJUSTMENTBASISCODE = [EXCHANGECOSTADJUSTMENTBASISCODE],
@BASECURRENCYID = [BASECURRENCYID],
@ORGANIZATIONBASERENTALCOST = [ORGANIZATIONBASERENTALCOST],
@ORGANIZATIONBASEEXCHANGECOST = [ORGANIZATIONBASEEXCHANGECOST],
@ORGANIZATIONRENTALCOSTADJUSTMENT = [ORGANIZATIONRENTALCOSTADJUSTMENT],
@ORGANIZATIONEXCHANGECOSTADJUSTMENT = [ORGANIZATIONEXCHANGECOSTADJUSTMENT],
@ORGANIZATIONCURRENCYEXCHANGERATEID = [ORGANIZATIONCURRENCYEXCHANGERATEID]
from dbo.[MKTSEGMENTATIONSEGMENTLIST]
where [ID] = @SOURCESEGMENTID;
insert into dbo.[MKTSEGMENTATIONSEGMENTLIST] (
[ID],
[OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
[OVERRIDELISTCOSTS],
[BASERENTALCOST],
[BASERENTALCOSTBASISCODE],
[BASEEXCHANGECOST],
[BASEEXCHANGECOSTBASISCODE],
[RENTALQUANTITY],
[RENTALCOSTADJUSTMENT],
[RENTALCOSTADJUSTMENTBASISCODE],
[EXCHANGEQUANTITY],
[EXCHANGECOSTADJUSTMENT],
[EXCHANGECOSTADJUSTMENTBASISCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[BASECURRENCYID],
[ORGANIZATIONBASERENTALCOST],
[ORGANIZATIONBASEEXCHANGECOST],
[ORGANIZATIONRENTALCOSTADJUSTMENT],
[ORGANIZATIONEXCHANGECOSTADJUSTMENT],
[ORGANIZATIONCURRENCYEXCHANGERATEID]
)
values
(
@TARGETSEGMENTID,
@OVERRIDEQUANTITIESANDORSEGMENTCOSTS,
@OVERRIDELISTCOSTS,
@BASERENTALCOST,
@BASERENTALCOSTBASISCODE,
@BASEEXCHANGECOST,
@BASEEXCHANGECOSTBASISCODE,
@RENTALQUANTITY,
@RENTALCOSTADJUSTMENT,
@RENTALCOSTADJUSTMENTBASISCODE,
@EXCHANGEQUANTITY,
@EXCHANGECOSTADJUSTMENT,
@EXCHANGECOSTADJUSTMENTBASISCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@BASECURRENCYID,
@ORGANIZATIONBASERENTALCOST,
@ORGANIZATIONBASEEXCHANGECOST,
@ORGANIZATIONRENTALCOSTADJUSTMENT,
@ORGANIZATIONEXCHANGECOSTADJUSTMENT,
@ORGANIZATIONCURRENCYEXCHANGERATEID
);
end;
end;
if @EXCLUDE = 0
begin
/* Copy the segmentation segment user defined source codes in the source code part table */
insert into dbo.[MKTSOURCECODEPART] (
[SEGMENTATIONID],
[SEGMENTATIONSEGMENTID],
[MARKETINGPLANITEMID],
[SOURCECODEITEMID],
[LISTID],
[CHANNELCODE],
[CODE],
[PARTDEFINITIONVALUESID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@TARGETSEGMENTATIONID,
@TARGETSEGMENTID,
[MARKETINGPLANITEMID],
[SOURCECODEITEMID],
[LISTID],
[CHANNELCODE],
[CODE],
[PARTDEFINITIONVALUESID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONSEGMENTID] = @SOURCESEGMENTID;
if @TARGETALLOWEFFORTBUSINESSUNITSOVERRIDE = 1
/* Copy the business units from the segment */
insert into dbo.[MKTSEGMENTATIONSEGMENTBUSINESSUNIT] (
[ID],
[MKTSEGMENTATIONSEGMENTID],
[BUSINESSUNITCODEID],
[PERCENTVALUE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@TARGETSEGMENTID,
[BUSINESSUNITCODEID],
[PERCENTVALUE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONSEGMENTBUSINESSUNIT]
where [MKTSEGMENTATIONSEGMENTID] = @SOURCESEGMENTID;
--Copy the test segments via cursor...
if @MAILINGTYPECODE <> 4
begin
declare TESTSEGMENTCURSOR cursor local fast_forward for
select
[MKTSEGMENTATIONTESTSEGMENT].[ID],
[MKTSEGMENTATIONTESTSEGMENT].[NAME],
[MKTSEGMENTATIONTESTSEGMENT].[DESCRIPTION],
isnull([MKTSEGMENTATIONTESTSEGMENT].[CODE], ''),
[MKTSEGMENTATIONTESTSEGMENT].[PARTDEFINITIONVALUESID],
[MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID],
isnull([MKTPACKAGE].[CODE], ''),
[MKTPACKAGE].[PARTDEFINITIONVALUESID],
isnull([MKTPACKAGE].[CHANNELSOURCECODE], ''),
[MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID],
[MKTSEGMENTATIONTESTSEGMENT].[RESPONSERATE],
[MKTSEGMENTATIONTESTSEGMENT].[GIFTAMOUNT],
[MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZE],
[MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZETYPECODE],
[MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZEMETHODCODE],
[MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID],
[MKTSEGMENTATIONTESTSEGMENT].[TESTSEGMENTCODE],
[MKTSEGMENTATIONTESTSEGMENT].[TESTPARTDEFINITIONVALUESID],
[MKTSEGMENTATIONTESTSEGMENT].[PREFIXCODE],
[MKTSEGMENTATIONTESTSEGMENT].[FRACTION],
(case when @TARGETALLOWEFFORTBUSINESSUNITSOVERRIDE = 0 then 0 else [MKTSEGMENTATIONTESTSEGMENT].[OVERRIDEBUSINESSUNITS] end)
from
dbo.[MKTSEGMENTATIONTESTSEGMENT]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
where
[SEGMENTID] = @SOURCESEGMENTID
and [SEGMENTID] not in (select [ID] from @DUPLICATESEGMENTATIONSEGMENTID)
order by
[MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE] asc;
open TESTSEGMENTCURSOR;
fetch next from TESTSEGMENTCURSOR into @SOURCETESTSEGMENTID, @NAME, @DESCRIPTION, @CODE, @CODEVALUEID, @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @RESPONSERATE, @GIFTAMOUNT, @SAMPLESIZE, @SAMPLESIZETYPECODE, @SAMPLESIZEMETHODCODE, @ASKLADDERID, @TESTSEGMENTCODE, @TESTSEGMENTCODEVALUEID, @PREFIXCODE, @FRACTION, @OVERRIDEBUSINESSUNITS;
while (@@FETCH_STATUS = 0)
begin
set @TARGETTESTSEGMENTID = null;
--Create the test segment...
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONTESTSEGMENT]
@TARGETTESTSEGMENTID output,
@TARGETSEGMENTID,
@CHANGEAGENTID,
@NAME,
@DESCRIPTION,
@CODE,
@TESTSEGMENTCODE,
@PACKAGEID,
@PACKAGECODE,
@RESPONSERATE,
@GIFTAMOUNT,
@SAMPLESIZE,
@SAMPLESIZETYPECODE,
@SAMPLESIZEMETHODCODE,
@ASKLADDERID,
@PREFIXCODE,
null,
null,
0,
@CODEVALUEID,
@TESTSEGMENTCODEVALUEID,
@PACKAGECODEVALUEID,
null,
@CHANNELSOURCECODE,
@CHANNELSOURCECODEVALUEID,
@FRACTION,
null,
@OVERRIDEBUSINESSUNITS,
@CURRENTAPPUSERID;
/* Get the test segment ID because the output parameter from the test segment add function does not return a value */
select
@TARGETTESTSEGMENTID = [ID]
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
where
[SEGMENTID] = @TARGETSEGMENTID and
[NAME] = @NAME and
[PREFIXCODE] = @PREFIXCODE;
/* Copy the segmentation test segment user defined source codes in the source code part table */
insert into dbo.[MKTSOURCECODEPART] (
[SEGMENTATIONID],
[SEGMENTATIONTESTSEGMENTID],
[MARKETINGPLANITEMID],
[SOURCECODEITEMID],
[LISTID],
[CHANNELCODE],
[CODE],
[PARTDEFINITIONVALUESID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@TARGETSEGMENTATIONID,
@TARGETTESTSEGMENTID,
[MARKETINGPLANITEMID],
[SOURCECODEITEMID],
[LISTID],
[CHANNELCODE],
[CODE],
[PARTDEFINITIONVALUESID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONTESTSEGMENTID] = @SOURCETESTSEGMENTID;
if @TARGETALLOWEFFORTBUSINESSUNITSOVERRIDE = 1
/* Copy the business units for the segmentation test segment */
insert into dbo.[MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT] (
[ID],
[MKTSEGMENTATIONTESTSEGMENTID],
[BUSINESSUNITCODEID],
[PERCENTVALUE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@TARGETTESTSEGMENTID,
[BUSINESSUNITCODEID],
[PERCENTVALUE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT]
where [MKTSEGMENTATIONTESTSEGMENTID] = @SOURCETESTSEGMENTID;
fetch next from TESTSEGMENTCURSOR into @SOURCETESTSEGMENTID, @NAME, @DESCRIPTION, @CODE, @CODEVALUEID, @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @RESPONSERATE, @GIFTAMOUNT, @SAMPLESIZE, @SAMPLESIZETYPECODE, @SAMPLESIZEMETHODCODE, @ASKLADDERID, @TESTSEGMENTCODE, @TESTSEGMENTCODEVALUEID, @PREFIXCODE, @FRACTION, @OVERRIDEBUSINESSUNITS;
end
close TESTSEGMENTCURSOR;
deallocate TESTSEGMENTCURSOR;
end
end -- if @EXCLUDE = 0
fetch next from SEGMENTCURSOR into @SOURCESEGMENTID, @SEGMENTID, @EXCLUDE, @SEGMENTTYPECODE, @CODE, @CODEVALUEID, @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @RESPONSERATE, @GIFTAMOUNT, @SAMPLESIZE, @SAMPLESIZETYPECODE, @SAMPLESIZEMETHODCODE, @SAMPLESIZEEXCLUDEREMAINDER, @ASKLADDERID, @USAGECODE, @TESTSEGMENTCODE, @TESTSEGMENTCODEVALUEID, @OVERRIDEADDRESSPROCESSING, @SEGMENTUSEADDRESSPROCESSING, @SEGMENTADDRESSPROCESSINGOPTIONID, @SEGMENTNAMEFORMATPARAMETERID, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @EXPOSURESTARTDATE, @EXPOSUREENDDATE, @OVERRIDEBUSINESSUNITS;
end;
close SEGMENTCURSOR;
deallocate SEGMENTCURSOR;
end