|
Column name
|
PII?
|
Column definition
|
Data type
|
Business rule
|
|
individual_business_unit_id
|
No
|
Identifies unique association between an individual and business unit.
|
BIGINT
|
Distinct individual_business_unit_id from ir.MATCH_INSTANCE.
|
|
full_name
|
Yes
|
Full name.
|
NVARCHAR(200)
|
Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.
|
|
prefix_name
|
Yes
|
A prefix portion of the name (Mr.).
|
NVARCHAR(32)
|
Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.
|
|
title
|
Yes
|
Individual's title (Dr.).
|
NVARCHAR(30)
|
Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.
|
|
first_name
|
Yes
|
Individual's first name.
|
NVARCHAR(60)
|
Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.
|
|
middle_name
|
Yes
|
Individual's middle name.
|
NVARCHAR(40)
|
Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.
|
|
last_name
|
Yes
|
Individual's last name.
|
NVARCHAR(70)
|
Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.
|
|
suffix_name
|
Yes
|
Individual's suffix name (Jr.).
|
NVARCHAR(15)
|
Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.
|
|
email_address
|
Yes
|
Email address that has been determined as the best email for this individual/business unit.
|
NVARCHAR(320)
|
Select email_id(s) and email_address(s) from INDIVIDUAL_EMAIL_XREF that is in the same record as individual_business_unit_id:
-
IF one record returned, assign this email.
-
IF more than one email has been found, then LEFT join to CONTACT_AUTH_EMAIL_SUMMARY on email_id:
-
IF only one email found that has OPT_IN value in auth_code, select that email.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL (no contact auth rec) join to EMAIL_SUMMARY on email_id with (max(last_open_datetime);
or max(last_click_thru_datetime) - email that was most recently opened/clicked else IF email_id is not found in EMAIL_SUMMARY pick the one from INDIVIDUAL_EMAIL_XREF with max(original_create_datetime).
|
|
home_phone_num
|
Yes
|
Home phone that has been determined as the best home phone for this individual/business unit.
|
NVARCHAR(32)
|
Select phone_num from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code = home and deactivated_ind <> y:
-
IF one record returned, assign this phone.
-
IF more than one phone has been found, then LEFT join to CONTACT_AUTH_PHONE_SUMMARY on phone_id:
-
IF only one phone found that has OPT_IN value in auth_code, select that phone.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).
|
|
mobile_phone_num
|
Yes
|
Mobile phone that has been determined as the best home phone for this individual/business unit.
|
NVARCHAR(32)
|
Select phone_num from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code = mobile and deactivated_ind <> y:
-
IF one record returned, assign this phone.
-
IF more than one phone has been found, then LEFT join to CONTACT_AUTH_SMS_SUMMARY on phone_id:
-
IF only one phone found that has OPT_IN value in auth_code, select that phone.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).
|
|
work_phone_num
|
Yes
|
Work phone that has been determined as the best home phone for this individual/business unit.
|
NVARCHAR(32)
|
Select phone_num from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code = business and deactivated_ind <> y:
-
IF one record returned, assign this phone.
-
IF more than one phone has been found, then LEFT join to CONTACT_AUTH_PHONE_SUMMARY on phone_id:
-
IF only one phone found that has OPT_IN value in auth_code, select that phone.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).
|
|
other_phone_num
|
Yes
|
Other phone number that has been determined as the best phone to contact and is not home/mobile/work for this individual/business unit.
|
NVARCHAR(32)
|
Select phone_num, phone_type_code from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code not in (home, mobile, business) and deactivated_ind <> y:
-
IF one record returned, assign this phone.
-
IF more than one phone has been found, then LEFT join to CONTACT_AUTH_PHONE_SUMMARY on phone_id:
-
IF only one phone found that has OPT_IN value in auth_code, select that phone.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).
|
|
other_phone_num_type_code
|
No
|
Identifies type of the phone number associated with the individual_business_unit that is other than home/mobile/work that has been determined as the best phone to contact.
|
NVARCHAR(50)
|
Select phone_num, phone_type_code from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code not in (home, mobile, business) and deactivated_ind <> y:
-
IF one record returned, assign this phone.
-
IF more than one phone has been found, then LEFT join to CONTACT_AUTH_PHONE_SUMMARY on phone_id:
-
IF only one phone found that has OPT_IN value in auth_code, select that phone.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).
|
|
address_1
|
Yes
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(128)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
address_2
|
Yes
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(128)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
address_3
|
Yes
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(128)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
address_4
|
Yes
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(128)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
address_5
|
Yes
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(128)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
address_6
|
Yes
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(128)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
address_7
|
Yes
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(128)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
suite
|
Yes
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(32)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
city
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(64)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
state_province
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(64)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
postal_code_1
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(16)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
postal_code_2
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(16)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
county_name
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(64)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
iso_2_country_code
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(2)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
country_name
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
NVARCHAR(150)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
latitude
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
DECIMAL(10, 7)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
|
longitude
|
No
|
Address information that has been determined as the best mailing address for this individual/business unit.
|
DECIMAL(10, 7)
|
Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:
-
IF one record returned, assign this address.
-
IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:
-
IF only one address found that has OPT_IN value in auth_code, select that address.
-
IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).
|
