3 RAP Integration
Planning Solutions built on Planning Data Schema (PDS) can be integrated with AI Foundation (AIF) and Retail Insights (RI) with in Retail Analytics and Planning (RAP) using RAP integration which in turn can get the foundation data from RMFCS or any other systems integrated to RAP. All three solutions (AIF, RI, PDS) within RAP can share data using RAP Data Exchange (RDX) using RAP interfaces.
Planning Solutions, such as MFP, configured and built in PDS using the RPASCE Configuration allows configurable solutions if they are not using template to use their custom hierarchy and fact names. Planning Solutions, such as MFP, allow configuring of interfaces using interface.cfg which controls the mapping of dimensions and facts to columns in the RAP interface staging tables. For more details about configuring interfaces using interfaces.cfg in RAP integration, see the Oracle Retail Predictive Application Server Cloud Edition Implementation Guide. The MFP template version has a pre-configured interface.cfg which contains the mapping of interfaces.
The above diagram shows the high-level RAP Architecture. The customer can upload their main input files as RAP Inbound Data using Object Storage and from their Planning Applications deployed in PDS can get the same using RDX. For more details about the RAP Inbound Interfaces, see the Oracle Retail Analytics and Planning Implementation Guide. Any supplemental data that is specific to planning can be directly loaded into PDS as PDS Flat Files. This section shows the details about the interfaces used by PDS in RAP using RDX.
Following are the pre-defined grouping of interfaces available in the MFP template version within RAP integration:
-
Foundation and Transactional data from RMFCS using Retail Insights (RI)
-
Plan Exports to Retail Insights (RI)
-
Forecast Imports from AI Foundation (AIF)
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 |
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 |
fflt |
W_PDS_ORGANIZATION_D |
CHANNEL_ID |
loc |
fflt_label |
W_PDS_ORGANIZATION_D |
CHANNEL_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 |
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: The VAT Hierarchy (vath) and Location Attributes Hierarchy (SATR) in the MFP GA are 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 |
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.
Plan Exports to Retail Insights
Approved plans from MFPCS can be exported to Retail Insights within RAP integration. Retail Insights allows getting four levels of plans for different versions. The MFP template version allows creating four levels of plans, Merch Plan (OP, CP), Merch Target (TG), Location Plan (OP, CP), and Location Target Plan (TG). All those plans and their different versions can be exported to Retail Insights on a weekly basis. Since MFP non-template versions can create a different level of plans and also can configure various metrics, the interface staging table in Retail Sights contains the same set of columns and various flex columns.
For more details about the list of columns available in the Retail Insights Interface Staging table if the customer plans to use extensibility or use the non-template version to send additional data, see the Oracle Retail Insights Implementation Guide. This guide contains only the mapped columns for the MFP template version for each level of plans.
The following table shows the list of interfaces in RAP to export the plan data from MFP to RI:
Interface | Interface and Table Name | Interface Type |
---|---|---|
Merch Plan Export |
MFP_PLAN1_EXP |
Data Explorer |
Merch Target Plan Export |
MFP_PLAN2_EXP |
Data Explorer |
Location Plan Export |
MFP_PLAN3_EXP |
Data Explorer |
Location Target Plan Export |
MFP_PLAN4_EXP |
Data Explorer |
Note: If Retail/Cost is denoted, that metric will have value only if that version of MFP is implemented from the template, otherwise that value will be always exported as zero.
Plan Level 1 MFP_PLAN1_EXP - Merch Plan Export
This plan export is for exporting both the OP and CP versions of approved Merch Plans. VERSION_NUM 0 is used to export the OP version and 1 is used to export the CP version. The following table only shows the mapping for OP versions. The CP versions of mapping remain the same; the Version Number used is 1.
Staging Table Column | Description | MFP Dimension/Measure Mapping | Retail/Cost |
---|---|---|---|
PROD_KEY |
Product Dimension |
scls |
|
LOC_KEY |
Location Dimension |
chnl |
|
CLND_KEY |
Calendar Dimension |
week |
|
PROD_DH_ATTR |
Attribute Dimension for RI |
-1 |
|
SUPPLIER_NUM |
Supplier Dimension for RI |
-1 |
|
CAL_DATE |
Last Day of Week |
MPOPLDOWD |
|
VERSION_NUM |
Version Number |
0 |
|
SLS_QTY |
Op Sales U |
MPOPSlsU |
|
SLSCL_QTY |
Op Sales Clr U |
MPOPSls2U |
|
SLSRGPRO_QTY |
Op Sales Reg+Promo U |
MPOPSls1U |
|
SLS_RTL_AMT |
Op Sales R |
MPOPSlsR |
|
SLSCL_RTL_AMT |
Op Sales Clr R |
MPOPSls2R |
|
SLSRGPRO_RTL_AMT |
Op Sales Reg+Promo R |
MPOPSls1R |
|
SLS_COST_AMT |
Op Sales C |
MPOPSlsC |
|
RET_QTY |
Op Returns U |
MPOPRtnU |
|
RETCL_QTY |
Op Returns Clr U |
MPOPRtn2U |
|
RETRGPRO_QTY |
Op Returns Reg+Promo U |
MPOPRtn1U |
|
RET_RTL_AMT |
Op Returns R |
MPOPRtnR |
|
RETCL_RTL_AMT |
Op Returns Clr R |
MPOPRtn2R |
|
RETRGPRO_RTL_AMT |
Op Returns Reg+Promo R |
MPOPRtn1R |
|
NET_SLSRGPRO_QTY |
Op Net Sales Reg+Promo U |
MPOPNSls1U |
|
NET_SLSCL_QTY |
Op Net Sales Clr U |
MPOPNSls2U |
|
NET_SLSRGPRO_RTL_AMT |
Op Net Sales Reg+Promo R |
MPOPNSls1R |
|
NET_SLSCL_RTL_AMT |
Op Net Sales Clr R |
MPOPNSls2R |
|
NET_MARGIN_RTL_AMT |
Op Net GM R |
MPOPNGMR |
|
TAX_RTL_AMT |
Op VAT R |
MPOPVATR |
|
SLSTE_RTL_AMT |
Op Net Sales R |
MPOPNSlsR |
|
MARGIN_RTL_AMT |
Op GM R |
MPOPGMR |
|
MKDNPM_RTL_AMT |
Op Inv Devaluation Reg+Promo R |
MPOPInvDV1R |
|
MKDNCL_RTL_AMT |
Op Inv Devaluation Clr R |
MPOPInvDV2R |
|
MKDNPR_RTL_AMT |
Op Markdown R |
MPOPMkdR |
|
BOH_COST_AMT |
Op BOP C |
MPOPBOPC |
|
BOH_RTL_AMT |
Op BOP R |
MPOPBOPR |
|
BOHRGPRO_RTL_AMT |
Op BOP Reg+Promo R |
MPOPBOP1R |
|
BOHCL_RTL_AMT |
Op BOP Clr R |
MPOPBOP2R |
|
BOH_QTY |
Op BOP U |
MPOPBOPU |
|
BOHRGPRO_QTY |
Op BOP Reg+Promo U |
MPOPBOP1U |
|
BOHCL_QTY |
Op BOP Clr U |
MPOPBOP2U |
|
EOH_COST_AMT |
Op EOP C |
MPOPEOPC |
|
EOHRGPRO_COST_AMT |
Op EOP Reg+Promo C |
MPOPEOP1C |
Cost |
EOHCL_COST_AMT |
Op EOP Clr C |
MPOPEOP2C |
Cost |
EOH_RTL_AMT |
Op EOP R |
MPOPEOPR |
Retail |
EOHRGPRO_RTL_AMT |
Op EOP Reg+Promo R |
MPOPEOP1R |
Retail |
EOHCL_RTL_AMT |
Op EOP Clr R |
MPOPEOP2R |
Retail |
EOH_QTY |
Op EOP U |
MPOPEOPU |
|
EOHRGPRO_QTY |
Op EOP Reg+Promo U |
MPOPEOP1U |
|
EOHCL_QTY |
Op EOP Clr U |
MPOPEOP2U |
|
INVRC_COST_AMT |
Op Receipts C |
MPOPRcptC |
|
INVRC_RTL_AMT |
Op Receipts R |
MPOPRcptR |
|
INVRC_QTY |
Op Receipts U |
MPOPRcptU |
|
INVADJ_COST_AMT |
Op Inv Adj C |
MPOPInvAdjC |
|
INVADJ_RTL_AMT |
Op Inv Adj R |
MPOPInvAdjR |
|
INVADJ_QTY |
Op Inv Adj U |
MPOPInvAdjU |
|
SHRINK_COST_AMT |
Op Shrink C |
MPOPShrkC |
|
SHRINK_RTL_AMT |
Op Shrink R |
MPOPShrkR |
Retail |
SHRINK_QTY |
Op Shrink U |
MPOPShrkU |
|
MISCI_COST_AMT |
Op Misc Adj C |
MPOPMiscAdjC |
Cost |
MISCI_RTL_AMT |
Op Misc Adj R |
MPOPMiscAdjR |
Retail |
MISCI_QTY |
Op Misc Adj U |
MPOPMiscAdjU |
|
MOVETOCL_COST_AMT |
Op Move to Clr C |
MPOPMOCC |
Cost |
MOVETOCL_RTL_AMT |
Op Move to Clr R |
MPOPMOCR |
Retail |
MOVETOCL_QTY |
Op Move to Clr U |
MPOPMOCU |
|
MOS_COST_AMT |
Op MOS C |
MPOPMOSC |
|
MOS_RTL_AMT |
Op MOS R |
MPOPMOSR |
Retail |
MOS_QTY |
Op MOS U |
MPOPMOSU |
|
MKDNWF_RTL_AMT |
Op Markdown due to W/F R |
MPOPMkdWFR |
|
RYLTY_RTL_AMT |
Op Royalties R |
MPOPRoyalR |
|
VNDFND_RTL_AMT |
Op Vendor Funds R |
MPOPVndFndR |
|
DMND_QTY |
Op Demand U |
MPOPDemandU |
|
DMND_CAN_QTY |
Op Cancel U |
MPOPCancU |
|
SLS_COMP_RTL_AMT |
Op Comp Sales R |
MPOPSlsCmpR |
|
SLS_NCOMP_RTL_AMT |
Op Non-Comp Sales R |
MPOPSlsNCpR |
|
TRAFFIC_CNT |
Op Traffic U |
MPOPTrafficU |
Plan Level 2 MFP_PLAN2_EXP - Merch Target Plan Export
This plan export is for exporting the Tgt version of approved Merch Target Plans. VERSION_NUM 0 is used to export the Target version.
Staging Table Column | Description | MFP Dimension/Measure Mapping | Retail/Cost |
---|---|---|---|
PROD_KEY |
Product Dimension |
dept |
|
LOC_KEY |
Location Dimension |
chnl |
|
CLND_KEY |
Calendar Dimension |
week |
|
PROD_DH_ATTR |
Attribute Dimension for RI |
-1 |
|
SUPPLIER_NUM |
Supplier Dimension for RI |
-1 |
|
CAL_DATE |
Last Day of Week |
MTTGLDOWD |
|
VERSION_NUM |
Version Number |
0 |
|
SLS_QTY |
Tgt Sales U |
MTTGSlsU |
|
SLSCL_QTY |
Tgt Sales Clr U |
MTTGSls2U |
Retail |
SLSRGPRO_QTY |
Tgt Sales Reg+Promo U |
MTTGSls1U |
Retail |
SLS_RTL_AMT |
Tgt Sales R |
MTTGSlsR |
|
SLSCL_RTL_AMT |
Tgt Sales Clr R |
MTTGSls2R |
Retail |
SLSRGPRO_RTL_AMT |
Tgt Sales Reg+Promo R |
MTTGSls1R |
Retail |
SLS_COST_AMT |
Tgt Sales C |
MTTGSlsC |
|
RET_QTY |
Tgt Returns U |
MTTGRtnU |
|
RETCL_QTY |
Tgt Returns Clr U |
MTTGRtn2U |
|
RETRGPRO_QTY |
Tgt Returns Reg+Promo U |
MTTGRtn1U |
|
RET_RTL_AMT |
Tgt Returns R |
MTTGRtnR |
|
RETCL_RTL_AMT |
Tgt Returns Clr R |
MTTGRtn2R |
|
RETRGPRO_RTL_AMT |
Tgt Returns Reg+Promo R |
MTTGRtn1R |
|
NET_SLSRGPRO_QTY |
Tgt Net Sales Reg+Promo U |
MTTGNSls1U |
|
NET_SLSCL_QTY |
Tgt Net Sales Clr U |
MTTGNSls2U |
|
NET_SLSRGPRO_RTL_AMT |
Tgt Net Sales Reg+Promo R |
MTTGNSls1R |
|
NET_SLSCL_RTL_AMT |
Tgt Net Sales Clr R |
MTTGNSls2R |
|
NET_MARGIN_RTL_AMT |
Tgt Net GM R |
MTTGNGMR |
|
TAX_RTL_AMT |
Tgt VAT R |
MTTGVATR |
|
SLSTE_RTL_AMT |
Tgt Net Sales R |
MTTGNSlsR |
|
MARGIN_RTL_AMT |
Tgt GM R |
MTTGGMR |
|
MKDNPM_RTL_AMT |
Tgt Inv Devaluation Reg+Promo R |
MTTGInvDV1R |
|
MKDNCL_RTL_AMT |
Tgt Inv Devaluation Clr R |
MTTGInvDV2R |
|
MKDNPR_RTL_AMT |
Tgt Markdown R |
MTTGMkdR |
|
BOH_COST_AMT |
Tgt BOP C |
MTTGBOPC |
|
BOH_RTL_AMT |
Tgt BOP R |
MTTGBOPR |
|
BOHRGPRO_RTL_AMT |
Tgt BOP Reg+Promo R |
MTTGBOP1R |
|
BOHCL_RTL_AMT |
Tgt BOP Clr R |
MTTGBOP2R |
|
BOH_QTY |
Tgt BOP U |
MTTGBOPU |
|
BOHRGPRO_QTY |
Tgt BOP Reg+Promo U |
MTTGBOP1U |
|
BOHCL_QTY |
Tgt BOP Clr U |
MTTGBOP2U |
|
EOH_COST_AMT |
Tgt EOP C |
MTTGEOPC |
|
EOHRGPRO_COST_AMT |
Tgt EOP Reg+Promo C |
MTTGEOP1C |
|
EOHCL_COST_AMT |
Tgt EOP Clr C |
MTTGEOP2C |
|
EOH_RTL_AMT |
Tgt EOP R |
MTTGEOPR |
|
EOHRGPRO_RTL_AMT |
Tgt EOP Reg+Promo R |
MTTGEOP1R |
|
EOHCL_RTL_AMT |
Tgt EOP Clr R |
MTTGEOP2R |
|
EOH_QTY |
Tgt EOP U |
MTTGEOPU |
|
EOHRGPRO_QTY |
Tgt EOP Reg+Promo U |
MTTGEOP1U |
|
EOHCL_QTY |
Tgt EOP Clr U |
MTTGEOP2U |
|
INVRC_COST_AMT |
Tgt Receipts C |
MTTGRcptC |
|
INVRC_RTL_AMT |
Tgt Receipts R |
MTTGRcptR |
|
INVRC_QTY |
Tgt Receipts U |
MTTGRcptU |
|
INVADJ_COST_AMT |
Tgt Inv Adj C |
MTTGInvAdjC |
|
INVADJ_RTL_AMT |
Tgt Inv Adj R |
MTTGInvAdjR |
Retail |
INVADJ_QTY |
Tgt Inv Adj U |
MTTGInvAdjU |
|
SHRINK_COST_AMT |
Tgt Shrink C |
MTTGShrkC |
|
SHRINK_RTL_AMT |
Tgt Shrink R |
MTTGShrkR |
Retail |
SHRINK_QTY |
Tgt Shrink U |
MTTGShrkU |
|
MISCI_COST_AMT |
Tgt Misc Adj C |
MTTGMiscAdjC |
|
MISCI_RTL_AMT |
Tgt Misc Adj R |
MTTGMiscAdjR |
Retail |
MISCI_QTY |
Tgt Misc Adj U |
MTTGMiscAdjU |
|
MOVETOCL_COST_AMT |
Tgt Move to Clr C |
MTTGMOCC |
|
MOVETOCL_RTL_AMT |
Tgt Move to Clr R |
MTTGMOCR |
Retail |
MOVETOCL_QTY |
Tgt Move to Clr U |
MTTGMOCU |
|
MOS_COST_AMT |
Tgt MOS C |
MTTGMOSC |
|
MOS_RTL_AMT |
Tgt MOS R |
MTTGMOSR |
Retail |
MOS_QTY |
Tgt MOS U |
MTTGMOSU |
|
MKDNWF_RTL_AMT |
Tgt Markdown due to W/F R |
MTTGMkdWFR |
|
RYLTY_RTL_AMT |
Tgt Royalties R |
MTTGRoyalR |
|
VNDFND_RTL_AMT |
Tgt Vendor Funds R |
MTTGVndFndR |
|
DMND_QTY |
Tgt Demand U |
MTTGDemandU |
|
DMND_CAN_QTY |
Tgt Cancel U |
MTTGCancU |
|
SLS_COMP_RTL_AMT |
Tgt Comp Sales R |
MTTGSlsCmpR |
|
SLS_NCOMP_RTL_AMT |
Tgt Non-Comp Sales R |
MTTGSlsNCpR |
|
TRAFFIC_CNT |
Tgt Traffic U |
MTTGTrafficU |
Plan Level 3 MFP_PLAN3_EXP - Location Plan Export
This plan export is for exporting both the OP and CP versions of approved Location Plans. VERSION_NUM 0 is used to export the OP version and 1 is used to export the CP version. The following table only shows the mapping for OP versions. The CP versions of mapping remain the same; the Version Number used is 1.
Staging Table Column | Description | MFP Dimension/Measure Mapping | Retail/Cost |
---|---|---|---|
PROD_KEY |
Product Dimension |
DEPT |
|
LOC_KEY |
Location Dimension |
STOR |
|
CLND_KEY |
Calendar Dimension |
WEEK |
|
PROD_DH_ATTR |
Attribute Dimension for RI |
-1 |
|
SUPPLIER_NUM |
Supplier Dimension for RI |
-1 |
|
CAL_DATE |
Last Day of Week |
LPOPLDOWD |
|
VERSION_NUM |
Version Number |
0 |
|
SLS_QTY |
Op Sales U |
LPOPSlsU |
|
SLSCL_QTY |
Op Sales Clr U |
LPOPSls2U |
|
SLS_RTL_AMT |
Op Sales R |
LPOPSlsR |
|
SLSCL_RTL_AMT |
Op Sales Clr R |
LPOPSls2R |
|
SLS_COST_AMT |
Op Sales C |
LPOPSlsC |
|
RET_QTY |
Op Returns U |
LPOPRtnU |
|
RETCL_QTY |
Op Returns Clr U |
LPOPRtn2U |
|
RET_RTL_AMT |
Op Returns R |
LPOPRtnR |
|
TAX_RTL_AMT |
Op VAT R |
LPOPVATR |
|
SLSTE_RTL_AMT |
Op Net Sales R |
LPOPNSlsR |
|
MARGIN_RTL_AMT |
Op GM R |
LPOPGMR |
|
MKDNPM_RTL_AMT |
Op Inv Devaluation Reg+Promo R |
LPOPInvDVR |
|
MKDNPR_RTL_AMT |
Op Markdown R |
LPOPMkdR |
|
BOH_COST_AMT |
Op BOP C |
LPOPBOPC |
|
BOH_RTL_AMT |
Op BOP R |
LPOPBOPR |
Retail |
BOH_QTY |
Op BOP U |
LPOPBOPU |
|
EOH_COST_AMT |
Op EOP C |
LPOPEOPC |
|
EOH_RTL_AMT |
Op EOP R |
LPOPEOPR |
Retail |
EOH_QTY |
Op EOP U |
LPOPEOPU |
|
INVRC_COST_AMT |
Op Receipts C |
LPOPRcptC |
|
INVRC_RTL_AMT |
Op Receipts R |
LPOPRcptR |
Retail |
INVRC_QTY |
Op Receipts U |
LPOPRcptU |
|
INVADJ_QTY |
Op Inv Adj U |
LPOPInvAdjU |
|
SHRINK_COST_AMT |
Op Shrink C |
LPOPShrkC |
|
SHRINK_RTL_AMT |
Op Shrink R |
LPOPShrkR |
Retail |
SHRINK_QTY |
Op Shrink U |
LPOPShrkU |
|
MKDNWF_RTL_AMT |
Op Markdown due to W/F R |
LPOPMkdWFR |
Plan Level 4 MFP_PLAN4_EXP - Location Target Plan Export
This plan export is for exporting the Tgt version of approved Location Target Plans. VERSION_NUM 0 is used to export the Target version.
Staging Table Column | Description | MFP Dimension/Measure Mapping | Retail/Cost |
---|---|---|---|
PROD_KEY |
Product Dimension |
DEPT |
|
LOC_KEY |
Location Dimension |
STOR |
|
CLND_KEY |
Calendar Dimension |
WEEK |
|
PROD_DH_ATTR |
Attribute Dimension for RI |
-1 |
|
SUPPLIER_NUM |
Supplier Dimension for RI |
-1 |
|
CAL_DATE |
Last Day of Week |
LTTGLDOWD |
|
VERSION_NUM |
Version Number |
0 |
|
SLS_QTY |
Tgt Sales U |
LTTGSlsU |
|
SLS_RTL_AMT |
Tgt Sales R |
LTTGSlsR |
|
RET_QTY |
Tgt Returns U |
LTTGRtnU |
|
RET_RTL_AMT |
Tgt Returns R |
LTTGRtnR |
|
MKDNPR_RTL_AMT |
Tgt Markdown R |
LTTGMkdR |
Retail |
Forecast Imports from AI Foundation
Forecasts can be generated from AI Foundation (AIF) and imported to MFPCS using RAP integration. AI Foundation can generate different levels of forecasts as needed by different levels of plans. It generates both Pre-Season forecasts (using the Auto-ES Forecast method) and In-Season Forecasts (using the Bayesian Forecast Method). AI Foundation directly gets the actuals and plan data from MFP through RI using RAP integration. Job flows in AI Foundation need to be scheduled to generate the forecast and import the same to MFP. For more details, see the Oracle Retail Analytics and Planning Implementation Guide.
In order to get forecasts from AI Foundation (AIF), during implementation, some initial setups need to be done in the AI Foundation (AIF) platform. For more details, see the Oracle Retail Analytics and Planning Implementation Guide.
The following table shows the interface table column details from AI Foundation in RDX used for the interface.
Interface Name: RSE_FCST_DEMAND_EXP
Table Column | Data Type | Comments |
---|---|---|
RUN_ID |
Number(10) |
The export Run ID as obtained from the RAP_INTF_UTIL. |
CAL_HIER_LEVEL |
Varchar2(30) |
The calendar level data is for Fiscal Year, Fiscal Quarter, Fiscal Period, Fiscal Week, and Fiscal Day. |
LOC_HIER_LEVEL |
Varchar2(30) |
The location hierarchy level data is for COMPANY, CHAIN, AREA, REGION, DISTRICT, and LOCATION. |
PROD_HIER_LEVEL |
Varchar2(30) |
The product hierarchy level the data is for CMP, DIV, GRP, DEPT, CLS, SBC, STYLE, STYLE_COLOR, and SKU. |
FCST_DATE_FROM |
Date |
The start date which the forecast is for. |
LOC_EXT_KEY |
Varchar2(80) |
The external id of the location. It will use the integration ids as provided to RI (preferably the RMS id, and not an integration id such as AREA~123). |
PROD_EXT_KEY |
Varchar2(80) |
The external id of the product hierarchy. It will use the integration ids as provided to RI (preferably the RMS id, and not an integration id such as CLS~123~456~789). |
CUSTSEG_EXT_KEY |
Varchar2(80) |
The external id of the customer segment. It will be NULL if not applicable. |
FCST_TYPE |
Varchar2(20) |
The type of forecast. PI, NPI (PI=Plan Influenced, PI = Non Plan Influenced) |
REG_SLS_QTY |
Number(38,20) |
Regular Sales Units |
REG_SLS_AMT |
Number(38,20) |
Regular Sales Amount |
PR_SLS_QTY |
Number(38,20) |
Promo Sales Units |
PR_SLS_AMT |
Number(38,20) |
Promo Sales Amount |
CLR_SLS_QTY |
Number(38,20) |
Clearance Sales Units |
CLR_SLS_AMT |
Number(38,20) |
Clearance Sales Amount |
REG_PR_SLS_QTY |
Number(38,20) |
Regular and Promo Sales Units |
REG_PR_SLS_AMT |
Number(38,20) |
Regular and Promo Sales Amount |
SLS_QTY |
Number(38,20) |
Total Sales Units |
SLS_AMT |
Number(38,20) |
Total Sales Amount |
RET_QTY |
Number(38,20) |
Return Units |
RET_AMT |
Number(38,20) |
Return Amount |
The same Interface table contains the forecast data for different levels of plans differentiated by _LEVEL columns within the interface. The single interface run pulls data for different levels of forecasts which are pre-configured. Customers using non-template versions, if using different levels of plans, can use the supported levels in AI Foundation to generate forecasts. The following sections provide the default levels of forecasts exported for the MFPCS template version and their mappings.
Merch Plan Forecasts Mapping
The following table shows the mapping for pre-season and in-season Merch Plan Forecasts.
Table Column | Mapping for Pre-Season (MPP) | Mapping for In-Season (MPI) |
---|---|---|
CAL_HIER_LEVEL |
Fiscal Week |
Fiscal Week |
LOC_HIER_LEVEL |
AREA |
AREA |
PROD_HIER_LEVEL |
SBC |
SBC |
FCST_DATE_FROM |
WEEK |
WEEK |
LOC_EXT_KEY |
CHNL |
CHNL |
PROD_EXT_KEY |
SCLS |
SCLS |
CUSTSEG_EXT_KEY |
NULL |
NULL |
FCST_TYPE |
NPI |
PI |
CLR_SLS_QTY |
MPWPDmdP2U |
MPWPDmdI2U |
CLR_SLS_AMT |
MPWPDmdP2R |
MPWPDmdI2R |
REG_PR_SLS_QTY |
MPWPDmdP1U |
MPWPDmdI1U |
REG_PR_SLS_AMT |
MPWPDmdP1R |
MPWPDmdI1R |
RET_QTY |
MPWPRtnPU |
MPWPRtnIU |
RET_AMT |
MPWPRtnPR |
MPWPRtnIR |
Merch Target Plan Forecasts Mapping
The following table shows the mapping for pre-season and in-season Merch Target Plan Forecasts.
Table Column | Mapping for Pre-Season (MTP) | Mapping for In-Season (MTI) |
---|---|---|
CAL_HIER_LEVEL |
Fiscal Week |
Fiscal Week |
LOC_HIER_LEVEL |
AREA |
AREA |
PROD_HIER_LEVEL |
DEPT |
DEPT |
FCST_DATE_FROM |
WEEK |
WEEK |
LOC_EXT_KEY |
CHNL |
CHNL |
PROD_EXT_KEY |
DEPT |
DEPT |
CUSTSEG_EXT_KEY |
NULL |
NULL |
FCST_TYPE |
NPI |
PI |
CLR_SLS_QTY |
MTWPDmdP2U |
MTWPDmdI2U |
CLR_SLS_AMT |
MTWPDmdP2R |
MTWPDmdI2R |
REG_PR_SLS_QTY |
MTWPDmdP1U |
MTWPDmdI1U |
REG_PR_SLS_AMT |
MTWPDmdP1R |
MTWPDmdI1R |
RET_QTY |
MTWPRtnPU |
MTWPRtnIU |
RET_AMT |
MTWPRtnPR |
MTWPRtnIR |
Location Plan Forecasts Mapping
The following table shows the mapping for pre-season and in-season Location Plan Forecasts.
Table Column | Mapping for Pre-Season (LPP) | Mapping for In-Season (LPI) |
---|---|---|
CAL_HIER_LEVEL |
Fiscal Week |
Fiscal Week |
LOC_HIER_LEVEL |
LOCATION |
LOCATION |
PROD_HIER_LEVEL |
DEPT |
DEPT |
FCST_DATE_FROM |
WEEK |
WEEK |
LOC_EXT_KEY |
STOR |
STOR |
PROD_EXT_KEY |
DEPT |
DEPT |
CUSTSEG_EXT_KEY |
NULL |
NULL |
FCST_TYPE |
NPI |
PI |
SLS_QTY |
LPWPDmdP1U |
LPWPDmdI1U |
SLS_AMT |
LPWPDmdP1R |
LPWPDmdI1R |
RET_QTY |
LPWPRtnPU |
LPWPRtnIU |
RET_AMT |
LPWPRtnPR |
LPWPRtnIR |
Location Target Plan Forecasts Mapping
The following table shows the mapping for pre-season and in-season Location Target Plan Forecasts.
Table Column | Mapping for Pre-Season (LTP) | Mapping for In-Season (LTI) |
---|---|---|
CAL_HIER_LEVEL |
Fiscal Week |
Fiscal Week |
LOC_HIER_LEVEL |
LOCATION |
LOCATION |
PROD_HIER_LEVEL |
CMP |
CMP |
FCST_DATE_FROM |
WEEK |
WEEK |
LOC_EXT_KEY |
STOR |
STOR |
PROD_EXT_KEY |
CMPP |
CMPP |
CUSTSEG_EXT_KEY |
NULL |
NULL |
FCST_TYPE |
NPI |
PI |
SLS_QTY |
LTWPDmdP1U |
LTWPDmdI1U |
SLS_AMT |
LTWPDmdP1R |
LTWPDmdI1R |
RET_QTY |
LTWPRtnPU |
LTWPRtnIU |
RET_AMT |
LTWPRtnPR |
LTWPRtnIR |
Implementation Steps with RAP Integration
If RAP integration is enabled in the environment (that is, if the customer is going to get data from RMFCS using RDX integration), follow these steps for implementation. The steps assume that RPAS, RASL, UI, and RDX are already deployed:
-
Run the Batch Process in RAP in Retail Insights (RI) to load the required initial data into the RDX staging tables.
-
Upload any application-specific hierarchy files and data files that are not coming from RDX into Object Storage.
-
Once the MFP Cloud Service environment is provisioned, use the bootstrap Build Application task to build the application and use the batch task as set_rdx to just set the Enable RDX Boolean before the initial batch, or run post_hier to enable the RDX Boolean and load/import only the hierarchy files, or run postbuild_rdx to enable the RDX and to load/import initial hierarchy and data files and also run the initial batch. Batch step post_hier can also be run from OAT, to enable the RDX and load the available hierarchy files after building the domain. Use postbuild only if planning to load and use only the GA data set.
-
Schedule the regular weekly flow in the RI, AIF, and Planning applications in JOS/POM to interface the initial data into the application to get data from both RDX and Object Storage.
Note:
At least post_hier or postbuild_rdx should be run once with at least the calendar hierarchy file before trying to run weekly batch using JOS/POM.