B Appendix: RMFCS Integration

Assortment Planning Cloud Service supports integration with Oracle Retail Merchandising Foundation Cloud Service (RMFCS). If a retailer has RMFCS as the source system for inventory and transactional data, they can readily integrate to get foundation hierarchy data, inventory, and transactional data from RMF Cloud Service. This appendix provides details on the list of hierarchies and data files that are integrated with Oracle Retail Merchandising Foundation Cloud Service.

Integration Assumptions

Following is a list of requirements/assumptions for integration with RMFCS:

• APCS Channel is mapped to RMFCS Area.

• Warehouses are sent to APCS as locations. RMFCS Warehouses holds inventory and on-order data. RMFCS Warehouses needs to be mapped to APCS Channels within APCS in order to get the true picture of inventory and on-order data.

• RMFCS sends hierarchy and data files on a weekly basis.

• RMFCS sends the data at the item level, so APCS needs to be implemented with the lowest data load intersection as item.

• RMFCS sends all data based on only one primary currency. It also sends currency conversion rates for different currencies. That can be used within APCS to plan and view data on different local currencies.

Integrated Hierarchy and Data Files

RMFCS sends the following sets of hierarchy and data files. All RMFCS files are copied to the common cloud service share location from where APCS can pick up the files for further processing. Interface files need to be transformed to format the files into the APCS required format to filter columns not used and split and create different group of files as needed by APCS. The APCS Configured Batch Process will transform the files to the APCS required formats, rename the final files as needed by APCS, and copy them to the domain input location for subsequent hierarchy or measure load processing. For each of the hierarchy and data files, RMFCS also sends a trigger file with the same name as the original file with the extension .complete. The Assortment Planning Cloud Service batch process waits for the trigger files to start processing the corresponding data or hierarchy file. After processing, it deletes those trigger files.

Hierarchy Files

The following hierarchy files are provided by RMFCS and need to be transformed to the APCS format before loading them into IP Cloud Service. For each hierarchy file, RMFCS only sends the base and alternate dimensions of the hierarchies and not the user alternate rollups.

Calendar Hierarchy - rms_clnd.csv.dat

RMFCS sends the calendar hierarchy file without calendar labels. Calendar labels are added in the weekly batch process by calling the APCS custom script (ra_custom) with the parameter rms_clnd_label, which adds the calendar labels. If a customer wants to use different calendar labels, they can load their preferred calendar labels using generic hierarchy position translations enabled for the calendar hierarchy.

RMFCS sends the calendar hierarchy file with dates for five years. Based on the current date, two year historical dates, current year dates, and two future year dates.

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process.

Field Name	Field Description	APCS Mapping
Day	The date from which the 4-5-4 data was derived, in YYYYMMDD format.	day
Week	The end of week date for the day, in YYYYMMDD format.	week
Month	The 4-5-4 month of the year, valid values 1 to 12.	mnth
Quarter	The 4-5-4 quarter of the year, valid values 1 to 4.	qtr
Half	The 4-5-4 half of the year, valid values 1 or 2.	half
Year	The 4-5-4 year.	year
week_of_year	The 4-5-4 week of the year, valid values 1 to 53.	woyr
day_of_week	The day number within the week, valid values 1 to 7.

Product Hierarchy - rms_prod.csv.dat

RMFCS exports all sellable and inventoried items.

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process. Some measure data files are also extracted and loaded from this RMFCS product hierarchy file.

Field Name	Functional Name	APCS Mapping
ITEM	Item ID	sku
ITEM_DESC	Item Description	sku_label
ITEM_PARENT_DIFF	Parent/Diff ID	skup
ITEM_PARENT_DIFF_DESC	Parent/Diff Description	skup_label
ITEM_PARENT	Parent ID	skug
ITEM_PARENT_DESC	Parent Description	skug_label
SUBCLASS_ID	Subclass ID	scls
SUB_NAME	Subclass Name	scls_label
CLASS_ID	Class ID	clss
CLASS_NAME	Class Name	clss_label
DEPT	Department	dept
DEPT_NAME	Department Name	dept_label
GROUP_NO	Group	pgrp
GROUP_NAME	Group Name	pgrp_label
DIVISION	Division	dvsn
DIV_NAME	Division Name	dvsn_label
COMPANY	Company	cmpp
CO_NAME	Company Name	cmpp_label
FORECAST_IND	Forecastable Item flag
CLASS_DISPLAY_ID	Class Display ID	drtyclsst
SUBCLASS_DISPLAY_ID	Subclass Display ID	drtysclst
BRAND_NAME	Brand ID	drdvprdatt
BRAND_DESCRIPTION	Brand Description
SUPPLIER	Supplier Site	drdvprdatt
SUP_NAME	Supplier Name
DIFF_TYPE1	Diff Type1
DIFF_ID1	Diff 1	drdvprdatt
DIFF_TYPE2	Diff Type2
DIFF_ID2	Diff 2	drdvprdatt
DIFF_TYPE3	Diff Type3
DIFF_ID3	Diff 3	drdvprdatt
DIFF_TYPE4	Diff Type4
DIFF_ID4	Diff 4	drdvprdatt

