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 in RI, but most of the time only SLS or RET fields will have a value. These values will be subtracted from each other to display Net Sales Quantity metrics in RI, 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 in RI, but most of the time only SLS or RET fields will have a value. These values will be subtracted from each other to display Net Sales Amount metrics in RI, 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 RI 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 RI’s 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.
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 RI.