Oracle Retail Merchandising Foundation Cloud Service (RMFCS) and Retail Pricing Cloud Service (RPCS) Integration

Overview: Use the integration with Oracle Retail Merchandising Foundation Cloud Service (RMFCS) to import item information.

The import from RMFCS also supports importing pricing information from Oracle Retail Pricing Cloud Service (RPCS). This import is also described below.

In this chapter:

• Data Flow from RMFCS and RPCS to Order Management System

• Configuration for RMFCS and RPCS Integrations

- Configuration within Order Management System

• Additional Considerations for RMFCS Integration

• Mapping Item and Pricing Information into Order Management System

- RMFCS to OMS Mapping

- RPCS to OMS Mapping

Data Flow from RMFCS and RPCS to Order Management System

If your company is configured for RMFCS integration for item creation and update, processing takes place as follows:

1. Import momzip file into file storage or FTP folder: If the File Storage API is enabled, you can use the API to place the inbound momzip file from RMFCS in the FILE_STORAGE table; otherwise, import the file into the CWDIRECTCP_UPLOAD_DIRECTORY. Note that RMFCS does not support populating the FILE_STORAGE table through the file storage API. See Working with File Imports for background.

Note: You cannot use Work with File Uploads (WUPL) to process the momzip file.

About the momzip file: The momzip file must contain one or more pipe-delimited text files, each with the DAT extension, created from RMFCS. The momzip file itself is a compressed file.

Item information: The itemhdr DAT file contains the item information to import from RMFCS into Order Management System. Note that not all fields in this file are mapped into Order Management System. See RMFCS to OMS Mapping and RPCS to OMS Mapping for details.

Pricing information: RPCS provides two DAT files:

- Regular price: The REGPC DAT file includes regular pricing information for items.

- Clearance price: The CLRPC DAT file includes clearance pricing for items.

Like the Itemhdr file, the two pricing files from RPCS are pipe-delimited text files. If either or both of these files are included in the momzip file, the file contents update the RI Item Upload Table (RIIUPP) and, ultimately, the Item Offer and Item Price tables. Because no offer information is mapped from RPCS, the offer code to use defaults from the RMSZIP Periodic Function. See RPCS to OMS Mapping for mapping details.

File naming: The periodic function identifies DAT files containing item or pricing data by the file name. The file from RMFCS needs to begin with itemhdr, while the pricing files need to begin with REGPC or CLRPC. Each file needs to include the store ID assigned in RMFCS.

For example, a file might be named itemhdr_20180702123456_1234_delta_50.dat, where 201807123456 is the date and time stamp, 1234 is the store ID, delta indicates that the file includes only changed records, and 50 is the total number of records in the file. Including the text delta in the file name is informational; regardless of whether it is included, processing the records works the same way.

Similarly, a pricing file name might be REGPC_20180702021616_1234.dat.

Any other files included in the momzip file are not used.

Note: File names and suffixes are not case-sensitive. For example, either momzip or MOMZIP are acceptable and will not result in an error.

2. Process records in momzip file and create RI Item Upload records: Once the momzip file is available in either the FILE_STORAGE table or the CWDIRECTCP_UPLOAD_DIRECTORY, the RMSZIP periodic function can process the contents of the file and create records in the RI Item Upload Table (RIIUPP). The periodic function includes a parameter defining the store ID assigned in RMFCS, as well as specifying the Company number.

See RMSZIP Periodic Function for details on configuring the periodic function.

If there are multiple momzip files, the oldest file is processed.

When this step completes, an RIIUPP record is created for each eligible row in the DAT file, and displayed at the Work with File Upload Screen, even though you cannot use this screen to upload import files from RMFCS or RPCS.

This preliminary step of creating the RI Item Upload records does not include all the field mapping that ultimately needs to take place when updating the target tables; instead, the mapping is completed during the next step in the process, when the target tables are updated.

3. Use RI Item Upload records to create or update items, offers, and prices: The RI Item Upload Translation Program and RI Item Upload Edit Program process the records in the RI Item Upload table, and use these records to create or update items, offers, and prices. You can begin these programs by selecting Process File at the Work with Retail Item Upload Screen, or by submitting the RIUPLD Function.

Configuration for RMFCS and RPCS Integrations

Configuration within Order Management System

The configuration required in Order Management System to support importing items from RMFCS and RPCS is described below.

System Control Values

The following system control values must be selected:

• Use Retail Integration (H26)

Important: See Retail Integration (External System into Order Management System) Overview and Setup for background on the additional setup required to support retail item integration. If you do not complete this setup, the import will not be successful.

• Require Long SKU Division with Long SKU Department (E85)

The following system control values must be unselected:

• Require Item Class in Work with Items (F06)

• Require Sort Sequence Number in the SKU File (F23)

