1. Order Broker Cloud Service Online Help
  2. Systems Menu
  3. Proximity Uploads

Proximity Uploads

Purpose: Use the Proximity Uploads screen to update the zip or postal code information in the proximity location table if you use proximity locator but do not use the Oracle Maps Cloud Service.

Used for the Routing Engine module.

Areas for proximity locator searching: You can upload U.S. zip codes. Canadian postal codes, or international postal codes for Order Broker to use when performing proximity locator searches.

Required zip or postal code data: Before you use the Proximity Uploads screen, you need to obtain a CSV (comma-separated value) file containing the zip or postal code information. See the Proximity upload process overview for more details.

In this topic:

How to display this screen: Select Proximity Uploads from the Home Screen or from the Systems Menu.

Note:

Available if Use Routing Engine is selected at the Tenant screen. Only users with Proximity Uploads authority can display this screen. See the Role Wizard for more information.

Proximity upload process overview

Purpose: Follow the steps below to update the zip or postal code information in the proximity location table.

Before you start: Obtain or create the CSV (comma-separated value) file for upload. See the Proximity CSV file layouts for details on the information required in this file.

Updating the proximity_location table: Perform the steps below at the Proximity Uploads screen.

Note:

It is important to schedule the update at a time when proximity searching is not required on your system, since once the update starts, proximity information will not be available until the update is complete. Each time Order Broker updates the proximity location table, it first clears the outdated information before adding the new, current information.
  1. Select the type of postal or zip code data that you are uploading from the Proximity Data Type drop-down list. Possible options are:
    • Canada
    • International
    • United States
  2. In the Country Code field, enter the country code associated with each uploaded zip or postal code record. Updates the country_cd field in the proximity_location table. This is a 3-position, alphanumeric code.
  3. Use the Browse button next to the File Name field to identify the CSV file to upload.

    Note:

    This file must match the Proximity Data Type selected, and must have a CSV extension.
  4. Click Upload. Order Broker:
    • Places the file in a temporary location.
    • If this is an update, clears the records with matching country codes from the proximity_location table.
    • Adds a record to the proximity_location table for each record in the uploaded file, using the Country Code to update the country_cd field and the Proximity Data Type to update the country field.
    • When processing is complete, sends the Proximity Upload Status Email to the Administrative email address specified at the Event Logging screen.

Location of archived or error files: Order Broker saves a copy of each uploaded file in the FILE_STORAGE table, using a CONTAINER of OROB-ARCHIVES if the upload was successful; otherwise, if there were errors, the CONTAINER it uses is OROB-ERRORS.

Proximity CSV file layouts

United States File Layout

The required contents of the CSV file and the target fields in the proximity_location table for the United States are described below.

Field contents described below as (empty) are not passed, with no space included between the comma delimiters, as in the following sample entry:

Postal Code,City,StateCode,County,,CityTypeCode,,CityAliasName,Latitude,Longitude

Note:

Column headings are required.
Field Contents Attributes Target in proximity_location table

postal (zip) code

varchar, 10 positions

POSTAL_CD

city

varchar, 35 positions

CITY

state code

varchar, 3 positions

STATE_PROVINCE_CD

county

varchar, 35 positions

COUNTY_PARISH

(empty)

N/A

N/A

city type code

nchar, 1 position

CITY_TYPE_CD

(empty)

N/A

N/A

city alias name

varchar, 35 positions

CITY_ALIAS_NAME

latitude

numeric, 9.6 positions

LATITUDE

longitude

numeric, 9.6 positions

LONGITUDE

Canada File Layout

The required contents of the CSV file and the target fields in the proximity_location table for Canada are described below.

Field contents described below as (empty) are not passed, with no space included between the comma delimiters, as in the following sample entry:

Postal Code,City,State,,Latitude,Longitude

Note:

Column headings are required.
Field Contents Attributes Target in proximity_location table

postal code

varchar, 10 positions

POSTAL_CD

city

varchar, 35 positions

CITY

province (state)

varchar, 3 positions

STATE_PROVINCE_CD

(empty)

