UFN_BBNC_CONSTITDATA_MULTIPLE
Returns constituent data for a list of constituent IDs from Blackbaud Internet Solutions.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @IDLISTXML | xml | IN | |
| @ORDERBYIDS | bit | IN | |
| @BUSINESSPHONETYPECODEMAPID | int | IN |
Definition
Copy
CREATE function dbo.UFN_BBNC_CONSTITDATA_MULTIPLE
(
@IDLISTXML xml,
@ORDERBYIDS bit,
@BUSINESSPHONETYPECODEMAPID int = null
)
returns @CONSTITDATA table
(
CONSTIT_BBNCID int,
CONSTIT_BIRTHDATE dbo.UDT_FUZZYDATE,
CONSTIT_FIRSTNAME nvarchar(100),
CONSTIT_NAME nvarchar(300),
CONSTIT_GENDER nvarchar(20),
CONSTIT_KEYNAME nvarchar(100),
CONSTIT_MAIDENNAME nvarchar(100),
CONSTIT_MIDDLENAME nvarchar(100),
CONSTIT_NICKNAME nvarchar(100),
CONSTIT_TITLECODE nvarchar(50),
CONSTIT_TITLE2CODE nvarchar(50),
CONSTIT_SUFFIXCODE nvarchar(50),
CONSTIT_SUFFIX2CODE nvarchar(50),
CONSTIT_REQUESTNOEMAIL bit,
CONSTIT_LOOKUPID nvarchar(100),
CONSTIT_MARITALSTATUS nvarchar(50),
ALUMNI_CLASSOF dbo.UDT_YEAR,
ALUMNI_DATE_ENTERED dbo.UDT_FUZZYDATE,
ALUMNI_DATE_LEFT dbo.UDT_FUZZYDATE,
ALUMNI_DEGREE nvarchar(100),
ALUMNI_FRAT_SOR nvarchar(100),
ALUMNI_GPA decimal(3, 2),
ALUMNI_KNOWN_NAME nvarchar(100),
ALUMNI_SCHOOL_NAME nvarchar(100),
ALUMNI_SCHOOL_TYPE nvarchar(100),
ALUMNI_STATUS nvarchar(100),
ALUMNI_MAJOR nvarchar(50),
ALUMNI_MINOR nvarchar(50),
ADDRESS_COUNTRY nvarchar(100),
ADDRESS_COUNTRY_ABBREVIATION nvarchar(20),
ADDRESS_ADDRESSBLOCK nvarchar(150),
ADDRESS_CITY nvarchar(50),
ADDRESS_STATE nvarchar(50),
ADDRESS_POSTCODE nvarchar(20),
ADDRESS_FORMATTED_ADDRESS nvarchar(300),
ADDRESS_COUNTRYADDRESSFORMATID uniqueidentifier,
EMAIL_TYPE nvarchar(50),
EMAIL_ADDRESS nvarchar(200),
PHONE_TYPE nvarchar(50),
PHONE_NUMBER nvarchar(100),
BUSINESS_NAME nvarchar(100),
BUSINESS_POSITION nvarchar(100),
BUSINESS_INDUSTRY nvarchar(50),
BUSINESS_COUNTRY nvarchar(100),
BUSINESS_COUNTRY_ABBREVIATION nvarchar(20),
BUSINESS_ADDRESSBLOCK nvarchar(150),
BUSINESS_CITY nvarchar(50),
BUSINESS_STATE nvarchar(50),
BUSINESS_POSTCODE nvarchar(20),
BUSINESS_FORMATTED_ADDRESS nvarchar(300),
BUSINESS_COUNTRYADDRESSFORMATID uniqueidentifier,
BUSINESS_PHONE_TYPE nvarchar(50),
BUSINESS_PHONE_NUMBER nvarchar(100),
SPOUSE_TITLECODE nvarchar(50),
SPOUSE_TITLE2CODE nvarchar(50),
SPOUSE_FIRSTNAME nvarchar(100),
SPOUSE_MIDDLENAME nvarchar(100),
SPOUSE_KEYNAME nvarchar(100),
SPOUSE_MAIDENNAME nvarchar(100),
SPOUSE_SUFFIXCODE nvarchar(50),
SPOUSE_SUFFIX2CODE nvarchar(50),
SPOUSE_CLASSOF dbo.UDT_FUZZYDATE,
EMAIL_ADDRESS_SYSTEM_ID int
)
as
begin
declare @IDLIST table
(
ID int,
SEQUENCE int
);
insert into @IDLIST(ID, SEQUENCE)
select T.c.value('(ID)[1]', 'int') as [ID],
T.c.value('(SEQUENCE)[1]', 'int') as [SEQUENCE]
from @IDLISTXML.nodes('/IDLIST/ITEM') T(c)
--purposefully using DATE
declare @CURRENTDATE date;
set @CURRENTDATE = getdate();
declare @BUSINESSADDRESSTYPECODEID uniqueidentifier;
declare @BUSINESSPHONETYPECODEID uniqueidentifier;
declare @DONOTEMAILSOLICITCODEID uniqueidentifier;
select top 1
@BUSINESSADDRESSTYPECODEID = coalesce(BUSINESSADDRESSTYPECODEID, '00000000-0000-0000-0000-000000000000'),
@DONOTEMAILSOLICITCODEID = DONOTEMAILSOLICITCODEID
from dbo.NETCOMMUNITYDEFAULTCODEMAP;
set @BUSINESSPHONETYPECODEID = '00000000-0000-0000-0000-000000000000';
select top (1)
@BUSINESSPHONETYPECODEID = PHONETYPECODE.ID
from
dbo.BBNCCODETABLEIDMAP
inner join dbo.PHONETYPECODE on BBNCCODETABLEIDMAP.TABLEENTRYID = PHONETYPECODE.ID
where
BBNCCODETABLEIDMAP.ID = @BUSINESSPHONETYPECODEMAPID
order by
PHONETYPECODE.SEQUENCE;
insert into @CONSTITDATA(CONSTIT_BBNCID, CONSTIT_BIRTHDATE, CONSTIT_FIRSTNAME, CONSTIT_NAME, CONSTIT_GENDER, CONSTIT_KEYNAME, CONSTIT_MAIDENNAME, CONSTIT_MIDDLENAME, CONSTIT_NICKNAME,
CONSTIT_TITLECODE, CONSTIT_TITLE2CODE, CONSTIT_SUFFIXCODE, CONSTIT_SUFFIX2CODE, CONSTIT_REQUESTNOEMAIL, CONSTIT_LOOKUPID, CONSTIT_MARITALSTATUS,
ALUMNI_CLASSOF, ALUMNI_DATE_ENTERED, ALUMNI_DATE_LEFT, ALUMNI_DEGREE, ALUMNI_FRAT_SOR,
ALUMNI_GPA, ALUMNI_KNOWN_NAME, ALUMNI_SCHOOL_NAME, ALUMNI_SCHOOL_TYPE, ALUMNI_STATUS, ALUMNI_MAJOR, ALUMNI_MINOR,
ADDRESS_COUNTRY, ADDRESS_COUNTRY_ABBREVIATION, ADDRESS_ADDRESSBLOCK, ADDRESS_CITY, ADDRESS_STATE, ADDRESS_POSTCODE,
ADDRESS_FORMATTED_ADDRESS, ADDRESS_COUNTRYADDRESSFORMATID,
EMAIL_TYPE, EMAIL_ADDRESS, EMAIL_ADDRESS_SYSTEM_ID,
PHONE_TYPE, PHONE_NUMBER,
BUSINESS_NAME, BUSINESS_POSITION, BUSINESS_INDUSTRY, BUSINESS_COUNTRY, BUSINESS_COUNTRY_ABBREVIATION,
BUSINESS_ADDRESSBLOCK, BUSINESS_CITY, BUSINESS_STATE, BUSINESS_POSTCODE, BUSINESS_FORMATTED_ADDRESS,
BUSINESS_COUNTRYADDRESSFORMATID,
BUSINESS_PHONE_TYPE, BUSINESS_PHONE_NUMBER,
SPOUSE_TITLECODE, SPOUSE_TITLE2CODE, SPOUSE_FIRSTNAME, SPOUSE_MIDDLENAME, SPOUSE_KEYNAME, SPOUSE_MAIDENNAME, SPOUSE_SUFFIXCODE, SPOUSE_SUFFIX2CODE,
SPOUSE_CLASSOF)
select
CONSTITUENT.SEQUENCEID as [ID],
CONSTITUENT.BIRTHDATE,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.NAME,
CONSTITUENT.GENDER,
CONSTITUENT.KEYNAME,
CONSTITUENT.MAIDENNAME,
CONSTITUENT.MIDDLENAME,
CONSTITUENT.NICKNAME,
TITLECODE.DESCRIPTION as TITLECODE,
TITLE2CODE.DESCRIPTION as TITLE2CODE,
SUFFIXCODE.DESCRIPTION as SUFFIXCODE,
SUFFIX2CODE.DESCRIPTION as SUFFIX2CODE,
--check for the appropriate solicit code for REQUESTNOEMAIL
case when (csc.ID is null) then 0 else 1 end as REQUESTNOEMAIL,
CONSTITUENT.LOOKUPID,
MARITALSTATUSCODE.DESCRIPTION as MARITALSTATUSCODE,
coalesce(EDUCATIONALHISTORY.CLASSOF, 0),
coalesce(EDUCATIONALHISTORY.STARTDATE, '00000000'),
coalesce(EDUCATIONALHISTORY.ENDDATE, '00000000'),
EDUCATIONALDEGREECODE.DESCRIPTION,
EDUCATIONALHISTORY.FRATERNITY,
EDUCATIONALHISTORY.GPA,
EDUCATIONALHISTORY.KNOWNNAME,
EDUCATIONALINSTITUTION.NAME,
EDUCATIONALHISTORYTYPECODE.DESCRIPTION,
EDUCATIONALHISTORY.CONSTITUENCYSTATUS,
EDUCATIONALMAJORCODE.DESCRIPTION,
EDUCATIONALMINORCODE.DESCRIPTION,
COUNTRY.DESCRIPTION,
COUNTRY.ABBREVIATION,
ADDRESS.ADDRESSBLOCK,
ADDRESS.CITY,
case
--TMV 04/11/2007 CR272266-040907 BBNC expects the full description for New Zealand city, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.
--TMV 04/13/2007 CR272671-041207 BBNC expects the full description for United Kingdom county, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.
when
(
COUNTRY.COUNTRYADDRESSFORMATID = '959809FB-8FA7-4A19-888F-9951BD9B29D3' --United Kingdom
or
COUNTRY.COUNTRYADDRESSFORMATID = 'A3B050A5-E1C8-4E1B-99AE-40E9FCADA0BC' --New Zealand
)
then
STATE.DESCRIPTION
else
STATE.ABBREVIATION
end,
ADDRESS.POSTCODE,
dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCk, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
COUNTRY.COUNTRYADDRESSFORMATID,
EMAILADDRESSTYPECODE.DESCRIPTION,
EMAILADDRESS.EMAILADDRESS,
EMAILMAP.ID,
PHONETYPECODE.DESCRIPTION,
PHONE.NUMBER,
[BUSINESS].NAME,
[BUSINESSRELATIONSHIPJOBINFO].JOBTITLE [POSITION],
INDUSTRYCODE.DESCRIPTION,
[BUSINESSCOUNTRY].DESCRIPTION,
[BUSINESSCOUNTRY].ABBREVIATION,
[BUSINESSADDRESS].ADDRESSBLOCK,
[BUSINESSADDRESS].CITY,
case
--TMV 04/11/2007 CR272266-040907 BBNC expects the full description for New Zealand city, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.
--TMV 04/13/2007 CR272671-041207 BBNC expects the full description for United Kingdom county, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.
when
(
[BUSINESSCOUNTRY].COUNTRYADDRESSFORMATID = '959809FB-8FA7-4A19-888F-9951BD9B29D3' --United Kingdom
or
[BUSINESSCOUNTRY].COUNTRYADDRESSFORMATID = 'A3B050A5-E1C8-4E1B-99AE-40E9FCADA0BC' --New Zealand
)
then
[BUSINESSSTATE].DESCRIPTION
else
[BUSINESSSTATE].ABBREVIATION
end,
[BUSINESSADDRESS].POSTCODE,
dbo.UFN_BUILDFULLADDRESS([BUSINESSADDRESS].ID, [BUSINESSADDRESS].ADDRESSBLOCK, [BUSINESSADDRESS].CITY, [BUSINESSADDRESS].STATEID, [BUSINESSADDRESS].POSTCODE, [BUSINESSADDRESS].COUNTRYID),
[BUSINESSCOUNTRY].COUNTRYADDRESSFORMATID,
[BUSINESSPHONETYPECODE].DESCRIPTION,
[BUSINESSPHONE].NUMBER,
[SPOUSETITLECODE].DESCRIPTION,
[SPOUSETITLE2CODE].DESCRIPTION,
[SPOUSE].FIRSTNAME,
[SPOUSE].MIDDLENAME,
[SPOUSE].KEYNAME,
[SPOUSE].MAIDENNAME,
[SPOUSESUFFIXCODE].DESCRIPTION,
[SPOUSESUFFIX2CODE].DESCRIPTION,
coalesce([SPOUSEEDUCATION].CLASSOF, 0)
from dbo.CONSTITUENT
inner join @IDLIST as [IDLIST] on [IDLIST].ID = CONSTITUENT.SEQUENCEID
left join dbo.TITLECODE on TITLECODE.ID = CONSTITUENT.TITLECODEID
left join dbo.TITLECODE TITLE2CODE on TITLECODE.ID = CONSTITUENT.TITLE2CODEID
left join dbo.SUFFIXCODE on SUFFIXCODE.ID = CONSTITUENT.SUFFIXCODEID
left join dbo.SUFFIXCODE SUFFIX2CODE on SUFFIXCODE.ID = CONSTITUENT.SUFFIX2CODEID
left join dbo.MARITALSTATUSCODE on MARITALSTATUSCODE.ID = CONSTITUENT.MARITALSTATUSCODEID
left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
left join dbo.STATE on STATE.ID = ADDRESS.STATEID
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
left join dbo.EMAILADDRESSTYPECODE on EMAILADDRESSTYPECODE.ID = EMAILADDRESS.EMAILADDRESSTYPECODEID
left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
left join dbo.PHONETYPECODE on PHONETYPECODE.ID = PHONE.PHONETYPECODEID
left join dbo.RELATIONSHIP as [BUSINESSRELATIONSHIP] on [BUSINESSRELATIONSHIP].RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and [BUSINESSRELATIONSHIP].ISPRIMARYBUSINESS = 1 and CONSTITUENT.ISORGANIZATION = 0
-- Note: Contrary to expectations, an organization constituent can have more than 1 relationship with ISPRIMARYBUSINESSRELATIONSHIP = 1.
-- So we need to pick the top 1. We can skip this check for individual constituents; a trigger on the relationship table will ensure that
-- individuals have at most 1 primary business relationship.
and (CONSTITUENT.ISORGANIZATION = 0 or [BUSINESSRELATIONSHIP].ID in (select top 1 ID from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and RELATIONSHIP.ISPRIMARYBUSINESS = 1))
left join dbo.RELATIONSHIPJOBINFO as [BUSINESSRELATIONSHIPJOBINFO] on [BUSINESSRELATIONSHIP].RELATIONSHIPSETID = [BUSINESSRELATIONSHIPJOBINFO].RELATIONSHIPSETID
and @CURRENTDATE between coalesce([BUSINESSRELATIONSHIPJOBINFO].STARTDATE, @CURRENTDATE) and coalesce([BUSINESSRELATIONSHIPJOBINFO].ENDDATE, @CURRENTDATE)
left join dbo.CONSTITUENT as [BUSINESS] on [BUSINESS].ID = [BUSINESSRELATIONSHIP].RECIPROCALCONSTITUENTID
left join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = [BUSINESS].ID
left join dbo.INDUSTRYCODE on INDUSTRYCODE.ID = ORGANIZATIONDATA.INDUSTRYCODEID
left join dbo.ADDRESS as [BUSINESSADDRESS] on -- Pull the address from the constituent, not their business. A constituent can have more than one address with the same type, choose the match that would appear first in the address data list.
[BUSINESSADDRESS].ID in (select top 1 ID from dbo.ADDRESS where CONSTITUENTID = CONSTITUENT.ID and ADDRESSTYPECODEID = @BUSINESSADDRESSTYPECODEID order by ISPRIMARY desc, SEQUENCE)
left join dbo.COUNTRY as [BUSINESSCOUNTRY] on [BUSINESSCOUNTRY].ID = [BUSINESSADDRESS].COUNTRYID
left join dbo.STATE as [BUSINESSSTATE] on [BUSINESSSTATE].ID = [BUSINESSADDRESS].STATEID
left join dbo.PHONE as [BUSINESSPHONE] on -- Pull the phone from the constituent, not their business. A constituent can have more than one phone with the same type, choose the match that would appear first in the phone data list.
[BUSINESSPHONE].ID in (select top 1 ID from dbo.PHONE where CONSTITUENTID = CONSTITUENT.ID and PHONETYPECODEID = @BUSINESSPHONETYPECODEID order by ISPRIMARY desc, SEQUENCE)
left join dbo.PHONETYPECODE as [BUSINESSPHONETYPECODE] on [BUSINESSPHONETYPECODE].ID = [BUSINESSPHONE].PHONETYPECODEID
left join dbo.RELATIONSHIP as [SPOUSERELATIONSHIP] on [SPOUSERELATIONSHIP].RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and [SPOUSERELATIONSHIP].ISSPOUSE = 1
left join dbo.CONSTITUENT as [SPOUSE] on [SPOUSE].ID = [SPOUSERELATIONSHIP].RECIPROCALCONSTITUENTID
left join dbo.TITLECODE as [SPOUSETITLECODE] on [SPOUSETITLECODE].ID = [SPOUSE].TITLECODEID
left join dbo.TITLECODE as [SPOUSETITLE2CODE] on [SPOUSETITLE2CODE].ID = [SPOUSE].TITLE2CODEID
left join dbo.SUFFIXCODE as [SPOUSESUFFIXCODE] on [SPOUSESUFFIXCODE].ID = [SPOUSE].SUFFIXCODEID
left join dbo.SUFFIXCODE as [SPOUSESUFFIX2CODE] on [SPOUSESUFFIX2CODE].ID = [SPOUSE].SUFFIX2CODEID
left join dbo.EDUCATIONALHISTORY as [SPOUSEEDUCATION] on [SPOUSEEDUCATION].CONSTITUENTID = [SPOUSE].ID and [SPOUSEEDUCATION].ISPRIMARYRECORD = 1
left join dbo.EDUCATIONALHISTORY on EDUCATIONALHISTORY.CONSTITUENTID = CONSTITUENT.ID and EDUCATIONALHISTORY.ISPRIMARYRECORD = 1
left join dbo.EDUCATIONALINSTITUTION on EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
left join dbo.EDUCATIONALHISTORYTYPECODE on EDUCATIONALHISTORYTYPECODE.ID = EDUCATIONALHISTORY.EDUCATIONALHISTORYTYPECODEID
left join dbo.EDUCATIONALDEGREECODE on EDUCATIONALDEGREECODE.ID = EDUCATIONALHISTORY.EDUCATIONALDEGREECODEID
left join dbo.EDUCATIONALMAJOR on EDUCATIONALMAJOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID
--Note: There can be multiple major for a single EDUCATIONALHISTORY record. This should pick out the top 1 of those for the join; I'm
--not sure about the performance implications, though. SQLServer should short-circut the 'and' if there's no major for the
--educationalhistory row. The same technique is used for minors below
and EDUCATIONALMAJOR.ID in (select top 1 ID from dbo.EDUCATIONALMAJOR where EDUCATIONALMAJOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID)
left join dbo.EDUCATIONALMINOR on EDUCATIONALMINOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID
and EDUCATIONALMINOR.ID in (select top 1 ID from dbo.EDUCATIONALMINOR where EDUCATIONALMINOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID)
left join dbo.EDUCATIONALMAJORCODE on EDUCATIONALMAJORCODE.ID = EDUCATIONALMAJOR.EDUCATIONALMAJORCODEID
left join dbo.EDUCATIONALMAJORCODE as [EDUCATIONALMINORCODE] on [EDUCATIONALMINORCODE].ID = EDUCATIONALMINOR.EDUCATIONALMAJORCODEID
--Only join if the solicit code is the one that flags do not email.
left join dbo.CONSTITUENTSOLICITCODE as csc on
csc.CONSTITUENTID=CONSTITUENT.ID and
csc.SOLICITCODEID=@DONOTEMAILSOLICITCODEID and
((csc.STARTDATE is null) or (datediff(day, csc.STARTDATE, getdate())>=0)) and
((csc.ENDDATE is null) or (datediff(day, getdate(), csc.ENDDATE)>=0))
left join dbo.BBNCCODETABLEIDMAP EMAILMAP on EMAILMAP.TABLEENTRYID = EMAILADDRESSTYPECODE.ID
order by
case when @ORDERBYIDS = 0 then CONSTITUENT.KEYNAME end,
case when @ORDERBYIDS = 1 then [IDLIST].SEQUENCE end;
--TMV 06/21/2007 CR277591-062007 Separate case statements must be used in a dynamic order by clause with incompatible data types.
return;
end