• Require Entity in Item File (G44)

Since this information is not provided by RMFCS, the import will not be successful if the information is required.

Properties

The FILE_STORAGE_IMPORTS_ENABLED property controls whether the File Storage API is enabled. If not, the CWDIRECTCP_UPLOAD_ DIRECTORY is used.

The FILE_STORAGE_MAX_SIZE admin property controls the maximum size of a file that can be uploaded through the file storage API. Uploaded files should be less than 1G in size, so this property should be set to 1073741824 or less.

RMSZIP Periodic Function

Use Working with Periodic Functions (WPER) to set up the RMSZIP periodic function. If the function needs to run in more than one company, mapping from more than one store in RMFCS, you can name the function RMSZIPN, where N is a unique number. When setting up the function:

• Set the Program name to PFRRMS.

• Set the Parameter to the store ID assigned in RMFCS, and the default offer code in Order Management System, separated by an underscore. For example, if the store ID is 1234 and the default offer is OFR, set the Parameter to 1234_OFR.

The offer code is required for the import of pricing information from RPCS. If you set the parameter to just the store ID, such as 1234, but do not include the offer code, such as 1234_OFR, the Current Offer (A33) applies.

• Select the Company Parameter flag.

RIUPLD Function

Use Working with Periodic Functions (WPER) to set up the RIUPLD periodic function, if it does not already exist. When setting up the function, set the Program name to PFR0084.

Periodic Process

Use Working with Periodic Processes (WPPR) to assign either or both of the periodic functions described above to a periodic process. The Company parameter is required to indicate the company where the records should be created or updated.

Additional Considerations for RMFCS Integration

Does not support SKUs: Since RMFCS does not support SKU’s, all items imported from RMFCS are non-SKU’d items.

Using RMFCS Item Level and Tran Level to determine whether to create or update an item: RMFCS uses a different hierarchy than Order Management System for multi-level items. The RMFCS hierarchy supports:

• A level 1 item: For example, a picture frame that comes in only one finish and size.

• A level 2 item: For example, an item group that includes a novelty tee shirt in sizes small, medium, and large. Level 1 is the tee shirt, while level 2 includes the sizes.

• A level 3 item: For example, an item group that includes a polo shirt in red, blue, and yellow, and sizes small, medium, and large. Level 1 is the polo shirt, level 2 is the sizes, and level 3 is the colors.

The records in the itemhdr file that map to items in Order Management System are identifiable by the fact that the ItemLevel passed in the file is the same as the TranLevel. If the ItemLevel and the TranLevel are different, the record is not used to update the RIIUPP table in Order Management System.

Troubleshooting Information for the RMFCS Item and Pricing Import

If the RMSZIP process ends without populating the RIIUPP table, possible explanations include:

• The momzip file was not found.

• The number of columns in the DAT file was incorrect.

• The RIIUPP table already contained records that need to be cleared before the process could run again.

When there is an error with the import, it is listed on the Work with File Upload Screen with a File Type of RIIUPP and a status of Error, and you can view the error description by selecting Action > View Error.

If the momzip file did not contain a DAT file containing the itemhdr data, the upload remains at the Work with File Upload Screen in Submitted status. To correct this situation, you need to delete the momzip file from the FILE_STORAGE table if you use the File Storage API, or from the CWDIRECTCP_UPLOAD_ DIRECTORY.

Mapping Item and Pricing Information into Order Management System

RMFCS to OMS Mapping

The item information that updates the RI Item Upload Table (RIIUPP) and ultimately updates the target tables, including information mapped from RMFCS, is described in the following table. The process creates record type 01 (Item/SKU) records in the table based on the information from RMFCS. See RI Item Upload Table (RIIUPP) for more information on the updated fields.

The file from RMFCS contains no null fields. A blank is passed for an alphanumeric field with no data, and a 0 is passed for a numeric field.

**DFLT ITEM? Certain fields default from the **DFLT ITEM Item/SKU, if one is configured. These fields are indicated below. This information defaults when the RI Item Upload Translation Program and RI Item Upload Edit Program run, using the information in the RI Item Upload table to update the target tables.

**DFTCHG Item? The **DFTCHG Item/SKU controls the update of existing items:

• Any fields that are populated for the **DFTCHG item are not changed for an existing item that is passed from RMFCS.

• And any fields that are not populated for the **DFTCHG item are eligible to be updated for an existing item that is passed from RMFCS.

See the **DFTCHG Item/SKU for more information.

See RPCS to OMS Mapping for information on how pricing information in the RI Item Upload Table (RIIUPP) is updated based on the data received from RTM.

Additional information from RMFCS: Additional information is included in the DAT files from RMFCS; however, only the fields that update the RI Item Upload Table (RIIUPP) are described in the following table.

Sample itemhdr row:

ITEMS|FULLHDR|1234|102900077|||N|N|1|1|Y|||||||||||||2CDQ|2222|5274|2222|5485|A|Item 102900077||Item 102900077||Y|N|1000|EA||||E|N|12.22|USD||USD|||N|N|N|N|N|ITEM|||||N||||N|N|Y|Y||||N|N|N|N||||||||N|N|

 

Note: About unmapped item/SKU fields: Any item/SKU in Order Management System fields not listed below are not mapped from RMFCS. They are left blank if alphanumeric, or set to 0 if numeric.

Target Field	Value Used	Description/ Comments
Item Updates
Company	Company number from the periodic process	The periodic process set up for the RMSZIP periodic function should be set up to require the Company Parameter.
Record Created Date	Current date when record created
Record Created Time	Current time when record created
Record Type	01	Hard-coded.
Request Type	blank	Since RMFCS does not indicate whether the record is an Add or a Change, Order Management System updates the record for the item if it exists. If no matching item exists, it creates a new item.
Sequence #	Sequential assigned number	Assignment of sequence numbers starts at 1 and continues for the first 999 records in the upload file. After the first 999 records, the Record Created Time increases by 1 second, and sequential assignment begins again and again runs from 1 through 999. This pattern continues for all records in the upload file, so that each has a unique Record Created Time/Sequence # assignment.
Key Type	IT	Hard-coded.
Item	RMFCS item code	Rather than using item SKU fields in the same way as Order Management System, RMFCS uses a hierarchical system to identify the relationships between individual items. An item code in the import table creates or updates an item in Order Management System only if the item’s ItemLevel and TranLevel in RMFCS are the same. See Additional Considerations for RMFCS Integration for a discussion.
SKU	Blank	Not used. See Additional Considerations for RMFCS Integration for a discussion.
Status	U	Unprocessed.
Processed Date	Blank	Populated through the RI Item Upload Process.
Processed Time	Blank	Populated through the RI Item Upload Process.
Allow SKUs	N	Hard-coded. SKUs are not supported through the integration.
Drop Ship	Blank	From the **DFLT ITEM Item/SKU; otherwise, unselected.
Long SKU Style	ItemParent	If no ItemParent is passed, the item code is used as the Long SKU Style.
Retail Style #	Item	The item code from RMFCS is used as the Retail Style #.
Long SKU Vendor	Subclass	The Subclass from RMFCS is used as the Long SKU Vendor.
Non-Inventory	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Selling Qty	1
Selling Weight	0	From the **DFLT ITEM Item/SKU; otherwise, 0.
Serial # Tracking	N	Not implemented in Order Management System.
Ship Alone	From ShipAloneInd	When the ShipAloneInd from RMFCS is set to Y, the Ship Alone field is set to S; otherwise, the Ship Alone field is left blank.
Allow % Discount?	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Oversize	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Description	ItemDesc	From the ItemDesc in RMFCS.
Exclude From Flex Pay	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Royalty	N	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Membership	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Buyer	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
Item Class	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
Location Class	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
Class (Long SKU Class)	Class	The Class from RMFCS is used as the Long SKU Class.
Department (Long SKU Department)	Department	The Department from RMFCS is used as the Long SKU Department.
UOM (Unit of Measure)	StandardUOM from RMFCS	Needs to be a valid unit of measure in Order Management System, set up through Working with Units of Measure (WUOM). Note: Units of measure set up in RMFCS should not exceed 3 positions, since this is the maximum field length supported in Order Management System.
Vendor #	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
Ship Via Code	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
Manufacturer Vendor	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
Entity Number	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
Season	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
Subscription	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Height Override	0	From the **DFLT ITEM Item/SKU; otherwise, 0.
Length Override	0	From the **DFLT ITEM Item/SKU; otherwise, 0.
SKU Gift Certificate	N	Hard-coded. Not supported in Order Management System.
Restrict Orders	Blank	From the **DFLT ITEM Item/SKU; otherwise, blank.
VAT Exempt Flag	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Suppress B/O Card	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Returnable	Blank	From the **DFLT ITEM Item/SKU; otherwise, not selected.
Whs (Warehouse)		From the **DFLT ITEM Item/SKU; otherwise, from the Default Warehouse (A04); otherwise, set to 0.
Soldout Control Code	Blank	Defaults from the Default Soldout Control Code (D72).
UOM Type	StandardUOM from RMFCS	Needs to match a unit of measure in Order Management System, set up through Working with Units of Measure (WUOM).
OROB Eligible		Selected.

RPCS to OMS Mapping

The offer and pricing information that updates the RI Item Upload Table (RIIUPP) and ultimately updates the target tables, including information mapped from RPCS, is described in the following table. The process creates record type 03 (Item Offer) and 05 (Item Price) records in the table based on the information from RPCS. See RI Item Upload Table (RIIUPP) for more information on the updated fields.

