USP_MKTACKNOWLEDGEMENTMAILINGPROCESS_CREATEMAILING
Creates a marketing effort from a marketing acknowledgement template.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ACKNOWLEDGEMENTMAILINGTEMPLATEID | uniqueidentifier | IN | |
| @SEGMENTATIONID | uniqueidentifier | INOUT | |
| @DATETEXT | nvarchar(50) | IN | |
| @SUCCESSFULRULES | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTACKNOWLEDGEMENTMAILINGPROCESS_CREATEMAILING]
(
@ACKNOWLEDGEMENTMAILINGTEMPLATEID uniqueidentifier,
@SEGMENTATIONID uniqueidentifier output,
@DATETEXT nvarchar(50),
@SUCCESSFULRULES xml,
@CHANGEAGENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier
)
as
set nocount on;
declare @CURRENTDATE datetime;
begin try
if @CHANGEAGENTID is null
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
/**************************/
/* Create the new mailing */
/**************************/
declare @CODE nvarchar(10);
declare @CODEVALUEID uniqueidentifier;
declare @NAME nvarchar(100);
declare @DESCRIPTION nvarchar(255);
declare @SITEID uniqueidentifier;
declare @SOURCECODEID uniqueidentifier;
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 @RUNACTIVATEANDEXPORT bit;
declare @EXPORTDESCRIPTION nvarchar(255);
declare @MAILEXPORTDEFINITIONID uniqueidentifier;
declare @EMAILEXPORTDEFINITIONID uniqueidentifier;
declare @CACHESOURCEANALYSISRULEDATA bit;
declare @BASECURRENCYID uniqueidentifier;
declare @RUNMARKETINGEXCLUSIONSREPORT bit;
select
@CODE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[CODE],
@CODEVALUEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[PARTDEFINITIONVALUESID],
@NAME = dbo.[UFN_MKTACKNOWLEDGEMENTMAILINGPROCESS_GETUNIQUENAME]([MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[NAME], @DATETEXT),
@DESCRIPTION = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[DESCRIPTION],
@SITEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SITEID],
@SOURCECODEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SOURCECODEID],
@EXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
@EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
@EXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
@EXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
@EXCLUSIONS = dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_TOITEMLISTXML]([MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID]),
@USEADDRESSPROCESSING = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[USEADDRESSPROCESSING],
@ADDRESSPROCESSINGOPTIONID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONID],
@NAMEFORMATPARAMETERID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[NAMEFORMATPARAMETERID],
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
@RUNACTIVATEANDEXPORT = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[RUNACTIVATEANDEXPORT],
@EXPORTDESCRIPTION = isnull([MKTACKNOWLEDGEMENTMAILINGTEMPLATEEXPORT].[DESCRIPTION], ''),
@MAILEXPORTDEFINITIONID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATEEXPORT].[MAILEXPORTDEFINITIONID],
@EMAILEXPORTDEFINITIONID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATEEXPORT].[EMAILEXPORTDEFINITIONID],
@CACHESOURCEANALYSISRULEDATA = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[CACHESOURCEANALYSISRULEDATA],
@BASECURRENCYID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[BASECURRENCYID],
@RUNMARKETINGEXCLUSIONSREPORT = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[RUNMARKETINGEXCLUSIONSREPORT]
from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE]
inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID]
left outer join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID]
left outer join dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEEXPORT] on [MKTACKNOWLEDGEMENTMAILINGTEMPLATEEXPORT].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID]
where [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID;
if @EXCLUSIONS is not null
set @EXCLUSIONS.modify('delete /EXCLUSIONS/ITEM/ID');
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATION]
@ID = @SEGMENTATIONID output,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CODE = @CODE,
@NAME = @NAME,
@DESCRIPTION = @DESCRIPTION,
@INCLUDESELECTIONS = null,
@EXCLUDESELECTIONS = null,
@EXCLUDESEGMENTATIONS = null,
@MARKETINGPLANITEMID = null,
@SOURCECODEID = @SOURCECODEID,
@ITEMLIST = null,
@MAILDATE = @CURRENTDATE,
@HOUSEHOLDINGTYPECODE = 0,
@SITEID = @SITEID,
@MAILINGTYPECODE = 1,
@EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
@EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
@EXCLUDEDECEASED = @EXCLUDEDECEASED,
@EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
@EXCLUSIONS = @EXCLUSIONS,
@USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
@ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
@NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
@ACTIVATIONKPIS = null,
@USEKPISASDEFAULT = null,
@APPEALINFORMATION = null,
@RUNACTIVATEANDEXPORT = @RUNACTIVATEANDEXPORT,
@EXPORTDESCRIPTION = @EXPORTDESCRIPTION,
@MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID,
@EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID,
@PHONEEXPORTDEFINITIONID = null,
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = 0,
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = 0,
@HOUSEHOLDINGONERECORDPERHOUSEHOLD = 0,
@CODEVALUEID = @CODEVALUEID,
@RUNSEGMENTATIONSEGMENTREFRESHPROCESS = 0,
@CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA,
@BUSINESSUNITS = null,
@OVERRIDEBUSINESSUNITS = 0,
@BASECURRENCYID = @BASECURRENCYID,
@ORGANIZATIONCURRENCYEXCHANGERATEID = null,
@RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT;
/**************************************************/
/* Copy the universe/exclusion selection filters */
/**************************************************/
insert into dbo.[MKTSEGMENTATIONFILTERSELECTION] (
[ID],
[SEGMENTATIONID],
[FILTERTYPECODE],
[SELECTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@SEGMENTATIONID,
[FILTERTYPECODE],
[SELECTIONID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEFILTERSELECTION]
where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID;
/******************************/
/* Copy the source code parts */
/******************************/
insert into dbo.[MKTSOURCECODEPART] (
[ID],
[SEGMENTATIONID],
[SOURCECODEITEMID],
[LISTID],
[CHANNELCODE],
[CODE],
[PARTDEFINITIONVALUESID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@SEGMENTATIONID,
[SOURCECODEITEMID],
[LISTID],
[CHANNELCODE],
[CODE],
[PARTDEFINITIONVALUESID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTSOURCECODEPART]
where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID;
/******************/
/* Copy the seeds */
/******************/
insert into dbo.[MKTSEGMENTATIONSEED] (
[ID],
[SEGMENTATIONID],
[SEEDID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@SEGMENTATIONID,
[SEEDID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATESEED]
where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID;
/*********************************/
/* Copy any ask ladder overrides */
/*********************************/
insert into dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE] (
[ID],
[SEGMENTATIONID],
[ASKLADDERID],
[IDSETREGISTERID],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
@SEGMENTATIONID,
[ASKLADDERID],
[IDSETREGISTERID],
[SEQUENCE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE]
where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID;
/********************************/
/* Copy the segments via cursor */
/********************************/
declare @TARGETSEGMENTID uniqueidentifier;
declare @SEGMENTID uniqueidentifier;
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 @ASKLADDERID uniqueidentifier;
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 @EXCLUDE bit;
declare SEGMENTCURSOR cursor local fast_forward for
select
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEGMENTID],
isnull([MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[CODE], '') as [CODE],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID] as [CODEVALUEID],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PACKAGEID],
isnull([MKTPACKAGE].[CODE], '') as [PACKAGECODE],
[MKTPACKAGE].[PARTDEFINITIONVALUESID] as [PACKAGECODEVALUEID],
isnull([MKTPACKAGE].[CHANNELSOURCECODE], '') as [CHANNELSOURCECODE],
[MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID] as [CHANNELSOURCECODEVALUEID],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[RESPONSERATE],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[GIFTAMOUNT],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ASKLADDERID],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEQUENCE],
isnull([MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[TESTSEGMENTCODE], '') as [TESTSEGMENTCODE],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[TESTPARTDEFINITIONVALUESID] as [TESTSEGMENTCODEVALUEID],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[USEADDRESSPROCESSING],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONID],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[NAMEFORMATPARAMETERID],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[EXCLUDE]
from (select T.c.value('(ID)[1]','uniqueidentifier') as [ID] from @SUCCESSFULRULES.nodes('/RULES/ITEM') T(c)) as [SUCCESSFULRULES]
inner join dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] on [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID] = [SUCCESSFULRULES].[ID]
left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PACKAGEID]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEGMENTID]
where [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID
order by [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEQUENCE];
open SEGMENTCURSOR;
fetch next from SEGMENTCURSOR into @SEGMENTID, @CODE, @CODEVALUEID, @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @RESPONSERATE, @GIFTAMOUNT, @ASKLADDERID, @SEQUENCE, @TESTSEGMENTCODE, @TESTSEGMENTCODEVALUEID, @OVERRIDEADDRESSPROCESSING, @SEGMENTUSEADDRESSPROCESSING, @SEGMENTADDRESSPROCESSINGOPTIONID, @SEGMENTNAMEFORMATPARAMETERID, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @EXCLUDE;
while (@@FETCH_STATUS = 0)
begin
set @TARGETSEGMENTID = null;
--Create the segment...
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT]
@ID = @TARGETSEGMENTID output,
@CHANGEAGENTID = @CHANGEAGENTID,
@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE = @SEGMENTATIONID,
@MARKETINGPLANBRIEFID = null,
@SEGMENTID = @SEGMENTID,
@CODE = @CODE,
@TESTSEGMENTCODE = @TESTSEGMENTCODE,
@PACKAGEID = @PACKAGEID,
@PACKAGECODE = @PACKAGECODE,
@RESPONSERATE = @RESPONSERATE,
@GIFTAMOUNT = @GIFTAMOUNT,
@SAMPLESIZE = 100,
@SAMPLESIZETYPECODE = 0,
@SAMPLESIZEMETHODCODE = 0,
@SEQUENCE = @SEQUENCE,
@ASKLADDERID = @ASKLADDERID,
@SAMPLESIZEEXCLUDEREMAINDER = 1,
@OVERRIDEADDRESSPROCESSING = @OVERRIDEADDRESSPROCESSING,
@USEADDRESSPROCESSING = @SEGMENTUSEADDRESSPROCESSING,
@ADDRESSPROCESSINGOPTIONID = @SEGMENTADDRESSPROCESSINGOPTIONID,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
@NAMEFORMATPARAMETERID = @SEGMENTNAMEFORMATPARAMETERID,
@CODEVALUEID = @CODEVALUEID,
@TESTSEGMENTCODEVALUEID = @TESTSEGMENTCODEVALUEID,
@PACKAGECODEVALUEID = @PACKAGECODEVALUEID,
@ITEMLIST = null,
@CHANNELSOURCECODE = @CHANNELSOURCECODE,
@CHANNELSOURCECODEVALUEID = @CHANNELSOURCECODEVALUEID,
@EXCLUDESPOUSE = 0,
@OVERRIDEBUSINESSUNITS = 0,
@BUSINESSUNITS = null,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@EXCLUDE = @EXCLUDE;
fetch next from SEGMENTCURSOR into @SEGMENTID, @CODE, @CODEVALUEID, @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @RESPONSERATE, @GIFTAMOUNT, @ASKLADDERID, @SEQUENCE, @TESTSEGMENTCODE, @TESTSEGMENTCODEVALUEID, @OVERRIDEADDRESSPROCESSING, @SEGMENTUSEADDRESSPROCESSING, @SEGMENTADDRESSPROCESSINGOPTIONID, @SEGMENTNAMEFORMATPARAMETERID, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @SEGMENTADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @EXCLUDE;
end;
close SEGMENTCURSOR;
deallocate SEGMENTCURSOR;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;