Sales Data Requirements

Sales data (SALES.csv) operates with the assumption that the source system for the data is an auditing system (like Oracle Sales Audit) or non-Oracle data warehouse system. It applies minimal transformations to the inputs and assumes all the needed cleansing and preparation of transaction data has happened outside of RI. Whether you are sourcing history data from one of those systems or directly from a POS or non-Oracle auditing application, there are some business rules that should be followed.

Requirement	File Type	Explanation
Sales Units	Historical and Ongoing	The values provided for unit quantities should represent the total transaction-line values for an item, split across the gross sales units and return units. In the case of an exchange, you could have both sales and return units on the same line, but most of the time only SLS or RET fields will have a value. These values will be subtracted from each other to form Net Sales Quantity metrics, so they should almost always be positive.
Sales Retail Amounts	Historical and Ongoing	The retail amounts on a sale or return represent the actual selling/return value, after all discounts are subtracted from the base price of the item. In the case of an exchange, you could have both sales and return units on the same line, but most of the time only SLS or RET fields will have a value. These values will be subtracted from each other to form Net Sales Amount metrics, so they should almost always be positive.
Sales Profit Amounts	Historical and Ongoing	Profit calculations must take into consideration the cost of the item at the time it was sold, and will vary based on the retailer's costing methods. The standard approach is to use the value for Weighted Average Cost (WAC) multiplied by the units sold/returned, and subtract that total cost value from the retail amount. An item that is sold and later returned may not have the same profit amounts, if the cost has changed between the two transactions or the return was not for the full price of the item. Most POS systems do not track item costs, so providing this data requires an external process to do the calculations for you.
Sales Taxes	Historical and Ongoing	Tax amounts generally represent Value Added Tax (VAT); however this column could be used to capture other tax amounts if loading directly from the POS or external audit system.
Employee Discounts	Historical and Ongoing	These columns are specifically for employee discounts when the employee purchases a product at the POS and gets a special discount (or has the discounted amount returned later on). These values are just the discount amount, meaning the reduction in value from the selling price.
Promotional Discounts	Historical and Ongoing	These values represent the total discount taken off the initial selling price for the line-item in the transaction. These values will almost always be populated for promotional sales. However, a regular or clearance sale could have a further discount applied (like a coupon) and that should also be captured here. These values are used to populate the Sales Promotion fact table for retail type “P” transactions. So make sure that any change in price related to a promotion is included in this discount amount, so that it is copied into other tables for Promotion-level reporting.
Liabilities	Historical and Ongoing	Liabilities are sales that have not yet been charged to the customer, either due to layaway practices or customer orders that are posted as a liability transaction before they are fulfilled. Liabilities are posted with a positive value when incurred, and reversed with a negative value when they are converted into a regular sale or cancelled. Liabilities are a separate set of metrics in RI and do not interact with sales values, as it is expected that the liability will always result in a cancellation or a sale being posted at a later date.
Liability Cancels	Historical and Ongoing	Liabilities that are cancelled should first be reversed and then posted to these fields as a positive amount. A cancelled liability will have no impact on sales and has a separate set of metrics in RI. The retailer can use liability cancellation metrics to track the value of customer orders that were cancelled before being charged.
Retail Type	Historical and Ongoing	The retail type represents the category of sale as one of Regular, Promotion, or Clearance. We use the codes R/P/C to denote this value. The priority order when assigning a code is C > P > R, meaning that a clearance sale will always have a clearance type, even if it is also affected by a promotion. This matches the financial practices of RMFCS and Sales Audit, which treat all clearance sales as clearance activity on the stock ledger.
Transaction Reversals and Revisions	Historical and Ongoing	When using Sales Audit to audit sales, the export process will automatically handle reversing a transaction and posting revisions to a transaction. Without that, you must manually create a process to send reversals and revisions to the data warehouse matching the same data format. These two records come at the same time. A reversal is an exact opposite of the original transaction line (usually all negative values, unless the original value was negative). This will be added to existing data and zero it out. The revision record should come next and contain the current actual values on the transaction (not the delta or difference in values). Keep in mind that, depending on the method used for calculating sales cost/profit amounts, a reversal and revision may have different values from the original profit amount. This could result in a very small residual profit from the prior revision.
Backposted Sales	Ongoing (Daily)	Sales can be backposted for days prior to the current business date. They will be loaded against their backposted transaction date and aggregated up into the existing sales data for that date. No transformations are done, even if the backposted sale is significantly older (for example,1+ years ago). It will be inserted and aggregated using that past date.
Original Selling Locations	Historical and Ongoing	When including the original selling location of a return transaction, you must also make sure that is a real location included on your Organization input data. Some source systems allow the manual entry of a return’s original selling location, so ensure that all such locations are included, or the records will be rejected by the data warehouse.

