Fact Data Key Columns

Fact data files, such as sales and inventory, share many common characteristics and standards that can be followed regardless of the specific file. The table below summarizes those key elements and their minimum requirements. You will generally always want to populate these fields where they exist on an interface file.

Table 7-9 Common Fact Data Fields

Column Header	Usage
ITEM	Unique identifier of a transaction item. Must align with the product records in the PRODUCT.csv file where ITEM_LEVEL = TRAN_LEVEL.
ORG_NUM	Unique identifier of an organizational entity. Must align with the location records in the ORGANIZATION.csv file.
DAY_DT	Transaction date or business date for the fact data entry, formatted as YYYYMMDD. Must be a valid date within the range of periods in CALENDER.csv.
RTL_TYPE_CODE	Retail types define a general category for the record that varies by interface. For Sales and Markdowns, it must be one of R/P/C, representing the regular/promo/clearance status of the transaction.
*_QTY	Fields ending with QTY represent the quantity or units of an item on the record, such as the units sold on a transaction line or the units of on-hand inventory.
*_AMT_LCL	Fields containing AMT_LCL represent the currency amount of a record in the local currency of the source system, such as sales retail amount in Canadian dollars from a Canada location, or the cost value of on-hand inventory at your U.S. warehouse.
DOC_CURR_CODE	The original document currency of the record in the source system. For example, a sale made in Canada may have a value of CAD in this field.
LOC_CURR_CODE	The local operating currency of your main office or headquarters. A company based in the United States would use USD in this field.

Nearly all fact files share a common intersection of an item, location, and date as specified above. Such files are expected to come into the platform on a nightly basis and contain that day’s transactions or business activity.

Most fact data also supports having currency amounts in their source currency, which is then automatically converted to your primary operating currency during the load process. There are several currency code and exchange rate columns on such interfaces, which should be populated if you need this functionality. The most important ones are shown in the list above, and other optional column for global currencies can be found in the Interfaces Guide. When you provide these fields, they must all be provided on every row of data, you cannot leave out any of the values or it will not load properly.

In addition to the commonly used fields, most files have other key columns that can be used to specify important details about the record. Some of these fields are marked as optional in the full interface specifications, meaning that if they are not provided, then some default values will be set for you.

Table 7-10 Additional Fact Data Key Columns

File	Column Header	Usage
SALES	SLS_TRX_ID	Unique identifier of 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 transaction-level data, specify another unique value in this field.
SALES	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.
SALES	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 (and they will not load properly as a result).
SALES	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 Science modules such as Demand Transference.
SALES	CASHIER_ID SALES_PERSON_ID	Cashier or salesperson associated with the transaction. Currently used only for Retail Insights.
SALES	CO_HEAD_ID CO_LINE_ID	Order ID and line ID for a customer order, such as an online sale. Currently only used by Retail Insights.
SALES	CUSTOMER_NUM CUSTOMER_TYPE	Customer identifier on a sales transaction, which is a critical piece of information for many Science 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, or CUSTOMER_NUM will be ignored.
SALES_PACK	ITEM PACK_NUM	ITEM on the sales pack interface is the component item inside a selling pack. The PACK_NUM is the identifier for the pack sold to the customer. The interface itself should be pro-rated component level sales spread down from the selling pack level. Only component sales are used in Planning.
INVENTORY	CLEARANCE_FLG	Y/N indicator for whether the item/location is currently on clearance.
RECEIPT	INVRC_TYPE_CODE	Indicates the type of receipt into a location using merchandising transaction codes 20 (purchase order receipt), 44~A (allocation transfer receipt), or 44~T (non-allocation transfer receipt). Only PO receipts are sent to planning applications, as they represent actual inventory entering the company, and not just movement between two owned locations.
ADJUSTMENT	REASON_CODE	User-defined reason for making the inventory adjustment. Currently used only for Retail Insights.
ADJUSTMENT	INVADJ_TYPE_CODE	Indicates the type of adjustment to inventory using merchandise transaction codes 22 (non-shrink adjustment), 23 (shrink adjustment), or 41 (stock count adjustment). The codes determine the associated Planning measures the data is placed into.
TRANSFER	FROM_ORG_NUM FROM_CLEARANCE_FLG FROM_*	Transfers are created for both the source of the transfer (the FROM location) and the destination (the TO location). Fields not having a FROM_ prefix specify the destination of the transfer.
TRANSFER	TSF_TYPE_ID	The type of transfer in the source system. It must be N, B, or I, representing a normal, book, or intercompany transfer.
RTV	INV_STATUS	Status codes for the type of inventory being returned to a vendor, such as damaged or unsellable.

Here are sample records for commonly used historical load files having a small set of fields populated. These fields are sufficient to see results in RI reporting and move the data to RSP or MFP but may not satisfy all the functional requirements of those applications. Review the interfaces guide for complete details on required/optional columns on these interfaces.

SALES.csv:

ITEM,ORG_NUM,DAY_DT,MIN_NUM,RTL_TYPE_CODE,SLS_TRX_ID,PROMO_ID,PROMO_COMP_ID,CASHIER_ID,REGISTER_ID,SALES_PERSON_ID,CUSTOMER_NUM,SLS_QTY,SLS_AMT_LCL,SLS_PROFIT_AMT_LCL,RET_QTY,RET_AMT_LCL,RET_PROFIT_AMT_LCL,TRAN_TYPE,LOC_CURR_CODE,DOC_CURR_CODE
1235842,1029,20210228,0,R,202102281029,-1,-1,96,19,65,-1,173,1730,605.5,0,0,0,SALE,USD,USD
1235842,1029,20210307,0,R,202103071029,-1,-1,12,19,55,-1,167,1670,584.5,0,0,0,SALE,USD,USD
1235842,1029,20210314,0,R,202103141029,-1,-1,30,18,20,-1,181,1810,633.5,0,0,0,SALE,USD,USD

INVENTORY.csv:

ITEM,ORG_NUM,DAY_DT,CLEARANCE_FLG,INV_SOH_QTY,INV_SOH_COST_AMT_LCL,INV_SOH_RTL_AMT_LCL,INV_UNIT_RTL_AMT_LCL,INV_AVG_COST_AMT_LCL,INV_UNIT_COST_AMT_LCL,PURCH_TYPE_CODE,DOC_CURR_CODE,LOC_CURR_CODE
72939751,1001,20200208,N,0,0,0,104.63,0,48.52,0,USD,USD
73137693,1001,20200208,N,0,0,0,104.63,0,48.52,0,USD,USD
75539075,1001,20200208,N,0,0,0,101.73,0,47.44,0,USD,USD

PRICE.csv:

ITEM,ORG_NUM,DAY_DT,PRICE_CHANGE_TRAN_TYPE,SELLING_UOM,STANDARD_UNIT_RTL_AMT_LCL,SELLING_UNIT_RTL_AMT_LCL,BASE_COST_AMT_LCL,LOC_CURR_CODE,DOC_CURR_CODE
89833651,1004,20200208,0,EA,93.11,93.11,53.56,USD,USD
90710567,1004,20200208,0,EA,90.41,90.41,50.74,USD,USD
90846443,1004,20200208,0,EA,79.87,79.87,44.57,USD,USD