Location Hierarchy - rms_loc.csv.dat

RMFCS Area will be exported as APCS Channel.

All Virtual Warehouses in RMFCS will be exported as unique locations.

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process.

Field Name	Functional Name	APCS Mapping
LOCATION	Location ID	stor, strc
LOC_NAME	Location Name	stor_label,strc_label
DISTRICT	District ID	dstr
DISTRICT_NAME	District Name	dstr_label
REGION	Region ID	regn, tdar
REGION_NAME	Region Name	regn_label, tdar_label
AREA	Area ID	chnl, chnc
AREA_NAME	Area Name	chnl_label, chnc_label
CHAIN	Chain ID	chan
CHAIN_NAME	Chain Name	chan_label
COMPANY	Company ID	comp
CO_NAME	Company Name	comp_label
COMPANY_CURRENCY	Primary Currency
LOC_TYPE	Location Type	loct
LOC_TYPE_NAME	Location Type Description	loct_label
PHYSICAL_WH	Physical WH ID	phwh
PHYSICAL_WH_NAME	Physical WH Name	phwh_label
CHANNEL_ID	WH Channel ID	fflt
CHANNEL_NAME	WH Channel Name	fflt_label
STORE_CLASS	Store Class
STORE_CLASS_DESCRIPTION	Store Class Description
STORE_FORMAT	Store Format
STORE_FORMAT_NAME	Store Format Name

Currency Hierarchy - rms_curr.csv.ovr

The Currency conversion rate data file from RMFCS will be transformed to be loaded as a Currency Hierarchy file to load the unique currency codes.

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process.

Field Name	Functional Name	APCS Mapping
EFFECTIVE_DATE	Effective Date
FROM_CURRENCY_CODE	From Currency
TO_CURRENCY_CODE	To Currency	curc, curc_label
EXCHANGE_TYPE	Exchange Rate Type
EXCHANGE_RATE	Exchange Rate

Product Attribute Hierarchy - rms_uda.csv.dat, rms_brand.csv.dat, rms_vendor.csv.dat, rms_diff.csv.dat

UDA, Suppliers (Vendor), Brand, and Diffs in RMFCS are treated as product attributes in Item Planning. RMFCS provides them as individual files in the same format; those will be merged and loaded as one product attribute file. UDA_VALUE (Product Attribute Values) are not unique across UDA_ID in RMFCS. While transforming and loading as a product attribute, UDA_ID position will be concatenated with UDA_VALUE using an underscore ("_").

The following table contains the list of column contents from the file.

Field Name	Functional Name	APCS Mapping
UDA_ID	UDA	patt
UDA_DESC	UDA Description	patt_label
UDA_VALUE	UDA Value	patv
UDA_VALUE_DESC	UDA Value Description	patv_labael

Data Files

The following data files are provided by RMFCS and need to be transformed to the APCS format before loading them into IP Cloud Service. All RMFCS files are transformed first and loaded together using the single Load Task Load - RMS Interface Data.

Inventory Data File - rms_inv.csv.ovr

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process.

Field Name	Functional Name	APCS Mapping
EOW_DATE	End of week date	week
ITEM	Item ID	sku
LOCATION	Location ID	stor
LOC_TYPE	Location Type
CLEAR_IND	Clearance flag
REGULAR_INVENTORY_UNITS	Inventory Units	drtyeopu
REGULAR_INVENTORY_COST	Inventory Cost	drtyeopc
REGULAR_INVENTORY_RETAIL	Inventory Retail	drtyeopr
UNIT_COST	Unit Cost	drtyslsprcc
AV_COST	Average Cost
UNIT_RETAIL	Unit Retail	drtyslsprcr

Transaction Data File - rms_tran.csv.ovr

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process. RMFCS sends Net Sales in all regular, promotion, and clearance buckets. But APCS GA also needs Gross Sales. After loading, additional batch calculations, which run as part of RMFCS batch transforms, calculate those required measures. Also, RMFCS sends multiple type transaction details specific to receipts; batch calculations will calculate the effective receipts data.

