USP_MAILING_GETNAMEFORMATS
Returns constituents with name format processing rules applied.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @NAMEFORMATPARAMETERID | uniqueidentifier | IN | |
| @CONSTITUENTIDSETTABLENAME | nvarchar(128) | IN | |
| @CONSTITUENTIDSETJOINCOLUMNNAME | nvarchar(128) | IN | |
| @CONSTITUENTIDSETCONTACTCOLUMNNAME | nvarchar(128) | IN | |
| @CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME | nvarchar(128) | IN | |
| @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME | nvarchar(128) | IN |
Definition
Copy
CREATE procedure dbo.[USP_MAILING_GETNAMEFORMATS]
(
@NAMEFORMATPARAMETERID uniqueidentifier,
@CONSTITUENTIDSETTABLENAME nvarchar(128), --Table or view that contains constituent IDs
@CONSTITUENTIDSETJOINCOLUMNNAME nvarchar(128), --Column to use when joining to the table or view
@CONSTITUENTIDSETCONTACTCOLUMNNAME nvarchar(128) = '', --Column to use when you have contacts
@CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME nvarchar(128) = '', --Column to use when you have group contacts
@CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME nvarchar(128) = '' --Column to use with exclude spouse options
)
as
set nocount on;
--Returns results in the following temp table named #TEMP_MAILING_NAMEFORMATS. 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_MAILING_NAMEFORMATS (
-- [CONSTITUENTID] uniqueidentifier not null,
-- [MAILTOCONSTITUENTID] uniqueidentifier not null,
-- [ADDRESSEE] nvarchar(700) collate database_default,
-- [SALUTATION] nvarchar(700) collate database_default,
-- [CONTACT] nvarchar(700) collate database_default
--);
declare @ADDRESSEEFUNCTIONID uniqueidentifier;
declare @ADDRESSEEFUNCTIONISJOINT bit;
declare @SALUTATIONFUNCTIONID uniqueidentifier;
declare @SALUTATIONFUNCTIONISJOINT bit;
declare @CONTACTADDRESSEEFUNCTIONID uniqueidentifier;
declare @CONTACTADDRESSEEFUNCTIONISJOINT bit;
declare @ORGSALUTATIONCODE tinyint;
declare @CONTACTSALUTATIONFUNCTIONID uniqueidentifier;
declare @CONTACTSALUTATIONFUNCTIONISJOINT bit;
declare @CONTACTSALUTATIONOPTIONCODE tinyint;
declare @CUSTOMNAME nvarchar(100);
declare @GROUPADDRESSEEFUNCTIONID uniqueidentifier;
declare @GROUPADDRESSEEFUNCTIONISJOINT bit;
declare @GROUPSALUTATIONCODE tinyint;
declare @GROUPSALUTATIONFUNCTIONID uniqueidentifier;
declare @GROUPSALUTATIONFUNCTIONISJOINT bit;
declare @GROUPNOCONTACTOPTIONCODE bit;
declare @GROUPNOCONTACTCUSTOMNAME nvarchar(100);
declare @JOINTRULETYPECODE As tinyint;
declare @JOINTSELECTIONID As uniqueidentifier;
declare @JOINTSELECTIONBOTHRULETYPECODE As tinyint;
declare @JOINTSELECTIONNEITHERRULETYPECODE As tinyint;
declare @OBJID int;
declare @ISTEMPTABLE bit;
declare @SQL nvarchar(max);
--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_MAILING_NAMEFORMATS');
if @OBJID is null
raiserror('The temp table #TEMP_MAILING_NAMEFORMATS 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_MAILING_NAMEFORMATS already has indexes defined on it. Please remove these indexes before calling this stored procedure.', 13, 1);
if not exists(select * from tempdb.sys.columns c where c.[object_id] = @OBJID and c.[name] = 'MAILTOCONSTITUENTID' and c.[system_type_id] = (select t.[system_type_id] from tempdb.sys.types t where t.[name] = 'uniqueidentifier'))
raiserror('The temp table #TEMP_MAILING_NAMEFORMATS is missing the new required column named MAILTOCONSTITUENTID of type uniqueidentifier. Please add the column to your create table statement.', 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..#TEMPNAMEFORMAT') is not null
raiserror('The temp table #TEMPNAMEFORMAT 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 column specified in the @CONSTITUENTIDSETJOINCOLUMNNAME parameter does not exist or is not defined properly as a uniqueidentifier.', 13, 1);
if len(@CONSTITUENTIDSETCONTACTCOLUMNNAME) > 0 and
((@ISTEMPTABLE = 1 and not exists(select * from tempdb.sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETCONTACTCOLUMNNAME 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] = @CONSTITUENTIDSETCONTACTCOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from sys.types t where t.[name] = 'uniqueidentifier'))))
raiserror('The constituent IDSet column specified in the @CONSTITUENTIDSETCONTACTCOLUMNNAME parameter does not exist or is not defined properly as a uniqueidentifier.', 13, 1);
if len(@CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME) > 0 and
((@ISTEMPTABLE = 1 and not exists(select * from tempdb.sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME 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] = @CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from sys.types t where t.[name] = 'uniqueidentifier'))))
raiserror('The constituent IDSet column specified in the @CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME parameter does not exist or is not defined properly as a uniqueidentifier.', 13, 1);
if len(@CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME) > 0 and
((@ISTEMPTABLE = 1 and not exists(select * from tempdb.sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from tempdb.sys.types t where t.[name] = 'bit'))) or
(@ISTEMPTABLE = 0 and not exists(select * from sys.columns c where c.[object_id] = @OBJID and c.[name] = @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME and c.[system_type_id] = (select t.[system_type_id] from sys.types t where t.[name] = 'bit'))))
raiserror('The constituent IDSet column specified in the @CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME parameter does not exist or is not defined properly as a bit.', 13, 1);
--Get the name format options...
select
@ADDRESSEEFUNCTIONID = [ADDRESSEEFUNCTIONID],
@ADDRESSEEFUNCTIONISJOINT = dbo.[UFN_NAMEFORMATFUNCTION_ISJOINT]([ADDRESSEEFUNCTIONID]),
@SALUTATIONFUNCTIONID = [SALUTATIONFUNCTIONID],
@SALUTATIONFUNCTIONISJOINT = dbo.[UFN_NAMEFORMATFUNCTION_ISJOINT]([SALUTATIONFUNCTIONID]),
@CONTACTADDRESSEEFUNCTIONID = [CONTACTADDRESSEEFUNCTIONID],
@CONTACTADDRESSEEFUNCTIONISJOINT = dbo.[UFN_NAMEFORMATFUNCTION_ISJOINT]([CONTACTADDRESSEEFUNCTIONID]),
@ORGSALUTATIONCODE = [ORGSALUTATIONCODE],
@CONTACTSALUTATIONFUNCTIONID = [CONTACTSALUTATIONFUNCTIONID],
@CONTACTSALUTATIONFUNCTIONISJOINT = dbo.[UFN_NAMEFORMATFUNCTION_ISJOINT]([CONTACTSALUTATIONFUNCTIONID]),
@CONTACTSALUTATIONOPTIONCODE = [CONTACTSALUTATIONOPTIONCODE],
@CUSTOMNAME = [CUSTOMNAME],
@GROUPADDRESSEEFUNCTIONID = [GROUPADDRESSEEFUNCTIONID],
@GROUPADDRESSEEFUNCTIONISJOINT = dbo.[UFN_NAMEFORMATFUNCTION_ISJOINT]([GROUPADDRESSEEFUNCTIONID]),
@GROUPSALUTATIONCODE = [GROUPSALUTATIONCODE],
@GROUPSALUTATIONFUNCTIONID = [GROUPSALUTATIONFUNCTIONID],
@GROUPSALUTATIONFUNCTIONISJOINT = dbo.[UFN_NAMEFORMATFUNCTION_ISJOINT]([GROUPSALUTATIONFUNCTIONID]),
@GROUPNOCONTACTOPTIONCODE = [GROUPNOCONTACTOPTIONCODE],
@GROUPNOCONTACTCUSTOMNAME = [GROUPNOCONTACTCUSTOMNAME],
@JOINTRULETYPECODE = [JOINTRULETYPECODE],
@JOINTSELECTIONID = [JOINTSELECTIONID],
@JOINTSELECTIONBOTHRULETYPECODE = [JOINTSELECTIONBOTHRULETYPECODE],
@JOINTSELECTIONNEITHERRULETYPECODE = [JOINTSELECTIONNEITHERRULETYPECODE]
from dbo.[NAMEFORMATPARAMETER]
where [ID] = @NAMEFORMATPARAMETERID;
/****************************************************/
/* Constituents */
/****************************************************/
--Debug only
--raiserror('Constituents...', 0, 1) with nowait;
--set @STARTDATE = getdate();
create table #TEMPCONSTITUENT (
[CONSTITUENTID] uniqueidentifier not null,
[CONTACTID] uniqueidentifier,
[GROUPCONTACTID] uniqueidentifier,
[EXCLUDESPOUSE] bit not null
);
--Insert the IDs into another temp table so we don't have make the huge statement below all dynamic sql.
--This should be really fast if we are just moving from one temp table to another.
set @SQL = 'insert into #TEMPCONSTITUENT ([CONSTITUENTID], [CONTACTID], [GROUPCONTACTID], [EXCLUDESPOUSE])' + char(13) +
' select' + char(13) +
' ' + quotename(@CONSTITUENTIDSETJOINCOLUMNNAME) + ',' + char(13) +
' ' + (case when len(@CONSTITUENTIDSETCONTACTCOLUMNNAME) > 0 then quotename(@CONSTITUENTIDSETCONTACTCOLUMNNAME) else 'null' end) + ',' + char(13) +
' ' + (case when len(@CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME) > 0 then quotename(@CONSTITUENTIDSETGROUPCONTACTCOLUMNNAME) else 'null' end) + ',' + char(13) +
' ' + (case when len(@CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME) > 0 then 'isnull(' + quotename(@CONSTITUENTIDSETEXCLUDESPOUSECOLUMNNAME) + ', 0)' else '0' end) + char(13) +
' from dbo.' + quotename(@CONSTITUENTIDSETTABLENAME);
exec (@SQL);
create nonclustered index [IX_TEMPCONSTITUENT_CONSTITUENTID] on #TEMPCONSTITUENT ([CONSTITUENTID]) include ([CONTACTID], [GROUPCONTACTID], [EXCLUDESPOUSE]);
--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;
/****************************************************/
/* Name formats */
/****************************************************/
--Debug only
--raiserror('Name formats...', 0, 1) with nowait;
--set @STARTDATE = getdate();
declare @TEMP_INDIVIDUALADDRESSEES table([SEQUENCE] int primary key, [ISPRIMARY] bit, [NAMEFORMATTYPECODEID] uniqueidentifier);
declare @TEMP_INDIVIDUALSALUTATIONS table([SEQUENCE] int primary key, [ISPRIMARY] bit, [NAMEFORMATTYPECODEID] uniqueidentifier);
declare @TEMP_ORGADDRESSEES table([SEQUENCE] int primary key, [ISPRIMARY] bit, [NAMEFORMATTYPECODEID] uniqueidentifier);
declare @TEMP_ORGSALUTATIONS table([SEQUENCE] int primary key, [ISPRIMARY] bit, [NAMEFORMATTYPECODEID] uniqueidentifier);
declare @TEMP_GROUPADDRESSEES table([SEQUENCE] int primary key, [ISPRIMARY] bit, [NAMEFORMATTYPECODEID] uniqueidentifier);
declare @TEMP_GROUPSALUTATIONS table([SEQUENCE] int primary key, [ISPRIMARY] bit, [NAMEFORMATTYPECODEID] uniqueidentifier);
insert into @TEMP_INDIVIDUALADDRESSEES ([SEQUENCE], [ISPRIMARY], [NAMEFORMATTYPECODEID])
select [SEQUENCE], [ADDRESSEEFORMATISPRIMARY], [NAMEFORMATTYPECODEID]
from dbo.[UFN_NAMEFORMATPARAMETER_GETINDADDRESSEES](@NAMEFORMATPARAMETERID);
insert into @TEMP_INDIVIDUALSALUTATIONS ([SEQUENCE], [ISPRIMARY], [NAMEFORMATTYPECODEID])
select [SEQUENCE], [SALUTATIONFORMATISPRIMARY], [NAMEFORMATTYPECODEID]
from dbo.[UFN_NAMEFORMATPARAMETER_GETINDSALUTATIONS](@NAMEFORMATPARAMETERID);
insert into @TEMP_ORGADDRESSEES ([SEQUENCE], [ISPRIMARY], [NAMEFORMATTYPECODEID])
select [SEQUENCE], [ADDRESSEEFORMATISPRIMARY], [NAMEFORMATTYPECODEID]
from dbo.[UFN_NAMEFORMATPARAMETER_GETORGADDRESSEES](@NAMEFORMATPARAMETERID);
insert into @TEMP_ORGSALUTATIONS ([SEQUENCE], [ISPRIMARY], [NAMEFORMATTYPECODEID])
select [SEQUENCE], [SALUTATIONFORMATISPRIMARY], [NAMEFORMATTYPECODEID]
from dbo.[UFN_NAMEFORMATPARAMETER_GETORGSALUTATIONS](@NAMEFORMATPARAMETERID);
insert into @TEMP_GROUPADDRESSEES ([SEQUENCE], [ISPRIMARY], [NAMEFORMATTYPECODEID])
select [SEQUENCE], [ADDRESSEEFORMATISPRIMARY], [NAMEFORMATTYPECODEID]
from dbo.[UFN_NAMEFORMATPARAMETER_GETGRPADDRESSEES](@NAMEFORMATPARAMETERID);
insert into @TEMP_GROUPSALUTATIONS ([SEQUENCE], [ISPRIMARY], [NAMEFORMATTYPECODEID])
select [SEQUENCE], [SALUTATIONFORMATISPRIMARY], [NAMEFORMATTYPECODEID]
from dbo.[UFN_NAMEFORMATPARAMETER_GETGRPSALUTATIONS](@NAMEFORMATPARAMETERID);
--Create some local variables so we can try to limit the number of name formats we pull back...
declare @PRIMARYADDRESSEEEXISTS bit = (case when exists(select * from @TEMP_INDIVIDUALADDRESSEES where ISPRIMARY = 1) or exists(select * from @TEMP_ORGADDRESSEES where ISPRIMARY = 1) or exists(select * from @TEMP_GROUPADDRESSEES where ISPRIMARY = 1) then 1 else 0 end);
declare @PRIMARYSALUTATIONEXISTS bit = (case when exists(select * from @TEMP_INDIVIDUALSALUTATIONS where ISPRIMARY = 1) or exists(select * from @TEMP_ORGSALUTATIONS where ISPRIMARY = 1) or exists(select * from @TEMP_GROUPSALUTATIONS where ISPRIMARY = 1) then 1 else 0 end);
declare @NAMEFORMATTYPECODEIDS table ([ID] uniqueidentifier primary key);
insert into @NAMEFORMATTYPECODEIDS ([ID])
select NAMEFORMATTYPECODEID from @TEMP_INDIVIDUALADDRESSEES where ISPRIMARY = 0
union
select NAMEFORMATTYPECODEID from @TEMP_INDIVIDUALSALUTATIONS where ISPRIMARY = 0
union
select NAMEFORMATTYPECODEID from @TEMP_ORGADDRESSEES where ISPRIMARY = 0
union
select NAMEFORMATTYPECODEID from @TEMP_ORGSALUTATIONS where ISPRIMARY = 0
union
select NAMEFORMATTYPECODEID from @TEMP_GROUPADDRESSEES where ISPRIMARY = 0
union
select NAMEFORMATTYPECODEID from @TEMP_GROUPSALUTATIONS where ISPRIMARY = 0;
--Pull back all possible name formats for our constituents...
create table #TEMPNAMEFORMAT (
[CONSTITUENTID] uniqueidentifier not null,
[MAILTOCONSTITUENTID] uniqueidentifier not null,
[NAMEFORMATTYPECODEID] uniqueidentifier not null,
[CUSTOMNAME] nvarchar(700) collate database_default not null,
[NAMEFORMATFUNCTIONID] uniqueidentifier,
[PRIMARYADDRESSEE] bit not null,
[PRIMARYSALUTATION] bit not null
);
insert into #TEMPNAMEFORMAT ([CONSTITUENTID], [MAILTOCONSTITUENTID], [NAMEFORMATTYPECODEID], [CUSTOMNAME], [NAMEFORMATFUNCTIONID], [PRIMARYADDRESSEE], [PRIMARYSALUTATION])
select
C.CONSTITUENTID,
NAMEFORMAT.CONSTITUENTID as [MAILTOCONSTITUENTID],
NAMEFORMAT.NAMEFORMATTYPECODEID,
NAMEFORMAT.CUSTOMNAME,
NAMEFORMAT.NAMEFORMATFUNCTIONID,
NAMEFORMAT.PRIMARYADDRESSEE,
NAMEFORMAT.PRIMARYSALUTATION
from #TEMPCONSTITUENT C
inner join dbo.CONSTITUENT on CONSTITUENT.ID = coalesce(C.CONTACTID, C.GROUPCONTACTID, C.CONSTITUENTID)
left join dbo.GROUPMEMBER on CONSTITUENT.ISGROUP = 1 and GROUPMEMBER.GROUPID = CONSTITUENT.ID and GROUPMEMBER.ISPRIMARY = 1
inner join dbo.NAMEFORMAT on NAMEFORMAT.CONSTITUENTID = (case when CONSTITUENT.ISGROUP = 1 then GROUPMEMBER.MEMBERID else CONSTITUENT.ID end)
where (@PRIMARYADDRESSEEEXISTS = 1 and NAMEFORMAT.PRIMARYADDRESSEE = 1)
or (@PRIMARYSALUTATIONEXISTS = 1 and NAMEFORMAT.PRIMARYSALUTATION = 1)
or NAMEFORMAT.NAMEFORMATTYPECODEID in (select ID from @NAMEFORMATTYPECODEIDS);
create nonclustered index [IX_TEMPNAMEFORMAT_CONSTITUENTID_MAILTOCONSTITUENTID] on #TEMPNAMEFORMAT ([CONSTITUENTID], [MAILTOCONSTITUENTID])
include ([NAMEFORMATTYPECODEID], [CUSTOMNAME], [NAMEFORMATFUNCTIONID], [PRIMARYADDRESSEE], [PRIMARYSALUTATION]);
--Debug only
--set @STATUS = 'Name formats... ' + cast((select count(*) from #TEMPNAMEFORMAT) as nvarchar(10)) + ' rows... ' + cast(datediff(s, @STARTDATE, getdate()) as nvarchar(10)) + ' seconds';
--raiserror(@STATUS, 0, 1) with nowait;
/****************************************************/
/* Final results */
/****************************************************/
--Debug only
--raiserror('Final results...', 0, 1) with nowait;
--set @STARTDATE = getdate();
with CONSTIT_CTE as
(
select
C.CONSTITUENTID,
C.CONTACTID,
C.EXCLUDESPOUSE,
isnull(MEMBERCONSTIT.ID, GROUPMEMBER.MEMBERID) as [MEMBERID],
CONSTITUENT.ISORGANIZATION,
CONSTITUENT.ISGROUP,
CONSTITUENT.NAME,
CONSTITUENT.GENDERCODE,
CONSTITUENT.KEYNAME,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.MIDDLENAME,
TITLECODE.[DESCRIPTION] as TITLE,
SUFFIXCODE.[DESCRIPTION] as SUFFIX,
CONSTITUENT.GENDERCODEID
from #TEMPCONSTITUENT as C
inner join dbo.CONSTITUENT on CONSTITUENT.ID = C.CONSTITUENTID
left join dbo.CONSTITUENT as MEMBERCONSTIT on MEMBERCONSTIT.ID = C.GROUPCONTACTID and MEMBERCONSTIT.ISGROUP = 0
left join dbo.GROUPMEMBER on CONSTITUENT.ISGROUP = 1 and GROUPMEMBER.GROUPID = CONSTITUENT.ID and GROUPMEMBER.ISPRIMARY = 1
left join dbo.TITLECODE on TITLECODE.ID = CONSTITUENT.TITLECODEID
left join dbo.SUFFIXCODE on SUFFIXCODE.ID = CONSTITUENT.SUFFIXCODEID
),
ADDRESSEE_CTE as
(
select
SUB.CONSTITUENTID,
SUB.MAILTOCONSTITUENTID,
SUB.CUSTOMNAME,
SUB.NAMEFORMATFUNCTIONID,
SUB.ISJOINTFUNCTION
from (
select
NF.CONSTITUENTID,
NF.MAILTOCONSTITUENTID,
NF.CUSTOMNAME,
NF.NAMEFORMATFUNCTIONID,
NAMEFORMATFUNCTION.ISJOINTFUNCTION,
row_number() over (partition by NF.CONSTITUENTID, NF.MAILTOCONSTITUENTID order by T.SEQUENCE) as SEQUENCE
from CONSTIT_CTE
inner join #TEMPNAMEFORMAT NF on NF.CONSTITUENTID = CONSTIT_CTE.CONSTITUENTID and NF.MAILTOCONSTITUENTID = (case when CONSTIT_CTE.ISORGANIZATION = 1 then CONSTIT_CTE.CONTACTID when CONSTIT_CTE.ISGROUP = 1 then CONSTIT_CTE.MEMBERID else CONSTIT_CTE.CONSTITUENTID end)
cross apply (
select T1.SEQUENCE from @TEMP_INDIVIDUALADDRESSEES T1 where CONSTIT_CTE.ISORGANIZATION = 0 and CONSTIT_CTE.ISGROUP = 0 and ((T1.ISPRIMARY = 1 and NF.PRIMARYADDRESSEE = 1) or T1.NAMEFORMATTYPECODEID = NF.NAMEFORMATTYPECODEID)
union all
select T2.SEQUENCE from @TEMP_ORGADDRESSEES T2 where CONSTIT_CTE.ISORGANIZATION = 1 and ((T2.ISPRIMARY = 1 and NF.PRIMARYADDRESSEE = 1) or T2.NAMEFORMATTYPECODEID = NF.NAMEFORMATTYPECODEID)
union all
select T3.SEQUENCE from @TEMP_GROUPADDRESSEES T3 where CONSTIT_CTE.ISGROUP = 1 and ((T3.ISPRIMARY = 1 and NF.PRIMARYADDRESSEE = 1) or T3.NAMEFORMATTYPECODEID = NF.NAMEFORMATTYPECODEID)
) as T
left join dbo.NAMEFORMATFUNCTION on CONSTIT_CTE.ISORGANIZATION = 0 and CONSTIT_CTE.ISGROUP = 0 and NAMEFORMATFUNCTION.ID = NF.NAMEFORMATFUNCTIONID
) as SUB
where SUB.SEQUENCE = 1
),
SALUTATION_CTE as
(
select
SUB.CONSTITUENTID,
SUB.MAILTOCONSTITUENTID,
SUB.CUSTOMNAME,
SUB.NAMEFORMATFUNCTIONID,
SUB.ISJOINTFUNCTION
from (
select
NF.CONSTITUENTID,
NF.MAILTOCONSTITUENTID,
NF.CUSTOMNAME,
NF.NAMEFORMATFUNCTIONID,
NAMEFORMATFUNCTION.ISJOINTFUNCTION,
row_number() over (partition by NF.CONSTITUENTID, NF.MAILTOCONSTITUENTID order by T.SEQUENCE) as SEQUENCE
from CONSTIT_CTE
inner join #TEMPNAMEFORMAT NF on NF.CONSTITUENTID = CONSTIT_CTE.CONSTITUENTID and NF.MAILTOCONSTITUENTID = (case when CONSTIT_CTE.ISORGANIZATION = 1 then CONSTIT_CTE.CONTACTID when CONSTIT_CTE.ISGROUP = 1 then CONSTIT_CTE.MEMBERID else CONSTIT_CTE.CONSTITUENTID end)
cross apply (
select T1.SEQUENCE from @TEMP_INDIVIDUALSALUTATIONS T1 where CONSTIT_CTE.ISORGANIZATION = 0 and CONSTIT_CTE.ISGROUP = 0 and ((T1.ISPRIMARY = 1 and NF.PRIMARYSALUTATION = 1) or T1.NAMEFORMATTYPECODEID = NF.NAMEFORMATTYPECODEID)
union all
select T2.SEQUENCE from @TEMP_ORGSALUTATIONS T2 where CONSTIT_CTE.ISORGANIZATION = 1 and ((T2.ISPRIMARY = 1 and NF.PRIMARYSALUTATION = 1) or T2.NAMEFORMATTYPECODEID = NF.NAMEFORMATTYPECODEID)
union all
select T3.SEQUENCE from @TEMP_GROUPSALUTATIONS T3 where CONSTIT_CTE.ISGROUP = 1 and ((T3.ISPRIMARY = 1 and NF.PRIMARYSALUTATION = 1) or T3.NAMEFORMATTYPECODEID = NF.NAMEFORMATTYPECODEID)
) as T
left join dbo.NAMEFORMATFUNCTION on CONSTIT_CTE.ISORGANIZATION = 0 and CONSTIT_CTE.ISGROUP = 0 and NAMEFORMATFUNCTION.ID = NF.NAMEFORMATFUNCTIONID
) as SUB
where SUB.SEQUENCE = 1
)
insert into #TEMP_MAILING_NAMEFORMATS ([CONSTITUENTID], [MAILTOCONSTITUENTID], [ADDRESSEE], [SALUTATION], [CONTACT])
select
CONSTIT_CTE.[CONSTITUENTID],
coalesce(CONSTIT_CTE.CONTACTID, CONSTIT_CTE.MEMBERID, CONSTIT_CTE.CONSTITUENTID) as [MAILTOCONSTITUENTID],
(case when CONSTIT_CTE.ISORGANIZATION = 1 or CONSTIT_CTE.ISGROUP = 1 then
CONSTIT_CTE.NAME
else
(case when len(ADDRESSEE_CTE.CUSTOMNAME) > 0 then
ADDRESSEE_CTE.CUSTOMNAME
else
(case when isnull(ADDRESSEE_CTE.ISJOINTFUNCTION, @ADDRESSEEFUNCTIONISJOINT) = 1 and CONSTIT_CTE.EXCLUDESPOUSE = 0 and dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(CONSTIT_CTE.CONSTITUENTID, SPOUSECONSTIT.ID, CONSTIT_CTE.GENDERCODE, SPOUSECONSTIT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTIT_CTE.GENDERCODEID, SPOUSECONSTIT.GENDERCODEID) = SPOUSECONSTIT.ID and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @ADDRESSEEFUNCTIONID)) = 0 then
--If the joint processor is the spouse, then just swap the params we pass to the function...
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @ADDRESSEEFUNCTIONID), SPOUSECONSTIT.ID, SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONSTIT_CTE.KEYNAME, CONSTIT_CTE.FIRSTNAME, CONSTIT_CTE.MIDDLENAME, CONSTIT_CTE.TITLE, CONSTIT_CTE.SUFFIX, 0), N'{CONDBREAK}', N' ')
else
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @ADDRESSEEFUNCTIONID), CONSTIT_CTE.CONSTITUENTID, CONSTIT_CTE.KEYNAME, CONSTIT_CTE.FIRSTNAME, CONSTIT_CTE.MIDDLENAME, CONSTIT_CTE.TITLE, CONSTIT_CTE.SUFFIX, SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONSTIT_CTE.EXCLUDESPOUSE), N'{CONDBREAK}', N' ')
end)
end)
end) as ADDRESSEE,
(case
when CONSTIT_CTE.ISORGANIZATION = 1 then
(case @ORGSALUTATIONCODE
when 1 then
(case when CONSTIT_CTE.CONTACTID is null then
(case when @CONTACTSALUTATIONOPTIONCODE = 1 then @CUSTOMNAME else CONSTIT_CTE.NAME end)
else
(case when len(SALUTATION_CTE.CUSTOMNAME) > 0 then
SALUTATION_CTE.CUSTOMNAME
else
(case when isnull(SALUTATION_CTE.ISJOINTFUNCTION, @CONTACTSALUTATIONFUNCTIONISJOINT) = 1 and CONSTIT_CTE.EXCLUDESPOUSE = 0 and dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(CONSTIT_CTE.CONTACTID, SPOUSECONSTIT.ID, CONSTIT_CTE.GENDERCODE, SPOUSECONSTIT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTIT_CTE.GENDERCODEID, SPOUSECONSTIT.GENDERCODEID) = SPOUSECONSTIT.ID and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @CONTACTSALUTATIONFUNCTIONID)) = 0 then
--If the joint processor is the spouse, then just swap the params we pass to the function...
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @CONTACTSALUTATIONFUNCTIONID), SPOUSECONSTIT.ID, SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONTACTCONSTIT.KEYNAME, CONTACTCONSTIT.FIRSTNAME, CONTACTCONSTIT.MIDDLENAME, CONTACTTITLE.[DESCRIPTION], CONTACTSUFFIX.[DESCRIPTION], 0), N'{CONDBREAK}', N' ')
else
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @CONTACTSALUTATIONFUNCTIONID), CONSTIT_CTE.CONTACTID, CONTACTCONSTIT.KEYNAME, CONTACTCONSTIT.FIRSTNAME, CONTACTCONSTIT.MIDDLENAME, CONTACTTITLE.[DESCRIPTION], CONTACTSUFFIX.[DESCRIPTION], SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONSTIT_CTE.EXCLUDESPOUSE), N'{CONDBREAK}', N' ')
end)
end)
end)
when 2 then
@CUSTOMNAME
else
CONSTIT_CTE.NAME
end)
when CONSTIT_CTE.ISGROUP = 1 then
(case @GROUPSALUTATIONCODE
when 1 then
(case when CONSTIT_CTE.MEMBERID is null then
(case when @GROUPNOCONTACTOPTIONCODE = 1 then @GROUPNOCONTACTCUSTOMNAME else CONSTIT_CTE.NAME end)
else
(case when len(SALUTATION_CTE.CUSTOMNAME) > 0 then
SALUTATION_CTE.CUSTOMNAME
else
(case when isnull(SALUTATION_CTE.ISJOINTFUNCTION, @GROUPSALUTATIONFUNCTIONISJOINT) = 1 and CONSTIT_CTE.EXCLUDESPOUSE = 0 and dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(CONSTIT_CTE.MEMBERID, SPOUSECONSTIT.ID, CONSTIT_CTE.GENDERCODE, SPOUSECONSTIT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTIT_CTE.GENDERCODEID, SPOUSECONSTIT.GENDERCODEID) = SPOUSECONSTIT.ID and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @GROUPSALUTATIONFUNCTIONID)) = 0 then
--If the joint processor is the spouse, then just swap the params we pass to the function...
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @GROUPSALUTATIONFUNCTIONID), SPOUSECONSTIT.ID, SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], GROUPCONSTIT.KEYNAME, GROUPCONSTIT.FIRSTNAME, GROUPCONSTIT.MIDDLENAME, GROUPTITLE.[DESCRIPTION], GROUPSUFFIX.[DESCRIPTION], 0), N'{CONDBREAK}', N' ')
else
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @GROUPSALUTATIONFUNCTIONID), CONSTIT_CTE.MEMBERID, GROUPCONSTIT.KEYNAME, GROUPCONSTIT.FIRSTNAME, GROUPCONSTIT.MIDDLENAME, GROUPTITLE.[DESCRIPTION], GROUPSUFFIX.[DESCRIPTION], SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONSTIT_CTE.EXCLUDESPOUSE), N'{CONDBREAK}', N' ')
end)
end)
end)
when 2 then
@GROUPNOCONTACTCUSTOMNAME
else
CONSTIT_CTE.NAME
end)
else
(case when len(SALUTATION_CTE.CUSTOMNAME) > 0 then
SALUTATION_CTE.CUSTOMNAME
else
(case when isnull(SALUTATION_CTE.ISJOINTFUNCTION, @SALUTATIONFUNCTIONISJOINT) = 1 and CONSTIT_CTE.EXCLUDESPOUSE = 0 and dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(CONSTIT_CTE.CONSTITUENTID, SPOUSECONSTIT.ID, CONSTIT_CTE.GENDERCODE, SPOUSECONSTIT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTIT_CTE.GENDERCODEID, SPOUSECONSTIT.GENDERCODEID) = SPOUSECONSTIT.ID and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @SALUTATIONFUNCTIONID)) = 0 then
--If the joint processor is the spouse, then just swap the params we pass to the function...
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @SALUTATIONFUNCTIONID), SPOUSECONSTIT.ID, SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONSTIT_CTE.KEYNAME, CONSTIT_CTE.FIRSTNAME, CONSTIT_CTE.MIDDLENAME, CONSTIT_CTE.TITLE, CONSTIT_CTE.SUFFIX, 0), N'{CONDBREAK}', N' ')
else
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(SALUTATION_CTE.NAMEFORMATFUNCTIONID, @SALUTATIONFUNCTIONID), CONSTIT_CTE.CONSTITUENTID, CONSTIT_CTE.KEYNAME, CONSTIT_CTE.FIRSTNAME, CONSTIT_CTE.MIDDLENAME, CONSTIT_CTE.TITLE, CONSTIT_CTE.SUFFIX, SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONSTIT_CTE.EXCLUDESPOUSE), N'{CONDBREAK}', N' ')
end)
end)
end) as SALUTATION,
(case
when CONSTIT_CTE.ISORGANIZATION = 1 then
(case when CONSTIT_CTE.CONTACTID is null then
--No contact found
null
else
(case when len(ADDRESSEE_CTE.CUSTOMNAME) > 0 then
ADDRESSEE_CTE.CUSTOMNAME
else
(case when isnull(ADDRESSEE_CTE.ISJOINTFUNCTION, @CONTACTADDRESSEEFUNCTIONISJOINT) = 1 and CONSTIT_CTE.EXCLUDESPOUSE = 0 and dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(CONSTIT_CTE.CONTACTID, SPOUSECONSTIT.ID, CONSTIT_CTE.GENDERCODE, SPOUSECONSTIT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTIT_CTE.GENDERCODEID, SPOUSECONSTIT.GENDERCODEID) = SPOUSECONSTIT.ID and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @CONTACTADDRESSEEFUNCTIONID)) = 0 then
--If the joint processor is the spouse, then just swap the params we pass to the function...
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @CONTACTADDRESSEEFUNCTIONID), SPOUSECONSTIT.ID, SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONTACTCONSTIT.KEYNAME, CONTACTCONSTIT.FIRSTNAME, CONTACTCONSTIT.MIDDLENAME, CONTACTTITLE.[DESCRIPTION], CONTACTSUFFIX.[DESCRIPTION], 0), N'{CONDBREAK}', N' ')
else
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @CONTACTADDRESSEEFUNCTIONID), CONSTIT_CTE.CONTACTID, CONTACTCONSTIT.KEYNAME, CONTACTCONSTIT.FIRSTNAME, CONTACTCONSTIT.MIDDLENAME, CONTACTTITLE.[DESCRIPTION], CONTACTSUFFIX.[DESCRIPTION], SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONSTIT_CTE.EXCLUDESPOUSE), N'{CONDBREAK}', N' ')
end)
end)
end)
when CONSTIT_CTE.ISGROUP = 1 then
(case when CONSTIT_CTE.MEMBERID is null then
--No group members found
null
else
(case when len(ADDRESSEE_CTE.CUSTOMNAME) > 0 then
ADDRESSEE_CTE.CUSTOMNAME
else
(case when isnull(ADDRESSEE_CTE.ISJOINTFUNCTION, @GROUPADDRESSEEFUNCTIONISJOINT) = 1 and CONSTIT_CTE.EXCLUDESPOUSE = 0 and dbo.UFN_MAILING_GETNAMEFORMATS_JOINTPROCESSOR(CONSTIT_CTE.MEMBERID, SPOUSECONSTIT.ID, CONSTIT_CTE.GENDERCODE, SPOUSECONSTIT.GENDERCODE, @JOINTRULETYPECODE, @JOINTSELECTIONID, @JOINTSELECTIONBOTHRULETYPECODE, @JOINTSELECTIONNEITHERRULETYPECODE, CONSTIT_CTE.GENDERCODEID, SPOUSECONSTIT.GENDERCODEID) = SPOUSECONSTIT.ID and dbo.[UFN_NAMEFORMATFUNCTION_ISJOINTSPOUSEFIELDFIRST](isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @GROUPADDRESSEEFUNCTIONID)) = 0 then
--If the joint processor is the spouse, then just swap the params we pass to the function...
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @GROUPADDRESSEEFUNCTIONID), SPOUSECONSTIT.ID, SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], GROUPCONSTIT.KEYNAME, GROUPCONSTIT.FIRSTNAME, GROUPCONSTIT.MIDDLENAME, GROUPTITLE.[DESCRIPTION], GROUPSUFFIX.[DESCRIPTION], 0), N'{CONDBREAK}', N' ')
else
--CONDBREAK is not currently handled anywhere, so just remove it, at least for now.
replace(dbo.UFN_BUILDNAMEFORMAT_INTERNAL(isnull(ADDRESSEE_CTE.NAMEFORMATFUNCTIONID, @GROUPADDRESSEEFUNCTIONID), CONSTIT_CTE.MEMBERID, GROUPCONSTIT.KEYNAME, GROUPCONSTIT.FIRSTNAME, GROUPCONSTIT.MIDDLENAME, GROUPTITLE.[DESCRIPTION], GROUPSUFFIX.[DESCRIPTION], SPOUSECONSTIT.KEYNAME, SPOUSECONSTIT.FIRSTNAME, SPOUSECONSTIT.MIDDLENAME, SPOUSETITLE.[DESCRIPTION], SPOUSESUFFIX.[DESCRIPTION], CONSTIT_CTE.EXCLUDESPOUSE), N'{CONDBREAK}', N' ')
end)
end)
end)
else
null
end) as CONTACT
from CONSTIT_CTE
left join ADDRESSEE_CTE on ADDRESSEE_CTE.CONSTITUENTID = CONSTIT_CTE.CONSTITUENTID and ADDRESSEE_CTE.MAILTOCONSTITUENTID = (case when CONSTIT_CTE.ISORGANIZATION = 1 then CONSTIT_CTE.CONTACTID when CONSTIT_CTE.ISGROUP = 1 then CONSTIT_CTE.MEMBERID else CONSTIT_CTE.CONSTITUENTID end)
left join SALUTATION_CTE on SALUTATION_CTE.CONSTITUENTID = CONSTIT_CTE.CONSTITUENTID and SALUTATION_CTE.MAILTOCONSTITUENTID = (case when CONSTIT_CTE.ISORGANIZATION = 1 then CONSTIT_CTE.CONTACTID when CONSTIT_CTE.ISGROUP = 1 then CONSTIT_CTE.MEMBERID else CONSTIT_CTE.CONSTITUENTID end)
left join dbo.DECEASEDCONSTITUENT as GROUPDECEASEDCONSTIT on GROUPDECEASEDCONSTIT.ID = CONSTIT_CTE.MEMBERID and CONSTIT_CTE.ISGROUP = 1
left join dbo.CONSTITUENT as GROUPCONSTIT on GROUPCONSTIT.ID = CONSTIT_CTE.MEMBERID and CONSTIT_CTE.ISGROUP = 1 and GROUPDECEASEDCONSTIT.ID is null and GROUPCONSTIT.ISINACTIVE = 0
left join dbo.TITLECODE as GROUPTITLE on GROUPTITLE.ID = GROUPCONSTIT.TITLECODEID and CONSTIT_CTE.ISGROUP = 1
left join dbo.SUFFIXCODE as GROUPSUFFIX on GROUPSUFFIX.ID = GROUPCONSTIT.SUFFIXCODEID and CONSTIT_CTE.ISGROUP = 1
left join dbo.DECEASEDCONSTITUENT as CONTACTDECEASEDCONSTIT on CONTACTDECEASEDCONSTIT.ID = CONSTIT_CTE.CONTACTID and CONSTIT_CTE.ISORGANIZATION = 1
left join dbo.CONSTITUENT as CONTACTCONSTIT on CONTACTCONSTIT.ID = CONSTIT_CTE.CONTACTID and CONSTIT_CTE.ISORGANIZATION = 1 and CONTACTDECEASEDCONSTIT.ID is null and CONTACTCONSTIT.ISINACTIVE = 0
left join dbo.TITLECODE as CONTACTTITLE on CONTACTTITLE.ID = CONTACTCONSTIT.TITLECODEID and CONSTIT_CTE.ISORGANIZATION = 1
left join dbo.SUFFIXCODE as CONTACTSUFFIX on CONTACTSUFFIX.ID = CONTACTCONSTIT.SUFFIXCODEID and CONSTIT_CTE.ISORGANIZATION = 1
left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = (case when CONSTIT_CTE.ISGROUP = 1 then CONSTIT_CTE.MEMBERID when CONSTIT_CTE.ISORGANIZATION = 1 then CONSTIT_CTE.CONTACTID else CONSTIT_CTE.CONSTITUENTID end) and RELATIONSHIP.ISSPOUSE = 1
left join dbo.DECEASEDCONSTITUENT as SPOUSEDECEASEDCONSTIT on SPOUSEDECEASEDCONSTIT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
left join dbo.CONSTITUENT as SPOUSECONSTIT on SPOUSECONSTIT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID and SPOUSEDECEASEDCONSTIT.ID is null and SPOUSECONSTIT.ISINACTIVE = 0
left join dbo.TITLECODE as SPOUSETITLE on SPOUSETITLE.ID = SPOUSECONSTIT.TITLECODEID
left join dbo.SUFFIXCODE as SPOUSESUFFIX on SPOUSESUFFIX.ID = SPOUSECONSTIT.SUFFIXCODEID;
--Create an index on the final results...
create nonclustered index [IX_TEMP_MAILING_NAMEFORMATS_CONSTITUENTID_MAILTOCONSTITUENTID] on #TEMP_MAILING_NAMEFORMATS ([CONSTITUENTID], [MAILTOCONSTITUENTID])
include ([ADDRESSEE], [SALUTATION], [CONTACT]);
--Debug only
--set @STATUS = 'Final results... ' + cast((select count(*) from #TEMP_MAILING_NAMEFORMATS) 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 #TEMPNAMEFORMAT;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
if object_id('tempdb..#TEMPCONSTITUENT') is not null
drop table #TEMPCONSTITUENT;
if object_id('tempdb..#TEMPNAMEFORMAT') is not null
drop table #TEMPNAMEFORMAT;
return 1;
end catch
return 0;