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.
Required setup: Before you can upload proximity data, you need to complete the Proximity Upload Files setting in the Data Folder Settings section of the Tenant - Admin screen, and make sure that the specified folder exists on the Order Broker server.
In this topic:
• Proximity upload process overview
• Proximity Upload Status Email
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 - Admin 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:
• Initial setup at the Tenant - Admin screen: Confirm that the Proximity Upload Files entry in the Data Folder Settings section of the screen is correct.
• 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, as 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 selected Proximity Data Type selected, and must have a CSV extension.
4. Click Upload.
Order Broker:
• Copies the specified file from the path specified in the Proximity Upload Files entry in the Data Folder Settings section of the Tenant - Admin screen.
• 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 archive files: Order Broker saves a copy of each uploaded file in the Archive folder of the Proximity Upload Files entry in the Data Folder Settings section of the Tenant - Admin screen. For example, if the Proximity Upload Files entry is set to /usr/share/Locate/data/ProximityUploadData, Order Broker saves a copy of the uploaded file under /usr/share/Locate/data/ProximityUploadData/Archive. Uploaded files are saved in this folder regardless of whether the upload was successful.
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 |
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. However, if you suspect that the process might not be successful, you can check the folder specified in the Proximity Upload Files entry in the Data Folder Settings section of the Tenant - Admin to see if the uploaded .CSV file is still there; if so, the process is still ongoing.
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
• The CSV file on the Order Broker server is moved or deleted before the process is complete
• 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.
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 and its location on the Order Broker server while the upload was taking place (after completion, the file is stored in the proximity upload archive folder specified in the Proximity Upload Files entry in the Data Folder Settings section of the Tenant - Admin screen)
• 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 location of the error file, if any, on the Order Broker server
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 |
: |
6 |
|
File Name |
: |
generic_postal_code_data.csv |
|
File Size |
: |
3367903 bytes |
|
Uploaded By |
: |
sbrown |
|
Date/Time File Uploaded |
: |
2017-10-12 13:09:30.073 |
|
Date/Time Load Finished |
: |
2017-10-12 13:19:12.311 |
|
# of Records Read : |
: |
80044 |
|
# of Records Loaded |
: |
80044 |
|
# of Records In Error |
: |
0 |
|
Please do not respond to this message.
--Order Broker |
||
|
Field |
Description |
|---|---|
|
Entry fields: |
|
|
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. |
|
|
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. |
|
|
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 data was successfully uploaded and added to the proximity location table • Completed with errors: The process completed, but not all data in the uploaded file was successfully added to the proximity location table • Failed: The process could not update the proximity location table, possibly because the .CSV file was deleted or moved • Uploaded: The .CSV file was uploaded to the Order Broker server, but the process has not finished updating the database Note: See Troubleshooting for information on correcting problems. |
|
File Name |
The complete current path and name of the upload file on the Order Broker server. This is not the path you selected in the File Name field. A location of fakepath might be indicated if you uploaded through Chrome. |
|
User ID |
The ID of the user who ran the upload. |
________________________________
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Legal Notices