Retail Integration (External System into Order Management System) Overview and Setup

Generic retail integration from an external system into Order Management System allows you to keep merchandise information in Order Management System in sync with merchandise information in your external merchandising system. Using this integration, you can provide your customers with a consistent shopping experience across your retail store, catalog, and online channels. You can carry the same merchandise and offer your customers the same merchandise pricing in your retail store, catalog, and online channels. This allows you to maintain the image and service levels customers have come to expect.

RMFCS integration: The item import from Oracle Retail Merchandising Foundation Cloud Service (RMFCS) also creates records in the RI Item Upload table and uses the **DFLT ITEM Item/SKU and **DFTCHG Item/SKU; however, the RMFCS import uses a different set of files to populate the RI Item Upload table, and a different periodic function. See Oracle Retail Merchandising Foundation Cloud Service (RMFCS) and Oracle Retail Pricing Cloud Service (RPCS) Integration for more information.

In this chapter:

• Uses for the Generic Retail Integration from an External System into Order Management System

• Item Upload Overview

- Item Upload Batch Processing

• Setup for Generic Retail Integration from External System into Order Management System

- Populating the RI Item Upload Table (RIIUPP)

- System Control Values

- Secured Feature

- Menu Options and Subfile Options

- Creating Default and Change Item/SKU Upload Values: **DFLT ITEM and **DFTCHG

• Creating Default and Change Item/SKU Upload Values: **DFLT ITEM and **DFTCHG

- **DFLT ITEM Item/SKU

- **DFTCHG Item/SKU

- Updating an Existing Item/SKU Value using Both **DFLT ITEM and **DFTCHG

Uses for the Generic Retail Integration from an External System into Order Management System

Using this integration, you can:

• upload new or changed merchandise information located in your retail store to Order Management System, such as:

- item and SKU

- item offer

- SKU offer

- item price

- SKU price

- vendor item

- item UPC

- item and SKU coordinate

• create a hierarchy between a long SKU division, long SKU department, and retail class in Order Management System.

Item Upload Overview

You can use the RI Item Upload Process to create and maintain item and SKU information in Order Management System that corresponds to your retail store merchandise. You can upload items and SKUs, item/SKU offers, item/SKU prices, item/SKU UPC codes, vendor items, and coordinate items, and optionally create many other supporting values using Item Upload Batch Processing.

The Use Retail Integration (H26) system control value determines whether Order Management System changes certain table structures so that it can read records you upload from your retail system. When this system control value is selected, the system:

• uses long SKU class codes from the Retail Class table instead of long SKU class codes from the Long SKU Class table. This allows you to create a hierarchy of retail class within long SKU department. You can link retail classes to a long SKU department in Working with Long SKU Departments (WLSD).

• requires a value in the Long SKU department, Long SKU class (retail class), Long SKU style, and Long SKU vendor fields when you create or maintain a base item.

If you wish to selectively require certain long SKU values, you can select one or more of the following system control values instead of selecting the Use Retail Integration (H26) system control value:

• Require L/S Department (I92)

• Require L/S Class (I93)

• Require L/S Style (I94) (This system control value also validates that the long SKU style code is unique at both the item and SKU level.)

Create supporting tables? Order Management System creates supporting table values, such as buyer code, for items/SKUs created or updated through the item upload if they do not already exist in the Order Management System table. See Tables Updated by the RI Item Upload.

RMFCS integration: The item import from Oracle Retail Merchandising Foundation Cloud Service (RMFCS) also creates records in the RI Item Upload table and uses the **DFLT ITEM Item/SKU and **DFTCHG Item/SKU; however, the RMFCS import uses a different set of files to populate the RI Item Upload table, and a different periodic function. See Oracle Retail Merchandising Foundation Cloud Service (RMFCS) and Oracle Retail Pricing Cloud Service (RPCS) Integration for more information.

Item Upload Batch Processing

Batch processing overview: This flowchart summarizes the process of passing batch item upload records from a retail system other than RMFCS to Order Management System.

