Shipping address

Network can recalculate long addresses to ensure that the entire address is in a legible format for delivery. Network customers ship products to their customers and some shipping vendors limit address lines to 35 characters. Address lines that are longer than 35 characters are truncated by the vendor's system, so the delivery person cannot see enough information to deliver the samples. The samples are returned and cannot be reused due to FDA regulations. To avoid customers from incurring additional overhead costs and expired samples, Network can now format the shipping address to ensure that address lines contain no more than 35 characters.

This feature is available for US addresses only. It is not enabled by default. Contact Veeva Support to enable the feature for your Network instance.

How it works

If the feature is enabled in your Network instance and address lines 1 and/or 2 for US addresses exceed 35 characters, the lines will be recalculated during the address cleansing stage. Addresses cleansing occurs when addresses are added or changed (for example, through data load, DCRs, Change Request API, and so on).

The shipping address rules can be applied to the existing locally managed (gray) US addresses in your Network instance by reprocessing the affected addresses. For more information, see the section called Processing existing addresses below.

Note: This feature is not supported if you have the address lines construction feature (standardizing address lines to support downstream systems) enabled in your Network instance. Only one address recalculation feature can be enabled in a single Network instance.

Shipping address rules

Network has three address line fields. When Network users add or update an address (through a change request, a profile, or a source subscription), if address lines 1 or 2 exceed 35 characters, Network applies the shipping rules to recalculate address lines 1, 2, and 3. This ensures that the address is in a legible format for delivery.

Considerations for address verification status

The format of the recalculated lines depends on the address verification status. There are two scenarios:

  • For addresses with an address verification status of Verified or Ambiguous or Partially Verified where street number (premise__v) and street name (thoroughfare__v) are populated:

    • Street number, street name, and suite number (sub_building__v) are always in the first two address lines.
    • Building and organization are on the next available line.

    In this example, the original Address Line 1 exceeded 35 characters. During address verification, the address is recalculated so that the suite information (Ste 350) moved to Address Line 2.

    If the address is not recalculated, shipping vendors truncate Address Line 1 and the suite information does not display for the delivery person.

  • For addresses with a verification status of Partially Verified, Unverified, Not Verified, or Overridden, address lines are wrapped.

Examples

Examples 1-5 apply to Verified or Ambiguous addresses; Example 6 applies to Partially Verified and Unverified addresses.

Example 1

Address Line 1 has more than 35 characters and Address Line 1 has a building.

# Address Lines Post-Processed Shipping Address Lines
1 The Clifton T. Perkins State Hospital 8490 Dorsey Run Rd
2 8490 Dorsey Run Rd The Clifton T. Perkins State
3   Hospital

Example 2

Address Line 1 has more than 35 characters and Address Line 1 has an organization.

# Address Lines Post-Processed Shipping Address Lines
1 Grand Rapids Medical Education Partners 1000 Monroe Ave NW
2 1000 Monroe Ave NW Grand Rapids Medical Education
3   Partners

Example 3

Address Line 1 has more than 35 characters and Address Line 1 has a premise, thoroughfare and suite number concatenated.

# Address Lines Post-Processed Shipping Address Lines
1 2801 W Kinnickinnic River Pkwy Ste 350 2801 W Kinnickinnic River Pkwy
2   Ste 350

Example 4

Address Line 2 has more than 35 characters and Address Line 1 has a building

# Address Lines Post-Processed Shipping Address Lines
1 Harbor Plaza 105 Paseo Concepcion De Gracia
2 105 Paseo Concepcion De Gracia Ste 1 Ste 1 Harbor Plaza

Example 5

Address Line 2 has more than 35 characters and Address Line 1 has an organization.

# Address Lines Post-Processed Shipping Address Lines
1 Nebraska Medicine 2510 Bellevue Medical Center Dr
2 2510 Bellevue Medical Center Dr Ste 250 Ste 250 Nebraska Medicine

Example 6

This example applies to Partially Verified and Unverified addresses. Address lines will be wrapped

# Address Lines Post-Processed Shipping Address Lines
1 Kaiser Permanente Optometry Department Kaiser Permanente Optometry
2 1011 Baldwin Park Blvd Department 011 Baldwin Park Blvd

Processing existing addresses

Existing locally managed (gray) US addresses with lines 1 and 2 that exceed 35 characters must be processed through the Network shipping address rules. If existing addresses are not recalculated using the shipping rules, duplicate records can be created when addresses are added or changed because the match rules will not be able to match the existing addresses with the new or changed addresses that have had their address lines recalculated.

To update the US addresses in your Network instance that exceed 35 characters, administrators should complete the following steps:

  1. Create a report to find the addresses in your Network instance that have more than 35 characters in address lines 1 and 2.
    1. On the Network menu, click Reports > Ad Hoc Queries.
    2. Click the Advanced tab.
    3. In the field, paste the following query:

      SELECT
              entity_vid__v,
              vid__v AS address_vid,
              address_line_1__v,
              address_line_2__v,
              address_line_3__v,
              premise__v,
              thoroughfare__v,
              building__v,
              organization__v,
              sub_building__v,
              address_verification_status__v,
              locality__v,
              administrative_area__v,
              postal_code__v,
              country__v
          FROM
              address
          WHERE
              (
                  CHAR_LENGTH( TRIM( address_line_1__v ) ) > 35
                  OR CHAR_LENGTH( TRIM( address_line_2__v ) ) > 35
              )
              AND record_state__v = 'VALID'
              AND is_veeva_master__v = 'false' ;

    4. Click Run.
    5. In the Results section, review the returned addresses.
    6. Optional. To save the report on your local computer, click Download.
  2. Create a .csv file with the affected addresses. (File A)

    The file must contain Network entity IDs (VIDs) for the entity and address and data for the following fields: address lines 1, 2, and 3; city; state; ZIP/Postal code; and country.

    The file must have the following names as 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
  3. Import the addresses back into Network using a source subscription. Contact Veeva Support to set up the source subscription.
    1. Create a new source system called Shipping Addresses. This system can be used to filter the records that are being updated and can be used for extracting the affected addresses later on.
    2. Configure a source subscription to use the exported address file (File A) as the source file. An existing source subscription can be used, but ensure that System is the Shipping Addresses system.
    3. Run the source subscription to trigger the shipping address rules.

    The addresses will be updated so that address lines are less than 35 characters.

  4. Optional. Compare the data so that you can see what it looked like before and after the shipping address rules were applied.
    1. Export the addresses that were processed by the shipping address rules. Use Network reports or a target subscription and add a filter for System = Shipping Address.
    2. In this new file (File B), change the address_line column names to shipping_address_line for example, change address_line_1__v to shipping_address_line_1__v.
    3. Import File A (used in the source subscription) and this new file (File B) into a database.
    4. Create another file (File C) by cross-referencing the entity and address VIDs in the two files (File A and B).
    5. Compare the source address_line fields with the Network cleansed address_line fields. The column headers in File C should have the following names:
      • entity vid
      • address vid
      • address_line_1__v
      • address_line_2__v
      • address_line_3__v
      • shipping address_line_1__v
      • shipping address_line_2__v
      • shipping address_line_3__v