6 Transformations
The Retail Analytics and Planning is a data-driven set of applications performing many transformations and calculations as part of normal operations. Review this chapter to learn about the most common types of data transformation activities occurring within the platform that may impact the data you send into the platform and the results you see as an end user.
Aggregate Tables in RI
Retail Insights accepts most data at a common base intersection of item, location, and date. However, downstream applications such as Planning or AI Foundation may need this data at higher levels of aggregation than that. To support those data needs, RI pre-aggregates the incoming fact data to certain higher levels, depending on the requirements of the consuming application. For Planning purposes, all fact data is aggregated to a common level of item, location, and fiscal week before it is exported for downstream consumption. Review the sections below to learn more about how data moves through Retail Insights for other applications to use.
Table Structures
If you are currently loading data into RI and need to access database tables for debugging or validation purposes, there are naming and format conventions used on each aggregate table. A base intersection table is abbreviated using the following notations:
Table 6-1 RI Base Fact Structure
Table Name Component | Explanation |
---|---|
W_ |
Most RI tables start with a “W” to denote a core data warehouse table. |
IT |
Abbreviation for Item |
LC |
Abbreviation for Location |
DY |
Abbreviation for Day |
WK |
Abbreviation for Week |
_D |
Dimensional tables end with “D” |
_F |
Base intersection fact tables end with “F” |
_A |
Aggregate tables end with “A” |
Using the above notation, you may interpret the table W_RTL_SLS_IT_LC_WK_A
as “Sales aggregate table at
the item/location/week intersection”.
Key Columns
Most fact tables in RI use the same key column structure, which consists of two types of internal identifiers. The first
identifier is referred to as a WID value. The WID on a fact table is a foreign key reference to a dimension table’s ROW_WID
column. For example, a PROD_WID
column in a sales table is referring to the ROW_WID
on W_PRODUCT_D
(the product dimension table). Joining the WIDs on a fact and a dimension will allow you
to look up user-facing descriptors for the dimensions, such as the product number.
The second identifier is known as SCD1_WID
and refers to slowly changing dimensions, which is a common
data warehousing concept. The IDs on the SCD1_WID
columns are carried forward through reclassifications and
other dimensional changes, allowing you to locate a single product throughout history, even if it has numerous records in
the parent dimension table. For example, joining PROD_SCD1_WID
from a sales table with SCD1_WID
on W_PRODUCT_D
will receive all instances of that product’s data throughout history, even if the product
has several different ROW_WID
entries due to reclassifications, which insert new records to the dimension
for the same item.
The other core structure to understand is Date WIDs (key column DT_WID
). These also join with the ROW_WID
of the parent dimension (W_MCAL_DAY_D
usually), but the format of the WID allows you to
extract the date value directly if needed, without table joins. The standard DT_WID
value used is a combination
of 1
+ date in YYYYMMDD + 000
. For example, 120210815000
is the DT_WID
value for “August 15, 2021”.
Transformations from RI to Planning
Data Filtering and Conversions
In addition to simply aggregating the incoming fact data from item/location/date to item/location/week level, it is also important to understand what RI is doing with the data as it moves from the input files to the outbound interfaces. The table below summarizes the transformations and business logic applied to shared facts used by RAP applications.
Table 6-2 RI Shared Data Transformations
Transformation | Explanation |
---|---|
Currency Conversion |
As part of the nightly batch, RI will use exchange rate information to convert all incoming data from the source currency to the primary business currency. All data sent to downstream applications is in the primary currency. The RI data model maintains separate columns for both local and primary currency amounts for BI usage. |
Tax Handling |
RI includes non-US taxes, such as VAT, in the sales retail amounts based on the indicators set up in the source system (such as Sales Audit). When sending the sales data to Planning and AI Foundation, the default sales values may include VAT and only specific VAT-exclusive fields will remove it. |
Transaction Date Usage |
All fact data coming into the system includes a transaction date on the record. RI aggregates from day to week level using transaction dates and does not alter or re-assign any records to different dates from what is provided. Transaction data in the past will be added to their historical week in the aggregates, no matter how far back it is dated. |
Pack Item Handling |
Downstream applications are currently only interested in the component item level, so RI will not send any data for pack items to other applications. Pack item sales must be spread to the component item level and loaded into the Sales Pack interface if this data is required for AI Foundation or Planning. All inventory, purchase order, and transaction data must be loaded at the component item level only. |
Stockholding Locations |
Inventory data for Planning is only exported for stockholding locations. A store indicated as a non-stockholding location on the location dimension will not be included in outbound inventory data. Physical warehouses which are not stockholding (because you use virtual warehouses) will also not be included. |
Warehouse Types |
Planning solutions assume that virtual warehouses are used as the stockholding locations for the business, and physical warehouses will be non-stockholding. For this reason, virtual warehouses are used to integrate data from RI to Planning, and no data is sent for the physical warehouses (except to indicate on each virtual WH the ID and name of the associated physical WH). If you don’t use virtual WHs, you can mark your physical WHs as virtual for the purposes of integration. |
Future On Order |
Planning applications require a forward-looking view of purchase orders based on the OTB EOW Date. RI accepts the actual purchase order details on the interfaces but will then transform the on-order amounts to be future-dated using the provided OTB EOW Dates. Orders which are past the OTB date will be included in the first EOW date, they will never be in the past. |
Include On Order |
Purchase Order data is limited by the Include On Order Flag on the
Order Head interface. A value of |
Orderable Items |
Purchase Order data is limited by the Orderable Flag on the Product
interface. A value of |
Inventory Adjustment Types |
RI accepts 3 types of inventory adjustments using the codes |
Inventory Receipt Types |
RI accepts 3 types of inventory receipts using the codes |
Inventory Transfer Types |
RI accepts 3 types of transfers using the codes |
Data Mappings
When you are generating input files to RAP, you may also want to know which columns are being moved to the output and how that data translates from what you see in the file to what you see in Planning applications. The list of mappings below describes how the data in the Retail Insights data model is exported to PDS.
Note:
Conversions and filters listed in the prior section of this chapter apply to all of this data (for example, data may be stored in local currency in RI but is always converted to the primary currency for export).Product Mapping
The item dimension and product hierarchy data is loaded mainly from the PRODUCT.csv
file or from RMFCS.
The primary RI table for item data is W_PRODUCT_D
while the hierarchy comes from W_PROD_CAT_DH
, but several temporary tables are used to pre-calculate the values before export. The mapping below is used by the interface
program to move data from RI to RDX. The temporary table W_RTL_ITEM_PARENT_TMP
is generated using data from W_PROD_CAT_DH
, W_PRODUCT_ATTR_D
, W_PRODUCT_D_TL
, W_RTL_IT_SUPPLIER_D
, and W_DOMAIN_MEMBER_LKP_TL
.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Item |
W_PDS_PRODUCT_D |
ITEM |
W_RTL_ITEM_PARENT_TMP.PROD_IT_NUM |
Item Desc |
W_PDS_PRODUCT_D |
ITEM_DESC |
W_RTL_ITEM_PARENT_TMP.PRODUCT_NAME |
Item Parent Diff |
W_PDS_PRODUCT_D |
ITEM_PARENT_DIFF |
CASE WHEN W_RTL_ITEM_PARENT_TMP.DIFF_AGGREGATE_ID = ''-1'' THEN W_RTL_ITEM_PARENT_TMP.PARENT_PROD_NUM ELSE W_RTL_ITEM_PARENT_TMP.PARENT_PROD_NUM||'':''||W_RTL_ITEM_PARENT_TMP.DIFF_AGGREGATE_ID END |
Item Parent Diff Desc |
W_PDS_PRODUCT_D |
ITEM_PARENT_DIFF_DESC |
CASE WHEN W_RTL_ITEM_PARENT_TMP.DIFF_AGGREGATE_ID = ''-1'' THEN W_RTL_ITEM_PARENT_TMP.PARENT_PRODUCT_NAME ELSE W_RTL_ITEM_PARENT_TMP.PARENT_PRODUCT_NAME||'':''||W_RTL_ITEM_PARENT_TMP.DIFF_AGGREGATE_ID END |
Item Parent |
W_PDS_PRODUCT_D |
ITEM_PARENT |
W_RTL_ITEM_PARENT_TMP.PARENT_PROD_NUM |
Item Parent Desc |
W_PDS_PRODUCT_D |
ITEM_PARENT_DESC |
W_RTL_ITEM_PARENT_TMP.PARENT_PRODUCT_NAME |
Subclass ID |
W_PDS_PRODUCT_D |
SUBCLASS_ID |
W_RTL_ITEM_PARENT_TMP.SUBCLASS_ID |
Subclass Label |
W_PDS_PRODUCT_D |
SUB_NAME |
W_RTL_ITEM_PARENT_TMP.PROD_SC_NUM||'' ''||W_RTL_ITEM_PARENT_TMP.SBC_DESC |
Class ID |
W_PDS_PRODUCT_D |
CLASS_ID |
W_RTL_ITEM_PARENT_TMP.CLASS_ID |
Class Label |
W_PDS_PRODUCT_D |
CLASS_NAME |
W_RTL_ITEM_PARENT_TMP.PROD_CL_NUM||'' ''||W_RTL_ITEM_PARENT_TMP.CLS_DESC |
Department |
W_PDS_PRODUCT_D |
DEPT |
W_RTL_ITEM_PARENT_TMP.PROD_DP_NUM |
Department Label |
W_PDS_PRODUCT_D |
DEPT_NAME |
W_RTL_ITEM_PARENT_TMP.PROD_DP_NUM||'' ''||W_RTL_ITEM_PARENT_TMP.DP_DESC |
Group |
W_PDS_PRODUCT_D |
GROUP_NO |
W_RTL_ITEM_PARENT_TMP.PROD_GP_NUM |
Group Label |
W_PDS_PRODUCT_D |
GROUP_NAME |
W_RTL_ITEM_PARENT_TMP.PROD_GP_NUM||'' ''||W_RTL_ITEM_PARENT_TMP.GRP_DESC |
Division |
W_PDS_PRODUCT_D |
DIVISION |
W_RTL_ITEM_PARENT_TMP.PROD_DV_NUM |
Division Label |
W_PDS_PRODUCT_D |
DIV_NAME |
W_RTL_ITEM_PARENT_TMP.PROD_DV_NUM||'' ''||W_RTL_ITEM_PARENT_TMP.DIV_DESC |
Company |
W_PDS_PRODUCT_D |
COMPANY |
W_RTL_ITEM_PARENT_TMP.CMP_NUM |
Company Label |
W_PDS_PRODUCT_D |
CO_NAME |
W_RTL_ITEM_PARENT_TMP.CMP_NUM||'' ''||W_RTL_ITEM_PARENT_TMP.CMP_DESC |
Forecastable Flag |
W_PDS_PRODUCT_D |
FORECAST_IND |
W_RTL_ITEM_PARENT_TMP.FORECAST_IND |
Class |
W_PDS_PRODUCT_D |
CLASS_DISPLAY_ID |
W_RTL_ITEM_PARENT_TMP.PROD_CL_NUM |
Subclass |
W_PDS_PRODUCT_D |
SUBCLASS_DISPLAY_ID |
W_RTL_ITEM_PARENT_TMP.PROD_SC_NUM |
Brand |
W_PDS_PRODUCT_D |
BRAND_NAME |
W_RTL_ITEM_PARENT_TMP.BRAND |
Brand Label |
W_PDS_PRODUCT_D |
BRAND_DESCRIPTION |
W_RTL_ITEM_PARENT_TMP.BRAND_DESC |
Supplier |
W_PDS_PRODUCT_D |
SUPPLIER |
NVL(W_PARTY_ORG_D.SUPPLIER_NUM''-1'') |
Supplier Label |
W_PDS_PRODUCT_D |
SUP_NAME |
NVL(W_PARTY_ORG_D.ORG_NAME''N/A'') |
Diff 1 |
W_PDS_PRODUCT_D |
DIFF_1 |
W_PRODUCT_D.DIFF_1 |
Diff 1 Type |
W_PDS_PRODUCT_D |
DIFF_1_TYPE |
W_PRODUCT_D.DIFF_1_TYPE |
Diff 2 |
W_PDS_PRODUCT_D |
DIFF_2 |
W_PRODUCT_D.DIFF_2 |
Diff 2 Type |
W_PDS_PRODUCT_D |
DIFF_2_TYPE |
W_PRODUCT_D.DIFF_2_TYPE |
Diff 3 |
W_PDS_PRODUCT_D |
DIFF_3 |
W_PRODUCT_D.DIFF_3 |
Diff 3 Type |
W_PDS_PRODUCT_D |
DIFF_3_TYPE |
W_PRODUCT_D.DIFF_3_TYPE |
Diff 4 |
W_PDS_PRODUCT_D |
DIFF_4 |
W_PRODUCT_D.DIFF_4 |
Diff 4 Type |
W_PDS_PRODUCT_D |
DIFF_4_TYPE |
W_PRODUCT_D.DIFF_4_TYPE |
Cost Zone Group ID |
W_PDS_PRODUCT_D |
COST_ZONE_GROUP_ID |
W_PRODUCT_D.COST_ZONE_GROUP_ID |
UOM Conv Factor |
W_PDS_PRODUCT_D |
UOM_CONV_FACTOR |
W_PRODUCT_D.UOM_CONV_FACTOR |
Store Order Multiple |
W_PDS_PRODUCT_D |
STORE_ORD_MULT |
W_PRODUCT_D.STORE_ORD_MULT |
Retail Label Type |
W_PDS_PRODUCT_D |
RETAIL_LABEL_TYPE |
W_PRODUCT_D.RETAIL_LABEL_TYPE |
Retail Label Value |
W_PDS_PRODUCT_D |
RETAIL_LABEL_VALUE |
W_PRODUCT_D.RETAIL_LABEL_VALUE |
Handling Temp |
W_PDS_PRODUCT_D |
HANDLING_TEMP |
W_PRODUCT_D.HANDLING_TEMP |
Handling Sensitivity |
W_PDS_PRODUCT_D |
HANDLING_SENSITIVITY |
W_PRODUCT_D.HANDLING_SENSITIVITY |
Catch Weight Flag |
W_PDS_PRODUCT_D |
CATCH_WEIGHT_IND |
W_PRODUCT_D.CATCH_WEIGHT_IND |
Waste Type |
W_PDS_PRODUCT_D |
WASTE_TYPE |
W_PRODUCT_D.WASTE_TYPE |
Waste Percent |
W_PDS_PRODUCT_D |
WASTE_PCT |
W_PRODUCT_D.WASTE_PCT |
Default Waste Percent |
W_PDS_PRODUCT_D |
DEFAULT_WASTE_PCT |
W_PRODUCT_D.DEFAULT_WASTE_PCT |
Item Service Level |
W_PDS_PRODUCT_D |
ITEM_SERVICE_LEVEL |
W_PRODUCT_D.ITEM_SERVICE_LEVEL |
Gift Wrap Flag |
W_PDS_PRODUCT_D |
GIFT_WRAP_IND |
W_PRODUCT_D.GIFT_WRAP_IND |
Ship Alone Flag |
W_PDS_PRODUCT_D |
SHIP_ALONE_IND |
W_PRODUCT_D.SHIP_ALONE_IND |
Order Type |
W_PDS_PRODUCT_D |
ORDER_TYPE |
W_PRODUCT_D.ORDER_TYPE |
Sales Type |
W_PDS_PRODUCT_D |
SALE_TYPE |
W_PRODUCT_D.SALE_TYPE |
Deposit Item Type |
W_PDS_PRODUCT_D |
DEPOSIT_ITEM_TYPE |
W_PRODUCT_D.DEPOSIT_ITEM_TYPE |
Container Item |
W_PDS_PRODUCT_D |
CONTAINER_ITEM |
W_PRODUCT_D.CONTAINER_ITEM |
Deposit Price Per UOM |
W_PDS_PRODUCT_D |
DEPOSIT_IN_PRICE_PER_UOM |
W_PRODUCT_D.DEPOSIT_IN_PRICE_PER_UOM |
AIP Case Type |
W_PDS_PRODUCT_D |
AIP_CASE_TYPE |
W_PRODUCT_D.AIP_CASE_TYPE |
Perishable Flag |
W_PDS_PRODUCT_D |
PERISHABLE_IND |
W_PRODUCT_D.PERISHABLE_IND |
Catch Weight UOM |
W_PDS_PRODUCT_D |
CATCH_WEIGHT_UOM |
W_PRODUCT_D.CATCH_WEIGHT_UOM |
Orderable Flag |
W_PDS_PRODUCT_D |
ORDERABLE_FLG |
W_PRODUCT_D.ORDERABLE_FLG |
Inventoried Flag |
W_PDS_PRODUCT_D |
INVENTORIED_FLG |
W_RTL_ITEM_PARENT_TMP.INVENTORIED_FLG |
Flexible Attribute 1 |
W_PDS_PRODUCT_D |
FLEX1_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX1_CHAR_VALUE, W_PRODUCT_D.FLEX1_CHAR_VALUE) |
Flexible Attribute 2 |
W_PDS_PRODUCT_D |
FLEX2_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX2_CHAR_VALUE, W_PRODUCT_D.FLEX2_CHAR_VALUE) |
Flexible Attribute 3 |
W_PDS_PRODUCT_D |
FLEX3_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX3_CHAR_VALUE, W_PRODUCT_D.FLEX3_CHAR_VALUE) |
Flexible Attribute 4 |
W_PDS_PRODUCT_D |
FLEX4_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX4_CHAR_VALUE, W_PRODUCT_D.FLEX4_CHAR_VALUE) |
Flexible Attribute 5 |
W_PDS_PRODUCT_D |
FLEX5_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX5_CHAR_VALUE, W_PRODUCT_D.FLEX5_CHAR_VALUE) |
Flexible Attribute 6 |
W_PDS_PRODUCT_D |
FLEX6_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX6_CHAR_VALUE, W_PRODUCT_D.FLEX6_CHAR_VALUE) |
Flexible Attribute 7 |
W_PDS_PRODUCT_D |
FLEX7_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX7_CHAR_VALUE, W_PRODUCT_D.FLEX7_CHAR_VALUE) |
Flexible Attribute 8 |
W_PDS_PRODUCT_D |
FLEX8_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX8_CHAR_VALUE, W_PRODUCT_D.FLEX8_CHAR_VALUE) |
Flexible Attribute 9 |
W_PDS_PRODUCT_D |
FLEX9_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX9_CHAR_VALUE, W_PRODUCT_D.FLEX9_CHAR_VALUE) |
Flexible Attribute 10 |
W_PDS_PRODUCT_D |
FLEX10_CHAR_VALUE |
COALESCE(W_PRODUCT_FLEX_D.FLEX10_CHAR_VALUE, W_PRODUCT_D.FLEX10_CHAR_VALUE) |
Flexible Attribute 11 |
W_PDS_PRODUCT_D |
FLEX11_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX11_CHAR_VALUE |
Flexible Attribute 12 |
W_PDS_PRODUCT_D |
FLEX12_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX12_CHAR_VALUE |
Flexible Attribute 13 |
W_PDS_PRODUCT_D |
FLEX13_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX13_CHAR_VALUE |
Flexible Attribute 14 |
W_PDS_PRODUCT_D |
FLEX14_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX14_CHAR_VALUE |
Flexible Attribute 15 |
W_PDS_PRODUCT_D |
FLEX15_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX15_CHAR_VALUE |
Flexible Attribute 16 |
W_PDS_PRODUCT_D |
FLEX16_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX16_CHAR_VALUE |
Flexible Attribute 17 |
W_PDS_PRODUCT_D |
FLEX17_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX17_CHAR_VALUE |
Flexible Attribute 18 |
W_PDS_PRODUCT_D |
FLEX18_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX18_CHAR_VALUE |
Flexible Attribute 19 |
W_PDS_PRODUCT_D |
FLEX19_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX19_CHAR_VALUE |
Flexible Attribute 20 |
W_PDS_PRODUCT_D |
FLEX20_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX20_CHAR_VALUE |
Flexible Attribute 21 |
W_PDS_PRODUCT_D |
FLEX21_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX21_CHAR_VALUE |
Flexible Attribute 22 |
W_PDS_PRODUCT_D |
FLEX22_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX22_CHAR_VALUE |
Flexible Attribute 23 |
W_PDS_PRODUCT_D |
FLEX23_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX23_CHAR_VALUE |
Flexible Attribute 24 |
W_PDS_PRODUCT_D |
FLEX24_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX24_CHAR_VALUE |
Flexible Attribute 25 |
W_PDS_PRODUCT_D |
FLEX25_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX25_CHAR_VALUE |
Flexible Attribute 26 |
W_PDS_PRODUCT_D |
FLEX26_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX26_CHAR_VALUE |
Flexible Attribute 27 |
W_PDS_PRODUCT_D |
FLEX27_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX27_CHAR_VALUE |
Flexible Attribute 28 |
W_PDS_PRODUCT_D |
FLEX28_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX28_CHAR_VALUE |
Flexible Attribute 29 |
W_PDS_PRODUCT_D |
FLEX29_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX29_CHAR_VALUE |
Flexible Attribute 30 |
W_PDS_PRODUCT_D |
FLEX30_CHAR_VALUE |
W_PRODUCT_FLEX_D.FLEX30_CHAR_VALUE |
Organization Mapping
The location dimension and organization hierarchy data is loaded mainly from the ORGANIZATION.csv
file
or from RMFCS. The primary RI table for location data is W_INT_ORG_D
while the hierarchy comes from W_INT_ORG_DH
, but several other tables are used to pre-calculate the values before export. The mapping below is
used by the interface program to move data from RI to RDX. W_DOMAIN_MEMBER_LKP_TL
is the holding table for
translatable description strings. W_INT_ORG_ATTR_D
is for location attributes. Other tables ending in TL
are for lookup strings for specific entities like store names.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Location |
W_PDS_ORGANIZATION_D |
LOCATION |
W_INT_ORG_D.ORG_NUM |
Location Label |
W_PDS_ORGANIZATION_D |
LOC_NAME |
W_INT_ORG_D.ORG_NUM||'' ''||W_INT_ORG_D_TL.ORG_NAME |
District |
W_PDS_ORGANIZATION_D |
DISTRICT |
'WH''||W_INT_ORG_D.ORG_NUM (warehouses), W_INT_ORG_DH_RTL_TMP.ORG_DS_NUM (stores) |
District Label |
W_PDS_ORGANIZATION_D |
DISTRICT_NAME |
W_INT_ORG_D_TL.ORG_NAME (warehouses), W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME (stores) |
Region |
W_PDS_ORGANIZATION_D |
REGION |
''WH''||W_INT_ORG_D.ORG_NUM (warehouses), W_INT_ORG_DH_RTL_TMP.ORG_RG_NUM (stores) |
Region Label |
W_PDS_ORGANIZATION_D |
REGION_NAME |
W_INT_ORG_D_TL.ORG_NAME (warehouses), W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME (stores) |
Area |
W_PDS_ORGANIZATION_D |
AREA |
''WH''||W_INT_ORG_D.ORG_NUM (warehouses), W_INT_ORG_DH_RTL_TMP.ORG_AR_NUM (stores) |
Area Label |
W_PDS_ORGANIZATION_D |
AREA_NAME |
W_INT_ORG_D_TL.ORG_NAME (warehouses), W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME (stores) |
Chain |
W_PDS_ORGANIZATION_D |
CHAIN |
''WH''||W_INT_ORG_D.ORG_NUM (warehouses), W_INT_ORG_DH_RTL_TMP.ORG_CH_NUM (stores) |
Chain Label |
W_PDS_ORGANIZATION_D |
CHAIN_NAME |
W_INT_ORG_D_TL.ORG_NAME (warehouses), W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME (stores) |
Company |
W_PDS_ORGANIZATION_D |
COMPANY |
W_INT_ORG_DH.ORG_TOP_NUM |
Company Label |
W_PDS_ORGANIZATION_D |
CO_NAME |
W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME |
Company Primary Currency |
W_PDS_ORGANIZATION_D |
COMPANY_CURRENCY |
RA_SRC_CURR_PARAM_G.COMPANY_CURRENCY |
Location Type Code |
W_PDS_ORGANIZATION_D |
LOC_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR42_NAME |
Location Type |
W_PDS_ORGANIZATION_D |
LOC_TYPE_NAME |
''Warehouse'' or "Store" |
Physical Warehouse ID for VWH |
W_PDS_ORGANIZATION_D |
PHYSICAL_WH |
W_INT_ORG_ATTR_D.ORG_ATTR14_NAME |
Physical Warehouse Name |
W_PDS_ORGANIZATION_D |
PHYSICAL_WH_NAME |
W_INT_ORG_D_TL.ORG_NAME |
Channel ID |
W_PDS_ORGANIZATION_D |
CHANNEL_ID |
NVL(TO_CHAR(W_INT_ORG_ATTR_D.ORG_ATTR5_NUM_VALUE) ''NA'') |
Channel Desc |
W_PDS_ORGANIZATION_D |
CHANNEL_NAME |
NVL(W_INT_ORG_ATTR_D.ORG_ATTR5_NAME ''UNASSIGNED'') |
Store Class |
W_PDS_ORGANIZATION_D |
STORE_CLASS |
CASE NVL(W_INT_ORG_ATTR_D.ORG_ATTR41_NAME,''-1'') WHEN ''-1'' THEN ''NA'' ELSE W_INT_ORG_ATTR_D.ORG_ATTR41_NAME END |
Store Class Desc |
W_PDS_ORGANIZATION_D |
STORE_CLASS_DESCRIPTION |
CASE NVL(W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME,''-1'') WHEN ''-1'' THEN ''NA'' ELSE W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME END |
Store Format |
W_PDS_ORGANIZATION_D |
STORE_FORMAT |
CASE NVL(W_INT_ORG_ATTR_D.ORG_ATTR22_NAME,''-1'') WHEN ''-1'' THEN ''NA'' ELSE W_INT_ORG_ATTR_D.ORG_ATTR22_NAME END |
Store Format Desc |
W_PDS_ORGANIZATION_D |
STORE_FORMAT_NAME |
CASE NVL(W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME,''-1'') WHEN ''-1'' THEN ''NA'' ELSE W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME END |
Store Close Date |
W_PDS_ORGANIZATION_D |
STORE_CLOSE_DATE |
W_INT_ORG_ATTR_D.ORG_ATTR3_DATE |
Store Open Date |
W_PDS_ORGANIZATION_D |
STORE_OPEN_DATE |
W_INT_ORG_ATTR_D.ORG_ATTR2_DATE |
Store Remodel Date |
W_PDS_ORGANIZATION_D |
REMODEL_DATE |
W_INT_ORG_ATTR_D.ORG_ATTR1_DATE |
Location Currency Code |
W_PDS_ORGANIZATION_D |
CURRENCY |
W_INT_ORG_D.W_CURR_CODE |
Store Type |
W_PDS_ORGANIZATION_D |
STORE_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR23_NAME |
Stockholding Flag |
W_PDS_ORGANIZATION_D |
STOCKHOLDING_IND |
NVL(W_INT_ORG_ATTR_D.ORG_ATTR19_NAME, ’Y’) |
Default Warehouse ID |
W_PDS_ORGANIZATION_D |
DEFAULT_WH_ID |
W_INT_ORG_ATTR_D.ORG_ATTR20_NAME |
Store Format Description |
W_PDS_ORGANIZATION_D |
STORE_FORMAT_DESC |
W_INT_ORG_ATTR_D.ORG_ATTR21_NAME |
Store Format ID |
W_PDS_ORGANIZATION_D |
STORE_FORMAT_ID |
W_INT_ORG_ATTR_D.ORG_ATTR22_NAME |
Store UPS Disst |
W_PDS_ORGANIZATION_D |
STORE_UPS_DIST |
W_INT_ORG_ATTR_D.ORG_ATTR24_NAME |
Time Zone |
W_PDS_ORGANIZATION_D |
TIME_ZONE |
W_INT_ORG_ATTR_D.ORG_ATTR25_NAME |
Transfer Zone ID |
W_PDS_ORGANIZATION_D |
TRANSFER_ZONE_ID |
W_INT_ORG_ATTR_D.ORG_ATTR26_NAME |
Transfer Zone Description |
W_PDS_ORGANIZATION_D |
TRANSFER_ZONE_DESC |
W_INT_ORG_ATTR_D.ORG_ATTR27_NAME |
VAT Region ID |
W_PDS_ORGANIZATION_D |
VAT_REGION_ID |
W_INT_ORG_ATTR_D.ORG_ATTR28_NAME |
VAT Include Flag |
W_PDS_ORGANIZATION_D |
VAT_INCLUDE_FLG |
W_INT_ORG_ATTR_D.ORG_ATTR29_NAME |
Virtual Warehouse Flag |
W_PDS_ORGANIZATION_D |
VIRTUAL_WH_FLG |
W_INT_ORG_ATTR_D.ORG_ATTR30_NAME |
Transfer Entity ID |
W_PDS_ORGANIZATION_D |
TRANSFER_ENTITY_ID |
W_INT_ORG_ATTR_D.ORG_ATTR31_NAME |
Transfer Entity Description |
W_PDS_ORGANIZATION_D |
TRANSFER_ENTITY_DESC |
W_INT_ORG_ATTR_D.ORG_ATTR32_NAME |
Wholesale/Franchise Cust Type |
W_PDS_ORGANIZATION_D |
WF_CUST_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR35_NAME |
Wholesale/Franchise Group ID |
W_PDS_ORGANIZATION_D |
WF_GROUP_ID |
W_INT_ORG_ATTR_D.ORG_ATTR36_NAME |
Wholesale/Franchise Group Name |
W_PDS_ORGANIZATION_D |
WF_GROUP_NAME |
W_INT_ORG_ATTR_D.ORG_ATTR37_NAME |
Wholesale/Franchise Cust ID |
W_PDS_ORGANIZATION_D |
WF_CUST_ID |
W_INT_ORG_ATTR_D.ORG_ATTR38_NAME |
Wholesale/Franchise Cust Name |
W_PDS_ORGANIZATION_D |
WF_CUST_NAME |
W_INT_ORG_ATTR_D.ORG_ATTR39_NAME |
Sister Store ID |
W_PDS_ORGANIZATION_D |
SISTER_STORE_ID |
W_INT_ORG_ATTR_D.ORG_ATTR40_NAME |
Store Class Type |
W_PDS_ORGANIZATION_D |
STORE_CLASS_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR41_NAME |
Store Class Desc |
W_PDS_ORGANIZATION_D |
STORE_CLASS_DESC |
W_INT_ORG_ATTR_D.ORG_ATTR44_NAME |
Customer Order Location Indicator |
W_PDS_ORGANIZATION_D |
CUST_ORDER_LOC_IND |
W_INT_ORG_ATTR_D.ORG_ATTR48_NAME |
Customer Order Shipping Indicator |
W_PDS_ORGANIZATION_D |
CUST_ORDER_SHIP_IND |
W_INT_ORG_ATTR_D.ORG_ATTR49_NAME |
Gift Wrapping Indicator |
W_PDS_ORGANIZATION_D |
GIFT_WRAPPING_IND |
W_INT_ORG_ATTR_D.ORG_ATTR50_NAME |
Location Language ISO Code |
W_PDS_ORGANIZATION_D |
LANG_ISO_CODE |
W_INT_ORG_ATTR_D.ORG_ATTR51_NAME |
WH Delivery Policy |
W_PDS_ORGANIZATION_D |
WH_DELIVERY_POLICY |
W_INT_ORG_ATTR_D.ORG_ATTR54_NAME |
WH Redistribution Indicator |
W_PDS_ORGANIZATION_D |
WH_REDIST_IND |
W_INT_ORG_ATTR_D.ORG_ATTR55_NAME |
WH Replenishment Indicator |
W_PDS_ORGANIZATION_D |
WH_REPL_IND |
W_INT_ORG_ATTR_D.ORG_ATTR56_NAME |
WH Finisher Indicator |
W_PDS_ORGANIZATION_D |
WH_FINISHER_IND |
W_INT_ORG_ATTR_D.ORG_ATTR57_NAME |
Virtual WH Type |
W_PDS_ORGANIZATION_D |
VIRTUAL_WH_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR58_NAME |
DUNS Number |
W_PDS_ORGANIZATION_D |
DUNS_NUMBER |
W_INT_ORG_ATTR_D.ORG_ATTR59_NAME |
DUNS Location |
W_PDS_ORGANIZATION_D |
DUNS_LOC |
W_INT_ORG_ATTR_D.ORG_ATTR60_NAME |
Selling Area Sq. Ft. |
W_PDS_ORGANIZATION_D |
SELLING_AREA |
W_INT_ORG_ATTR_D.ORG_ATTR1_NUM_VALUE |
Linear Distance |
W_PDS_ORGANIZATION_D |
LINEAR_DISTANCE |
W_INT_ORG_ATTR_D.ORG_ATTR2_NUM_VALUE |
Total Sq. Ft. |
W_PDS_ORGANIZATION_D |
TOTAL_AREA |
W_INT_ORG_ATTR_D.ORG_ATTR3_NUM_VALUE |
WH Inbound Handling Days |
W_PDS_ORGANIZATION_D |
INBOUND_HANDLING_DAYS |
W_INT_ORG_ATTR_D.ORG_ATTR6_NUM_VALUE |
Stop Order Days |
W_PDS_ORGANIZATION_D |
STOP_ORDER_DAYS |
W_INT_ORG_ATTR_D.ORG_ATTR7_NUM_VALUE |
Start Order Days |
W_PDS_ORGANIZATION_D |
START_ORDER_DAYS |
W_INT_ORG_ATTR_D.ORG_ATTR8_NUM_VALUE |
Flexible Attribute 1 |
W_PDS_ORGANIZATION_D |
FLEX1_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX1_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX1_CHAR_VALUE) |
Flexible Attribute 2 |
W_PDS_ORGANIZATION_D |
FLEX2_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX2_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX2_CHAR_VALUE) |
Flexible Attribute 3 |
W_PDS_ORGANIZATION_D |
FLEX3_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX3_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX3_CHAR_VALUE) |
Flexible Attribute 4 |
W_PDS_ORGANIZATION_D |
FLEX4_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX4_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX4_CHAR_VALUE) |
Flexible Attribute 5 |
W_PDS_ORGANIZATION_D |
FLEX5_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX5_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX5_CHAR_VALUE) |
Flexible Attribute 6 |
W_PDS_ORGANIZATION_D |
FLEX6_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX6_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX6_CHAR_VALUE) |
Flexible Attribute 7 |
W_PDS_ORGANIZATION_D |
FLEX7_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX7_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX7_CHAR_VALUE) |
Flexible Attribute 8 |
W_PDS_ORGANIZATION_D |
FLEX8_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX8_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX8_CHAR_VALUE) |
Flexible Attribute 9 |
W_PDS_ORGANIZATION_D |
FLEX9_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX9_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX9_CHAR_VALUE) |
Flexible Attribute 10 |
W_PDS_ORGANIZATION_D |
FLEX10_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX10_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX10_CHAR_VALUE) |
Flexible Attribute 11 |
W_PDS_ORGANIZATION_D |
FLEX11_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX11_CHAR_VALUE |
Flexible Attribute 12 |
W_PDS_ORGANIZATION_D |
FLEX12_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX12_CHAR_VALUE |
Flexible Attribute 13 |
W_PDS_ORGANIZATION_D |
FLEX13_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX13_CHAR_VALUE |
Flexible Attribute 14 |
W_PDS_ORGANIZATION_D |
FLEX14_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX14_CHAR_VALUE |
Flexible Attribute 15 |
W_PDS_ORGANIZATION_D |
FLEX15_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX15_CHAR_VALUE |
Flexible Attribute 16 |
W_PDS_ORGANIZATION_D |
FLEX16_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX16_CHAR_VALUE |
Flexible Attribute 17 |
W_PDS_ORGANIZATION_D |
FLEX17_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX17_CHAR_VALUE |
Flexible Attribute 18 |
W_PDS_ORGANIZATION_D |
FLEX18_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX18_CHAR_VALUE |
Flexible Attribute 19 |
W_PDS_ORGANIZATION_D |
FLEX19_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX19_CHAR_VALUE |
Flexible Attribute 20 |
W_PDS_ORGANIZATION_D |
FLEX20_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX20_CHAR_VALUE |
Flexible Attribute 21 |
W_PDS_ORGANIZATION_D |
FLEX21_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX21_CHAR_VALUE |
Flexible Attribute 22 |
W_PDS_ORGANIZATION_D |
FLEX22_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX22_CHAR_VALUE |
Flexible Attribute 23 |
W_PDS_ORGANIZATION_D |
FLEX23_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX23_CHAR_VALUE |
Flexible Attribute 24 |
W_PDS_ORGANIZATION_D |
FLEX24_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX24_CHAR_VALUE |
Flexible Attribute 25 |
W_PDS_ORGANIZATION_D |
FLEX25_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX25_CHAR_VALUE |
Flexible Attribute 26 |
W_PDS_ORGANIZATION_D |
FLEX26_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX26_CHAR_VALUE |
Flexible Attribute 27 |
W_PDS_ORGANIZATION_D |
FLEX27_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX27_CHAR_VALUE |
Flexible Attribute 28 |
W_PDS_ORGANIZATION_D |
FLEX28_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX28_CHAR_VALUE |
Flexible Attribute 29 |
W_PDS_ORGANIZATION_D |
FLEX29_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX29_CHAR_VALUE |
Flexible Attribute 30 |
W_PDS_ORGANIZATION_D |
FLEX30_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX30_CHAR_VALUE |
Calendar Mapping
The calendar hierarchy data is loaded from the CALENDAR.csv
file or from RMFCS. The calendar is a fiscal
calender (4-4-5 or 4-5-4). The primary RI table having day-level data is W_MCAL_DAY_D
. RI automatically generates
the calendar using the start/end dates for the periods in the input file.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Day Date |
W_PDS_CALENDAR_D |
DAY |
W_MCAL_DAY_D.MCAL_DAY_DT |
Week Ending Date |
W_PDS_CALENDAR_D |
WEEK |
W_MCAL_DAY_D.MCAL_WEEK_END_DT |
Month Number |
W_PDS_CALENDAR_D |
MONTH |
W_MCAL_DAY_D.MCAL_PERIOD |
Quarter Number |
W_PDS_CALENDAR_D |
QUARTER |
W_MCAL_DAY_D.MCAL_QTR |
Half Year Number |
W_PDS_CALENDAR_D |
HALF |
CASE WHEN W_MCAL_DAY_D.MCAL_QTR <= 2 THEN 1 ELSE 2 END |
Year Number |
W_PDS_CALENDAR_D |
YEAR |
W_MCAL_DAY_D.MCAL_YEAR |
Week of Year |
W_PDS_CALENDAR_D |
WEEK_OF_YEAR |
W_MCAL_DAY_D.MCAL_WEEK_OF_YEAR |
Day of Week |
W_PDS_CALENDAR_D |
DAY_OF_WEEK |
W_MCAL_DAY_D.MCAL_DAY_OF_WEEK |
Exchange Rate Mapping
The exchange rate data is loaded from the EXCH_RATE.csv
file or from RMFCS.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Start Date |
W_PDS_EXCH_RATE_G |
EFFECTIVE_DATE |
W_EXCH_RATE_G.START_DT |
From Currency Code |
W_PDS_EXCH_RATE_G |
FROM_CURRENCY_CODE |
W_EXCH_RATE_G.W_FROM_CURCY_CODE |
To Currency Code |
W_PDS_EXCH_RATE_G |
TO_CURRENCY_CODE |
W_EXCH_RATE_G.W_TO_CURCY_CODE |
Exchange Rate Type |
W_PDS_EXCH_RATE_G |
EXCHANGE_TYPE |
W_EXCH_RATE_G.RATE_TYPE |
Exchange Rate |
W_PDS_EXCH_RATE_G |
EXCHANGE_RATE |
W_EXCH_RATE_G.EXCH_RATE |
User Defined Attributes (UDA) Mapping
The user-defined attributes label data is loaded from the ATTR.csv
file or from RMFCS. This table is only
for the attribute group and value labels and hierarchy. UDA type code refers to 3 types (LV
, FF
, or DT
) which is a list of values, free-form text, or date attribute type. Different implementations may
require different subsets of UDAs from their source system.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
UDA Type |
W_PDS_UDA_D |
UDA_TYPE_CODE |
W_RTL_PRODUCT_ATTR_D.PROD_ATTR_TYPE |
UDA Group ID |
W_PDS_UDA_D |
UDA_ID |
W_RTL_PRODUCT_ATTR_D.PROD_ATTR_GROUP_ID |
UDA Group Desc |
W_PDS_UDA_D |
UDA_DESC |
W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME |
UDA Value ID |
W_PDS_UDA_D |
UDA_VALUE |
W_RTL_PRODUCT_ATTR_D.PROD_ATTR_ID |
UDA Value Desc |
W_PDS_UDA_D |
UDA_VALUE_DESC |
W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME |
Differentiator Attributes Mapping
The differentiator data is loaded from the ATTR.csv
file or from RMFCS. This table is only for the attribute
group and value labels and hierarchy. Diffs include any input data marked as type DIFF
, as well as pre-defined
diff types such as COLOR
and SIZE
.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Diff Group ID |
W_PDS_DIFF_D |
DIFF_TYPE_ID |
W_RTL_PRODUCT_ATTR_D.PROD_ATTR_GROUP_ID |
Diff Group Desc |
W_PDS_DIFF_D |
DIFF_TYPE_DESC |
W_RTL_PRODUCT_ATTR_D_TL.PROD_ATTR_TYPE_DESC |
Diff ID |
W_PDS_DIFF_D |
DIFF_ID |
W_RTL_PRODUCT_ATTR_D.PROD_ATTR_ID |
Diff Desc |
W_PDS_DIFF_D |
DIFF_DESC |
W_RTL_PRODUCT_ATTR_D_TL.PROD_ATTR_DESC |
Item Attributes Mapping
The item attribute relationship data is loaded from the PROD_ATTR.csv
file or from RMFCS. This is the
relationship between items (SKUs) and their attributes (UDAs).
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Item |
W_PDS_PRODUCT_ATTR_D |
ITEM |
W_PRODUCT_D_RTL_TMP.PROD_IT_NUM |
UDA Type |
W_PDS_PRODUCT_ATTR_D |
UDA_TYPE |
W_RTL_ITEM_GRP1_D. FLEX_ATTRIB_2_CHAR |
UDA Group ID |
W_PDS_PRODUCT_ATTR_D |
UDA_ID |
W_RTL_ITEM_GRP1_D. FLEX_ATTRIB_1_CHAR |
UDA Group Desc |
W_PDS_PRODUCT_ATTR_D |
UDA_DESC |
W_DOMAIN_MEMBER_LKP_TL. DOMAIN_MEMBER_NAME |
UDA Value ID |
W_PDS_PRODUCT_ATTR_D |
UDA_VALUE |
W_RTL_ITEM_GRP1_D. FLEX_ATTRIB_3_CHAR |
UDA Value Desc |
W_PDS_PRODUCT_ATTR_D |
UDA_VALUE_DESC |
W_DOMAIN_MEMBER_LKP_TL. DOMAIN_MEMBER_NAME |
Differentiator Group Mapping
The differentiator groups data is loaded from the DIFF_GROUP.csv
file or from RMFCS. These are for assortment
planning diff group hierarchy and are the same groups used in AIF Size Profile Science.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Diff Type |
W_PDS_DIFF_GRP_D |
DIFF_TYPE_ID |
W_RTL_DIFF_GRP_D. DIFF_TYPE |
Diff ID |
W_PDS_DIFF_GRP_D |
DIFF_ID |
W_RTL_DIFF_GRP_D. DIFF_ID |
Diff Desc |
W_PDS_DIFF_GRP_D |
DIFF_DESC |
W_DOMAIN_MEMBER_LKP_TL. DOMAIN_MEMBER_NAME |
Diff Group ID |
W_PDS_DIFF_GRP_D |
DIFF_GROUP_ID |
W_RTL_DIFF_GRP_D. DIFF_GROUP_ID |
Diff Group Desc |
W_PDS_DIFF_GRP_D |
DIFF_GROUP_DESC |
W_RTL_DIFF_GRP_D_TL. DIFF_GROUP_DESC |
Brand Mapping
The brand data is loaded from the PRODUCT.csv
file or from RMFCS. The product data load programs will
insert the brand information into the additional tables used below (as long as these tables are enabled during foundation
loads).
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Brand ID |
W_PDS_BRAND_D |
BRAND_NAME |
W_RTL_PRODUCT_BRAND_D. BRAND_ID |
Brand Desc |
W_PDS_BRAND_D |
BRAND_DESCRIPTION |
W_RTL_PRODUCT_BRAND_D_TL. BRAND_DESCR |
Replenishment Attribute Mapping
The replenishment attribute data is loaded from the PROD_LOC_REPL.csv
file or from RMFCS. This data is
not used by any planning solution in the default templates, but it is made available for customer extensions.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Item |
W_PDS_REPL_ATTR_IT_LC_D |
ITEM |
W_PRODUCT_D_RTL_TMP.PROD_IT_NUM |
Location |
W_PDS_REPL_ATTR_IT_LC_D |
LOCATION |
W_INT_ORG_D_RTL_TMP.ORG_NUM |
Food Stamp Flag |
W_PDS_REPL_ATTR_IT_LC_D |
FOOD_STAMP_IND |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR1_NAME |
Reward Eligible Flag |
W_PDS_REPL_ATTR_IT_LC_D |
REWARD_ELIGIBLE_IND |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR2_NAME |
Natl Brand Comp Item |
W_PDS_REPL_ATTR_IT_LC_D |
NATL_BRAND_COMP_ITEM |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR3_NAME |
Elect Mkt Clubs |
W_PDS_REPL_ATTR_IT_LC_D |
ELECT_MKT_CLUBS |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR4_NAME |
Store Reorderable Flag |
W_PDS_REPL_ATTR_IT_LC_D |
STORE_REORDERABLE_IND |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR5_NAME |
Manual Price Entry |
W_PDS_REPL_ATTR_IT_LC_D |
MANUAL_PRICE_ENTRY |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR6_NAME |
WIC Flag |
W_PDS_REPL_ATTR_IT_LC_D |
WIC_IND |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR7_NAME |
In Store Market Basket |
W_PDS_REPL_ATTR_IT_LC_D |
IN_STORE_MARKET_BASKET |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR8_NAME |
Returnable Flag |
W_PDS_REPL_ATTR_IT_LC_D |
RETURNABLE_IND |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR9_NAME |
Launch Date |
W_PDS_REPL_ATTR_IT_LC_D |
LAUNCH_DATE |
W_INVENTORY_PRODUCT_ATTR_D.INV_ATTR1_DATE |
Refundable Flag |
W_PDS_REPL_ATTR_IT_LC_D |
REFUNDABLE_IND |
W_INVENTORY_PRODUCT_ATTR_D.REFUNDABLE_IND |
Back Order Flag |
W_PDS_REPL_ATTR_IT_LC_D |
BACK_ORDER_IND |
W_INVENTORY_PRODUCT_ATTR_D.BACK_ORDER_IND |
Replenishment Supplier Num |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_SUPPLIER_NUM |
W_INVENTORY_PRODUCT_ATTR_D.REPL_SUPPLIER_NUM |
Replenishment Country Code |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_COUNTRY_CODE |
W_INVENTORY_PRODUCT_ATTR_D.REPL_COUNTRY_CODE |
Replenishment Review Cycle |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_REVIEW_CYCLE |
W_INVENTORY_PRODUCT_ATTR_D.REPL_REVIEW_CYCLE |
Replenishment Stock Cat |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_STOCK_CAT |
W_INVENTORY_PRODUCT_ATTR_D.REPL_STOCK_CAT |
Replenishment Source Wh |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_SOURCE_WH |
W_INVENTORY_PRODUCT_ATTR_D.REPL_SOURCE_WH |
Replenishment Activate Dt |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_ACTIVATE_DT |
W_INVENTORY_PRODUCT_ATTR_D.REPL_ACTIVATE_DT |
Replenishment Deactivate Dt |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_DEACTIVATE_DT |
NVL(W_INVENTORY_PRODUCT_ATTR_D.REPL_DEACTIVATE_DT ,'2100-01-01') |
Replenishment Pres Stock |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_PRES_STOCK |
W_INVENTORY_PRODUCT_ATTR_D.REPL_PRES_STOCK |
Replenishment Demo Stock |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_DEMO_STOCK |
W_INVENTORY_PRODUCT_ATTR_D.REPL_DEMO_STOCK |
Replenishment Min Stock |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_MIN_STOCK |
W_INVENTORY_PRODUCT_ATTR_D.REPL_MIN_STOCK |
Replenishment Max Stock |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_MAX_STOCK |
W_INVENTORY_PRODUCT_ATTR_D.REPL_MAX_STOCK |
Replenishment Service Level |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_SERVICE_LEVEL |
W_INVENTORY_PRODUCT_ATTR_D.REPL_SERVICE_LEVEL |
Replenishment Pickup Leadtime |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_PICKUP_LEADTIME |
W_INVENTORY_PRODUCT_ATTR_D.REPL_PICKUP_LEADTIME |
Replenishment Wh Leadtime |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_WH_LEADTIME |
W_INVENTORY_PRODUCT_ATTR_D.REPL_WH_LEADTIME |
Replenishment Active Flag |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_ACTIVE_FLAG |
CASE WHEN W_INVENTORY_PRODUCT_ATTR_D.REPL_ACTIVE_FLAG IS NULL THEN CASE WHEN NVL(W_INVENTORY_PRODUCT_ATTR_D.REPL_DEACTIVATE_DT ,'2100-01-01') > current_dt THEN ''Y'' ELSE ''N'' END ELSE W_INVENTORY_PRODUCT_ATTR_D.REPL_ACTIVE_FLAG END |
Exit Date |
W_PDS_REPL_ATTR_IT_LC_D |
EXIT_DATE |
W_INVENTORY_PRODUCT_ATTR_D.EXIT_DATE |
Promo Exclude Flg |
W_PDS_REPL_ATTR_IT_LC_D |
PROMO_EXCL_FLG |
W_INVENTORY_PRODUCT_ATTR_D.PROMO_EXCL_FLG |
Mkdn Exclude Flg |
W_PDS_REPL_ATTR_IT_LC_D |
MKDN_EXCL_FLG |
W_INVENTORY_PRODUCT_ATTR_D.MKDN_EXCL_FLG |
Replenishment Order Ctrl |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_ORDER_CTRL |
W_INVENTORY_PRODUCT_ATTR_D.REPL_ORDER_CTRL |
Replenishment Method |
W_PDS_REPL_ATTR_IT_LC_D |
REPL_METHOD |
W_INVENTORY_PRODUCT_ATTR_D.REPL_METHOD |
Incr Percent |
W_PDS_REPL_ATTR_IT_LC_D |
INCR_PCT |
W_INVENTORY_PRODUCT_ATTR_D.INCR_PCT |
Min Supply Days |
W_PDS_REPL_ATTR_IT_LC_D |
MIN_SUPPLY_DAYS |
W_INVENTORY_PRODUCT_ATTR_D.MIN_SUPPLY_DAYS |
Max Supply Days |
W_PDS_REPL_ATTR_IT_LC_D |
MAX_SUPPLY_DAYS |
W_INVENTORY_PRODUCT_ATTR_D.MAX_SUPPLY_DAYS |
Time Supply Horizon |
W_PDS_REPL_ATTR_IT_LC_D |
TIME_SUPPLY_HORIZON |
W_INVENTORY_PRODUCT_ATTR_D.TIME_SUPPLY_HORIZON |
Inv Selling Days |
W_PDS_REPL_ATTR_IT_LC_D |
INV_SELLING_DAYS |
W_INVENTORY_PRODUCT_ATTR_D.INV_SELLING_DAYS |
Lost Sales Factor |
W_PDS_REPL_ATTR_IT_LC_D |
LOST_SALES_FACTOR |
W_INVENTORY_PRODUCT_ATTR_D.LOST_SALES_FACTOR |
Reject Store Order Flag |
W_PDS_REPL_ATTR_IT_LC_D |
REJECT_STORE_ORD_IND |
W_INVENTORY_PRODUCT_ATTR_D.REJECT_STORE_ORD_IND |
Non Scaling Flag |
W_PDS_REPL_ATTR_IT_LC_D |
NON_SCALING_IND |
W_INVENTORY_PRODUCT_ATTR_D.NON_SCALING_IND |
Max Scale Value |
W_PDS_REPL_ATTR_IT_LC_D |
MAX_SCALE_VALUE |
W_INVENTORY_PRODUCT_ATTR_D.MAX_SCALE_VALUE |
Terminal Stock Qty |
W_PDS_REPL_ATTR_IT_LC_D |
TERMINAL_STOCK_QTY |
W_INVENTORY_PRODUCT_ATTR_D.TERMINAL_STOCK_QTY |
Season Id |
W_PDS_REPL_ATTR_IT_LC_D |
SEASON_ID |
W_INVENTORY_PRODUCT_ATTR_D.SEASON_ID |
Phase Id |
W_PDS_REPL_ATTR_IT_LC_D |
PHASE_ID |
W_INVENTORY_PRODUCT_ATTR_D.PHASE_ID |
Last Review Date |
W_PDS_REPL_ATTR_IT_LC_D |
LAST_REVIEW_DATE |
W_INVENTORY_PRODUCT_ATTR_D.LAST_REVIEW_DATE |
Next Review Date |
W_PDS_REPL_ATTR_IT_LC_D |
NEXT_REVIEW_DATE |
W_INVENTORY_PRODUCT_ATTR_D.NEXT_REVIEW_DATE |
Unit Tolerance |
W_PDS_REPL_ATTR_IT_LC_D |
UNIT_TOLERANCE |
W_INVENTORY_PRODUCT_ATTR_D.UNIT_TOLERANCE |
Percent Tolerance |
W_PDS_REPL_ATTR_IT_LC_D |
PCT_TOLERANCE |
W_INVENTORY_PRODUCT_ATTR_D.PCT_TOLERANCE |
Use Tolerance Flag |
W_PDS_REPL_ATTR_IT_LC_D |
USE_TOLERANCE_IND |
W_INVENTORY_PRODUCT_ATTR_D.USE_TOLERANCE_IND |
Last Delivery Date |
W_PDS_REPL_ATTR_IT_LC_D |
LAST_DELIVERY_DATE |
W_INVENTORY_PRODUCT_ATTR_D.LAST_DELIVERY_DATE |
Next Delivery Date |
W_PDS_REPL_ATTR_IT_LC_D |
NEXT_DELIVERY_DATE |
W_INVENTORY_PRODUCT_ATTR_D.NEXT_DELIVERY_DATE |
MBR Order Qty |
W_PDS_REPL_ATTR_IT_LC_D |
MBR_ORDER_QTY |
W_INVENTORY_PRODUCT_ATTR_D.MBR_ORDER_QTY |
Adj Pickup Lead Time |
W_PDS_REPL_ATTR_IT_LC_D |
ADJ_PICKUP_LEAD_TIME |
W_INVENTORY_PRODUCT_ATTR_D.ADJ_PICKUP_LEAD_TIME |
Adj Supp Lead Time |
W_PDS_REPL_ATTR_IT_LC_D |
ADJ_SUPP_LEAD_TIME |
W_INVENTORY_PRODUCT_ATTR_D.ADJ_SUPP_LEAD_TIME |
Tsf Po Link No |
W_PDS_REPL_ATTR_IT_LC_D |
TSF_PO_LINK_NO |
W_INVENTORY_PRODUCT_ATTR_D.TSF_PO_LINK_NO |
Last ROQ |
W_PDS_REPL_ATTR_IT_LC_D |
LAST_ROQ |
W_INVENTORY_PRODUCT_ATTR_D.LAST_ROQ |
Store Ord Multiple |
W_PDS_REPL_ATTR_IT_LC_D |
STORE_ORD_MULT |
W_INVENTORY_PRODUCT_ATTR_D.STORE_ORD_MULT |
Unit Cost |
W_PDS_REPL_ATTR_IT_LC_D |
UNIT_COST |
W_INVENTORY_PRODUCT_ATTR_D.UNIT_COST |
Supplier Lead Time |
W_PDS_REPL_ATTR_IT_LC_D |
SUPP_LEAD_TIME |
W_INVENTORY_PRODUCT_ATTR_D.SUPP_LEAD_TIME |
Inner Pack Size |
W_PDS_REPL_ATTR_IT_LC_D |
INNER_PACK_SIZE |
W_INVENTORY_PRODUCT_ATTR_D.INNER_PACK_SIZE |
Supplier Pack Size |
W_PDS_REPL_ATTR_IT_LC_D |
SUPP_PACK_SIZE |
W_INVENTORY_PRODUCT_ATTR_D.SUPP_PACK_SIZE |
Tier |
W_PDS_REPL_ATTR_IT_LC_D |
TIER |
W_INVENTORY_PRODUCT_ATTR_D.TIER |
Height |
W_PDS_REPL_ATTR_IT_LC_D |
HEIGHT |
W_INVENTORY_PRODUCT_ATTR_D.HEIGHT |
Round Lvl |
W_PDS_REPL_ATTR_IT_LC_D |
ROUND_LVL |
W_INVENTORY_PRODUCT_ATTR_D.ROUND_LVL |
Round To Inner Percent |
W_PDS_REPL_ATTR_IT_LC_D |
ROUND_TO_INNER_PCT |
W_INVENTORY_PRODUCT_ATTR_D.ROUND_TO_INNER_PCT |
Round To Case Percent |
W_PDS_REPL_ATTR_IT_LC_D |
ROUND_TO_CASE_PCT |
W_INVENTORY_PRODUCT_ATTR_D.ROUND_TO_CASE_PCT |
Round To Layer Percent |
W_PDS_REPL_ATTR_IT_LC_D |
ROUND_TO_LAYER_PCT |
W_INVENTORY_PRODUCT_ATTR_D.ROUND_TO_LAYER_PCT |
Round To Pallet Percent |
W_PDS_REPL_ATTR_IT_LC_D |
ROUND_TO_PALLET_PCT |
W_INVENTORY_PRODUCT_ATTR_D.ROUND_TO_PALLET_PCT |
Service Level Type |
W_PDS_REPL_ATTR_IT_LC_D |
SERVICE_LEVEL_TYPE |
W_INVENTORY_PRODUCT_ATTR_D.SERVICE_LEVEL_TYPE |
Tsf Zero SOH Flag |
W_PDS_REPL_ATTR_IT_LC_D |
TSF_ZERO_SOH_IND |
W_INVENTORY_PRODUCT_ATTR_D.TSF_ZERO_SOH_IND |
Multiple Runs Per Day Flag |
W_PDS_REPL_ATTR_IT_LC_D |
MULT_RUNS_PER_DAY_IND |
W_INVENTORY_PRODUCT_ATTR_D.MULT_RUNS_PER_DAY_IND |
Add Lead Time Flag |
W_PDS_REPL_ATTR_IT_LC_D |
ADD_LEAD_TIME_IND |
W_INVENTORY_PRODUCT_ATTR_D.ADD_LEAD_TIME_IND |
Deposit Code |
W_PDS_REPL_ATTR_IT_LC_D |
DEPOSIT_CODE |
W_INVENTORY_PRODUCT_ATTR_D.DEPOSIT_CODE |
Proportional Tare Percent |
W_PDS_REPL_ATTR_IT_LC_D |
PROPORTIONAL_TARE_PCT |
W_INVENTORY_PRODUCT_ATTR_D.PROPORTIONAL_TARE_PCT |
Fixed Tare Value |
W_PDS_REPL_ATTR_IT_LC_D |
FIXED_TARE_VALUE |
W_INVENTORY_PRODUCT_ATTR_D.FIXED_TARE_VALUE |
Fixed Tare UOM |
W_PDS_REPL_ATTR_IT_LC_D |
FIXED_TARE_UOM |
W_INVENTORY_PRODUCT_ATTR_D.FIXED_TARE_UOM |
Return Policy |
W_PDS_REPL_ATTR_IT_LC_D |
RETURN_POLICY |
W_INVENTORY_PRODUCT_ATTR_D.RETURN_POLICY |
Stop Sale Flag |
W_PDS_REPL_ATTR_IT_LC_D |
STOP_SALE_IND |
W_INVENTORY_PRODUCT_ATTR_D.STOP_SALE_IND |
Report Code |
W_PDS_REPL_ATTR_IT_LC_D |
REPORT_CODE |
W_INVENTORY_PRODUCT_ATTR_D.REPORT_CODE |
Reference Date Type For Exit |
W_PDS_REPL_ATTR_IT_LC_D |
REFERENCE_DATE_TYPE_FOR_EXIT |
W_INVENTORY_PRODUCT_ATTR_D.REFERENCE_DATE_TYPE_FOR_EXIT |
Weeks To Exit |
W_PDS_REPL_ATTR_IT_LC_D |
WEEKS_TO_EXIT |
W_INVENTORY_PRODUCT_ATTR_D.WEEKS_TO_EXIT |
Optimize Flag |
W_PDS_REPL_ATTR_IT_LC_D |
OPTIMIZE_IND |
W_INVENTORY_PRODUCT_ATTR_D.OPTIMIZE_IND |
Supplier Mapping
The supplier data is loaded from the PRODUCT.csv
file or from RMFCS. The product data load programs will
insert the supplier information into the additional tables used below (as long as these tables are enabled during foundation
loads).
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Supplier ID |
W_PDS_SUPPLIER_D |
SUPPLIER |
W_PARTY_ORG_D .SUPPLIER_NUM |
Supplier Desc |
W_PDS_SUPPLIER_D |
SUP_NAME |
W_PARTY_ORG_D .ORG_NAME |
Sales Mapping
Data for sales is loaded from the SALES.csv
file or from RMFCS (Sales Audit). The primary RI table is
the week-level aggregate generated by the historical load process. All data mappings in this area are split out by retail
type. Any measure having reg/pro/clr
in the name are being filtered on that retail type code as part of the
export. When you provide input data to RAP, you specify the retail type code as R
, P
, or C
, and those values are used here to determine the output. A custom 4th option (using type code O
for Other) is also allowed, as long as you extend the W_XACT_TYPE_D
dimension in RI to have the
extra type code. Other sales are only included in the Total Sales measures in the PDS export.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Gross Reg Sales Units |
W_PDS_SLS_IT_LC_WK_A |
SALES_REG_UNITS |
W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY |
Gross Pro Sales Units |
W_PDS_SLS_IT_LC_WK_A |
SALES_PRO_UNITS |
W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY |
Gross Clr Sales Units |
W_PDS_SLS_IT_LC_WK_A |
SALES_CLR_UNITS |
W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY |
Gross Reg Sales Cost |
W_PDS_SLS_IT_LC_WK_A |
SALES_REG_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT) |
Gross Pro Sales Cost |
W_PDS_SLS_IT_LC_WK_A |
SALES_PRO_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT) |
Gross Clr Sales Cost |
W_PDS_SLS_IT_LC_WK_A |
SALES_CLR_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT) |
Gross Reg Sales Retail |
W_PDS_SLS_IT_LC_WK_A |
SALES_REG_RETAIL |
W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT |
Gross Pro Sales Retail |
W_PDS_SLS_IT_LC_WK_A |
SALES_PRO_RETAIL |
W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT |
Gross Clr Sales Retail |
W_PDS_SLS_IT_LC_WK_A |
SALES_CLR_RETAIL |
W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT |
Gross Reg Sales Tax |
W_PDS_SLS_IT_LC_WK_A |
SALES_REG_TAX |
W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT |
Gross Pro Sales Tax |
W_PDS_SLS_IT_LC_WK_A |
SALES_PRO_TAX |
W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT |
Gross Clr Sales Tax |
W_PDS_SLS_IT_LC_WK_A |
SALES_CLR_TAX |
W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT |
Net Reg Sales Units |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_REG_UNITS |
(W_RTL_SLS_IT_LC_WK_A.SLS_QTY-RET_QTY) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY-RETPK_QTY) |
Net Pro Sales Units |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_PRO_UNITS |
(W_RTL_SLS_IT_LC_WK_A.SLS_QTY-RET_QTY) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY-RETPK_QTY) |
Net Clr Sales Units |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_CLR_UNITS |
(W_RTL_SLS_IT_LC_WK_A.SLS_QTY-RET_QTY) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY-RETPK_QTY) |
Net Reg Sales Cost |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_REG_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_PROFIT_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_PROF_AMT-RETPK_PROF_AMT) |
Net Pro Sales Cost |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_PRO_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_PROFIT_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_PROF_AMT-RETPK_PROF_AMT) |
Net Clr Sales Cost |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_CLR_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_PROFIT_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_PROF_AMT-RETPK_PROF_AMT) |
Net Reg Sales Retail |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_REG_RETAIL |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) |
Net Pro Sales Retail |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_PRO_RETAIL |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) |
Net Clr Sales Retail |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_CLR_RETAIL |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) |
Net Reg Sales Retail Excluding VAT |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_REG_RETAIL_VAT_EXCL |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT) |
Net Pro Sales Retail Excluding VAT |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_PRO_RETAIL_VAT_EXCL |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT) |
Net Clr Sales Retail Excluding VAT |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_CLR_RETAIL_VAT_EXCL |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) +(W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT) |
Net Reg Sales Tax |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_REG_TAX |
(W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT) |
Net Pro Sales Tax |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_PRO_TAX |
(W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT) |
Net Clr Sales Tax |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_CLR_TAX |
(W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT) |
Returns Reg Units |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_REG_UNITS |
W_RTL_SLS_IT_LC_WK_A.RET_QTY + W_RTL_SLSPK_IT_LC_WK_A.RETPK_QTY |
Returns Pro Units |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_PRO_UNITS |
W_RTL_SLS_IT_LC_WK_A.RET_QTY + W_RTL_SLSPK_IT_LC_WK_A.RETPK_QTY |
Returns Clr Units |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_CLR_UNITS |
W_RTL_SLS_IT_LC_WK_A.RET_QTY + W_RTL_SLSPK_IT_LC_WK_A.RETPK_QTY |
Returns Reg Cost |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_REG_COST |
(W_RTL_SLS_IT_LC_WK_A.RET_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT-RETPK_PROF_AMT) |
Returns Pro Cost |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_PRO_COST |
(W_RTL_SLS_IT_LC_WK_A.RET_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT-RETPK_PROF_AMT) |
Returns Clr Cost |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_CLR_COST |
(W_RTL_SLS_IT_LC_WK_A.RET_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT-RETPK_PROF_AMT) |
Returns Reg Retail |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_REG_RETAIL |
W_RTL_SLS_IT_LC_WK_A.RET_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT |
Returns Pro Retail |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_PRO_RETAIL |
W_RTL_SLS_IT_LC_WK_A.RET_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT |
Returns Clr Retail |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_CLR_RETAIL |
W_RTL_SLS_IT_LC_WK_A.RET_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_AMT |
Returns Reg Tax |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_REG_TAX |
W_RTL_SLS_IT_LC_WK_A.RET_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_TAX_AMT |
Returns Pro Tax |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_PRO_TAX |
W_RTL_SLS_IT_LC_WK_A.RET_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_TAX_AMT |
Returns Clr Tax |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_CLR_TAX |
W_RTL_SLS_IT_LC_WK_A.RET_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.RETPK_TAX_AMT |
Total Gross Sales Units |
W_PDS_SLS_IT_LC_WK_A |
SALES_TOTAL_UNITS |
W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY |
Total Gross Sales Cost |
W_PDS_SLS_IT_LC_WK_A |
SALES_TOTAL_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT) |
Total Gross Sales Retail |
W_PDS_SLS_IT_LC_WK_A |
SALES_TOTAL_RETAIL |
W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT |
Total Gross Sales Tax |
W_PDS_SLS_IT_LC_WK_A |
SALES_TOTAL_TAX |
W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT |
Total Net Sales Units |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_TOTAL_UNITS |
(W_RTL_SLS_IT_LC_WK_A.SLS_QTY-RET_QTY) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY-RETPK_QTY) |
Total Net Sales Cost |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_TOTAL_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) - (W_RTL_SLS_IT_LC_WK_A.SLS_PROFIT_AMT-RET_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) - (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_PROF_AMT-RETPK_PROF_AMT) |
Total Net Sales Retail |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_TOTAL_RETAIL |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-RET_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-RETPK_AMT) |
Total Net Sales Tax |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_TOTAL_TAX |
(W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT-RET_TAX_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT-RETPK_TAX_AMT) |
Gross Sales Mapping
A separate sales aggregate is also provided for Demand Forecasting (RDF), which filters and aggregates the sales differently from the base extract for the other Planning applications. This export includes only gross sales and has a single set of measure columns with a separate field for the retail type (reg/pro/clr). The data is filtered to include only sales for non-warehouse locations.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Gross Sales Units |
W_PDS_GRS_SLS_IT_LC_WK_A |
SALES_UNITS |
W_RTL_SLS_IT_LC_WK_A.SLS_QTY + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_QTY |
Gross Sales Cost |
W_PDS_GRS_SLS_IT_LC_WK_A |
SALES_COST |
(W_RTL_SLS_IT_LC_WK_A.SLS_AMT-SLS_PROFIT_AMT) + (W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT-SLSPK_PROF_AMT) |
Gross Sales Retail |
W_PDS_GRS_SLS_IT_LC_WK_A |
SALES_RETAIL |
W_RTL_SLS_IT_LC_WK_A.SLS_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_AMT |
Gross Sales Tax |
W_PDS_GRS_SLS_IT_LC_WK_A |
SALES_TAX |
W_RTL_SLS_IT_LC_WK_A.SLS_TAX_AMT + W_RTL_SLSPK_IT_LC_WK_A.SLSPK_TAX_AMT |
Inventory Position Mapping
Data is loaded from the INVENTORY.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Inventory Units |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_UNITS |
W_RTL_INV_IT_LC_WK_A.INV_SOH_QTY + INV_IN_TRAN_QTY |
Inventory Cost |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_COST |
W_RTL_INV_IT_LC_WK_A.INV_SOH_COST_AMT + INV_IN_TRAN_COST_AMT |
Inventory Retail |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_RETAIL |
W_RTL_INV_IT_LC_WK_A.INV_SOH_RTL_AMT + INV_IN_TRAN_RTL_AMT |
Unit Cost |
W_PDS_INV_IT_LC_WK_A |
UNIT_COST |
W_RTL_INV_IT_LC_WK_A.INV_UNIT_COST_AMT |
Average Cost |
W_PDS_INV_IT_LC_WK_A |
AV_COST |
W_RTL_INV_IT_LC_WK_A.INV_AVG_COST_AMT |
Unit Retail |
W_PDS_INV_IT_LC_WK_A |
UNIT_RETAIL |
W_RTL_INV_IT_LC_WK_A.INV_UNIT_RTL_AMT |
Custom Measures 1 - 20 |
W_PDS_INV_IT_LC_WK_A |
FLEX1_NUM_VALUE through FLEX20_NUM_VALUE |
W_RTL_INV_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_INV_IT_LC_WK_A.FLEX20_NUM_VALUE |
On Order Mapping
Data is loaded from the ORDER_HEAD.csv
and ORDER_DETAIL.csv
files or from RMFCS. Purchase
order data is transformed from the raw order line details into a forward-looking total on-order amount based on the OTB end-of-week
date on the order. Data is also filtered to remove orders not flagged as Include On Order.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
On Order Units |
W_PDS_PO_ONORD_IT_LC_WK_A |
ON_ORDER_UNITS |
W_RTL_PO_ONORD_IT_LC_DY_F.PO_ONORD_QTY |
On Order Cost |
W_PDS_PO_ONORD_IT_LC_WK_A |
ON_ORDER_COST |
W_RTL_PO_ONORD_IT_LC_DY_F.PO_ONORD_COST_AMT_LCL |
On Order Retail |
W_PDS_PO_ONORD_IT_LC_WK_A |
ON_ORDER_RETAIL |
W_RTL_PO_ONORD_IT_LC_DY_F.PO_ONORD_RTL_AMT_LCL |
Markdown Mapping
Data is loaded from the MARKDOWN.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Regular Markdown |
W_PDS_MKDN_IT_LC_WK_A |
REG_MARKDOWN_RETAIL |
W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = R |
Promotion Markdown (Regular) |
W_PDS_MKDN_IT_LC_WK_A |
PROMO_MARKDOWN_RETAIL_REG |
W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = P and CLEARANCE_FLG = N |
Promotion Markdown (Clearance) |
W_PDS_MKDN_IT_LC_WK_A |
PROMO_MARKDOWN_RETAIL_CLEAR |
W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = P and CLEARANCE_FLG = Y |
Clearance Markdown |
W_PDS_MKDN_IT_LC_WK_A |
CLEAR_MARKDOWN_RETAIL |
W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = C |
Markup |
W_PDS_MKDN_IT_LC_WK_A |
MARKUP |
W_RTL_MKDN_IT_LC_WK_A.MKUP_AMT where retail type = R |
Markup Cancel |
W_PDS_MKDN_IT_LC_WK_A |
MARKUP_CANCEL |
W_RTL_MKDN_IT_LC_WK_A.MKUP_CAN_AMT |
Markdown Cancel |
W_PDS_MKDN_IT_LC_WK_A |
MARKDOWN_CANCEL |
W_RTL_MKDN_IT_LC_WK_A.MKDN_CAN_AMT |
Intercompany Markup |
W_PDS_MKDN_IT_LC_WK_A |
INTERCOMPANY_MARKUP |
W_RTL_MKDN_IT_LC_WK_A.MKUP_AMT where retail type = I |
Intercompany Markdown |
W_PDS_MKDN_IT_LC_WK_A |
INTERCOMPANY_MARKDOWN |
W_RTL_MKDN_IT_LC_WK_A.MKDN_AMT where retail type = I |
Wholesale/Franchise Mapping
Data is loaded from the SALES_WF.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Franchise Sales Units |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_SALES_UNITS |
W_RTL_SLSWF_IT_LC_WK_A.SLSWF_QTY |
Franchise Sales Cost |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_SALES_COST |
W_RTL_SLSWF_IT_LC_WK_A.SLSWF_ACQ_COST_AMT |
Franchise Sales Retail |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_SALES_RETAIL |
W_RTL_SLSWF_IT_LC_WK_A.SLSWF_AMT |
Franchise Sales Tax |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_SALES_TAX |
W_RTL_SLSWF_IT_LC_WK_A.SLSWF_TAX_AMT |
Franchise Returns Units |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_UNITS |
W_RTL_SLSWF_IT_LC_WK_A.RETWF_QTY |
Franchise Returns Cost |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_COST |
W_RTL_SLSWF_IT_LC_WK_A.RETWF_ACQ_COST_AMT |
Franchise Returns Retail |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_RETAIL |
W_RTL_SLSWF_IT_LC_WK_A.RETWF_AMT |
Franchise Returns Tax |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_TAX |
W_RTL_SLSWF_IT_LC_WK_A.RETWF_TAX_AMT |
Franchise Restocking Fee |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RESTOCK_FEE |
W_RTL_SLSWF_IT_LC_WK_A.RETWF_RSTK_FEE_AMT |
Franchise Markdown |
W_PDS_SLSWF_IT_LC_WK_A |
WF_MARKDOWN_RETAIL |
W_RTL_SLSWF_IT_LC_WK_A.SLSWF_MKDN_AMT - RETWF_MKDN_AMT |
Franchise Markup |
W_PDS_SLSWF_IT_LC_WK_A |
WF_MARKUP_RETAIL |
W_RTL_SLSWF_IT_LC_WK_A.SLSWF_MKUP_AMT - RETWF_MKUP_AMT |
Inventory Adjustments Mapping
Data is loaded from the ADJUSTMENT.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Shrink Units |
W_PDS_INVADJ_IT_LC_WK_A |
SHRINK_UNITS |
W_RTL_INVADJ_IT_LC_WK_A.INVADJ_QTY where adj type = 22 |
Shrink Cost |
W_PDS_INVADJ_IT_LC_WK_A |
SHRINK_COST |
W_RTL_INVADJ_IT_LC_WK_A.INVADJ_COST_AMT where adj type = 22 |
Shrink Retail |
W_PDS_INVADJ_IT_LC_WK_A |
SHRINK_RETAIL |
W_RTL_INVADJ_IT_LC_WK_A.INVADJ_RTL_AMT where adj type = 22 |
Non-Shrink Adjustments Units |
W_PDS_INVADJ_IT_LC_WK_A |
NON_SHRINK_ADJ_UNITS |
W_RTL_INVADJ_IT_LC_WK_A.INVADJ_QTY where adj type = 23 |
Non-Shrink Adjustments Cost |
W_PDS_INVADJ_IT_LC_WK_A |
NON_SHRINK_ADJ_COST |
W_RTL_INVADJ_IT_LC_WK_A.INVADJ_COST_AMT where adj type = 23 |
Non-Shrink Adjustments Retail |
W_PDS_INVADJ_IT_LC_WK_A |
NON_SHRINK_ADJ_RETAIL |
W_RTL_INVADJ_IT_LC_WK_A.INVADJ_RTL_AMT where adj type = 23 |
Inventory Receipts Mapping
Data is loaded from the RECEIPT.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
PO Receipt Units |
W_PDS_INVRC_IT_LC_WK_A |
PO_RECEIPT_UNITS |
W_RTL_INVRC_IT_LC_WK_A.INVRC_QTY where rcpt type code = 20 |
PO Receipt Cost |
W_PDS_INVRC_IT_LC_WK_A |
PO_RECEIPT_COST |
W_RTL_INVRC_IT_LC_WK_A.INVRC_COST_AMT where rcpt type code = 20 |
PO Receipt Retail |
W_PDS_INVRC_IT_LC_WK_A |
PO_RECEIPT_RETAIL |
W_RTL_INVRC_IT_LC_WK_A.INVRC_RTL_AMT where rcpt type code = 20 |
Transfer/Allocation Receipt Units |
W_PDS_INVRC_IT_LC_WK_A |
TSF_RECEIPT_UNITS |
W_RTL_INVRC_IT_LC_WK_A.INVRC_QTY where rcpt type code = 44~A or 44~T |
Transfer/Allocation Receipt Cost |
W_PDS_INVRC_IT_LC_WK_A |
TSF_RECEIPT_COST |
W_RTL_INVRC_IT_LC_WK_A.INVRC_COST_AMT where rcpt type code = 44~A or 44~T |
Transfer/Allocation Receipt Retail |
W_PDS_INVRC_IT_LC_WK_A |
TSF_RECEIPT_RETAIL |
W_RTL_INVRC_IT_LC_WK_A.INVRC_RTL_AMT where rcpt type code = 44~A or 44~T |
PO Receipt Custom Measures 1 - 20 |
W_PDS_INVRC_IT_LC_WK_A |
PO_FLEX1_NUM_VALUE through PO_FLEX20_NUM_VALUE |
W_RTL_INVRC_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_INVRC_IT_LC_WK_A.FLEX20_NUM_VALUE where rcpt type code = 20 |
Transfer Receipt Custom Measures 1 - 20 |
W_PDS_INVRC_IT_LC_WK_A |
TSF_FLEX1_NUM_VALUE through TSF_FLEX20_NUM_VALUE |
W_RTL_INVRC_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_INVRC_IT_LC_WK_A.FLEX20_NUM_VALUE where rcpt type code = 44~A or 44~T |
Inventory Transfers Mapping
Data is loaded from the TRANSFER.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Transfer Type |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_TYPE |
W_XACT_TYPE_D.W_XACT_TYPE_CODE in (N,B,I) (for normal/book/intercompany tsfs) |
Transfer Inbound Units |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_UNITS |
W_RTL_INVTSF_IT_LC_WK_A.TSF_TO_LOC_QTY |
Transfer Inbound Cost |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_COST |
W_RTL_INVTSF_IT_LC_WK_A.TSF_TO_LOC_COST_AMT |
Transfer Inbound Retail |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_RETAIL |
W_RTL_INVTSF_IT_LC_WK_A.TSF_TO_LOC_RTL_AMT |
Transfer Outbound Units |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_UNITS |
W_RTL_INVTSF_IT_LC_WK_A.TSF_FROM_LOC_QTY |
Transfer Outbound Cost |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_COST |
W_RTL_INVTSF_IT_LC_WK_A.TSF_FROM_LOC_COST_AMT |
Transfer Outbound Retail |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_RETAIL |
W_RTL_INVTSF_IT_LC_WK_A.TSF_FROM_LOC_RTL_AMT |
Inventory RTVs Mapping
Data is loaded from the RTV.csv
file or from RMFCS. The primary RI table is the week-level aggregate generated
by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
RTV Units |
W_PDS_INVRTV_IT_LC_WK_A |
RTV_UNITS |
W_RTL_INVRTV_IT_LC_WK_A.RTV_QTY |
RTV Cost |
W_PDS_INVRTV_IT_LC_WK_A |
RTV_COST |
W_RTL_INVRTV_IT_LC_WK_A.RTV_COST_AMT |
RTV Retail |
W_PDS_INVRTV_IT_LC_WK_A |
RTV_RETAIL |
W_RTL_INVRTV_IT_LC_WK_A.RTV_RTL_AMT |
RTV Restocking Fee |
W_PDS_INVRTV_IT_LC_WK_A |
RTV_RESTOCK_FEE |
W_RTL_INVRTV_IT_LC_WK_A.RTV_RSTCK_COST_AMT |
Inventory Reclass Mapping
Data is loaded from the INV_RECLASS.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Reclass In Units |
W_PDS_INVRECLASS_IT_LC_WK_A |
RECLASS_IN_UNITS |
W_RTL_INVRCL_IT_LC_WK_A.RCL_TO_LOC_QTY |
Reclass In Cost |
W_PDS_INVRECLASS_IT_LC_WK_A |
RECLASS_IN_COST |
W_RTL_INVRCL_IT_LC_WK_A.RCL_TO_LOC_COST_AMT |
Reclass In Retail |
W_PDS_INVRECLASS_IT_LC_WK_A |
RECLASS_IN_RETAIL |
W_RTL_INVRCL_IT_LC_WK_A.RCL_TO_LOC_RTL_AMT |
Reclass Out Units |
W_PDS_INVRECLASS_IT_LC_WK_A |
RECLASS_OUT_UNITS |
W_RTL_INVRCL_IT_LC_WK_A.RCL_FROM_LOC_QTY |
Reclass Out Cost |
W_PDS_INVRECLASS_IT_LC_WK_A |
RECLASS_OUT_COST |
W_RTL_INVRCL_IT_LC_WK_A.RCL_FROM_LOC_COST_AMT |
Reclass Out Retail |
W_PDS_INVRECLASS_IT_LC_WK_A |
RECLASS_OUT_RETAIL |
W_RTL_INVRCL_IT_LC_WK_A.RCL_FROM_LOC_RTL_AMT |
Deal Income Mapping
Data is loaded from the DEAL_INCOME.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Deal Income Sales Based |
W_PDS_DEALINC_IT_LC_WK_A |
DEAL_INCOME_SALES |
W_RTL_DEALINC_IT_LC_WK_A.DEAL_SLS_COST_AMT |
Deal Income Purchases Based |
W_PDS_DEALINC_IT_LC_WK_A |
DEAL_INCOME_PURCHASES |
W_RTL_DEALINC_IT_LC_WK_A.DEAL_PRCH_COST_AMT |
Intercompany Margin Mapping
Data is loaded from the IC_MARGIN.csv
file or from RMFCS. The primary RI table is the week-level aggregate
generated by the historical load process.
Measure | Target Table | Target Column | RI Data Source |
---|---|---|---|
Intercompany Margin |
W_PDS_ICM_IT_LC_WK_A |
INTERCOMPANY_MARGIN |
W_RTL_ICM_IT_LC_WK_A.IC_MARGIN_AMT |
Transformations in Planning
Planning applications allow the loading of fact data at the load intersection level (such as Item and Location) but uses the data within the application at an aggregated level (called the base intersection). In MFP, though all facts are loaded at the item level, it only needs data to plan at the Subclass level. The data will be aggregated from item level to subclass level for all the configured metrics to be directly used by the application. During re-classifications (such as when one item is moved from one subclass to another subclass), after the new hierarchy details are imported into MFP it also triggers re-classification of all fact data. Re-aggregation of fact data then happens only for shared facts having different load and base intersections.
In Planning applications, fact data is grouped as dynamic fact groups based mainly on the base intersection and interface
details, as defined in the Data Interface of the Application Configuration. RI and AI Foundation use a relational data model,
whereas Planning applications internally use a hierarchical data model. Data from RAP, stored using the relational data model,
needs to be transformed to be loaded into Planning applications. A similar approach is necessary for data coming out of planning
applications to AI Foundation or external sources. These data transformations happen as part of the interfaces defined in interface.cfg
(Interfaces Configuration File), which is a mapping of dimensions and measures from Planning applications
to external system table columns. Refer to the application-specific Implementation Guides for more information about Planning
Data Interfaces.