Importing Locations through File Storage API

Importing locations allows you to create a location, including the address, telephone numbers, and other related information such as the store rank and hours, based on information defined in a location import file from an external system.

What is a location? A location is a place where a product is sold or stocked. A location can be a warehouse or store where you keep actual inventory, or it can also be a virtual location such as a web storefront or a vendor. Locations are defined within an organization both by the system to which they belong and their location type.

Location address: It is important that the location address be accurate, since the location address is used as the ship-to address for ship-for-pickup orders.

Location relationships: See Organization, System, and Location for an overview of the relationships among Order Broker elements, including locations.

Required setup for file upload: You can use the File Storage API, to import locations to the location table and import location level fulfillment preferences to the preferencestable.

Create a pipe-delimited flat file named LOCATION_SYS.TXT, where SYS is the associated system code, making sure to name the file in all uppercase. Create a header row and a separate row for each location you wish to import. See Sample Location Import File for a sample of the data to include in the file.

Using the File Storage API, place the file in the OROB-IMPORTS container of the FILE_STORAGE table. The file remains in this location until you run the import; see Location Import Steps for processing details

Use of OCDS: See OCDS Imports for information on importing store and virtual warehouse locations through an integration with OCDS.

Note:

Creating or updating a location through this import process does not support assigning Location Attributes to locations.
You can also create locations through the LocationUpdate request messages. See the Operations Guide for more information.

For more information: See:

Importing Items/Products, Inventory, Barcodes, and Locations into the Database for an overview on the import process and background information.
Schedule Jobs for more information on the import schedule for an integrated system, or to run the import process on demand.
the Product Imports History screen and the Location Import Errors Report for more troubleshooting information related to the location import process.

Location Import Steps

The import steps related specifically to location import through the File Storage API:

The process clears outdated records from the location_import table based on the Days to Keep Errors for the system. If a record is flagged with an error code, it remains in the import table until the Days to Keep Errors has passed and you next run an import for that system.
The process uses the pipe-delimited flat file named LOCATION_SYS.TXT, where SYS is the system code that matches the system code associated with the import being processed in the OROB-IMPORTS container of the FILE_STORAGE table. The name of the pipe-delimited file should be uppercase, including the system code, regardless of whether the system code is upper and lowercase in Order Broker.
If the process cannot move the records to the location_import table for field edits, it moves the records in error in the LOCATION_SYS.TXT flat file to the.OROB-ERRORS container in the FILE_STORAGE table. This can occur if, for example, the number of columns in the flat file is invalid. In this case, a general error is listed at the Product Imports History screen.
If the records in the location import file pass the initial edits, the process uses the information from the flat file to create records in the location_import table. See Location Import Mapping for more information on how the data in the LOCATION_SYS.TXT file maps to the location_import table.
Next, if a record in the location import file contains an error, the system updates the record in the location_import table with the error code.

In this situation, you can run the Location Import Errors Report to review the list of errors in the import file. Correct the records in error in the originating system and use the file storage API to replace the file.

Note that certain errors are listed on the error report with an error code of 56 and a reason of Locations Import Failed - Other Error. This can occur when, for example, an address line is too long or a one-position flag, such as the Active flag, contains an invalid character. In these cases, a more descriptive error description is included in the error file.
For records in the location import file that process successfully, the system:
- Creates and updates records in the location table. If the Active field is Y, the location is listed on the Locations screen. See Location Import Mapping for more information on how the data in the location_import table maps to the location table.
- Updates all address information if any address information was included in the import file, including clearing the data in any address fields that are blank (containing a space) or empty in the import file. However, if all address fields are empty in the import file, the current address information is not replaced, and only non-address fields are updated with the data from the import file.
- With the exception of address data, clears any fields that contain a blank space in the import file; otherwise, does not update any field that is empty in the import file.
- Creates and updates records in the preferences table. These preference settings display at the location level on the Fulfillment Tab of the Preferences screen.
- Deletes the location_import record.
- If you use proximity locator, determines the location’s latitude and longitude using either the Oracle Maps Cloud Service or the proximity location table, depending on your configuration, and saves this information as part of the location record.
After processing all import files:
- The process writes a log record for each import process, displayed at the Product Imports History screen.
- Based on the Location Product Import setting at the Event Logging screen, after processing all import files, the process generates an email notification indicating success (if all records were successfully imported) or failure (if any record could not be imported).

The backed up files in the FILE_STORAGE table are cleared based on the number of days specified in the Product Import Files setting in the Retention Settings area of the Tenant - Admin screen.

Invalid address? The import process creates a location record even if the address specified in the import file is incomplete or invalid. In this case, the location record is listed on the Location Import Errors Report with an error of 75 - The address combination is invalid. If the location is not included as expected in inventory search results or order assignments, you can use the Edit Location to verify or correct the address.

Update Latitude and Longitude: The import process updates the location’s Latitude and Longitude when creating or updating the location. See Proximity Locator Searching for an overview.

Days Open not updated: The Days Open fields for a location are all automatically selected when the import process creates a new location, and the existing settings are not updated when the import process updates an existing location. You can update these fields at the Edit Location screen, or through the Location Bulk Updates wizard.

