Importing Product Locations through File Storage API

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

Note:

This help topic does not address importing product location data from OCDS or individual inventory updates through the RIB. See OCDS Imports or Available-to-Sell Individual Inventory Updates through Oracle Retail Integration Cloud Service (RICS) for more information. However, Product Location Import Error Files, below, does provide information on possible product location import errors, including errors that occur through the import of store or warehouse inventory through OCDS.

Required setup: To import product locations to the product_location table:

• Create a pipe-delimited flat file that includes a separate row for each product location. See Sample Product Location Import File for a sample of the data to include in the file.
• The file should be named PRODUCT_LOCATION_SYS_NNNNN.TXT, where SYS is the associated system code and NNNNN is an optional series of characters, such as a date/time stamp, a location code, or both. 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. See the Product Location Import Steps for a discussion.
• The process looks in the OROB-IMPORTS container of the FILE_STORAGE table. The file remains in this location until you run the import, as described below.

Note:

You can also create product locations 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.
• Days to Keep Errors for more information on setting up the import schedule for a system, or to run the import process on demand.
• the Product Imports History screen for more troubleshooting information related to the import process.

Product Location Import Steps

Use the Product Import option to import product, system product, bar code, and location data from pipe-delimited files. The import steps related specifically to product location import:

1. The process uses the pipe-delimited flat file. The file should be named PRODUCT_LOCATION_SYSNNNNN.TXT, where:

   • SYS is the associated system code. The system code here should match the case for the code in your organization.

   • NNNNN is an optional suffix, which can include information such as a date/time stamp or a location code. The use of a date/time stamp can be useful if the integrating inventory system generates update files multiple times in a day.

   Multiple file processing:

   • If you include an optional suffix in the file name and there is more than one product location file in the FILE_STORAGE table, the files are processed in alphanumeric order. For example, if there are files named PRODUCT_LOCATION_INV_123_20161231010101.TXT and PRODUCT_LOCATION_INV_123_20161231040101.TXT, the PRODUCT_LOCATION_INV_123_20161231010101.TXT file is processed first.

   • If one file has an optional suffix and one does not, the file without the suffix is processed first.

   • Numeric suffixes are sorted before alphabetical suffixes. For example, a suffix that starts with 123 is processed before a suffix that starts with DC.

   • f a product location is included in more than one import file, the product location is overwritten. For example, a product location is in two files, and the first file has an available quantity of 100, while the next file processed has an available quantity of 98. After processing both files, the available quantity is set to 98.

   File name matching:

   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 import file(s) must be in the OROB-IMPORTS container of the FILE_STORAGE table.

   Job Batch Size: The Job Batch Size controls the number of records to process in each batch.

2. If there are any errors in the file that prevent the process from moving each record to the product_location table, it moves the records in error in the import file to the OROB-ERRORS container in the file storage table, adding a date and time stamp to the name of the file, such as PRODUCT_LOCATION_SYS.TXT.20161028.153000.bak. See Product Location Import Error Files, below, for more information.

3. If there are no errors for an import record, the process updates the record in the product_location table.

   Note:

   The import process ignores any files that do not conform to the file naming convention or do not match the system running the import. It does not report an error if no matching files are found in the FILE_STORAGE table.

4. 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 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.

For more information: See Product Location Import Error Files, below, for information on possible errors.

Sample Product Location Import File

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

system_cd|location_cd|product_cd|available_qty|next_po_qty|next_po_date|daily_sell_through_qty|sell_qty_multiple|minimum_sell_qty|shrink_rate|sales_velocity|status|clearance|selling_price|cost

cwdDoc|10|cumin|100|1|2015-08-27|1|2||4|5|A|Y|19.99|2.3456

The import does not attempt to process the first row in the file; however, the number of columns in the header row is used to validate the number of columns, including empty columns, in each of the import records in the file. The total number of columns in the header row needs to match the number of columns for each record.

Product Location Import Mapping

The table below lists the fields in the product location import flat file and the product_location table.

Availability information and attributes: For an existing product location, the import can update either the availability information (available quantity, next PO quantity, and next PO date), any or all of the attributes, or both.

