Oracle® Retail Merchandising and WMS Cloud Implementation Guide Release 19.0 F25718-02 |
|
![]() Previous |
![]() Next |
This chapter lists the setup and conversion of data to be done before any messages are sent between solutions. This includes conversion of master data, like stores, warehouses, and items, as well as conversion of any open transactional data and initialization of inventory levels.
For a few foundational data elements, it is recommended that you just manually set up the data in WMS Cloud to match what you have in your Merchandising implementation. This includes the configuration of order types and reason codes.
Order types are used to determine which types of transactions will be mapped to Merchandising, SIOCS, and OROB. Because of how they are managed in WMS Cloud, they cannot be pre-configured and so must be setup manually as outlined below. Create one order type record in WMS Cloud for each of the below order types. Order Type is case sensitive. Only these order types are supported in the integration.
Table 4-1 Order Types
Oracle Retail Transaction | WMS Cloud | |
---|---|---|
Order Type | Description | |
Customer Orders |
B2C |
Sales Orders |
Stock Orders and Purchase Orders |
B2B |
Stock Orders |
RTV Request |
RTV |
Return to Vendor Request |
The flags for configuration of the order types can be set however works best for your business. However, for B2C orders, the "Single Order on Multiple Loads" must be set to Not Allowed when integrating with OROB because it can only handle a single tracking number per fulfillment order.
For more information on how to create order types and how these flags are used in WMS Cloud, see the Oracle Warehouse Management Cloud documentation.
Reason codes in WMS Cloud are similar to the inventory adjustment reason codes used in Merchandising. If you already have inventory adjustments reasons configured in Merchandising, then these same codes should be setup in WMS Cloud, as appropriate. If you are adding new codes for your WMS Cloud implementation, then you will need to add them in both solutions.
Note: Merchandising requires inventory adjustment reasons to be numeric and less than or equal to four digits in length. |
Additionally, you will need to set up a reason code in WMS Cloud with the ID of RMA, which should be used for customer order returns processing in the warehouse.
For customer order returns processed into the warehouse, WMS Cloud does not update Merchandising directly, as Merchandising is expecting an update through returns processing (OMS > Sales Audit > Merchandising). So, the return should be mapped only to OMS from WMS. To prevent the return from flowing through and updating Merchandising, a reason code RMA must be created in WMS. This is what is used in the integration to filter these transactions from updating Merchandising. This reason code must be used when processing inventory adjustments in WMS related to a customer order return. To better support this process, it is recommended that you create a distinct RF screen for customer orders using the Createlpn option and as part of that setup, set RMA as the default reason-code with the mode set to either prompt for reason code OR null.
Note: Customer order returns cannot use Receiving in WMS when integrated with Merchandising. |
Lock codes in WMS Cloud are similar to inventory status codes in Merchandising. These need to be coordinated between the two solutions in order to properly map locked inventory in WMS to Merchandising. There are two components of this in WMS Cloud and two components of this in Merchandising that should be configured. First, in WMS Cloud, when creating the lock code, you will also configure an ERP bucket that this lock code should map to. This is because in some cases two lock codes can be assigned for an item in WMS Cloud, but in Merchandising it will always only have one unavailable status at a time. You can also define a priority for the lock code. If used, this will determine which is used to update Merchandising in the case that two are added or removed. Both the lock code and ERP bucket values must exist in Merchandising. See "Appendix C - Activity Codes" for details on how they are translated in the integration.
Both the lock code and ERP bucket value will also need to be created as Inventory Status Codes setup in Merchandising. Then, if you wish this code to update an unavailable inventory bucket in Merchandising, you must also map the status code to an inventory status in Merchandising. This allows you to track fewer unavailable statuses in Merchandising than in WMS, if desired. Any inventory status codes that are not mapped to a status in Merchandising will update stock on hand, but not the non-sellable quantity in Merchandising. These codes are used in processing integrations for receipts of POs and stock orders, as well as in inventory adjustments.
The example below shows how this might be configured:
Table 4-3 Merchandising
Inventory Status Code | Inventory Status |
---|---|
DM - Damaged |
1 - Damaged |
IR - In Repair |
1 - Damaged |
TRBL - Trouble |
1 - Damaged |
In this case, both the lock codes in WMS Cloud are mapped to the same unavailable bucket in Merchandising.
Note: Allocatable lock codes, which allows different classifications of available inventory, are not supported in the WMS Cloud and Merchandising integration. |
This section describes how to initialize master data in WMS Cloud based from Merchandising data. Master data includes stores, warehouses, external finishers, supplier sites, items, and item/location combinations from Merchandising. Prior to running the integration to load data into Cloud WMS, the key master data from Merchandising must be loaded into WMS Cloud, to ensure all subscribers and publishers can execute successfully.
Other Notes
The WMS Managed flag for each of the below described entities in WMS Cloud should be set to N (false).
Where necessary, truncate Merchandising values to ensure that they meet any size limitations of WMS Cloud.
In addition to the data elements called out below, you will need to validate that the codes used for language, states and countries match between Merchandising/SIOCS and WMS Cloud as part of the initial configuration.
WMS Cloud is a multi-tenant application and requires a company codeFoot 1 to be sent in all inbound integrations, including those recommended for data conversion below. The code to be used when loading data can be found in the Companies screen in WMS Cloud for your implementation.
Note: This code will be assigned to your company on provisioning of WMS Cloud. It is not the same ID created as the top level of the organizational and merchandise hierarchies in Merchandising. |
Use the action code CREATE for all conversion data loading, when required on the WMS Cloud inbound file.
To extract data from your Merchandising database to support the conversion requirements outlined below, it is recommended that you use one of the following methods:
Replicated DAS schema
Application Express (APEX) Data Viewer
BI Publisher
First, stores, partners, and warehouses must be loaded. For stores and partners, these can be setup manually in the WMS Cloud Facilities screen, or loaded from data extracted from Merchandising. It is recommended that data be loaded using the Store and Site interfaces to minimize data entry errors.
Extract the attributes for stores into a .csv file, based on the attributes in the Store API. The table below outlines how the Merchandising attributes map to the WMS Cloud attributes.
Table 4-4 Stores Mappings
Merchandising | WMS Cloud |
---|---|
store.store |
code |
store.store_name |
name |
addr.add_1 |
address_1 |
addr.add_2 |
address_2 |
addr.city |
city |
addr.county |
locality |
addr.state |
state |
addr.country_id |
country |
addr.post |
zip |
store.store_mgr_name |
contact |
store.phone_number |
phone_nbr |
store.email |
|
store.lang |
lang |
Other Notes
Use the primary business address (type 01) as the address for this import.
Extract attributes for external finishers into a .csv file, based on the attributes in the Site API. The table below outlines how the Merchandising attributes map to the WMS Cloud attributes.
Table 4-5 External Finisher Mappings
Merchandising | WMS Cloud |
---|---|
partner.partner_id |
code |
partner.partner_desc |
name |
addr.add_1 |
address_1 |
addr.add_2 |
address_2 |
addr.city |
city |
addr.county |
locality |
addr.state |
state |
addr.country_id |
country |
addr.post |
zip |
addr.contact_name |
contact |
addr.contact_phone |
phone_nbr |
addr.contact_email |
|
partner.lang |
lang |
Other Notes
Only partners of type 'E' (External Finisher) from Merchandising should be loaded into WMS Cloud.
Use the primary business address (type 01) as the address for this import.
Because WMS Cloud does not have an inbound integration for warehouses, they must be created within the solution manually. The warehouse IDs in WMS Cloud should match the physical warehouse IDs in Merchandising. Virtual warehouses will not exist in WMS Cloud. Warehouses in WMS Cloud are added in the Facilities screens. For details on how to create facilities, refer to the Oracle Warehouse Management Cloud documentation.
Note: If you add any new physical warehouses in Merchandising post implementation of this integration, those facilities will also need to be created manually in WMS Cloud using similar rules to that described above. |
Extract attributes for supplier sites into a .csv file, based on the attributes in the Vendor API. The table below outlines how the Merchandising attributes map to the WMS Cloud attributes.
Table 4-6 Vendors Mappings
Merchandising | WMS Cloud |
---|---|
sups.supplier |
vendor_code |
sups.sup_name |
name |
addr.add_1 |
address_1 |
addr.add_2 |
address_2 |
addr.add_3 |
address_3 |
addr.city |
city |
addr.state |
state |
addr.country_id |
country |
addr.post |
zip |
addr.contact_name |
contact |
addr.contact_phone |
phone_nbr |
addr.contact_email |
|
Other Notes
Only supplier sites should be loaded into WMS Cloud.
Use the primary business address (type 01) as the address for this import.
To initialize items in WMS Cloud, it is recommended that they be loaded from data exported from Merchandising to create a .csv file with the attributes required. Then, use the APIs provided by WMS Cloud for loading. Only approved items should be loaded into WMS Cloud.
Use the Item API to load transaction level items from Merchandising. The table below outlines how the Merchandising attributes map to the WMS Cloud attributes.
Table 4-7 Item Header Mappings
Merchandising | WMS Cloud |
---|---|
item_master.item |
part_a or item_alternate_code |
item_master.item_parent |
part_a |
diff_ids.diff_1_description |
part_b |
diff_ids.diff_2_description |
part_c |
diff_ids.diff_3_description |
part_d |
diff_ids.diff_4_description |
part_e |
item_master.dept |
hierarchy1_code |
deps.dept_name |
hierarchy1_description |
item_master.class |
hierarchy2_code |
class.class_name |
hierarchy2_description |
item_master.subclass |
hierarchy3_code |
subclass.sub_name |
hierarchy3_description |
item_master.item_desc |
description |
item_master.short_desc |
short_descr |
item_master.handling_temp |
shipping_temperature_instr |
(see below) |
catch_weight_method |
(see below) |
retail_price |
item_master.brand_name |
brand_code |
item_master.inventory_ind |
invn_attr_a_tracking |
image: (see below) |
description2 |
Other Notes
Only approved, transaction-level items should be loaded into WMS Cloud using this API.
For items without a parent or with a parent but not using diffs in Merchandising, part_a should be set equal to the item ID when loaded into WMS Cloud.
Merchandising will not send length, width, height, weight, and cube information to WMS Cloud. These fields will be protected in WMS Cloud. This is due to differences in UOM related fields between the two solutions.
Values not listed above are not available in Merchandising so should be left null in the import to WMS Cloud.
Item Facility records do not need to be loaded. They will be created automatically in WMS Cloud.
If an item has a defined catch_weight_type in Merchandising, then use Y for the catch_weight_method field. Otherwise, set to N.
For retail price, the integration will send the regular retail price for the item in the base zone converted to the primary currency and standard unit of measure. Similar logic can be used for the item conversion.
Hierarchy levels 4 and 5 should be left null in WMS Cloud.
The primary item image for an item in Merchandising will be mapped to the WMS Cloud description2 field for items in the integration, if defined. For conversion, it is recommended you do the same, concatenating the image_addr and the image_name for the primary image defined for your item.
Use the Item Barcode API to load reference items from Merchandising to WMS Cloud. The table below outlines how the Merchandising attributes map to the WMS Cloud attributes.
Table 4-8 Item Barcodes Mappings
Merchandising | WMS Cloud |
---|---|
item_master.item |
vendor_barcode |
item_master.item_parent |
item_barcode |
Other Notes
Only approved reference items (where tran_level+1 = item_level) from Merchandising for transaction items that meet the criteria of the item import above should be mapped in this API.
WMS Cloud requires a mandatory barcode for each item loaded into it, and in cases where Merchandising does not have this value, the item can be duplicated as the barcode.
Use the Item Pre Packs API to load the components of a pack item from Merchandising to WMS Cloud. The table below outlines how the Merchandising attributes map to the WMS Cloud attributes.
Table 4-9 Item Pre-packs Mappings
Merchandising | WMS Cloud |
---|---|
packitem_breakout.pack_no |
parent_item_code |
packitem_breakout.seq_no |
seq_no |
packitem_breakout.item |
child_item_code |
packitem_breakout.pack_item_qty |
child_units |
Other Notes
Only component details for approved, complex pack items should be loaded into WMS Cloud using this API.
Simple packs are treated as regular items in terms of stocking and inventory movement within WMS Cloud and hence they will be sent in the integration as part of the item feed.
It is highly recommended that you close all open shipments between the facilities that will be using WMS Cloud and your stores so that there are no open transfers or allocations for the facility, including any customer orders from the warehouse. This means that no stock orders would need to be converted into WMS Cloud from Merchandising. However, it is expected that some open purchase orders may exist that need to be converted. Additionally, there will be a need to initialize the inventory values in WMS Cloud and synch this with Merchandising. The sections below outline the recommended approach for this.
To initialize purchase orders in WMS Cloud, it is recommended that they be loaded from data exported from Merchandising using the Data Viewer or BI Publisher to create a .csv file with the attributes needed. Then, use the APIs provided by WMS Cloud for loading. Only approved purchase orders that have open quantity still to be received should be loaded into WMS Cloud.
Table 4-10 Purchase Orders Mappings
Merchandising | WMS Cloud |
---|---|
wh.physical_wh |
facility_code |
ordhead.order_no |
po_nbr |
ordhead.dept |
dept_code |
ordhead.supplier |
vendor_code |
ordhead.not_after_date |
cancel_date |
ordhead.currency_code |
cust_field_1 |
use conversion date |
ord_date |
ordhead.not_before_date |
delivery_date |
ordhead.earliest_ship_date |
ship_date |
ordloc.item |
item_alternate_code |
wh.physical_wh |
facility_code |
ordloc.qty_ordered (see also note below) |
ord_qty |
item_master.standard_uom |
uom |
ordloc.unit_cost |
unit_cost |
ordsku.supp_pack_size |
pre_pack_total_units |
(see note below) |
seq_nbr |
Other Notes
All open, approved POs that are shipping to a facility that will be managed by WMS Cloud.
Only include open orders in the converted data. Open orders are those in approved status in Merchandising that have non-received order quantity.
All virtual warehouse order quantity will need to be summed to the physical warehouse level for this conversion. Virtual warehouses will not be visible in WMS Cloud.
The sequence number required by WMS Cloud is similar to a PO line number and is used for all purchase orders loaded into WMS Cloud. This will need to be generated for the converted orders. The generated sequence number must match the way that the integration layer will generate sequence numbers for purchase orders going forward so that any updates to converted POs will not encounter issues. This is done using a java function hashcode() for the item to generate a unique sequence number.
Math.abs(poDetail.item.toString().hashCode())
Vendor ASNs do not need to be converted.
In order to initialize inventory levels in WMS Cloud and ensure that they match the values in Merchandising, it is recommended that a cycle count be created and the results of the count be mapped to a similar count setup in Merchandising. WMS Cloud doesn't support importing a cycle count from an external system, so the recommended approach is as followsFoot 2 :
Note: After go-live, this is the recommended approach for future cycle counting in the warehouse as well. |
Schedule a cycle count in WMS Cloud.
Schedule a corresponding stock count in Merchandising for the same date/facility including all departments.
Export the results of the count from WMS Cloud by scheduling an Inventory Summary export (see below for more information on scheduling jobs).
Configure the output interface.
Convert the format exported above into the format used by the Merchandising import for WMS stock countsFoot 3 , which is used to take a file from a warehouse management system and convert it into the format needed by the Stock Count Upload process. Make sure the date used in the extracted file matches the count created in step 2.
Note: This import is called LIFSTKUP_JOB in the job scheduler. It is also part of the STOCKCOUNTUPLOAD_PROCESS and STOCKCOUNT_UPLOAD_PROCESS_ADHOC. See the Oracle Retail Merchandising Foundation Cloud Service Operations Guide Volume 1 for more details. |
Run the appropriate processes and take the appropriate actions in Merchandising to upload the results and complete the count.
This should set the inventory levels in WMS Cloud and ensure that they match those in Merchandising.
Other Notes
Only the total quantity and/or total weight are used by Merchandising in the input file for the import. The other quantity and weight fields are ignored.
Footnote Legend
Footnote 1: This code will be assigned to your company on provisioning of WMS Cloud. It is not the same ID created as the top level of the organizational and merchandise hierarchies in Merchandising.