USP_COMMUNICATIONS_CREATEORUPDATE_LETTERS
This procedure creates or updates the communication letters for a given communication.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @MAILTYPECODE | tinyint | IN | |
| @MAILEXPORTDEFINITIONID | uniqueidentifier | IN | |
| @EMAILEXPORTDEFINITIONID | uniqueidentifier | IN | |
| @LETTERS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_COMMUNICATIONS_CREATEORUPDATE_LETTERS
(
@SEGMENTATIONID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@MAILTYPECODE tinyint,
@MAILEXPORTDEFINITIONID uniqueidentifier = null,
@EMAILEXPORTDEFINITIONID uniqueidentifier = null,
@LETTERS xml = null
)
as
begin
-- @MAILTYPECODE pertains to the MailTypeCode field in dbo.MAILPREFERENCE for constituent communication preferences
-- 0 - Revenue Acknowledgements
-- 1 - Appeals
-- 2 - Events
-- 3 - General Correspondence
-- 4 - Reminders
-- 5 - Receipts
-- 6 - Planned Gift Acknowledgements
-- 7 - Tribute Acknowledgements
-- 8 - Stewardship
-- etc... This code should not need to change as new types are introduced...
-- @CHANNELCODE 0 - Mail, 1 - Email, 2 - Mail and Email
-- @CHANNELPREFERENCECODE 0 - Email, 1 - Mail
declare @COMMUNICATIONTYPECODE tinyint = 0;
declare @MAILDATE datetime = null;
declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null;
declare @NAMEFORMATPARAMETERID uniqueidentifier = null;
select
@COMMUNICATIONTYPECODE = COMMUNICATIONTYPECODE,
@MAILDATE = MAILDATE,
@ADDRESSPROCESSINGOPTIONID = ADDRESSPROCESSINGOPTIONID,
@NAMEFORMATPARAMETERID = NAMEFORMATPARAMETERID
from dbo.MKTSEGMENTATION
where ID = @SEGMENTATIONID;
declare @MAXSEQUENCE int = 0;
declare @COMMUNICATIONLETTERID uniqueidentifier;
declare @RUNNOW bit = 1;
declare @RUNSCHEDULED bit = 1;
declare @SEQUENCE int = 0;
declare @NAME nvarchar(100);
declare @CHANNELCODE tinyint;
declare @CHANNELPREFERENCECODE tinyint;
declare @OUTPUTTYPECODE tinyint;
declare @INCLUDEINACTIVE bit = 0;
declare @MKTASKLADDERID uniqueidentifier;
declare @MAILCONTENTHTML nvarchar(max);
declare @EMAILCONTENTHTML nvarchar(max);
declare @EMAILSUBJECT nvarchar(4000);
declare @EMAILFROMADDRESS dbo.UDT_EMAILADDRESS;
declare @EMAILFROMDISPLAYNAME nvarchar(255);
declare @EMAILREPLYTOADDRESS dbo.UDT_EMAILADDRESS;
declare @PAPERSIZECODE tinyint;
declare @MARGINTOP decimal(20, 4);
declare @MARGINBOTTOM decimal(20, 4);
declare @MARGINLEFT decimal(20, 4);
declare @MARGINRIGHT decimal(20, 4);
declare @MAILPACKAGEID uniqueidentifier;
declare @MAILSEGMENTID uniqueidentifier;
declare @EMAILPACKAGEID uniqueidentifier;
declare @EMAILSEGMENTID uniqueidentifier;
declare @SELECTIONS xml = null;
declare @EXCLUDEDSELECTIONS xml = null;
declare @EXCLUDEDSOLICITCODES xml = null;
declare @EXCLUDEBASEDONRECENTCOMMUNICATION bit;
declare @NUMRECENTCOMMUNICATIONPERIODS int;
declare @RECENTCOMMUNICATIONPERIODTYPECODE tinyint;
declare @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR bit;
declare @NUMTOTALCOMMUNICATIONSINPASTYEAR int;
declare @EXCLUDEBASEDONRECENTGIVING bit;
declare @NUMRECENTGIVINGPERIODS int;
declare @RECENTGIVINGPERIODTYPECODE tinyint;
declare @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR bit;
declare @TOTALREVENUEAMOUNTINPASTYEAR money;
declare @COMMUNICATIONTYPES xml = null;
declare @EMAILSEGMENTATIONSEGMENTID uniqueidentifier;
declare @EMAILRESPONSERATE decimal(5, 2);
declare @EMAILAVERAGEGIFTAMOUNT money;
declare @EMAILCOST money;
declare @MAILSEGMENTATIONSEGMENTID uniqueidentifier;
declare @MAILRESPONSERATE decimal(5, 2);
declare @MAILAVERAGEGIFTAMOUNT money;
declare @MAILCOST money;
declare @CURRENTDATE datetime = getDate();
-- Remove any letters from the mailing that were dropped
declare REMOVELETTERCURSOR cursor local fast_forward for
select
COMMUNICATIONLETTER.ID
from dbo.COMMUNICATIONLETTER
left outer join dbo.UFN_COMMUNICATION_GETLETTERS_FROMITEMLISTXML(@LETTERS) LETTERS
on COMMUNICATIONLETTER.ID = LETTERS.ID
where SEGMENTATIONID = @SEGMENTATIONID
and LETTERS.ID is null;
open REMOVELETTERCURSOR;
fetch next from REMOVELETTERCURSOR into @COMMUNICATIONLETTERID;
while (@@FETCH_STATUS = 0)
begin
exec dbo.USP_COMMUNICATIONLETTER_REMOVE @COMMUNICATIONLETTERID, @CHANGEAGENTID;
fetch next from REMOVELETTERCURSOR into @COMMUNICATIONLETTERID;
end
close REMOVELETTERCURSOR;
deallocate REMOVELETTERCURSOR;
-- Add/Update letters for this mailing
declare LETTERCURSOR cursor local fast_forward for
select
LETTERS.ID,
LETTERS.RUNNOW,
LETTERS.RUNSCHEDULED,
LETTERS.SEQUENCE,
LETTERS.NAME,
LETTERS.CHANNELCODE,
LETTERS.CHANNELPREFERENCECODE,
LETTERS.INCLUDEINACTIVE,
LETTERS.MKTASKLADDERID,
LETTERS.MAILCONTENTHTML,
LETTERS.EMAILCONTENTHTML,
LETTERS.EMAILSUBJECT,
coalesce(LETTERS.EMAILFROMADDRESS, ''),
LETTERS.EMAILFROMDISPLAYNAME,
coalesce(LETTERS.EMAILREPLYTOADDRESS, ''),
LETTERS.PAPERSIZECODE,
LETTERS.MARGINTOP,
LETTERS.MARGINBOTTOM,
LETTERS.MARGINLEFT,
LETTERS.MARGINRIGHT,
LETTERS.MAILPACKAGEID,
LETTERS.MAILSEGMENTID,
MAILSEGMENTATIONSEGMENT.ID,
coalesce(MAILSEGMENTATIONSEGMENT.RESPONSERATE, 0) as MAILRESPONSERATE,
coalesce(MAILSEGMENTATIONSEGMENT.GIFTAMOUNT, 0) as MAILAVERATEGIFTAMOUNT,
coalesce(MAILPACKAGE.UNITCOST, 0) as MAILCOST,
LETTERS.EMAILPACKAGEID,
LETTERS.EMAILSEGMENTID,
EMAILSEGMENTATIONSEGMENT.ID,
coalesce(EMAILSEGMENTATIONSEGMENT.RESPONSERATE, 0) as EMAILRESPONSERATE,
coalesce(EMAILSEGMENTATIONSEGMENT.GIFTAMOUNT, 0) as EMAILAVERATEGIFTAMOUNT,
coalesce(EMAILPACKAGE.UNITCOST, 0) as EMAILCOST,
LETTERS.SELECTIONSXML,
LETTERS.EXCLUDEDSELECTIONSXML,
LETTERS.EXCLUDEDSOLICITCODESXML,
LETTERS.EXCLUDEBASEDONRECENTCOMMUNICATION,
LETTERS.NUMRECENTCOMMUNICATIONPERIODS,
LETTERS.RECENTCOMMUNICATIONPERIODTYPECODE,
LETTERS.EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
LETTERS.NUMTOTALCOMMUNICATIONSINPASTYEAR,
LETTERS.EXCLUDEBASEDONRECENTGIVING,
LETTERS.NUMRECENTGIVINGPERIODS,
LETTERS.RECENTGIVINGPERIODTYPECODE,
LETTERS.EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
LETTERS.TOTALREVENUEAMOUNTINPASTYEAR,
LETTERS.COMMUNICATIONTYPESXML,
coalesce(LETTERS.OUTPUTTYPECODE, 0) as OUTPUTTYPECODE
from dbo.UFN_COMMUNICATION_GETLETTERS_FROMITEMLISTXML(@LETTERS) LETTERS
left join dbo.MKTSEGMENTATIONSEGMENT EMAILSEGMENTATIONSEGMENT
on EMAILSEGMENTATIONSEGMENT.SEGMENTATIONID = @SEGMENTATIONID
and LETTERS.EMAILSEGMENTID = EMAILSEGMENTATIONSEGMENT.SEGMENTID
left join dbo.MKTSEGMENTATIONSEGMENT MAILSEGMENTATIONSEGMENT
on MAILSEGMENTATIONSEGMENT.SEGMENTATIONID = @SEGMENTATIONID
and LETTERS.MAILSEGMENTID = MAILSEGMENTATIONSEGMENT.SEGMENTID
left join dbo.MKTPACKAGE EMAILPACKAGE
on LETTERS.EMAILPACKAGEID = EMAILPACKAGE.ID
left join dbo.MKTPACKAGE MAILPACKAGE
on LETTERS.EMAILPACKAGEID = MAILPACKAGE.ID
order by LETTERS.SEQUENCE asc;
open LETTERCURSOR;
fetch next from LETTERCURSOR into @COMMUNICATIONLETTERID, @RUNNOW, @RUNSCHEDULED, @SEQUENCE, @NAME, @CHANNELCODE, @CHANNELPREFERENCECODE,
@INCLUDEINACTIVE, @MKTASKLADDERID, @MAILCONTENTHTML, @EMAILCONTENTHTML,
@EMAILSUBJECT, @EMAILFROMADDRESS, @EMAILFROMDISPLAYNAME, @EMAILREPLYTOADDRESS, @PAPERSIZECODE, @MARGINTOP, @MARGINBOTTOM,
@MARGINLEFT, @MARGINRIGHT, @MAILPACKAGEID, @MAILSEGMENTID, @MAILSEGMENTATIONSEGMENTID, @MAILRESPONSERATE, @MAILAVERAGEGIFTAMOUNT, @MAILCOST,
@EMAILPACKAGEID, @EMAILSEGMENTID, @EMAILSEGMENTATIONSEGMENTID, @EMAILRESPONSERATE, @EMAILAVERAGEGIFTAMOUNT, @EMAILCOST,
@SELECTIONS, @EXCLUDEDSELECTIONS, @EXCLUDEDSOLICITCODES, @EXCLUDEBASEDONRECENTCOMMUNICATION, @NUMRECENTCOMMUNICATIONPERIODS,
@RECENTCOMMUNICATIONPERIODTYPECODE, @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR, @NUMTOTALCOMMUNICATIONSINPASTYEAR,
@EXCLUDEBASEDONRECENTGIVING, @NUMRECENTGIVINGPERIODS, @RECENTGIVINGPERIODTYPECODE, @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
@TOTALREVENUEAMOUNTINPASTYEAR, @COMMUNICATIONTYPES, @OUTPUTTYPECODE;
while (@@FETCH_STATUS = 0)
begin
if @COMMUNICATIONLETTERID is null or @COMMUNICATIONLETTERID = '00000000-0000-0000-0000-000000000000'
set @COMMUNICATIONLETTERID = newid();
if @OUTPUTTYPECODE = 1 -- CSV only
begin
set @CHANNELCODE = 0; -- assume mail only. Likely to change.
set @EMAILCONTENTHTML = '';
set @MAILCONTENTHTML = '';
set @EMAILFROMADDRESS = '';
set @EMAILSUBJECT = '';
set @EMAILFROMDISPLAYNAME = '';
set @EMAILREPLYTOADDRESS = '';
end
-- Create/Update the letter
if exists(select ID from dbo.COMMUNICATIONLETTER where ID = @COMMUNICATIONLETTERID)
update dbo.COMMUNICATIONLETTER set
RUNNOW = @RUNNOW,
RUNSCHEDULED = @RUNSCHEDULED,
SEQUENCE = @SEQUENCE,
NAME = @NAME,
CHANNELCODE = @CHANNELCODE,
CHANNELPREFERENCECODE = @CHANNELPREFERENCECODE,
OUTPUTTYPECODE = @OUTPUTTYPECODE,
INCLUDEINACTIVE = @INCLUDEINACTIVE,
MKTASKLADDERID = @MKTASKLADDERID,
MAILCONTENTHTML = @MAILCONTENTHTML,
EMAILCONTENTHTML = @EMAILCONTENTHTML,
EMAILSUBJECT = @EMAILSUBJECT,
EMAILFROMADDRESS = @EMAILFROMADDRESS,
EMAILFROMDISPLAYNAME = @EMAILFROMDISPLAYNAME,
EMAILREPLYTOADDRESS = @EMAILREPLYTOADDRESS,
PAPERSIZECODE = @PAPERSIZECODE,
MARGINTOP = @MARGINTOP,
MARGINBOTTOM = @MARGINBOTTOM,
MARGINLEFT = @MARGINLEFT,
MARGINRIGHT = @MARGINRIGHT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where COMMUNICATIONLETTER.ID = @COMMUNICATIONLETTERID;
else
insert into dbo.COMMUNICATIONLETTER
(ID, SEGMENTATIONID, RUNNOW, RUNSCHEDULED, COMMUNICATIONTYPECODE, SEQUENCE, NAME, CHANNELCODE, CHANNELPREFERENCECODE, OUTPUTTYPECODE, INCLUDEINACTIVE,
MKTASKLADDERID, MAILCONTENTHTML, EMAILCONTENTHTML, EMAILSUBJECT, EMAILFROMADDRESS, EMAILFROMDISPLAYNAME, EMAILREPLYTOADDRESS,
PAPERSIZECODE, MARGINTOP, MARGINBOTTOM, MARGINLEFT, MARGINRIGHT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@COMMUNICATIONLETTERID, @SEGMENTATIONID, @RUNNOW, @RUNSCHEDULED, @COMMUNICATIONTYPECODE, @SEQUENCE, @NAME, @CHANNELCODE, @CHANNELPREFERENCECODE, @OUTPUTTYPECODE, @INCLUDEINACTIVE,
@MKTASKLADDERID, @MAILCONTENTHTML, @EMAILCONTENTHTML, @EMAILSUBJECT, @EMAILFROMADDRESS, @EMAILFROMDISPLAYNAME, @EMAILREPLYTOADDRESS,
@PAPERSIZECODE, @MARGINTOP, @MARGINBOTTOM, @MARGINLEFT, @MARGINRIGHT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
-- Save the selections to be included in this letter
merge into dbo.COMMUNICATIONLETTERSELECTION as target
using (select ID, SELECTIONID from dbo.UFN_COMMUNICATIONLETTER_GETINCLUDEDSELECTIONS_FROMITEMLISTXML(@SELECTIONS)) as source
on (target.ID = source.ID and target.COMMUNICATIONLETTERID = @COMMUNICATIONLETTERID)
when not matched by target then
insert (ID, COMMUNICATIONLETTERID, IDSETREGISTERID, FILTERTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (newID(), @COMMUNICATIONLETTERID, source.SELECTIONID, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
when not matched by source and target.COMMUNICATIONLETTERID = @COMMUNICATIONLETTERID and target.FILTERTYPECODE = 0 then delete;
-- Save/Update the selections to be excluded from this letter
merge into dbo.COMMUNICATIONLETTERSELECTION as target
using (select ID, SELECTIONID from dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSELECTIONS_FROMITEMLISTXML(@EXCLUDEDSELECTIONS)) as source
on (target.ID = source.ID and target.COMMUNICATIONLETTERID = @COMMUNICATIONLETTERID)
when not matched by target then
insert (ID, COMMUNICATIONLETTERID, IDSETREGISTERID, FILTERTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (newID(), @COMMUNICATIONLETTERID, source.SELECTIONID, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
when not matched by source and target.COMMUNICATIONLETTERID = @COMMUNICATIONLETTERID and target.FILTERTYPECODE = 1 then delete;
-- Save/Update the solicit codes to be excluded from this letter
merge into dbo.COMMUNICATIONLETTEREXCLUDEDSOLICITCODE as target
using (select ID, SOLICITCODEID from dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSOLICITCODES_FROMITEMLISTXML(@EXCLUDEDSOLICITCODES)) as source
on (target.ID = source.ID and target.COMMUNICATIONLETTERID = @COMMUNICATIONLETTERID)
when not matched by target then
insert (ID, COMMUNICATIONLETTERID, SOLICITCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (newID(), @COMMUNICATIONLETTERID, source.SOLICITCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
when not matched by source and target.COMMUNICATIONLETTERID = @COMMUNICATIONLETTERID then delete;
-- Create an IDSET for the communication exclusions (Appeal mailings only)
if @COMMUNICATIONTYPECODE = 1
begin
declare @COMMUNICATIONEXCLUSIONID uniqueidentifier = null;
declare @IDSETREGISTERID uniqueidentifier = null;
exec dbo.USP_COMMUNICATIONLETTERACTIVITYEXCLUSIONS_CREATEORUPDATE
@COMMUNICATIONEXCLUSIONID output,
@IDSETREGISTERID output,
@CHANGEAGENTID,
@COMMUNICATIONLETTERID,
@EXCLUDEBASEDONRECENTCOMMUNICATION,
@NUMRECENTCOMMUNICATIONPERIODS,
@RECENTCOMMUNICATIONPERIODTYPECODE,
@EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
@NUMTOTALCOMMUNICATIONSINPASTYEAR,
@EXCLUDEBASEDONRECENTGIVING,
@NUMRECENTGIVINGPERIODS,
@RECENTGIVINGPERIODTYPECODE,
@EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
@TOTALREVENUEAMOUNTINPASTYEAR,
@COMMUNICATIONTYPES;
if @IDSETREGISTERID is not null
begin
declare @EXCLUDESELECTIONSTABLE table ([ID] uniqueidentifier, [SELECTIONID] uniqueidentifier);
insert into @EXCLUDESELECTIONSTABLE (ID, SELECTIONID)
select
ID,
SELECTIONID
from dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSELECTIONS_FROMITEMLISTXML(@EXCLUDEDSELECTIONS);
insert into @EXCLUDESELECTIONSTABLE
(ID, SELECTIONID)
values
(newID(), @IDSETREGISTERID);
-- Add communication exclusions IDSETREGISTERID to the EXCLUDEDSELECTIONS
set @EXCLUDEDSELECTIONS = (
select ID, SELECTIONID
from @EXCLUDESELECTIONSTABLE
for xml raw('ITEM'),type,elements,root('EXCLUDEDSELECTIONS'),BINARY BASE64
);
end
end
declare @PACKAGENAME nvarchar(100);
declare @PACKAGEDESCRIPTION nvarchar(255);
declare @SINGLECHANNEL bit;
declare @PREFERREDCHANNEL bit;
set @SINGLECHANNEL =
case @CHANNELCODE
when 2 then 0
else 1
end;
-- If sending email or both, create/update the email package, segment, and segmentation segment
if @CHANNELCODE <> 0
begin
-- Save the package
set @PACKAGENAME = dbo.UFN_MKTPACKAGE_GETUNIQUENAME('Email Package: ' + left(@NAME, 46) + ' (' + convert(nvarchar(36), @COMMUNICATIONLETTERID) + ')');
set @PACKAGEDESCRIPTION = 'Automatically generated email package for ''' + @NAME + ''' communication letter.';
-- Remove IsSystem flag before saving
update dbo.MKTPACKAGE set
ISSYSTEM = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @EMAILPACKAGEID;
exec dbo.USP_MKTPACKAGE_EMAIL_SAVE @EMAILPACKAGEID output, @CHANGEAGENTID, @PACKAGENAME, @PACKAGEDESCRIPTION, '', null, @EMAILCOST, 0, null, 0, 0, @EMAILEXPORTDEFINITIONID, null, '', null, @CURRENTAPPUSERID;
update dbo.MKTPACKAGE set
ISSYSTEM = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @EMAILPACKAGEID;
-- Create the segment
set @PREFERREDCHANNEL =
case @CHANNELCODE
when 2 then
case @CHANNELPREFERENCECODE
when 0 then 1
else 0
end
else 0
end;
exec dbo.[USP_COMMUNICATIONS_CREATEORUPDATESEGMENT_2]
@EMAILSEGMENTID output,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
@COMMUNICATIONLETTERID,
@NAME,
@SELECTIONS,
@EXCLUDEDSELECTIONS,
@EXCLUDEDSOLICITCODES,
@MAILTYPECODE, -- MailTypeCode 1 - Appeals
1, -- DeliveryMethodCode 1 - Email
@SINGLECHANNEL,
@PREFERREDCHANNEL,
@INCLUDEINACTIVE;
-- Create/Update the segmentation segment
set @SEQUENCE =
case @CHANNELCODE
when 2 then
case @CHANNELPREFERENCECODE
when 0 then @MAXSEQUENCE + 1
else @MAXSEQUENCE + 2
end
else @MAXSEQUENCE + 1
end
if @EMAILSEGMENTATIONSEGMENTID is null
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT]
@EMAILSEGMENTATIONSEGMENTID output,
@CHANGEAGENTID,
@SEGMENTATIONID, --@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE
null, --@MARKETINGPLANBRIEFID
@EMAILSEGMENTID, --@SEGMENTID
'', --@CODE
'', --@TESTSEGMENTCODE
@EMAILPACKAGEID, --@PACKAGEID
'', --@PACKAGECODE
@EMAILRESPONSERATE, --@RESPONSERATE (Revenue Calculator)
@EMAILAVERAGEGIFTAMOUNT, --@GIFTAMOUNT (Revenue Calculator)
100, --@SAMPLESIZE
0, --@SAMPLESIZETYPECODE
0, --@SAMPLESIZEMETHODCODE
@SEQUENCE,
@MKTASKLADDERID, --@MKTASKLADDERID
1, --@SAMPLESIZEEXCLUDEREMAINDER
0, --@OVERRIDEADDRESSPROCESSING
1, --@USEADDRESSPROCESSING
@ADDRESSPROCESSINGOPTIONID,
1, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@MAILDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
@NAMEFORMATPARAMETERID,
null, --@CODEVALUEID
null, --@TESTSEGMENTCODEVALUEID
null, --@PACKAGECODEVALUEID
null, --@ITEMLIST
'', --@CHANNELSOURCECODE
null; --@CHANNELSOURCECODEVALUEID
else
begin
exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_6]
@EMAILSEGMENTATIONSEGMENTID,
@CHANGEAGENTID,
@EMAILSEGMENTID, --@SEGMENTID
'', --@CODE
'', --@TESTSEGMENTCODE
@EMAILPACKAGEID, --@PACKAGEID
'', --@PACKAGECODE
@EMAILRESPONSERATE, --@RESPONSERATE (Revenue Calculator)
@EMAILAVERAGEGIFTAMOUNT, --@GIFTAMOUNT (Revenue Calculator)
100, --@SAMPLESIZE
0, --@SAMPLESIZETYPECODE
0, --@SAMPLESIZEMETHODCODE
1, --@SAMPLESIZEEXCLUDEREMAINDER
@MKTASKLADDERID, --@MKTASKLADDERID
0, --@OVERRIDEADDRESSPROCESSING
1, --@USEADDRESSPROCESSING
@ADDRESSPROCESSINGOPTIONID,
1, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@MAILDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
@NAMEFORMATPARAMETERID,
null, --@CODEVALUEID
null, --@PACKAGECODEVALUEID
null, --@TESTSEGMENTCODEVALUEID
null, --@ITEMLIST
'', --@CHANNELSOURCECODE
null, --@CHANNELSOURCECODEVALUEID
0, --@EXCLUDESPOUSE
0, --@OVERRIDEBUSINESSUNITS
null; --@BUSINESSUNITS
update dbo.MKTSEGMENTATIONSEGMENT set
SEQUENCE = @SEQUENCE
where ID = @EMAILSEGMENTATIONSEGMENTID;
end
end
-- If sending mail or both, create/update package, segment, and segmentation segment
if @CHANNELCODE <> 1
begin
--Save the package
set @PACKAGENAME = dbo.UFN_MKTPACKAGE_GETUNIQUENAME('Mail Package: ' + left(@NAME, 46) + ' (' + convert(nvarchar(36), @COMMUNICATIONLETTERID) + ')');
set @PACKAGEDESCRIPTION = 'Automatically generated mail package for ''' + @NAME + ''' communication letter.';
if not exists(select ID from dbo.MKTPACKAGE where ID = @MAILPACKAGEID)
exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTPACKAGE_MAIL @MAILPACKAGEID output, @CHANGEAGENTID, @PACKAGENAME, @PACKAGEDESCRIPTION, '', @MAILCOST, 0, null, null, null, @MAILEXPORTDEFINITIONID, '255', null, '', null, @CURRENTAPPUSERID;
else
begin
-- Remove IsSystem flag before editing the mail package
update dbo.MKTPACKAGE set
ISSYSTEM = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @MAILPACKAGEID;
exec dbo.USP_DATAFORMTEMPLATE_EDIT_MKTPACKAGE_MAIL @MAILPACKAGEID, @CHANGEAGENTID, @PACKAGENAME, @PACKAGEDESCRIPTION, '', null, @MAILCOST, 0, null, null, @MAILEXPORTDEFINITIONID, null, '', null;
end
update dbo.MKTPACKAGE set
ISSYSTEM = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @MAILPACKAGEID;
set @PREFERREDCHANNEL =
case @CHANNELCODE
when 2 then
case @CHANNELPREFERENCECODE
when 1 then 1
else 0
end
else 0
end;
-- Create or update the segment
exec dbo.[USP_COMMUNICATIONS_CREATEORUPDATESEGMENT_2]
@MAILSEGMENTID output,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
@COMMUNICATIONLETTERID,
@NAME,
@SELECTIONS,
@EXCLUDEDSELECTIONS,
@EXCLUDEDSOLICITCODES,
@MAILTYPECODE, -- MailTypeCode 1 - Appeals
0, -- DeliveryMethodCode 0 - Mail
@SINGLECHANNEL,
@PREFERREDCHANNEL,
@INCLUDEINACTIVE;
-- Create/Update the segmentation segment
set @SEQUENCE =
case @CHANNELCODE
when 2 then
case @CHANNELPREFERENCECODE
when 1 then @MAXSEQUENCE + 1
else @MAXSEQUENCE + 2
end
else @MAXSEQUENCE + 1
end
if @MAILSEGMENTATIONSEGMENTID is null
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT]
@MAILSEGMENTATIONSEGMENTID output,
@CHANGEAGENTID,
@SEGMENTATIONID, --@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE
null, --@MARKETINGPLANBRIEFID
@MAILSEGMENTID, --@SEGMENTID
'', --@CODE
'', --@TESTSEGMENTCODE
@MAILPACKAGEID, --@PACKAGEID
'', --@PACKAGECODE
@MAILRESPONSERATE, --@RESPONSERATE (Revenue Calculator)
@MAILAVERAGEGIFTAMOUNT, --@GIFTAMOUNT (Revenue Calculator)
100, --@SAMPLESIZE
0, --@SAMPLESIZETYPECODE
0, --@SAMPLESIZEMETHODCODE
@SEQUENCE,
@MKTASKLADDERID, --@MKTASKLADDERID
1, --@SAMPLESIZEEXCLUDEREMAINDER
0, --@OVERRIDEADDRESSPROCESSING
1, --@USEADDRESSPROCESSING
@ADDRESSPROCESSINGOPTIONID,
1, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@MAILDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
@NAMEFORMATPARAMETERID,
null, --@CODEVALUEID
null, --@TESTSEGMENTCODEVALUEID
null, --@PACKAGECODEVALUEID
null, --@ITEMLIST
'', --@CHANNELSOURCECODE
null; --@CHANNELSOURCECODEVALUEID
else
begin
exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_6]
@MAILSEGMENTATIONSEGMENTID,
@CHANGEAGENTID,
@MAILSEGMENTID, --@SEGMENTID
'', --@CODE
'', --@TESTSEGMENTCODE
@MAILPACKAGEID, --@PACKAGEID
'', --@PACKAGECODE
@MAILRESPONSERATE, --@RESPONSERATE (Revenue Calculator)
@MAILAVERAGEGIFTAMOUNT, --@GIFTAMOUNT (Revenue Calculator)
100, --@SAMPLESIZE
0, --@SAMPLESIZETYPECODE
0, --@SAMPLESIZEMETHODCODE
1, --@SAMPLESIZEEXCLUDEREMAINDER
@MKTASKLADDERID, --@MKTASKLADDERID
0, --@OVERRIDEADDRESSPROCESSING
1, --@USEADDRESSPROCESSING
@ADDRESSPROCESSINGOPTIONID,
1, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@MAILDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
@NAMEFORMATPARAMETERID,
null, --@CODEVALUEID
null, --@PACKAGECODEVALUEID
null, --@TESTSEGMENTCODEVALUEID
null, --@ITEMLIST
'', --@CHANNELSOURCECODE
null, --@CHANNELSOURCECODEVALUEID
0, --@EXCLUDESPOUSE
0, --@OVERRIDEBUSINESSUNITS
null; --@BUSINESSUNITS
update dbo.MKTSEGMENTATIONSEGMENT set
SEQUENCE = @SEQUENCE
where ID = @MAILSEGMENTATIONSEGMENTID;
end
end
-- Delete packages and segments for lingering channels
declare @SEGMENTATIONSEGMENTID uniqueidentifier = null;
declare @MKTSEGMENTATIONPACKAGEID uniqueidentifier = null;
declare @LINGERINGCHANNEL_PACKAGEID uniqueidentifier = null;
declare @LINGERINGCHANNEL_SEGMENTID uniqueidentifier = null;
if @CHANNELCODE = 1 and @MAILPACKAGEID is not null and @MAILSEGMENTID is not null
begin
set @LINGERINGCHANNEL_PACKAGEID = @MAILPACKAGEID;
set @MAILPACKAGEID = null;
set @LINGERINGCHANNEL_SEGMENTID = @MAILSEGMENTID;
set @MAILSEGMENTID = null;
end
if @CHANNELCODE = 0 and @EMAILPACKAGEID is not null and @EMAILSEGMENTID is not null
begin
set @LINGERINGCHANNEL_PACKAGEID = @EMAILPACKAGEID;
set @EMAILPACKAGEID = null;
set @LINGERINGCHANNEL_SEGMENTID = @EMAILSEGMENTID;
set @EMAILSEGMENTID = null;
end
-- Update the letter with the package and segment id's
update dbo.COMMUNICATIONLETTER set
MAILPACKAGEID = case @MAILPACKAGEID when '00000000-0000-0000-0000-000000000000' then null else @MAILPACKAGEID end,
MAILSEGMENTID = case @MAILSEGMENTID when '00000000-0000-0000-0000-000000000000' then null else @MAILSEGMENTID end,
EMAILPACKAGEID = case @EMAILPACKAGEID when '00000000-0000-0000-0000-000000000000' then null else @EMAILPACKAGEID end,
EMAILSEGMENTID = case @EMAILSEGMENTID when '00000000-0000-0000-0000-000000000000' then null else @EMAILSEGMENTID end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @COMMUNICATIONLETTERID;
-- Remove package/segment for lingering channel
if @LINGERINGCHANNEL_PACKAGEID is not null and @LINGERINGCHANNEL_SEGMENTID is not null
begin
select
@SEGMENTATIONSEGMENTID = ID
from dbo.MKTSEGMENTATIONSEGMENT
where SEGMENTID = @LINGERINGCHANNEL_SEGMENTID
and SEGMENTATIONID = @SEGMENTATIONID;
if @SEGMENTATIONSEGMENTID is not null
exec dbo.USP_MKTSEGMENTATIONSEGMENT_DELETE @SEGMENTATIONSEGMENTID, @CHANGEAGENTID;
-- delete the segmentation package, not the package itself. The package is needed to maintain run history for tables like REVENUELETTER.
select
@MKTSEGMENTATIONPACKAGEID = ID
from dbo.MKTSEGMENTATIONPACKAGE
where PACKAGEID = @LINGERINGCHANNEL_PACKAGEID
and SEGMENTATIONID = @SEGMENTATIONID;
exec dbo.USP_MKTSEGMENTATIONPACKAGE_DELETE @MKTSEGMENTATIONPACKAGEID, @CHANGEAGENTID;
update dbo.MKTSEGMENT set
ISSYSTEM = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @LINGERINGCHANNEL_SEGMENTID;
exec dbo.USP_MKTSEGMENT_DELETE @LINGERINGCHANNEL_SEGMENTID, @CHANGEAGENTID, @CURRENTAPPUSERID;
end
if @CHANNELCODE = 2
set @MAXSEQUENCE = @MAXSEQUENCE + 2;
else
set @MAXSEQUENCE = @MAXSEQUENCE + 1;
fetch next from LETTERCURSOR into @COMMUNICATIONLETTERID, @RUNNOW, @RUNSCHEDULED, @SEQUENCE, @NAME, @CHANNELCODE, @CHANNELPREFERENCECODE,
@INCLUDEINACTIVE, @MKTASKLADDERID, @MAILCONTENTHTML, @EMAILCONTENTHTML,
@EMAILSUBJECT, @EMAILFROMADDRESS, @EMAILFROMDISPLAYNAME, @EMAILREPLYTOADDRESS, @PAPERSIZECODE, @MARGINTOP, @MARGINBOTTOM,
@MARGINLEFT, @MARGINRIGHT, @MAILPACKAGEID, @MAILSEGMENTID, @MAILSEGMENTATIONSEGMENTID, @MAILRESPONSERATE, @MAILAVERAGEGIFTAMOUNT, @MAILCOST,
@EMAILPACKAGEID, @EMAILSEGMENTID, @EMAILSEGMENTATIONSEGMENTID, @EMAILRESPONSERATE, @EMAILAVERAGEGIFTAMOUNT, @EMAILCOST,
@SELECTIONS, @EXCLUDEDSELECTIONS, @EXCLUDEDSOLICITCODES, @EXCLUDEBASEDONRECENTCOMMUNICATION, @NUMRECENTCOMMUNICATIONPERIODS,
@RECENTCOMMUNICATIONPERIODTYPECODE, @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR, @NUMTOTALCOMMUNICATIONSINPASTYEAR,
@EXCLUDEBASEDONRECENTGIVING, @NUMRECENTGIVINGPERIODS, @RECENTGIVINGPERIODTYPECODE, @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
@TOTALREVENUEAMOUNTINPASTYEAR, @COMMUNICATIONTYPES, @OUTPUTTYPECODE;
end
close LETTERCURSOR;
deallocate LETTERCURSOR;
end