RMFCS integration: See Oracle Retail Merchandising Foundation Cloud Service (RMFCS) and Oracle Retail Pricing Cloud Service (RPCS) Integration for more information on the process of importing item data from RMFCS.

How do you upload retail merchandise information? Use the File Storage API or Work with File Uploads (WUPL) to upload retail merchandise information into the RI Item Upload Table (RIIUPP).

• If you use the File Storage API to upload the generic Retail Integration Item file named RIIUPP into the FILE_STORAGE table in the database, you need to use the UPRITEM Upload Retail Item File (Program name PFR0134, Parameter RIIUPP) periodic function to create records in the RI Item Upload Table (RIIUPP).

• If you use Work with File Uploads (WUPL), the RI Item Upload Table records are created through the upload process.

How do you process records in the RI Item Upload table? Once you have passed item upload records from your retail master system to the RI Item Upload table, you can view all the records in the batch using the Working with Retail Integration Item Upload (RIIU) menu option. In order to process the records in the RI Item Upload table:

• Select Process at the Work with Retail Item Upload Screen.

• Select Accept at the Copy Item to Company Window in Working with Existing Items (MITM).

• Submit the RIUPLD function using the PFR0084 program. See Scheduling Jobs for background.

Records that pass all edits: The system deletes these records from the RI Item Upload table and the requested update (create or maintain) is made in Order Management System.

Records that fail one or more of the edits: The system retains these records in the RI Item Upload table and also creates a record for each RI Item Upload record in error in the RI Item Upload Error table. You can review and correct the errors, or delete the record(s) using Working with Retail Integration Item Upload (RIIU). You can review item upload records in error on the:

• Item Upload Error Report: lists item upload records that are in error.

• Record Deleted from RI Item Upload Report: lists item upload records that you manually deleted at the Work with Retail Item Upload Screen.

Retail Item Upload email: If a valid email is defined in the Email address field for the user that submitted the RI Item Upload Program, the system sends a Retail Item Upload Email to the user’s email address when the job completes indicating whether all records were processed successfully or whether any records contain errors.

For more information: See Working with Retail Integration Item Upload (RIIU).

Setup for Generic Retail Integration from External System into Order Management System

Before you can use the generic retail integration from an external system (other than RMFCS) into Order Management System, you must perform the necessary setup. Information requiring creation and setup includes:

• Populating the RI Item Upload Table (RIIUPP)

• System Control Values

• Secured Feature

• Menu Options and Subfile Options

• Creating Default and Change Item/SKU Upload Values: **DFLT ITEM and **DFTCHG

- **DFLT ITEM Item/SKU

- **DFTCHG Item/SKU

- Updating an Existing Item/SKU Value using Both **DFLT ITEM and **DFTCHG

Populating the RI Item Upload Table (RIIUPP)

Create an RI Item Upload text file that contains the records to upload.

To upload the text file:

• If you use the File Storage API to upload the Retail Integration Item file named RIIUPP or RIIUP.txt (the txt extension is not case-sensitive) into the FILE_STORAGE table in the database, you need to use the UPRITEM Upload Retail Item File (Program name PFR0134, Parameter RIIUPP) periodic function to create records in the RI Item Upload Table (RIIUPP).

• If you use Work with File Uploads (WUPL) to upload the text file named RIIUPP, the RI Item Upload Table records are created through the upload process.

See Sample Retail Integration Items Upload Data for information on the contents of the text file.

Note: When populating the RI Item Upload table, the information should be entered in all upper case.

RMFCS integration: The item import from Oracle Retail Merchandising Foundation Cloud Service (RMFCS) also creates records in the RI Item Upload table and uses the **DFLT ITEM Item/SKU and **DFTCHG Item/SKU; however, the RMFCS import uses a different set of files to populate the RI Item Upload table, and a different periodic function. See Oracle Retail Merchandising Foundation Cloud Service (RMFCS) and Oracle Retail Pricing Cloud Service (RPCS) Integration for more information.