N/A

N/A

latitude

numeric, 9.6 positions

LATITUDE

longitude

numeric, 9.6 positions

LONGITUDE

International File Layout

The required contents of the CSV file and the target fields in the proximity_location table for other countries are described below. A sample entry is:

Postal Code,Latitude,Longitude,City,StateCode

Note:

Column headings are required.
Field Contents Attributes Target in proximity_location table

postal code

varchar, 10 positions

POSTAL_CD

latitude

numeric, 9.6 positions

LATITUDE

longitude

numeric, 9.6 positions

LONGITUDE

city

varchar, 35 positions

CITY

state or province code

varchar, 3 positions

STATE_PROVINCE_CD

Troubleshooting

How to tell if the process is complete? Normally, Order Broker generates an email to the Administrative email address specified at the Event Logging screen when the process is complete, using the Locale specified at that screen to determine the language for the email.

Your upload process could fail if:

  • You select a Proximity Data Type that does not match the selected File Name.
  • You edit the CSV file in any way before you start the upload.
  • You attempt to start an upload while another upload is still in process.

If the process fails, it is possible that proximity data will be unavailable for the country related to the CSV file, because the first thing that Order Broker does as part of the upload process is clear the related records from the proximity location table before rebuilding it. To correct this situation, begin the upload process again, using a complete CSV file.

If the upload remains in Uploaded status for longer than expected, contact your Oracle representative.

Proximity Upload Status Email

Order Broker generates the Proximity Upload Status email to the Administrative email address specified at the Event Logging screen at the completion of the proximity upload process if the Email Notifications flag for the Proximity Data Load option is set to Administrator. The Language specified at the Event Logging screen is used.

This email includes:

  • The status of the upload, i.e, whether it completed successfully or if there were errors.
  • The file name uploaded.
  • The size of the uploaded file.
  • The user ID who performed the upload process.
  • The date and time the upload started and finished.
  • The number of records read, created in the proximity_location table, and in error.

The language is based on the Language specified at the Event Logging screen.

The “from” address on the email is the From Email specified at the Event Logging screen.

Example:

****ATTENTION****

Your Proximity Upload has Completed Successfully.

Proximity Data Type: United States

File Name: generic_postal_code_data.csv

File Size: 3367903 bytes

Uploaded By: sample_usr

Date/Time File Uploaded: 2021–10–11 12:34:56.123

Date/Time Load Finished: 2021–10–11 12:34:59.234

# of Records Read: 8004

# of Records Loaded: 8004

# of Records In Error: 0

Please do not respond to this message.

–Order Broker

Fields at this screen

Field Description
Entry fields:
Proximity Data Type

Indicates the postal code type that you are uploading. Your selection updates the country field in the proximity_location table. Possible selections are:

  • Canada
  • International
  • United States

Required.
Country Code

The country code associated with each uploaded zip or postal code record. Updates the country_cd field in the proximity_location table.

Order Broker requires that store locations and locate items or submit order requests specify the country code that matches the proximity location record. For example, if you enter US here, then USA is not a valid entry at the New Location or Edit Location screens, or in the locate items request message or the submit order message.

Alphanumeric, 3 positions; required.
File Name

The name of the CSV file containing the zip or postal code data. You can use the Browse button to select the file. Required.

Upload history fields:
Date

The date and time of the upload process. The date and time are updated by the Order Broker server when you begin the upload process, and then again when the process is complete.
Proximity Data Type

The value selected from the Proximity Data Type drop-down box at the time you ran the upload.

Status

The status of the upload. Possible statuses are:

  • Completed: The entire upload and update completed without any errors.

  • Completed with Errors: The proximity upload completed, but one or more records could not be processed.

  • Failed: The process could not update the proximity location table, possibly because the file or filename being invalid, the .CSV file was deleted or moved, or there was an error during processing.
  • Uploaded: The .CSV file was uploaded, but the process has not finished updating the database.

Note:

See Troubleshooting for information on correcting problems.
File Name

The name of the uploaded file.
User ID

The ID of the user who ran the upload.