D Appendix: RMFCS Integration
MFP 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 RMFCS. This appendix provides details on the list of hierarchies and data files that are integrated with RMFCS.
RMFCS integration can be done using flat file integration if the customer is using a previous release of RMFCS 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 RMFCS:
-
Warehouses are sent to MFP as locations. RMFCS Virtual Warehouses holds inventory, receipts, and on-order data. RMFCS Virtual Warehouses will be present within the Channel.
-
RMFCS sends hierarchy and data files on a weekly basis.
-
RMFCS sends the data at the item level, so MFP 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 MFP to plan and view data on different local currencies.
RMFCS Integration using File-Based Approach
RMFCS sends the following sets of hierarchy and data files. All RMFCS 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. The 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, RMFCS 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 RMFCS 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 RMFCS and need to be transformed to the MFP format before loading them into MFP 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 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.
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 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
RMFCS 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
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 MFP are filtered by the transform process.
| Field Name | Functional Name | MFP Mapping |
|---|---|---|
|
LOCATION |
Location ID |
stor |
|
LOC_NAME |
Location Name |
strc |
|
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 |
|
|
CHANNEL_NAME |
WH Channel Name |
|
|
STORE_CLASS |
Store Class |
|
|
STORE_CLASS_DESCRIPTION |
Store Class Description |
|
|
STORE_FORMAT |
Store Format |
|
|
STORE_FORMAT_NAME |
Store Format Name |
|
|
PLANNING_CHANNEL_ID |
Planning Channel ID |
chnc |
|
PLANNING_CHANNEL_NAME |
Planning Channel Name |
chnc_label |
|
PLANNING_COUNTRY_ID |
Planning Country ID |
ccty |
|
PLANNING_COUNTRY_NAME |
Planning Country Name |
ccty_label |
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 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 RMFCS and need to be transformed to the MFP format before loading them into MFP Cloud Service. All RMFCS 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. RMFCS 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 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 | 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 Data Aggregration
In RMFCS, 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 and Location level for Location Planning. In order to get the full picture of inventory for a Location in Location Planning, each warehouse location’s inventory within a channel needs to be spread to locations based on their actuals. That is handled by weekly batch aggregations to spread the warehouse data within a channel to all the locations for Location Plan metrics. Since the Virtual Warehouses will be part of the channel, for channel level plans, aggregated data will already include the warehouse data at the Channel level.
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 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 RMFCS 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 RMFCS files in Object Storage, transform the data into the required format, and load the data. 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 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 RMFCS files and rebuild the domain with the initial set of RMFCS hierarchy files.