8 Data File Generation
When you are implementing the Retail Analytics and Planning without using an Oracle merchandising system for foundation data, or you are providing history data from non-Oracle sources, you will need to create several data files following the platform specifications. This chapter will provide guidance on the data file formats, structures, business rules, and other considerations that must be accounted for when generating the data.
Important:
Do not begin data file creation for RAP until you have reviewed this chapter and have an understanding of the key data structures used throughout the platform.For complete column-level definitions of the interfaces, including datatype and length requirements, refer to the RI and AI Foundation Interfaces Guide in My Oracle Support. From the same document, you may also download Data Samples for all of the files covered in this chapter.
Files Types and Data Format
The shared platform data files discussed in this chapter may use a standard comma-delimited (CSV) file format, with text strings enclosed by quotation marks or other characters. The files must be UTF-8 encoded; other encoding types such as ANSI are not supported and may fail in the loads due to unrecognized characters. The files expect the first line to be column headers, and lines 2+ should contain the input data. For specific columns in each of these files, the following standards can be used as a guideline (though they can be changed by configuration options).
Table 8-1 Foundation File Formatting
Datatype | Format | Example | Explanation |
---|---|---|---|
Number |
0.00 |
340911.10 |
Numbers should be unformatted with periods for decimal places. Commas or other symbols should not be used within the numerical values. |
Character |
“abc” |
“Item #4561” |
Any alphanumeric string can be optionally enclosed by quotation marks to
encapsulate special characters such as commas in a descriptive value. Unique identifier columns, like |
Date |
YYYYMMDD |
20201231 |
Dates should be provided as simple 8-digit values with no formatting in year-month-day sequences. |
Context Files
Before creating and processing a data file on the platform, choose the fields that will be populated and instruct the platform
to only look for data in those columns. This configuration is handled through the use of Context (CTX) Files that are uploaded
alongside each base data file. For example, the context file for PRODUCT.csv
will be PRODUCT.csv.ctx
(appending the .ctx
file descriptor to the end of the base filename).
Within each context file you must provide a single column containing:
-
One or more parameters defining the behavior of the file load and the format of the file.
-
The list of fields contained in the source file, in the order in which they appear in the file specification:
-
#TABLE#<Staging Table Name>#
-
#DELIMITER#<Input Value>#
-
#DATEFORMAT#<Input Value>#
-
#REJECTLIMIT#<Input Value>#
-
#RECORDDELIMITER#<Input Value>#
-
#IGNOREBLANKLINES#<Input Value>#
-
#SKIPHEADERS#<Input Value>#
-
#TRIMSPACES#<Input Value>#
-
#TRUNCATECOL#<Input Value>#
-
#COLUMNLIST#<Input Value>#
<COL1>
<COL2>
<COL3>
-
The following is an example context file for the CALENDAR.csv
data file:
File Name: CALENDAR.csv.ctx
#TABLE#W_MCAL_PERIOD_DTS#
#DELIMITER#,#
#DATEFORMAT#YYYY-MM-DD#
#REJECTLIMIT#1#
#RECORDDELIMITER#\n#
#IGNOREBLANKLINES#false#
#SKIPHEADERS#1#
#TRIMSPACES#rtrim#
#TRUNCATECOL#false#
#COLUMNLIST#
MCAL_CAL_ID
MCAL_PERIOD_TYPE
MCAL_PERIOD_NAME
MCAL_PERIOD
MCAL_PERIOD_ST_DT
MCAL_PERIOD_END_DT
MCAL_QTR
MCAL_YEAR
MCAL_QTR_START_DT
MCAL_QTR_END_DT
MCAL_YEAR_START_DT
MCAL_YEAR_END_DT
The file must be UNIX formatted and have an end-of-line character on every line, including the last one. As shown above,
the final EOL may appear as a new line in a text editor. The #TABLE#
field is required: it indicates the
name of the database staging table updated by the file. The COLUMNLIST
tag is also required: it determines
the columns the customer uses in their .dat
or .csv
file. The column list must match the
order of fields in the file from left to right, which must also align with the published file specifications. Include the
list of columns after the #COLUMNLIST#
tag. Most of the other parameters are optional and the rows can be
excluded from the context file. However, this will set values to system defaults that may not align with your format.
Note:
Both RI and AI Foundation can use these context files to determine the format of incoming data.The server maintains a copy of all the context files used, so you do not need to send a context file every time. If no context files are found, the Analytics and Planning uses the last known configuration.
For additional format options, the available values used are from the DBMS_CLOUD package options in ADW.
If you want to retrieve the latest copy of the context files, the RI_ZIP_UPLOAD_CTX_JOB
job in process RI_ZIP_UPLOAD_CTX_ADHOC
can be run from the AIF DATA standalone schedule in POM. This job will extract all the context
files from the custom_ext_table_config
directory, package them in a zip file, and upload that file to Object
Storage. The zip file is named RAP_CTX.zip
, and will use ris/outgoing
as the prefix for
File Transfer Services (FTS) to access it.
In addition to being able to obtain copies of the files, there is also a database table named RI_DIS_ADW_EXT_TABLE_CTX_CONFIG
that holds the context file information that was last uploaded to the database. Except for the COLUMN_LIST
and FORMAT_OPTIONS
columns, the data in the other columns on the table is editable using the Control &
Tactical Center screen in AI Foundation, so you can provide override values. The table does not have any initial data; it
is populated when a CTX file is processed by RI_UPDATE_TENANT_JOB
. When a CTX file is provided and data is
also present in the table, the priority is to use the CTX file. If a CTX file was not provided in the current batch run, then
the data on this table will be used. After the batch run, this table will reflect the most recently used CTX file configurations.
A change in format data in the table will trigger an update to ADW only if the values are different from what was last
sent. This is done by comparing the entries at the FORMAT_OPTIONS
column. Modifying the COLUMN_LIST
in this table will not trigger a request to ADW to update the options. COLUMN_LIST
is not editable through
the Control & Tactical Center screen, as it only serves as a view to show the contents of the last payload sent to ADW.
Sending the updates through a CTX file is the preferred method for modifying the column list. If no CTX files are provided,
the RI_UPDATE_TENANT_JOB
will end immediately instead of pushing the same configurations to ADW again. If
you notice slow performance on this job then you can stop providing CTX files when they are not changing, and the job will
finish within 10-20 seconds.
There is an OVERRIDE_REJECTLIMIT_TO_DT
column on the table that will determine whether a REJECTLIMIT
value other than zero is used. If this date column is null or is already past the current date, then the REJECTLIMIT
will be reset to 0
and will trigger an update to ADW. The REJECTLIMIT
value provided in
the table will be used until the date specified in this column.
Application-Specific Data Formats
Each application within the Retail Analytics and Planning may require data to be provided using specific rules and data formats, which can differ from those used in the common platform files. This section describes the use-cases for alternate data formats and lays out the basic rules that must be followed.
Retail Insights
Retail Insights has a large number of legacy interfaces that do not follow the shared platform data formats. These interfaces
are populated with files named after their target database table with a file extension of .dat
, such as W_PRODUCT_DS.dat
. All files ending with a .dat
extension are pipe-delimited files by default (using
the |
symbol as the column separator) but can be changed using CTX file options. These files also have a
Unix line-ending character by default, although the line-ending character can be configured to be a different value, if needed.
These files may be created by a legacy Merchandising (RMS) extract process or may be produced through existing integrations
to an older version of RI or AI Foundation.
Table 8-2 Retail Insights Legacy File Formatting
Datatype | Format | Example | Explanation |
---|---|---|---|
Number |
0.00 |
340911.10 |
Unformatted numbers with periods for decimal places. Commas or other symbols cannot be used within the numerical values. |
Character |
abc |
Item #4561 |
Any alphanumeric string will provided as-is, with the exception that it must NOT contain pipe characters or line-ending characters. |
Date |
YYYY-MM-DD;00:00:00 |
2020-05-09;00:00:00 |
Dates without timestamps must still use a timestamp format,
but they must be hard-coded to have a time of 00:00:00. Date fields (such as |
Timestamp |
YYYY-MM-DD;HH:MM:SS |
2020-05-09;09:35:19 |
Use full date-and-time formatting ONLY when a full timestamp is expected on the column. This is not commonly used and should be noted in the interface specifications, if supported. |
This file format is used when integrating with legacy solutions such as the Retail Merchandising System (RMS) through the Retail Data Extractor (RDE) on v19 or earlier versions.
Example data from the file W_RTL_PLAN1_PROD1_LC1_T1_FS.dat
:
70|-1|13|-1|2019-05-04;00:00:00|RETAIL|0|1118.82|1|70~13~2019-05-04;00:00:00~0
70|-1|13|-1|2019-05-11;00:00:00|RETAIL|0|476.09|1|70~13~2019-05-11;00:00:00~0
70|-1|13|-1|2019-05-18;00:00:00|RETAIL|0|296.62|1|70~13~2019-05-18;00:00:00~0
Retail AI Foundation Cloud Services
Modules within the AI Foundation Cloud Services leverage the same Context (CTX) file concepts as described in the common foundation file formats. You may control the structure and contents of AI Foundation files using the parameters in the context files. The full list of interfaces used by AI Foundation modules is included in the Interfaces Guide.
Planning Platform
Planning solutions using PDS (Planning Data Schema), such as Merchandise Financial Planning, have two main types of files:
Hierarchy/Dimension Files – Foundation Data for the Hierarchy/Dimensions.
Measure/Fact Files – Factual Data specific to loadable metric/measures.
When loading directly to Planning applications, both types of files should only be in CSV format and they should contain headers. Headers contain the details of the dimension names for Hierarchy/Dimension Files and the fact names for Measure/Fact Files.
Hierarchy/Dimension Files uses the naming convention <Hierarchy Name>.hdr.csv.dat
and Measure Files
can be any meaningful fact-grouping name, but with allowed extensions such as .ovr
, .rpl
, or .inc
.
-
OVR extension is used for override files
-
RPL extension is used to delete and replace position-based data sets
-
INC extension is for incremental files that can increment positional data.
If using the common foundation CSV files, most of the data can be interfaced using those shared integrations. However, certain files (such as the VAT Hierarchy) must be directly loaded to Planning: it does not come from RI at this time. Refer to the application-specific Planning Implementation Guides for more details about the list of files that are not included in foundation integrations.
Dimension Files
A dimension is a collection of descriptive elements, attributes, or hierarchical structures that provide context to your business data. Dimensions tell the platform what your business looks like and how it operates. They describe the factual data (such as sales transactions) and provide means for aggregation and summarization throughout the platform. Dimensions follow a strict set of business rules and formatting requirements that must be followed when generating the files.
There are certain common rules that apply across all of the dimension files and must be followed without exception. Failure to adhere to these rules may result in failed data loads or incorrectly structured datasets in the platform.
-
All dimension files must be provided as full snapshots of the source data at all times, unless you change the configuration of a specific dimension to be
IS_INCREMENTAL=Y
where incremental loads are supported. Incremental dimension loading should only be done once nightly/weekly batch processing has started. Initial/history dimension loads should always be full snapshots. -
Hierarchy levels must follow a strict tree structure, where each parent has a 1-to-N relationship with the children elements below them. You cannot have the same child level identifier repeat across more than one parent level, with the exception of Class/Subclass levels (which may repeat on the ID columns but must be unique on the UID columns). For example, Department 12 can only exist under Division 1, it cannot also exist under Division 2.
-
Hierarchy files (product, organization, calendar) must have a value in all non-null fields for all rows and must fill in all the required hierarchy levels without exception. For example, even if your non-Oracle product data only has 4 hierarchy levels, you must provide the complete 7-level product hierarchy to the platform. Fill in the upper levels of the hierarchy with values to make up for the differences, such as having the division and group levels both be a single, hard-coded value.
-
Any time you are providing a key identifier of an entity (such as a supplier ID, channel ID, brand ID, and so on) you should fill in the values on all rows of the data file, using a dummy value for rows that don’t have that entity. For example, for items that don’t have a brand, you can assign them to a generic “No Brand” value to support filtering and reporting on these records throughout the platform. You may find it easier to identify the “No Brand” group of products when working with CDTs in the AI Foundation Cloud Services or when creating dashboards in RI, compared to leaving the values empty in the file.
-
Any hierarchy-level ID (department ID, region ID, and so on) or dimensional ID value (brand name, supplier ID, channel ID, store format ID, and so on) intended for Planning applications must not have spaces or special characters on any field, or it will be rejected by the PDS load. ID columns to be used in planning should use a combination of numbers, letters, and underscores only.
-
Any change to hierarchy levels after the first dimension is loaded will be treated as a reclassification and will have certain internal processes and data changes triggered as a result. If possible, avoid loading hierarchy changes to levels above Item/Location during the historical load process. If you need to load new hierarchies during the history loads, make sure to advance the business date in RI using the specified jobs and date parameters, do NOT load altered hierarchies on top of the same business date as previous loads.
-
All fields designated as flags (having FLG or FLAG in the field name) must have a
Y
orN
value. Filters and analytics within the system will generally assumeY
/N
is used and not function properly if other values (like0
/1
) are provided. -
Retail Insights requires that all hierarchy identifiers above item/location level MUST be numerical. The reporting layer is designed around having numerical identifiers in hierarchies and no data will show in reports if that is not followed. If you are not implementing Retail Insights, then alphanumeric hierarchy IDs could be used, though it is not preferred.
Product File
The product file is named PRODUCT.csv
, and it contains most of the identifying information about the merchandise
you sell and the services you provide. The file structure follows certain rules based on the Retail Merchandising Foundation
Cloud Services (RMFCS) data model, as that is the paradigm for retail foundation data that we are following across all RAP
foundation files.
The columns below are the minimum required data elements, but the file supports many more optional fields, as listed in the Interfaces Guide. Optional fields tend to be used as reporting attributes in RI and are nullable descriptive fields. Optional fields designated for use in a AI Foundation or Planning module are generally nullable too, but should generally be populated with non-null values to provide more complete data to those modules.
Table 8-3 Product File Required Fields
Column Header | Usage |
---|---|
ITEM |
Product number which uniquely identifies the record. Could be any of these records:
Style/colors are NOT considered as items and do not need to be provided as separate records. |
ITEM_PARENT |
Parent item associated with this record when the item level is 2 or 3. If you are not providing
level 2 or 3 item records, then you may set this value to REQUIRED when providing multiple levels of items to establish the parent/child relationships. |
ITEM_GRANDPARENT |
Grandparent item associated with this record when the item level is 3. If you are not providing
level 3 item records, then you may set this value to REQUIRED when providing multiple levels of items to establish the parent/child relationships. |
ITEM_LEVEL |
Item Level ( Typical fashion item levels are:
|
TRAN_LEVEL |
Transaction level ( |
PACK_FLG |
Pack flag (where REQUIRED column if the retailer has packs, optional otherwise. If pack items are going to be included,
then also note that additional interfaces |
DIFF_AGGREGATE |
Combined differentiator values are used in defining the diff aggregate level (in between Item level |
LVL4_PRODCAT_ID |
Default level for Subclass, which is the first level above item in the hierarchy structure. Sometimes referred to as segment or subsegment. All items of any type are mapped to a subclass as their first level. Parent items are not to be treated as hierarchy levels in the file. |
LVL4_PRODCAT_UID |
Unique identifier of the Subclass. In many merchandising systems the subclass is not unique on its own, so a separate, unique key value must be provided in this case. |
LVL5_PRODCAT_ID |
Default level for Class (sometimes referred to as Subcategory). |
LVL5_PRODCAT_UID |
Unique identifier of the Class. In many merchandising systems the class is not unique on its own, so a separate unique key value must be provided in this case. |
LVL6_PRODCAT_ID |
Default Level for Department (also referred to as Category). |
LVL7_PRODCAT_ID |
Default Level for Group. |
LVL8_PRODCAT_ID |
Default Level for Division. |
TOP_PRODCAT_ID |
Default Level for Company. Only one company is supported at this time, you may not have 2+ companies
in the same dataset. Typically, this is hard-coded to a value of |
ITEM_DESC |
Product Name or primary item description. When you are providing multiple levels of items, this may contain the style name, SKU name, or sub-transaction item name (for example, UPC description). |
LVL4_PRODCAT_DESC |
Default Level for Subclass Description. |
LVL5_PRODCAT_DESC |
Default Level for Class Description. |
LVL6_PRODCAT_DESC |
Default Level for Department Description. |
LVL7_PRODCAT_DESC |
Default Level for Group Description. |
LVL8_PRODCAT_DESC |
Default Level for Division Description. |
TOP_PRODCAT_DESC |
Default Level for Company Description. |
INVENTORIED_FLG |
Indicates whether the item carries stock on hand. Data sent to Planning
apps is generally only for inventoried items ( |
SELLABLE_FLG |
Indicates whether the item is sellable to customers. Data sent to Planning
apps is only for sellable items ( |
The product hierarchy fields use generic level names to support non-traditional hierarchy structures (for example, your
first hierarchy level may not be called Subclass, but you are still loading it into the same position in the file). Other
file columns such as LVL1
to LVL3
exist in the interface but are not yet used in any module
of the platform.
Note:
Multi-level items are not always required and depend on your use-cases. For example, the lowest level (ITEM_LEVEL=3
) for sub-transaction items is only used in Retail Insights for reporting on UPC or barcode level attribute values. Most
implementations will only have ITEM_LEVEL=1
and ITEM_LEVEL=2
records. If you are a non-fashion
retailer you may only have a single item level (for SKUs) and the other levels could be ignored. The reason for having different
records for each item level is to allow for different attributes at each level, which can be very important in Retail Insights
analytics. You may also need to provide multiple item levels for optimizing or planning data at a Style or Style/Color level
in the non-RI modules. When providing multiple item level records, note that the item IDs must be unique across all
levels and records.
Example data for the PRODUCT.csv
file columns above, including all 3 supported item levels (style, SKU,
and UPC):
ITEM,ITEM_PARENT,ITEM_GRANDPARENT,ITEM_LEVEL,TRAN_LEVEL,PACK_FLG,DIFF_AGGREGATE,LVL4_PRODCAT_ID,LVL4_PRODCAT_UID,LVL5_PRODCAT_ID,LVL5_PRODCAT_UID,LVL6_PRODCAT_ID,LVL7_PRODCAT_ID,LVL8_PRODCAT_ID,TOP_LVL_PRODCAT_ID,ITEM_DESC,LVL4_PRODCAT_DESC,LVL5_PRODCAT_DESC,LVL6_PRODCAT_DESC,LVL7_PRODCAT_DESC,LVL8_PRODCAT_DESC,TOP_LVL_PRODCAT_DESC,INVENTORIED_FLG,SELLABLE_FLG
190085210200,-1,-1,1,2,N,,8,9001,3,910,3,2,1,1,2IN1 SHORTS,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd,Y,Y
190085205725,190085210200,-1,2,2,N,BLK,8,9001,3,910,3,2,1,1,2IN1 SHORTS:BLACK:LARGE,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd,Y,Y
190085205923,190085210200,-1,2,2,N,DG,8,9001,3,910,3,2,1,1,2IN1 SHORTS:DARK GREY:LARGE,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd,Y,Y
1190085205725,190085205725,190085210200,3,2,N,,8,9001,3,910,3,2,1,1,2IN1 SHORTS:BLACK:LARGE:BC,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd,Y,Y
1190085205923,190085205923,190085210200,3,2,N,,8,9001,3,910,3,2,1,1,2IN1 SHORTS:DARK GREY:LARGE:BC,Shorts,Active Apparel,Women's Activewear,Activewear,Apparel,Retailer Ltd,Y,Y
This example and the field descriptions covered in this section all follow the standard Merchandising Foundation (RMFCS) structure for product data, and it is strongly recommended that you use this format for RAP. If you are a legacy Planning customer or have specific needs for extended hierarchies, the preferred approach is to convert your non-RMS hierarchy structure to a standard RMS-like foundation format. This conversion involves:
-
Provide only the SKUs and Styles as separate item records (dropping the style/color level from the hierarchy). The Style will be the
ITEM_PARENT
value on the SKU records andITEM_GRANDPARENT
will always be-1
. -
Populate the field
DIFF_AGGREGATE
at the SKU level with the differentiator previously used in the style/color level. For example, a legacy style/color item ID ofS1000358:BLUE
will instead createS1000358
as theITEM
for the style-level record and theITEM_PARENT
in the SKU record. The valueBLUE
is written in theDIFF_AGGREGATE
field in the SKU-level record (DIFF_AGGREGATE
can be set to-1
or left null on style level records). -
When constructing the extended hierarchies in Planning and AI Foundation, the styles and diff aggregate values are concatenated together to dynamically create the style/color level of the hierarchy where needed.
Following this approach for your product hierarchy ensures you are aligned with the majority of Oracle Retail applications and will be able to take up additional retail applications in the future without restructuring your product data again.
For other fields not shown here, they are optional from a data load perspective but may be used by one or more applications on the platform, so it is best to consider all fields on the interface and populate as much data as you can. For example, supplier information is a requirement for Inventory Planning Optimization, and brand information is often used in Clustering or Demand Transference. Also note that some fields come in pairs and must be provided together or not at all. This includes:
-
Brand name and description
-
Supplier ID and description
Description fields can be set to the same value as the identifier if no other value is known or used, but you must include both fields with non-null values when you want to provide the data.
Product Alternates
You may also use the file PRODUCT_ALT.csv
to load additional attributes and hierarchy levels specifically
for use in Planning applications. The file data is always at item level and may have up to 30 flexible fields for data. These
columns exist in the PRODUCT.csv
file if you are a non-RMFCS customer so this separate file would be redundant.
If you are using RMFCS, then this file provides a way to send extra data to Planning that does not exist in RMFCS.
When using flex fields as alternate hierarchy levels, there are some rules you will need to follow:
-
All hierarchies added this way must have an ID and Description pair as two separate columns
-
The ID column for an alternate hierarchy must ONLY contain numbers; no other characters are permitted
Numerical ID fields are required for integration purposes. When a plan is generated in MFP or AP using an alternate hierarchy, and you wish to send that plan data to AIF for in-season forecasting, the alternate hierarchy ID used must be a number for the integration to work. If your alternate hierarchy level will not be used as the base intersection of a plan, then it does not need to be limited to numerical IDs (although it is still recommended to do so). This requirement is the same for all hierarchy levels when Retail Insights is used, as RI can only accept numerical hierarchy IDs for all levels (for both base levels and alternates).
For example, you might populate FLEX1_CHAR_VALUE
with numerical IDs for an alternate level named “Subsegment”.
You will put the descriptions into FLEX2_CHAR_VALUE
. These values can be mapped into PDS by altering the interface.cfg
file, and the values may be used to define plans or targets in MFP. When you export your plans for
AIF, they are written into integration tables such as MFP_PLAN1_EXP
using the numerical identifiers from FLEX1_CHAR_VALUE
as the plan level. This is further integrated to RI tables like W_RTL_PLAN1_PROD1_LC1_T1_FS
(columns ORG_DH_NUM
and PROD_DH_NUM
for location/product IDs respectively). This is where
numerical IDs become required for these interfaces to function; they will not load the data if the IDs are non-numerical.
Once loaded into W_RTL_PLAN1_PROD1_LC1_T1_F
and similar tables, AIF reads the plan data to feed in-season
forecast generation.
Loading the data to RI tables at a flex field level requires additional configuration. Refer to the RI Implementation Guide for details. AIF also requires additional setup to use alternate hierarchies. Refer to the section “Building Alternate Hierarchy in AIF” in the AIF Implementation Guide for details.
Re-Using Product Identifiers
It may happen over time that the same product keys (such as SKU numbers) will be re-used to represent brand new items.
There are two parameters in C_ODI_PARAM_VW
that must be updated to enable this functionality (RI_ITEM_REUSE_IND
and RI_ITEM_REUSE_AFTER_DAYS
). Set RI_ITEM_REUSE_IND
to Y
and set RI_ITEM_REUSE_AFTER_DAYS
to some number greater than 0. The combination of these parameters will alter the product
hierarchy load in the following ways:
-
Once enabled, if an item is deleted (when using incremental loads) or stops appearing in the nightly product files (when using full snapshot loads) it will NOT be closed, it will remain as
current_flg=Y
for the number of days specified in the configuration. -
If the item re-appears in the product file the next night, then the existing item record remains open with
current_flg=Y
and it will be as if it was never dropped or deleted. -
If the item re-appears in the product file only after the set number of days has elapsed, then the old version of the product is both closed and archived (example below). The incoming item record is inserted as a new item with no history.
Here is an example of re-using an item:
-
Item number 1001 is created as a new item in a source system, such as RMFCS.
-
Item exists for a period of time while accumulating fact data such as sales and inventory.
-
Item becomes inactive and is eventually deleted from the source system, which marks it inactive in RAP after the set number of days for re-use has passed.
-
After another period of time (such as 1 month) the same item number 1001 is added back in the source system, representing a completely new item with different attributes and hierarchies. The window between the old and new item is configured in
C_ODI_PARAM_VW
as mentioned in the Setup and Configuration chapter. -
When item 1001 comes to RAP again, the old version of this item will be archived using a value appended to the code (for example, 1001_230101) across all product tables in RI (the item data would already have been dropped from Planning when the re-use number of days had elapsed). Item 1001 is then inserted as a new item not associated in any way with the prior instance of that ID. The history that already exists for item 1001 is now associated with the archived item 1001_230101, and new fact data will be associated only with the new definition of item 1001 going forward.
The same process flow applies both when you are creating the PRODUCT.csv
file from a non-Oracle source
and when you use RMFCS to provide the data.
Organization File
The organization file will contain most of the identifying information about the locations where you sell or store merchandise, including physical locations (such as a brick & mortar store) and virtual locations (such as a web store or virtual warehouse entity). The file structure follows certain rules based on the Retail Merchandising Foundation Cloud Services (RMFCS) data model, as that is the paradigm for retail foundation data that we are following across all RAP foundation files. The columns below are the minimum required data elements, but the file supports many more optional fields, as listed in the Interfaces Guide.
Table 8-4 Organization File Required Fields
Column Header | Usage |
---|---|
ORG_NUM |
The external identifier for a location, including stores, warehouses, and partner locations. This value MUST be a number if you will use Retail Insights. RI cannot use non-numeric organization IDs. |
ORG_TYPE_CODE |
The type code of the location. It must be one of |
CURR_CODE |
This is the 3-character base currency code of the organizational entity, such as |
ORG_HIER10_NUM |
Default Level for District, which is the first level above Location in the hierarchy. Hierarchy values MUST be a number if you will use Retail Insights. RI cannot use non-numeric hierarchy IDs. |
ORG_HIER11_NUM |
Default Level for Region. |
ORG_HIER12_NUM |
Default Level for Area. Also referred to as the Channel level in some Planning applications. |
ORG_HIER13_NUM |
Default Level for Chain. |
ORG_TOP_NUM |
Default Level for Company. Only one company is supported at this time. You may not have 2+ companies in the same instance. |
ORG_DESC |
Short name or short description of the location. |
ORG_SECONDARY_DESC |
Full name or long description of the location. |
ORG_HIER10_DESC |
Default Level for District Description. |
ORG_HIER11_DESC |
Default Level for Region Description. |
ORG_HIER12_DESC |
Default Level for Area Description. Also referred to as the Channel level in some Planning applications. |
ORG_HIER13_DESC |
Default Level for Chain Description. |
ORG_TOP_DESC |
Default Level for Company Description. |
PHYS_WH_ID |
The physical warehouse ID linked to a virtual warehouse. Must be specified for warehouses, can be null otherwise. A physical warehouse record can have its own ID as the value here. A virtual warehouse should have the linked physical warehouse ID that contains the virtual location. |
VIRTUAL_WH_FLG |
Indicates whether the warehouse record is a physical or virtual WH. Planning
GA solutions only use virtual WHs so flag must be |
The organization hierarchy fields use generic level names to support non-traditional hierarchy levels (for example, your
first hierarchy level may not be called District, but you are still loading it into the same position in the file which is
used for Districts). Other levels, such as 1
to 9
, have columns in the interface but are
not yet used in any module of the platform.
Warehouses get special handling both in the input interface load and throughout the RAP applications. Warehouses are not
considered a part of the organization hierarchy structure. While you are required to put some value in the hierarchy level
fields for warehouses (because the columns are not nullable) those values are not currently used. Instead, the values will
be discarded and the warehouses are loaded with no parent levels in the data warehouse tables. You should provide a unique
reserved value like 1
or 9999
on all hierarchy level numbers between location and company
for warehouses, just to ensure the data is loaded without violating any multi-parentage rules. When exporting the warehouse
locations to Planning applications, each warehouse ID is assigned its own name and number for each parent level, prefixed
with WH
to make the level IDs distinct from any store hierarchy level. The warehouses must then be mapped
to channels from the MFP user interface before you can use their data.
Example data for the ORGANIZATION.csv
file columns above as well as some optional fields available on
the interface:
ORG_NUM,ORG_TYPE_CODE,CURR_CODE,STATE_PROV_NAME,COUNTRY_REGION_NAME,ORG_HIER10_NUM,ORG_HIER11_NUM,ORG_HIER12_NUM,ORG_HIER13_NUM,ORG_TOP_NUM,ORG_DESC,ORG_SECONDARY_DESC,ORG_HIER10_DESC,ORG_HIER11_DESC,ORG_HIER12_DESC,ORG_HIER13_DESC,ORG_TOP_DESC,CHANNEL_ID,CHANNEL_NAME,PHYS_WH_ID,STOCKHOLDING_FLG,STORE_FORMAT_DESC,STORE_FORMAT_ID,STORE_TYPE,TRANSFER_ZONE_ID,TRANSFER_ZONE_DESC,VIRTUAL_WH_FLG,STORE_CLASS_TYPE,STORE_CLASS_DESC,WH_DELIVERY_POLICY,WH_REPL_IND,DUNS_NUMBER,STORE_REMODEL_DT,STORE_CLOSE_DT,INBOUND_HANDLING_DAYS,FLEX1_CHAR_VALUE,FLEX2_CHAR_VALUE,FLEX3_CHAR_VALUE,FLEX4_CHAR_VALUE,FLEX5_CHAR_VALUE,FLEX6_CHAR_VALUE,FLEX7_CHAR_VALUE,FLEX8_CHAR_VALUE,FLEX9_CHAR_VALUE,FLEX10_CHAR_VALUE
1000,S,USD,North Carolina,United States,1070,170,1,1,1,Charlotte,Charlotte,North Carolina,Mid-Atlantic,Brick & Mortar,US,Retailer Ltd,1,North America,,Y,Store,1,C,101,Zone 101,N,1,A,,,,,,,WH-1,Warehouse - US,1,Store Pick Up / Take With,3,Comp,6,Mixed Humid,1,Very Large
1001,S,USD,Georgia,United States,1023,400,1,1,1,Atlanta,Atlanta,Georgia,South Atlantic,Brick & Mortar,US,Retailer Ltd,1,North America,,Y,Kiosk,2,C,101,Zone 101,N,6,F,,,,,,,WH-1,Warehouse - US,2,Deliver/Install at Customer ,3,Comp,7,Hot Humid,3,Medium
1002,S,USD,Texas,United States,1104,230,1,1,1,Dallas,Dallas,Texas,Gulf States,Brick & Mortar,US,Retailer Ltd,1,North America,,Y,Store,1,C,101,Zone 101,N,6,F,,,,,,,WH-1,Warehouse - US,3,Home Delivery,3,Comp,4,Hot Dry,3,Medium
It is important that your organization hierarchy follow the standard rules laid out at the beginning of this chapter. All IDs must be unique (within their level) and IDs can never be re-used under multiple parents. All IDs must be numbers if you are using Retail Insights. The entire 6-level structure must be filled out, even if your source system doesn’t have that many levels.
Note:
You may duplicate a higher level down to lower levels if you need to fill it out to meet the data requirements.Also note that some optional fields come in pairs and must be provided together or not at all. This includes:
-
Banner ID and description
-
Channel ID and description
-
Store format ID and description
Description fields can be set to the same value as the identifier if no other value is known or used, but you must include both fields with non-null values when you provide the data.
Organization Alternates
You may also use the file ORGANIZATION_ALT.csv
to load additional attributes and hierarchy levels specifically
for use in Planning applications. The file data is always at location level and may have up to 30 flexible fields for data.
These columns exist on the ORGANIZATION.csv
file if you are a non-RMFCS customer, so this separate file would
be redundant. If you are using RMFCS, then this file provides a way to send extra data to Planning that does not exist in
RMFCS.
When using flex fields as alternate hierarchy levels, there are some rules you will need to follow:
-
All hierarchies added this way must have an ID and Description pair as two separate columns
-
The ID column for an alternate hierarchy must ONLY contain numbers, no other characters are permitted
Numerical ID fields are required for integration purposes. When a plan is generated in MFP or AP using an alternate hierarchy, and you wish to send that plan data to AIF for in-season forecasting, the alternate hierarchy ID used must be a number for the integration to work. If your alternate hierarchy level will not be used as the base intersection of a plan, then it does not need to be limited to numerical IDs (although it is still recommended to do so). This requirement is the same for all hierarchy levels when Retail Insights is used, as RI can only accept numerical hierarchy IDs for all levels (both base levels and alternates).
For example, you might populate FLEX1_CHAR_VALUE
with numerical IDs for an alternate level named “Subsegment”.
You will put the descriptions into FLEX2_CHAR_VALUE
. These values can be mapped into PDS by altering the interface.cfg
file, and the values can be used to define plans or targets in MFP. When you export your plans for
AIF, they are written into integration tables such as MFP_PLAN1_EXP
using the numerical identifiers from FLEX1_CHAR_VALUE
as the plan level. This is further integrated to RI tables like W_RTL_PLAN1_PROD1_LC1_T1_FS
(columns ORG_DH_NUM
and PROD_DH_NUM
for location/product IDs respectively). This is where
numerical IDs become required for these interfaces to function; they will not load the data if the IDs are non-numerical.
Once loaded into W_RTL_PLAN1_PROD1_LC1_T1_F
and similar tables, AIF reads the plan data to feed in-season
forecast generation.
Loading the data to RI tables at a flex field level requires additional configuration. Refer to the RI Implementation Guide for details. AIF also requires additional setup to use alternate hierarchies. Refer to the section “Building Alternate Hierarchy in AIF” in the AIF Implementation Guide for details.
Calendar File
The calendar file contains your primary business or fiscal calendar, defined at the fiscal-period level of detail. The most common fiscal calendar used is a 4-5-4 National Retail Federation (NRF) calendar or a variation of it with different year-ending dates. This calendar defines the financial, analytical, or planning periods used by the business. It must contain some form of fiscal calendar, but if you are a business that operates solely on the Gregorian calendar, a default calendar file can be generated by an ad hoc batch program to initialize the system. However, if you are implementing a planning solution, you must use the Fiscal Calendar as your primary calendar, and only this calendar will be integrated from RI to Planning.
Table 8-5 Calendar File Required Fields
Column Header | Usage |
---|---|
MCAL_CAL_ID |
Identifies the accounting calendar. At this time a hard-coded value
of |
MCAL_PERIOD_TYPE |
Identifies the accounting period type ( |
MCAL_PERIOD_NAME |
Name of the fiscal period, such as |
MCAL_PERIOD |
Period number within a year, for a 4-5-4 calendar this should be
between |
MCAL_PERIOD_ST_DT |
Identifies the first date of the period in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_PERIOD_END_DT |
Identifies the last date of the period in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_QTR |
Identifies the quarter of the year to which this period belongs.
Possible values are |
MCAL_YEAR |
Identifies the fiscal year in YYYY format. |
MCAL_QTR_START_DT |
Identifies the start date of the quarter in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_QTR_END_DT |
Identifies the end date of the quarter in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_YEAR_START_DT |
Identifies the start date of the year in YYYYMMDD format (default format can be changed in CTX files). |
MCAL_YEAR_END_DT |
Identifies the end date of the year in YYYYMMDD format (default format can be changed in CTX files). |
The hard-coded calendar ID is used to align with several internal tables that are designed to support multiple calendars
but currently have only one in place, and that calendar uses the provided value of MCAL_CAL_ID
above.
The fiscal calendar should have, at a minimum, a 5-year range (2 years in the past, the current fiscal year, and 2 years forward from that) but is usually much longer so that you do not need to update the file often. Most implementations should start with a 10-15 year fiscal calendar length. The calendar should start at least 1 full year before the planned beginning of your history files and extend at least 1 year beyond your expected business needs in all RAP modules.
Example data for the CALENDAR.csv
file columns above:
MCAL_CAL_ID,MCAL_PERIOD_TYPE,MCAL_PERIOD_NAME,MCAL_PERIOD,MCAL_PERIOD_ST_DT,MCAL_PERIOD_END_DT,MCAL_QTR,MCAL_YEAR,MCAL_QTR_START_DT,MCAL_QTR_END_DT,MCAL_YEAR_START_DT,MCAL_YEAR_END_DT
Retail Calendar~41,4,Period01,1,20070204,20070303,1,2007,20070204,20070505,20070204,20080202
Retail Calendar~41,5,Period02,2,20070304,20070407,1,2007,20070204,20070505,20070204,20080202
Retail Calendar~41,4,Period03,3,20070408,20070505,1,2007,20070204,20070505,20070204,20080202
Retail Calendar~41,4,Period04,4,20070506,20070602,2,2007,20070506,20070804,20070204,20080202
Exchange Rates File
The exchange rates file captures conversion rates between any two currency codes that may appear in your fact data. The standard practice for fact data is to load the source system values in the original currency and allow the platform to convert the amounts to the primary currency. This file facilitates that process and triggers bulk updates in nightly processing any time you wish to change your exchange rates for financial reporting purposes. Adding new rates to the file with an effective date equal to the batch date triggers a mass update to all positional facts, converting all the amounts to the new exchange rate even if the item/location did not otherwise change in the source system.
Note that you do not have to provide exchange rates data for the following scenarios:
-
You are loading your data already converted to primary currency
-
You only use a single currency for your entire business
-
You are only implementing a AI Foundation module and expect to perform those processes in the local currency amounts
Even when exchange rates are not required as a separate file, you must still populate the currency codes (DOC_CURR_CODE
, LOC_CURR_CODE
) in the fact data files with values. Review the scenarios below to understand how to set
these values and provide the associated rates.
Scenario 1 - No Conversion
For this use-case, all data is in the desired currency before sending
it to Oracle. You do not want the platform to convert your data from source currency to primary currency. All fact records
must have LOC_CURR_CODE
= DOC_CURR_CODE
. For example, set both values to USD
for sales in the U.S. and both values to CAD
for sales in Canada that you pre-converted. EXCH_RATE.csv
data is not required or used for records having the same currency code on both columns.
Scenario 2 – Only One Currency
If your business only operates in one region and uses a single currency
code, then you don’t need to provide exchange rate data. All fact records must have LOC_CURR_CODE
= DOC_CURR_CODE
. For example, set both values to USD
on all rows if your primary operating currency
is USD. EXCH_RATE.csv
data is not required or used for records having the same currency code on both columns.
Scenario 3 – Multiple Currencies
When you do plan to provide data in multiple source currencies,
you must also provide the exchange rates into and out of those currencies. Your fact data must have DOC_CURR_CODE
set to the currency of the source system where the transaction was recorded (for example, a sale in Canada has a document
currency of CAD
). The value of LOC_CURR_CODE
will be the same on all records and must be
the primary operating currency of your business (if you operate mainly in the United States then it will be USD
).
Exchange rates should be provided using the standard international rates (for example USD > CAD may be 1.38) but the fact load will perform lookups in reverse. Fact conversions are applied as a division process. For example, “transaction amount / exchange rate” is the formula to convert from document currency to primary currency; so when converting from CAD > USD the system will look up the value for USD > CAD and divide by that number to get the final value.
Table 8-6 Exchange Rate File Required Fields
Column Header | Usage |
---|---|
START_DT |
Contains the effective start date of the exchange rate. Set to the current business date to trigger new rate conversions. |
END_DT |
Contains the effective end date of the exchange rate. Default to |
EXCHANGE_RATE |
Contains the exchange rate for the specified currency/type/effective date combination. |
FROM_CURRENCY_CODE |
Code of the currency to be exchanged. |
TO_CURRENCY_CODE |
Code of the currency to which a currency is exchanged. |
Sample data for the EXCH_RATE.csv file columns above:
START_DT,END_DT,EXCHANGE_RATE,FROM_CURRENCY_CODE,TO_CURRENCY_CODE
20180514,21000101,0.8640055,CAD,USD
20180514,21000101,0.1233959,CNY,USD
The exchange rates data must also satisfy the following criteria if you are loading data for use in Retail Insights reporting:
-
Rates must be provided in both directions for every combination of currencies that can occur in your dataset (for example, USD > CAD and CAD > USD).
-
Dates must provide complete coverage of your entire timeframe in the dataset, both for historical and current data. The current effective records for all rates can use 2100-01-01 as the end date. Dates cannot overlap, only a single rate must be effective per day.
-
Rates should not change more often than absolutely necessary based on the business requirements. If you are implementing RI with positional data, a rate change triggers a complete recalculation of the stock on hand cost/retail amounts for the entire business across all pre-calculated aggregate tables. When RI is not used for financial reporting you might only change the rates once each fiscal year, to maintain a single constant currency for analytical purposes.
Attributes Files
Product attributes are provided on two files: one file for the attribute-to-product mappings and another for attribute descriptions and codes. These files should be provided together to fully describe all the attributes being loaded into the system. The attribute descriptors file must be a full snapshot of all attribute types and values at all times. The product attribute mapping file should start as a full snapshot but can move to incremental (delta) load methods once nightly batches begin, if you can extract the information as deltas only.
Product attributes are a major component of the RI and AI Foundation modules and drive many analytical processes but are not required for some planning modules like MFP.
Table 8-7 Attribute File Required Fields
Column Header | Usage |
---|---|
ATTR_VALUE_ID |
Unique identifier for a user-defined attribute or product differentiator.
This interface contains ALL values regardless of whether they are used on items yet or not. These values must also match the |
ATTR_VALUE_DESC |
Descriptive value for a user-defined attribute or product differentiator,
such as |
ATTR_GROUP_ID |
Unique identifier for a group of user-defined attributes, or the
name/code for the differentiator group. Reserved attribute types like |
ATTR_GROUP_DESC |
Descriptive value for a group of user-defined attributes, such as |
ATTR_TYPE_CODE |
Indicates the type of UDA or differentiator. UDA types should be
hard-coded as one of
For non-UDA types, some diffs have special reserved codes for this field as
well, which should be used when applicable, and include |
Sample data for the ATTR.csv
file columns above:
ATTR_VALUE_ID,ATTR_VALUE_DESC,ATTR_GROUP_ID,ATTR_GROUP_DESC,ATTR_TYPE_CODE
13,No_Sugar_IN13,45008,UDA_ING_2018.01.16.01.00,FF
14,Zero_Carbs_IN14,45008,UDA_ING_2018.01.16.01.00,FF
3,Distressed,80008,Wash,LV
STEEL,Steel,METAL,Metals,DIFF
CHOC,Chocolate,FLAVOR,Flavor,FLAVOR
GRAY_43214,Gray,COLOR,Color,COLOR
32X32_9957,32X32,SIZE,Size,SIZE
Table 8-8 Product Attribute File Required Fields
Column Header | Usage |
---|---|
ITEM |
The item number associated with the specified attribute value. |
ATTR_ID |
Identifier for an attribute value, such as the UDA value ID or Diff
ID which is mapped to the item on the record, or the ID for the item list this item belongs to. These values must also match
the |
ATTR_GRP_TYPE |
The attribute group type. This is a set of fixed values which must
be selected from what RAP supports. Supported values are |
ATTR_GRP_ID |
Identifier for the attribute group containing the value on this
record. Must match a
|
DIFF_GRP_ID |
Differentiator group used to assign the diff attribute on this item; for example the Size Group ID used when generating SKUs from a parent Style. Only SKUs will have diff groups associated with them. Only diffs will have groups, not UDAs or other record types. This is not the same as an attribute group, which is the overall grouping of attribute values across all items. This is used mainly for Size Profile Science. Foreign key reference
to |
DIFF_GRP_DESC |
Descriptive value of the diff group mapped to this item/attribute record. |
Sample data for the PROD_ATTR.csv
file columns above:
ITEM,ATTR_ID,ATTR_GRP_TYPE,ATTR_GRP_ID,DIFF_GRP_ID,DIFF_GRP_DESC
91203747,13,ITEMUDA,45008,,
91203747,3,ITEMUDA,80008,,
190496585706,STEEL,ITEMDIFF,METAL,,
86323133004,GRAY_43214,COLOR,COLOR,,
190085302141,CHOC,PRODUCT_ATTRIBUTES,FLAVOR,,
345873291,32X32_9957,PRODUCT_ATTRIBUTES,SIZE,S13,Pant Sizes
Fact Files
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 8-9 Common Fact Data Fields
Column Header | Usage |
---|---|
ITEM |
Unique identifier of a transaction item. Must align with the product
records in the |
ORG_NUM |
Unique identifier of an organizational entity. Must align with the
location records in the |
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 |
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 |
*_QTY |
Fields ending with |
*_AMT_LCL |
Fields containing |
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 |
LOC_CURR_CODE |
The local operating currency of your main office or headquarters.
A company based in the United States would use |
ETL_THREAD_VAL |
If you are providing any data files ending in a |
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.
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 AI Foundation 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
Fact Data Incremental Logic
Daily or weekly fact data files can be provided incrementally instead of as full snapshots, but the specific handling of incremental changes can be different for the various fact types. The table below summarizes the incremental update logic used on the core fact areas.
Facts | Incremental Logic |
---|---|
Transactional (Sales, Receipts, Markdowns, Adjustments, RTVs, and so on) |
Loading transaction data into RAP uses additive merge logic when new data comes into the tables. If the target intersection doesn’t exist, it will insert it. If the target intersection DOES exist, then it will merge the records by adding together the source and target fields. For example, an existing sales transaction that is revised will add together the Quantity and Amount fields from the source and target. Note: When posting a partial revision, send zeros in fields that should not be adjusted. |
Positional (Inventory, Purchase Order, Price, Cost, and so on) |
Positional data loaded into RAP must always be for the current date — it cannot be back-posted — and will merge into the target tables with the incoming values (the new day’s position is a combination of existing data from yesterday merged with the incoming data). You must send a zero if a given position was moved to zero or dropped from the source system; otherwise it would continue to carry forward the last non-zero position in the database. Refer to the detailed sections later in this chapter for Inventory Position and Pricing examples. |
Non-Transactional and Non-Positional Facts (Store Traffic, Flex Facts, History Planning Facts) |
Some interfaces that are not related to any transactional or positional data elements, like the Store Traffic or Planning interfaces, use non-additive merge logic. When an existing intersection comes into the staging table, it is merged to the target table but overwrites/replaces the target values with the source values. |
Multi-Threading and Parallelism
Due to the high data volumes of most fact data (such as sales and inventory), it is necessary to process the data using
multiple CPU threads on the database. In RAP’s second-generation architecture, multi-threading is handled automatically. You
must not attempt to alter any threading parameters to force a specific thread count greater than 1. If you are providing any
data files ending in a .dat
extension, then it might contain an ETL_THREAD_VAL
column. This
column must be hard-coded to be 1
on all rows without exception; it should not be null and should not be
any value greater than 1. Similarly, there are database parameters named LOC_NUM_OF_THREAD
in the C_ODI_PARAM_VW
table. These must be set to a value of 1
and should not be altered to any value
greater than 1.
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 |
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. |
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 RI. |
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 |
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 |
DAY_DT |
The date that the transaction occurred in the source system. Must be a valid date within the periods in the |
MIN_NUM |
Hour and minute of the transaction in 24-hour, 4-digit format; for example, 11:23 PM would be |
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 ( |
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 |
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. |
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 |
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 |
Sales Pack Data
If you have pack items (sets of SKUs or multiple units of SKUs sold as one item) then you will need to spread sales of
such items from the pack item level to the individual component SKU level. The interface file SALES_PACK.csv
is provided to load component sales values at a summary level of item/location/date. This data is included in sales calculations
for both AI Foundation and Planning applications by adding together the sales of SKUs sold individually on SALES.csv
and the sales of items inside packs on SALES_PACK.csv
.
As an example, assume you have a SKU# 1090 which is a white T-shirt. This item is sold individually to customers, but it is also included in a pack of three shirts. The 3-pack is sold using a separate SKU# 3451. You must provide the data for this scenario as follows:
-
When SKU 1090 sells to a customer, you will have a transaction for 1 unit on
SALES.csv
-
When SKU 3451 sells to a customer, you will have a transaction for 1 unit on
SALES.csv
, plus a record for SKU 1090 for 3 units onSALES_PACK.csv
(representing the 3 units inside the pack that sold).
When this data is loaded into other applications like MFP, you will see a total of 4 units of sales for SKU 1090, because we will sum together the sales from both interfaces. The pack-level sale of SKU 3451 is not exported to Planning applications because that would result in double-counting at an aggregate level, but it can be used for other purposes such as Retail Insights reports.
When you are providing SALES_PACK.csv
you must also provide the pack item/component item relationships
using a dimension file PROD_PACK.csv
. Refer to the RAP Interfaces Guide for the full interface specifications
of both of these files.
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:
|
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
( |
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 |
ORG_NUM |
Must be the location number that is holding the inventory. Must have a record in the |
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 |
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 |
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 |
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 |
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 RI 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 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:
An item/location may have many changes with types |
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 alltype=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 |
ORG_NUM |
Must be the location number where the item on the record is ranged to. Must have a record in |
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 |
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. |
Receipts Data Requirements
Receipts data (RECEIPT.csv
) is used specifically for receipt transactions where inventory units are received
into an owned location (like a store or warehouse), and that receipt impacts the stock on hand for the location. The file
is used for several purposes throughout RAP: it is needed by MFP for inventory plans, by IPO and LPO for determining first
and last receiving dates by item/location, and by RI for reporting on receipt activity. The receipts data must be loaded in
parallel with inventory position if AIF modules are being implemented, because the calculations for IPO/LPO are done up front
during each load of inventory position and receipt files.
Rule | Explanation |
---|---|
Receipt Types |
The receipts are provided using a type code, with 3 specific codes supported:
MFP GA solution only uses type 20 transactions but the rest of the RAP solutions use all types. |
Receipts vs. Transfers |
Transfer receipts are not the same thing as transfers ( |
Unplanned Receipts |
It is possible for a location to receive inventory it did not ask for (for example, there is no associated PO or allocation linked to those units). Such receipts should still appear as a type 44~T receipt transaction, so long as those units of inventory do get pulled into the location’s stock on hand. |
In most cases, you will be providing the same set of receipt 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 |
ORG_NUM |
Must be the location number where the item is received. Must have a record in |
DAY_DT |
The date that the receipt occurred on. Receipts can occur on any date both in history and nightly batch files. |
INVRC_TYPE_CODE |
Indicates the type of receipt into a location using merchandising transaction codes |
INVRC_QTY INVRC_COST_AMT_LCL INVRC_RTL_AMT_LCL |
The units being received and the total cost/retail value of those units relative to the receiving location. |
LOC_CURR_CODE DOC_CURR_CODE |
The currency codes linked to the cost and retail amounts on the receipt data. The values
in these fields will control how the system converts the amount (such as |
Transfer Data Requirements
Transfer data (TRANSFER.csv
) is used to capture movement of inventory between two locations (warehouses
or stores). Transfer transactions are sent at the time the transfer is initiated at the shipping location. Transfer transactions
are used primarily in Planning applications and RI reporting.
Rule | Explanation |
---|---|
Transfer Types |
Transfers are provided using a type code, with 3 specific codes supported:
Most transfers are categorized as Normal ( |
Transfer In vs. Transfer Out |
The transfers file has two sets of measures for the unit/cost/retail value into the location and out of the location. Typically these values contain the same data, but since they are aggregated and displayed separately in the target systems, they are also separate on the input so you have full control over what goes into each measure. For example, a transfer in of 5 units to location 102 would also have a transfer out of 5 units leaving location 56 (on the same record). |
In most cases, you will be providing the same set of transfer 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 |
ORG_NUM |
Must be the location number where the item inventory is being shipped to (i.e. the receiving location). Must
have a record in |
DAY_DT |
The date that the transfer was initiated on (NOT the date when it completes or is received). Transfers can occur on any date both in history and nightly batch files. |
FROM_ORG_NUM |
Must be the location number where the item inventory is being moved out of (that is, the shipping
location). Must have a record in |
TSF_TYPE_CODE |
The numerical code representing the transfer type, as described in the business rules for transfers earlier in this section. This is a part of the primary key, meaning you may have multiple records for the same item/location/from-location/date with different transfer type codes. |
TSF_TO_LOC_QTY TSF_TO_LOC_COST_AMT_LCL TSF_TO_LOC_RTL_AMT_LCL |
The units and total cost/retail values for the transfer
relative to the “to” location ( |
TSF_FROM_LOC_QTY TSF_FROM_LOC_COST_AMT_LCL TSF_FROM_LOC_RTL_AMT_LCL |
The units and total cost/retail values for the transfer
relative to the “from” location ( |
LOC_CURR_CODE FROM_DOC_CURR_CODE FROM_LOC_CURR_CODE DOC_CURR_CODE |
The currency codes linked to the cost
and retail amounts on the transfer data. The values in these fields will control how the system converts the amount (such
as |
Adjustment Data Requirements
Adjustments data (ADJUSTMENT.csv
) is used to capture manual changes to stock on hand made for any reason
that does not fall into one of the other categories like sales or receipts. Adjustments are used only in Planning and RI applications.
Rule | Explanation |
---|---|
Adjustment Types |
The adjustments are provided using a type code, with 3 specific codes supported:
Only types |
Reason Codes |
Reason codes are used to identify the specific type of adjustment that occurred for that item, location, and date. If you are loading data for Planning apps, then they are not required because Planning apps do not look at reason codes. They are only used for RI reporting. There are no required codes; it will depend on the data in your source system. The codes should be numerical, and there is a Description field that must also be provided for the display name. |
Positive and Negative Values |
Adjustments should be positive by default. A positive adjustment on the input file means a decrease in the stock on hand at the location. A negative adjustment means an increase to stock on hand (basically you have adjusted the units back into the location’s inventory, which is less common). When the data is sent to MFP, the default planning import will invert the signs for the positive adjustments to become subtractions to inventory. |
In most cases, you will be providing the same set of adjustment 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 |
ORG_NUM |
Must be the location number where the item inventory was adjusted. Must have a record in |
DAY_DT |
The date that the adjustment occurred on. Adjustments can occur on any date both in history and nightly batch files. |
INVADJ_TYPE_CODE |
Indicates the type of adjustment to inventory using transaction codes |
INVADJ_QTY INVADJ_COST_AMT_LCL INVADJ_RTL_AMT_LCL |
The units and total cost/retail values for the adjustment. The cost and retail amounts may have varying calculations in your source system depending on how shrink and non-shrink inventory adjustments are determined. Adjustments can be negative or positive depending on whether inventory is being removed or added to the stock on hand. The sign is reversed by MFP, meaning negative amounts on the input will display as positive values on the MFP measures. |
LOC_CURR_CODE DOC_CURR_CODE |
The currency codes linked to the cost and retail amounts on the adjustment data. The
values in these fields will control how the system converts the amount (such as |
RTV Data Requirements
Return to vendor data (RTV.csv
) is used to capture returns of inventory from your stores or warehouses
back to the original vendor. An RTV is a transaction that decreases your owned inventory at a location because you are shipping
units to a non-owned location. RTVs are used only in Planning and RI applications.
Rule | Explanation |
---|---|
Supplier IDs, Reason Codes, Status Codes |
All of the reason code, supplier number, and status code fields in an RTV record are optional and used only for RI reporting purposes, because planning applications do not report at those levels. If you are not specifying these values, leave the columns out of the file entirely, and a default value of -1 will be assigned to the record in those columns. |
Positive and Negative Values |
RTV transactions should always be positive values. Only send negative values to reverse a previously-sent transaction in order to zero it out from the database. |
In most cases, you will be providing the same set of RTV 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 |
ORG_NUM |
Must be the location number where the RTV is being shipped out from. Must have a record in |
DAY_DT |
The date that the RTV occurred on. RTVs can occur on any date both in history and nightly batch files. |
RTV_QTY RTV_COST_AMT_LCL RTV_RTL_AMT_LCL |
The units and total cost/retail values for the returns to vendor. |
LOC_CURR_CODE DOC_CURR_CODE |
The currency codes linked to the cost and retail amounts on the RTV data. The values
in these fields will control how the system converts the amount (such as |
Markdown Data Requirements
Markdown data (MARKDOWN.csv
) is used to capture changes in retail value of owned inventory due to a permanent
or temporary price change. Markdowns are used only in Planning and RI applications. There are separate measures on the input
file for markdown and markup effects depending on the kind of price change and direction of the change. For regular and clearance
price changes, the file captures the total change in value of owned inventory units on the day the price change goes into
effect. For promotional or temporary price changes, the file should have only the marginal effects of the price change when
any of that inventory is sold to a customer (since the overall value of your inventory is not changed by a temporary promotion).
Rule | Explanation |
---|---|
Markdown Amounts |
Markdown amounts are only the change in total value of inventory, not the total value itself. Permanent and clearance price changes result in markdown amounts derived like this: Markdown Retail = (SOH*Old Retail) – (SOH*New Retail) Markdown Retail = (150*15) – (150*12) = $450 Promotional price changes do not need the total markdown amount calculation, and instead send a promotion markdown amount at the time of any sale: Promotional Markdown Retail = (Units Sold*Old Retail) – (Units Sold*New Retail) Promotional Markdown Retail = (5*17) – (5*15) = $10 Markdown amounts will generally be positive values when the price was decreased, and the target systems will know when to add or subtract the markdown amounts where needed. |
Markdown Types |
The markdowns are provided using a type code, with 3 specific codes supported:
|
Markup Handling |
When a regular price is increased or a clearance price is set back to regular price, you can send a separate transaction with positive Markup values populated in the record. You do not need to send negative values to reverse a markdown; the target systems can use the markup measures to do that. A similar rule applies to the markdown/markup cancel measures. |
Inventory Usage for PDS Measures |
Markdown data is joined with inventory data when you are exporting it to Planning applications, specifically to calculate two markdown measures (reg-promo and clearance-promo markdown amounts). The markdown export uses the clearance flag from the inventory history to determine the measure rollups. If there is no inventory record for a given item/loc/week intersection, the markdown data will default into the reg-promo markdown measure. |
In most cases, you will be providing the same set of markdown 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 |
ORG_NUM |
Must be the location number where the markdown transaction occurred. Must have a record in |
DAY_DT |
The date that the markdown occurred on. Markdowns can occur on any date both in history and nightly batch files. |
RTL_TYPE_CODE |
The type of markdown using one of |
MKDN_QTY MKDN_AMT_LCL |
The units affected by a markdown and the total change in retail value as a result of a markdown. Both values will be positive numbers when representing a decrease in retail value as the result of a markdown. |
MKUP_QTY MKUP_AMT_LCL |
The units affected by a markup and the total change in retail value as a result of a markup. Both values will be positive numbers when representing an increase in retail value as the result of a markup. |
MKDN_CAN_QTY MKDN_CAN_AMT_LCL |
The units affected by a cancelled markdown and the total change in retail value as a result of the cancellation. Both values will be positive numbers when representing an increase in retail value as the result of the cancellation. |
MKUP_CAN_QTY MKUP_CAN_AMT_LCL |
The units affected by a cancelled markup and the total change in retail value as a result of the cancellation. Both values will be positive numbers when representing a decrease in retail value as the result of the cancellation. |
DOC_CURR_CODE LOC_CURR_CODE |
The currency codes linked to the retail amounts on the markdown data. The values in
these fields will control how the system converts the amount (such as |
Purchase Order Data Requirements
Purchase order data (ORDER_HEAD.csv
and ORDER_DETAIL.csv
) is used to capture the raw
PO details at the lowest level. Purchase orders are used in Planning, IPO, and RI applications. For Planning, the PO data
is aggregated from the order level to a forward-looking summary based on the open-to-buy (OTB) week date. The raw details
are used as-is in RI and IPO.
Rule | Explanation |
---|---|
Daily Data Requirements |
It is expected that the PO header and detail files start as full daily snapshots of
all active or recently closed orders. The detail data is maintained positionally so that, if no update is received, we will
continue to carry forward the last known value. Once daily batches have started, you can transition the PO details file only
to an incremental update file (header file must always be a complete snapshot). When sending data incrementally, you must
include all order updates for a given date, both for open and closed orders. If an order changed at the header level (such
as closing or cancelling the order), you should send all the detail lines in that order even if some didn’t change. This includes
when order lines are fully received and move to 0 units remaining, these changes must be sent to RAP. If you are unable to
satisfy these incremental data requirements, you may change the parameter |
Historical Data and Past Dates |
The Order interfaces do not support loading historical data or data with past dates
on the detailed order-line records. Every time you load orders, it is for the current set of data for a single business date.
The |
Order Status |
The header file for POs has a variety of attributes but one of the most important is the status,
which should be either |
OTB EOW Date |
The OTB end-of-week date is used for the Planning aggregations to create a forward-looking view of expected receipts from POs. Open order quantities are aggregated to the OTB week before being exported to Planning. If the OTB week has elapsed, the order quantities are included in next week’s OTB roll-up regardless of how far in the past the date is, because the earliest that PO can come as receipts is in the next business week. |
Include On Order Indicator |
There is a required flag on the order header file to tell the system that an order
should be included in calculations for Planning or not. When the flag is set to |
The ORDER_HEAD.csv
and ORDER_DETAIL.csv
files both have a minimum set of required fields
to make the integrations within RAP function, so those will be listed out below with additional usage notes. The two files
are tightly coupled and it’s expected that you send both at the same time; you will never send only one of them.
Table 8-10 ORDER_HEAD.csv
Column Header | Usage |
---|---|
ORDER_NO |
Unique identifier of a purchase order in the source system. This must be the same number used across the entire life of the order, so that you can post updates and revisions to it over time. If your source system changes the order number for any reason, be aware that you may need to keep track of old order numbers to post the updates to RAP for the right orders. |
STATUS |
Tells the system if the order is active ( |
OTB_EOW_DATE |
The week-ending date where the open order quantities should be considered for Open To Buy (OTB)
planning. This will drive the aggregation of the purchase order data into future weeks before it is loaded into Planning applications.
When the |
INCLUDE_ON_ORDER_IND |
A flag to instruct the system on which orders should be considered for Open to Buy and any other
open on-order calculations. If the flag is |
Table 8-11 ORDER_DETAIL.csv
Column Header | Usage |
---|---|
ITEM |
Must be the transaction level item or SKU number and must have a record in the |
ORG_NUM |
Must be the location number where the order was placed from and will be received at. Must have a record in |
DAY_DT |
The current business date in the system for this data load. You cannot send order data for any other date except the current business date, as this is a positional table like Inventory. Past/future dates will not be accepted. The purpose of this column is mainly for reference and archival purposes (for example, when looking at old data files you will know which business date this set of records was for). |
ORDER_NO |
A cross-reference to the order number on |
PO_ONORD_QTY PO_ONORD_COST_AMT_LCL PO_ONORD_RTL_AMT_LCL |
The current outstanding order quantities and total cost/retail value of the units on order. These values represent the expected units that have not been received yet. Because this interface is positional (like inventory) we will carry forward the last known quantities every day unless you update the system with new records. This means that, even when the order is received or cancelled, we must eventually get a record that moves all of these columns to 0, or we will carry forward the non-zero values forever. |
DOC_CURR_CODE LOC_CURR_CODE |
The currency codes linked to the cost and retail amounts on the order data. The values
in these fields will control how the system converts the amount (such as |
It’s also necessary to understand the lifecycle of a purchase order and how that should be reflected in the data files over time. RAP will require data to be sent for each step in the order process as outlined below.
-
When the order is approved, the
ORDER_HEAD
file should contain a row for the order withstatus=A
and theORER_DETAIL
should contain the items on the order with non-zero quantities for the on-order amounts. -
As the lines of the order are received,
ORDER_HEAD
should continue to have the row for the order with every update, andORDER_DETAIL
should be sent with the order lines that require changes from the last known value. If you have the ability to detect which order lines changed, you only need to send those. RAP will remember and carry forward any order lines that were not updated. If you can’t detect the changes to order lines, just send all lines in the order every time. -
If any lines are cancelled from the order, you must send that update as a set of zero values on the
PO_ONORD_*
columns inORDER_DETAIL
to zero out the cancelled lines in RAP. Similarly, if the entire order is canceled or closed before being fully received, you must send all lines of the order with zero values on thePO_ONORD_*
columns inORDER_DETAIL
and also updateORDER_HEAD
to have a status ofC
. If this is not possible in your source system, you must configure the parameterPO_FULL_LOAD_IND
to a value ofY
in Manage System Configurations, then you will be allowed to send full loads of only non-zero order lines and the system will zero out the rest. -
As order lines start to be received normally, send the new order quantities for each change, including when a line is fully received and moves to 0 units on order. When an order becomes fully received we need all rows of data in RAP to move to 0 for that order’s values, so that we stop including it in future on-order rollups. If
PO_FULL_LOAD_IND=Y
then we don’t need zero balance updates from you, just stop sending the order details when it reaches zero and we will zero it out automatically. -
When an order is finally fully-received and closed, send one final update where
ORDER_HEAD
shows the status asC
and theORDER_DETAIL
data is moved to 0 units on order for any lines not updated yet.
Depending on your source system, it can be difficult to detect all of these changes to the purchase orders over time and
send only incremental updates. In such cases, you may always post all orders to RAP which are active or have been closed within
recent history and we will merge the data into the system on top of the existing order records. Then the main requirement
that must be accounted for is the cancelling or removal of order lines from an order, which must still be tracked and sent
to RAP even if your source system deletes the data (unless PO_FULL_LOAD_IND=Y
).
Other Fact File Considerations
The following section describes additional data considerations that may apply to your implementation depending on the types and volumes of data being provided to the platform. Review each topic closely, as it affects the data provided in the foundation files.
Positional Data Handling
The largest sets of fact data in the platform tend to be those that represent every possible item/location combination (such as prices or costs). To efficiently store and process these data volumes, a data warehouse technique known as compression is used to capture only the changed records on a day-to-day basis, effectively maintaining a “current position” for every set of identifiers, which is updated during each batch execution. The output of this compression process is called positional data, and the following functional areas use this method of data load and storage:
-
Inventory (INV and INVU)
-
Prices (PRICE)
-
Costs (BCOST and NCOST)
-
Purchase Orders (PO_ONORD) and Allocations On Order (PO_ONALC)
Positional data loads follow very specific rules and cannot be processed in the same manner as non-positional data such as sales transactions.
Table 8-12 Positional Data Rules
Rule | Explanation |
---|---|
Data Must be Sequential |
Positional data must be loaded in the order of the calendar date on which it occurs and cannot be loaded out-of-order. For example, when loading history data for inventory, you must provide each week of inventory one after the other, starting from Week 1, 2, 3, and so on. |
Data Cannot be Back Posted |
Positional data cannot be posted to any date prior to the current load date or business date of the system. If your current load date is Week 52 2021, you cannot post records back to Week 50: those past positions are unable to be changed. Any corrections that need to be loaded must be effective from the current date forward. |
Data Must be Seeded |
Because positional data must maintain the current position of all data elements in the fact (even those that are inactive or not changing) it is required to initialize or “seed” positional facts with a starting value for every possible combination of identifiers. This happens at two times:
|
Throughout the initial data load process, there will be additional steps called out any time a positional load must be performed, to ensure you accurately capture both historical and initial seed data before starting nightly batch runs.
System Parameters File
The dimension file for RA_SRC_CURR_PARAM_G.dat
is not used as part of your history load process directly,
but instead provides an important piece of information to the platform for operational activities. This file must contain
the current business date associated with the files in the ZIP package. The file should be included with the nightly ZIP upload
that contains your foundation data, such as RI_RMS_DATA.zip
or RAP_DATA.zip
. You may use
a CTX file to change the column delimiter used in the file, but you should retain the other format characteristics exactly
as shown below (for example, the date format must be YYYYMMDD
and it does not need a header row).
The file has only two generic columns, PARAM_NAME
and PARAM_VALUE
. When data is sourced
from RMFCS, it will be automatically generated and sent to RI in the nightly batch. If your data does not come from RMFCS,
then you need to include the file manually. Currently, only two rows will be used, but future releases may look for additional
parameters in the file.
For non-MFCS customers, the file should contain at least the following rows:
VDATE|20220101
PRIME_CURRENCY_CODE|USD
For anyone that will be using MFCS now or at any time in the future, you instead should provide the full set of parameters that MFCS would eventually be generating for you, like so:
PRIME_CURRENCY_CODE|USD
CONSOLIDATION_CODE|C
VAT_IND|Y
STKLDGR_VAT_INCL_RETL_IND|Y
MULTI_CURRENCY_IND|Y
CLASS_LEVEL_VAT_IND|Y
DOMAIN_LEVEL|D
CALENDAR_454_IND|4
VDATE|20230506
NEXT_VDATE|20230507
LAST_EOM_DATE|20240131
CURR_BOM_DATE|20240201
MAX_BACKPOST_DAYS|10
PRIME_EXCHNG_RATE|1
PRIMARY_LANG|EN
DEFAULT_TAX_TYPE|GTAX
INVOICE_LAST_POST_DATE|20170101
The parameter value with VDATE
is the current business date that all your other files were generated for
in YYYYMMDD
format. The date should match the values on your fact data, such as the DAY_DT
columns in sales, inventory, and so on. This format is not configurable and should be provided as shown. The parameter value
with PRIME_CURRENCY_CODE
is used by the system to set default currencies on fact files when you do not provide
them yourself or if there are null currency codes on a row.
Assuming you will be using RDE jobs to extract data from MFCS later on, the other parameters can be provided as shown above
or with any other values. The first time you run the RDE job ETLREFRESHGENSDE_JOB
, it will extract all the
parameter values from MFCS and directly update the RA_SRC_CURR_PARAM_G
table records. The update from MFCS
assumes that RA_SRC_CURR_PARAM_G
already has rows for all of the above parameters, which is why it is important
to initialize the data as shown if you are loading data from flat files.