Importing Products, System Products, and Item Image URLs through File Storage API

Purpose: Use the Schedule Jobs screen to import locations, products and system products, product locations, item image URLs, and UPC barcodes. This help topic describes the fields you can map and update for products, system products, and item image URLs.

Note:

This help topic does not address importing product data from OCDS. See OCDS Imports for more information.

Required setup: To import products and system products to the product and system_product tables, create a pipe-delimited flat file named PRODUCT_SYS.TXT, where SYS is the associated system code, making sure to name the file in all uppercase, including the system code, even if the system code is set up in Order Broker as upper and lowercase. Create a separate row for each product or system product. See Sample Product Import File for a sample of the data to include in the file.

The process looks for the file in the OROB-IMPORTS container of the FILE_STORAGE table. The file remains in this location until you run the import, as described below.

Oracle recommends that you do not use UPC codes as system product codes, because UPCs are not permanently assigned to a single product.
Creating or updating a product through this import process does not support assigning Product Attributes to products.
You can also create products through the ProductUpdate 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.
Product Import for more information on setting up the product import schedule for a system, or to run the import process on demand.
the Product Imports History screen and the Product Import Errors Report for more troubleshooting information related to the product and system product import process.

Product and System Product Import Steps

The import steps related specifically to product and system product import:

The process clears outdated records from the product_importtable 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 PRODUCT_SYS.TXT, where SYS is the system code, that is in the OROB-IMPORTS container of the FILE_STORAGE table. The name of the pipe-delimited file should be uppercase, including the system code, even if the system code is set up in Order Broker as upper and lowercase.

Job Batch Size: The Job Batch Size controls the number of records to process in each batch.
If the process cannot move the records to the product_import table for field edits, it moves the records in error in the PRODUCT_SYS.TXT flat file to the OROB-ERRORS container of the FILE_STORAGE table, adding a date and time stamp to the name of the file, such as PRODUCT_SYS.TXT.20150628.153000.err. This can occur if, for example, there are an invalid number of columns in the flat file, a numeric field contains alphabetical data, a date is not formatted correctly, or the length of a field exceeds the maximum length in the database. In this case, a general error is listed at the Product Imports History screen, and no errors are listed on the Product Import Errors Report.
If the records in the file pass the initial edits, the process uses the information from the flat file to create records in the product_import table. See Product Import Mapping for more information on how the data in the PRODUCT_SYS.TXT file maps to the product_import table.
Next, if there is an error based on the required data for product and system product records, the process updates the record in the product_importtable with the error code.

In this situation, you can run the Product 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.
If there are no errors for a product_import record, the process creates or updates the related product record (if the import is for the default system) and system product record and the product_import record is deleted.
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 archive and error containers 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.

Sample Product Import File

To import products or system products, create a pipe-delimited flat file named PRODUCT_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 file must contain the following columns, each column separated using pipes |. Each column is required; a blank column can be entered as | |.

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

The import ignores the first row in the file.

Product Import Mapping

The table below lists the fields in the product import flat file, the product_import table, and the product and product_location tables.

Note:

The flat file field names indicated below are informational. The import ignores the first row in the flat file.

Field	Attributes	Description
system_cd	alphanumeric, 10 positions	See system. The system code can be 1 to 10 positions in length, 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, but does not need to be the same as the system running the import. Required.
department	alphanumeric, 40 positions	The description of the product's department. Order Broker updates this field for the product only if it is passed from the default system. Informational only. Can be set to a blank. Order Management System integration: If your default system is an Order Management System company, this field is the description of the item/SKU’s Long SKU Department. Long SKU departments are used to identify items within a retail hierarchy.
class	alphanumeric, 40 positions	The description of the product's class. Order Broker updates this field for the product only if it is passed from the default system. Informational only. Can be set to a blank. Order Management System integration: If your default system is an Order Management System company, this field is the description of the item/SKU’s Long SKU Class. Taken from the base item rather than the SKU if this is a SKU’d item. Long SKU classes can be used together with long SKU departments to identify items within a retail hierarchy.
sub_class	alphanumeric, 40 positions	The description of the product’s category. Order Broker updates this field for the product only if it is passed from the default system. Informational only. Can be set to a blank. Order Management System 18.2 or earlier integration: If your default system is an Order Management System company, this field is the description of the item/SKU’s Long SKU Division. Long SKU divisions can be used together with long SKU departments and classes to identify items within a retail hierarchy. Order Management System 18.3 or later: If your default system is an Order Management System, this field is either: The description of the item/SKU’s Long SKU Division, if the OROB Item Category Value (M54) system control value is set to LSDIVISION or blank. Long SKU divisions can be used together with long SKU departments and classes to identify items within a retail hierarchy. The item category code, if the OROB Item Category Value (M54) system control value is set to CATEGORY.
system_product_cd	alphanumeric, 35 positions	The system product code identifying the product in the external system. The system product code might differ from the product code if the external system is not the default system for the organization. If the system product code is already assigned to a different product in the system, there is no error, but the duplicate system product is not created. Required. Note: Oracle recommends that you do not use UPC codes as system product codes, because UPCs are not permanently assigned to a single product.
product_cd	alphanumeric, 35 positions	The product code identifying the item in the default system. If the load record is creating or updating a system product, the product_cd must be a valid product in Order Broker. There can be only one entry for the same product code in the import file. Required. Note: The import process does not flag the product as an error if the product code includes an invalid character, such as the ^ symbol; however, such special characters are not valid as part of the product code in Order Broker and can subsequently cause errors during standard processing.
product_description	alphanumeric, 40 positions	The Name of a product. Order Broker updates this field for the product only if it is passed from the default system. Required.
master_style_cd	alphanumeric, 35 positions	See master style. Optional field. Order Broker updates this field for the product only if it passed from the default system. Informational only. Can be set to a blank. Note: Normally, you would never change the master style for a product in Order Broker.
image_url	alphanumeric, 255 positions	Updates the Image URL for the product, indicating where to find the product image to display in Store Connect, when passed for the product in the default system. Optional field. Must be a validly formatted URL, such as `https://www.example.com/folder/image.png`, where: `http` or `https` is the protocol `www.example.com` is the domain or server name `folder` is a folder or subfolder where the image is found `image.png` or `image.jpg` is the name of the image Must not exceed 255 positions. If there was already an item image URL specified for the product, it is overwritten. If an item image URL was previously specified and a blank is passed in the import file, the item image URL is cleared. The import does not validate that an image is found at the specified URL.