USP_COMMUNICATIONS_CREATEORUPDATESEGMENT_2
Creates or updates the underlying segment for a generic mailing.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @LETTERID | uniqueidentifier | IN | |
| @LETTERNAME | nvarchar(100) | IN | |
| @SELECTIONS | xml | IN | |
| @EXCLUDEDSELECTIONS | xml | IN | |
| @EXCLUDEDSOLICITCODES | xml | IN | |
| @MAILTYPECODE | tinyint | IN | |
| @DELIVERYMETHODCODE | tinyint | IN | |
| @SINGLECHANNEL | bit | IN | |
| @PREFERREDCHANNEL | bit | IN | |
| @INCLUDEINACTIVE | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_COMMUNICATIONS_CREATEORUPDATESEGMENT_2
(
@ID uniqueidentifier output,
@CURRENTAPPUSERID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@LETTERID uniqueidentifier,
@LETTERNAME nvarchar(100),
@SELECTIONS xml,
@EXCLUDEDSELECTIONS xml,
@EXCLUDEDSOLICITCODES xml,
@MAILTYPECODE tinyint,
@DELIVERYMETHODCODE tinyint,
@SINGLECHANNEL bit,
@PREFERREDCHANNEL bit,
@INCLUDEINACTIVE bit
)
with execute as owner
as
begin
--This procedure creates a segment and a view that unions the selections together.
-- Currently, Appeal Mailings are the only form of communication allowing multiple selections, but this is subject to change
-- @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...
-- DELIVERYMETHODCODE also pertains the corresponding field in dbo.MAILPREFERENCE
-- 0 - Mail
-- 1 - Email
set nocount on;
declare @CURRENTDATE datetime = getDate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
declare @SEGMENTATIONID uniqueidentifier;
declare @COMMUNICATIONTYPECODE tinyint;
select
@SEGMENTATIONID = SEGMENTATIONID,
@COMMUNICATIONTYPECODE = COMMUNICATIONTYPECODE
from dbo.COMMUNICATIONLETTER
where ID = @LETTERID;
declare @SEGMENTTYPE nvarchar(20);
if @DELIVERYMETHODCODE = 1
set @SEGMENTTYPE = 'Email';
else
set @SEGMENTTYPE = 'Mail';
declare @UPDATE bit = 1;
if @ID is null or @ID = '00000000-0000-0000-0000-000000000000'
begin
set @ID = newId();
set @UPDATE = 0;
end
declare @BBECRECORDSOURCEID uniqueidentifier;
set @BBECRECORDSOURCEID = dbo.UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID();
declare @MKTSEGMENTNAME nvarchar(100) = dbo.UFN_MKTSEGMENT_GETUNIQUENAME(@ID, @SEGMENTTYPE + ' Segment: ' + @LETTERNAME + ' (' + convert(nvarchar(36), @LETTERID) + ')', null);
declare @MKTSEGMENTDESCRIPTION nvarchar(255) = 'Automatically generated ' + lower(@SEGMENTTYPE) + ' segment for ''' + @LETTERNAME + ''' appeal mailing setup letter.';
-- Save the segment
if @UPDATE = 0
begin
-- Save the segment
insert into dbo.MKTSEGMENT
(
ID,
NAME,
DESCRIPTION,
SEGMENTTYPECODE,
SEGMENTCATEGORYCODEID,
CODE,
QUERYVIEWCATALOGID,
PARTDEFINITIONVALUESID,
ISSYSTEM,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@MKTSEGMENTNAME,
@MKTSEGMENTDESCRIPTION,
case when @MAILTYPECODE in (0, 4, 5, 6, 7) then 3 else 1 end,
null,
'',
@BBECRECORDSOURCEID,
null,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
-- Insert a parameter set for the refresh process for this segment
insert into dbo.MKTSEGMENTREFRESHPROCESS
(
ID,
SEGMENTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
-- Save all the selections
exec dbo.USP_MKTSEGMENT_GETSELECTIONS_ADDFROMXML @ID, null, @SELECTIONS, @CHANGEAGENTID, @CURRENTDATE;
end
else
begin
update dbo.MKTSEGMENT
set
NAME = @MKTSEGMENTNAME,
DESCRIPTION = @MKTSEGMENTDESCRIPTION,
ISSYSTEM = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
-- Save all the selections
exec dbo.USP_MKTSEGMENT_GETSELECTIONS_UPDATEFROMXML @ID, null, @SELECTIONS, @CHANGEAGENTID, @CURRENTDATE;
end
-- Create the VIEW and add it to the IDSETREGISTER
declare @SEGSQL nvarchar(max);
declare @EXCLUDESQL nvarchar(max);
declare @VIEWSQL nvarchar(max);
declare @SEGMENTVIEW nvarchar(60);
declare @TABLECOUNT int;
declare @BASETABLE nvarchar(255);
declare @UNIONTABLE nvarchar(255);
declare @SELECTIONNAME nvarchar(300);
declare @SEGMENTNAME nvarchar(100);
declare @SEGMENTDESCRIPTION nvarchar(255);
declare @DATATYPE nvarchar(128);
declare @SELECTIONID uniqueidentifier;
declare @IDSETREGISTERID uniqueidentifier;
declare @RECORDTYPEID uniqueidentifier;
declare @RETVAL int;
set @TABLECOUNT = 1;
set @BASETABLE = null;
-- Get the segment information
select
@SEGMENTVIEW = dbo.UFN_MKTSEGMENT_MAKEVIEWNAME(MKTSEGMENT.ID),
@SEGMENTNAME = MKTSEGMENT.NAME,
@SEGMENTDESCRIPTION = MKTSEGMENT.DESCRIPTION,
@DATATYPE = QUERYVIEWCATALOG.PRIMARYKEYTYPENAME,
@RECORDTYPEID = QUERYVIEWCATALOG.RECORDTYPEID
from dbo.MKTSEGMENT
inner join dbo.QUERYVIEWCATALOG
on QUERYVIEWCATALOG.ID = MKTSEGMENT.QUERYVIEWCATALOGID
where MKTSEGMENT.ID = @ID;
-- Selections included in Segment
declare SELECTIONCURSOR cursor local fast_forward for
select
MKTSEGMENTSELECTION.SELECTIONID
from dbo.MKTSEGMENTSELECTION
inner join dbo.IDSETREGISTER on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
where MKTSEGMENTSELECTION.SEGMENTID = @ID;
open SELECTIONCURSOR;
fetch next from SELECTIONCURSOR into @SELECTIONID;
while (@@FETCH_STATUS = 0)
begin
--Remove the CR and LF characters since we are inserting this name into a dash-dash (--) comment in the SQL we are building...
select
@SELECTIONNAME = replace(replace(NAME, char(13), ''), char(10), '')
from dbo.IDSETREGISTER
where ID = @SELECTIONID;
if @BASETABLE is null
begin
set @BASETABLE = 'T' + convert(nvarchar(10), @TABLECOUNT) + '';
set @SEGSQL = 'select '+ @BASETABLE +'.ID from dbo.' + dbo.UFN_MKTSELECTION_GETFUNCTIONNAME(@SELECTIONID) + ' as ' + @BASETABLE + ' --' + @SELECTIONNAME + char(13);
end
else
begin
set @UNIONTABLE = 'T' + convert(nvarchar(10), @TABLECOUNT) + '';
set @SEGSQL = isnull(@SEGSQL,'') + 'union select '+ @UNIONTABLE +'.ID from dbo.' + dbo.UFN_MKTSELECTION_GETFUNCTIONNAME(@SELECTIONID) + ' as ' + @UNIONTABLE + ' --' + @SELECTIONNAME + char(13);
end
set @TABLECOUNT = @TABLECOUNT + 1;
fetch next from SELECTIONCURSOR into @SELECTIONID;
end;
close SELECTIONCURSOR;
deallocate SELECTIONCURSOR;
-- Build Exclusions SQL
set @TABLECOUNT = 1;
-- Required Solicit codes
set @EXCLUDESQL = 'select ' + char(13) +
' distinct CONSTITUENTID as ID ' + char(13) +
'from dbo.CONSTITUENTSOLICITCODE ' + char(13) +
'inner join dbo.SOLICITCODE ' + char(13) +
' on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID ' + char(13) +
'where SOLICITCODE.EXCLUSIONCODE = 2 -- Required Solicit codes' + char(13);
-- Default and optional solicit codes
declare @SOLICITCODEID uniqueidentifier;
declare @SOLICITCODENAME nvarchar(100);
declare EXCLUDEDSOLICITCODECURSOR cursor local fast_forward for
select
EXCLUDEDSOLICITCODES.SOLICITCODEID,
--Remove the CR and LF characters since we are inserting this name into a dash-dash (--) comment in the SQL we are building...
replace(replace(SOLICITCODE.DESCRIPTION, char(13), ''), char(10), '')
from dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSOLICITCODES_FROMITEMLISTXML(@EXCLUDEDSOLICITCODES) EXCLUDEDSOLICITCODES
inner join dbo.SOLICITCODE
on EXCLUDEDSOLICITCODES.SOLICITCODEID = SOLICITCODE.ID;
open EXCLUDEDSOLICITCODECURSOR;
fetch next from EXCLUDEDSOLICITCODECURSOR into @SOLICITCODEID, @SOLICITCODENAME;
declare @SOLICITCODEIDLIST nvarchar(max) = '';
while (@@FETCH_STATUS = 0)
begin
if len(@SOLICITCODEIDLIST) > 0
set @SOLICITCODEIDLIST = @SOLICITCODEIDLIST + ', ';
set @SOLICITCODEIDLIST = @SOLICITCODEIDLIST + '''' + convert(nvarchar(36), @SOLICITCODEID) + '''';
fetch next from EXCLUDEDSOLICITCODECURSOR into @SOLICITCODEID, @SOLICITCODENAME;
end;
close EXCLUDEDSOLICITCODECURSOR;
deallocate EXCLUDEDSOLICITCODECURSOR;
if len(@SOLICITCODEIDLIST) > 0
set @EXCLUDESQL = @EXCLUDESQL +
' or SOLICITCODE.ID in (' + @SOLICITCODEIDLIST + ')' + char(13);
set @EXCLUDESQL = @EXCLUDESQL +
' and ((CONSTITUENTSOLICITCODE.STARTDATE is null and CONSTITUENTSOLICITCODE.ENDDATE is null) or ((CONSTITUENTSOLICITCODE.STARTDATE is null or CONSTITUENTSOLICITCODE.STARTDATE <= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE())) and (CONSTITUENTSOLICITCODE.ENDDATE is null or CONSTITUENTSOLICITCODE.ENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE()))))' + char(13);
-- Excluded solicit codes for Acknowledgements
if @COMMUNICATIONTYPECODE = 3
set @EXCLUDESQL = isnull(@EXCLUDESQL, '')
+ 'union select distinct CONSTITUENTSOLICITCODE.CONSTITUENTID as ID from dbo.SIMPLEACKNOWLEDGEMENTSOLICITCODES inner join dbo.CONSTITUENTSOLICITCODE on SIMPLEACKNOWLEDGEMENTSOLICITCODES.SOLICITCODEID = CONSTITUENTSOLICITCODE.SOLICITCODEID where SIMPLEACKNOWLEDGEMENTSOLICITCODES.SEGMENTATIONID = ''' + convert(nvarchar(36), @SEGMENTATIONID) + ''' '
+ 'and ((CONSTITUENTSOLICITCODE.STARTDATE is null and CONSTITUENTSOLICITCODE.ENDDATE is null) or ((CONSTITUENTSOLICITCODE.STARTDATE is null or CONSTITUENTSOLICITCODE.STARTDATE <= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE())) and (CONSTITUENTSOLICITCODE.ENDDATE is null or CONSTITUENTSOLICITCODE.ENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE()))))';
-- Excluded solicit codes for Reminders
if @COMMUNICATIONTYPECODE = 4
set @EXCLUDESQL = isnull(@EXCLUDESQL, '')
+ 'union select distinct CONSTITUENTSOLICITCODE.CONSTITUENTID as ID from dbo.REMINDEROPTIONSOLICITCODES inner join dbo.CONSTITUENTSOLICITCODE on REMINDEROPTIONSOLICITCODES.SOLICITCODEID = CONSTITUENTSOLICITCODE.SOLICITCODEID where REMINDEROPTIONSOLICITCODES.SEGMENTATIONID = ''' + convert(nvarchar(36), @SEGMENTATIONID) + ''' '
+ 'and ((CONSTITUENTSOLICITCODE.STARTDATE is null and CONSTITUENTSOLICITCODE.ENDDATE is null) or ((CONSTITUENTSOLICITCODE.STARTDATE is null or CONSTITUENTSOLICITCODE.STARTDATE <= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE())) and (CONSTITUENTSOLICITCODE.ENDDATE is null or CONSTITUENTSOLICITCODE.ENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE()))))';
-- Selections excluded from Segment
declare EXCLUDEDSELECTIONCURSOR cursor local fast_forward for
select
EXCLUDEDSELECTIONS.SELECTIONID,
--Remove the CR and LF characters since we are inserting this name into a dash-dash (--) comment in the SQL we are building...
replace(replace(IDSETREGISTER.NAME, char(13), ''), char(10), '')
from dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSELECTIONS_FROMITEMLISTXML(@EXCLUDEDSELECTIONS) EXCLUDEDSELECTIONS
inner join dbo.IDSETREGISTER
on EXCLUDEDSELECTIONS.SELECTIONID = IDSETREGISTER.ID;
open EXCLUDEDSELECTIONCURSOR;
fetch next from EXCLUDEDSELECTIONCURSOR into @SELECTIONID, @SELECTIONNAME;
while (@@FETCH_STATUS = 0)
begin
set @UNIONTABLE = 'T' + convert(nvarchar(10), @TABLECOUNT) + '';
set @EXCLUDESQL = isnull(@EXCLUDESQL,'') + 'union select '+ @UNIONTABLE +'.ID from dbo.' + dbo.UFN_MKTSELECTION_GETFUNCTIONNAME(@SELECTIONID) + ' as ' + @UNIONTABLE + ' --' + @SELECTIONNAME + char(13);
set @TABLECOUNT = @TABLECOUNT + 1;
fetch next from EXCLUDEDSELECTIONCURSOR into @SELECTIONID, @SELECTIONNAME;
end;
close EXCLUDEDSELECTIONCURSOR;
deallocate EXCLUDEDSELECTIONCURSOR;
-- Create or alter the segment view
declare @OBJID int;
select @OBJID = object_id(@SEGMENTVIEW, N'V');
if @OBJID is null
set @VIEWSQL = 'create';
else
set @VIEWSQL = 'alter';
set @VIEWSQL = @VIEWSQL + ' view dbo.' + @SEGMENTVIEW + '' + char(13) +
'as' + char(13);
if @BASETABLE is null
set @VIEWSQL = @VIEWSQL + 'select top(0) cast(null as ' + @DATATYPE + ') as ID';
else
begin
set @SEGSQL = 'with SelectionUnion as (' + char(13)
+ @SEGSQL
+ '), ' + char(13)
+ 'ExclusionUnion as (' + char(13)
+ @EXCLUDESQL
+ '), ' + char(13)
if @MAILTYPECODE = 1
begin
set @SEGSQL = @SEGSQL
+ 'ParameterSet as (' + char(13)
+ ' select ' + char(13)
+ ' APPEAL.APPEALCATEGORYCODEID ID ' + char(13)
+ ' from dbo.COMMUNICATIONLETTER ' + char(13)
+ ' inner join dbo.APPEALMAILING ' + char(13)
+ ' on COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILING.ID ' + char(13)
+ ' inner join dbo.APPEAL ' + char(13)
+ ' on APPEALMAILING.APPEALID = APPEAL.ID ' + char(13)
+ ' where COMMUNICATIONLETTER.ID = ''' + cast(@LETTERID as nvarchar(36)) + '''' + char(13)
+ '), ' + char(13);
end
set @SEGSQL = @SEGSQL
+ 'MailPreferences as (' + char(13)
+ ' select ' + char(13)
+ ' CONSTITUENTID, ' + char(13)
+ ' DELIVERYMETHODCODE, ' + char(13)
+ ' SENDMAIL, ' + char(13)
+ ' DONOTSENDOTHERCHANNEL, ' + char(13);
-- Join to best match MailPreference based on criteria
-- Appeals - Category (Only current concern)
-- Events - Category
-- Receipts - Receipt type (Not yet supported)
declare @PARAMETERSETID uniqueidentifier = null;
if @MAILTYPECODE = 1 -- Appeals
set @SEGSQL = @SEGSQL
+ ' case ' + char(13)
+ ' when CATEGORYCODEID is not null and CATEGORYCODEID = (select ID from ParameterSet) then 1' + char(13)
+ ' when CATEGORYCODEID is null then 0 ' + char(13)
+ ' else -1 ' + char(13)
+ ' end MATCHSCORE ' + char(13);
else
set @SEGSQL = @SEGSQL
+ ' 0 MATCHSCORE ' + char(13);
set @SEGSQL = @SEGSQL
+ ' from dbo.MAILPREFERENCE ' + char(13)
+ ' where MAILPREFERENCE.MAILTYPECODE = ' + cast(@MAILTYPECODE as nvarchar) + ' ' + char(13);
if @MAILTYPECODE = 1
set @SEGSQL = @SEGSQL
+ '),' + char(13)
+ 'BestMatchMailPreference as (' + char(13)
+ ' select * ' + char(13)
+ ' from MailPreferences MP ' + char(13)
+ ' where MP.MATCHSCORE = (' + char(13)
+ ' select ' + char(13)
+ ' max(MATCHSCORE) ' + char(13)
+ ' from MailPreferences ' + char(13)
+ ' where MailPreferences.CONSTITUENTID = MP.CONSTITUENTID ' + char(13)
+ ' group by CONSTITUENTID ' + char(13)
+ ' )' + char(13);
set @SEGSQL = @SEGSQL
+ ') ' + char(13)
+ 'select distinct SelectionUnion.ID ' + char(13)
+ 'from SelectionUnion ' + char(13);
if @COMMUNICATIONTYPECODE = 4 -- Reminder
set @SEGSQL = @SEGSQL
+ 'inner join dbo.FINANCIALTRANSACTION ' + char(13)
+ ' on SelectionUnion.ID = FINANCIALTRANSACTION.ID ' + char(13)
+ ' and FINANCIALTRANSACTION.DELETEDON is null ' + char(13)
+ 'inner join dbo.REVENUESCHEDULE ' + char(13)
+ ' on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID ' + char(13);
if @COMMUNICATIONTYPECODE = 3 -- Acknowledgements
set @SEGSQL = @SEGSQL
+ 'inner join dbo.FINANCIALTRANSACTION ' + char(13)
+ ' on SelectionUnion.ID = FINANCIALTRANSACTION.ID ' + char(13)
+ ' and FINANCIALTRANSACTION.CONSTITUENTID is not null ' + char(13)
+ ' and FINANCIALTRANSACTION.DELETEDON is null ' + char(13)
+ 'inner join dbo.REVENUE_EXT ' + char(13)
+ ' on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID ' + char(13)
+ 'inner join dbo.FINANCIALTRANSACTIONLINEITEM ' + char(13)
+ ' on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID' + char(13)
+ ' and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null' + char(13)
+ 'inner join dbo.UFN_REVENUELIFECYCLE_GETVALIDREVENUE() as VALIDREVENUE ' + char(13)
+ ' on FINANCIALTRANSACTIONLINEITEM.ID = VALIDREVENUE.REVENUESPLITID ' + char(13);
if @INCLUDEINACTIVE = 0
set @SEGSQL = @SEGSQL
+ 'inner join dbo.CONSTITUENT ' + char(13)
+ case
when @COMMUNICATIONTYPECODE = 1 then
+ ' on SelectionUnion.ID = CONSTITUENT.ID ' + char(13)
when @COMMUNICATIONTYPECODE in (3, 4) then
+ ' on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID ' + char(13)
end
+ ' and CONSTITUENT.ISINACTIVE = 0 ' + char(13);
set @SEGSQL = @SEGSQL
+ 'left join ExclusionUnion ' + char(13)
+ case
when @COMMUNICATIONTYPECODE = 1 then
+ ' on SelectionUnion.ID = ExclusionUnion.ID ' + char(13)
when @COMMUNICATIONTYPECODE in (3, 4) then
+ ' on FINANCIALTRANSACTION.CONSTITUENTID = ExclusionUnion.ID ' + char(13)
end;
if @MAILTYPECODE = 1
set @SEGSQL = @SEGSQL
+ 'left join BestMatchMailPreference MAILPREFERENCE ' + char(13)
+ ' on SelectionUnion.ID = MAILPREFERENCE.CONSTITUENTID ' + char(13);
else
set @SEGSQL = @SEGSQL
+ 'left join MailPreferences MAILPREFERENCE ' + char(13)
+ case
when @COMMUNICATIONTYPECODE = 1 then
+ ' on SelectionUnion.ID = MAILPREFERENCE.CONSTITUENTID ' + char(13)
when @COMMUNICATIONTYPECODE in (3, 4) then
+ ' on FINANCIALTRANSACTION.CONSTITUENTID = MAILPREFERENCE.CONSTITUENTID ' + char(13)
end;
set @SEGSQL = @SEGSQL
+ ' and MAILPREFERENCE.MATCHSCORE <> -1 ' + char(13)
+ 'where ExclusionUnion.ID is null ' + char(13);
if @COMMUNICATIONTYPECODE = 4 -- Reminder
begin
set @SEGSQL = @SEGSQL
+ ' and FINANCIALTRANSACTION.TYPECODE in (1, 2) ' + char(13) -- Pledge, Recurring gift
+ ' and REVENUESCHEDULE.SENDPLEDGEREMINDER = 1 and REVENUESCHEDULE.STATUSCODE = 0 ' + char(13)
+ ' and dbo.UFN_REMINDER_PAYMENTDUE(FINANCIALTRANSACTION.ID, getDate()) > 0 ' + char(13);
end
if @COMMUNICATIONTYPECODE = 3 begin -- Acknowledgements
set @SEGSQL = @SEGSQL + ' and FINANCIALTRANSACTION.ID not in (select REVENUEID from dbo.REVENUELETTER) and REVENUE_EXT.DONOTACKNOWLEDGE = 0 '
end
-- Mail or Email only
-- Include constits with no pref, that prefer this channel,
-- or that prefer another channel but are ok with receiving communications on other channels
if @SINGLECHANNEL = 1
set @SEGSQL = @SEGSQL
+ ' and (' + char(13)
+ ' coalesce(MAILPREFERENCE.DELIVERYMETHODCODE, ' + cast(@DELIVERYMETHODCODE as nvarchar) + ') = ' + cast(@DELIVERYMETHODCODE as nvarchar) + ' ' + char(13)
+ ' or ( ' + char(13)
+ ' coalesce(MAILPREFERENCE.DELIVERYMETHODCODE, ' + cast(@DELIVERYMETHODCODE as nvarchar) + ') <> ' + cast(@DELIVERYMETHODCODE as nvarchar) + ' ' + char(13)
+ ' and coalesce(MAILPREFERENCE.DONOTSENDOTHERCHANNEL, 0) = 0 ' + char(13)
+ ' ) ' + char(13)
+ ' ) ';
-- Mail and email, and current channel is preferred
-- Include constits with no pref or that prefer this channel
if @PREFERREDCHANNEL = 1
set @SEGSQL = @SEGSQL
+ ' and coalesce(MAILPREFERENCE.DELIVERYMETHODCODE, ' + cast(@DELIVERYMETHODCODE as nvarchar) + ') = ' + cast(@DELIVERYMETHODCODE as nvarchar) + char(13);
set @SEGSQL = @SEGSQL
+ ' and coalesce(MAILPREFERENCE.SENDMAIL, 1) = 1 ' + char(13);
set @VIEWSQL = @VIEWSQL + @SEGSQL;
end
exec (@VIEWSQL);
-- Grant rights for new views
if @OBJID is null
exec ('grant select on dbo.' + @SEGMENTVIEW + ' to BBAPPFXSERVICEROLE');
-- Add to the ID Set Register
-- Get the ID set ID if this is an edit operation, else it will be null. Also get the segment record type.
select distinct
@IDSETREGISTERID = MKTSEGMENT.IDSETREGISTERID,
@RECORDTYPEID = isnull(IDSETREGISTER.RECORDTYPEID, @RECORDTYPEID)
from dbo.MKTSEGMENT
left join dbo.MKTSEGMENTSELECTION
on MKTSEGMENTSELECTION.SEGMENTID = MKTSEGMENT.ID
left join dbo.IDSETREGISTER
on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
where MKTSEGMENT.ID = @ID;
-- Create the ID set...
set @SELECTIONNAME = 'Selection: ' + @SEGMENTNAME;
declare @SELECTIONDESCRIPTION as nvarchar(255) = 'Automatically generated selection for ' + @SEGMENTNAME;
exec dbo.USP_IDSETREGISTER_CREATEORUPDATE
@IDSETREGISTERID output,
@SELECTIONNAME,
@SELECTIONDESCRIPTION,
@SEGMENTVIEW,
0,
@RECORDTYPEID,
0,
1,
0,
@CHANGEAGENTID;
-- Mark IDSET as inactive to filter from searches
-- Make sure the RECORDTYPEID gets set. The SP doesn't update it on an UPDATE.
update dbo.IDSETREGISTER set
RECORDTYPEID = @RECORDTYPEID,
ACTIVE = 0,
ISSYSTEM = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @IDSETREGISTERID;
-- Save the ID set ID on the segment table...
update dbo.MKTSEGMENT set
IDSETREGISTERID = @IDSETREGISTERID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
set @RETVAL = 1;
end catch
return isnull(@RETVAL, 0);
end