Import Foundation and Transactional Data from 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
Location Attributes Hierarchy	VW_SATR_HIER	Hierarchy Importer
Customer Segment Hierarchy	W_PDS_CUSTSEG_D	Hierarchy Importer
Location Attributes	W_PDS_ORG_ATTR_STR_D	Data Importer
Replenishment Data	W_PDS_REPL_ATTR_IT_LC_D	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
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
csgh	csvd	W_PDS_CUSTSEG_D	CUSTSEG_ID
csgh	csvd_label	W_PDS_CUSTSEG_D	CUSTSEG_NAME
csgh	csgd	W_PDS_CUSTSEG_D	CUSTSEG_ID
csgh	csgd_label	W_PDS_CUSTSEG_D	CUSTSEG_NAME

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. CLND_TYPE can be set to 'F' (Fiscal) or 'G' (Gregorian) or 'B' (Both) based on the type of calendar the customer wants to use for the calendar date range in PDS. It is defaulted to Both, in order to include the calendar range of both Fiscal and Gregorian Calendar start and end dates by default.

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. PDS Integration variables PATR_BRAND, PATR_SUPP are defaulted with values ‘brnd’ and ‘supp’, but those can be changed if the customer wants to use different attribute names. PDS Integration variable ‘PATR_OTHER’ can be enabled to ‘Y’ (default is set ‘N’) to include the ‘Z_Other’ attribute value for all the attributes that are used by AP GA.

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. PDS Integration variable ‘SATR_GROUP’ can be enabled to ‘Y’ (default is set to ‘N’) to include the Sales Performance Group and Space Group Location Attributes used by AP GA.

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
drdvskuimgt	VW_PROD_DATA	PRODUCT_IMAGE_NAME
drdvskuimgl	VW_PROD_DATA	PRODUCT_IMAGE_ADDR
drdvslsprcr	VW_PROD_DATA	INITIAL_ITEM_RETAIL
drdvslsprcc	VW_PROD_DATA	INITIAL_ITEM_COST
addvlocatt	W_PDS_ORG_ATTR_STR_D	ATTR_VALUE
drtyreplb	W_PDS_REPL_ATTR_IT_LC_D	REPL_ACTIVE_FLAG
drtyrcptd	W_PDS_INV_IT_LC_WK_A	FIRST_INVRC_DT
drtyslsprc	W_PDS_INV_IT_LC_WK_A	UNIT_COST
drtyslsprr	W_PDS_INV_IT_LC_WK_A	UNIT_RETAIL

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 such as Image details, RMF CS Unique Class, and Sub-Class Ids.

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.

Note: If the customer wants to use position filtering to filter a few products only in APCS in a multi-app environment, they can use the extensibility in GA, to set the Position Filter Measure property for product in Hierarchies to the measure DRDVAPFltSkuB and then can use any flex field for the product in W_PDS_PRODUCT_D to mark those items as ‘Y’ and interface that data to APCS_DRDVAPFltSkuB by changing the interface.cfg mapping for VW_PROD_DATA using extensibility guidelines.

Note: Pack Items are filtered by default for AP 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 and non-pack items.