D Appendix: RMF CS Integration
MFP Cloud Service supports integration with Oracle Retail Merchandising Foundation Cloud Service (RMF CS). If a retailer has RMF CS as the source system for inventory and transactional data, they can readily integrate to get foundation hierarchy data, inventory, and transactional data from RMF CS. This appendix provides details on the list of hierarchies and data files that are integrated with RMF CS.
RMF CS integration can be done using flat file integration if the customer is using a previous release of RMF CS which sends data to MFP as interface files. It can be integrated using RAP Data Exchange (RDX) in RAP integration which will directly import data into Planning Data Schema (PDS) without any file-based approach. The following sections cover details about file-based integration. For details about the RAP integration, see RAP Integration.
Integration Assumptions
Following is a list of requirements/assumptions for integration with RMF CS:
-
MFP Channel is mapped to RMF CS Area.
-
Warehouses are sent to MFP as locations. RMF CS Warehouses holds inventory and on-order data. RMF CS Warehouses needs to be mapped to MFP Channels within MFP in order to get the true picture of inventory and on-order data.
-
RMF CS sends hierarchy and data files on a weekly basis.
-
RMF CS sends the data at the item level, so MFP needs to be implemented with the lowest data load intersection as item.
-
RMF CS sends all data based on only one primary currency. It also sends currency conversion rates for different currencies. That can be used within MFP to plan and view data on different local currencies.
RMF CS Integration using File-Based Approach
RMF CS sends the following sets of hierarchy and data files. All RMF CS files are copied to the common cloud service share location from where MFP Cloud Service can pick up the files for further processing. Interface files need to be transformed to format the files into the MFP required format to filter columns not used and split and create different group of files as needed by MFP. MFP Configured Batch Process will transform the files to the MFP required formats, rename the final files as needed by MFP, and copy them to the input location for subsequent hierarchy or measure load processing. For each of the hierarchy and data files, RMF CS also sends a trigger file with the same name as the original file with the extension .complete. The MFP 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.
Any required data files that are not coming as part of the RMF CS interface need to be loaded in the weekly batch and grouped as load_non_rms in the batch control file. The data files will be loaded as part of the weekly batch.
Hierarchy Files
The following hierarchy files are provided by RMF CS and need to be transformed to the MFP format before loading them into MFP Cloud Service. For each hierarchy file, RMF CS only sends the base and alternate dimensions of the hierarchies and not the user alternate rollups.
Calendar Hierarchy - rms_clnd.csv.dat
RMF CS sends the calendar hierarchy file without calendar labels. Calendar labels are added in the weekly batch process by calling the MFP 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.
RMF CS 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 MFP are filtered by the transform process.
| Field Name | Field Description | MFP 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
RMF CS exports all sellable and inventoried items.
The following table contains the list of column contents from the file. Only required columns for MFP are filtered by the transform process.
| Field Name | Functional Name | MFP Mapping |
|---|---|---|
|
ITEM |
Item ID |
sku |
|
ITEM_DESC |
Item Description |
sku_label |
|
ITEM_PARENT_DIFF |
Parent/Diff ID |
|
|
ITEM_PARENT_DIFF_DESC |
Parent/Diff Description |
|
|
ITEM_PARENT |
Parent ID |
|
|
ITEM_PARENT_DESC |
Parent Description |
|
|
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 |
|
|
BRAND_DESCRIPTION |
Brand Description |
|
|
SUPPLIER |
Supplier Site |
|
|
SUP_NAME |
Supplier Name |
|
|
DIFF_TYPE1 |
Diff Type1 |
|
|
DIFF_ID1 |
Diff 1 |
|
|
DIFF_TYPE2 |
Diff Type2 |
|
|
DIFF_ID2 |
Diff 2 |
|
|
DIFF_TYPE3 |
Diff Type3 |
|
|
DIFF_ID3 |
Diff 3 |
|
|
DIFF_TYPE4 |
Diff Type4 |
|
|
DIFF_ID4 |
Diff 4 |
Location Hierarchy - rms_loc.csv.dat
RMF CS Area will be exported as MFP Channel.
All Virtual Warehouses in RMF CS will be exported as unique locations.
The following table contains the list of column contents from the file. Only required columns for MFP are filtered by the transform process.
| Field Name | Functional Name | MFP Mapping |
|---|---|---|
|
LOCATION |
Location ID |
stor |
|
LOC_NAME |
Location Name |
stor_label |
|
DISTRICT |
District ID |
dstr |
|
DISTRICT_NAME |
District Name |
dstr_label |
|
REGION |
Region ID |
regn |
|
REGION_NAME |
Region Name |
regn_label |
|
AREA |
Area ID |
chnl |
|
AREA_NAME |
Area Name |
chnl_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 RMF CS 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 MFP are filtered by the transform process.
| Field Name | Functional Name | MFP 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 |
Data Files
The following data files are provided by RMF CS and need to be transformed to the MFP format before loading them into MFP Cloud Service. All RMF CS files are transformed first and then converted into the required file format needed by the batch step that loads the data.
Inventory Data File - rms_inv.csv.ovr
The following table contains the list of column contents from the file. Only required columns for MFP are filtered by the transform process.
| Field Name | Functional Name | MFP 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 |
drtyeop1u/drtyeop2u |
|
REGULAR_INVENTORY_COST |
Inventory Cost |
drtyeop1c/drtyeop2c |
|
REGULAR_INVENTORY_RETAIL |
Inventory Retail |
drtyeop1r/drtyeop2r |
|
UNIT_COST |
Unit Cost |
|
|
AV_COST |
Average Cost |
|
|
UNIT_RETAIL |
Unit Retail |
On Order Data File - rms_oo.csv.ovr
The following table contains the list of column contents from the file. Only required columns for MFP are filtered by the transform process.
| Field Name | Functional Name | MFP 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 |
Transaction Data File - rms_tran.csv.ovr
The following table contains the list of column contents from the file. Only required columns for MFP are filtered by the transform process. RMF CS sends Net Sales in all regular, promotion, and clearance buckets. But MFP GA uses sales as only two types, Reg+Promo and Clearance, and also needs Gross Sales. After loading, additional batch calculations, which run as part of RMF CS batch transforms, calculate those required measures.
Also, RMF CS sends multiple type transaction details specific to receipts; batch calculations will calculate the effective receipts data.
| Field Name | Functional Name | MFP 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 |
drtymkdregr |
|
PROMO_MARKDOWN_RETAIL_REG |
Promotion Markdown (Regular) |
drtymkdpror |
|
PROMO_MARKDOWN_RETAIL_CLEAR |
Promotion Markdown (Clearance) |
drtymkdpclr |
|
CLEAR_MARKDOWN_RETAIL |
Clearance Markdown |
drtymkdclrr |
|
WF_MARKDOWN_RETAIL |
Franchise Markdown |
drtywfmkdr |
|
WF_MARKUP_RETAIL |
Franchise Markup |
drtywfmkur |
|
SHRINK_UNITS |
Shrink Units |
drtyrinva1u |
|
SHRINK_COST |
Shrink Cost |
drtyrinva1c |
|
SHRINK_RETAIL |
Shrink Retail |
drtyrinva1r |
|
DEAL_INCOME_SALES |
Deal Income Sales Based |
drtyvndfndr |
|
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 |
drtyrinva2u |
|
NON_SHRINK_ADJ_COST |
Non-Shrink Adjustments Cost |
drtyrinva2c |
|
NON_SHRINK_ADJ_RETAIL |
Non-Shrink Adjustments Retail |
drtyrinva2r |
|
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 |
Currency Conversion Rate File - rms_curr.csv.ovr
The following table contains the list of column contents from the file. Only required columns for MFP are filtered by the transform process.
| Field Name | Functional Name | MFP 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 MFP are filtered by the transform process.
| Field Name | Functional Name | MFP 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 |
addvlocopnd |
|
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 |
Warehouse Transformation
In RMF CS, warehouses hold inventory, receipts, and on-order data which are not mapped to any specific channels. In MFP, plans are only created at the MFP 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 MFP 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, receipts, and on-order data for those warehouses to assigned channels. This can also be done by running the batch Online Administration Tool task Transform RMS Warehouse Data. That transform process will also spread warehouse metrics to the store level for the Location Plan based on actuals and be added as part of actuals, so that the seeded location level plans can include the warehouse level plan when aggregated at the channel level. Warehouse does not need to be planned separately in the location level plan.
Batch Tasks Specific to RMF CS Integration
The following sets of Online Administration Tools tasks are pre-configured in Configured Batch Tasks to support RMF CS integration. For more details about running the tasks, see the Oracle Retail Merchandise Financial 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 RMF CS data based on the Enable RDX flag. If Enable RDX is set, it will import the data from the RDX staging tables, otherwise it will look for the RMF CS files in Object Storage, transform the data into the required format, and load the data. It also runs the transformation of RMF CS 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 MFP required format and copies the hierarchy files to the input directory. It will not load those files.
Typically, this can be used during initial installation to transform the initial set of RMF CS files and rebuild the domain with the initial set of RMF CS hierarchy files.
-
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 RMF CS Warehouse data to channels. This task also gets called as part of the Weekly Batch with RMS.