V_QUERY_ADDRESSPROCESSING_ADDRESSES
This provides the ability to query for address information using address processing parameters.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ADDRESSID | uniqueidentifier | Address ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| ADDRESSTYPECODEID_TRANSLATION | nvarchar(100) | yes | Type |
| ISPRIMARY | bit | Primary address | |
| DONOTMAIL | bit | Do not mail | |
| STARTDATE | UDT_MONTHDAY | Seasonal start date | |
| ENDDATE | UDT_MONTHDAY | Seasonal end date | |
| COUNTRYID_TRANSLATION | nvarchar(100) | yes | Country |
| COUNTRYID_ABBREVIATION | nvarchar(5) | yes | Country abbreviation |
| ADDRESSBLOCK | nvarchar(150) | Address | |
| CITY | nvarchar(50) | City | |
| STATEID_TRANSLATION | nvarchar(100) | yes | State |
| STATEID_ABBREVIATION | nvarchar(50) | yes | State abbreviation |
| POSTCODE | nvarchar(12) | ZIP | |
| CART | nvarchar(10) | CART | |
| DPC | nvarchar(8) | DPC | |
| LOT | nvarchar(5) | LOT | |
| COUNTYCODEID_TRANSLATION | nvarchar(100) | yes | County |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| DONOTMAILREASONCODEID_TRANSLATION | nvarchar(100) | yes | Reason to not mail |
| FULLADDRESS | nvarchar(300) | yes | Full Address |
| HISTORICALSTARTDATE | date | yes | Start date |
| HISTORICALENDDATE | date | yes | End date |
| ISCONFIDENTIAL | bit | Confidential | |
| RELATIONSHIPID | uniqueidentifier | yes | Relationship ID |
| ADDRESSEE | nvarchar(700) | yes | Addressee |
| SALUTATION | nvarchar(700) | yes | Salutation |
| CONTACT | nvarchar(700) | yes | Contact addressee |
| MAILTOCONSTITUENTID | uniqueidentifier | yes | Mail to constituent ID |
| POSITION | nvarchar(100) | yes | Position |
| CONTACTID | uniqueidentifier | yes | Contact ID |
| ADDRESSLINE1 | nvarchar(150) | yes | |
| ADDRESSLINE2 | nvarchar(150) | yes | |
| ADDRESSLINE3 | nvarchar(150) | yes | |
| ADDRESSLINE4 | nvarchar(150) | yes | |
| ADDRESSLINE5 | nvarchar(150) | yes | |
| CONTACTORADDRESSEE | nvarchar(700) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/1/2024 11:51:28 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3800.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_ADDRESSPROCESSING_ADDRESSES AS
select
[ADDRESS].[ID] as [ADDRESSID],
[ADDRESS].[CONSTITUENTID],
[ADDRESSTYPECODE].[DESCRIPTION] as [ADDRESSTYPECODEID_TRANSLATION],
[ADDRESS].[ISPRIMARY],
[ADDRESS].[DONOTMAIL],
[ADDRESS].[STARTDATE],
[ADDRESS].[ENDDATE],
[COUNTRY].[DESCRIPTION] as [COUNTRYID_TRANSLATION],
[COUNTRY].[ABBREVIATION] as [COUNTRYID_ABBREVIATION],
[ADDRESS].[ADDRESSBLOCK],
[ADDRESS].[CITY],
[STATE].[DESCRIPTION] as [STATEID_TRANSLATION],
[STATE].[ABBREVIATION] as [STATEID_ABBREVIATION],
[ADDRESS].[POSTCODE],
[ADDRESS].[CART],
[ADDRESS].[DPC],
[ADDRESS].[LOT],
[COUNTYCODE].[DESCRIPTION] as [COUNTYCODEID_TRANSLATION],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[ADDRESS].[DATEADDED],
[ADDRESS].[DATECHANGED],
[ADDRESS].[TSLONG],
[DONOTMAILREASONCODE].[DESCRIPTION] as [DONOTMAILREASONCODEID_TRANSLATION],
dbo.[UFN_BUILDFULLADDRESS]([ADDRESS].[ID], [ADDRESS].[ADDRESSBLOCK], [ADDRESS].[CITY], [STATE].[ID], [ADDRESS].[POSTCODE], [COUNTRY].[ID]) as [FULLADDRESS],
[ADDRESS].[HISTORICALSTARTDATE],
[ADDRESS].[HISTORICALENDDATE],
[ADDRESS].[ISCONFIDENTIAL],
[ADDRESS].[RELATIONSHIPID],
-- these fields are actually returned as part of address processing stored procedures
cast('' as nvarchar(700)) as [ADDRESSEE],
cast('' as nvarchar(700)) as [SALUTATION],
cast('' as nvarchar(700)) as [CONTACT],
cast(null as uniqueidentifier) as [MAILTOCONSTITUENTID],
--HOUSEHOLDID
--GROUPCONTACTID
cast('' as nvarchar(100)) as [POSITION],
cast(null as uniqueidentifier) as [CONTACTID],
[ADDRLINES].[ADDRESSLINE1],
[ADDRLINES].[ADDRESSLINE2],
[ADDRLINES].[ADDRESSLINE3],
[ADDRLINES].[ADDRESSLINE4],
[ADDRLINES].[ADDRESSLINE5],
-- this field is also actually returned as part of address processing stored procedures
cast('' as nvarchar(700)) as [CONTACTORADDRESSEE]
/*#EXTENSION*/
from dbo.[ADDRESS]
left outer join dbo.[ADDRESSTYPECODE] on [ADDRESSTYPECODE].[ID] = [ADDRESS].[ADDRESSTYPECODEID]
left outer join dbo.[COUNTRY] on [COUNTRY].[ID] = [ADDRESS].[COUNTRYID]
left outer join dbo.[STATE] on [STATE].[ID] = [ADDRESS].[STATEID]
--Since we are using a SQL CLR function, the optimizer cannot figure that out the table function only returns one row, and so the optimizer
--will include this "outer apply" in even the simplest execution plans and hurt performance. To remedy the situation, we are using a "top 1"
--clause to help out the optimizer so that it doesn't execute the function unless you return one of the address line fields.
outer apply (select top 1 [ADDRESSLINE1], [ADDRESSLINE2], [ADDRESSLINE3], [ADDRESSLINE4], [ADDRESSLINE5] from dbo.[UFN_ADDRESS_GETADDRESSLINES]([ADDRESS].[ADDRESSBLOCK])) as [ADDRLINES]
left outer join dbo.[ADDRESSVALIDATIONUPDATE] on [ADDRESS].[ID] = [ADDRESSVALIDATIONUPDATE].[ID]
left outer join dbo.[COUNTYCODE] on [ADDRESSVALIDATIONUPDATE].[COUNTYCODEID] = [COUNTYCODE].[ID]
left outer join dbo.[DONOTMAILREASONCODE] on [DONOTMAILREASONCODE].[ID] = [ADDRESS].[DONOTMAILREASONCODEID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [ADDRESS].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [ADDRESS].[CHANGEDBYID]