Field Name	Functional Name	APCS Mapping
EOW_DATE	End of Week Date	week
ITEM	Item ID	sku
LOCATION	Location ID	stor
LOC_TYPE	Location Type
CLEAR_IND	Clearance flag
STANDARD_UOM	Standard UOM
CURRENCY_CODE	Currency Code
NET_SALES_REG_UNITS	Net Sales Units - Reg	drtynslsregu
NET_SALES_REG_COST	Net Sales Cost - Reg	drtynslsregc
NET_SALES_REG_RETAIL	Net Sales Retail - Reg	drtynslsregr
NET_SALES_PROMO_UNITS	Net Sales Units - Promo	drtynslsprou
NET_SALES_PROMO_COST	Net Sales Cost - Promo	drtynslsproc
NET_SALES_PROMO_RETAIL	Net Sales Retail - Promo	drtynslspror
NET_SALES_CLEAR_UNITS	Net Sales Units - Clear	drtynslsclru
NET_SALES_CLEAR_COST	Net Sales Cost - Clear	drtynslsclrc
NET_SALES_CLEAR_RETAIL	Net Sales Retail - Clear	drtynslsclrr
NET_SALES_REG_RETAIL_VAT_EXCL	Net Sales (VAT Exclusive) Retail - Reg
NET_SALES_PROMO_RETAIL_VAT_EXCL	Net Sales (VAT Exclusive) Retail - Promo
NET_SALES_CLR_RETAIL_VAT_EXCL	Net Sales (VAT Exclusive) Retail - Clear
RETURNS_REG_UNITS	Returns Units - Reg	drtyrtnregu
RETURNS_REG_COST	Returns Cost - Reg	drtyrtnregc
RETURNS_REG_RETAIL	Returns Retail - Reg	drtyrtnregr
RETURNS_PROMO_UNITS	Returns Units - Promo	drtyrtnprou
RETURNS_PROMO_COST	Returns Cost - Promo	drtyrtnproc
RETURNS_PROMO_RETAIL	Returns Retail - Promo	drtyrtnpror
RETURNS_CLEAR_UNITS	Returns Units - Clear	drtyrtnclru
RETURNS_CLEAR_COST	Returns Cost - Clear	drtyrtnclrc
RETURNS_CLEAR_RETAIL	Returns Retail - Clear	drtyrtnclrr
REG_MARKDOWN_RETAIL	Regular Markdown
PROMO_MARKDOWN_RETAIL_REG	Promotion Markdown (Regular)
PROMO_MARKDOWN_RETAIL_CLEAR	Promotion Markdown (Clearance)
CLEAR_MARKDOWN_RETAIL	Clearance Markdown
WF_MARKDOWN_RETAIL	Franchise Markdown
WF_MARKUP_RETAIL	Franchise Markup
SHRINK_UNITS	Shrink Units
SHRINK_COST	Shrink Cost
SHRINK_RETAIL	Shrink Retail
DEAL_INCOME_SALES	Deal Income Sales Based
PO_RECEIPT_UNITS	PO Receipt Units	drtyporcptu
PO_RECEIPT_COST	PO Receipt Cost	drtyporcptc
PO_RECEIPT_RETAIL	PO Receipt Retail	drtyporcptr
NON_SHRINK_ADJ_UNITS	Non-Shrink Adjustments Units
NON_SHRINK_ADJ_COST	Non-Shrink Adjustments Cost
NON_SHRINK_ADJ_RETAIL	Non-Shrink Adjustments Retail
DEAL_INCOME_PURCHASES	Deal Income Purchases Based
MARKUP	Markup	drtymkupr
MARKDOWN_CANCEL	Markdown Cancel	drtymkdcanr
INTERCOMPANY_MARKUP	Intercompany Markup	drtyicmkur
INTERCOMPANY_MARKDOWN	Intercompany Markdown	drtyicmkdr
RTV_UNITS	RTV Units
RTV_COST	RTV Cost
RTV_RETAIL	RTV Retail
TSF_IN_UNITS	Transfer/Allocation Inbound Units	drtytraninu
TSF_IN_COST	Transfer/Allocation Inbound Cost	drtytraninc
TSF_IN_RETAIL	Transfer/Allocation Inbound Retail	drtytraninr
TSF_IN_UNITS_BOOK	Book Transfer/Allocation Inbound Units	drtytraninbu
TSF_IN_COST_BOOK	Book Transfer/Allocation Inbound Cost	drtytraninbc
TSF_IN_RETAIL_BOOK	Book Transfer/Allocation Inbound Retail	drtytraninbr
TSF_OUT_UNITS	Transfer/Allocation Outbound Units	drtytranoutu
TSF_OUT_COST	Transfer/Allocation Outbound Cost	drtytranoutc
TSF_OUT_RETAIL	Transfer/Allocation Outbound Retail	drtytranoutr
TSF_OUT_UNITS_BOOK	Book Transfer/Allocation Outbound Units	drtytranoutbu
TSF_OUT_COST_BOOK	Book Transfer/Allocation Outbound Cost	drtytranoutbc
TSF_OUT_RETAIL_BOOK	Book Transfer/Allocation Outbound Retail	drtytranoutbr
RECLASS_IN_UNITS	Reclass In Units
RECLASS_IN_COST	Reclass In Cost
RECLASS_IN_RETAIL	Reclass In Retail
RECLASS_OUT_UNITS	Reclass Out Units
RECLASS_OUT_COST	Reclass Out Cost
RECLASS_OUT_RETAIL	Reclass Out Retail
TSF_IN_UNITS_ICT	Intercompany Transfer/Allocation Inbound Units	drtytraniniu
TSF_IN_COST_ICT	Intercompany Transfer/Allocation Inbound Cost	drtytraninic
TSF_IN_RETAIL_ICT	Intercompany Transfer/Allocation Inbound Retail	drtytraninir
TSF_OUT_UNITS_ICT	Intercompany Transfer/Allocation Outbound Units	drtytranoutiu
TSF_OUT_COST_ICT	Intercompany Transfer/Allocation Outbound Cost	drtytranoutir
TSF_OUT_RETAIL_ICT	Intercompany Transfer/Allocation Outbound Retail	drtytranoutic
INTERCOMPANY_MARGIN	Intercompany Margin
TSF_RECEIPT_UNITS	Transfer/Allocation Receipt Units
TSF_RECEIPT_COST	Transfer/Allocation Receipt Cost
TSF_RECEIPT_RETAIL	Transfer/Allocation Receipt Retail
RTV_RESTOCK_FEE	RTV Restocking Fee
FRANCHISE_SALES_UNITS	Franchise Sales Units	drtywfslsu
FRANCHISE_SALES_COST	Franchise Sales Cost	drtywfslsc
FRANCHISE_SALES_RETAIL	Franchise Sales Retail	drtywfslsr
FRANCHISE_RETURNS_UNITS	Franchise Returns Units	drtywfrtnu
FRANCHISE_RETURNS_COST	Franchise Returns Cost	drtywfrtnc
FRANCHISE_RETURNS_RETAIL	Franchise Returns Retail	drtywfrtnr
FRANCHISE_RESTOCK_FEE	Franchise Restocking Fee

