USP_MKTSEGMENTATION_COPY_HELPER
Copies a marketing effort.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @COPYTESTMAILINGS | bit | IN | |
| @TARGETSEGMENTATIONID | uniqueidentifier | IN | |
| @COPYFORMAT | nvarchar(100) | IN | |
| @EFFORTNAMESCHEMETEXT | xml | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATION_COPY_HELPER]
(
@ID uniqueidentifier output,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@COPYTESTMAILINGS bit = 1,
@TARGETSEGMENTATIONID uniqueidentifier = null, /* For public media efforts only */
@COPYFORMAT nvarchar(100) = null,
@EFFORTNAMESCHEMETEXT xml = null /* For efforts with name patterns */
)
as
set nocount on;
declare @SOURCESEGMENTATIONID uniqueidentifier;
declare @CURRENTDATE datetime;
declare @EFFORTCOMMUNICATIONTEMPLATEID uniqueidentifier;
declare @TEMPLATENAME nvarchar(100);
declare @COMMUNICATIONNAMESCHEMEID uniqueidentifier;
declare @COMMUNICATIONNAMESCHEMENAME nvarchar(100);
declare @COUNTERVALUE nvarchar(10);
declare @COUNTER int;
begin try
set @SOURCESEGMENTATIONID = @ID;
if @CHANGEAGENTID is null
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
/**************************/
/* Create the new mailing */
/**************************/
declare @RC int;
declare @MARKETINGPLANITEMID uniqueidentifier;
declare @DATALOADED bit;
declare @ISHISTORICAL bit = 0;
declare @ACTIVE bit;
declare @CODE nvarchar(10);
declare @CODEVALUEID uniqueidentifier;
declare @NAME nvarchar(100);
declare @DESCRIPTION nvarchar(255);
declare @SITEID uniqueidentifier;
declare @SOURCECODEID uniqueidentifier;
declare @ISTESTMAILING tinyint;
declare @MAILDATE datetime;
declare @PLANPATH nvarchar(max);
declare @HOUSEHOLDINGTYPECODE tinyint;
declare @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit;
declare @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit;
declare @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit;
declare @ENABLEHOUSEHOLDING bit;
declare @SITEREQUIRED bit;
declare @SITECANBECHANGED bit;
declare @MAILINGTYPECODE tinyint;
declare @EXCLUSIONDATETYPECODE tinyint;
declare @EXCLUSIONASOFDATE datetime;
declare @EXCLUDEDECEASED bit;
declare @EXCLUDEINACTIVE bit;
declare @EXCLUSIONS xml;
declare @USEADDRESSPROCESSING bit;
declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier;
declare @NAMEFORMATPARAMETERID uniqueidentifier;
declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
declare @APPEALINFORMATION xml;
declare @RUNACTIVATEANDEXPORT bit;
declare @EXPORTDESCRIPTION nvarchar(255);
declare @MAILEXPORTDEFINITIONID uniqueidentifier;
declare @EMAILEXPORTDEFINITIONID uniqueidentifier;
declare @PHONEEXPORTDEFINITIONID uniqueidentifier;
declare @SEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
declare @OVERRIDEBUSINESSUNITS bit;
declare @BUSINESSUNITS xml;
declare @SOURCECODEITEMID uniqueidentifier;
declare @LISTID uniqueidentifier;
declare @CHANNELCODE tinyint;
declare @PARTDEFINITIONVALUESID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONBASERENTALCOST money;
declare @ORGANIZATIONBASEEXCHANGECOST money;
declare @ORGANIZATIONRENTALCOSTADJUSTMENT money;
declare @ORGANIZATIONEXCHANGECOSTADJUSTMENT money;
declare @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit;
declare @CACHESOURCEANALYSISRULEDATA bit;
declare @RUNMARKETINGEXCLUSIONSREPORT bit;
declare @EFFORTCHANNELCODE tinyint;
declare @ALLOWRESERVINGFINDERNUMBERS bit;
declare @ALLOWSPECIFYBUDGET bit;
declare @ALLOWEXCLUDEPREVIOUSEFFORTS bit;
declare @DUEDATE datetime;
select
@MAILINGTYPECODE = [MAILINGTYPECODE]
from dbo.[MKTSEGMENTATION]
where [ID] = @SOURCESEGMENTATIONID;
if @MAILINGTYPECODE = 4 --Public media effort
begin
if @TARGETSEGMENTATIONID is null
raiserror('Public media efforts cannot be copied directly using this stored procedure. Please use the "Marketing Effort: Copy" record operation to copy a public media effort.', 13, 1);
else
begin
--Make sure the target ID already exists...
if not exists(select * from dbo.[MKTSEGMENTATION] where [ID] = @TARGETSEGMENTATIONID)
raiserror('The target public media effort ID does not exist in the database. Please use the "Marketing Effort: Copy" record operation to copy a public media effort.', 13, 1);
end
end
else
set @TARGETSEGMENTATIONID = newid();
select
@ORGANIZATIONCURRENCYEXCHANGERATEID = [ORGANIZATIONCURRENCYEXCHANGERATEID]
from dbo.[MKTSEGMENTATIONBUDGET]
where [ID] = @SOURCESEGMENTATIONID;
if @MAILINGTYPECODE <> 4
exec @RC = dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATION_4]
@ID = @SOURCESEGMENTATIONID,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@DATALOADED = @DATALOADED output,
@ACTIVE = @ACTIVE output,
@CODE = @CODE output,
@NAME = @NAME output,
@DESCRIPTION = @DESCRIPTION output,
@SITEID = @SITEID output,
@SOURCECODEID = @SOURCECODEID output,
@ITEMLIST = null,
@ISTESTMAILING = @ISTESTMAILING output,
@MAILDATE = @MAILDATE output,
@PLANPATH = @PLANPATH output,
@HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE output,
@ENABLEHOUSEHOLDING = @ENABLEHOUSEHOLDING output,
@SITEREQUIRED = @SITEREQUIRED output,
@SITECANBECHANGED = @SITECANBECHANGED output,
@MAILINGTYPECODE = @MAILINGTYPECODE output,
@ISBBEC = null,
@OWNERID = null,
@EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE output,
@EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE output,
@EXCLUDEDECEASED = @EXCLUDEDECEASED output,
@EXCLUDEINACTIVE = @EXCLUDEINACTIVE output,
@EXCLUSIONS = @EXCLUSIONS output,
@USEADDRESSPROCESSING = @USEADDRESSPROCESSING output,
@ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID output,
@NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID output,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE output,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE output,
@TSLONG = null,
@ACTIVATIONKPIS = null,
@USEKPISASDEFAULT = null,
@APPEALINFORMATION = @APPEALINFORMATION output,
@RUNACTIVATEANDEXPORT = @RUNACTIVATEANDEXPORT output,
@EXPORTDESCRIPTION = @EXPORTDESCRIPTION output,
@MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID output,
@EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID output,
@PHONEEXPORTDEFINITIONID = @PHONEEXPORTDEFINITIONID output,
@CODEVALUEID = @CODEVALUEID output,
@RUNSEGMENTATIONSEGMENTREFRESHPROCESS = @RUNSEGMENTATIONSEGMENTREFRESHPROCESS output,
@CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA output,
@BUSINESSUNITS = null,
@OVERRIDEBUSINESSUNITS = @OVERRIDEBUSINESSUNITS output,
@BASECURRENCYID = @BASECURRENCYID output,
@ISHISTORICAL = @ISHISTORICAL output,
@RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT output,
@CHANNELCODE = @EFFORTCHANNELCODE output,
@ALLOWRESERVINGFINDERNUMBERS = @ALLOWRESERVINGFINDERNUMBERS output,
@ALLOWSPECIFYBUDGET = @ALLOWSPECIFYBUDGET output,
@ALLOWEXCLUDEPREVIOUSEFFORTS = @ALLOWEXCLUDEPREVIOUSEFFORTS output,
@DUEDATE = @DUEDATE output;
else
exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONPASSIVE]
@ID = @SOURCESEGMENTATIONID,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@DATALOADED = @DATALOADED output,
@MAILINGTYPECODE = @MAILINGTYPECODE output,
@ACTIVE = @ACTIVE output,
@NAME = @NAME output,
@DESCRIPTION = @DESCRIPTION output,
@SITEID = @SITEID output,
@SITEREQUIRED = @SITEREQUIRED output,
@SITECANBECHANGED = @SITECANBECHANGED output,
@MAILDATE = @MAILDATE output,
@SOURCECODEID = @SOURCECODEID output,
@CODEVALUEID = @CODEVALUEID output,
@CODE = @CODE output,
@ITEMLIST = null,
@ISTESTMAILING = @ISTESTMAILING output,
@ACTIVATIONKPIS = null,
@USEKPISASDEFAULT = null,
@TSLONG = null,
@BASECURRENCYID = @BASECURRENCYID output;
if @ISHISTORICAL = 1
begin
raiserror('BBERR_MKTSEGMENTATION_ISHISTORICAL', 13, 1);
return 1;
end
--Skip all this for public media efforts because the effort will already be created by the CLR code before this...
if @MAILINGTYPECODE <> 4
begin
/******************************************/
/* BEGIN: Non-Public media effort section */
/******************************************/
declare @SOURCENAME nvarchar(100);
select
@SOURCENAME = [MKTSEGMENTATION].[NAME],
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
@HOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
@EFFORTCOMMUNICATIONTEMPLATEID = [MKTCOMMUNICATIONTEMPLATE].[ID],
@TEMPLATENAME =[MKTCOMMUNICATIONTEMPLATE].[NAME]
from dbo.[MKTSEGMENTATION]
left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID]
where [MKTSEGMENTATION].[ID] = @SOURCESEGMENTATIONID;
declare @UNIQUENAME nvarchar(100);
if @EFFORTCOMMUNICATIONTEMPLATEID is not null
begin
--get the name pattern Id if the template has one
select
@COMMUNICATIONNAMESCHEMEID = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONNAMESCHEMEID],
@COMMUNICATIONNAMESCHEMENAME = [MKTCOMMUNICATIONNAMESCHEME].[NAME]
from
dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT]
inner join dbo.[MKTCOMMUNICATIONNAMESCHEME] on [MKTCOMMUNICATIONNAMESCHEME].[ID] = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONNAMESCHEMEID]
where
[MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = @EFFORTCOMMUNICATIONTEMPLATEID;
--set the current name from the name pattern text
if @COMMUNICATIONNAMESCHEMEID is not null and @EFFORTNAMESCHEMETEXT is not null
set @SOURCENAME = dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETNAMEFROMNAMESCHEMETEXT](@EFFORTNAMESCHEMETEXT, NULL);
--build a unique name
exec dbo.[USP_MKTCOMMUNICATIONEFFORT_GETUNIQUENAME]
@COMMUNICATIONNAMESCHEMEID = @COMMUNICATIONNAMESCHEMEID,
@EFFORTNAMESCHEMETEXT = @EFFORTNAMESCHEMETEXT,
@EFFORTNAME = @SOURCENAME output,
@COUNTERVALUE = @COUNTERVALUE output,
@COUNTER = @COUNTER output,
@ISCOPY = 1,
@NAMEFORMAT = @COPYFORMAT;
set @UNIQUENAME = @SOURCENAME;
end
else
begin
if @COPYFORMAT is null
set @UNIQUENAME = dbo.[UFN_MKTSEGMENTATION_GETUNIQUENAME](@TARGETSEGMENTATIONID, @NAME, null);
else
begin
-- This stored procedure will set the unique name
set @UNIQUENAME = @SOURCENAME;
exec dbo.[USP_MKTCOMMON_GETUNIQUENAME] @UNIQUENAME output, @COPYFORMAT, 'MKTSEGMENTATION', 'NAME', 0;
end
end
-- return only the selected solicit code exclusions
-- (required are always used by default and thus aren't even saved to the database)
select @SEGMENTATIONACTIVATEPROCESSID = [ID] from dbo.[MKTSEGMENTATIONACTIVATEPROCESS] where [SEGMENTATIONID] = @SOURCESEGMENTATIONID;
set @EXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETSELECTEDSOLICITCODEEXCLUSIONS_TOITEMLISTXML](@SEGMENTATIONACTIVATEPROCESSID);
-- null out existing IDs from solicit code exclusions
if @EXCLUSIONS is not null
set @EXCLUSIONS.modify('delete /EXCLUSIONS/ITEM/ID');
/*Auto-Increment effort source code if auto-increment is enabled */
if dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUE_GETAUTOINCREMENTSETTING](@CODEVALUEID) = 1
set @CODE = dbo.[UFN_MKTSOURCECODE_AUTOINCREMENTCODE](@CODEVALUEID, @MAILINGTYPECODE);
/*Create the copy of the mailing*/
exec @RC = dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATION]
@ID = @TARGETSEGMENTATIONID output,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CODE = @CODE,
@NAME = @UNIQUENAME,
@DESCRIPTION = @DESCRIPTION,
@INCLUDESELECTIONS = null,
@EXCLUDESELECTIONS = null,
@EXCLUDESEGMENTATIONS = null,
@MARKETINGPLANITEMID = @MARKETINGPLANITEMID,
@SOURCECODEID = @SOURCECODEID,
@ITEMLIST = null,
@MAILDATE = @MAILDATE,
@HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
@SITEID = @SITEID,
@MAILINGTYPECODE = @MAILINGTYPECODE,
@EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
@EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
@EXCLUDEDECEASED = @EXCLUDEDECEASED,
@EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
@EXCLUSIONS = @EXCLUSIONS,
@USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
@ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
@NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
@ACTIVATIONKPIS = null,
@USEKPISASDEFAULT = 0,
@APPEALINFORMATION = null,
@RUNACTIVATEANDEXPORT = @RUNACTIVATEANDEXPORT,
@EXPORTDESCRIPTION = @EXPORTDESCRIPTION,
@MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID,
@EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID,
@PHONEEXPORTDEFINITIONID = @PHONEEXPORTDEFINITIONID,
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
@HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
@CODEVALUEID = @CODEVALUEID,
@RUNSEGMENTATIONSEGMENTREFRESHPROCESS = @RUNSEGMENTATIONSEGMENTREFRESHPROCESS,
@CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA,
@BUSINESSUNITS = null,
@OVERRIDEBUSINESSUNITS = @OVERRIDEBUSINESSUNITS,
@BASECURRENCYID = @BASECURRENCYID,
@ORGANIZATIONCURRENCYEXCHANGERATEID = @ORGANIZATIONCURRENCYEXCHANGERATEID,
@RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT,
@CHANNELCODE = @EFFORTCHANNELCODE,
@ALLOWRESERVINGFINDERNUMBERS = @ALLOWRESERVINGFINDERNUMBERS,
@ALLOWSPECIFYBUDGET = @ALLOWSPECIFYBUDGET,
@ALLOWEXCLUDEPREVIOUSEFFORTS = @ALLOWEXCLUDEPREVIOUSEFFORTS,
@DUEDATE = @DUEDATE;
--Copy the template and name pattern if there is one
if @EFFORTCOMMUNICATIONTEMPLATEID is not null
begin
exec dbo.[USP_MKTCOMMUNICATIONTEMPLATE_COPY]
@EFFORTCOMMUNICATIONTEMPLATEID output,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
@TEMPLATENAME,
@TARGETSEGMENTATIONID;
--copy name pattern
if @COMMUNICATIONNAMESCHEMEID is not null
begin
exec dbo.[USP_MKTCOMMUNICATIONNAMESCHEME_COPY] @COMMUNICATIONNAMESCHEMEID output, @CURRENTAPPUSERID, @CHANGEAGENTID, @COMMUNICATIONNAMESCHEMENAME, @TARGETSEGMENTATIONID;
--update part values
update
dbo.[MKTCOMMUNICATIONNAMESCHEMEPART]
set
[MKTCOMMUNICATIONNAMESCHEMEPART].[COMMUNICATIONEFFORTVALUE] = case when [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] = 8 then @COUNTERVALUE else [NAMESCHEMEPARTS].[FREEFORMPART] end
from
dbo.[MKTCOMMUNICATIONNAMESCHEMEPART]
inner join
(select
T.c.value('(@FREEFORMPART)[1]', 'nvarchar(100)') as [FREEFORMPART],
T.c.value('(@SEQUENCE)[1]', 'int') as [SEQUENCE],
@COMMUNICATIONNAMESCHEMEID [ID],
T.c.value('(@NAMEPARTTYPECODE)[1]', 'tinyint') as [NAMEPARTTYPECODE]
from @EFFORTNAMESCHEMETEXT.nodes('/EFFORTNAMESCHEMETEXT/ITEM') T(c)
where T.c.value('(@SEQUENCE)[1]', 'int') > 0
) [NAMESCHEMEPARTS] on [NAMESCHEMEPARTS].[ID] = [MKTCOMMUNICATIONNAMESCHEMEPART].[MKTCOMMUNICATIONNAMESCHEMEID]
where [MKTCOMMUNICATIONNAMESCHEMEPART].[SEQUENCE] = [NAMESCHEMEPARTS].[SEQUENCE] and [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] in (8, 10);
end
end
/***************************************/
/* Update the Test Mailing information */
/***************************************/
declare @PARENTSEGMENTATIONID uniqueidentifier;
declare @TESTSAMPLESIZE int;
declare @TESTSAMPLESIZETYPECODE tinyint;
select
@PARENTSEGMENTATIONID = [PARENTSEGMENTATIONID],
@TESTSAMPLESIZE = [SAMPLESIZE],
@TESTSAMPLESIZETYPECODE = [SAMPLESIZETYPECODE]
from dbo.[MKTSEGMENTATION]
where [ID] = @SOURCESEGMENTATIONID;
update dbo.[MKTSEGMENTATION] set
[PARENTSEGMENTATIONID] = @PARENTSEGMENTATIONID,
[SAMPLESIZE] = @TESTSAMPLESIZE,
[SAMPLESIZETYPECODE] = @TESTSAMPLESIZETYPECODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [ID] = @TARGETSEGMENTATIONID;
/*************************************/
/* Copy the previous mailing filters */
/*************************************/
insert into dbo.[MKTSEGMENTATIONFILTERSEGMENTATION]
(
[SEGMENTATIONID],
[PREVIOUSSEGMENTATIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@TARGETSEGMENTATIONID,
[PREVIOUSSEGMENTATIONID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION]
where [SEGMENTATIONID] = @SOURCESEGMENTATIONID;
/**************************************************/
/* Copy the universe/exclusion selection filters */
/**************************************************/
insert into dbo.[MKTSEGMENTATIONFILTERSELECTION] (
[SEGMENTATIONID],
[FILTERTYPECODE],
[SELECTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@TARGETSEGMENTATIONID,
[FILTERTYPECODE],
[SELECTIONID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONFILTERSELECTION]
where [SEGMENTATIONID] = @SOURCESEGMENTATIONID;
/******************/
/* Copy the seeds */
/******************/
insert into dbo.[MKTSEGMENTATIONSEED]
(
[SEGMENTATIONID],
[SEEDID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@TARGETSEGMENTATIONID,
[SEEDID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONSEED]
where [SEGMENTATIONID] = @SOURCESEGMENTATIONID;
/*************************************/
/* Copy the activation appeal fields */
/*************************************/
insert into dbo.[MKTSEGMENTATIONACTIVATE] (
[ID],
[SEGMENTATIONID],
[RECORDSOURCEID],
[APPEALSYSTEMID],
[APPEALID],
[APPEALDESCRIPTION],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@TARGETSEGMENTATIONID,
[RECORDSOURCEID],
[APPEALSYSTEMID],
[APPEALID],
[APPEALDESCRIPTION],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONACTIVATE]
where [SEGMENTATIONID] = @SOURCESEGMENTATIONID;
/****************************/
/* Copy the activation KPIs */
/****************************/
insert into dbo.[MKTSEGMENTATIONACTIVATEKPI] (
[ID],
[SEGMENTATIONID],
[KPICATALOGID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@TARGETSEGMENTATIONID,
[KPICATALOGID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONACTIVATEKPI]
where [SEGMENTATIONID] = @SOURCESEGMENTATIONID;
/*********************************/
/* Copy any ask ladder overrides */
/*********************************/
insert into dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE] (
[ID],
[SEGMENTATIONID],
[ASKLADDERID],
[IDSETREGISTERID],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@TARGETSEGMENTATIONID,
[ASKLADDERID],
[IDSETREGISTERID],
[SEQUENCE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE]
where [SEGMENTATIONID] = @SOURCESEGMENTATIONID;
/**********************************************************/
/* copy the MKTSEGMENTATIONSEGMENTCALCULATEPROCESS values */
/**********************************************************/
declare @RUNSEGMENTATIONSEGMENTREFRESHPROCESSCOUNTS bit = 0;
declare @CACHESOURCEANALYSISRULEDATACOUNTS bit = 0;
declare @RUNMARKETINGEXCLUSIONSREPORTCOUNTS bit = 0;
select
@RUNSEGMENTATIONSEGMENTREFRESHPROCESSCOUNTS = [RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
@CACHESOURCEANALYSISRULEDATACOUNTS = [CACHESOURCEANALYSISRULEDATA],
@RUNMARKETINGEXCLUSIONSREPORTCOUNTS = [RUNMARKETINGEXCLUSIONSREPORT]
from
dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
where
[SEGMENTATIONID] = @SOURCESEGMENTATIONID;
update
dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
set
[RUNSEGMENTATIONSEGMENTREFRESHPROCESS] = @RUNSEGMENTATIONSEGMENTREFRESHPROCESSCOUNTS,
[CACHESOURCEANALYSISRULEDATA] = @CACHESOURCEANALYSISRULEDATACOUNTS,
[RUNMARKETINGEXCLUSIONSREPORT] = @RUNMARKETINGEXCLUSIONSREPORTCOUNTS,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where
[SEGMENTATIONID] = @TARGETSEGMENTATIONID;
/**********************************************************/
/* Copy the export values */
/**********************************************************/
declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = null;
declare @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE tinyint = null;
declare @CURRENCYDECIMALDIGITS int = null;
declare @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE tinyint = null;
declare @CURRENCYGROUPSEPARATOR nvarchar(4) = null;
declare @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE tinyint = null;
declare @CURRENCYDECIMALSEPARATOR nvarchar(4) = null;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = null;
declare @DATEFORMAT nvarchar(50) = null;
declare @FUZZYDATEFORMAT nvarchar(50) = null;
declare @MONTHDAYFORMAT nvarchar(50) = null;
declare @HOURMINUTEFORMAT nvarchar(50) = null;
declare @CSVLINEBREAKCODE tinyint = null;
/* Select values from original template */
select
@CURRENCYSYMBOLDISPLAYSETTINGCODE = [CURRENCYSYMBOLDISPLAYSETTINGCODE],
@CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE = [CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE],
@CURRENCYDECIMALDIGITS = [CURRENCYDECIMALDIGITS],
@CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE = [CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE],
@CURRENCYGROUPSEPARATOR = [CURRENCYGROUPSEPARATOR],
@CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE = [CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE],
@CURRENCYDECIMALSEPARATOR = [CURRENCYDECIMALSEPARATOR],
@DATEFORMAT = [DATEFORMAT],
@FUZZYDATEFORMAT = [FUZZYDATEFORMAT],
@MONTHDAYFORMAT = [MONTHDAYFORMAT],
@HOURMINUTEFORMAT = [HOURMINUTEFORMAT],
@CSVLINEBREAKCODE = [CSVLINEBREAKCODE]
from dbo.[BUSINESSPROCESSEXPORTFORMAT]
inner join [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT] on [BUSINESSPROCESSEXPORTFORMAT].[ID] = [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT].[ID]
inner join [MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT].[PARAMETERSETID] = [MKTSEGMENTATIONEXPORTPROCESS].[ID]
where
[MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = @SOURCESEGMENTATIONID;
/* Insert values into new template */
update
dbo.[BUSINESSPROCESSEXPORTFORMAT]
set
[CURRENCYSYMBOLDISPLAYSETTINGCODE] = @CURRENCYSYMBOLDISPLAYSETTINGCODE,
[CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE] = @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE,
[CURRENCYDECIMALDIGITS] = @CURRENCYDECIMALDIGITS,
[CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE] = @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE,
[CURRENCYGROUPSEPARATOR] = @CURRENCYGROUPSEPARATOR,
[CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE] = @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE,
[CURRENCYDECIMALSEPARATOR] = @CURRENCYDECIMALSEPARATOR,
[DATEFORMAT] = @DATEFORMAT,
[FUZZYDATEFORMAT] = @FUZZYDATEFORMAT,
[MONTHDAYFORMAT] = @MONTHDAYFORMAT,
[HOURMINUTEFORMAT] = @HOURMINUTEFORMAT,
[CSVLINEBREAKCODE] = @CSVLINEBREAKCODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
from dbo.[BUSINESSPROCESSEXPORTFORMAT]
inner join [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT] on [BUSINESSPROCESSEXPORTFORMAT].[ID] = [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT].[ID]
inner join [MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT].[PARAMETERSETID] = [MKTSEGMENTATIONEXPORTPROCESS].[ID]
where [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = @TARGETSEGMENTATIONID;
/*****************************************/
/* END: Non-Public media effort section */
/*****************************************/
end
/**********************************************************************************************/
/* update RUNSEGMENTATIONSEGMENTCALCULATEPROCESS as it defaults to 1 when the effort is added */
/**********************************************************************************************/
declare @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS bit;
select
@RUNSEGMENTATIONSEGMENTCALCULATEPROCESS = [RUNSEGMENTATIONSEGMENTCALCULATEPROCESS]
from
dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
where
[SEGMENTATIONID] = @SOURCESEGMENTATIONID;
update
dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
set
[RUNSEGMENTATIONSEGMENTCALCULATEPROCESS] = @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where
[SEGMENTATIONID] = @TARGETSEGMENTATIONID;
/*********************************/
/* Update the budget information */
/*********************************/
declare @BUDGETAMOUNT money;
declare @FIXEDCOST money;
declare @ORGANIZATIONBUDGETAMOUNT money;
declare @ORGANIZATIONFIXEDCOST money;
select
@BUDGETAMOUNT = [BUDGETAMOUNT],
@FIXEDCOST = [FIXEDCOST],
@ORGANIZATIONBUDGETAMOUNT = [ORGANIZATIONBUDGETAMOUNT],
@ORGANIZATIONFIXEDCOST = [ORGANIZATIONFIXEDCOST],
@ORGANIZATIONCURRENCYEXCHANGERATEID = [ORGANIZATIONCURRENCYEXCHANGERATEID]
from dbo.[MKTSEGMENTATIONBUDGET]
where [ID] = @SOURCESEGMENTATIONID;
update dbo.[MKTSEGMENTATIONBUDGET] set
[BUDGETAMOUNT] = @BUDGETAMOUNT,
[FIXEDCOST] = @FIXEDCOST,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE,
[ORGANIZATIONBUDGETAMOUNT] = @ORGANIZATIONBUDGETAMOUNT,
[ORGANIZATIONFIXEDCOST] = @ORGANIZATIONFIXEDCOST,
[ORGANIZATIONCURRENCYEXCHANGERATEID] = @ORGANIZATIONCURRENCYEXCHANGERATEID
where [ID] = @TARGETSEGMENTATIONID;
/**********************************************************/
/* Copy the mailing-level user defined source code parts. */
/**********************************************************/
declare SOURCECODEPARTCURSOR cursor local fast_forward for
select
[MARKETINGPLANITEMID],
[SOURCECODEITEMID],
[LISTID],
[CHANNELCODE],
[CODE],
[PARTDEFINITIONVALUESID]
from dbo.[MKTSOURCECODEPART]
where [SEGMENTATIONID] = @SOURCESEGMENTATIONID
and [SEGMENTATIONSEGMENTID] is null
and [SEGMENTATIONTESTSEGMENTID] is null;
open SOURCECODEPARTCURSOR;
fetch next from SOURCECODEPARTCURSOR into @MARKETINGPLANITEMID, @SOURCECODEITEMID, @LISTID, @CHANNELCODE, @CODE, @PARTDEFINITIONVALUESID;
while (@@FETCH_STATUS = 0)
begin
if dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUE_GETAUTOINCREMENTSETTING](@PARTDEFINITIONVALUESID) = 1
set @CODE = dbo.[UFN_MKTSOURCECODE_AUTOINCREMENTCODE](@PARTDEFINITIONVALUESID, @MAILINGTYPECODE);
insert into dbo.[MKTSOURCECODEPART] (
[SEGMENTATIONID],
[MARKETINGPLANITEMID],
[SOURCECODEITEMID],
[LISTID],
[CHANNELCODE],
[CODE],
[PARTDEFINITIONVALUESID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
) values (
@TARGETSEGMENTATIONID,
@MARKETINGPLANITEMID,
@SOURCECODEITEMID,
@LISTID,
@CHANNELCODE,
@CODE,
@PARTDEFINITIONVALUESID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
fetch next from SOURCECODEPARTCURSOR into @MARKETINGPLANITEMID, @SOURCECODEITEMID, @LISTID, @CHANNELCODE, @CODE, @PARTDEFINITIONVALUESID;
end
close SOURCECODEPARTCURSOR;
deallocate SOURCECODEPARTCURSOR;
/*********************************/
/* Copy any business units */
/*********************************/
insert into dbo.[MKTSEGMENTATIONBUSINESSUNIT] (
[ID],
[MKTSEGMENTATIONID],
[BUSINESSUNITCODEID],
[PERCENTVALUE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@TARGETSEGMENTATIONID,
[BUSINESSUNITCODEID],
[PERCENTVALUE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSEGMENTATIONBUSINESSUNIT]
where [MKTSEGMENTATIONID] = @SOURCESEGMENTATIONID;
/********************************/
/* Copy the segments via cursor */
/********************************/
exec dbo.[USP_MKTSEGMENTATIONSEGMENT_COPYSEGMENTSFROMEFFORT] @TARGETSEGMENTATIONID, @CHANGEAGENTID, @CURRENTAPPUSERID, @SOURCESEGMENTATIONID;
/*************************************/
/* Copy any test mailings via cursor */
/*************************************/
if @MAILINGTYPECODE <> 4 and @COPYTESTMAILINGS = 1
begin
declare @TESTMAILINGID uniqueidentifier;
declare @NEWTESTMAILINGID uniqueidentifier;
declare TESTMAILINGCURSOR cursor local fast_forward for
select [MKTSEGMENTATION].[ID]
from dbo.[MKTSEGMENTATION]
where [MKTSEGMENTATION].[PARENTSEGMENTATIONID] = @SOURCESEGMENTATIONID;
open TESTMAILINGCURSOR;
fetch next from TESTMAILINGCURSOR into @TESTMAILINGID;
while (@@FETCH_STATUS = 0)
begin
--@TESTMAILINGID goes in as the SOURCE SEGMENTATION ID, and comes out as the COPY's ID
exec dbo.[USP_MKTSEGMENTATION_COPY_HELPER] @TESTMAILINGID output, @CURRENTAPPUSERID, @CHANGEAGENTID, 0, null, @COPYFORMAT;
--Update the parent id
update dbo.[MKTSEGMENTATION] set
[PARENTSEGMENTATIONID] = @TARGETSEGMENTATIONID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where [ID] = @TESTMAILINGID;
fetch next from TESTMAILINGCURSOR into @TESTMAILINGID;
end;
close TESTMAILINGCURSOR;
deallocate TESTMAILINGCURSOR;
end
--Return the new mailing ID...
set @ID = @TARGETSEGMENTATIONID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;