Address lines construction

US and Canadian addresses can be reconstructed to ensure that they are standardized to support requirements for downstream systems. Most downstream systems map Address Line 1 only and expect it to contain the street name and number. However, address cleansing places the building or organization name in the first few address lines and moves street number, name, and suite number to the last available address line. If this information is removed in downstream systems, samples cannot be delivered. Additional costs and IT overhead might be incurred to resolve the problem. Using this feature to normalize the address lines will support downstream systems and improve data consistency.

Enable the feature

• Canadian addresses - To enable this feature for locally managed Canadian addresses, contact Veeva Support.
• US addresses - Administrators can enable this feature for locally managed US addresses. Go to Settings > General Settings and click the Address lines construction option.

  Tip: The US OpenData team uses this feature to update US addresses. Customers with both locally managed US records and OpenData US records should consider enabling this feature so all US addresses are standardized consistently.

Supported addresses

This feature applies only to US and Canadian addresses.

When the feature is enabled, existing addresses must be updated. See the section below called "Backfilling addresses in your Network instance".

Addresses that are not supported

Address cleansing is not applied to locally managed addresses that are externally mastered (the is_externally_mastered__v field is true) or third party mastered addresses. These types of addresses are not supported for address lines construction.

Note: This feature is not supported if you have the shipping address feature (truncating and wrapping long US addresses for delivery) enabled in your Network instance. Only one address recalculation feature can be enabled in a single Network instance.

Current address mapping example

Today, in Network, after addresses are cleansed, building or organization are placed in Address Line 1. When the record is exported to downstream systems, Address Line 2 is often stripped from the record, so the street number, name and suite number (if available) is lost. Samples cannot be delivered without this information.

Field	Network (after address cleansing)	Downstream system
Address Line 1	Kaiser Permanente Medical Center	Kaiser Permanente Medical Center
Address Line 2	6600 Bruceville Rd
Address Line 3
City	Sacramento	Sacramento
State	CA	CA
Postal Code	95823	95823
Country	US	US

To learn about how addresses are cleansed and parsed, see About address validation and parsing.

Address reconstruction rules

When the feature is enabled for US and/or Canadian addresses in your Network instance, address lines that meet the rule requirements will be reconstructed when they are added (or updated) and verified using data change requests, the profile page, or source subscriptions.

Network uses a third party cleansing tool to verify addresses. The address reconstruction rules run only after addresses are cleansed.

Reconstruction rules are applied if the following requirements are met:

• Address verification status is V (Verified) or A (Ambiguous) or P (Partially Verified).
• Street number, street name, and/or building are populated

Reconstruction rules

• Address line 1 = Street number (premise__v) street name (thoroughfare__v) suite number (sub_building__v)

• Address line 2 = building (building__v). If available.

• Address line 3 = organization (organization__v). If building is not available, move to Address Line 2.

Examples

Review the following examples to see how Network reconstructs the address lines when this feature is enabled.

Example 1

Address Line 1 has a building, and Address Line 2 has a street name and number.

Field	Address Lines (after cleansing)	Reconstructed Address Lines
Address Line 1	Stratford Court Apts	58 N Lansdowne Ave
Address Line 2	58 N Lansdowne Ave	Stratford Court Apts

Example 2

Address Line 1 has a building, and Address Line 2 has a street name, number, and suite number.

Field	Address Lines (after cleansing)	Reconstructed Address Lines
Address Line 1	Gray Street Medical Bldg	210 E Gray St Ste 1105
Address Line 2	210 E Gray St Ste 1105	Gray Street Medical Bldg

Example 3

Address Line 1 has an organization, Address Line 2 has a building, and Address Line 3 street name, number, and suite number.

Field	Address Lines (after cleansing)	Reconstructed Address Lines
Address Line 1	Gulfcoast Veterinary Clinic	15560 McGregor Blvd Ste 3
Address Line 2	15560 McGregor Blvd Ste 3 Bruno Plaza	Bruno Plaza
Address Line 3		Gulfcoast Veterinary Clinic

Backfilling addresses in your Network instance

When this feature is enabled in your Network instance, existing US and Canadian addresses must be updated to ensure that duplicate entities (HCPs, HCOs) are not created. Duplicate entities might be created if locally owned entities are loaded into Network without custom keys, particularly if the subscription's match rules only consider the address_line_1__v field.

Default match rules

In version 19R2.0, the default match rules for the US and Canada were updated to help prevent duplicate entities from being created before you are able to update the existing local addresses in your Network instance. Previously, the default match rules matched only on the address_line_1__v field; they were updated to consider all of the address lines fields.

Updating Veeva OpenData addresses

If you subscribe to Veeva OpenData for the US, those addresses will be reconstructed in batches in the master instance and will be updated in your Network instance. When your Veeva OpenData subscription runs, updated addresses, affected entities (HCPs, HCOs) and all associated sub-objects will be downloaded. Candidate records are excluded from this process.

Updating local addresses

