Data Conversion

4 Data Conversion

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.

Manual Conversion

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

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
Oracle Retail Transaction	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, with some exceptions described below.

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.

Stock Orders

The Partial allocation flag must not be checked when integrating the stock order status for stock allocation or deallocation with Merchandising.

Customer 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 for each fulfillment order.

Return to Vendor Request

To support multiple inventory status codes in the stock return request from Merchandising, as well as to send RTV shipment with the inventory from a locked status to Merchandising, the following configurations must be made for an RTV order type:

Eligible Lock Code for Allocation - must be set to a comma-delimited list of all lock codes that could have RTVs created against them. In Merchandising, these lock codes are mapped to the inventory status codes within an inventory status. For more details on the lock code and inventory status code mapping, see the "Lock Codes" section below.
Wave Flag - this flag must be unchecked. RTV orders will be allocated and picked using the "RF Direct Allocation" or "RF Pick and Allocate" modules in WMS.
Allocate during Pick - this flag must be checked to allow allocation when the order is picked for return.

Reason Codes

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 reason codes 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.

Customer Order Returns

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.

Receiver Unit Adjustments

If you want receipt adjustments made during pre-verification to be communicated to Merchandising as receipt adjustments, rather than just an inventory adjustment, then you should configure a reason code in WMS Cloud named RUA. This will be a signal to the integration of this adjustment to map it to the Merchandising as a receipt adjustment rather than an inventory adjustment. To better support this process, it is recommended that you create a distinct RF screen for receipt adjustment using the Modifylpn option and, as part of that setup, set RUA as the default reason code with the mode set to either prompt for reason code OR null.

Note:

The RUA reason code should only be used for the purpose described above. Use of that reason code for other adjustments will raise errors, because Merchandising requires that reason codes for inventory adjustments be numeric.

Lock Codes

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 - 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, inventory adjustments and RTVs.

The example below shows how this might be configured:

Table 4-2 WMS Cloud

Lock Code	ERP Bucket
DM - Damaged	TRBL
IR - In Repair	TRBL

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.

Convert Master Data

This section describes how to initialize master data in WMS Cloud based from Merchandising data. Master data includes stores, warehouses, external finishers, supplier sites and items 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 code^Foot 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

Stores, Partners, and Warehouses

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.

Stores

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

WMS Cloud	Merchandising
code	store.store
name	store.store_name
address_1	addr.add_1
address_2	addr.add_2
city	addr.city
locality	addr.county
state	addr.state
country	addr.country_id
zip	addr.post
contact	store.store_mgr_name
phone_nbr	store.phone_number
email	store.email
lang	store.lang

Other Notes

Use the primary business address (type 01) as the address for this import.

External Finishers

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

WMS Cloud	Merchandising
code	partner.partner_id
name	partner.partner_desc
address_1	addr.add_1
address_2	addr.add_2
city	addr.city
locality	addr.county
state	addr.state
country	addr.country_id
zip	addr.post
contact	addr.contact_name
phone_nbr	addr.contact_phone
email	addr.contact_email
lang	partner.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.

Warehouses

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.

Vendors

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

WMS Cloud	Merchandising
vendor_code	sups.supplier
name	sups.sup_name
address_1	addr.add_1
address_2	addr.add_2
address_3	addr.add_3
city	addr.city
state	addr.state
country	addr.country_id
zip	addr.post
contact	addr.contact_name
phone_nbr	addr.contact_phone
email	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.

Items

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.

Item Header

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

WMS Cloud	Merchandising
part_a or item_alternate_code	item_master.item
part_a	item_master.item_parent
part_b	item_master.diff1\|':'\|diff_ids.diff_1_description - truncated to fit the field size in WMS Cloud
part_c	item_master.diff2\|':'\|diff_ids.diff_2_description - truncated to fit the field size in WMS Cloud
part_d	item_master.diff3\|':'\|diff_ids.diff_3_description - truncated to fit the field size in WMS Cloud
part_e	item_master.diff4\|':'\|diff_ids.diff_4_description - truncated to fit the field size in WMS Cloud
hierarchy1_code	item_master.dept
hierarchy1_description	deps.dept_name
hierarchy2_code	item_master.class
hierarchy2_description	class.class_name
hierarchy3_code	item_master.subclass
hierarchy3_description	subclass.sub_name
description	item_master.item_desc
short_descr	item_master.short_desc
shipping_temperature_instr	item_master.handling_temp
catch_weight_method	(see below)
retail_price	(see below)
brand_code	item_master.brand_name
invn_attr_a_tracking	item_master.inventory_ind
description2	image: (see below)

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.

