USP_ADDRESSPROCESS_ADDRESSES
Returns addresses according to address processing rules.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ADDRESSPROCESSINGOPTIONID | uniqueidentifier | IN | |
| @MAILTYPE | tinyint | IN | |
| @PARAMETERSETID | uniqueidentifier | IN | |
| @DATE | datetime | IN | |
| @INCLUDEHOUSEHOLDPROCESSING | bit | IN | |
| @CONSTITUENTIDSETTABLENAME | nvarchar(128) | IN | |
| @CONSTITUENTIDSETJOINCOLUMNNAME | nvarchar(128) | IN | |
| @IGNORECHANNELPREFERENCEFORSUPPRESSION | bit | IN | |
| @IGNOREADDRESSSUPPRESSION | bit | IN |
Definition
Copy
CREATE procedure dbo.[USP_ADDRESSPROCESS_ADDRESSES]
(
@ADDRESSPROCESSINGOPTIONID uniqueidentifier,
@MAILTYPE tinyint,
@PARAMETERSETID uniqueidentifier,
@DATE datetime,
@INCLUDEHOUSEHOLDPROCESSING bit,
@CONSTITUENTIDSETTABLENAME nvarchar(128), --Table or view that contains constituent IDs
@CONSTITUENTIDSETJOINCOLUMNNAME nvarchar(128), --Column to use when joining to the table or view
@IGNORECHANNELPREFERENCEFORSUPPRESSION bit = 0, --0=Respect channel preferences when suppressing addresses, 1=Ignore channel preferences and do not let other channels (ie: email) cause addresses to be suppressed
@IGNOREADDRESSSUPPRESSION bit = 0 --0=Suppress records that do not have a valid address, 1=Ignore address suppression and return all original records with possibly null data
)
as
set nocount on;
--Returns results in the following temp table named #TEMP_ADDRESSPROCESS_ADDRESSES. This temp table must exist
--outside of this stored procedure. Use the statement below to create the temp table before calling this stored
--procedure. The temp table will be indexed on the CONSTITUENTID column.
--
--create table #TEMP_ADDRESSPROCESS_ADDRESSES (
-- [CONSTITUENTID] uniqueidentifier not null,
-- [ADDRESSID] uniqueidentifier,
-- [CONTACTID] uniqueidentifier,
-- [POSITION] nvarchar(100) collate database_default,
-- [HOUSEHOLDID] uniqueidentifier,
-- [RETURNEDASHOUSEHOLDMEMBER] bit not null,
-- [GROUPCONTACTID] uniqueidentifier
--);
declare @INDUSESEASONALADDRESS bit;
declare @ORGMAILINGPREFERENCE tinyint;
declare @INDALTADDRESS1TYPECODEID uniqueidentifier;
declare @INDALTADDRESS1ISPRIMARY bit;
declare @INDALTADDRESS2TYPECODEID uniqueidentifier;
declare @INDALTADDRESS2ISPRIMARY bit;
declare @ORGALTADDRESS1TYPECODEID uniqueidentifier;
declare @ORGALTADDRESS1ISPRIMARY bit;
declare @ORGALTADDRESS2TYPECODEID uniqueidentifier;
declare @ORGALTADDRESS2ISPRIMARY bit;
declare @INDINCLUDEWITHNOADDRESS bit;
declare @ORGINCLUDEWITHNOADDRESS bit;
declare @ORGINCLUDEWITHNOCONTACT bit;
declare @ORGSENDTOALLCONTACTS bit;
declare @INDUSECONSTITUENTPREFS bit;
declare @ORGUSECONSTITUENTPREFS bit;
declare @GROUPALTADDRESS1TYPECODEID uniqueidentifier;
declare @GROUPALTADDRESS1ISPRIMARY bit;
declare @GROUPALTADDRESS2TYPECODEID uniqueidentifier;
declare @GROUPALTADDRESS2ISPRIMARY bit;
declare @GROUPINCLUDEWITHNOADDRESS bit;
declare @GROUPUSECONSTITUENTPREFS tinyint;
declare @CORRESPONDENCECODEID uniqueidentifier;
declare @EVENTCATEGORYCODEID uniqueidentifier;
declare @EVENTSITEIDS table ([ID] uniqueidentifier primary key);
declare @APPEALBUSINESSUNITCODEIDS table ([ID] uniqueidentifier primary key);
declare @APPEALCATEGORYCODEID uniqueidentifier;
declare @APPEALSITEID uniqueidentifier;
declare @OBJID int;
declare @ISTEMPTABLE bit;
declare @SQL nvarchar(max);
declare @DATEEARLIESTTIME datetime = dbo.[UFN_DATE_GETEARLIESTTIME](@DATE);
declare @DATEISTODAY bit = (case when @DATEEARLIESTTIME = dbo.[UFN_DATE_GETEARLIESTTIME](getdate()) then 1 else 0 end)
--Debug only
--declare @STARTDATE datetime;
--declare @STATUS nvarchar(100);
begin try
--Validate the required results temp table exists and that it does not have indexes defined on it...
set @OBJID = object_id('tempdb..#TEMP_ADDRESSPROCESS_ADDRESSES');
if @OBJID is null
raiserror('The temp table #TEMP_ADDRESSPROCESS_ADDRESSES is required to hold the results of this stored procedure but it does not exist. Please create the temp table before calling this stored procedure.', 13, 1);
if exists(select * from tempdb.sys.indexes where object_id = @OBJID and type = 2) --only concerned with nonclustered indexes
raiserror('The temp table #TEMP_ADDRESSPROCESS_ADDRESSES already has indexes defined on it. Please remove these indexes before calling this stored procedure.', 13, 1);
--Validate that none of the temp tables used in this stored procedure exist already...
if object_id('tempdb..#TEMPCONSTITUENT') is not null
raiserror('The temp table #TEMPCONSTITUENT is used by this stored procedure but it already exists in this session. Please make sure the temp table does not exist before calling this stored procedure.', 13, 1);
if object_id('tempdb..#TEMPADDRESS') is not null
raiserror('The temp table #TEMPADDRESS is used by this stored procedure but it already exists in this session. Please make sure the temp table does not exist before calling this stored procedure.', 13, 1);
if object_id('tempdb..#TEMPMAILPREFERENCE') is not null
raiserror('The temp table #TEMPMAILPREFERENCE is used by this stored procedure but it already exists in this session. Please make sure the temp table does not exist before calling this stored procedure.', 13, 1);
if object_id('tempdb..#TEMPMAILPREFERENCEADDRESS') is not null
raiserror('The temp table #TEMPMAILPREFERENCEADDRESS is used by this stored procedure but it already exists in this session. Please make sure the temp table does not exist before calling this stored procedure.', 13, 1);
if object_id('tempdb..#TEMPCONTACTADDRESS') is not null
raiserror('The temp table #TEMPCONTACTADDRESS is used by this stored procedure but it already exists in this session. Please make sure the temp table does not exist before calling this stored procedure.', 13, 1);
--Validate the constituent IDSet table exists and is defined appropriately...
if len(@CONSTITUENTIDSETTABLENAME) = 0 or len(@CONSTITUENTIDSETJOINCOLUMNNAME) = 0
raiserror('The parameters @CONSTITUENTIDSETTABLENAME and @CONSTITUENTIDSETJOINCOLUMNNAME are required to execute this stored procedure.', 13, 1);
set @ISTEMPTABLE = (case when left(@CONSTITUENTIDSETTABLENAME, 1) = '#' then 1 else 0 end);
set @OBJID = object_id((case when @ISTEMPTABLE = 1 then 'tempdb..' else 'dbo.' end) + '[' + @CONSTITUENTIDSETTABLENAME + ']');
if @OBJID is null
raiserror('The constituent IDSet table specified in the @CONSTITUENTIDSETTABLENAME parameter does not exist.', 13, 1);
if (@ISTEMPTABLE = 1 and not exists(select * from tempdb.sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETJOINCOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from tempdb.sys.types t where t.[name] = 'uniqueidentifier'))) or
(@ISTEMPTABLE = 0 and not exists(select * from sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETJOINCOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from sys.types t where t.[name] = 'uniqueidentifier')))
raiserror('The constituent IDSet join column specified in the @CONSTITUENTIDSETJOINCOLUMNNAME parameter does not exist or is not defined properly as a uniqueidentifier.', 13, 1);
if @IGNORECHANNELPREFERENCEFORSUPPRESSION is null
set @IGNORECHANNELPREFERENCEFORSUPPRESSION = 0;
if @IGNOREADDRESSSUPPRESSION is null
set @IGNOREADDRESSSUPPRESSION = 0;
--Get the address processing options...
select
@INDUSESEASONALADDRESS = [INDUSESEASONALADDRESS],
@ORGMAILINGPREFERENCE = [ORGMAILINGPREFERENCE],
@INDALTADDRESS1TYPECODEID = [INDALTADDRESS1TYPECODEID],
@INDALTADDRESS1ISPRIMARY = [INDALTADDRESS1ISPRIMARY],
@INDALTADDRESS2TYPECODEID = [INDALTADDRESS2TYPECODEID],
@INDALTADDRESS2ISPRIMARY = [INDALTADDRESS2ISPRIMARY],
@ORGALTADDRESS1TYPECODEID = [ORGALTADDRESS1TYPECODEID],
@ORGALTADDRESS1ISPRIMARY = [ORGALTADDRESS1ISPRIMARY],
@ORGALTADDRESS2TYPECODEID = [ORGALTADDRESS2TYPECODEID],
@ORGALTADDRESS2ISPRIMARY = [ORGALTADDRESS2ISPRIMARY],
@INDINCLUDEWITHNOADDRESS = [INDINCLUDEWITHNOADDRESS],
@ORGINCLUDEWITHNOADDRESS = [ORGINCLUDEWITHNOADDRESS],
@ORGINCLUDEWITHNOCONTACT = [ORGINCLUDEWITHNOCONTACT],
@ORGSENDTOALLCONTACTS = [ORGSENDTOALLCONTACTS],
@INDUSECONSTITUENTPREFS = [INDUSECONSTITUENTPREFS],
@ORGUSECONSTITUENTPREFS = [ORGUSECONSTITUENTPREFS],
@GROUPALTADDRESS1TYPECODEID = [GROUPALTADDRESS1TYPECODEID],
@GROUPALTADDRESS1ISPRIMARY = [GROUPALTADDRESS1ISPRIMARY],
@GROUPALTADDRESS2TYPECODEID = [GROUPALTADDRESS2TYPECODEID],
@GROUPALTADDRESS2ISPRIMARY = [GROUPALTADDRESS2ISPRIMARY],
@GROUPINCLUDEWITHNOADDRESS = [GROUPINCLUDEWITHNOADDRESS],
@GROUPUSECONSTITUENTPREFS = [GROUPUSECONSTITUENTPREFS]
from dbo.[ADDRESSPROCESSINGOPTION]
where [ID] = @ADDRESSPROCESSINGOPTIONID;
--Get some info for different mail types...
--0 = Revenue Acknowledgements
--1 = Appeals
--2 = Events
--3 = General Correspondence
--4 = Reminders
--5 = Receipts
--6 = Planned Gift Acknowledgements
--7 = Tribute Acknowledgements
--8 = Stewardship
if @MAILTYPE = 1
begin
select
@APPEALCATEGORYCODEID = APPEALCATEGORYCODEID,
@APPEALSITEID = SITEID
from dbo.APPEAL
where ID = @PARAMETERSETID;
insert into @APPEALBUSINESSUNITCODEIDS ([ID])
select BUSINESSUNITCODEID
from dbo.APPEALBUSINESSUNIT
where APPEALID = @PARAMETERSETID;
end
else if @MAILTYPE = 2
begin
select
@EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID
from dbo.EVENT E
inner join dbo.INVITATION I on E.ID = I.EVENTID
where I.ID = @PARAMETERSETID;
insert into @EVENTSITEIDS ([ID])
select E.SITEID
from dbo.EVENTSITE E
inner join dbo.INVITATION I on E.EVENTID = I.EVENTID
where I.ID = @PARAMETERSETID;
end
else if @MAILTYPE = 3
select
@CORRESPONDENCECODEID = CORRESPONDENCECODEID
from dbo.CORRESPONDENCEPROCESS
where ID = @PARAMETERSETID;
/****************************************************/
/* Constituents */
/****************************************************/
--Debug only
--raiserror('Constituents...', 0, 1) with nowait;
--set @STARTDATE = getdate();
create table #TEMPCONSTITUENT (
[ID] uniqueidentifier not null,
[HOUSEHOLDID] uniqueidentifier,
[RETURNEDASHOUSEHOLDMEMBER] bit not null,
[ISORGANIZATION] bit not null,
[ISGROUP] bit not null
);
if @DATEISTODAY = 1
--If @DATE is today, then use the CONSTITUENTHOUSEHOLD table as an optimization for current household info...
set @SQL = 'with [HOUSEHOLD_CTE] ([CONSTITUENTID], [HOUSEHOLDID]) as' + char(13) +
'(' + char(13) +
' --Get all households and members as of the current date...' + char(13) +
' select' + char(13) +
' [ID],' + char(13) +
' (case when [ISHOUSEHOLD] = 1 then [ID] else [HOUSEHOLDID] end)' + char(13) +
' from dbo.[CONSTITUENTHOUSEHOLD]' + char(13) +
'),' + char(13);
else
--If @DATE is not today, then get household info as of that date...
set @SQL = 'with [HOUSEHOLDS_CTE] ([HOUSEHOLDID]) as' + char(13) +
'(' + char(13) +
' --Get all household records...' + char(13) +
' select [ID]' + char(13) +
' from dbo.[GROUPDATA]' + char(13) +
' where [GROUPTYPECODE] = 0' + char(13) +
'),' + char(13) +
'[HOUSEHOLD_CTE] ([CONSTITUENTID], [HOUSEHOLDID]) as' + char(13) +
'(' + char(13) +
' --Get all households and members as of a specific date...' + char(13) +
' select' + char(13) +
' [HOUSEHOLDID],' + char(13) +
' [HOUSEHOLDID]' + char(13) +
' from [HOUSEHOLDS_CTE]' + char(13) +
char(13) +
' union all' + char(13) +
char(13) +
' select' + char(13) +
' [GROUPMEMBER].[MEMBERID],' + char(13) +
' [HOUSEHOLDS_CTE].[HOUSEHOLDID]' + char(13) +
' from [HOUSEHOLDS_CTE]' + char(13) +
' inner join dbo.[GROUPMEMBER] on [GROUPMEMBER].[GROUPID] = [HOUSEHOLDS_CTE].[HOUSEHOLDID]' + char(13) +
' inner join dbo.[GROUPMEMBERDATERANGE] on [GROUPMEMBERDATERANGE].[GROUPMEMBERID] = [GROUPMEMBER].[ID]' + char(13) +
' where (([GROUPMEMBERDATERANGE].[DATEFROM] is null and ([GROUPMEMBERDATERANGE].[DATETO] is null or [GROUPMEMBERDATERANGE].[DATETO] > @DATEEARLIESTTIME))' + char(13) +
' or ([GROUPMEMBERDATERANGE].[DATETO] is null and ([GROUPMEMBERDATERANGE].[DATEFROM] is null or [GROUPMEMBERDATERANGE].[DATEFROM] <= @DATEEARLIESTTIME))' + char(13) +
' or ([GROUPMEMBERDATERANGE].[DATEFROM] <= @DATEEARLIESTTIME and [GROUPMEMBERDATERANGE].[DATETO] > @DATEEARLIESTTIME))' + char(13) +
'),' + char(13);
set @SQL += '[CONSTITUENTS_CTE] ([ID], [HOUSEHOLDID], [RETURNEDASHOUSEHOLDMEMBER], [ISORGANIZATION], [ISGROUP]) as' + char(13) +
'(' + char(13) +
' select' + char(13) +
' [CONSTITUENT].[ID],' + char(13) +
' [HOUSEHOLD_CTE].[HOUSEHOLDID],' + char(13) +
' 0 as [RETURNEDASHOUSEHOLDMEMBER],' + char(13) +
' [CONSTITUENT].[ISORGANIZATION],' + char(13) +
' [CONSTITUENT].[ISGROUP]' + char(13) +
' from dbo.[CONSTITUENT]' + char(13) +
' left join [HOUSEHOLD_CTE] on [HOUSEHOLD_CTE].[CONSTITUENTID] = [CONSTITUENT].[ID]' + char(13);
if @INCLUDEHOUSEHOLDPROCESSING = 1
set @SQL += char(13) +
' union all' + char(13) +
char(13) +
' select' + char(13) +
' [HOUSEHOLDID] as [ID],' + char(13) +
' [HOUSEHOLDID] as [HOUSEHOLDID],' + char(13) +
' 1 as [RETURNEDASHOUSEHOLDMEMBER],' + char(13) +
' 0 as [ISORGANIZATION],' + char(13) +
' 1 as [ISGROUP]' + char(13) +
' from [HOUSEHOLD_CTE]' + char(13);
set @SQL += ')' + char(13) +
'insert into #TEMPCONSTITUENT ([ID], [HOUSEHOLDID], [RETURNEDASHOUSEHOLDMEMBER], [ISORGANIZATION], [ISGROUP])' + char(13) +
' select' + char(13) +
' [CONSTITUENTS_CTE].[ID],' + char(13) +
' [CONSTITUENTS_CTE].[HOUSEHOLDID],' + char(13) +
' [CONSTITUENTS_CTE].[RETURNEDASHOUSEHOLDMEMBER],' + char(13) +
' [CONSTITUENTS_CTE].[ISORGANIZATION],' + char(13) +
' [CONSTITUENTS_CTE].[ISGROUP]' + char(13) +
' from dbo.[' + @CONSTITUENTIDSETTABLENAME + '] as [TEMP]' + char(13) +
' inner join [CONSTITUENTS_CTE] on [CONSTITUENTS_CTE].[ID] = [TEMP].[' + @CONSTITUENTIDSETJOINCOLUMNNAME + ']';
if @DATEISTODAY = 1
exec sp_executesql @SQL;
else
exec sp_executesql @SQL, N'@DATEEARLIESTTIME datetime', @DATEEARLIESTTIME = @DATEEARLIESTTIME;
--Create an index on the temp table...
create nonclustered index [IX_TEMPCONSTITUENTS_ID] on #TEMPCONSTITUENT ([ID])
include ([HOUSEHOLDID], [RETURNEDASHOUSEHOLDMEMBER], [ISORGANIZATION], [ISGROUP]);
--Debug only
--set @STATUS = 'Constituents... ' + cast((select count(*) from #TEMPCONSTITUENT) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';
--raiserror(@STATUS, 0, 1) with nowait;
/****************************************************/
/* Addresses */
/****************************************************/
--Debug only
--raiserror('Addresses...', 0, 1) with nowait;
--set @STARTDATE = getdate();
create table #TEMPADDRESS (
[ID] uniqueidentifier not null,
[CONSTITUENTID] uniqueidentifier not null,
[ADDRESSTYPECODEID] uniqueidentifier,
[ISPRIMARY] bit not null,
[STARTDATE] char(4) collate database_default not null,
[ENDDATE] char(4) collate database_default not null,
[SEQUENCE] int not null
);
insert into #TEMPADDRESS ([ID], [CONSTITUENTID], [ADDRESSTYPECODEID], [ISPRIMARY], [STARTDATE], [ENDDATE], [SEQUENCE])
select
A.[ID],
A.[CONSTITUENTID],
A.[ADDRESSTYPECODEID],
A.[ISPRIMARY],
A.[STARTDATE],
A.[ENDDATE],
A.[SEQUENCE]
from #TEMPCONSTITUENT as C
inner join dbo.[ADDRESS] as A on A.[CONSTITUENTID] = C.[ID] and A.[DONOTMAIL] = 0;
--Create some indexes on the temp table...
create nonclustered index [IX_TEMPADDRESS_CONSTITUENTID] on #TEMPADDRESS ([CONSTITUENTID])
include ([ID], [ADDRESSTYPECODEID], [ISPRIMARY], [STARTDATE], [ENDDATE], [SEQUENCE]);
--Debug only
--set @STATUS = 'Addresses... ' + cast((select count(*) from #TEMPADDRESS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';
--raiserror(@STATUS, 0, 1) with nowait;
/****************************************************/
/* Mail Preferences */
/****************************************************/
--Debug only
--raiserror('Mail Preferences...', 0, 1) with nowait;
--set @STARTDATE = getdate();
create table #TEMPMAILPREFERENCE (
[ID] uniqueidentifier not null,
[CONSTITUENTID] uniqueidentifier not null,
[ADDRESSID] uniqueidentifier,
[USESEASONALADDRESS] bit not null,
[USEPRIMARYADDRESS] bit not null,
[CORRESPONDENCECODEID] uniqueidentifier,
[EVENTCATEGORYCODEID] uniqueidentifier,
[SITEID] uniqueidentifier,
[ACKNOWLEDGEMENTID] uniqueidentifier,
[CORRESPONDENCEID] uniqueidentifier,
[PLEDGEREMINDERID] uniqueidentifier,
[PURPOSEID] uniqueidentifier,
[CATEGORYCODEID] uniqueidentifier,
[BUSINESSUNITCODEID] uniqueidentifier
);
insert into #TEMPMAILPREFERENCE ([ID], [CONSTITUENTID], [ADDRESSID], [USESEASONALADDRESS], [USEPRIMARYADDRESS], [CORRESPONDENCECODEID], [EVENTCATEGORYCODEID], [SITEID], [ACKNOWLEDGEMENTID], [CORRESPONDENCEID], [PLEDGEREMINDERID], [PURPOSEID], [CATEGORYCODEID], [BUSINESSUNITCODEID])
select
MP.[ID],
MP.[CONSTITUENTID],
MP.[ADDRESSID],
MP.[USESEASONALADDRESS],
MP.[USEPRIMARYADDRESS],
MP.[CORRESPONDENCECODEID],
MP.[EVENTCATEGORYCODEID],
MP.[SITEID],
MP.[ACKNOWLEDGEMENTID],
MP.[CORRESPONDENCEID],
MP.[PLEDGEREMINDERID],
MP.[PURPOSEID],
MP.[CATEGORYCODEID],
MP.[BUSINESSUNITCODEID]
from #TEMPCONSTITUENT C
inner join dbo.[MAILPREFERENCE] as MP on MP.[CONSTITUENTID] = C.[ID] and MP.[SENDMAIL] = 1 and MP.[DELIVERYMETHODCODE] = 0 and MP.[MAILTYPECODE] = @MAILTYPE;
--Create an index on the temp table...
create nonclustered index [IX_TEMPMAILPREFERENCE_CONSTITUENTID] on #TEMPMAILPREFERENCE ([CONSTITUENTID])
include ([ID], [ADDRESSID], [USESEASONALADDRESS], [USEPRIMARYADDRESS], [CORRESPONDENCECODEID], [EVENTCATEGORYCODEID], [SITEID], [ACKNOWLEDGEMENTID], [CORRESPONDENCEID], [PLEDGEREMINDERID], [PURPOSEID], [CATEGORYCODEID], [BUSINESSUNITCODEID]);
--Debug only
--set @STATUS = 'Mail Preferences... ' + cast((select count(*) from #TEMPMAILPREFERENCE) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';
--raiserror(@STATUS, 0, 1) with nowait;
/****************************************************/
/* Mail Preference Addresses */
/****************************************************/
--Debug only
--raiserror('Mail Preference Addresses...', 0, 1) with nowait;
--set @STARTDATE = getdate();
create table #TEMPMAILPREFERENCEADDRESS (
[CONSTITUENTID] uniqueidentifier not null,
[ADDRESSID] uniqueidentifier,
[CONTACTID] uniqueidentifier,
[POSITION] nvarchar(100) collate database_default,
[GROUPCONTACTID] uniqueidentifier
);
insert into #TEMPMAILPREFERENCEADDRESS ([CONSTITUENTID], [ADDRESSID], [CONTACTID], [POSITION], [GROUPCONTACTID])
select
MP.[CONSTITUENTID],
isnull(SA.[ADDRESSID], A.[ID]) as [ADDRESSID],
null as [CONTACTID],
null as [POSITION],
null as [GROUPCONTACTID]
from #TEMPMAILPREFERENCE as MP
inner join #TEMPCONSTITUENT as C on MP.[CONSTITUENTID] = C.[ID]
left join dbo.[UFN_ADDRESSPROCESS_SEASONALADDRESSES](@DATE) as SA on MP.[CONSTITUENTID] = SA.[CONSTITUENTID] and MP.[USESEASONALADDRESS] = 1
inner join #TEMPADDRESS as A on (MP.[ADDRESSID] = A.[ID] or (MP.[CONSTITUENTID] = A.[CONSTITUENTID] and MP.[USEPRIMARYADDRESS] = 1 and A.[ISPRIMARY] = 1))
where
(
(@INDUSECONSTITUENTPREFS = 1 and C.[ISORGANIZATION] = 0 and C.[ISGROUP] = 0) or
(@ORGUSECONSTITUENTPREFS = 1 and C.[ISORGANIZATION] = 1 and not exists(select * from dbo.MAILPREFERENCEORGCONTACTTYPE where MAILPREFERENCEID = MP.ID)) or
(@GROUPUSECONSTITUENTPREFS = 1 and C.[ISGROUP] = 1 and not exists(select * from dbo.MAILPREFERENCEGROUPCONTACT where MAILPREFERENCEID = MP.ID))
)
and
(
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) = @PARAMETERSETID
or
(
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) is null
and not exists(select *
from dbo.MAILPREFERENCE
where CONSTITUENTID = MP.CONSTITUENTID
and (case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) = @PARAMETERSETID)
-- only consider correspondence codes when there isn't a specific process preference
and
(@MAILTYPE <> 3 or
(@MAILTYPE = 3 and
(
(MP.CORRESPONDENCECODEID is null and not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCECODEID = @CORRESPONDENCECODEID and CONSTITUENTID = MP.CONSTITUENTID))
or
(MP.CORRESPONDENCECODEID = @CORRESPONDENCECODEID and not exists (select * from dbo.MAILPREFERENCE where CORRESPONDENCEID = @PARAMETERSETID and CONSTITUENTID = MP.CONSTITUENTID))
)
)
)
)
)
and
(@MAILTYPE <> 1 or
(@MAILTYPE = 1 and
(
MP.BUSINESSUNITCODEID is null and
MP.CATEGORYCODEID is null and
MP.SITEID is null and
not exists(select *
from dbo.MAILPREFERENCE sub, dbo.APPEAL
left join dbo.APPEALBUSINESSUNIT AB on AB.APPEALID = APPEAL.ID
where APPEAL.ID = @PARAMETERSETID and sub.ID <> MP.ID
and sub.CONSTITUENTID = MP.CONSTITUENTID
and ((sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
or (sub.SITEID is null and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID is null)))
)
or
(
(MP.BUSINESSUNITCODEID in (select [ID] from @APPEALBUSINESSUNITCODEIDS) or MP.BUSINESSUNITCODEID is null) and
(MP.CATEGORYCODEID = @APPEALCATEGORYCODEID or MP.CATEGORYCODEID is null) and
(MP.SITEID = @APPEALSITEID or MP.SITEID is null) and
not (MP.CATEGORYCODEID is null and MP.SITEID is null and MP.BUSINESSUNITCODEID is null)
)
)
)
and
(@MAILTYPE <> 2 or
(@MAILTYPE = 2 and
(
MP.EVENTCATEGORYCODEID is null and
MP.SITEID is null and
not exists(select *
from dbo.MAILPREFERENCE sub, dbo.INVITATION I
inner join dbo.EVENT E on I.EVENTID = E.ID
left join dbo.EVENTSITE on EVENTSITE.EVENTID = E.ID
where I.ID = @PARAMETERSETID and sub.ID <> MP.ID
and sub.CONSTITUENTID = MP.CONSTITUENTID
and ((sub.SITEID = EVENTSITE.SITEID and sub.EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID)
or (sub.SITEID is null and sub.EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID)
or (sub.EVENTCATEGORYCODEID is null and sub.SITEID = EVENTSITE.SITEID)))
)
or
(
(MP.EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID or MP.EVENTCATEGORYCODEID is null) and
(MP.SITEID in (select [ID] from @EVENTSITEIDS) or MP.SITEID is null) and
not (MP.EVENTCATEGORYCODEID is null and MP.SITEID is null)
)
)
);
if @ORGUSECONSTITUENTPREFS = 1 and @MAILTYPE <> 5
--Grab contact addresses...
insert into #TEMPMAILPREFERENCEADDRESS ([CONSTITUENTID], [ADDRESSID], [CONTACTID], [POSITION], [GROUPCONTACTID])
select distinct
MP.CONSTITUENTID,
isnull(CA.ID, OA.ID) as ADDRESSID,
R.RECIPROCALCONSTITUENTID as CONTACTID,
isnull((select top 1 JOBTITLE from dbo.RELATIONSHIPJOBINFO where RELATIONSHIPSETID = R.RELATIONSHIPSETID order by STARTDATE desc), '') as POSITION,
null as GROUPCONTACTID
from dbo.MAILPREFERENCEORGCONTACTTYPE
inner join #TEMPMAILPREFERENCE MP on MAILPREFERENCEORGCONTACTTYPE.MAILPREFERENCEID = MP.ID
left join #TEMPADDRESS OA on MP.CONSTITUENTID = OA.CONSTITUENTID and OA.ISPRIMARY = 1
left join dbo.RELATIONSHIP R on R.RELATIONSHIPCONSTITUENTID = MP.CONSTITUENTID
left join dbo.[ADDRESS] CA on R.ID = CA.RELATIONSHIPID and CA.DONOTMAIL = 0
where
(
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) = @PARAMETERSETID
or
(
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) is null
and not exists(select *
from dbo.MAILPREFERENCE
where CONSTITUENTID = MP.CONSTITUENTID
and (case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end = @PARAMETERSETID))
-- only consider correspondence codes when there isn't a specific process preference
and
(@MAILTYPE <> 3 or
(@MAILTYPE = 3 and
(
(MP.CORRESPONDENCECODEID is null and not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCECODEID = @CORRESPONDENCECODEID and CONSTITUENTID = MP.CONSTITUENTID))
or
(MP.CORRESPONDENCECODEID = CORRESPONDENCECODEID and not exists (select ID from dbo.MAILPREFERENCE where CORRESPONDENCEID = @PARAMETERSETID and CONSTITUENTID = MP.CONSTITUENTID))
)
)
)
)
)
and
(@MAILTYPE <> 1 or
(@MAILTYPE = 1 and
(
MP.BUSINESSUNITCODEID is null and
MP.CATEGORYCODEID is null and
MP.SITEID is null and
not exists(select *
from dbo.MAILPREFERENCE sub, dbo.APPEAL
left join dbo.APPEALBUSINESSUNIT AB on AB.APPEALID = APPEAL.ID
where APPEAL.ID = @PARAMETERSETID and sub.ID <> MP.ID
and sub.CONSTITUENTID = MP.CONSTITUENTID
and ((sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
or (sub.SITEID is null and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID = AB.BUSINESSUNITCODEID)
or (sub.SITEID is null and sub.CATEGORYCODEID = APPEAL.APPEALCATEGORYCODEID and sub.BUSINESSUNITCODEID is null)
or (sub.SITEID = APPEAL.SITEID and sub.CATEGORYCODEID is null and sub.BUSINESSUNITCODEID is null)))
)
or
(
(MP.BUSINESSUNITCODEID in (select [ID] from @APPEALBUSINESSUNITCODEIDS) or MP.BUSINESSUNITCODEID is null) and
(MP.CATEGORYCODEID = @APPEALCATEGORYCODEID or MP.CATEGORYCODEID is null) and
(MP.SITEID = @APPEALSITEID or MP.SITEID is null) and
not (MP.CATEGORYCODEID is null and MP.SITEID is null and MP.BUSINESSUNITCODEID is null)
)
)
)
and
(@MAILTYPE <> 2 or
(@MAILTYPE = 2 and
(
MP.EVENTCATEGORYCODEID is null and
MP.SITEID is null and
not exists(select *
from dbo.MAILPREFERENCE sub, dbo.INVITATION I
inner join dbo.EVENT E on I.EVENTID = E.ID
left join dbo.EVENTSITE on EVENTSITE.EVENTID = E.ID
where I.ID = @PARAMETERSETID and sub.ID <> MP.ID
and sub.CONSTITUENTID = MP.CONSTITUENTID
and ((sub.SITEID = EVENTSITE.SITEID and sub.EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID)
or (sub.SITEID is null and sub.EVENTCATEGORYCODEID = E.EVENTCATEGORYCODEID)
or (sub.EVENTCATEGORYCODEID is null and sub.SITEID = EVENTSITE.SITEID)))
)
or
(
(MP.EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID or MP.EVENTCATEGORYCODEID is null) and
(MP.SITEID in (select [ID] from @EVENTSITEIDS) or MP.SITEID is null) and
not (MP.EVENTCATEGORYCODEID is null and MP.SITEID is null)
)
)
)
and R.ISCONTACT = 1
and (R.CONTACTTYPECODEID = MAILPREFERENCEORGCONTACTTYPE.CONTACTTYPECODEID or (R.ISPRIMARYCONTACT = 1 and MAILPREFERENCEORGCONTACTTYPE.USEPRIMARYCONTACT = 1));
if @ORGUSECONSTITUENTPREFS = 1 and @MAILTYPE = 5
--For receipts, only grab the first contact found
insert into #TEMPMAILPREFERENCEADDRESS ([CONSTITUENTID], [ADDRESSID], [CONTACTID], [POSITION], [GROUPCONTACTID])
select
MP.CONSTITUENTID,
isnull(MPO.ADDRESSID, OA.ID) as ADDRESSID,
MPO.RECIPROCALCONSTITUENTID as CONTACTID,
MPO.POSITION as POSITION,
null as GROUPCONTACTID
from #TEMPMAILPREFERENCE MP
left join #TEMPADDRESS OA on MP.CONSTITUENTID = OA.CONSTITUENTID and OA.ISPRIMARY = 1
cross apply (
select top 1
RELATIONSHIP.ID,
RELATIONSHIP.RECIPROCALCONSTITUENTID,
isnull((select top 1 JOBTITLE from dbo.RELATIONSHIPJOBINFO where RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID order by STARTDATE desc), '') as POSITION,
A.ID as ADDRESSID
from dbo.MAILPREFERENCEORGCONTACTTYPE APO
inner join dbo.RELATIONSHIP on APO.CONTACTTYPECODEID = RELATIONSHIP.CONTACTTYPECODEID or (APO.USEPRIMARYCONTACT = 1 and RELATIONSHIP.ISPRIMARYCONTACT = 1)
inner join dbo.CONTACTTYPECODE CTC on CTC.ID = RELATIONSHIP.CONTACTTYPECODEID
inner join dbo.[ADDRESS] A on RELATIONSHIP.ID = A.RELATIONSHIPID and A.DONOTMAIL = 0
where APO.MAILPREFERENCEID = MP.ID
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = MP.CONSTITUENTID
and RELATIONSHIP.ISCONTACT = 1
order by APO.USEPRIMARYCONTACT desc, CTC.[DESCRIPTION] asc
) MPO;
if @GROUPUSECONSTITUENTPREFS = 1
--Grab group member addresses...
insert into #TEMPMAILPREFERENCEADDRESS ([CONSTITUENTID], [ADDRESSID], [CONTACTID], [POSITION], [GROUPCONTACTID])
select
MP.CONSTITUENTID,
isnull(SA.ADDRESSID, A.ID) as ADDRESSID,
null as CONTACTID,
null as POSITION,
GM.MEMBERID as GROUPCONTACTID
from dbo.MAILPREFERENCEGROUPCONTACT GC
inner join #TEMPMAILPREFERENCE MP on GC.MAILPREFERENCEID = MP.ID
inner join #TEMPCONSTITUENT C on MP.CONSTITUENTID = C.ID
inner join dbo.GROUPMEMBER GM on (GM.GROUPID=C.ID and (GC.CONSTITUENTID=GM.MEMBERID or (GC.USEPRIMARYCONTACT=1 and GM.ISPRIMARY=1)))
left join dbo.UFN_ADDRESSPROCESS_SEASONALADDRESSES(@DATE) SA on GM.MEMBERID = SA.CONSTITUENTID and MP.USESEASONALADDRESS = 1
inner join dbo.[ADDRESS] A on (GM.MEMBERID = A.CONSTITUENTID and (A.ID = GC.ADDRESSID or (GC.USEPRIMARYADDRESS = 1 and A.ISPRIMARY = 1)) and A.DONOTMAIL = 0)
where C.ISGROUP = 1 and
(
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) = @PARAMETERSETID
or
(case @MAILTYPE when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) is null
)
and
(@MAILTYPE <> 3 or
(@MAILTYPE = 3 and
(
(MP.CORRESPONDENCECODEID is null and not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCECODEID = @CORRESPONDENCECODEID and CONSTITUENTID = MP.CONSTITUENTID))
or
(MP.CORRESPONDENCECODEID = @CORRESPONDENCECODEID and not exists(select * from dbo.MAILPREFERENCE where CORRESPONDENCEID = @PARAMETERSETID and CONSTITUENTID = MP.CONSTITUENTID))
)
)
);
--Create an index on the temp table...
create nonclustered index [IX_TEMPMAILPREFERENCEADDRESS_CONSTITUENTID] on #TEMPMAILPREFERENCEADDRESS ([CONSTITUENTID])
include ([ADDRESSID], [CONTACTID], [POSITION], [GROUPCONTACTID]);
--Debug only
--set @STATUS = 'Mail Preference Addresses... ' + cast((select count(*) from #TEMPMAILPREFERENCEADDRESS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';
--raiserror(@STATUS, 0, 1) with nowait;
/****************************************************/
/* Contact Addresses */
/****************************************************/
--Debug only
--raiserror('Contact Addresses...', 0, 1) with nowait;
--set @STARTDATE = getdate();
create table #TEMPCONTACTADDRESS (
[CONSTITUENTID] uniqueidentifier not null,
[ADDRESSID] uniqueidentifier,
[CONTACTID] uniqueidentifier,
[POSITION] nvarchar(100) collate database_default
);
if @ORGMAILINGPREFERENCE = 0
begin
if @ORGSENDTOALLCONTACTS = 0 or @MAILTYPE = 5
--For receipts, only include 1st contact
--Only send to 1 contact, or this is for receipts
insert into #TEMPCONTACTADDRESS ([CONSTITUENTID], [ADDRESSID], [CONTACTID], [POSITION])
select
C.ID as CONSTITUENTID,
isnull(R.ADDRESSID, OA.ID) as ADDRESSID,
R.RECIPROCALCONSTITUENTID as CONTACTID,
R.POSITION as POSITION
from dbo.#TEMPCONSTITUENT C
left join #TEMPADDRESS OA on C.ID = OA.CONSTITUENTID and OA.ISPRIMARY = 1
outer apply (
select top 1 CT.*
from dbo.UFN_ADDRESSPROCESS_CONTACTTYPEADDRESS(@ADDRESSPROCESSINGOPTIONID) CT
where CT.RELATIONSHIPCONSTITUENTID = C.ID
order by CT.PROCESSORDER
) R
where C.ISORGANIZATION = 1
and (@ORGINCLUDEWITHNOCONTACT = 1 or (@ORGINCLUDEWITHNOCONTACT = 0 and R.RECIPROCALCONSTITUENTID is not null))
and not exists(select *
from dbo.#TEMPMAILPREFERENCE MP
where CONSTITUENTID = C.ID
and @ORGUSECONSTITUENTPREFS = 1
and MP.ADDRESSID is not null
and (select count(*) from dbo.MAILPREFERENCEORGCONTACTTYPE MPC where MPC.MAILPREFERENCEID = MP.ID) = 0
and
(
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) = @PARAMETERSETID
or
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) is null
)
and (CORRESPONDENCECODEID is null or (@MAILTYPE = 3 and CORRESPONDENCECODEID = @CORRESPONDENCECODEID)));
if @ORGSENDTOALLCONTACTS = 1 and @MAILTYPE <> 5
insert into #TEMPCONTACTADDRESS ([CONSTITUENTID], [ADDRESSID], [CONTACTID], [POSITION])
select
C.ID as CONSTITUENTID,
isnull(R.ADDRESSID, OA.ID) as ADDRESSID,
R.RECIPROCALCONSTITUENTID as CONTACTID,
R.POSITION as POSITION
from dbo.#TEMPCONSTITUENT C
left join #TEMPADDRESS OA on C.ID = OA.CONSTITUENTID and OA.ISPRIMARY = 1
left join dbo.UFN_ADDRESSPROCESS_CONTACTTYPEADDRESS(@ADDRESSPROCESSINGOPTIONID) R on C.ID = R.RELATIONSHIPCONSTITUENTID
where C.ISORGANIZATION = 1
and (@ORGINCLUDEWITHNOCONTACT = 1 or (@ORGINCLUDEWITHNOCONTACT = 0 and R.RECIPROCALCONSTITUENTID is not null))
and not exists(select *
from dbo.#TEMPMAILPREFERENCE MP
where CONSTITUENTID = C.ID
and @ORGUSECONSTITUENTPREFS = 1
and MP.ADDRESSID is not null
and (select count(*) from dbo.MAILPREFERENCEORGCONTACTTYPE MPC where MPC.MAILPREFERENCEID = MP.ID) = 0
and
(
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) = @PARAMETERSETID
or
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) is null
)
and (CORRESPONDENCECODEID is null or (@MAILTYPE = 3 and CORRESPONDENCECODEID = @CORRESPONDENCECODEID)));
--Create an index on the temp table...
create nonclustered index [IX_TEMPCONTACTADDRESS_CONSTITUENTID] on #TEMPCONTACTADDRESS ([CONSTITUENTID])
include ([ADDRESSID], [CONTACTID], [POSITION]);
--Debug only
--set @STATUS = 'Contact Addresses... ' + cast((select count(*) from #TEMPCONTACTADDRESS) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';
--raiserror(@STATUS, 0, 1) with nowait;
end
/****************************************************/
/* BasicDev only */
/****************************************************/
declare @USEDONOTSENDOTHERCHANNEL bit;
set @USEDONOTSENDOTHERCHANNEL = (
case when exists(
select *
from dbo.[INSTALLEDPRODUCTLIST]
where [ID] = '42c15648-749e-4859-a56d-3a6474814cc7'
and ([EXPIREDATE] = '' or [EXPIREDATE] >= getdate()))
then 1 else 0 end);
/****************************************************/
/* Return the final addresses */
/****************************************************/
--Debug only
--raiserror('Return the final addresses...', 0, 1) with nowait;
--set @STARTDATE = getdate();
insert into #TEMP_ADDRESSPROCESS_ADDRESSES ([CONSTITUENTID], [ADDRESSID], [CONTACTID], [POSITION], [HOUSEHOLDID], [RETURNEDASHOUSEHOLDMEMBER], [GROUPCONTACTID])
select distinct
C.ID as CONSTITUENTID,
coalesce(MP.ADDRESSID,
(case when @INDUSESEASONALADDRESS = 1 and C.ISORGANIZATION = 0
then (select top 1 A.ID from #TEMPADDRESS A where A.CONSTITUENTID = C.ID and A.STARTDATE <> '0000' and ((cast((right('0' + cast(month(@DATE) as varchar(2)), 2) + right('0' + cast(day(@DATE) as varchar(2)), 2)) as int) - cast(A.STARTDATE as int)) + 1231) % 1231 between 0 and ((cast(A.ENDDATE as int) - cast(A.STARTDATE as int)) + 1231) % 1231 order by A.SEQUENCE)
else null
end),
CA.ADDRESSID,
(select top 1
A.ID
from #TEMPADDRESS A
where A.CONSTITUENTID = C.ID
and
(
(
C.ISORGANIZATION = 0 AND C.ISGROUP = 0 and
(A.ADDRESSTYPECODEID = @INDALTADDRESS1TYPECODEID or (A.ISPRIMARY = 1 and @INDALTADDRESS1ISPRIMARY = 1))
)
or
(
@ORGMAILINGPREFERENCE = 1 and C.ISORGANIZATION = 1 and
(A.ADDRESSTYPECODEID = @ORGALTADDRESS1TYPECODEID or (A.ISPRIMARY = 1 and @ORGALTADDRESS1ISPRIMARY = 1))
)
or
(
C.ISGROUP = 1 and
(A.ADDRESSTYPECODEID = @GROUPALTADDRESS1TYPECODEID or (A.ISPRIMARY = 1 and @GROUPALTADDRESS1ISPRIMARY = 1))
)
)
order by A.SEQUENCE),
(select top 1
A.ID
from #TEMPADDRESS A
where A.CONSTITUENTID = C.ID
and
(
(
C.ISORGANIZATION = 0 and C.ISGROUP = 0 and
(A.ADDRESSTYPECODEID = @INDALTADDRESS2TYPECODEID or (A.ISPRIMARY = 1 and @INDALTADDRESS2ISPRIMARY = 1))
)
or
(
@ORGMAILINGPREFERENCE = 1 and C.ISORGANIZATION = 1 and
(A.ADDRESSTYPECODEID = @ORGALTADDRESS2TYPECODEID or (A.ISPRIMARY = 1 and @ORGALTADDRESS2ISPRIMARY = 1))
)
or
(
C.ISGROUP = 1 and
(A.ADDRESSTYPECODEID = @GROUPALTADDRESS2TYPECODEID or (A.ISPRIMARY = 1 and @GROUPALTADDRESS2ISPRIMARY = 1))
)
)
order by A.SEQUENCE)
) as ADDRESSID,
isnull(MP.CONTACTID, CA.CONTACTID) as CONTACTID,
isnull(MP.POSITION, CA.POSITION) as POSITION,
C.HOUSEHOLDID,
C.RETURNEDASHOUSEHOLDMEMBER,
(case
when (C.RETURNEDASHOUSEHOLDMEMBER = 1) then isnull(MP.GROUPCONTACTID, C.HOUSEHOLDID)
when (C.ISGROUP = 1 and C.HOUSEHOLDID is not null) then isnull(MP.GROUPCONTACTID, C.HOUSEHOLDID)
else null
end) as GROUPCONTACTID
from #TEMPCONSTITUENT C
left join #TEMPMAILPREFERENCEADDRESS MP on MP.CONSTITUENTID = C.ID
left join #TEMPCONTACTADDRESS CA on CA.CONSTITUENTID = C.ID and @ORGMAILINGPREFERENCE = 0
where @IGNOREADDRESSSUPPRESSION = 1
or
(
@IGNOREADDRESSSUPPRESSION = 0
and
(
(
C.ISORGANIZATION = 1 and
(
(
@ORGMAILINGPREFERENCE = 0 and --if a valid mail pref is found, keep the record despite contact option
((@ORGINCLUDEWITHNOCONTACT = 0 and (MP.ADDRESSID is not null or CA.CONTACTID is not null)) or @ORGINCLUDEWITHNOCONTACT = 1)
)
or
(
@ORGMAILINGPREFERENCE = 1 and
((@ORGINCLUDEWITHNOADDRESS = 0 and
coalesce(MP.ADDRESSID,
CA.ADDRESSID,
(select top 1
A.ID
from #TEMPADDRESS A
where A.CONSTITUENTID = C.ID
and
(
@ORGMAILINGPREFERENCE = 1 and C.ISORGANIZATION = 1 and
(case
when (A.ADDRESSTYPECODEID = @ORGALTADDRESS1TYPECODEID or (A.ISPRIMARY = 1 and @ORGALTADDRESS1ISPRIMARY = 1)) then 1
when (A.ADDRESSTYPECODEID = @ORGALTADDRESS2TYPECODEID or (A.ISPRIMARY = 1 and @ORGALTADDRESS2ISPRIMARY = 1)) then 1
end) = 1
)
order by A.SEQUENCE)
) is not null) or @ORGINCLUDEWITHNOADDRESS = 1)
)
)
)
or
(
C.ISORGANIZATION = 0 and C.ISGROUP = 0 and
((@INDINCLUDEWITHNOADDRESS = 0 and
isnull(MP.ADDRESSID,
(select top 1
A.ID
from #TEMPADDRESS A
where A.CONSTITUENTID = C.ID
and
(
(
@INDUSESEASONALADDRESS = 1 and C.ISORGANIZATION = 0 and
A.STARTDATE <> '0000' and ((cast((right('0' + cast(month(@DATE) as varchar(2)), 2) + right('0' + cast(day(@DATE) as varchar(2)), 2)) as int) - cast(A.STARTDATE as int)) + 1231) % 1231 between 0 and ((cast(A.ENDDATE as int) - cast(A.STARTDATE as int)) + 1231) % 1231
)
or
(
C.ISORGANIZATION = 0 and C.ISGROUP = 0 and
(case
when (A.ADDRESSTYPECODEID = @INDALTADDRESS1TYPECODEID or (A.ISPRIMARY = 1 and @INDALTADDRESS1ISPRIMARY = 1)) then 1
when (A.ADDRESSTYPECODEID = @INDALTADDRESS2TYPECODEID or (A.ISPRIMARY = 1 and @INDALTADDRESS2ISPRIMARY = 1)) then 1
end) = 1
)
)
order by A.SEQUENCE)
) is not null) or @INDINCLUDEWITHNOADDRESS = 1)
)
or
(
C.ISGROUP = 1 and
((@GROUPINCLUDEWITHNOADDRESS = 0 and
isnull(MP.ADDRESSID,
(select top 1
A.ID
from #TEMPADDRESS A
where A.CONSTITUENTID = C.ID
and
(
(
@INDUSESEASONALADDRESS = 1 and C.ISORGANIZATION = 0 and
A.STARTDATE <> '0000' and ((cast((right('0' + cast(month(@DATE) as varchar(2)), 2) + right('0' + cast(day(@DATE) as varchar(2)), 2)) as int) - cast(A.STARTDATE as int)) + 1231) % 1231 between 0 and ((cast(A.ENDDATE as int) - cast(A.STARTDATE as int)) + 1231) % 1231
)
or
(
C.ISGROUP = 1 and
(case
when (A.ADDRESSTYPECODEID = @GROUPALTADDRESS1TYPECODEID or (A.ISPRIMARY = 1 and @GROUPALTADDRESS1ISPRIMARY = 1)) then 1
when (A.ADDRESSTYPECODEID = @GROUPALTADDRESS2TYPECODEID or (A.ISPRIMARY = 1 and @GROUPALTADDRESS2ISPRIMARY = 1)) then 1
end) = 1
)
)
order by A.SEQUENCE)
) is not null) or @GROUPINCLUDEWITHNOADDRESS = 1)
)
)
and
(
--Verify that the comm prefs allow the user to receive mailings
isnull((
select SUPPRESSMAILING from
(
select top 1
case when (SENDMAIL = 0 or (@IGNORECHANNELPREFERENCEFORSUPPRESSION = 0 and SENDMAIL = 1 and DELIVERYMETHODCODE = 1 and (@USEDONOTSENDOTHERCHANNEL = 0 or (@USEDONOTSENDOTHERCHANNEL = 1 and DONOTSENDOTHERCHANNEL = 1)))) then 1 else 0 end SUPPRESSMAILING,
case
--Revenue acknowledgements
when @MAILTYPE = 0 and (ACKNOWLEDGEMENTID is not null) then 1
--Appeals
when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 3
when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null) then 2
when @MAILTYPE = 1 and (SITEID is not null and CATEGORYCODEID is not null) then 2
when @MAILTYPE = 1 and (BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null) then 2
when @MAILTYPE = 1 and (SITEID is not null or BUSINESSUNITCODEID is not null or CATEGORYCODEID is not null) then 1
--Events
when @MAILTYPE = 2 and (EVENTCATEGORYCODEID is not null and SITEID is not null) then 2
when @MAILTYPE = 2 and (EVENTCATEGORYCODEID is not null or SITEID is not null) then 1
--Correspondence
when @MAILTYPE = 3 and (CORRESPONDENCECODEID is not null and CORRESPONDENCEID is not null) then 2
when @MAILTYPE = 3 and (CORRESPONDENCECODEID is not null or CORRESPONDENCEID is not null) then 1
--Reminders
when @MAILTYPE = 4 and (PLEDGEREMINDERID is not null) then 1
--Stewardship
when @MAILTYPE = 8 and (PURPOSEID is not null) then 1
else 0
end as CRITERIAMATCHSCORE
from dbo.MAILPREFERENCE
where CONSTITUENTID = C.ID
and MAILTYPECODE = @MAILTYPE
and
(
(@INDUSECONSTITUENTPREFS = 1 and C.ISORGANIZATION = 0 and C.ISGROUP = 0) or
(@ORGUSECONSTITUENTPREFS = 1 and C.ISORGANIZATION = 1) or
(@GROUPUSECONSTITUENTPREFS = 1 and C.ISGROUP = 1)
)
and
(
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) = @PARAMETERSETID
or
(case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
when 8 then PURPOSEID
else null
end) is null
)
and ((CORRESPONDENCECODEID is null) or (@MAILTYPE = 3 and CORRESPONDENCECODEID = @CORRESPONDENCECODEID))
and ((EVENTCATEGORYCODEID is null) or (@MAILTYPE = 2 and EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID))
and
(
(SITEID is null) or
(@MAILTYPE = 1 and SITEID = @APPEALSITEID) or
(@MAILTYPE = 2 and SITEID in (select [ID] from @EVENTSITEIDS))
)
and ((PURPOSEID is null) or (@MAILTYPE = 8 and PURPOSEID = @PARAMETERSETID))
and ((BUSINESSUNITCODEID is null) or (@MAILTYPE = 1 and BUSINESSUNITCODEID in (select [ID] from @APPEALBUSINESSUNITCODEIDS)))
and ((CATEGORYCODEID is null) or (@MAILTYPE = 1 and CATEGORYCODEID = @APPEALCATEGORYCODEID))
order by CRITERIAMATCHSCORE desc, SUPPRESSMAILING desc
) as MATCHEDPREFERENCES
), 0) = 0
)
);
--Create an index on the final address results...
create nonclustered index [IX_TEMP_ADDRESSPROCESS_ADDRESSES_CONSTITUENTID] on #TEMP_ADDRESSPROCESS_ADDRESSES ([CONSTITUENTID])
include ([ADDRESSID], [CONTACTID], [POSITION], [HOUSEHOLDID], [RETURNEDASHOUSEHOLDMEMBER], [GROUPCONTACTID]);
--Debug only
--set @STATUS = 'Return the final addresses... ' + cast((select count(*) from #TEMP_ADDRESSPROCESS_ADDRESSES) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';
--raiserror(@STATUS, 0, 1) with nowait;
/****************************************************/
/* Clean up */
/****************************************************/
drop table #TEMPCONSTITUENT;
drop table #TEMPADDRESS;
drop table #TEMPMAILPREFERENCE;
drop table #TEMPMAILPREFERENCEADDRESS;
drop table #TEMPCONTACTADDRESS;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
if object_id('tempdb..#TEMPCONSTITUENT') is not null
drop table #TEMPCONSTITUENT;
if object_id('tempdb..#TEMPADDRESS') is not null
drop table #TEMPADDRESS;
if object_id('tempdb..#TEMPMAILPREFERENCE') is not null
drop table #TEMPMAILPREFERENCE;
if object_id('tempdb..#TEMPMAILPREFERENCEADDRESS') is not null
drop table #TEMPMAILPREFERENCEADDRESS;
if object_id('tempdb..#TEMPCONTACTADDRESS') is not null
drop table #TEMPCONTACTADDRESS;
return 1;
end catch
return 0;