Foundation and Transactional Data from RMFCS using Retail Insights
RMFCS can send Foundation and Transactional data to RAP integration using Retail Insights (RI) and other systems within RAP. The systems can share the data, even if RMFCS is not implemented for the customer. The customer can upload the foundation and data files in the file format needed by RI in RAP integration. That way, the same data can be published to all applications within RAP. It can be done by scheduling required job flows in Retail Insights to get the foundation data from RMFCS and loading it into the staging tables present in Retail Data Exchange (RDX) from where the configured interfaces in MFP can pull the required data into Facts in Planning Data Schema (PDS) where MFP is deployed.
The customer can also load foundation data directly into RAP using the file format specified for RAP integration and using the same staging process in RI to write the data into RDX staging tables from where Planning can pull the data using standard configured interfaces. Only mapped columns specific to GA interfaces are detailed in this guide. For more details about interface file formats and the jobs flow details, see the Oracle Retail Analytics and Planning Implementation Guide. Also refer to those guides to find more information about the available columns in each interface staging tables in RDX sourced from RI so that customers using extensibility on template or using custom configuration (non-template) can pull the required data from RDX.
For information about the mappings between the columns in RDX interface table and RAP Inbound Data File columns, see the Data Mappings in the Transformations from RI to Planning section of the Oracle Retail Analytics and Planning Implementation Guide. The following sections shows the mappings between the RDX Interface table columns and the measures and dimensions in Planning.
The following table shows the list of interfaces in RAP to get the foundation and transactional data:
| Interface | Interface and Table Name | Interface Type |
|---|---|---|
|
Product Hierarchy |
W_PDS_PRODUCT_D |
Hierarchy Importer |
|
Location Hierarchy |
W_PDS_ORGANIZATION_D |
Hierarchy Importer |
|
Calendar Hierarchy |
W_PDS_CALENDAR_D (VW_CLND_HIER) |
Hierarchy Importer |
|
Currency Hierarchy |
VW_CURR_HIER (W_PDS_EXCH_RATE_D) |
Hierarchy Importer |
|
Sales Interface |
W_PDS_SLS_IT_LC_WK_A |
Data Importer |
|
Inventory Interface |
W_PDS_INV_IT_LC_WK_A |
Data Importer |
|
Markdown Interface |
W_PDS_MKDN_IT_LC_WK_A |
Data Importer |
|
On Order Interface |
W_PDS_PO_ONORD_IT_LC_WK_A |
Data Importer |
|
Receipts Interface |
W_PDS_INVRC_IT_LC_WK_A |
Data Importer |
|
Inventory Adjustments |
W_PDS_INVADJ_IT_LC_WK_A |
Data Importer |
|
Inventory Transfers |
W_PDS_INVTSF_IT_LC_WK_A |
Data Importer |
|
Deal Incomes |
W_PDS_DEALINC_IT_LC_WK_A |
Data Importer |
|
Wholesale/Franchise |
W_PDS_SLSWF_IT_LC_WK_A |
Data Importer |
|
Currency Conversion Rates |
W_PDS_EXCH_RATE_D (VW_CURR_RATE) |
Data Importer |
|
Location Data |
VW_LOC_DATA |
Data Importer |
The following table shows the mapping of dimensions to columns for Hierarchy Importer interfaces from external interface tables:
| Hierarchy | Dimension | External Interface Table | External Mapped Column |
|---|---|---|---|
|
prod |
sku |
W_PDS_PRODUCT_D |
ITEM |
|
prod |
sku_label |
W_PDS_PRODUCT_D |
ITEM_DESC |
| prod | skup |
W_PDS_PRODUCT_D |
ITEM_PARENT_DIFF |
| prod | skup_label |
W_PDS_PRODUCT_D |
ITEM_PARENT_DIFF_DESC |
| prod | skug |
W_PDS_PRODUCT_D |
ITEM_PARENT |
| prod | skug_label |
W_PDS_PRODUCT_D |
ITEM_PARENT_DESC |
|
prod |
scls |
W_PDS_PRODUCT_D |
CLASS_ID |
|
prod |
scls_label |
W_PDS_PRODUCT_D |
DEPT |
|
prod |
clss |
W_PDS_PRODUCT_D |
GROUP_NO |
|
prod |
class_label |
W_PDS_PRODUCT_D |
DIVISION |
|
prod |
dept |
W_PDS_PRODUCT_D |
COMPANY |
|
prod |
dept_label |
W_PDS_PRODUCT_D |
CO_NAME |
|
prod |
pgrp |
W_PDS_PRODUCT_D |
GROUP_NO |
|
prod |
pgrp_label |
W_PDS_PRODUCT_D |
GROUP_NAME |
|
prod |
dvsn |
W_PDS_PRODUCT_D |
DIVISION |
|
prod |
dvsn_label |
W_PDS_PRODUCT_D |
DIV_NAME |
|
prod |
cmpp |
W_PDS_PRODUCT_D |
COMPANY |
|
prod |
cmpp_label |
W_PDS_PRODUCT_D |
CO_NAME |
|
loc |
stor |
W_PDS_ORGANIZATION_D |
LOCATION |
|
loc |
stor_label |
W_PDS_ORGANIZATION_D |
LOC_NAME |
|
loc |
dstr |
W_PDS_ORGANIZATION_D |
DISTRICT |
|
loc |
dstr_label |
W_PDS_ORGANIZATION_D |
DISTRICT_NAME |
|
loc |
regn |
W_PDS_ORGANIZATION_D |
REGION |
|
loc |
regn_label |
W_PDS_ORGANIZATION_D |
REGION_NAME |
|
loc |
chnl |
W_PDS_ORGANIZATION_D |
AREA |
|
loc |
chnl_label |
W_PDS_ORGANIZATION_D |
AREA_NAME |
|
loc |
chan |
W_PDS_ORGANIZATION_D |
CHAIN |
|
loc |
chan_label |
W_PDS_ORGANIZATION_D |
CHAIN_NAME |
|
loc |
comp |
W_PDS_ORGANIZATION_D |
COMPANY |
|
loc |
comp_label |
W_PDS_ORGANIZATION_D |
CO_NAME |
|
loc |
phwh |
W_PDS_ORGANIZATION_D |
PHYSICAL_WH |
|
loc |
phwh_label |
W_PDS_ORGANIZATION_D |
PHYSICAL_WH_NAME |
|
loc |
loct |
W_PDS_ORGANIZATION_D |
LOC_TYPE |
|
loc |
loct_label |
W_PDS_ORGANIZATION_D |
LOC_TYPE_NAME |
|
loc |
strc |
W_PDS_ORGANIZATION_D |
LOCATION |
|
loc |
strc_label |
W_PDS_ORGANIZATION_D |
LOC_NAME |
|
loc |
chnc |
W_PDS_ORGANIZATION_D |
PLANNING_CHANNEL_ID |
|
loc |
chnc_label |
W_PDS_ORGANIZATION_D |
PLANNING_CHANNEL_NAME |
|
loc |
ccty |
W_PDS_ORGANIZATION_D |
PLANNING_COUNTRY_ID |
|
loc |
ccty_label |
W_PDS_ORGANIZATION_D |
PLANNING_COUNTRY_NAME |
|
clnd |
day |
W_PDS_CALENDAR_D |
DAY |
|
clnd |
day_label |
W_PDS_CALENDAR_D |
DAY_LABEL |
|
clnd |
week |
W_PDS_CALENDAR_D |
WEEK |
|
clnd |
week_label |
W_PDS_CALENDAR_D |
WEEK_LABEL |
|
clnd |
mnth |
W_PDS_CALENDAR_D |
MNTH |
|
clnd |
mnth_label |
W_PDS_CALENDAR_D |
MNTH_LABEL |
|
clnd |
qrtr |
W_PDS_CALENDAR_D |
QRTR |
|
clnd |
qrtr_label |
W_PDS_CALENDAR_D |
QRTR_LABEL |
|
clnd |
half |
W_PDS_CALENDAR_D |
HALF |
|
clnd |
half_label |
W_PDS_CALENDAR_D |
HALF_LABEL |
|
clnd |
year |
W_PDS_CALENDAR_D |
YEAR |
|
clnd |
year_label |
W_PDS_CALENDAR_D |
YEAR_LABEL |
|
clnd |
woyr |
W_PDS_CALENDAR_D |
WOYR |
|
clnd |
woyr_label |
W_PDS_CALENDAR_D |
WOYR_LABEL |
|
clnd |
stdb |
W_PDS_CALENDAR_D |
STDB |
|
curh |
curc |
VW_CURR_HIER |
TO_CURRENCY_CODE |
|
curh |
curc_label |
VW_CURR_HIER |
TO_CURRENCY_CODE |
| satr | satt | VW_SATR_HIER | ATTR_ID |
| satr | satt_label | VW_SATR_HIER | ATTR_DESC |
| satr | satv | VW_SATR_HIER | ATTR_VALUE |
| satr | satv_label | VW_SATR_HIER | ATTR_VALUE_DESC |
Note: For Calendar Hierarchy (clnd), RMFCS is not sending the labels. Internally, VW_CLND_HIER is defined in PDS against the interface W_PDS_CALENDAR_D table to derive the labels and also default the calendar import to PDS to have two past years, one current year, and two future years based on the current business date. The Administrator can update the same using the Online Administration Tool Tasks under System Admin Tasks -> List/Set/Unset PDS Integration variables and can update the CLND_PAST_YEARS and CLND_FUTURE_YEARS variables. By default, both are set to 2. The customer can also update the start fiscal month by setting the CLND_START_MONTH variable. By default, it is set to 2 to have the fiscal start month label be generated as February.
Note: For Currency Hierarchy (curh), there is no direct interface table. Internally, VW_CURR_HIER is defined in PDS against the interface W_PDS_EXCH_RATE_G table to get the unique currency codes. The base currency code is set by default as USD to get the conversion rates for other currencies. The Administrator can update the same using the Online Administration Tool Tasks under System Admin Tasks -> List/Set/Unset PDS Integration variables and can update the BASE_CURRENCY_CODE.
Note: For the Location Attribute Hierarchy (SATR), there is no direct interface table. Internally, VW_SATR_HIER is defined in PDS against the interface W_PDS_ORG_ATTR_STR_D table to get the distinct location attributes.
Note: The VAT Hierarchy (vath) in the MFP GA is not integrated using RAP integration. The customer needs to explicitly provide those files for the MFP GA.
The following table shows the mapping of fact names/measures names to columns for the Data Importer interfaces from the external interface tables in RDX:
| Fact Name | External Interface Table | External Mapped Column | External Mapping Condition |
|---|---|---|---|
|
drtyeop1c |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_COST |
CLEAR_IND = 'N' |
|
drtyeop1r |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_RETAIL |
CLEAR_IND = 'N' |
|
drtyeop1u |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_UNITS |
CLEAR_IND = 'N' |
|
drtyeop2c |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_COST |
CLEAR_IND = 'Y' |
|
drtyeop2r |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_RETAIL |
CLEAR_IND = 'Y' |
|
drtyeop2u |
W_PDS_INV_IT_LC_WK_A |
REGULAR_INVENTORY_UNITS |
CLEAR_IND = 'Y' |
|
drtyrinva1c |
W_PDS_INVADJ_IT_LC_WK_A |
SHRINK_COST |
|
|
drtyrinva1r |
W_PDS_INVADJ_IT_LC_WK_A |
SHRINK_RETAIL |
|
|
drtyrinva1u |
W_PDS_INVADJ_IT_LC_WK_A |
SHRINK_UNITS |
|
|
drtyrinva2c |
W_PDS_INVADJ_IT_LC_WK_A |
NON_SHRINK_ADJ_COST |
|
|
drtyrinva2r |
W_PDS_INVADJ_IT_LC_WK_A |
NON_SHRINK_ADJ_RETAIL |
|
|
drtyrinva2u |
W_PDS_INVADJ_IT_LC_WK_A |
NON_SHRINK_ADJ_UNITS |
|
|
drtymkdcanr |
W_PDS_MKDN_IT_LC_WK_A |
MARKDOWN_CANCEL |
|
|
drtymkdclrr |
W_PDS_MKDN_IT_LC_WK_A |
CLEAR_MARKDOWN_RETAIL |
|
|
drtymkdpclr |
W_PDS_MKDN_IT_LC_WK_A |
PROMO_MARKDOWN_RETAIL_CLEAR |
|
|
drtymkdpror |
W_PDS_MKDN_IT_LC_WK_A |
PROMO_MARKDOWN_RETAIL_REG |
|
|
drtymkdregr |
W_PDS_MKDN_IT_LC_WK_A |
REG_MARKDOWN_RETAIL |
|
|
drtymkupr |
W_PDS_MKDN_IT_LC_WK_A |
MARKUP |
|
|
drtyicmkur |
W_PDS_MKDN_IT_LC_WK_A |
INTERCOMPANY_MARKUP |
|
|
drtyicmkdr |
W_PDS_MKDN_IT_LC_WK_A |
INTERCOMPANY_MARKDOWN |
|
|
drtywfmkdr |
W_PDS_SLSWF_IT_LC_WK_A |
WF_MARKDOWN_RETAIL |
|
|
drtywfmkur |
W_PDS_SLSWF_IT_LC_WK_A |
WF_MARKUP_RETAIL |
|
|
drtywfslsu |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_SALES_UNITS |
|
|
drtywfslsc |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_SALES_COST |
|
|
drtywfslsr |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_SALES_RETAIL |
|
|
drtywfrtnu |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_UNITS |
|
|
drtywfrtnc |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_COST |
|
|
drtywfrtnr |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_RETAIL |
|
|
drtynslsclrc |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_CLR_COST |
|
|
drtynslsclrr |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_CLR_RETAIL |
|
|
drtynslsclru |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_CLR_UNITS |
|
|
drtynslsproc |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_PRO_COST |
|
|
drtynslspror |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_PRO_RETAIL |
|
|
drtynslsprou |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_PRO_UNITS |
|
|
drtynslsregc |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_REG_COST |
|
|
drtynslsregr |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_REG_RETAIL |
|
|
drtynslsregu |
W_PDS_SLS_IT_LC_WK_A |
NET_SALES_REG_UNITS |
|
|
drtyrtnclrc |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_CLR_COST |
|
|
drtyrtnclrr |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_CLR_RETAIL |
|
|
drtyrtnclru |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_CLR_UNITS |
|
|
drtyrtnproc |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_PRO_COST |
|
|
drtyrtnpror |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_PRO_RETAIL |
|
|
drtyrtnprou |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_PRO_UNITS |
|
|
drtyrtnregc |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_REG_COST |
|
|
drtyrtnregr |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_REG_RETAIL |
|
|
drtyrtnregu |
W_PDS_SLS_IT_LC_WK_A |
RETURNS_REG_UNITS |
|
|
drtyooc |
W_PDS_PO_ONORD_IT_LC_WK_A |
ON_ORDER_COST |
|
|
drtyoor |
W_PDS_PO_ONORD_IT_LC_WK_A |
ON_ORDER_RETAIL |
|
|
drtyoou |
W_PDS_PO_ONORD_IT_LC_WK_A |
ON_ORDER_UNITS |
|
|
drtyporcptc |
W_PDS_INVRC_IT_LC_WK_A |
PO_RECEIPT_COST |
|
|
drtyporcptr |
W_PDS_INVRC_IT_LC_WK_A |
PO_RECEIPT_RETAIL |
|
|
drtyporcptu |
W_PDS_INVRC_IT_LC_WK_A |
PO_RECEIPT_UNITS |
|
|
drtytraninbc |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_COST |
TSF_TYPE = 'B' |
|
drtytraninbr |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_RETAIL |
TSF_TYPE = 'B' |
|
drtytraninbu |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_UNITS |
TSF_TYPE = 'B' |
|
drtytraninic |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_COST |
TSF_TYPE = 'I' |
|
drtytraninir |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_RETAIL |
TSF_TYPE = 'I' |
|
drtytraniniu |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_UNITS |
TSF_TYPE = 'I' |
|
drtytraninr |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_RETAIL |
TSF_TYPE = 'N' |
|
drtytraninc |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_COST |
TSF_TYPE = 'N' |
|
drtytraninu |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_IN_UNITS |
TSF_TYPE = 'N' |
|
drtytranoutbc |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_COST |
TSF_TYPE = 'B' |
|
drtytranoutbr |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_RETAIL |
TSF_TYPE = 'B' |
|
drtytranoutbu |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_UNITS |
TSF_TYPE = 'B' |
|
drtytranoutic |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_COST |
TSF_TYPE = 'I' |
|
drtytranoutir |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_RETAIL |
TSF_TYPE = 'I' |
|
drtytranoutiu |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_UNITS |
TSF_TYPE = 'I' |
|
drtytranoutr |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_RETAIL |
TSF_TYPE = 'N' |
|
drtytranoutu |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_UNITS |
TSF_TYPE = 'N' |
|
drtytranoutc |
W_PDS_INVTSF_IT_LC_WK_A |
TSF_OUT_COST |
TSF_TYPE = 'N' |
|
drtyvndfndr |
W_PDS_DEALINC_IT_LC_WK_A |
DEAL_INCOME_SALES |
|
|
addvlocopnd |
VW_LOC_DATA |
STORE_OPEN_DATE |
|
|
addvlocendd |
VW_LOC_DATA |
STORE_CLOSE_DATE |
|
|
addvlocrefd |
VW_LOC_DATA |
REMODEL_DATE |
|
|
addvwfpoct |
VW_LOC_DATA |
STORE_TYPE |
|
|
drtylcratex |
W_PDS_EXCH_RATE_G |
EXCHANGE_RATE |
|
| addvlocatt | W_PDS_ORG_ATTR_STR_D | ATTR_VALUE |
Note: For Location specific data, the same W_PDS_ORGANIZATION_D hierarchy table used for the location hierarchy is used. The view VW_LOC_DATA is defined in PDS to point to the same set of data and used as data importer interface.
Note: Pack Items are filtered by default for MFP during the product hierarchy import from RAP using the Pack Item Filter Value measure set to N. This shared filter measure, PCKFLGVAL, can be managed in the Planning Admin workbook. In a multi-app scenario, if other applications need to bring in pack items, this can be changed to % to bring in both pack items and non-pack items.