System Control Values

This table contains the system control values you should set up to use generic retail integration from an external system into Order Management System.

System Control Value	Description
Use Retail Integration (H26)	Select this field if you wish the system to: • use long SKU class codes from the Retail Class table instead of long SKU class codes from the Long SKU Class table. This allows you to create a hierarchy of retail class within long SKU department. You can link retail classes to a long SKU department in Working with Long SKU Departments (WLSD). • require a value in the Long SKU department, Long SKU class (retail class), Long SKU style, and Long SKU vendor fields when you create or maintain a base item. Leave this field blank if you do not wish to link a long SKU class (retail class) to a long SKU department. In addition, leave this field blank if you wish to selectively indicate which long SKU values are required.
Require L/S Department (I92)	Select this field if you want to require a value in the Long SKU department when you create or maintain a base item. Note: The system ignores the setting of this system control value if the Use Retail Integration (H26) system control value is selected.
Require L/S Class (I93)	Select this field if you want to require a value in the Long SKU class when you create or maintain a base item. Note: The system ignores the setting of this system control value if the Use Retail Integration (H26) system control value is selected.
Require L/S Style (I94)	Select this field if you want to require a value in the Long SKU style when you create or maintain a SKU. The system also validates that the long SKU style at the item level and SKU level is a unique value. Note: The system ignores the setting of this system control value if the Use Retail Integration (H26) system control value is selected.
Auto-Generate Item Keywords from Description (F79)	Select this field if you want the system to automatically create keywords for items created through the RI Item Upload process for record type 01 Item/SKU. Note: The system creates new keywords for the item based on its current description; however, any existing keywords for the item will remain.

Secured Feature

The Maintain Long SKU Values in MITM (B05) secured feature determines whether a user has authority to change long SKU values in Work with Item/SKUs.

Menu Options and Subfile Options

This table contains the menu options and subfile options you use for retail integration from external system into Order Management System.

Menu Option	Purpose
Working with Retail Integration Item Upload (RIIU)	Use this menu option to process, review and correct batch item upload records passed to Order Management System from your retail master system.
Performing Initial Item Entry (MITM)	Enter values in these fields when you create or modify an item/SKU and you are using retail integration from an external system into Order Management System: • Long SKU department • Long SKU class • Long SKU style • Long SKU vendor optionally: • Retail style number • Original retail price • Long SKU color • Long SKU size • Long SKU width • UPC code Creating default item/SKU values You can create the **DFLT ITEM Item/SKU to update blank fields in the item upload records with the values from the corresponding fields in the **DFLT ITEM. See **DFLT ITEM Item/SKU for setup instructions. Changing item/SKU values You can create the **DFTCHG Item/SKU to determine when the system updates the fields for an existing item/SKU with the values defined for a change item upload record. • If a value has been defined for a field in the **DFTCHG item, the system does not update the value defined for the existing item with the value from the change item upload record. • If a value has not been defined for a field in the **DTFCHG item, the system updates the value defined for the existing item with the value from the change item upload record. See **DFTCHG Item/SKU for setup instructions.
Working with Periodic Functions (WPER)	You can schedule the periodic functions, including the UPRITEM Upload Retail Item File (Program name PFR0134, Parameter RIIUPP), the RI Item Upload Translation Program, and RI Item Upload Edit Program to run the Item Upload Process periodically. See Scheduling Jobs for background.

Creating Default and Change Item/SKU Upload Values: **DFLT ITEM and **DFTCHG

You can create the following items in Order Management System for the system to consider when uploading RI item upload records for record type 01 (item/SKU information).

• **DFLT ITEM: create this item in Order Management System to define the values to default to add or change item upload records; see **DFLT ITEM Item/SKU.

• **DFTCHG: create this item in Order Management System to determine when the system updates an existing item/SKU with the values defined in a change item upload record; see **DFTCHG Item/SKU.

