Price Data Requirements

Pricing data (PRICE.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 the RI and AI Foundation 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
The first price file must include records for all active item/locations	Historical	The pricing fact is stored positionally, meaning that it first needs a starting position for an entity (for example, an initial price for an item/location) and then it may not be sent again unless there is a change to the value. The very first price file loaded into the data warehouse must contain a starting position for all active item/locations. How you determine which item/locations were active on that day in history will depend on your source system (for example, you can base it on items having stock on hand, historical ranging status, or a price history table if you have one).
The first record sent for an item/location must come as a new price transaction type	Historical and Ongoing	Price records have a column for the price type (`PRICE_CHANGE_TRAN_TYPE`) with a fixed list of possible values. The value `0` represents a new price, which means it is the first time our system is getting a price for this item/location. All item/locations must be given a type `0` record as their first entry in the historical or ongoing data files. All the initial position records in the first file will have `type=0`. Also, all new item/locations coming in later files must first come with `type=0`.
Price records should follow a specific lifecycle using the type codes	Historical and Ongoing	The typical flow of price changes that will occur for an item/location should be as follows: New price/cost (`PRICE_CHANGE_TRAN_TYPE=0`) Regular cost changes (`PRICE_CHANGE_TRAN_TYPE=2`) Regular price changes (`PRICE_CHANGE_TRAN_TYPE=4`) Promotional/temporary markdowns (`PRICE_CHANGE_TRAN_TYPE=9`) Clearance markdowns (`PRICE_CHANGE_TRAN_TYPE=8`) Price reset due to new selling cycle or season change (`PRICE_CHANGE_TRAN_TYPE=0`) An item/location may have many changes with types `2`/`4`/`8`/`9` before eventually staying at `8` (for a final markdown) or resetting to `0` (if the item lifecycle should restart).
Price changes are for the end-of-day value only	Historical and Ongoing	An item price may change many times a day, but you must only send the end-of-day final position for the item/location. The file interface assumes only one record will be sent per item/location/effective date, representing the final price on that date.

For historical loads, this results in the following flow of data across all your files:

Generate an initial position PRICE.csv that has all type=0 records for the item/locations you want to specify a starting price for. Load this as the very first file using the historical load ad hoc process.
Generate your first month of price change records. This will have a mixture of all the price change types. New item/location records may come in with type=0 and records already established can get updates using any of the other type codes. Only send records when a price or cost value changes; do not send every item/location on every date. You also must not send more than one change per item/location/date.
Repeat the monthly file generation (or more than one month if your data volume for price changes is low) and load process until all price history has been loaded for the historical timeframe.
When you are ready for the cutover to batches, you must also re-seed the positions of all item/locations that need a price 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 a price record added on day 1. Use the Seeding Ad Hoc process for Pricing to do this step, not the historical load.

In most cases, you will be providing the same set of price columns for any application. These columns are listed below with additional usage notes.

Column Header	Usage
ITEM	Must be the transaction-level item or SKU number and must have a record in the `PRODUCT.csv` file. You should provide a price record for all sellable or inventoried items.
ORG_NUM	Must be the location number where the item on the record is ranged to. Must have a record in `ORGANIZATION.csv` file. A price should be provided for every location where a transaction could occur for the item on the record, such as a sale or return.
DAY_DT	The date that the price record is for. In historical files it will be the effective date of the price change. In nightly batch files it must be the current system business date on all rows, you cannot post prices for any other date. The data sent nightly is for price changes effective for that one date.
PRICE_CHANGE_TRAN_TYPE	The type of price change event, represented by a numerical code as defined in the business rules earlier in this section. This is NOT part of the primary key, meaning that you can only provide a single price per item/location/date.
SELLING_UNIT_RTL_AMT_LCL	The current selling retail price of the item, at the specified location, on the specified business date. The selling price will generally reflect the current “ticket price” of the item that a customer would pay before transaction-level adjustments like coupons or loyalty awards. The price is also a key input to certain forecast functions (LLC, causal, promo lift).
BASE_COST_AMT_LCL	The unit cost of the item at this location. Only used by AI Foundation and Retail Insights reporting. In LPO, the price and cost are both used to determine the Gross Margin amount for the item/location in pricing objectives.
LOC_CURR_CODE DOC_CURR_CODE	The currency codes linked to the cost and retail amounts on the price data. The values in these fields will control how the system converts the amount (such as `SELLING_UNIT_RTL_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.
ORIG_SELLING_RTL_AMT_LCL	This is a nullable column provided for specifying the original full price of an item/location in cases where you need to update the original price as part of a different price change posting. For example, an item may begin selling at a store only after it has been marked down. The first price change event is a markdown (type 8) and you also populate this field on the same row to indicate what the original price was prior to the markdown.
LST_REG_RTL_AMT_LCL	This is a nullable column provided for specifying the most recent regular price of an item/location in cases where you need to update the regular price as part of a different price change posting. For example, an item may begin selling at a store only after it has been marked down. The first price change event is a markdown (type 8) and you also populate this field on the same row to indicate what the last regular price was prior to the markdown.