Inventory Data Requirements
Inventory data (INVENTORY.csv
) has several special requirements that need to be followed when generating
historical data and ongoing daily feeds, due to the way the data is stored within Retail Insights as well as the different
use-cases in each of the AI Foundation and Planning applications. A retailer may not have the data in the required format
in their source system, and adjustments would have to be made in the data extraction process to ensure these rules are followed.
Requirement | File Type | Explanation |
---|---|---|
Records may be needed before the item/location has any stock on hand |
Historical and Ongoing |
The inventory position contains fields for inventory movement, such as on-order, in-transit, and reserved quantities. As soon as any of those values may contain data for an item/location (and you intend to use those fields in RAP), a record should be included for inventory, even if the actual stock on hand is still zero that day or week. |
Zero balances may be needed for stock on hand |
Historical and Ongoing |
In general, you are not required to provide zero balance records in history data, just send the non-zero records. The need for zeros in the data falls into one of two use cases:
|
Clearance indicator is used to show the end-of-period status of the item/location’s inventory |
Historical and Ongoing |
Inventory data has a required column for a Clearance Flag
( |
Any change to values on the inventory position should send an update of the full record from the source system. |
Ongoing (Daily) |
If you are using other inventory values besides stock on hand (such as on-order or in-transit), you must ensure the extracts will send a complete record to the inventory interface when any of those values change. For example, a new item/location may carry an on-order balance or in-transit balance for several weeks before it has any stock on hand, so your extracts must trigger changes to those values, not just changes to stock on hand. |
For historical loads, this results in the following flow of data across all your files:
-
Generate the first month of week-ending inventory balances in
INVENTORY.csv
for all active item/locations in each week of data. Load using the historical inventory load ad hoc process. Make sure you load Receipts data in parallel with inventory data if you need to capture historical first/last receipt dates against the stock positions (for IPO or LPO usage). -
Repeat the monthly file generation process, including sets of week-ending balances in chronological order. Remember that you cannot load inventory data out of order, once a given intersection (item/loc/week) is loaded you cannot go back and reload or modify it without deleting it first. Make sure all the requirements listed in the table above are satisfied for every week of data. Depending on your data volumes you can include more than one month in a single file upload.
-
Load every week of inventory snapshots through to the end of your historical period. If there will be a gap of time before starting nightly batches, plan to load an additional history file at a later date to catch up. Make sure you continue loading Receipts data in parallel with inventory data if first/last receipt date calculations are needed.
-
When you are ready to cutover to batches, you must also re-seed the positions of all item/locations that need to have an inventory record on Day 1 of nightly batch execution (same as for all positional facts in RI). This is needed to fill in any gaps where currently active item/locations are not present in the historical files but need to have an inventory record added on day 1. Use the Seeding Adhoc process for Inventory to do this step, or include a full inventory snapshot file in your first nightly batch run to set all active positions.
The columns you provide on the inventory file will vary depending on your application needs (for example, you may not need the in-transit or on-order columns if you are only providing data for IPOCS-Demand Forecasting). The most commonly used columns are listed below with additional usage notes.
Column Header | Usage |
---|---|
ITEM |
Must be the transaction-level item or SKU number for the inventory position and must have a record in the |
ORG_NUM |
Must be the location number that is holding the inventory. Must have a record in the |
DAY_DT |
The date that the inventory position record is for. In historical files it must be a week-ending date. In nightly batch files it must be the current system business date on all rows; you cannot post inventory for any other date. |
CLEARANCE_FLG |
Must be a value of |
INV_SOH_QTY INV_SOH_COST_AMT_LCL INV_SOH_RTL_AMT_LCL |
Stock on hand and available to sell. The cost and retail amounts are the total cost/retail value of all units of stock. All the values on this interface are close-of-business values (for example, stock on hand at end of day or end of week). Pack item inventory should be broken down into their component quantities and amounts and summed together with the stock for the same SKUs held individually. Only one row should be provided per item/location/date. |
INV_IN_TRAN_QTY INV_IN_TRAN_COST_AMT_LCL INV_IN_TRAN_RTL_AMT_LCL |
Stock in-transit between two owned locations (such as from warehouse-to-store or store-to-store). This stock is shipped but not yet received. It will be summed together with SOH values for MFP to show total owned stock for a location, inclusive of units that will arrive at that location soon. All the same criteria listed for SOH values apply to these fields as well. |
INV_UNIT_RTL_AMT_LCL |
The base unit retail value of an item at the location for this stock position. Used by multiple
applications to display the retail value for a specific item/location in the context of owned inventory. May or may not be
the same value as the Selling Price provided on the |
INV_AVG_COST_AMT_LCL INV_UNIT_COST_AMT_LCL |
The average cost and unit cost of owned inventory for this item/location.
The default cost used by retailers is usually the average cost (also known as Weighted Average Cost or WAC). Other columns
like |
LOC_CURR_CODE DOC_CURR_CODE |
The currency codes linked to the cost and retail amounts on the inventory data. The
values in these fields will control how the system converts the amount (such as |