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:
-
Warehouses are sent to APCS as locations. RMFCS Warehouses holds inventory 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 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
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 |
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 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 Data Aggregation
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 Store/Location Cluster level. In order to get the full picture of inventory for locations within a channel, 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.
The weekly batch aggregation 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. A Service Request (SR) can be created to request to connect the RMFCS server with the Planning Servers. This is needed only if the customer already has RMFCS as the master source for defining new items. 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 RMF CS 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 > Set RMF CS 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 RMF CS 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.