Note: You are not required to create the **DFLT ITEM item or **DFTCHG item, however it is recommended.

RMFCS integration: The item import from Oracle Retail Merchandising Foundation Cloud Service (RMFCS) also creates records in the RI Item Upload table and uses the **DFLT ITEM Item/SKU and **DFTCHG Item/SKU. See Oracle Retail Merchandising Foundation Cloud Service (RMFCS) and Oracle Retail Pricing Cloud Service (RPCS) Integration for more information.

**DFLT ITEM Item/SKU

You can create an item in Order Management System to define the values to default to add or change item upload records. This is helpful if there are certain values that are always the same for each item; instead of entering the value for each item upload record, you can have the value default. For example, if the buyer code for each item is always BTM, you can enter BTM as a default value to default to each of your item upload records.

The system defaults a value to the item upload record only if the current value for the item upload record is blank; if the field in the item upload record is not blank, the system does not default a value.

Example: If you enter A01 in the Location field for **DFLT ITEM, the system defaults location A01 when you upload an add or change item upload record and the Location field for the item upload record is blank. If the Location field for the item upload record is not blank, the system does not use the value in **DFLT ITEM.

Note: The system defaults values to add or change item upload records only for record type 01 (item/SKU information). You cannot default values for any other record type, such as item/offer or vendor item.

Many fields are unique to an item/SKU; nevertheless, when you pass item upload records, every blank field in the item upload record defaults a value from the **DFLT ITEM (if you’ve defined one) except these fields:

• Allow SKUs

• Item Description

• SKU Description

Instructions: Use the following steps to create the **DFLT ITEM item and SKU.

1. Create the SKU element NA for SKU Element 1 in the Work with SKU Element 1 (WSK1) menu option. Important: You need to create the SKU element NA if you wish to default information at the SKU level.

2. Create the item **DFLT ITEM in the Work with Items/SKUs (MITM) menu option. See Performing Initial Item Entry (MITM). When creating this item:

- Select the SKUs (Stock keeping unit) field to indicate the item contains SKUs.

- Populate the fields that you want to establish as company-wide item default values for any blank fields that you pass for an 01 item/SKU record type. Note: If you define a default SKU group, the system uses this value for SKUed items only since this field does not apply to non-SKUed items.

- Advance to the Create SKU 1 of 2 (With Overrides) Screen and enter NA in the first SKU element field.

- Populate the fields that you want to set up as SKU default values for any blank fields that you pass for an 01 item/SKU record type.

- When you are finished entering the SKU default values, select OK to start using these item and SKU default values for item uploads.

When does the system use the **DFLT ITEM value? This table explains when the system updates the value defined for the existing item/SKU, based on the value defined for the existing item, **DFLT ITEM, and item upload record.

Note: This table assumes the **DFTCHG Item/SKU does not exist; see Updating an Existing Item/SKU Value using Both **DFLT ITEM and **DFTCHG to review a table that explains how the system updates the value defined for an existing item/SKU, based on the value defined for the existing item, **DFLT ITEM, **DFTCHG, and the upload record.

The Buyer field value is:
Existing item/SKU	**DFLT ITEM	Upload record	Results
new item	blank	blank	The system updates the Buyer field for the new item/SKU to blank.
new item	AAA	blank	The system updates the Buyer field for the new item/SKU to AAA.
new item	blank	AAA	The system updates the Buyer field for the new item/SKU to AAA.
new item	AAA	BBB	The system updates the Buyer field for the new item/SKU to BBB.
AAA	blank	blank	The system updates the Buyer field for the existing item/SKU to blank.
blank	AAA	blank	The system updates the Buyer field for the existing item/SKU to AAA.
blank	blank	AAA	The system updates the Buyer field for the existing item/SKU to AAA.
AAA	BBB	blank	The system updates the Buyer field for the existing item/SKU to BBB.
AAA	blank	BBB	The system updates the Buyer field for the existing item/SKU to BBB.
blank	AAA	BBB	The system updates the Buyer field for the existing item/SKU to BBB.
AAA	BBB	CCC	The system updates the Buyer field for the existing item/SKU to CCC.