Item Barcodes

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

WMS Cloud	Merchandising
vendor_barcode	item_master.item
item_barcode	item_master.item_parent

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.

Item Pre-packs

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

WMS Cloud	Merchandising
parent_item_code	packitem_breakout.pack_no
seq_no	packitem_breakout.seq_no
child_item_code	packitem_breakout.item
child_units	packitem_breakout.pack_item_qty

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, hence they will be sent in the integration as part of the item feed.

Item Supplier Pack Size

Use the Item Supplier Country API to load the pack size details for an item from Merchandising to WMS Cloud. The table below outlines how the Merchandising attributes map to the WMS Cloud attributes.

Table 4-10 Item Supplier Pack Size Mappings

WMS Cloud	Merchandising
std_pack_qty	inner_pack_size
std_case_qty	supp_pack_size
lpns_per_tier	ti
tiers_per_pallet	hi

Other Notes

This is an optional data point; the item supplier pack size details are not interfaced in the default setup of this integration.
Only pack sizes for the primary supplier and country are loaded into WMS Cloud using this API.

Item Dimensions

Use the Item Dimension API to load the dimension details for an item from Merchandising to WMS Cloud. The table below outlines how the Merchandising attributes map to the WMS Cloud attributes.

Table 4-11 Item Dimensions Mappings

WMS Cloud	Merchandising
length	Based on item_supp_country_dim.dim_object, For dim object as each, item_supp_country_dim. unit_length For dim object as inner, item_supp_country_dim. std_pack_length For dim object as case, item_supp_country_dim. std_case_length
width	Based on item_supp_country_dim.dim_object, For dim object as each, item_supp_country_dim. unit_width For dim object as inner, item_supp_country_dim. std_pack_width For dim object as case, item_supp_country_dim. std_case_width
height	Based on item_supp_country_dim.dim_object, For dim object as each, item_supp_country_dim. unit_height For dim object as inner, item_supp_country_dim. std_pack_height For dim object as case, item_supp_country_dim. std_case_height
weight	Based on item_supp_country_dim.dim_object, For dim object as each, item_supp_country_dim. unit_weight For dim object as inner, item_supp_country_dim. std_pack_weight For dim object as case, item_supp_country_dim. std_case_weight

Other Notes

This is an optional data point; the item dimension details are not interfaced in the default setup of this integration.
Only dimensions for the primary supplier and country are loaded into WMS Cloud using this API.

Convert Transactional Data

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.

Purchase Orders

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-12 Purchase Orders Mappings

WMS Cloud	Merchandising
facility_code	wh.physical_wh
po_nbr	ordhead.order_no
dept_code	ordhead.dept
vendor_code	ordhead.supplier
cancel_date	ordhead.not_after_date
cust_field_1	ordhead.currency_code
ord_date	use conversion date
delivery_date	ordhead.not_before_date
ship_date	ordhead.earliest_ship_date
item_alternate_code	ordloc.item
facility_code	wh.physical_wh
ord_qty	ordloc.qty_ordered (see also note below)
uom	item_master.standard_uom
unit_cost	ordloc.unit_cost
pre_pack_total_units	ordsku.supp_pack_size
seq_nbr	(see note below)

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.

Inventory

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 follows^Foot 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).

Figure 4-1 WMS Scheduled Jobs
Configure the output interface.

Figure 4-2 WMS Output Interface Configuration
Convert the format exported above into the format used by the Merchandising import for WMS stock counts^Foot 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.

Footnote 2:

After go-live, this is also the recommended approach for future cycle counting in the warehouse as well.

Footnote 3:

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.