Importing Items/Products, Inventory, Barcodes, Images, and Locations into the Database

Part of the initial setup required for Order Broker is to populate the database with information such as locations, products, system products, and product locations. Also, you normally update this information periodically with any new or changed products and periodic inventory updates, and have the option of adding new information at any time.

Merchandising Cloud Services applications (RMFCS) imports: Importing products and barcodes from Merchandising Cloud Services applications (RMFCS) uses a different process than the one described under Import Process Overview (Other than RMFCS File Upload through OCDS) See Importing Data from Merchandising Cloud Services (RMFCS) through the Omnichannel Cloud Data Service (OCDS).

Load products from default system first: Begin by importing products from your default system, creating records in the product, system_product, and product_location tables for this system. Afterward, load products from any system that is not flagged as the default in order to create the system_product and product_location records for that system. The default system is the one flagged as the Default at the Systems screen. See the description of the Data Hierarchy for a discussion of the default system.

You cannot load a product from a non-default system before first creating the product for the default system.

Note:

Oracle recommends that you do not use UPC codes as system product codes, because UPCs are not permanently assigned to a single product.

Job batch size: The Job Batch Size defines the number of records for Order Broker to include in each batch of product, product location, and incremental inventory import records. The default setting is 1000 records.

Case-sensitive? Oracle recommends that when you use a file import, that you use uppercase for all import file names, regardless of whether the code identifying the importing system is all uppercase. For example, even if the system code is Xstore, the import file name could be PRODUCT_XSTORE.TXT.

Importing Data from Merchandising Cloud Services (RMFCS) through the Omnichannel Cloud Data Service (OCDS)

You can import the following information through the Omnichannel Cloud Data Service (OCDS):

Warehouse locations: The import includes only virtual warehouse locations, with the address derived from the physical warehouse associated with the virtual warehouse. The data imported does not include additional information, such as location attributes or preference settings.
Store locations. The import includes store locations and addresses. The data imported does not include additional information, such as location attributes or preference settings.
Products and system products. The system product code should be the same as the product code for all integrating systems. The import should take place first for the default system, in order to create the product records first before attempting to create system product records for additional systems.
Item image URLs: Optionally, you can import item image URLs in order to display item images in Store Connect.
Inventory (product locations) for warehouse and store locations. The available quantity is imported, but not purchase order information.
Product barcodes: Optionally, you can import product barcodes so that they can be used to scan items in Store Connect.

About OCDS: OCDS is a component of Oracle Retail Integration Cloud Service (RICS) that serves as a central repository of this information, which can originate in an enterprise product such as Merchandising Cloud Services applications (RMFCS). Other products, such as Order Management System, can also import this data.

Note:

When Order Management System integrates with Order Broker for customer order fulfillment, if either product integrates with OCDS, the other product also needs to be integrated with OCDS.

Order Broker submits a web service request for each type of import, and uses the data in the response to update each target table.

Note:

When RMFCS is the system of record for products, the product code is the same as the system product code in all systems in your organization, including the default system. For example, if the product code is 12345, then 12345 is also the system product code for all systems.

Configuration: To import this data from OCDS, use the OCDS Integration tab at the System screen to specify:

The URL to use when requesting data for each type of import.
A flag to indicate whether each import type is currently active. For example, you might perform an initial warehouse and store inventory load and then deactivate these imports.
The default location types to use when creating warehouse and store locations.
Authentication information for the web service requests, as well as the wait time.

Submitting the import: If any of the OCDS URLs defined at the System screen are flagged as active, data imports take place from OCDS rather than from any other source. Importing data through the file storage API does not take place.

For more information: See OCDS Imports.

File Storage API for Imports and Exports

About the File Storage API: The File Storage API is a RESTful web service that supports importing and exporting data: uploads, downloads, deletions, and inquiries. If the File Storage API is enabled, it handles all imports and exports through files, including:

Imports scheduled through the Schedule Jobs screen:
- products, system products, and item image URLs for display in Store Connect
- product locations
- locations
- product barcodes
Incremental inventory updates, scheduled through the Schedule Jobs screen
Exports scheduled through the Schedule Jobs screen:
- Inventory quantity
- Fulfilled inventory
- Sales order data extract

Note:

Importing Data from Merchandising Cloud Services (RMFCS) through the Omnichannel Cloud Data Service (OCDS) does not use the file storage API.

The file storage API is enabled by default for new installations of release 18.0 or higher.