To create a new product location, the availability information is required.

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.
• If any optional fields in the import file are left blank--that is, the file includes the pipe delimiters without a space or zero between them--the import does not update these fields.

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 where the import process is being run, but does not need to be the same as the system running the import. Required.
location_cd	alphanumeric, 10 positions	The location where the product is stocked in the external system. Required.
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. Required.
available_qty	numeric, 6 positions	The current quantity of the product available to sell in this location as of the time of the import process. Used to calculate the available to promise quantity. A negative quantity, preceded by a minus sign (-), indicates that the item is backordered. If the quantity passed includes a decimal, it is truncated; for example, if a quantity if 5.75 is passed, the available quantity is set to 5. Note: If no available quantity is passed: The next PO quantity and next PO date are not updated. However, if the available quantity specified is 0 or negative, the PO information is updated. The product location attributes are still updated if the product location already exists; however, the import does not create a new product location. Optional, but can be set to 0.
next_po_qty	numeric, 6 positions	The quantity ordered for this product on the next purchase order for this location. Not updated if no available quantity is passed. If the quantity passed includes a decimal, it is truncated; for example, if a quantity if 5.75 is passed, the available quantity is set to 5. Optional, but required if the available quantity is passed.
next_po_date	datetime	The next date when a purchase order for this product is expected for delivery in this location. YYYY-MM-DD format. If no time is specified in the file, a time of 12:00:00 AM is appended. Can be blank, even if there is a next_po_qty. Not updated if no next PO quantity is passed. Note: The next PO date is cleared if no date is passed in the import file, but the next PO quantity is passed.
Note: If the availability fields are valid but there is an error related to one of the attributes, the product location is created or updated with the availability information. Attributes: The following product attributes are available to guide selection of fulfilling or sourcing locations for orders: probability rules: See the Probability Rule Wizard for more information standard brokering: optionally, the Routing Engine can use the Sales Velocity for ranking weighted brokering: the Science Engine can use various product location attributes based on the configured weighted brokering percentages Each of the product attributes are user-defined.
daily_sell_through_ qty	numeric, up to 6 positions	The Daily Sell Through Quantity. This quantity can be up to 6 positions, and must be a whole number. It cannot be a negative number. Optional.
sell_qty_multiple	numeric, up to 6 positions	The Sell Quantity/Multiple. This quantity can be up to 6 positions, and must be a whole number. It cannot be a negative number. Optional.
minimum_sell_qty	numeric, up to 6 positions	The Minimum Sell Quantity. This quantity can be up to 6 positions, and must be a whole number. It cannot be a negative number. Optional.
shrink_rate	numeric, up to 3 positions	The Shrink Rate %. This percentage can be up to 3 positions, and must be a whole number from 0 to 100. It cannot be a negative number. Optional.
sales_velocity	numeric, 2 positions with an optional 2-place decimal	The Sales Velocity. Can be blank, or any number from 0 to 99.99. It cannot be a negative number. Optional.
The following columns can each be omitted entirely, instead of just leaving them blank (for example, ||); however, you must not only omit the column for all records in the import file, but also omit the column from the file headers. If the number of columns included for a product location record is different from the number of columns in the file headers, the record is in error, for example: Invalid number of import columns, 14 passed. Example: A record with all fields included, with some optional fields blank: OBRDoc|10|cumin|100|1|2015-08-27|1|2||||A|Y|19.99|2.3456 A record with the status, clearance, selling_price, and cost omitted entirely, and also some optional fields blank: OBRDoc|10|cumin|100|1|2015-08-27|1|2||||
status	alphanumeric, 1 position	The status of the product in this location. Optional. Informational only. Possible statuses are: A or blank = Active (default). The product can be ordered. If the status in the import file is blank, this field is set to A for a new product location, and the Status for an existing product location is not updated. I = Inactive. Cannot currently be ordered. C = Discontinued. Can still be ordered, but will be inactive at some point in the future.
clearance	alphanumeric, 1 position	Indicates whether the product is on clearance in this location. Optional. Possible settings are: Y = The product is on clearance. N or blank (default) = The product is not on clearance. If the status in the import file is blank, this field is set to N for a new product location, and the On Clearance setting for an existing product location is not updated. Used in LocateItems Sequence and Splitting Examples (Standard Brokering) calculation. If you use LocateItems Sequence and Splitting Examples (Standard Brokering) and this flag is selected, the Science Engine uses a selling price of .01 to calculate margin.
selling_price	numeric, 19 positions with a 4-place decimal	The single-unit selling price of the product in this location. Can be up to 19 positions with a 4-position decimal. It can also be 0, but cannot be a negative number, and should not include a currency symbol. Optional, but should be specified if Gross Margin is used in the LocateItems Sequence and Splitting Examples (Standard Brokering) calculation.
cost	numeric, 19 positions with a 4-place decimal	The single-unit cost of the product in this location. Optional. It can be 0, but cannot be blank or a negative number, cannot exceed the specified field length, and should not include a currency symbol. The single-unit cost of the product in this location. Optional, but should be specified if Gross Margin is used in the LocateItems Sequence and Splitting Examples (Standard Brokering) calculation.

Product Location Import Error Files

Each submitted product location record that is in error is included in a file n the OROB-ERRORS container of the FILE_STORAGE table.

The file name is PRODUCT_LOCATION_SYS.TXT.20161016.160011.bak, where PRODUCT_LOCATION_SYS.TXT is the name of the original import file, and 20161016.160011 are the date and time for the import, in YYYYMMDD.HHMMSS format.

How errors are indicated in the error file: The error file is in the same format as the product location import file, except that for each record included in the file, there is an additional column entitled error_column. This column indicates the column that contained an error that prevented the record from processing. For example, if the product code for an import record was invalid, the error_column indicates product_cd. The header row is included if the error is not related to the number of columns.

1. The error file does not indicate the nature of the problem with a particular field. For example, if the error_column indicates selling_price, it does not indicate whether the selling price was invalid because it included too many positions or a non-numeric character.
2. If one or more required columns are missing for a record, the error resembles Invalid number of import columns, 10 passed.In this situation, the header row is not included in the error file.
3. If there were no records in the import file, the error_column indicates Import file has no lines to import.

OCDS imports: If any errors occur through the import of warehouse or store inventory through OCDS Imports, an error file is also created in the OROB-ERRORS folder of the file storage table, as described above. In this case, the file name includes the OCDS prefix (for example, OCDS_PRODUCT_LOCATION_SYS.TXT) and the only errors that might be included in the error file are those related to location code, product code, or available quantity. The OCDS import does not update all available fields in the product location table, and there are no possible errors related to the system code, because the code used is from the system you selected when running or scheduling the import at the Schedule Jobs screen.