On Order Data File - rms_oo.csv.ovr

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process.

Field Name	Functional Name	APCS Mapping
EOW_DATE	End of week date	week
ITEM	Item ID	sku
LOCATION	Location ID	stor
LOC_TYPE	Location Type
CLEAR_IND	Clearance flag
ON_ORDER_UNITS	On Order Units	drtyoou
ON_ORDER_COST	On Order Cost	drtyooc
ON_ORDER_RETAIL	On Order Retail	drtyoor

Currency Conversion Rate File - rms_curr.csv.ovr

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process.

Field Name	Functional Name	APCS Mapping
EFFECTIVE_DATE	Effective Date	day
FROM_CURRENCY_CODE	From Currency
TO_CURRENCY_CODE	To Currency	curc
EXCHANGE_TYPE	Exchange Rate Type
EXCHANGE_RATE	Exchange Rate	drtylcratex

Location File - rms_store.csv.ovr

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process.

Field Name	Functional Name	APCS Mapping
STORE	Store ID	stor
STORE_NAME	Store Name
DISTRICT	District ID
STORE_CLOSE_DATE	Store Close Date	addvlocendd
STORE_OPEN_DATE	Store Open Date
REMODEL_DATE	Remodel Date	addvlocrefd
STORE_CLASS	Store Class
STORE_CLASS_DESCRIPTION	Store Class Description
STORE_FORMAT	Store Format
STORE_FORMAT_NAME	Store Format Name
CURRENCY	Currency Code
STORE_TYPE	Store Type	addvwfpoct
STOCKHOLDING_IND	Stockholding

Item Attribute Mapping File - prdattt.csv.ovr

The following table contains the list of column contents from the file. Only required columns for APCS are filtered by the transform process. This file only contains Product Attribute mappings for UDA attributes. Item Attribute mappings for Brand, Supplier, and Diffs are derived and loaded from the RMFCS Product file.