Other import and export options: Other import and export options are handled through web service messages, including:

Imports:
- Product Update Request Message or Product Update Request JSON Message: creates or updates products, system products, and product locations
- Availability Update Request XML Message: Updates the available quantity for product locations, or creates product locations if they do not already exist.
- Location Update Request JSON Message: Create or update locations
- CreateDSVendor Request Message: Create or update vendors

Exports:
- System Discovery Request and Response XML Messages: Export a list of systems
- Location Discovery Request and Response XML Messages: Export a list of locations for a system
- Location Detail Request and Response JSON Messages: Request detailed information on all locations for a system

See the Operations Guide for information on the above web service request messages.

Use of the FILE_STORAGE table:The FILE_STORAGE table stores data on import files and export files, as well as errors that occur during import processing. The web service requests files from, deletes files in, and puts files in this table.

Web service requests: Requests supported by the File Storage API and their purposes are:

getFile: Download an export file or error file that has been generated by Order Broker.
deleteFile: Delete a file record, such as an export file that has already been retrieved.
putFile: Upload an import file to Order Broker. The file format can be text (.TXT file extension) or compressed (.ZIP file extension). If the file is compressed, Order Broker extracts the information when processing the import. No other file extensions are supported. Note: If you are uploading a zip file, then it must contain a TXT file of the same name as the ZIP file, and be in the base level of the file, with no subfolders.

The File Storage API returns an error if there is already an existing file in the table with the same name, regardless of whether the suffix is different. For example, the API returns an error if you attempt to upload a file named PRODUCT_SYS.ZIP if there is currently a FILE_STORAGE record named PRODUCT_SYS.TXT.
getFiles: Request a list of file records in the FILE_STORAGE table.

Every request needs to specify a container. The types of containers are:

OROB-IMPORTS: Import file records that can be processed through the File Storage API. For example, use the putFile request to create an OROB-IMPORTS record so that Order Broker can import the data.
OROB-EXPORTS: Export file records that have been generated through the File Storage API. For example, use the getFile request to download an export file, or use the deleteFile record to delete an export that has been downloaded.
OROB-ERRORS: Error file records that resulted from an import process through the File Storage API. For example, use the getFiles request to retrieve a list of error files that have been created.

All request messages also need to use a valid Storage web service user ID with a valid password. See Web Service Authorization for background.

File cleanup:

Import file records: Order Broker deletes these file records when they are processed, although errors are retained in the OROB-ERRORS container of the file storage table.
Export file records: It is the responsibility of the integrating system to delete these records. They can be deleted through the deleteFile request message.
Error files records: These records are purged automatically through the daily cleanup job based on the number of days specified in the File Storage setting under Retention Settings at the Tenant-Admin screen.

Reviewing or deleting file storage records: Use the File Storage History screen to review and, optionally, delete file storage records.

Enabling the File Storage API: The File Storage API is enabled if the STORAGE_ID in the TENANT_CONFIG table in the Admin database is set to database. This entry is set by default for a new installation of Order Broker 18.0 or later. To enable File Storage after upgrading from an earlier release of Order Broker, contact your Oracle representative.

Summary of File Storage API responses: The response codes that might be returned to file storage requests include:

200 = The getFile or getFiles request was successful.
204 = The putFile or deleteFile request was successful.
401 = The request failed because the web service user and password were not correct.
403 = The file you attempted to upload from RMFCS exceeded 1 GB.
404 = The request failed for other reasons.

For more information: See the Order Broker File Storage API technical reference paper on My Oracle Support for more information on implementing this API.

Import Process Overview (Other than RMFCS File Upload through OCDS)

For information on imports from RMFCS, see Importing Data from Merchandising Cloud Services (RMFCS) through the Omnichannel Cloud Data Service (OCDS)

Illustrtes the flow of import information from import files into database tables, including import tables in the database. Each is described below.

Import processing steps: Processing steps are parallel for all types of information:

The process clears outdated records from the import tables (location_import, product_import, product_barcode_import, and product_import_log) based on the Days to Keep Errors specified for the system at the Schedule Jobs screen. 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; otherwise, the process adds or updates the record or field in the target table (location, product, system_product, or product_barcode), and the record is cleared the next time you run an import for the system, regardless of whether the Days to Keep Errors has passed.

Note:
The product location import does not use an import table; instead, it updates the product_location table directly.