See Auto-Cancel Unclaimed Orders for background on how the Days Open fields control the update of the Pickup By Date.

Sample Location Import File

To import locations, create a pipe-delimited flat file named LOCATION_SYS.TXT, where SYS is the associated system code. The file name should be all uppercase, including the system code, even if the system code is set up in Order Broker as upper and lowercase.

The following is a sample of the contents to include in the LOCATION_SYS.TXT pipe-delimited flat file. The first row is the header information, which is informational only, and the following row is the location data.

The file must contain the following columns, with each column separated using pipes |. Each column besides the LABOR_COST is required; an empty column can be entered as ||.

Note:

When updating a location, data passed in the location import file overrides the existing data for the location. If a setting in the location import file contains a blank space (| |), the system clears any value currently in the field. For example, if the existing rank for the location is 1, and you the RANK field in the location import file contains a space, the system clears the value of 1 from the Rank field. However, the address fields are treated as a single unit, and if any address data is passed in the import file, the entire address is updated, including clearing any fields that are blank or empty in the import file.

7|STC|TEST2|Location Test Import2|1234 Sample St|Address line 2|Address line 3|Address line 4|250|Westborough|MA|01581|US|5085550100|9371|5085550101|9-6|4|GreaterBoston|Firstname Lastname|flast@example.com|Y|Y|N|Y|N|Y|N|N|12.34

Location Import Mapping

The table below lists the fields in the location import flat file, the location_import table, and the location and preferences tables.

Note:

The field names indicated below are informational. The import ignores the field names in the first row in the flat file, although it does confirm that the number of columns in the first row is consistent with the number of columns in each import record.
See the Edit Location screen or the Preferences screen for more information on each field in context.