To update the existing US or Canadian local addresses in your Network instance, use reporting to find the affected addresses and then load them into your Network instance using a source subscription so the address lines construction rules will run and update them.

Create a report

Create a report to find the locally owned addresses in your Network instance that have building or organization in Address Line 1 or Address Line 2.

The report must include the following data:

• Entity IDs (VIDs) and Address VIDs - Including the IDs prevents duplicate entities from being created.
• Full address (Address lines 1, 2, and 3; city; state; ZIP; and country) - This ensures that the rules are applied to the existing addresses.

The downloaded results (.csv file) must include the following column headers:

• entity VID
• address VID
• address_line_1__v
• address_line_2__v
• address_line_3__v
• locality__v
• administrative_area__v
• postal_code__v
• country__v

Example

To create a report that meets the data and column header requirements:

1. On the Network menu bar, click Reports > SQL Query Editor.

2. In the text field, paste the following SQL query to retrieve all locally owned valid addresses:

   SELECT address.vid__v,
          address.address_line_1__v,
          address.address_line_2__v,
          address.address_line_3__v,
          address.locality__v,
          address.administrative_area__v,
          address.postal_code__v,
          address.country__v,
          address.organization__v,
          address.building__v,
          address.sub_building__v,
          address.premise__v,
          address.thoroughfare__v,
          address.address_verification_status__v,
          address.entity_type__v,
          address.entity_vid__v
   FROM address
     INNER JOIN hcp ON address.entity_vid__v = hcp.vid__v
   WHERE address.record_state__v = 'VALID'
   AND   hcp.record_state__v = 'VALID'
   AND   address.is_veeva_master__v = 'f'
   AND   (address.address_line_1__v = address.building__v OR address.address_line_1__v = address.organization__v)
   UNION
   SELECT address.vid__v,
          address.address_line_1__v,
          address.address_line_2__v,
          address.address_line_3__v,
          address.locality__v,
          address.administrative_area__v,
          address.postal_code__v,
          address.country__v,
          address.organization__v,
          address.building__v,
          address.sub_building__v,
          address.premise__v,
          address.thoroughfare__v,
          address.address_verification_status__v,
          address.entity_type__v,
          address.entity_vid__v
   FROM address
     INNER JOIN hco ON address.entity_vid__v = hco.vid__v
   WHERE address.record_state__v = 'VALID'
   AND   hco.record_state__v = 'VALID'
   AND   address.is_veeva_master__v = 'f'
   AND   (address.address_line_1__v = address.building__v OR address.address_line_1__v = address.organization__v)

3. Click Run.
4. In the Results section, review the returned addresses.
5. To save the report on your local computer, click Download. (The .csv file that is created will be referred to here as "File A").

Import the addresses into Network

Update the addresses in File A (if required) and import them into Network to trigger the address lines construction rules.

1. Optional. The address cleansing tool cannot always parse building, organization, premise, thoroughfare, and sub building when they are placed in separate address lines. For example, address line 1 = building, address line 2 = organization, and address line 3 = premise, thoroughfare, and sub building.

   For these addresses, try updating the address fields to the following format in the .csv file:

   • address_line_1__v = building
   • address_line_2__v = street number <space> street name <space> suite number <space> organization

   If the third party address cleansing process is able to validate the address, it will parse the data into the correct fields. After the address is cleansed, the address lines construction rules can be applied.

2. In the admin console, click System Interfaces > Systems and add a new system called Address Lines Construction.

   Note: This is an optional step. You can use an existing system for the addresses, but using a specially named system enables you to later find the records that were updated by filtering on the system.

3. Configure a source subscription to use the exported address file (File A) as the source file. An existing subscription can be used. If you created a new system, ensure that you change the System to the one that you just created.
4. Run the source subscription to trigger the address lines construction rules.

The addresses will be updated so that address lines are reconstructed.

Compare updated addresses

These steps are optional. Create a new file so that you can compare the source addresses with the newly reconstructed addresses in a database. If you see unexpected results, you can manually override the addresses.

1. Use reporting or a target subscription to export the addresses that were processed by the address lines construction rules. Add the filter, System = Address Lines Construction. (This will be referred to as "File B").

2. In the .csv file, update the address lines column names with the following changes:

   • address_line_1__v to reconstructed_address_line_1__v
   • address_line_2__v to reconstructed_address_line_2__v
   • address_line_3__v to reconstructed_address_line_3__v
3. Import File A and File B into a database.

4. Create another file, File C, by cross-referencing the entity and address VIDs in the two files (Files A and B).

   The column headers in File C should be:

   • entity_VID
   • address_VID
   • address_line_1__v
   • address_line_2__v
   • address_line_3__v
   • reconstructed_address_line_1__v
   • reconstructed_address_line_2__v
   • reconstructed_address_line_3__v
5. Compare the source address line fields with the Network reconstructed address line fields.

If the reconstructed address lines are in an unexpected format, use the address VIDs to find the addresses in the Network UI and manually override them.