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 ap ca, 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 APCS, 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 APCS 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 is the pre-defined grouping of interfaces available in the APCS template version within RAP integration.
Pre-defined imports from RAP integration to APCS:
-
Import Foundation and Transactional data from RMFCS using Retail Insights (RI)
-
Import Forecasts from AI Foundation (AIF)
-
Import DT Parameters from AI Foundation (AIF)
-
Import Location Clusters from AI Foundation (AIF)
-
Import Size Profiles from AI Foundation (AIF)
Pre-defined exports to RAP integration to APCS:
-
Export Assortment Periods for Location Clusters from AI Foundation (AIF)
-
Export Active Assortments to AI Foundation (AIF)
-
Export Assortment Plans to Retail Insights (RI)
Import 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 APCS can pull the required data into Facts in Planning Data Schema (PDS) where APCS 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.
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 |
Cluster Hierarchy |
VW_CLRH_HIER (W_PDS_ORGANIZATION_D |
Hierarchy Importer |
Product Attribute Hierarchy |
W_PDS_UDA_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 |
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 Transfers |
W_PDS_INVTSF_IT_LC_WK_A |
Data Importer |
Markdowns Interface |
W_PDS_MKDN_IT_LC_WK_A |
Data Importer |
Wholesale/Franchise |
W_PDS_SLSWF_IT_LC_WK_A |
Data Importer |
Product Attributes |
W_PDS_PRODUCT_ATTR_D |
Data Importer |
Location Data |
VW_LOC_DATA |
Data Importer |
Product Data |
VW_PROD_DATA |
Data Importer |
UDA Data |
VW_UDA_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 |
prod |
vndr |
W_PDS_PRODUCT_D |
SUPPLIER |
prod |
vndr_label |
W_PDS_PRODUCT_D |
SUP_NAME |
prod |
brnd |
W_PDS_PRODUCT_D |
BRAND_NAME |
prod |
brnd_label |
W_PDS_PRODUCT_D |
BRAND_DESCRIPTION |
prod |
sta1 |
W_PDS_PRODUCT_D |
NA |
prod |
sta1_label |
W_PDS_PRODUCT_D |
Unassigned |
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 |
LOCATION_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 |
clnd |
stdb_label |
W_PDS_CALENDAR_D |
STDB_LABEL |
clnd |
hldy |
W_PDS_CALENDAR_D |
NA |
clnd |
hldy_label |
W_PDS_CALENDAR_D |
Unassigned |
clnd |
evnt |
W_PDS_CALENDAR_D |
NA |
clnd |
evnt_label |
W_PDS_CALENDAR_D |
Unassigned |
clnd |
bypd |
W_PDS_CALENDAR_D |
NA |
clnd |
bypd_label |
W_PDS_CALENDAR_D |
Unassigned |
clrh |
clus |
VW_CLRH_HIER |
CLUS_ID |
clrh |
clus_label |
VW_CLRH_HIER |
CLUS_DESC |
patr |
patv |
W_PDS_UDA_D (VW_PATR_HIER) |
PROD_ATTR_VALUE |
patr |
patv_label |
W_PDS_UDA_D (VW_PATR_HIER) |
PROD_ATTR_VALUE_DESC |
patr |
patt |
W_PDS_UDA_D (VW_PATR_HIER) |
PROD_ATTR |
patr |
patt_label |
W_PDS_UDA_D (VW_PATR_HIER) |
PROD_ATTR_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 Cluster Hierarchy (clrh), there is no direct interface table. Internally, VW_CLRH_HIER is defined in PDS against the interface W_PDS_ORGANIZATION_D table to get the locations as cluster ids.
Note: The VW_PATR_HIER view is an internal view in PDS against the base RDX tables W_PDS_UDA_D, W_PDS_DIFF_D, W_PDS_SUPPLIER_D, and W_PDS_BRAND_D by concatenating all of them as product attributes. It also concatenates the product attribute name with the product attribute values using ‘_’ to make the product attribute values unique. The Product Attribute name for Supplier (W_PDS_SUPPLIER_D) is used as ‘supp’ and Brand (W_PDS_BRAND_D) is used as ‘brnd’. Only Product attributes with UDA_TYPE_CODE as ‘LV’ from W_PDS_UDA_D are included in the view.
Note: For all APCS hierarchies that are not integrated using RAP integration, the customer needs to explicitly provide those files.
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' |
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' |
drtyicmkur |
W_PDS_MKDN_IT_LC_WK_A |
INTERCOMPANY_MARKUP |
|
drtyicmkdr |
W_PDS_MKDN_IT_LC_WK_A |
INTERCOMPANY_MARKDOWN |
|
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 |
|
drtywfrtnr |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_COST |
|
drtywfrtnc |
W_PDS_SLSWF_IT_LC_WK_A |
FRANCHISE_RETURNS_RETAIL |
|
addvlocopnd |
VW_LOC_DATA |
STORE_OPEN_DATE |
|
addvlocendd |
VW_LOC_DATA |
STORE_CLOSE_DATE |
|
addvlocrefd |
VW_LOC_DATA |
REMODEL_DATE |
|
drdvprdatt |
W_PDS_PRODUCT_ATTR_D (VW_PATV_DATA) |
PROD_ATTR_VALUE |
|
drdvppatvt |
VW_PATV_DATA |
PATV_VALUE |
|
drtyudab |
VW_UDS_DATA |
True for PROD_ATTR |
|
drtypclsst |
VW_PROD_DATA |
CLASS_DISPLAY_ID |
|
drtypsclst |
VW_PROD_DATA |
SUBCLASS_DISPLAY_ID |
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. Similarly, VW_PROD_DATA is defined against W_PDS_PRODUCT_D to load any required data as measures.
VW_PATR_DATA is defined against W_PDS_PRODUCT_ATTR_D for UDA_TYPE in ‘LV’ and also gets attribute values for the DIFF*, SUPPLIER, AND BRAND_NAME from the W_PDS_PRODUCT_D table at the item level. It also concatenates the product attribute values with product attribute names using ‘_’ and uses ‘supp’ and ‘brnd’ as product attribute names for Supplier and Brand.
The VW_PATV_DATA internal view defined against the Product Attribute Hierarchy table contains product attribute values without concatenation of product attribute names and it uses similar tables as in VW_PATR_HIER. The VW_UDA_DATA is defined against W_PDS_UDA_D to only contain distinct UDA to uniquely identify the UDAs defined in RMFCS.
Import Forecasts from AI Foundation
Forecasts can be generated from AI Foundation (AIF) and imported to APCS 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 through RAP integration. Job flows in AI Foundation need to be scheduled to generate the forecast and import the same to APCS. For more details, see the Oracle Retail Analytics and Planning Integration Implementation Guide.
In order to get forecasts from AI Foundation, during implementation, some initial setups need to be done in the AI Foundation platform. For more details, see the Oracle Retail Analytics and Planning Integration 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, LOCATION, and CHANNEL. |
PROD_HIER_LEVEL |
Varchar2(30) |
The product hierarchy level the data is for CMP, DIV, GRP, DEPT, CLS, SBC, STYLE, STYLE_COLOR, and ITEM. |
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 APCS template version and their mappings.
Item Level Forecasts Mapping
The following table shows the mapping for pre-season and in-season Item Level Forecasts.
Table Column | Mapping for Pre-Season (MPP) | Mapping for In-Season (MPI) |
---|---|---|
CAL_HIER_LEVEL |
Fiscal Week |
Fiscal Week |
LOC_HIER_LEVEL |
LOCATION |
LOCATION |
PROD_HIER_LEVEL |
ITEM |
ITEM |
FCST_DATE_FROM |
WEEK |
WEEK |
LOC_EXT_KEY |
STOR |
STOR |
PROD_EXT_KEY |
SKU |
SKU |
CUSTSEG_EXT_KEY |
NULL |
NULL |
FCST_TYPE |
NPI |
PI |
REG_PR_SLS_QTY |
FCTYFCPMU |
FCTYFCIMU |
REG_PR_SLS_AMT |
FCTYFCPMR |
FCTYFCIMR |
Sub-Class Level Forecasts Mapping
The following table shows the mapping for pre-season Sub-Class Level Forecasts.
Table Column | Mapping for Pre-Season (MTP) |
---|---|
CAL_HIER_LEVEL |
Fiscal Week |
LOC_HIER_LEVEL |
LOCATION |
PROD_HIER_LEVEL |
SBC |
FCST_DATE_FROM |
WEEK |
LOC_EXT_KEY |
STOR |
PROD_EXT_KEY |
SCLS |
CUSTSEG_EXT_KEY |
NULL |
FCST_TYPE |
NPI |
REG_PR_SLS_QTY |
FCDVSlS1U |
REG_PR_SLS_AMT |
FCDVSLS1R |
Import DT Parameters from AI Foundation
APCS uses Demand Transference (DT) from AI Foundation to suggest and optimize the assortments. DT is based on Item attributes, Attribute Weights, Functional Fit for Attributes, Assortment Elasticity, and Rate of Sale of Items. Item Attributes and Rate of Sale of item are available from RI interfaces. Other DT parameters such as Attribute Weights, Functional Fit for Attributes, and Assortment Elasticity are interfaced from AI Foundation through RAP integration.
In order to get DT parameters from AI Foundation, during implementation, some initial setups need to be done in the AI Foundation platform. AI Foundation needs a customer segment to be defined for DT interfaces. AI Foundation can use multiple customer segments but AP is not using Customer Segment, so the customer should only set one dummy customer segment in AI Foundation for this DT integration. For more details, see the Oracle Retail Analytics and Planning Integration Implementation Guide.
The following table shows the interface table column details from AI Foundation in RDX used for the interface and the corresponding mapping of columns in APCS. Only mapped columns are used by the APCS template version. If RAP integration is enabled and if Enable RSE DT Integration is set to true, then this interface will run as part of the weekly batch.
Interface Name: RSE_ASSORT_ELASTICITY_EXP
Table Column | Data Type | Comments | Dimension / Measure / Value Mapping |
---|---|---|---|
RUN_ID |
Number(10) |
The export Run ID as obtained from the RAP_INTF_UTIL. |
|
LOC_HIER_LEVEL |
Varchar2(30) |
The location hierarchy level data is for COMPANY, CHAIN, AREA, REGION, DISTRICT, and LOCATION. |
AREA |
PROD_HIER_LEVEL |
Varchar2(30) |
The product hierarchy level the data is for CMP, DIV, GRP, DEPT, CLS, SBC, STYLE, STYLE_COLOR, and ITEM. |
SBC |
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). |
CHNL |
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). |
SCLS |
CUSTSEG_EXT_KEY |
Varchar2(80) |
The external id of the customer segment. |
|
ASSORT_ELASTICITY |
Number(38,20) |
The assortment elasticity which DT has calculated. |
DRTYASSRTELASV |
EFFECTIVE_DT_FROM |
Date |
The date when this data was activated. |
Interface Name: RSE_ASSORT_ATTR_WGT_EXP
Table Column | Data Type | Comments | Dimension / Measure / Value Mapping |
---|---|---|---|
RUN_ID |
Number(10) |
The export Run ID as obtained from the RAP_INTF_UTIL. |
|
LOC_HIER_LEVEL |
Varchar2(30) |
The location hierarchy level data is for COMPANY, CHAIN, AREA, REGION, DISTRICT, and LOCATION. |
AREA |
PROD_HIER_LEVEL |
Varchar2(30) |
The product hierarchy level the data is for CMP, DIV, GRP, DEPT, CLS, SBC, STYLE, STYLE_COLOR, and SKU. |
SBC |
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). |
CHNL |
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). |
SCLS |
CUSTSEG_EXT_KEY |
Varchar2(80) |
The external id of the customer segment. It will be NULL if not applicable. |
|
PROD_ATTR_GRP_EXT_KEY |
Varchar2(80) |
The external ID for the product attribute. |
PATT |
ATTR_WGT |
Number(38,20) |
The attribute weight. |
DRTYATTRWGTV |
FUNC_ATTR_FLG |
Varchar2(1) |
Y/N Flag indicating this is a functional attribute. A functional attribute is one which fits a specific purpose and cannot be substituted by other products with other values for this attribute. |
DRTYFUNCFITB |
Import Location Clusters from AI Foundation
Location Clusters can be defined in AI Foundation and can be interfaced to APCS. The APCS template version also supports defining Location Clusters within the application. AI Foundation allows defining clusters at different levels across the product hierarchy but the APCS template version allows interfacing clusters defined at the department level. Location Clusters are defined for a date range and those date ranges can be defined as Assortment Periods in APCS and the same can be exported to AI Foundation to define the Location Clusters. For more details, see Export Assortment Periods for Location Clustering to AI Foundation.
In order to get location clusters from AI Foundation, during implementation, some initial setups need to be done in the AI Foundation platform. For more details, see the Oracle Retail Analytics and Planning Integration Implementation Guide.
The following table shows the interface table column details from AI Foundation in RDX used for the interface and the corresponding mapping of columns in APCS. Only mapped columns are used by the APCS template version. If RAP integration is enabled and if Enable RSE Cluster Integration is set to true, then this interface will run as part of the weekly batch.
Interface Name: RSE_LOC_CLUSTER_EXP
Table Column | Data Type | Comments | Dimension / Measure / Value Mapping |
---|---|---|---|
RUN_ID |
Number(10) |
The export Run ID as obtained from the RAP_INTF_UTIL. |
|
LOC_HIER_LEVEL |
Varchar2(30) |
The location hierarchy level data is for COMPANY, CHAIN, AREA, REGION, DISTRICT, and LOCATION. |
LOCATION |
PROD_HIER_LEVEL |
Varchar2(30) |
The product hierarchy level the data is for CMP, DIV, GRP, DEPT, CLS, SBC, STYLE, STYLE_COLOR, and ITEM. |
DEPT |
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). |
STOR |
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). |
DEPT |
EFF_START_DT |
Date |
The starting date which the cluster is effective. |
WEEK, DRDVSRTD |
EFF_END_DT |
Date |
The ending date for which the cluster is effective. |
DRDVENDD |
CLUSTER_ID |
Number(10) |
The identifier for the cluster. |
DRDVSTRCLUST |
CLUSTER_LABEL |
Varchar2(50) |
A descriptive name/label for the cluster. |
DRDVSTRCLUSL |
Import Size Profiles from AI Foundation
Size Profiles can be defined in AI Foundation and can be interfaced to APCS. The APCS template version uses Size Profiles defined from AIF or can use the size profiles set at the Admin level within the application. The APCS template version imports size profiles from AIF at the sub-class level. It is used to define Buy Quantity by Size and Receipts by Sizes while planning them.
In order to get Size Profiles from AI Foundation, during implementation, some initial setups need to be done in the AI Foundation platform. For more details, see the Oracle Retail Analytics and Planning Integration Implementation Guide.
The following table shows the interface table column details from AI Foundation in RDX used for the interface and the corresponding mapping of columns in APCS. Only mapped columns are used by the APCS template version. For complete details of all available columns, see the Oracle Retail Analytics and Planning Integration Implementation Guide. If RAP integration is enabled and Enable RSE Size Profile Integration is set to true, then this interface will run as part of the weekly batch. If integration is enabled, it will use the same interface table data to also load the Size Hierarchy file (SIZH) using the internal view VW_SIZH_HIER and then also load the Size Profile.
Interface Name: RSE_SIZE_PROFILE_EXP
Table Column | Data Type | Comments | Dimension / Measure / Value Mapping |
---|---|---|---|
RUN_ID |
Number(10) |
The export Run ID as obtained from the RAP_INTF_UTIL. |
|
LOC_LEVEL_NAME |
Varchar2(255) |
The location hierarchy level data is for COMPANY, CHAIN, AREA, REGION, DISTRICT, and LOCATION. |
LOCATION |
PROD_LEVEL_NAME |
Varchar2(255) |
The product hierarchy level the data is for CMP, DIV, GRP, DEPT, CLS, SBC, STYLE, and STYLE_COLOR. |
SBC |
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). |
STOR |
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). |
SCLS |
SIZE_ID |
Number(10) |
Size Identifier |
SIZD |
SIZE_PCT_UNITS |
Number(10,5) |
Size Profile Percentage |
DRDVSIZE |
USED_BY_AIP |
Varchar2(1) |
Flag to indicate that record is for AIP Y |
PRFP |
SIZE_LABEL |
Varchar2(255) |
Size Label |
SIZD_LABEL |
SIZE_GROUP_ID |
Varchar2(255) |
Size Group Identifier |
SRNG |
SIZE_GROUP_LABEL |
Varchar2(255) |
Size Group Label |
SRNG_LABEL |
Note:
For Size Hierarchy (sizh), the internal view (VW_SIZE_HIER) is defined against the AIF RSE_SIZE_PROFILE_EXP table to get the unique sizes (SIZE_ID, SIZE_LABEL) and size ranges (SIZE_GROUP_ID and SIZE_GROUP_LABEL) which are marked to be used AP (USED_BY_AIP flag as ‘Y’).
Export Assortment Periods for Location Clustering to AI Foundation
In the APCS template version, the customer can define the Assortment periods at pre-defined product levels (DEPT). Assortment Periods are date ranges to plan the assortments; it can vary for different product levels. The customer can export the defined Assortment Periods by enabling the Boolean measure Export Period for Clustering at the Assortment Period level.
In order to import and use this data from AI Foundation, during implementation, some initial setups need to be done in the AI Foundation platform. For more details, see the Oracle Retail Analytics and Planning Integration Implementation Guide.
The following table shows the interface tables and column details from APCS in RDX used for the interface and the corresponding mapping of columns in APCS. Only mapped columns are used by the APCS template version. If RAP integration is enabled and if Enable RSE Cluster Integration is set to true, then this interface will run as part of the weekly batch.
Interface Name: AP_ASSORT_PERIOD_EXP
Table Column | Data Type | Comments | Dimension / Measure / Value Mapping |
---|---|---|---|
RUN_ID |
Number(10) |
The export Run ID as obtained from the RAP_INTF_UTIL. |
|
PROD_LEVEL |
Varchar2(80) |
The product hierarchy level the data is for CMP, DIV, GRP, DEPT, CLS, SBC, STYLE, STYLE_COLOR, and SKU. |
DEPT |
PROD_KEY |
Varchar2(80) |
Product Identifier |
DEPT |
LOC_LEVEL |
Varchar2(80) |
The location hierarchy level data is for COMPANY, CHAIN, AREA, REGION, DISTRICT, and LOCATION. |
COMPANY |
LOC_KEY |
Varchar2(80) |
Location Identifier |
COMP |
ASSORT_PERIOD_KEY |
Varchar2(80) |
Assortment Period Key |
BPER |
ASSORT_PERIOD_START_DATE |
Date |
Assortment Period Start Date |
BCDVSRTD |
ASSORT_PERIOD_END_DATE |
Date |
Assortment Period End Date |
BCDVENDD |
EXT_NAME |
Varchar2(80) |
External Cluster Name |
BCDVCLUSTERT |
CLUSTER_DESCR |
Varchar2(255) |
Cluster Description |
BCDVPRDL |
Export Active Assortments to AI Foundation
In the APCS template version, the customer can plan active assortments for an assortment period and those details can be exported to AI Foundation at the Item/Store level.
In order to import and use this data from AI Foundation, during implementation, some initial setups need to be done in the AI Foundation platform. For more details, see the Oracle Retail Analytics and Planning Integration Implementation Guide.
The following table shows the interface tables and column details from APCS in RDX used for the interface and the corresponding mapping of columns in APCS. Only mapped columns are used by the APCS template version. If RAP integration is enabled, then this interface will run as part of the weekly batch.
Interface Name: AP_ACTIVE_ASSORT_EXP
Table Column | Data Type | Comments | Dimension / Measure / Value Mapping |
---|---|---|---|
RUN_ID |
Number(10) |
The export Run ID as obtained from the RAP_INTF_UTIL. |
|
PROD_KEY |
Varchar2(80) |
Product Identifier |
SKU |
LOC_KEY |
Varchar2(80) |
Location Identifier |
STOR |
PROD_LEVEL |
Varchar2(80) |
The product hierarchy level the data is for CMP, DIV, GRP, DEPT, CLS, SBC, STYLE, STYLE_COLOR, and SKU. |
ITEM |
LOC_LEVEL |
Varchar2(80) |
The location hierarchy level data is for COMPANY, CHAIN, AREA, REGION, DISTRICT, and LOCATION. |
LOCATION |
ACTIVE_START_DATE |
Date |
Active Assortment Start Date |
BPDBSRTD |
ACTIVE_END_DATE |
Date |
Active Assortment End Date |
BPDBENDD |
Export Assortment Plans to Retail Insights
Approved plans from APCS can be exported to Retail Insights within RAP integration. The APCS template version allows creating and exporting plans at the week/store/item level for both OP and CP versions. Plans defined at the week/store/style-color level are evenly spread to the week/store/item level before this exports in the batch process. Plans for different versions can be exported to Retail Insights on a weekly basis. It exports all the approved plans for the un-elapsed periods. With the AP non-template version, customers can create a different level of plans and they can also configure various metrics. The interface staging table in Retail Sights contains more metrics columns and various flex columns. The customer can update and configure the interface.cfg mappings to export additional columns that can be used by Retail Insights.
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 APCS template version.
This plan export is for exporting both the OP and CP versions of approved Assortment 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.
Interface Name: AP_PLAN1_EXP
Staging Table Column | Description | Dimension/Measure Mapping |
---|---|---|
PROD_KEY |
Product Dimension |
sku |
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 |
IEOPPWKED |
VERSION_NUM |
Version Number |
0 |
PROD_LEVEL |
Product Level |
ITEM |
LOC_LEVEL |
Location Level |
LOCATION |
SLS_QTY |
Op Sales Reg+Promo U |
IEOPSLS1U |
SLS_RTL_AMT |
Op Sales Reg+Promo R |
IEOPSLS1R |
SLS_COST_AMT |
Op Sales Reg+Promo C |
IEOPSLS1C |
EOH_COST_AMT |
Op EOP C |
IEOPEOPC |
EOH_RTL_AMT |
Op EOP R |
IEOPEOPR |
EOH_QTY |
Op EOP U |
IEOPEOPU |
INVRC_COST_AMT |
Op Receipts C |
IEOPRCPTC |
INVRC_RTL_AMT |
Op Receipts R |
IEOPRCPTR |
INVRC_QTY |
Op Receipts U |
IEOPRCPTU |
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 AP 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 the weekly batch using JOS/POM.