Note:
The product location import file name can include an optional suffix to indicate information such as the location code or a date/time stamp. Use of a file name suffix enables the inventory system to stage multiple product location import files throughout the day, and have the files processed in order when Order Broker runs a scheduled import. See Importing Product Locations through File Storage API for more information.
The process checks the FILE_STORAGE table for a record, created from a compressed (ZIP) file and extracted when the FILE_STORAGE record is created.

The process uses files or records named LOCATION_SYS.TXT, PRODUCT_SYS.TXT, PRODUCT_LOCATION_SYS.TXT, and PRODUCT_BARCODE_SYS.TXT files, where SYS is the system code running the import. The product location import file name can include an optional suffix to indicate information such as the location code or a date/time stamp. Use of a file name suffix enables the inventory system to stage multiple product location import files throughout the day, and have the files processed in order when Order Broker runs a scheduled import.
For each pipe-delimited file record, the process confirms that the data is formatted correctly and can be loaded into the related import table. For example, it checks whether a record includes an incorrect number of columns, has alphabetical characters in a numeric field, includes an improperly formatted date, or includes a field that exceeds the maximum length in the database. If there are any basic formatting errors in the uploaded data, it makes a copy of the file containing just the records in error in an OROB-ERRORS record in the FILE_STORAGE table with a message indicating the nature of the error.
The process deletes the record from the FILE_STORAGE table.
The process creates records in the import tables (location_import, product_import, or product_barcode_import) with any records that passed the basic edit. This step does not take place for product location records.
The process checks the location, product, and product barcode in the respective import tables for specific data errors, such as confirming that a product or system is correct, and flags the import file records with any errors.
For the product location import, the records in the FILE_STORAGE record or the PRODUCT_LOCATION_SYS.TXT file are checked for errors. Any entries in the record or import file that pass the edits update the PRODUCT_LOCATION table.
Any records in the import table that pass the edits update the target tables. Any empty fields in the import table do not update the target table; however, in the case of location updates, the location address is treated as a unit, so if any address data is passed for a location, the entire address is replaced with the data from the import table, including clearing any fields that are empty in the import table.
The process writes a log record for each import process. Import history information is available for review at the Product Imports History screen. History is retained for the number of days specified in the Job History retention setting at the Tenant-Admin screen.
Details on each product, location, or barcode record in error are available on a related error report (the Location Import Errors Report, Product Barcode Import Errors Report, or Product Import Errors Report). Also, the process updates the information on the last process date, time, and user for the system.
Based on the Location Product Import setting at the Event Logging screen, the process generates an email notification indicating success (if all records were successfully imported) or failure (if any record could not be imported).

Reset fulfilled quantity during product import? If the system’s Track Fulfilled Quantity field is set to Reset During Product Import, each product location is reset before the import process finishes. The Last Status is not updated at the Schedule Jobs screen until this last step is complete.

Note:

Oracle recommends that you do not use UPC codes as system product codes, because UPCs are not permanently assigned to a single product.

Running the process: You can set up a periodic processing schedule at the Schedule Jobs screen. You can also run the process on demand at that screen.

Updating or creating product locations through inventory inquiry: When Order Broker requests updated inventory information from an online system as a result of a LocateItems request, Product Availability request, or SubmitOrder message, or when “reshopping” a rejected order, it updates the availability information for existing product locations, or creates new product locations if they do not already exist. However, it does not send an inventory inquiry to a system unless a product location for the item and system already exists.

Note:

Order Broker updates the product location records only if the Update Offline Inventory flag at the Preferences screen is selected.

Using the product update or availability update request messages:

Product update request message: Enables you to create or update products, system products, and product locations; however, does not update product location attributes, and does not update item image URLs.
Availability update request message: Enables you to increase, decrease, or reset the available quantity for a product location.

These options can be useful for ad hoc updates. See the Operations Guide for more information.

Processing incremental inventory updates: Optionally, you can run an update program to import updated inventory levels from an integrated system, and then calculate the probable quantity available in each of the system’s product locations after factoring in any reserved quantity and current probability rules. A system can then retrieve the updated inventory information, including calculating the probable quantity, and use this information (for example, display this quantity on the ecommerce site). See the Incremental Inventory Import for more information.

For more information: See:

