USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPRENEWALEFFORT
The save procedure used by the add dataform template "Membership Renewal Effort Add Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @MEMBERSHIPPROGRAMID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @MEMBERSHIPPROGRAMNAME | nvarchar(50) | IN | Name |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(500) | IN | Description |
| @RENEWALNOTICES | xml | IN | Renewal notices |
| @MEMBERSHIPLEVELS | xml | IN | Membership levels to include |
| @DEFAULTCHANNEL | bit | IN | Default channel |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPRENEWALEFFORT
(
@ID uniqueidentifier = null output,
@MEMBERSHIPPROGRAMID uniqueidentifier,
@MEMBERSHIPPROGRAMNAME nvarchar(50) = null,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100) = null,
@DESCRIPTION nvarchar(500) = null,
@RENEWALNOTICES xml = null,
@MEMBERSHIPLEVELS xml = null,
@DEFAULTCHANNEL bit = 0,
@CURRENTAPPUSERID uniqueidentifier
)
as
set nocount on;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate();
if @MEMBERSHIPPROGRAMNAME is null
select @MEMBERSHIPPROGRAMNAME = NAME
from dbo.MEMBERSHIPPROGRAM
where ID = @MEMBERSHIPPROGRAMID;
begin try
if not exists (select ID from dbo.MKTEXPORTDEFINITION
where ID in ('079E3C09-1D86-4B6B-AD25-85C606E4761B', '0CB53337-8979-419C-A782-42A6D983DACB'))
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_NOEXPORTDEFINITIONCONFIGURATIONDATA', 13, 1)
return 1
end
if not exists (select ID from dbo.ADDRESSPROCESSINGOPTION
where ID = '12825E0C-DFC4-4C1A-A512-E26194EB6237')
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_NOADDRESSPROCESSINGOPTIONCONFIGURATIONDATA', 13, 1)
return 1
end
if not exists (select ID from dbo.NAMEFORMATPARAMETER
where ID = '4AFC6309-E26A-4012-95C8-8A59EE751EE5')
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_NONAMEFORMATPARAMETERCONFIGURATIONDATA', 13, 1)
return 1
end
if exists (select ID from dbo.MKTMEMBERSHIPMAILINGTEMPLATE
where NAME = @NAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
if not exists (select T.item.value('(ID)[1]','uniqueidentifier')
from @MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(item)
where T.item.value('(SELECTED)[1]','bit') = 1)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_NOMEMBERSHIPLEVELSELECTED', 13, 1)
return 1
end
if not exists (select T.item.value('(ID)[1]','uniqueidentifier')
from @RENEWALNOTICES.nodes('/RENEWALNOTICES/ITEM') T(item)
where T.item.value('(SELECTED)[1]','bit') = 1)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_NORENEWALNOTICESELECTED', 13, 1)
return 1
end
if not exists (select T.item.value('(ID)[1]','uniqueidentifier'),
T.item.value('(TIMETOEXPIRATION)[1]','integer'),
T.item.value('(MAILPACKAGEID)[1]','uniqueidentifier'),
T.item.value('(EMAILPACKAGEID)[1]','uniqueidentifier')
from @RENEWALNOTICES.nodes('/RENEWALNOTICES/ITEM') T(item)
where T.item.value('(SELECTED)[1]','bit') = 1
and ((not T.item.value('(MAILPACKAGEID)[1]','uniqueidentifier') is null)
or (not T.item.value('(EMAILPACKAGEID)[1]','uniqueidentifier') is null)))
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_NOPACKAGESELECTED', 13, 1)
return 1
end
declare @SITEID uniqueidentifier
--If site is required for the current user, then get the site from the membership program
if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1
select @SITEID = MEMBERSHIPPROGRAM.SITEID
from dbo.MEMBERSHIPPROGRAM
where MEMBERSHIPPROGRAM.ID = @MEMBERSHIPPROGRAMID
---------------------------------------------------------
-- 1. Create Membership Renewal Effort Template
---------------------------------------------------------
-- 1.1 Create appeal
declare @APPEALID uniqueidentifier;
exec dbo.USP_DATAFORMTEMPLATE_ADD_APPEAL
@APPEALID output,
@CHANGEAGENTID ,
@NAME, --@NAME
@NAME, --@DESCRIPTION
null, --@APPEALCATEGORYCODEID
null, --@BUSINESSUNITCODEID
@CURRENTDATE, --@STARTDATE
null, --@ENDDATE
0, --@GOAL
null, --@APPEALREPORT1CODEID
@SITEID, --@SITEID
@MEMBERSHIPPROGRAMID,
@CURRENTAPPUSERID
if @APPEALID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEAPPEAL', 13, 1)
return 1
end
-- 1.2 Create template
declare @APPEALINFORMATION xml
set @APPEALINFORMATION = (
select
[QUERYVIEWCATALOG].[DISPLAYNAME] as "RECORDSOURCENAME",
[MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID] as "SEARCHLISTCATALOGID",
[MKTAPPEALRECORDSOURCE].[ID] as "RECORDSOURCEID",
@APPEALID as APPEALSYSTEMID,
@NAME as APPEALID,
@NAME as APPEALDESCRIPTION
from dbo.[MKTAPPEALRECORDSOURCE] inner join QUERYVIEWCATALOG on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
for xml raw('ITEM'),type,elements,root('APPEALINFORMATION'),BINARY BASE64
)
exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTMEMBERSHIPMAILINGTEMPLATE
@ID output,
@CURRENTAPPUSERID,
@CHANGEAGENTID,
'', --@CODE
@NAME, --@NAME
@DESCRIPTION, --@DESCRIPTION
null, --@INCLUDESELECTIONS
null, --@EXCLUDESELECTIONS
null, --@SOURCECODEID
null, --@ITEMLIST
@SITEID, --@SITEID
0, --@EXCLUSIONDATETYPECODE
null, --@EXCLUSIONASOFDATE
1, --@EXCLUDEDECEASED
1, --@EXCLUDEINACTIVE
null, --@EXCLUSIONS
1, --@USEADDRESSPROCESSING
'12825E0C-DFC4-4C1A-A512-E26194EB6237', --@ADDRESSPROCESSINGOPTIONID
'4AFC6309-E26A-4012-95C8-8A59EE751EE5', --@NAMEFORMATPARAMETERID
0, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@CURRENTDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
null, --@ACTIVATIONKPIS
0, --@USEKPISASDEFAULT
@APPEALINFORMATION, --@APPEALINFORMATION
1, --@RUNACTIVATEANDEXPORT
'', --@EXPORTDESCRIPTION
'079E3C09-1D86-4B6B-AD25-85C606E4761B', --@MAILEXPORTDEFINITIONID
'0CB53337-8979-419C-A782-42A6D983DACB', --@EMAILEXPORTDEFINITIONID
null, --@PHONEEXPORTDEFINITIONID
null, --@CODEVALUEID
1, --@RUNSEGMENTATIONSEGMENTREFRESHPROCESS
0 --@CACHESOURCEANALYSISRULEDATA
if @ID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEMEMBERSHIPMAILINGTEMPLATE', 13, 1)
return 1
end
-- Create membership renewal effort process record if necessary
if not exists (select 1 from dbo.MKTMEMBERSHIPRENEWALEFFORTPROCESS where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID)
insert into dbo.[MKTMEMBERSHIPRENEWALEFFORTPROCESS] (
[ID],
[MEMBERSHIPPROGRAMID],
[MEMBERSHIPMAILINGTEMPLATEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
) values (
newid(),
@MEMBERSHIPPROGRAMID,
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
---------------------------------------------------------
-- 2. Create Membership Rules (one rule for each package)
---------------------------------------------------------
declare @SELECTED bit
declare @TIMETOEXPIRATION int
declare @MAILPACKAGEID uniqueidentifier
declare @EMAILPACKAGEID uniqueidentifier
declare @SMARTQUERYNAME nvarchar(255)
declare @SMARTQUERYINSTANCEID uniqueidentifier
declare @SMARTQUERYCATALOGID uniqueidentifier
declare @TVFNAME nvarchar(128)
declare @TVFPARAMETERVALUES nvarchar(max)
declare @DATAFORMITEMFILTERXML xml
declare @IDSETREGISTERID uniqueidentifier
declare @QUERYVIEWCATALOGID uniqueidentifier
declare @T1 table([VALUE] uniqueidentifier, [LABEL] nvarchar(255))
declare @SELECTIONS xml
declare @SEGMENTID uniqueidentifier
declare @SEGMENTNAME nvarchar(100)
declare @RULEID uniqueidentifier
declare @LEVELID uniqueidentifier
declare @LEVELNAME nvarchar(50)
declare @FILTERSELECTION uniqueidentifier
declare @PRIMARYKEYFIELD nvarchar(128)
declare @STATICTABLE nvarchar(128)
declare @MAILINGPREFERENCESMARTQUERYNAME nvarchar(255)
declare @MAILINGPREFERENCESMARTQUERYINSTANCEID uniqueidentifier
declare @MAILINGPREFERENCESMARTQUERYCATALOGID uniqueidentifier
declare @MAILINGPREFERENCETVFNAME nvarchar(128)
declare @MAILINGPREFERENCETVFPARAMETERVALUES nvarchar(max)
declare @MAILINGPREFERENCEDATAFORMITEMFILTERXML xml
declare @MAILINGPREFERENCEEMAILIDSETREGISTERID uniqueidentifier
declare @MAILINGPREFERENCEMAILIDSETREGISTERID uniqueidentifier
declare @MAILINGPREFERENCEBOTHEXCEPTMAILIDSETREGISTERID uniqueidentifier
declare @MAILINGPREFERENCEBOTHEXCEPTEMAILIDSETREGISTERID uniqueidentifier
declare @PACKAGEEXPORTDEFINITIONNOTFORMEMBERSHIP nvarchar(255)
declare @EXPORTRECORDTYPEID uniqueidentifier
select @MAILINGPREFERENCESMARTQUERYCATALOGID = ID, @MAILINGPREFERENCETVFNAME = TVFNAME
from dbo.SMARTQUERYCATALOG
where NAME = 'Membership Mailing Preference Smart Query'
-- 2.0 Create smart query (static) selections for mailing preference
-- 2.0.1 Email preference selection
set @MAILINGPREFERENCESMARTQUERYNAME = null
set @MAILINGPREFERENCESMARTQUERYNAME = @NAME + ' - Mailing Preference - Email'
if exists (select ID from dbo.SMARTQUERYINSTANCE
where NAME = @MAILINGPREFERENCESMARTQUERYNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
set @MAILINGPREFERENCESMARTQUERYINSTANCEID = newid()
set @MAILINGPREFERENCEDATAFORMITEMFILTERXML = '
<DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
<Values>
<fv ID="MEMBERSHIPPROGRAMID">
<Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + '</Value>
</fv>
<fv ID="MAILINGPREFERENCE">
<Value xsi:type="xsd:string">1</Value>
</fv>
<fv ID="INCLUDENOPREFERENCE">
<Value xsi:type="xsd:boolean">true</Value>
</fv>
<fv ID="EXCLUDEPREFERENCE" />
</Values>
</DataFormItem>
';
set @MAILINGPREFERENCETVFPARAMETERVALUES = 'N''' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + ''' /*@MEMBERSHIPPROGRAMID*/,
1 /*@MAILINGPREFERENCE*/,
1 /*@INCLUDENOPREFERENCE*/,
null /*@EXCLUDEPREFERENCE*/,
''' + convert(nvarchar(50), @CURRENTAPPUSERID) + ''' /*@CURRENTAPPUSERID*/,
2147483647 /*@MAXROWS*/
'
-- Create smart query instance
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATE]
@MAILINGPREFERENCESMARTQUERYINSTANCEID, --@ID
@MAILINGPREFERENCESMARTQUERYCATALOGID, --@SMARTQUERYCATALOGID
@MAILINGPREFERENCESMARTQUERYNAME, --@NAME
@MAILINGPREFERENCESMARTQUERYNAME, --@DESCRIPTION
null, --@QUERYCATEGORYCODEID
null, --@DATAFORMINSTANCEID
@MAILINGPREFERENCEDATAFORMITEMFILTERXML, --@DATAFORMITEMFILTERXML
1, --@OTHERSCANMODIFY
@CURRENTAPPUSERID, --@APPUSERID
@CHANGEAGENTID, --@CHANGEAGENTID
@SITEID, --@SITEID
0 --@MOBILIZE
-- Create table for static selection
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATESTATICTABLE]
@MAILINGPREFERENCESMARTQUERYINSTANCEID
-- Insert static selection table
select @PRIMARYKEYFIELD = PRIMARYKEYFIELD from SMARTQUERYCATALOG
inner join dbo.SMARTQUERYINSTANCE on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
where SMARTQUERYINSTANCE.ID = @MAILINGPREFERENCESMARTQUERYINSTANCEID;
set @STATICTABLE = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETSTATICTABLENAME(@MAILINGPREFERENCESMARTQUERYINSTANCEID);
exec ('insert into ' + @STATICTABLE +
' select DISTINCT ' + @PRIMARYKEYFIELD + ' ID from dbo.' + @MAILINGPREFERENCETVFNAME + '(' + @MAILINGPREFERENCETVFPARAMETERVALUES + ')')
set @MAILINGPREFERENCEEMAILIDSETREGISTERID = null
-- Create ID set record
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATEIDSET]
@MAILINGPREFERENCESMARTQUERYINSTANCEID, --@SMARTQUERYINSTANCEID
@MAILINGPREFERENCETVFNAME, --@TVFNAME
@MAILINGPREFERENCETVFPARAMETERVALUES, --@TVFPARAMETERVALUES
1, --@ISSTATIC
1, --@USEINQUERYDESIGNER
null, --@NUMROWS
@CHANGEAGENTID,
@MAILINGPREFERENCEEMAILIDSETREGISTERID output
if @MAILINGPREFERENCEEMAILIDSETREGISTERID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEIDSETREGISTER', 13, 1)
return 1
end
-- 2.0.2 Mail preference selection
set @MAILINGPREFERENCESMARTQUERYNAME = null
set @MAILINGPREFERENCESMARTQUERYNAME = @NAME + ' - Mailing Preference - Mail'
if exists (select ID from dbo.SMARTQUERYINSTANCE
where NAME = @MAILINGPREFERENCESMARTQUERYNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
set @MAILINGPREFERENCESMARTQUERYINSTANCEID = null
set @MAILINGPREFERENCESMARTQUERYINSTANCEID = newid()
set @MAILINGPREFERENCEDATAFORMITEMFILTERXML = '
<DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
<Values>
<fv ID="MEMBERSHIPPROGRAMID">
<Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + '</Value>
</fv>
<fv ID="MAILINGPREFERENCE">
<Value xsi:type="xsd:string">0</Value>
</fv>
<fv ID="INCLUDENOPREFERENCE">
<Value xsi:type="xsd:boolean">true</Value>
</fv>
<fv ID="EXCLUDEPREFERENCE" />
</Values>
</DataFormItem>
';
set @MAILINGPREFERENCETVFPARAMETERVALUES = 'N''' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + ''' /*@MEMBERSHIPPROGRAMID*/,
0 /*@MAILINGPREFERENCE*/,
1 /*@INCLUDENOPREFERENCE*/,
null /*@EXCLUDEPREFERENCE*/,
''' + convert(nvarchar(50), @CURRENTAPPUSERID) + ''' /*@CURRENTAPPUSERID*/,
2147483647 /*@MAXROWS*/
'
-- Create smart query instance
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATE]
@MAILINGPREFERENCESMARTQUERYINSTANCEID, --@ID
@MAILINGPREFERENCESMARTQUERYCATALOGID, --@SMARTQUERYCATALOGID
@MAILINGPREFERENCESMARTQUERYNAME, --@NAME
@MAILINGPREFERENCESMARTQUERYNAME, --@DESCRIPTION
null, --@QUERYCATEGORYCODEID
null, --@DATAFORMINSTANCEID
@MAILINGPREFERENCEDATAFORMITEMFILTERXML, --@DATAFORMITEMFILTERXML
1, --@OTHERSCANMODIFY
@CURRENTAPPUSERID, --@APPUSERID
@CHANGEAGENTID, --@CHANGEAGENTID
@SITEID, --@SITEID
0 --@MOBILIZE
-- Create table for static selection
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATESTATICTABLE]
@MAILINGPREFERENCESMARTQUERYINSTANCEID
-- Insert static selection table
select @PRIMARYKEYFIELD = PRIMARYKEYFIELD from SMARTQUERYCATALOG
inner join dbo.SMARTQUERYINSTANCE on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
where SMARTQUERYINSTANCE.ID = @MAILINGPREFERENCESMARTQUERYINSTANCEID;
set @STATICTABLE = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETSTATICTABLENAME(@MAILINGPREFERENCESMARTQUERYINSTANCEID);
exec ('insert into ' + @STATICTABLE +
' select DISTINCT ' + @PRIMARYKEYFIELD + ' ID from dbo.' + @MAILINGPREFERENCETVFNAME + '(' + @MAILINGPREFERENCETVFPARAMETERVALUES + ')')
set @MAILINGPREFERENCEMAILIDSETREGISTERID = null
-- Create ID set record
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATEIDSET]
@MAILINGPREFERENCESMARTQUERYINSTANCEID, --@SMARTQUERYINSTANCEID
@MAILINGPREFERENCETVFNAME, --@TVFNAME
@MAILINGPREFERENCETVFPARAMETERVALUES, --@TVFPARAMETERVALUES
1, --@ISSTATIC
1, --@USEINQUERYDESIGNER
null, --@NUMROWS
@CHANGEAGENTID,
@MAILINGPREFERENCEMAILIDSETREGISTERID output
if @MAILINGPREFERENCEMAILIDSETREGISTERID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEIDSETREGISTER', 13, 1)
return 1
end
-- 2.0.3 This selection is used when there is only email package selected
set @MAILINGPREFERENCESMARTQUERYNAME = null;
set @MAILINGPREFERENCESMARTQUERYNAME = @NAME + ' - Mailing Preference - Both (Exclude mail only)';
if exists (select ID from dbo.SMARTQUERYINSTANCE
where NAME = @MAILINGPREFERENCESMARTQUERYNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1);
return 1;
end
set @MAILINGPREFERENCESMARTQUERYINSTANCEID = null;
set @MAILINGPREFERENCESMARTQUERYINSTANCEID = newid();
set @MAILINGPREFERENCEDATAFORMITEMFILTERXML = '
<DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
<Values>
<fv ID="MEMBERSHIPPROGRAMID">
<Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + '</Value>
</fv>
<fv ID="MAILINGPREFERENCE" >
<Value xsi:type="xsd:string">1</Value>
</fv>
<fv ID="INCLUDENOPREFERENCE">
<Value xsi:type="xsd:boolean">true</Value>
</fv>
<fv ID="EXCLUDEPREFERENCE">
<Value xsi:type="xsd:string">1</Value>
</fv>
</Values>
</DataFormItem>
';
set @MAILINGPREFERENCETVFPARAMETERVALUES = 'N''' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + ''' /*@MEMBERSHIPPROGRAMID*/,
1 /*@MAILINGPREFERENCE*/,
1 /*@INCLUDENOPREFERENCE*/,
1 /*@EXCLUDEPREFERENCE*/,
''' + convert(nvarchar(50), @CURRENTAPPUSERID) + ''' /*@CURRENTAPPUSERID*/,
2147483647 /*@MAXROWS*/
'
-- Create smart query instance
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATE]
@MAILINGPREFERENCESMARTQUERYINSTANCEID, --@ID
@MAILINGPREFERENCESMARTQUERYCATALOGID, --@SMARTQUERYCATALOGID
@MAILINGPREFERENCESMARTQUERYNAME, --@NAME
@MAILINGPREFERENCESMARTQUERYNAME, --@DESCRIPTION
null, --@QUERYCATEGORYCODEID
null, --@DATAFORMINSTANCEID
@MAILINGPREFERENCEDATAFORMITEMFILTERXML, --@DATAFORMITEMFILTERXML
1, --@OTHERSCANMODIFY
@CURRENTAPPUSERID, --@APPUSERID
@CHANGEAGENTID, --@CHANGEAGENTID
@SITEID, --@SITEID
0 --@MOBILIZE
-- Create table for static selection
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATESTATICTABLE]
@MAILINGPREFERENCESMARTQUERYINSTANCEID
-- Insert static selection table
select @PRIMARYKEYFIELD = PRIMARYKEYFIELD from SMARTQUERYCATALOG
inner join dbo.SMARTQUERYINSTANCE on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
where SMARTQUERYINSTANCE.ID = @MAILINGPREFERENCESMARTQUERYINSTANCEID;
set @STATICTABLE = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETSTATICTABLENAME(@MAILINGPREFERENCESMARTQUERYINSTANCEID);
exec ('insert into ' + @STATICTABLE +
' select DISTINCT ' + @PRIMARYKEYFIELD + ' ID from dbo.' + @MAILINGPREFERENCETVFNAME + '(' + @MAILINGPREFERENCETVFPARAMETERVALUES + ')')
set @MAILINGPREFERENCEBOTHEXCEPTMAILIDSETREGISTERID = null
-- Create ID set record
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATEIDSET]
@MAILINGPREFERENCESMARTQUERYINSTANCEID, --@SMARTQUERYINSTANCEID
@MAILINGPREFERENCETVFNAME, --@TVFNAME
@MAILINGPREFERENCETVFPARAMETERVALUES, --@TVFPARAMETERVALUES
1, --@ISSTATIC
1, --@USEINQUERYDESIGNER
null, --@NUMROWS
@CHANGEAGENTID,
@MAILINGPREFERENCEBOTHEXCEPTMAILIDSETREGISTERID output
if @MAILINGPREFERENCEBOTHEXCEPTMAILIDSETREGISTERID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEIDSETREGISTER', 13, 1)
return 1
end
-- 2.0.3 This selection is used when there is only mail package selected
set @MAILINGPREFERENCESMARTQUERYNAME = null;
set @MAILINGPREFERENCESMARTQUERYNAME = @NAME + ' - Mailing Preference - Both (Exclude email only)';
if exists (select ID from dbo.SMARTQUERYINSTANCE
where NAME = @MAILINGPREFERENCESMARTQUERYNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1);
return 1;
end
set @MAILINGPREFERENCESMARTQUERYINSTANCEID = null;
set @MAILINGPREFERENCESMARTQUERYINSTANCEID = newid();
set @MAILINGPREFERENCEDATAFORMITEMFILTERXML = '
<DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
<Values>
<fv ID="MEMBERSHIPPROGRAMID">
<Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + '</Value>
</fv>
<fv ID="MAILINGPREFERENCE" >
<Value xsi:type="xsd:string">0</Value>
</fv>
<fv ID="INCLUDENOPREFERENCE">
<Value xsi:type="xsd:boolean">true</Value>
</fv>
<fv ID="EXCLUDEPREFERENCE">
<Value xsi:type="xsd:string">0</Value>
</fv>
</Values>
</DataFormItem>
';
set @MAILINGPREFERENCETVFPARAMETERVALUES = 'N''' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + ''' /*@MEMBERSHIPPROGRAMID*/,
0 /*@MAILINGPREFERENCE*/,
1 /*@INCLUDENOPREFERENCE*/,
0 /*@EXCLUDEPREFERENCE*/,
''' + convert(nvarchar(50), @CURRENTAPPUSERID) + ''' /*@CURRENTAPPUSERID*/,
2147483647 /*@MAXROWS*/
'
-- Create smart query instance
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATE]
@MAILINGPREFERENCESMARTQUERYINSTANCEID, --@ID
@MAILINGPREFERENCESMARTQUERYCATALOGID, --@SMARTQUERYCATALOGID
@MAILINGPREFERENCESMARTQUERYNAME, --@NAME
@MAILINGPREFERENCESMARTQUERYNAME, --@DESCRIPTION
null, --@QUERYCATEGORYCODEID
null, --@DATAFORMINSTANCEID
@MAILINGPREFERENCEDATAFORMITEMFILTERXML, --@DATAFORMITEMFILTERXML
1, --@OTHERSCANMODIFY
@CURRENTAPPUSERID, --@APPUSERID
@CHANGEAGENTID, --@CHANGEAGENTID
@SITEID, --@SITEID
0 --@MOBILIZE
-- Create table for static selection
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATESTATICTABLE]
@MAILINGPREFERENCESMARTQUERYINSTANCEID
-- Insert static selection table
select @PRIMARYKEYFIELD = PRIMARYKEYFIELD from SMARTQUERYCATALOG
inner join dbo.SMARTQUERYINSTANCE on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
where SMARTQUERYINSTANCE.ID = @MAILINGPREFERENCESMARTQUERYINSTANCEID;
set @STATICTABLE = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETSTATICTABLENAME(@MAILINGPREFERENCESMARTQUERYINSTANCEID);
exec ('insert into ' + @STATICTABLE +
' select DISTINCT ' + @PRIMARYKEYFIELD + ' ID from dbo.' + @MAILINGPREFERENCETVFNAME + '(' + @MAILINGPREFERENCETVFPARAMETERVALUES + ')')
set @MAILINGPREFERENCEBOTHEXCEPTEMAILIDSETREGISTERID = null
-- Create ID set record
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATEIDSET]
@MAILINGPREFERENCESMARTQUERYINSTANCEID, --@SMARTQUERYINSTANCEID
@MAILINGPREFERENCETVFNAME, --@TVFNAME
@MAILINGPREFERENCETVFPARAMETERVALUES, --@TVFPARAMETERVALUES
1, --@ISSTATIC
1, --@USEINQUERYDESIGNER
null, --@NUMROWS
@CHANGEAGENTID,
@MAILINGPREFERENCEBOTHEXCEPTEMAILIDSETREGISTERID output
if @MAILINGPREFERENCEBOTHEXCEPTEMAILIDSETREGISTERID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEIDSETREGISTER', 13, 1)
return 1
end
select @SMARTQUERYCATALOGID = ID, @TVFNAME = TVFNAME
from dbo.SMARTQUERYCATALOG
where NAME = 'Membership Smart Query'
declare PACKAGE cursor for
select T.item.value('(SELECTED)[1]','bit'),
T.item.value('(TIMETOEXPIRATION)[1]','integer'),
T.item.value('(MAILPACKAGEID)[1]','uniqueidentifier'),
T.item.value('(EMAILPACKAGEID)[1]','uniqueidentifier')
from @RENEWALNOTICES.nodes('/RENEWALNOTICES/ITEM') T(item)
where T.item.value('(SELECTED)[1]','bit') = 1
open PACKAGE
fetch next from PACKAGE into @SELECTED, @TIMETOEXPIRATION, @MAILPACKAGEID, @EMAILPACKAGEID
while @@fetch_status = 0
begin
-- 2.0 Check if the export definition in the package is membership export definition
if not @MAILPACKAGEID is null
begin
set @EXPORTRECORDTYPEID = null
select @EXPORTRECORDTYPEID = EXPORTDEFINITION.RECORDTYPEID
from dbo.MKTPACKAGE inner join dbo.EXPORTDEFINITION on MKTPACKAGE.EXPORTDEFINITIONID = EXPORTDEFINITION.ID
where MKTPACKAGE.ID = @MAILPACKAGEID
if @EXPORTRECORDTYPEID is null
select @EXPORTRECORDTYPEID = EXPORTDEFINITION.RECORDTYPEID
from dbo.MKTPACKAGE
inner join dbo.LETTERCODE on MKTPACKAGE.LETTERCODEID = LETTERCODE.ID
inner join dbo.EXPORTDEFINITION on LETTERCODE.EXPORTDEFINITIONID = EXPORTDEFINITION.ID
where MKTPACKAGE.ID = @MAILPACKAGEID
if @EXPORTRECORDTYPEID is null
select @EXPORTRECORDTYPEID = EXPORTDEFINITION.RECORDTYPEID
from dbo.MKTPACKAGE
inner join dbo.EXPORTDEFINITION on dbo.UFN_MKTEXPORTDEFINITION_GETIDFROMNETCOMMUNITYDATASOURCEID(MKTPACKAGE.NETCOMMUNITYDATASOURCEID) = EXPORTDEFINITION.ID
where MKTPACKAGE.ID = @MAILPACKAGEID
if (not @EXPORTRECORDTYPEID is null ) and dbo.UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPEID(@EXPORTRECORDTYPEID) <> 2
begin
set @PACKAGEEXPORTDEFINITIONNOTFORMEMBERSHIP = 'The mail package for "' + case @TIMETOEXPIRATION
when 0 then '3 months before expiration'
when 1 then '2 months before expiration'
when 2 then '1 month before expiration'
when 3 then 'Month of expiration'
when 4 then '1 month after expiration'
when 5 then '2 months after expiration'
when 6 then '3 months after expiration'
end + '" contains an invalid export definition. Please select a new package, or edit the current package to include a membership renewal export definition.'
raiserror(@PACKAGEEXPORTDEFINITIONNOTFORMEMBERSHIP, 13, 1)
return 1
end
end
if not @EMAILPACKAGEID is null
begin
set @EXPORTRECORDTYPEID = null
select @EXPORTRECORDTYPEID = EXPORTDEFINITION.RECORDTYPEID
from dbo.MKTPACKAGE inner join dbo.EXPORTDEFINITION on MKTPACKAGE.EXPORTDEFINITIONID = EXPORTDEFINITION.ID
where MKTPACKAGE.ID = @EMAILPACKAGEID
if @EXPORTRECORDTYPEID is null
select @EXPORTRECORDTYPEID = EXPORTDEFINITION.RECORDTYPEID
from dbo.MKTPACKAGE
inner join dbo.LETTERCODE on MKTPACKAGE.LETTERCODEID = LETTERCODE.ID
inner join dbo.EXPORTDEFINITION on LETTERCODE.EXPORTDEFINITIONID = EXPORTDEFINITION.ID
where MKTPACKAGE.ID = @EMAILPACKAGEID
if @EXPORTRECORDTYPEID is null
select @EXPORTRECORDTYPEID = EXPORTDEFINITION.RECORDTYPEID
from dbo.MKTPACKAGE
inner join dbo.EXPORTDEFINITION on dbo.UFN_MKTEXPORTDEFINITION_GETIDFROMNETCOMMUNITYDATASOURCEID(MKTPACKAGE.NETCOMMUNITYDATASOURCEID) = EXPORTDEFINITION.ID
where MKTPACKAGE.ID = @EMAILPACKAGEID
if (not @EXPORTRECORDTYPEID is null ) and dbo.UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPEID(@EXPORTRECORDTYPEID) <> 2
begin
set @PACKAGEEXPORTDEFINITIONNOTFORMEMBERSHIP = 'The email package of "' + case @TIMETOEXPIRATION
when 0 then '3 months before expiration'
when 1 then '2 months before expiration'
when 2 then '1 month before expiration'
when 3 then 'Month of expiration'
when 4 then '1 month after expiration'
when 5 then '2 months after expiration'
when 6 then '3 months after expiration'
end + '" contains an invalid export definition. Please select a new package, or edit the current package to include a membership renewal export definition.'
raiserror(@PACKAGEEXPORTDEFINITIONNOTFORMEMBERSHIP, 13, 1)
return 1
end
end
-- 2.1 Create smart query (static) selections
set @SMARTQUERYNAME = null
set @SMARTQUERYNAME = @NAME + ' - ' + case @TIMETOEXPIRATION
when 0 then '3 months before expiration'
when 1 then '2 months before expiration'
when 2 then '1 month before expiration'
when 3 then 'Month of expiration'
when 4 then '1 month after expiration'
when 5 then '2 months after expiration'
when 6 then '3 months after expiration'
end
if exists (select ID from dbo.SMARTQUERYINSTANCE
where NAME = @SMARTQUERYNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
set @SMARTQUERYINSTANCEID = newid()
set @DATAFORMITEMFILTERXML = '
<DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
<Values>
<fv ID="TIERCODEID" />
<fv ID="MEMBERSHIPLEVELID" />
<fv ID="STATUS" />
<fv ID="MEMBERSHIPLEVELTYPECODEID" />
<fv ID="MEMBERSHIPPROGRAMID">
<Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + '</Value>
</fv>
<fv ID="CHECKEXPIRATIONDATE">
<Value xsi:type="xsd:boolean">true</Value>
</fv>
<fv ID="MONTHSTOEXPIRATION">
<Value xsi:type="xsd:int">' + case @TIMETOEXPIRATION
when 0 then '3'
when 1 then '2'
when 2 then '1'
when 3 then '0'
when 4 then '-1'
when 5 then '-2'
when 6 then '-3'
end + '</Value>
</fv>
</Values>
</DataFormItem>
'
set @TVFPARAMETERVALUES = 'N''' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + ''' /*@MEMBERSHIPPROGRAMID*/,
null /*@MEMBERSHIPLEVELID*/,
null /*@STATUS*/,
null /*@MEMBERSHIPLEVELTYPECODEID*/,
null /*@TIERCODEID*/,
1 /*@CHECKEXPIRATIONDATE*/,
' + case @TIMETOEXPIRATION
when 0 then '3'
when 1 then '2'
when 2 then '1'
when 3 then '0'
when 4 then '-1'
when 5 then '-2'
when 6 then '-3'
end + '/*@MONTHSTOEXPIRATION*/,
''' + convert(nvarchar(50), @CURRENTAPPUSERID) + ''' /*@CURRENTAPPUSERID*/,
2147483647 /*@MAXROWS*/
'
-- Create smart query instance
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATE]
@SMARTQUERYINSTANCEID, --@ID
@SMARTQUERYCATALOGID, --@SMARTQUERYCATALOGID
@SMARTQUERYNAME, --@NAME
@SMARTQUERYNAME, --@DESCRIPTION
null, --@QUERYCATEGORYCODEID
null, --@DATAFORMINSTANCEID
@DATAFORMITEMFILTERXML, --@DATAFORMITEMFILTERXML
1, --@OTHERSCANMODIFY
@CURRENTAPPUSERID, --@APPUSERID
@CHANGEAGENTID, --@CHANGEAGENTID
@SITEID, --@SITEID
0 --@MOBILIZE
-- Create table for static selection
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATESTATICTABLE]
@SMARTQUERYINSTANCEID
-- Insert static selection table
select @PRIMARYKEYFIELD = PRIMARYKEYFIELD from SMARTQUERYCATALOG
inner join dbo.SMARTQUERYINSTANCE on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
where SMARTQUERYINSTANCE.ID = @SMARTQUERYINSTANCEID;
set @STATICTABLE = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETSTATICTABLENAME(@SMARTQUERYINSTANCEID);
exec ('insert into ' + @STATICTABLE +
' select DISTINCT ' + @PRIMARYKEYFIELD + ' ID from dbo.' + @TVFNAME + '(' + @TVFPARAMETERVALUES + ')')
set @IDSETREGISTERID = null
-- Create ID set record
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATEIDSET]
@SMARTQUERYINSTANCEID, --@SMARTQUERYINSTANCEID
@TVFNAME, --@TVFNAME
@TVFPARAMETERVALUES, --@TVFPARAMETERVALUES
1, --@ISSTATIC
1, --@USEINQUERYDESIGNER
null, --@NUMROWS
@CHANGEAGENTID,
@IDSETREGISTERID output
if @IDSETREGISTERID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEIDSETREGISTER', 13, 1)
return 1
end
-- 2.2 Create segments and rules
if dbo.[UFN_MKTSELECTION_SMARTQUERIESEXIST](3) = 0
begin
-- No membership-based smart queries exist
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_NOSMARTQUERIESEXIST', 13, 1);
return 1;
end
insert into @T1
exec dbo.[USP_SIMPLEDATALIST_MKTRECORDSSOURCE] @CURRENTAPPUSERID;
select @QUERYVIEWCATALOGID = [VALUE]
from @T1
if @DEFAULTCHANNEL = 1
begin
-- Create email segment first, then mail segment
if @EMAILPACKAGEID is not null
begin
if @MAILPACKAGEID is not null
set @SELECTIONS = (select SELECTIONID from (select @IDSETREGISTERID as SELECTIONID
union select @MAILINGPREFERENCEEMAILIDSETREGISTERID) as SELECTIONS
for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64)
else
set @SELECTIONS = (select SELECTIONID from (select @IDSETREGISTERID as SELECTIONID
union select @MAILINGPREFERENCEBOTHEXCEPTMAILIDSETREGISTERID) as SELECTIONS
for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64)
-- Create segment
set @SEGMENTID = newid()
set @SEGMENTNAME = @NAME + ' - ' + case @TIMETOEXPIRATION
when 0 then '3 months before expiration'
when 1 then '2 months before expiration'
when 2 then '1 month before expiration'
when 3 then 'Month of expiration'
when 4 then '1 month after expiration'
when 5 then '2 months after expiration'
when 6 then '3 months after expiration'
end + ' (email)'
if exists (select ID from dbo.MKTSEGMENT
where NAME = @SEGMENTNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTMEMBERSHIP]
@SEGMENTID output,
@CHANGEAGENTID,
@QUERYVIEWCATALOGID,
@SEGMENTNAME,
'',
'',
@SELECTIONS,
null,
null,
null
if @SEGMENTID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATESEGMENT', 13, 1)
return 1
end
-- Create rules
set @RULEID = null
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTMEMBERSHIPMAILINGTEMPLATERULE]
@RULEID output,
@CHANGEAGENTID,
@ID, --@MEMBERSHIPMAILINGTEMPLATEID
@SEGMENTID, --@SEGMENTID
'', --@CODEID
'', --@TESTSEGMENTCODE
@EMAILPACKAGEID,--@PACKAGEID
'', --@PACKAGECODE
0, --@RESPONSERATE
0, --@GIFTAMOUNT
null, --@ASKLADDERID
1, --@OVERRIDEADDRESSPROCESSING
1, --@USEADDRESSPROCESSING
'12825E0C-DFC4-4C1A-A512-E26194EB6237', --@ADDRESSPROCESSINGOPTIONID
0, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@CURRENTDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
'4AFC6309-E26A-4012-95C8-8A59EE751EE5', --@NAMEFORMATPARAMETERID
null, --@CODEVALUEID
null, --@PACKAGECODEVALUEID
null, --@TESTSEGMENTCODEVALUEID
'', --@CHANNELSOURCECODE
null --@CHANNELSOURCECODEVALUEID
if @RULEID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATERULE', 13, 1)
return 1
end
end
if @MAILPACKAGEID is not null
begin
if @EMAILPACKAGEID is not null
set @SELECTIONS = (select SELECTIONID from (select @IDSETREGISTERID as SELECTIONID
union select @MAILINGPREFERENCEMAILIDSETREGISTERID) as SELECTIONS
for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64)
else
set @SELECTIONS = (select SELECTIONID from (select @IDSETREGISTERID as SELECTIONID
union select @MAILINGPREFERENCEBOTHEXCEPTMAILIDSETREGISTERID) as SELECTIONS
for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64)
-- Create segment
set @SEGMENTID = newid()
set @SEGMENTNAME = @NAME + ' - ' + case @TIMETOEXPIRATION
when 0 then '3 months before expiration'
when 1 then '2 months before expiration'
when 2 then '1 month before expiration'
when 3 then 'Month of expiration'
when 4 then '1 month after expiration'
when 5 then '2 months after expiration'
when 6 then '3 months after expiration'
end + ' (mail)'
if exists (select ID from dbo.MKTSEGMENT
where NAME = @SEGMENTNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTMEMBERSHIP]
@SEGMENTID output,
@CHANGEAGENTID,
@QUERYVIEWCATALOGID,
@SEGMENTNAME,
'',
'',
@SELECTIONS,
null,
null,
null
if @SEGMENTID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATESEGMENT', 13, 1)
return 1
end
-- Create rules
set @RULEID = null
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTMEMBERSHIPMAILINGTEMPLATERULE]
@RULEID output,
@CHANGEAGENTID,
@ID, --@MEMBERSHIPMAILINGTEMPLATEID
@SEGMENTID, --@SEGMENTID
'', --@CODEID
'', --@TESTSEGMENTCODE
@MAILPACKAGEID, --@PACKAGEID
'', --@PACKAGECODE
0, --@RESPONSERATE
0, --@GIFTAMOUNT
null, --@ASKLADDERID
1, --@OVERRIDEADDRESSPROCESSING
1, --@USEADDRESSPROCESSING
'12825E0C-DFC4-4C1A-A512-E26194EB6237', --@ADDRESSPROCESSINGOPTIONID
0, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@CURRENTDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
'4AFC6309-E26A-4012-95C8-8A59EE751EE5', --@NAMEFORMATPARAMETERID
null, --@CODEVALUEID
null, --@PACKAGECODEVALUEID
null, --@TESTSEGMENTCODEVALUEID
'', --@CHANNELSOURCECODE
null --@CHANNELSOURCECODEVALUEID
if @RULEID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATERULE', 13, 1)
return 1
end
end
end
else
begin
-- Create mail segment first, then email segment
if @MAILPACKAGEID is not null
begin
if @EMAILPACKAGEID is not null
set @SELECTIONS = (select SELECTIONID from (select @IDSETREGISTERID as SELECTIONID
union select @MAILINGPREFERENCEMAILIDSETREGISTERID) as SELECTIONS
for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64)
else
set @SELECTIONS = (select SELECTIONID from (select @IDSETREGISTERID as SELECTIONID
union select @MAILINGPREFERENCEBOTHEXCEPTEMAILIDSETREGISTERID) as SELECTIONS
for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64)
-- Create segment
set @SEGMENTID = newid()
set @SEGMENTNAME = @NAME + ' - ' + case @TIMETOEXPIRATION
when 0 then '3 months before expiration'
when 1 then '2 months before expiration'
when 2 then '1 month before expiration'
when 3 then 'Month of expiration'
when 4 then '1 month after expiration'
when 5 then '2 months after expiration'
when 6 then '3 months after expiration'
end + ' (mail)'
if exists (select ID from dbo.MKTSEGMENT
where NAME = @SEGMENTNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTMEMBERSHIP]
@SEGMENTID output,
@CHANGEAGENTID,
@QUERYVIEWCATALOGID,
@SEGMENTNAME,
'',
'',
@SELECTIONS,
null,
null,
null
if @SEGMENTID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATESEGMENT', 13, 1)
return 1
end
-- Create rules
set @RULEID = null
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTMEMBERSHIPMAILINGTEMPLATERULE]
@RULEID output,
@CHANGEAGENTID,
@ID, --@MEMBERSHIPMAILINGTEMPLATEID
@SEGMENTID, --@SEGMENTID
'', --@CODEID
'', --@TESTSEGMENTCODE
@MAILPACKAGEID, --@PACKAGEID
'', --@PACKAGECODE
0, --@RESPONSERATE
0, --@GIFTAMOUNT
null, --@ASKLADDERID
1, --@OVERRIDEADDRESSPROCESSING
1, --@USEADDRESSPROCESSING
'12825E0C-DFC4-4C1A-A512-E26194EB6237', --@ADDRESSPROCESSINGOPTIONID
0, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@CURRENTDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
'4AFC6309-E26A-4012-95C8-8A59EE751EE5', --@NAMEFORMATPARAMETERID
null, --@CODEVALUEID
null, --@PACKAGECODEVALUEID
null, --@TESTSEGMENTCODEVALUEID
'', --@CHANNELSOURCECODE
null --@CHANNELSOURCECODEVALUEID
if @RULEID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATERULE', 13, 1)
return 1
end
end
if @EMAILPACKAGEID is not null
begin
if @MAILPACKAGEID is not null
set @SELECTIONS = (select SELECTIONID from (select @IDSETREGISTERID as SELECTIONID
union select @MAILINGPREFERENCEEMAILIDSETREGISTERID) as SELECTIONS
for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64)
else
set @SELECTIONS = (select SELECTIONID from (select @IDSETREGISTERID as SELECTIONID
union select @MAILINGPREFERENCEBOTHEXCEPTMAILIDSETREGISTERID) as SELECTIONS
for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64)
-- Create segment
set @SEGMENTID = newid()
set @SEGMENTNAME = @NAME + ' - ' + case @TIMETOEXPIRATION
when 0 then '3 months before expiration'
when 1 then '2 months before expiration'
when 2 then '1 month before expiration'
when 3 then 'Month of expiration'
when 4 then '1 month after expiration'
when 5 then '2 months after expiration'
when 6 then '3 months after expiration'
end + ' (email)'
if exists (select ID from dbo.MKTSEGMENT
where NAME = @SEGMENTNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTMEMBERSHIP]
@SEGMENTID output,
@CHANGEAGENTID,
@QUERYVIEWCATALOGID,
@SEGMENTNAME,
'',
'',
@SELECTIONS,
null,
null,
null
if @SEGMENTID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATESEGMENT', 13, 1)
return 1
end
-- Create rules
set @RULEID = null
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTMEMBERSHIPMAILINGTEMPLATERULE]
@RULEID output,
@CHANGEAGENTID,
@ID, --@MEMBERSHIPMAILINGTEMPLATEID
@SEGMENTID, --@SEGMENTID
'', --@CODEID
'', --@TESTSEGMENTCODE
@EMAILPACKAGEID,--@PACKAGEID
'', --@PACKAGECODE
0, --@RESPONSERATE
0, --@GIFTAMOUNT
null, --@ASKLADDERID
1, --@OVERRIDEADDRESSPROCESSING
1, --@USEADDRESSPROCESSING
'12825E0C-DFC4-4C1A-A512-E26194EB6237', --@ADDRESSPROCESSINGOPTIONID
0, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE
@CURRENTDATE, --@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE
'4AFC6309-E26A-4012-95C8-8A59EE751EE5', --@NAMEFORMATPARAMETERID
null, --@CODEVALUEID
null, --@PACKAGECODEVALUEID
null, --@TESTSEGMENTCODEVALUEID
'', --@CHANNELSOURCECODE
null --@CHANNELSOURCECODEVALUEID
if @RULEID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATERULE', 13, 1)
return 1
end
end
end
fetch next from PACKAGE into @SELECTED, @TIMETOEXPIRATION, @MAILPACKAGEID, @EMAILPACKAGEID
end
close PACKAGE
deallocate PACKAGE
-- 3. Create universe if not all the membership program levels are selected
if exists (select T.item.value('(ID)[1]','uniqueidentifier')
from @MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(item)
where T.item.value('(SELECTED)[1]','bit') = 0)
begin
declare LEVELS cursor for
select T.item.value('(ID)[1]','uniqueidentifier'), T.item.value('(NAME)[1]','nvarchar(50)')
from @MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(item)
where T.item.value('(SELECTED)[1]','bit') = 1
open LEVELS
fetch next from LEVELS into @LEVELID, @LEVELNAME
while @@fetch_status = 0
begin
-- 3.1 Create smart query selections
set @SMARTQUERYNAME = null
set @SMARTQUERYNAME = @NAME + ' - ' + @LEVELNAME + ' level'
if exists (select ID from dbo.SMARTQUERYINSTANCE
where NAME = @SMARTQUERYNAME)
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_DUPLICATENAME', 13, 1)
return 1
end
set @SMARTQUERYINSTANCEID = newid()
set @DATAFORMITEMFILTERXML = '
<DataFormItem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="bb_appfx_dataforms">
<Values>
<fv ID="TIERCODEID" />
<fv ID="MEMBERSHIPLEVELID">
<Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">' + convert(nvarchar(50), @LEVELID) + '</Value>
</fv>
<fv ID="STATUS" />
<fv ID="MEMBERSHIPLEVELTYPECODEID" />
<fv ID="MEMBERSHIPPROGRAMID">
<Value xmlns:q1="http://microsoft.com/wsdl/types/" xsi:type="q1:guid">' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + '</Value>
</fv>
<fv ID="CHECKEXPIRATIONDATE">
<Value xsi:type="xsd:boolean">false</Value>
</fv>
<fv ID="MONTHSTOEXPIRATION" />
</Values>
</DataFormItem>
'
set @TVFPARAMETERVALUES = 'N''' + convert(nvarchar(50), @MEMBERSHIPPROGRAMID) + ''' /*@MEMBERSHIPPROGRAMID*/,
''' + convert(nvarchar(50), @LEVELID) + ''' /*@MEMBERSHIPLEVELID*/,
null /*@STATUS*/,
null /*@MEMBERSHIPLEVELTYPECODEID*/,
null /*@TIERCODEID*/,
0 /*@CHECKEXPIRATIONDATE*/,
null /*@MONTHSTOEXPIRATION*/,
''' + convert(nvarchar(50), @CURRENTAPPUSERID) + ''' /*@CURRENTAPPUSERID*/,
2147483647 /*@MAXROWS*/
'
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATE]
@SMARTQUERYINSTANCEID, --@ID
@SMARTQUERYCATALOGID, --@SMARTQUERYCATALOGID
@SMARTQUERYNAME, --@NAME
@SMARTQUERYNAME, --@DESCRIPTION
null, --@QUERYCATEGORYCODEID
null, --@DATAFORMINSTANCEID
@DATAFORMITEMFILTERXML, --@DATAFORMITEMFILTERXML
1, --@OTHERSCANMODIFY
@CURRENTAPPUSERID, --@APPUSERID
@CHANGEAGENTID, --@CHANGEAGENTID
@SITEID, --@SITEID
0 --@MOBILIZE
-- Create table for static selection
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATESTATICTABLE]
@SMARTQUERYINSTANCEID
-- Insert static selection table
select @PRIMARYKEYFIELD = PRIMARYKEYFIELD from SMARTQUERYCATALOG
inner join dbo.SMARTQUERYINSTANCE on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID
where SMARTQUERYINSTANCE.ID = @SMARTQUERYINSTANCEID;
set @STATICTABLE = dbo.UFN_SMARTQUERYINSTANCE_MAKEIDSETSTATICTABLENAME(@SMARTQUERYINSTANCEID);
exec ('insert into ' + @STATICTABLE +
' select DISTINCT ' + @PRIMARYKEYFIELD + ' ID from dbo.' + @TVFNAME + '(' + @TVFPARAMETERVALUES + ')')
set @IDSETREGISTERID = null
exec [dbo].[USP_SMARTQUERYINSTANCE_CREATEORUPDATEIDSET]
@SMARTQUERYINSTANCEID,
@TVFNAME,
@TVFPARAMETERVALUES,
1,
1,
null,
@CHANGEAGENTID,
@IDSETREGISTERID output
if @IDSETREGISTERID is null
begin
raiserror('ERR_MEMBERSHIPRENEWALEFFORT_CREATEIDSETREGISTER', 13, 1)
return 1
end
-- Create filter selection in universe
set @FILTERSELECTION = null
exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTMEMBERSHIPMAILINGTEMPLATEFILTERSELECTION]
@FILTERSELECTION output,
@CHANGEAGENTID,
@ID,
1,
@IDSETREGISTERID
fetch next from LEVELS into @LEVELID, @LEVELNAME
end
close LEVELS
deallocate LEVELS
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0