6 Data Processing and 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.
Data Warehouse Aggregate Tables
The RAP data warehouse (used to maintain all foundation data for the RAP applications) 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, the data warehouse 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 the foundation data warehouse for all our applications to use.
Table Structures
If you are currently loading data into the data warehouse using AIF DATA batch jobs 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 data warehouse tables start with a “W” to denote a core data warehouse object. |
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 the data warehouse 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 Data Warehouse 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 the data warehouse 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 data warehouse facts used by RAP applications.
Table 6-2 Foundation Data Transformations
Transformation | Explanation |
---|---|
Currency Conversion |
As part of the nightly batch, AIF DATA jobs 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 data model maintains separate columns for both local and primary currency amounts for RI and AIF usage. |
Tax Handling |
The data model 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) and in the data extraction jobs (RDE). 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. You may optionally remove VAT from all data using configuration changes. |
Transaction Date Usage |
All fact data coming into the system includes a transaction date on the record. AIF DATA jobs aggregate 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 AIF DATA will not send any fact 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 |
The system accepts 3 types of inventory adjustments using the codes |
Inventory Receipt Types |
The system accepts 3 types of inventory receipts using the codes |
Inventory Transfer Types |
The system 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 foundation data warehouse 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 data warehouse 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 the data warehouse 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 | 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 |
Image Filename |
W_PDS_PRODUCT_D |
PRODUCT_IMAGE_NAME |
W_PRODUCT_ATTR_D. PRODUCT_IMAGE_NAME |
Image Address |
W_PDS_PRODUCT_D |
PRODUCT_IMAGE_ADDR |
W_PRODUCT_ATTR_D. PRODUCT_IMAGE_ADDR |
Attr Image Filename |
W_PDS_PRODUCT_D |
PRODUCT_ATTR_IMAGE_NAME |
W_PRODUCT_ATTR_D. PRODUCT_ATTR_IMAGE_NAME |
Attr Image Address |
W_PDS_PRODUCT_D |
PRODUCT_ATTR_IMAGE_ADDR |
W_PRODUCT_ATTR_D. PRODUCT_ATTR_IMAGE_ADDR |
Organization Mapping
The location dimension and organization hierarchy data is loaded mainly from the
ORGANIZATION.csv
file or from RMFCS. The primary data warehouse
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 the data warehouse 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. The mappings are separated by store and
warehouse, when different logic is used based on the location type. Only virtual
warehouses are exported here, physical warehouse records are excluded from the
export.
Measure | Target Table | Target Column | 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 must be a fiscal calendar (such as 4-4-5 or 4-5-4). The primary data
warehouse table having day-level data is W_MCAL_DAY_D
. AIF DATA jobs
automatically generate the calendar using the start/end dates for the fiscal periods in
the input file. AIF DATA jobs also generate an internal Gregorian calendar at the same
time the fiscal calendar is loaded, and this data is exported alongside the fiscal
calendar for extensions and customizations.
Measure | Target Table | Target Column | 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 |
Gregorian Day of Week |
W_PDS_CALENDAR_D |
GREG_DAY_OF_WEEK |
W_MCAL_DAY_D.CAL_DAY_OF_THE_WEEK_CODE |
Gregorian Day of Month |
W_PDS_CALENDAR_D |
GREG_DAY_OF_MONTH |
W_MCAL_DAY_D.CAL_DAY_OF_MONTH |
Gregorian Day of Year |
W_PDS_CALENDAR_D |
GREG_DAY_OF_YEAR |
W_MCAL_DAY_D.CAL_DAY_OF_YEAR |
Gregorian Week ID |
W_PDS_CALENDAR_D |
GREG_WEEK_ID |
W_MCAL_DAY_D.CAL_WEEK_WID |
Gregorian Week Ending Date |
W_PDS_CALENDAR_D |
GREG_WEEK_END_DT |
W_MCAL_DAY_D.CAL_WEEK_END_DT |
Gregorian Week Name |
W_PDS_CALENDAR_D |
GREG_WEEK_NAME |
W_MCAL_DAY_D.CAL_WEEK_NAME |
GregorianMonth ID |
W_PDS_CALENDAR_D |
GREG_MONTH_ID |
W_MCAL_DAY_D.CAL_MONTH_WID |
Gregorian Month of Year |
W_PDS_CALENDAR_D |
GREG_MONTH |
W_MCAL_DAY_D.CAL_MONTH |
Gregorian Month Ending Date |
W_PDS_CALENDAR_D |
GREG_MONTH_END_DT |
W_MCAL_DAY_D.CAL_MONTH_END_DT |
Gregorian Month Name |
W_PDS_CALENDAR_D |
GREG_MONTH_NAME |
W_MCAL_DAY_D.W_CAL_MONTH_CODE |
Gregorian Quarter ID |
W_PDS_CALENDAR_D |
GREG_QTR_ID |
W_MCAL_DAY_D.CAL_QTR_WID |
Gregorian Quarter of Year |
W_PDS_CALENDAR_D |
GREG_QTR |
W_MCAL_DAY_D.CAL_QTR |
Gregorian Quarter Ending Date |
W_PDS_CALENDAR_D |
GREG_QTR_END_DT |
W_MCAL_DAY_D.CAL_QTR_END_DT |
Gregorian Quarter Name |
W_PDS_CALENDAR_D |
GREG_QTR_NAME |
W_MCAL_DAY_D.CAL_QTR_NAME |
Gregorian Half of Year |
W_PDS_CALENDAR_D |
GREG_HALF |
W_MCAL_DAY_D.CAL_HALF |
Gregorian Half Name |
W_PDS_CALENDAR_D |
GREG_HALF_NAME |
W_MCAL_DAY_D.CAL_HALF_NAME |
Gregorian Year |
W_PDS_CALENDAR_D |
GREG_YEAR |
W_MCAL_DAY_D.CAL_YEAR |
Gregorian Year Ending Date |
W_PDS_CALENDAR_D |
GREG_YEAR_END_DT |
W_MCAL_DAY_D.CAL_YEAR_END_DT |
Exchange Rate Mapping
The exchange rate data is loaded from the EXCH_RATE.csv
file or from RMFCS.
Measure | Target Table | Target Column | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 data warehouse table is the week-level aggregate generated by the historical
and daily load processes. 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 the data warehouse to have the extra
type code. Other sales are only included in the Total Sales measures in the PDS export.
The data only includes non-pack item sales, as it expects pack sales to be spread to
their component level when used.
Measure | Target Table | Target Column | 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) |
Custom Measures 1 - 20 |
W_PDS_SLS_IT_LC_WK_A |
FLEX1_NUM_VALUE through FLEX20_NUM_VALUE |
W_RTL_ SLS_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_SLS_IT_LC_WK_A.FLEX20_NUM_VALUE |
Gross Sales Mapping
A separate sales aggregate is also provided for Inventory Planning Optimization Cloud Service-Demand Forecasting (IPOCS-Demand Forecasting), 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. The data only includes non-pack item sales, as it expects pack sales to be spread to their component level when used.
Measure | Target Table | Target Column | 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 data
warehouse table is the week-level aggregate generated by the historical load process.
Only data for stockholding locations (STOCKHOLDING_FLG=Y
) is exported
to PDS.
Measure | Target Table | Target Column | 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 | 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 |
Custom Measures 1 - 20 |
W_PDS_PO_ONORD_IT_LC_WK_A |
FLEX1_NUM_VALUE through FLEX20_NUM_VALUE |
W_RTL_PO_ONORD_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_PO_ONORD_IT_LC_WK_A.FLEX20_NUM_VALUE |
Markdown Mapping
Data is loaded from the MARKDOWN.csv
file or from RMFCS. The primary data
warehouse table is the week-level aggregate generated by the historical load
process.
Measure | Target Table | Target Column | 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 |
Custom Measures 1 - 20 |
W_PDS_ MKDN_IT_LC_WK_A |
FLEX1_NUM_VALUE through FLEX20_NUM_VALUE |
W_RTL_ MKDN_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_ MKDN_IT_LC_WK_A.FLEX20_NUM_VALUE |
Wholesale/Franchise Mapping
Data is loaded from the SALES_WF.csv
file or from RMFCS. The primary data
warehouse table is the week-level aggregate generated by the historical load
process.
Measure | Target Table | Target Column | 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 data
warehouse table is the week-level aggregate generated by the historical load
process.
Measure | Target Table | Target Column | 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 data
warehouse table is the week-level aggregate generated by the historical load
process.
Measure | Target Table | Target Column | 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 data
warehouse table is the week-level aggregate generated by the historical load
process.
Measure | Target Table | Target Column | 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 data warehouse
table is the week-level aggregate generated by the historical load process.
Measure | Target Table | Target Column | 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 data
warehouse table is the week-level aggregate generated by the historical load
process.
Measure | Target Table | Target Column | 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 data
warehouse table is the week-level aggregate generated by the historical load
process.
Measure | Target Table | Target Column | 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 |
Custom Measures 1 - 20 |
W_PDS_ DEALINC_IT_LC_WK_A |
FLEX1_NUM_VALUE through FLEX20_NUM_VALUE |
W_RTL_ DEALINC_IT_LC_WK_A.FLEX1_NUM_VALUE through W_RTL_ DEALINC_IT_LC_WK_A.FLEX20_NUM_VALUE |
Intercompany Margin Mapping
Data is loaded from the IC_MARGIN.csv
file or from RMFCS. The primary data
warehouse table is the week-level aggregate generated by the historical load
process.
Measure | Target Table | Target Column | 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.