System, Schedule Jobs, and Product Imports History: configuring the import process and reviewing completed imports.
Importing Locations through File Storage API, Importing Products and System Products through File Storage API, Importing Product Locations through File Storage API, and Importing UPC Barcodes through File Storage API: import details, file layouts, and data mapping.
Product Barcode Import Errors Report, Product Import Errors Report, Location Import Errors Report: reviewing errors.
Operations Guide: information on the interactive RESTful inventory update.

Additional Types of Import Processes (Other than RMFCS File Upload and OCDS)

The additional types of import processes you can use to create and update locations, products and system products, product locations, and product barcodes in Order Broker are described briefly below.

RMFCS: For information on imports from RMFCS, see Importing Data from Merchandising Cloud Services (RMFCS) through the Omnichannel Cloud Data Service (OCDS),

Available-to-Sell Individual Inventory Updates through Oracle Retail Integration Cloud Service (RICS)

Order Broker processes individual inventory updates whenever they are received from through Oracle Retail Integration Cloud Service (RICS). This information originates in Oracle Retail Merchandising Foundation Cloud Service (RMFCS) or Enterprise Inventory Cloud Service (EICS). Processing this update requires Oracle Retail Integration Cloud Service authentication; see Web Service Authorization for more information.

Information received: The information in the individual inventory update maps to the availability update request:

If the product location already exists, only the Available Quantity is updated with the available to sell quantity passed. The current available quantity is overwritten with the available to sell quantity passed; no calculation takes place. This update functions the same way as a method of SET passed in the availability update request message.
If the product location does not already exist, it is created, using the system item, location, and location type passed.

If any of the information passed does not map correctly into the existing data in Order Broker, Order Broker returns an error to the RICS. Examples of incorrect data include:

The user name and password for web service authentication are not valid.
The item, location, or system do not exist.

See the Availability Update Request XML Message and Availability Update Response XML Message in the Operations Guide for more details.

Import Files

For import through the File Storage API for Imports and Exports, pipe-delimited files containing location, product, product location, and product barcode information are placed in the FILE_STORAGE table through a RESTful web service. You use the Schedule Jobs screen to set up an import schedule to process the data and create or update the related records in the database.

Periodic import from files does not require the system to be flagged as Online. The Online setting at the System screen indicates that Order Broker obtains current inventory information from the system when the Routing Engine evaluates which locations might be able to fulfill an order. Order Broker updates the product location records with this information only if the Update Offline Inventory flag at the Preferences screen is selected.

Additional import processes are described below.

Interactive Updates

Interactive inventory update through RESTful service call: If the system is flagged as Online and a URL is specified, Order Broker also uses a RESTful service call to request current inventory information for a specific product when it requires the information for a LocateItems request, a SubmitOrder request, or a Product Availability request, or when it needs to “reshop” a rejected order. In this case, the Remote System Service returns the information in a RESTful service response rather than in flat files.

Illustrates the flow of information between Order Broker and a remote system service through a RESTful service call. The remote system service can run a stored procedure over the integrated system database.

Order Broker updates the product location records only if the Update Offline Inventory flag at the Preferences screen is selected.

Obtaining interactive updates from SIM or EICS: Order Broker can request interactive inventory updates from Oracle Retail Store Inventory Management (SIM) or Enterprise Inventory Cloud Service (EICS). If a system is flagged as Online and a Connection Type of SIM is specified, Order Broker sends a request message to obtain interactive inventory updates for a specific product when it requires the information for a LocateItems request or a SubmitOrder request, a Product Availability request, or when it needs to “reshop” a rejected order.

SIM returns the current Total Stock On Hand from the store location that matches the product location in Order Broker, and the Available Quantity for the product location is updated with this quantity. If the Item Status in SIM or EICS is Deleted, Discontinued, or Inactive, the Available Quantity for the product location is set to 0.

Differences between Order Broker and SIM or EICS:

Even if the Item Status in SIM or EICS is Deleted, Discontinued, or Inactive, the Available Quantity for the product location is set to 0. However, the product location may still be eligible for order assignment if the Backorder Available flag for the location is selected.
The response from SIM or EICS does not update the Next PO Date or Next PO Quantity for a product location.
SIM and EICS support a decimal position for the available quantity. If the available quantity passed from SIM includes a decimal, Order Broker truncates the number. For example, if SIM passes an available quantity of 12.75, the available quantity in Order Broker is set to 12.

Incremental Inventory Import

Optionally, you can configure a system to periodically run an import of updates to product location records. This process enables you to schedule incremental product location updates from the integrated system. See the Incremental Inventory Import option at the Schedule Jobs screen for details.