Field	Attributes	Description
SYSTEM_CD used to update the SYSTEM_ID in the location table	alphanumeric, 10 positions	See system. The system code can include special characters and must be unique in Order Broker. The system code must be a valid system for the organization that the import process is being run. Required. Mapping: The system determines the SYSTEM_ID in the location table by mapping the SYSTEM_CD in the location_import table to the SYSTEM_CD in the system table.
LOCATION_TYPE_CD used to update the LOCATION_TYPE_ID in the location table	alphanumeric, 10 positions	See location type. The location type code must be a valid location type code for the organization. Required. Mapping: The system determines the LOCATION_TYPE_ID in the location table by mapping the LOCATION_TYPE_CD in the location_import table to the LOCATION_TYPE_CD in the location_type table.
LOCATION_CD	alphanumeric, 10 positions	See location. The location code must be unique for each organization and system. If the location code does not exist, the system creates a new location; if the location code already exists, the system updates the location. Required.
NAME	alphanumeric, 40 positions	Location names do not need to be the same as the name or description of the location in the integrated system, such as Order Management System or Xstore. Required.
The import process updates all address information if any address information was included in the import file, including clearing the data in any address fields that are blank or empty in the import file. However, if all address fields are empty in the import file, the current address information is not replaced, and only non-address fields are updated with the data from the import file. Address fields include address lines, apartment/suite, city, state/province, postal code, and country.
ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3, and ADDRESS_LINE_4	alphanumeric, 50 positions each	Optional.
AP_SUITE named APT_OR_SUITE in the location table	alphanumeric, 20 positions	Optional.
CITY	alphanumeric, 35 positions	Optional.
STATE/PROVINCE_CODE	alphanumeric, 3 positions	Required if you use the proximity locator. Should be a valid ISO code.
POSTAL_CD	alphanumeric, 10 positions	The ZIP or postal code for the location. Required if you use the proximity locator. Note: To prevent issues with proximity calculation or errors upon import, Canadian postal codes should be imported as a six-position code plus an embedded space. For example, the correct format is Y1A 1A3 rather than Y1A1A3.
COUNTRY_CD	alphanumeric, 3 positions	Required if you use the proximity locator; in this situation, the country code must exist in the proximity table. Should be a valid ISO code. Note: The import process creates a location record even if the address specified in the import file is incomplete or invalid. If the location is not included as expected in inventory search results or order assignments, you can use the Edit Location to verify or correct the address.
PHONE	alphanumeric, 20 positions	Optional.
EXTENSION	alphanumeric, 10 positions	Optional.
FAX	alphanumeric, 20 positions	Optional.
LOCATION_HOURS	alphanumeric, 60 positions	Optional.
RANK	alphanumeric, 10 positions	Optional.
REGION	alphanumeric, 20 positions	Optional.
CONTACT_NAME	alphanumeric, 50 positions	Optional.
EMAIL	alphanumeric, 255 positions	The email address must be formatted as user@host.com (or other valid suffix such as .org). Order Broker does not validate that your entry represents an existing email address. Separate multiple email addresses with a semicolon (;).
The following location level preferences in the location import file map to the PREFERENCES table. If any of the preferences are set to anything other than Y, N, or blank, the upload will be in error, with an error message such as the following indicated in the error file: `'Q' is invalid for Column 'SHIPFORPICKUP_SOURCE_AVAILABLE', expected Y or N.` These records are not listed on the Location Import Errors Report.
DELIVERY	alphanumeric, 1 position	Indicates to the Routing Engine whether a location is eligible to fulfill an order whose fulfillment type is DELIVERY. See Delivery Order for a discussion. Updates the location level Delivery Available field on the Fulfillment Tab of the Preferences screen: If the import value was Y, the preference is Yes. If the import value was N, the preference is No. If the import value was blank, the preference is Not Defined. Mapping: The system determines the location level Delivery Available setting on the Fulfillment tab of the Preferences screen by mapping DELIVERY in the location_import table to the PREFERENCE_VALUE for PREFERENCE_TYPE_ID 114 (Delivery Available) and LEVEL_ID 30 (location level) in the PREFERENCES table.
SHIP_TO	alphanumeric, 1 position	Not currently implemented.
RETAIL_PICKUP	alphanumeric, 1 position	Not currently implemented.
PICKUP	alphanumeric, 1 position	Indicates to the Routing Engine whether a location is eligible to fulfill an order whose fulfillment type is PICKUP. See Pickup Order for a discussion. Updates the location level Pickup Available field on the Fulfillment Tab of the Preferences screen: If the import value was Y, the preference is Yes. If the import value was N, the preference is No. If the import value was blank, the preference is Not Defined. Mapping: The system determines the location level Pickup Available setting on the Fulfillment tab of the Preferences screen by mapping PICKUP in the location_import table to the PREFERENCE_VALUE for PREFERENCE_TYPE_ID 113 (pickup available) and LEVEL_ID 30 (location level) in the preferences table.
BACK_ORDER_AVAILABLE	alphanumeric, 1 position	Indicates whether a location can be assigned a delivery order even if it does not currently have sufficient inventory on-hand. Enter Y to have the location eligible to fulfill a delivery order even if it does not currently have the requested quantity of each item on-hand, or enter N or leave blank if you do not want the location selected unless it currently has the full quantity of each item on-hand. Note: Do not set this field to Y for the default unfulfillable location. Listed in the location level Backorder Available field on the Fulfillment Tab of the Preferences screen: If the import value was Y, the preference is Yes. If the import value was N, the preference is No. If the import value was blank, the preference is Not Defined. Mapping: The system determines the location level Backorder Available setting on the Fulfillment tab of the Preferences screen by mapping BACK_ORDER_AVAILABLE in the location_import table to the PREFERENCE_VALUE for PREFERENCE_TYPE_ID 128 (backorder available) and LEVEL_ID 30 (location level) in the preferences table.
ACTIVE	alphanumeric, 1 position	Enter Y to indicate the location is active. Informational only.
ShipForPickup_ Source_Available	alphanumeric, 1 position	Indicates to the Routing Engine whether a location is eligible to source an order whose fulfillment type is SHIPFORPICKUP. See Ship For Pickup Order for a discussion. Updates the location level Ship For Pickup Sourcing Available field on the Fulfillment Tab of the Preferences screen: If the import value was Y, the preference is Yes. If the import value was N, the preference is No. If the import value was blank, the preference is Not Defined. Mapping: The system determines the location level Ship For Pickup Sourcing Available setting on the Fulfillment tab of the Preferences screen by mapping SHIPFORPICKUP_SOURCE_AVAIL in the location_import table to the PREFERENCE_VALUE for PREFERENCE_TYPE_ID 140 (sourcing available) and LEVEL_ID 30 (location level) in the preferences table.
ShipForPickup_ Pickup_Available	alphanumeric, 1 position	Indicates to the Routing Engine whether a location is eligible to have the customer pick up an order whose fulfillment type is SHIPFORPICKUP. See Ship For Pickup Order for a discussion. Updates the location level Ship For Pickup Receiving/Pickup Available field on the Fulfillment Tab of the Preferences screen: If the import value was Y, the preference is Yes. If the import value was N, the preference is No. If the import value was blank, the preference is Not Defined. Mapping: The system determines the location level Ship For Pickup Receiving / Pickup Available setting on the Fulfillment tab of the Preferences screen by mapping SHIPFORPICKUP_PICKUP_AVAIL in the location_import table to the PREFERENCE_VALUE for PREFERENCE_TYPE_ID 139 (receiving/pickup available) and LEVEL_ID 30 (location level) in the preferences table.
LABOR_COST	numeric, 19.4	Used to “shop” an order for fulfillment or sourcing as part of the LocateItems Sequence and Splitting Examples (Standard Brokering) method when determining the cost to pick, pack, and ship an order. It can be 0, but cannot be a negative number, and should not include a currency symbol. Should not exceed the specified length, or the upload record will be in error. This error is not listed on the Location Import Errors report. Optional. Note: This column can be omitted entirely, instead of just leaving it blank; however, to omit the labor cost column, you must not only omit the column for each record, but also omit the column from the file headers. If no labor cost column is included for the location records, but the LABOR_COST is included in the file headers, the record is in error: `Invalid number of import columns, 29 passed.` This error is not listed on the Location Import Errors report. You can also update the labor cost through the Location Bulk Updates wizard.