The columns you provide in the sales file will vary greatly depending on your application needs (for example ,you may not need the sales profit columns if you don’t care about Sales Cost or Margin measures). 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 sale and must have a record in the PRODUCT.csv file.
ORG_NUM	Must be the store or warehouse number that will get credit for the sale. Note that the location where the sale occurred is not always the location that should be credited for it (for example, a web order placed in-store can still be credited to a web location). Must have a record in ORGANIZATION.csv file.
DAY_DT	The date that the transaction occurred in the source system. Must be a valid date within the periods in the CALENDAR.csv file.
MIN_NUM	Hour and minute of the transaction in 24-hour, 4-digit format; for example, 11:23 PM would be 2323. Currently only used in Retail Insights reporting; default to 0 if not needed.
IT_SEQ_NUM	Sequence of a line in the transaction. Every line of a transaction must have its own sequence number. This facilitates uniqueness requirements where the same item could have multiple lines. Without a unique sequence number, the line would not be unique on the input file and the system would see them as duplicate records (duplicate sales lines without a sequence number will be ignored). If you are pre-summing your sales lines such that there will never be duplicate rows, then this column is not needed.
RTL_TYPE_CODE	This is the sales type using one of (R, P, C), where R = regular, P = promotion, C = clearance. This is a critical piece of information for many AIF and Planning purposes. For example, in some AIF modules like SPO, you can choose to include or exclude sales by retail type in the calculations. In forecasting, you can generate different forecasts based on the retail type, which can feed into Planning measures which are split by reg/pro/clr designations.
SLS_TRX_ID	Unique identifier for a sales transaction. By default, it is expected sales data will come at the most granular level (transaction-line). If you are not able to provide true transaction-level data, you can specify some other unique value in this field. This value is part of the business key for the table, so you need to be able to reference the same keys over time (such as when revising or reversing existing transactions).
PROMO_ID PROMO_COMP_ID	This two-part identifier maps to the Promotion and Offer associated with a transaction line. They are required if you wish to load sales by promotion for Retail Insights or certain AI Foundation modules such as Demand Transference. They are also required if you wish to perform promotion lift estimation as part of IPOCS-Demand Forecasting, since the system needs to know the specific promotion linked to a set of sales transactions. When providing these values, also provide the PROMOTION.csv file.
CUSTOMER_NUM CUSTOMER_TYPE	Customer identifier on a sales transaction, which is a critical piece of information for many AI Foundation modules, such as Customer Segmentation and Consumer Decisions Trees. CUSTOMER_TYPE should not be used at this time, it serves no purpose in downstream modules. If you do include the column, hard-code it to a value of CUSTID on all rows, otherwise CUSTOMER_NUM values will be ignored.
SLS_QTY SLS_AMT_LCL SLS_PROFIT_AMT_LCL SLS_TAX_AMT_LCL	Represents the gross sales values for the transaction line (meaning before returns). Will almost always be positive, except in cases of negative profit amounts or if you are reversing a prior transaction line to zero out the amounts. Tax amount is for VAT and other special taxes outside the United States (should not be used for US sales tax).
RET_QTY RET_AMT_LCL RET_PROFIT_AMT_LCL RET_TAX_AMT_LCL	Represents customer return transactions. Will almost always be positive, except in cases of negative profit amounts or if you are reversing a prior transaction line to zero out the amounts. Both gross sales and returns are positive because they are subtracted from each other to determine net sales. Tax amount is for VAT and other special taxes outside the United States (should not be used for US sales tax).
REVISION_NUM	If you will be allowing transactions to get revisions from your source system (such as when a sales audit system changes the sales amount after a user audited the transaction) then you should use this field to track the revision number. The initial transaction should come as 1 and later revisions should be posted with a value of 2 or greater. Revision numbers will be stored on the data warehouse table for tracking and auditing purposes.
LOC_CURR_CODE DOC_CURR_CODE	The currency codes linked to the sales amounts on the transaction. The values in these fields will control how the system converts the amount (such as SLS_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 point of sale, then DOC_CURR_CODE will be the currency of the source system. 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.