**DFTCHG Item/SKU

You can create an item in Order Management System to determine when the system updates an existing item/SKU with the values defined in a change item upload record. This is helpful if you only want to enter values in the change item upload record for the fields you wish to change, not the fields you wish to keep the same. For example, if you wish to only change the buyer code for an existing item, you can upload an item upload record with a value in the Buyer field and all other optional fields blank; if a value is defined for the **DFTCHG item, the system knows that you do not want to update the other fields for the existing item/SKU to blank, but rather, keep the existing values. However, if the **DFTCHG item does not exist or a value is not defined for the **DFTCHG item, the system updates the other fields for the existing item/SKU to blank.

Example: If you enter A01 in the Location field for **DFTCHG, the system does not update the Location field for an existing item/SKU when you upload a change item upload record; if you do not enter a value in the Location field for **DFTCHG, the system updates the Location field for an existing item/SKU when you upload a change item upload record.

Note: The system uses the **DFTCHG item only for record type 01 (item/SKU information) upload records in change mode. The system does not use **DFTCHG for any other record type, such as item/offer or vendor item, and does not use **DFTCHG for add mode or delete mode.

Instructions: Use the following steps to create the **DFTCHG item and SKU.

1. Create the SKU element *DFT for SKU Element 1 in the Work with SKU Element 1 (WSK1) menu option. Important: You need to create the SKU element *DFT if you wish to determine change information at the SKU level.

2. Create the item **DFTCHG in the Work with Items/SKUs (MITM) menu option. See Performing Initial Item Entry (MITM). When creating this item:

- Select the SKUs (Stock keeping unit) field to indicate the item contains SKUs.

- Populate the fields that you want to establish as company-wide item values that you do not wish to change for existing item/SKUs when you upload a change item/SKU upload record. Note: The values you enter in the fields are meaningless; the value only indicates that you do not wish this field to update for an existing item.

- Advance to the Create SKU 1 of 2 (With Overrides) Screen and enter *DFT in the first SKU element field.

- Populate the fields that you want to set up as SKU values that you do not wish to change for existing item/SKUs. Note: The values you enter in the fields are meaningless; the value only indicates that you do not wish this field to update for an existing SKU.

- When you are finished entering the SKU default values, select OK to start using these item and SKU values for item uploads.

Required fields: Certain fields are required when you create the **DFTCHG item and *DFT SKU (for example, the Warehouse field). In order to update these fields based on information passed for a record in the RI Item Upload table for record type 01 (item/SKU information) in change mode, you must manually update these fields to blank in the Order Management System database. Note: If you manually clear a required field for the **DFTCHG item or *DFT SKU in the database, you can no longer change the item and SKU using the Work with Items/SKUs (MITM) menu option. If you try to change the item or SKU in Work with Items/SKUs (MITM), the system will require an entry in each required field.

Check box fields: Certain fields defined for an item (such as the Drop ship flag) and defined for a SKU (such as the Suppress backorder card flag) display as check boxes in the Work with Items/SKUs (MITM) menu option.

• If you select a check box field, the system updates the record in the database to Y and considers the field populated with a value.

• If you leave a checkbox field unselected, the system updates the record in the database to N and also considers the field populated with a value.

Because a check box field is always populated in the database with a Y or N, the system will never update this field based on information passed in the RI Item Upload table for record type 01 (item/SKU information) upload records in change mode. In order for the system to consider the field unpopulated (blank), and allow you to update the field based on information passed in the RI Item Upload table, you must manually update the check box fields for the **DFTCHG item in the Item table and manually update the check box fields for the *DFT SKU in the SKU table so that the field does not contain a Y or an N.

