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: Daily Batch: If no change comes into the system on a given day, we carry forward that balance. This means that you cannot send only non-zero values in the daily data files, as it is assumed the last known value is also the current value for the day or week. You must send a zero balance any time the inventory has moved from non-zero to zero. History Load: The history file does not carry balances forward, the sent data is directly inserted. Null values are assumed to be zero in AIF and PDS. This means a zero balance record is unnecessary. However, for integration purposes, a zero balance stock-on-hand may be desired because it is used to determine the Markdown measure classification (clearance or non-clearance promo markdowns).
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 (`Y`/`N`) to indicate for a given day or week what the status of that item/location’s inventory is. The flag is intended to be the end-of-period clearance status of the item/location, so in the history data you should not send multiple records for the same item/location if the flag changed in the middle of the week. Send only one record with the correct flag for the end-of-week value. Default to `N` if you don’t use it or don’t know it.
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 `PRODUCT.csv` file. Should not be a pack item; all inventory data should be held against the individual components.
ORG_NUM	Must be the location number that is holding the inventory. Must have a record in the `ORGANIZATION.csv` file. If a location is flagged as non-stockholding in the `ORGANIZATION.csv` file, then it should not have any data in this file.
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 `Y` or `N` to indicate the inventory is on clearance or not. Differentiating inventory status is important to RI, AI Foundation, and Planning applications anywhere you would be viewing or planning clearance sales separately from regular sales. Depending on your planning configuration needs, you cannot plan/forecast clearance sales without also having clearance inventory (like when you are specifically forecasting clearance sales and want to compare to clearance inventory). If you are not implementing a solution that separates regular and clearance activities, then you would set this value to `N` on all rows.
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 `PRICE.csv` file depending on how the business calculates the retail value of owned inventory. Other columns like `INV_SOH_RTL_AMT_LCL` should be a multiplication of this value times the unit quantity.
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 `INV_SOH_COST_AMT_LCL` should be a multiplication of one of these values times the unit quantity.
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 `INV_SOH_COST_AMT_LCL`) and how the `EXCH_RATE.csv` file data will be used. If you are providing the data in the source currency from the store or warehouse location, then `DOC_CURR_CODE` will be the currency of the source location. If the data is already all on one currency, then `DOC_CURR_CODE` will be that currency code. `LOC_CURR_CODE` is the primary reporting currency you wish RAP applications to operate in, so it will generally be a single hard-coded value on all your data files. Review the section on Exchange Rate dimension data for additional scenarios.