Field Name	Functional Name	APCS Mapping
ITEM	Item ID	sku
UDA_ID	UDA ID	patt
UDA_DESC	UDA Description
UDA_VALUE	UDA Value	drdvprdatt
UDA_VALUE_DESC	UDA Value Description
FORECAST_IND	Forecastable Item

Warehouse Transformation

In RMFCS, warehouses hold inventory, receipts, and on-order data that are not mapped to any specific channels. In APCS, plans are only created at the APCS Channel level. In order to get the full picture of inventory for a channel, each warehouse location needs to be mapped to a channel within APCS using the Warehouse Mapping workbook template in Location Setup or the mapping needs to be loaded to the measure addvchwhmapt.

After the mapping data is set, the weekly batch process will transform the loaded inventory, on-order, and receipt data for those warehouses to assigned channels. This can also be done by running the batch Online Administration Tool task Transform RMFCS Warehouse Data. The batch process will spread the Warehouse data at the channel level to the store level using the Warehouse to Store Spread Method which has options to use Location Plan, Location Sales, Actuals, and Actual Sales with the default option using Location Plan interfaced from MFPCS. The customer can change this option in the Batch Setup view in the Admin workbooks.

New Placeholder Item Integration

New Placeholder Items (Style/Color or Styles) created in APCS can be integrated with RMFCS. It involves multiple levels of process which are detailed below:

• First, the RMFCS Server should be connected with APCS during deployment, after mapping the dimensions (style/color, style) to item in RMFCS. New Placeholder items created in APCS will directly use the reserved item numbers from RMFCS. For more details, refer to the RPASCE-RMS Integration for Item Management - Slow Item Build section in the Oracle Retail Predictive Application Server Cloud Edition Administration Guide. The customer should ensure that max_item_resv_qty and max_item_expiry_days are set on the RMFCS DB side to allow reserving of items from the RMFCS side.

• The customer can then create new placeholder items within APCS and assign attributes to them. RMFCS does not contain style/color as a separate level, so an attribute should be selected as the RMFCS Color attribute in the Product Setup view in Planning Administration. All new style/colors created should be assigned with a unique color attribute. The customer should then select the style/color ready for Export to RMFCS. The process only allows for creating new placeholder items and assigning initial attributes. Any further updates/maintenance of the item or RMFCS specific item attributes should be managed in the RMFCS side.

• The customer can run the Export to RMF CS custom menu which exports all items marked for Export to RMFCS in real time to the RMFCS Item Induction process. It exports new item details, and its assigned attributes (UDA).

• In batch, the customer needs to run Rename Style/Color that renames the style/color positions created as style concatenated with a color attribute as it will be identified in the RMFCS product hierarchy file. This process is scheduled to run as part of weekly batch before the hierarchy load process.

• Once RMFCS approves the newly interfaced items, the same items will be coming in the product hierarchy file as regular items. Since it will use the same position names for Item and Style/Color, the standard hierarchy load process in the APCS side will then formalize those informal placeholder Item and Style/Color.

Batch Tasks Specific to RMFCS Integration

The following sets of Online Administration Tools tasks are pre-configured in Configured Batch Tasks to support RMFCS integration. For more details about running the tasks, see the Oracle Retail Assortment Planning Cloud Service Administration Guide.

• Run Batch Task Group > Weekly Batch

  If Enable RMF CS Integration is set to true, the existing Weekly Batch task will run additional tasks to process the RMFCS files. It also runs the transformation of RMFCS Warehouse data as part of the weekly batch.

• Run Batch Task Group > Transform RMS Files

  This task only transforms all hierarchy and data files to the APCS required format, adds the header information, and copies the files to the input location so that any subsequent data or hierarchy load process can process the same data.

• Run Batch Task Group > Transform RMS Warehouse Data

  Whenever there is any change in the warehouse mapping, users can schedule this task to transform the RMFCS Warehouse data to channels for inventory, order, and receipts. This task also gets called as part of the Weekly Batch with RMS.

• Run Batch Task Group > Set RMS Dimension Mapping

  RMFCS can be linked with the APCS domain in real time to get the reserved item numbers for new placeholder items created. In order to do that, connections between the RMFCS servers and RPAS server needs to be set up during installation time. For more details about those setups, see the Oracle Retail Predictive Application Server Cloud Edition Administration Guide. Once those are set, running this task will map the sku (Item) and skup (style/color) dimensions to Item in RMFCS. Once this mapping is done, any new place holder sku or skup created will use the reserved RMFCS item numbers directly from RMFCS.

• Run Batch Task Group > Rename Style/Color

  This task renames the Style/Color DPM positions which are exported to RMFCS, with Style concatenated with the Color Attribute Id, as it comes in the Product Hierarchy Interface.