Note: If you manually update the check box fields for the **DFTCHG item and *DFT SKU in the database, you can no longer change the item and SKU using the Work with Items/SKUs (MITM) menu option. If you try to change the item or SKU in Work with Items/SKUs (MITM), the system will update the check box fields that you set to blank in the database to N (unselected).

When does the system use the **DFTCHG value? This table explains when the system updates the value defined for the existing item/SKU, based on the value defined for the existing item, **DFTCHG, and item upload record.

Note: This table assumes the **DFLT ITEM Item/SKU does not exist; see Updating an Existing Item/SKU Value using Both **DFLT ITEM and **DFTCHG to review a table that explains how the system updates the value defined for an existing item/SKU, based on the value defined for the existing item, **DFLT ITEM, **DFTCHG, and the upload record.

The Buyer field value is:
Existing item/SKU	**DFTCHG	Upload record	Results
AAA	blank	blank	The system updates the Buyer field for the existing item/SKU to blank.
blank	AAA	blank	The system does not update the Buyer field for the existing item/SKU; the field remains blank.
blank	blank	AAA	The system updates the Buyer field for the existing item/SKU to AAA.
AAA	BBB	blank	The system does not update the Buyer field for the existing item/SKU; the field remains AAA.
AAA	blank	BBB	The system updates the Buyer field for the existing item/SKU to BBB.
blank	AAA	BBB	The system does not update the Buyer field for the existing item/SKU; the field remains blank.
AAA	BBB	CCC	The system does not update the Buyer field for the existing item/SKU; the field remains AAA.

Updating an Existing Item/SKU Value using Both **DFLT ITEM and **DFTCHG

If you have both the **DFLT ITEM Item/SKU and **DFTCHG Item/SKU set up, the system uses the following hierarchy when uploading a change item upload record:

• Is the field in the item upload record blank? If yes, the system defaults the value defined in the **DFLT ITEM Item/SKU.

• Does a value exist for the field in the **DFTCHG Item/SKU?

- If yes, the system should not update the exiting item/SKU with the uploaded value, regardless if the value defaulted from **DFLT ITEM.

- If no, the system should update the existing item/SKU with the uploaded value, regardless if the uploaded value is blank or defaulted from **DFLT ITEM.

This table explains when the system updates the value defined for the existing item/SKU, based on the value defined for the existing item, **DFLT ITEM, **DFTCHG, and item upload record.

The Buyer field value is:
Existing item/SKU	**DFLT ITEM	**DFTCHG	Upload record	Results
AAA	blank	blank	blank	The system updates the Buyer field for the existing item/SKU to blank.
blank	AAA	blank	blank	The system updates the Buyer field for the existing item/SKU to AAA.
blank	blank	AAA	blank	The system does not update the Buyer field for the existing item/SKU; the field remains blank.
blank	blank	blank	AAA	The system updates the Buyer field for the existing item/SKU to AAA.
AAA	BBB	blank	blank	The system updates the Buyer field for the existing item/SKU to BBB.
AAA	blank	BBB	blank	The system does not update the Buyer field for the existing item/SKU; the field remains AAA.
AAA	blank	blank	BBB	The system updates the Buyer field for the existing item/SKU to BBB.
blank	blank	AAA	BBB	The system does not update the Buyer field for the existing item/SKU; the field remains blank.
blank	AAA	BBB	blank	The system does not update the Buyer field for the existing item/SKU; the field remains blank.
blank	AAA	blank	BBB	The system updates the Buyer field for the existing item/SKU to BBB.
AAA	BBB	CCC	blank	The system does not update the Buyer field for the existing item/SKU; the field remains AAA.
AAA	BBB	blank	CCC	The system updates the Buyer field for the existing item/SKU to CCC.
AAA	blank	BBB	CCC	The system does not update the Buyer field for the existing item/SKU; the field remains AAA.
blank	AAA	BBB	CCC	The system does not update the Buyer field for the existing item/SKU; the field remains blank.
AAA	BBB	CCC	DDD	The system does not update the Buyer field for the existing item/SKU; the field remains AAA.