The file from RPCS contains no null fields. A blank is passed for an alphanumeric field with no data, and a 0 is passed for a numeric field.

See RMFCS to OMS Mapping for information on how item information in the Item table and related tables is updated based on the data received from RMFCS.

Additional information from RPCS: Additional information is included in the DAT files from RPCS. The files each include a header record (FHEAD) and a tail record (FTAIL), as well as a detail record (FDETL) for each item offer and price. However, only the fields that update the RI Item Upload Table (RIIUPP) are described in the following table.

Unmapped Item Offer fields: Any Item Offer or Item Price fields not listed in the following tables are not mapped from RMFCS. They are left blank if alphanumeric, or set to 0 if numeric.

Sample REGPC file contents:

FHEAD|1|REGPC|20180627021616|1234|S

FDETL|2|CRE|1001|101000122|20180404000000|1|16.75|EA|USD|0||||

FDETL|3|CRE|1002|101000132|20180405000000|1|16.75|EA|USD|0||||

FDETL|4|CRE|1003|101000123|20180627000000|1|16.75|EA|USD|0||||

FTAIL|5|3

 

Sample CLRPC file contents:

FHEAD|1|CLRPC|20180404021514|4241|S

FDETL|2|CRE|10002|101000126|20180404000000|6.85|EA|USD|

FDETL|3|CRE|10003|101000136|20180405000000|8.85|EA|USD|

FDETL|4|CRE|10004|101000135|20180404000000|7.85|EA|USD|

FTAIL|5|3

Item Offer Mapping from RPCS

Target Field	Value Used	Description/ Comments
Item Updates
Company	Company number from the periodic process	The periodic process set up for the RMSZIP Periodic Function should be set up to require the Company Parameter.
Record Created Date	Current date when record created
Record Created Time	Current time when record created
Record Type	03	Indicates Item Offer. Hard-coded.
Request Type	blank	Since RPCS does not indicate whether the record is an Add or a Change, Order Management System updates the record for the item if it exists. If no matching item exists, it creates a new item.
Sequence #	Sequential assigned number	Assignment of sequence numbers starts at 1 and continues for the first 999 records in the upload file. After the first 999 records, the Record Created Time increases by 1 second, and sequential assignment begins again and again runs from 1 through 999. This pattern continues for all records in the upload file, so that each has a unique Record Created Time/Sequence # assignment.
Key Type	IT	Hard-coded.
Item	RMFCS item code	The item to be updated with the Item Offer information.
SKU	Blank	Not used. See Additional Considerations for RMFCS Integration for a discussion.
Status	U	Unprocessed.
Processed Date	Blank	Populated through the RI Item Upload Process.
Processed Time	Blank	Populated through the RI Item Upload Process.
Offer for Item Offer	Offer from periodic function	The offer code defaults from the Parameter defined for the RMSZIP Periodic Function. If no offer code is specified for the periodic function, the Current Offer (A33) applies.

Item Price Mapping from RPCS

The following mapping applies to both the REGPC (regular price) and CLRPC (clearance price) files from RPCS.

Target Field	Value Used	Description/ Comments
Item Updates
Company	Company number from the periodic process	The periodic process set up for the RMSZIP Periodic Function should be set up to require the Company Parameter.
Record Created Date	Current date when record created
Record Created Time	Current time when record created
Record Type	05	Indicates Item Price. Hard-coded.
Request Type	blank	Since RPCS does not indicate whether the record is an Add or a Change, Order Management System updates the record for the item if it exists. If no matching item exists, it creates a new item.
Sequence #	Sequential assigned number	Assignment of sequence numbers starts at 1 and continues for the first 999 records in the upload file. After the first 999 records, the Record Created Time increases by 1 second, and sequential assignment begins again and again runs from 1 through 999. This pattern continues for all records in the upload file, so that each has a unique Record Created Time/Sequence # assignment.
Key Type	IT	Hard-coded.
Item	RMFCS item code	The item to be updated with the Item Offer information.
SKU	Blank	Not used. See Additional Considerations for RMFCS Integration for a discussion.
Status	U	Unprocessed.
Processed Date	Blank	Populated through the RI Item Upload Process.
Processed Time	Blank	Populated through the RI Item Upload Process.
Offer for Item Offer	Offer from periodic function	The offer code defaults from the Parameter defined for the RMSZIP Periodic Function. If no offer code is specified for the periodic function, the Current Offer (A33) applies.
Effective Date	RPCS Effective Date	The date when the price becomes effective.
Qty	RPCS: Multi-Units	The quantity required for the price break. In the case of the CLRPC file (clearance price), the quantity is hard-coded to 1.
Price	RPCS Multi-Unit Retail	The item price.