2 Data Entities
Base Seeding and Manual Configuration
Prior to starting the conversion activities outlined in this document, it is expected that your provisioned Merchandising environment will have the base seeding scripts run, which load core foundational data like valid currency and country codes. Some of these data entities can be tailored for your business, such as calendar, codes, and system options. It is expected this will be done as needed pre-conversion such that any dependent data is configured properly to support the conversion activities. See the Oracle Retail Merchandising Implementation Guide for more details on this configuration.
Functional Conversion
For tables that will be converted, dedicated processes used to load files of data provided by you based on cleansed data from your legacy systems are outlined in body of this document. These programs will validate data and load it into the Merchandising and Pricing tables. It will also have methods to validate and correct data errors as part of the load process.
For certain tables that are impacted as part of the conversion, there will be calculations done as part of the conversion to calculate initial values based on the converted tables.
When converting data into Merchandising, it is expected that the conversion will follow the general flow below, with the vast majority of the data converted falling into the Foundation and Item category.
![Functional Conversion Flow Functional Conversion Flow](img/func_conversion_flow.png)
Foundation Data and Item
Regardless of whether you are planning a "big bang" type conversion or planning to rollout in phases by function, you will need to start with foundation data. The data that can be converted as part of foundation data is listed below and broken into categories to help with understanding how you may want to consider converting your data. The details on what is needed to be included in conversion, are included as part of the templates that are downloaded and discussed more in the "Download Templates" section below. However, some notes are also included with the foundational entities below to add some additional context to help you determine if you need to convert data into these tables. See also the Merchandising Implementation Guide for more details on some of these foundational entities.
One other general note: across all entities you will see the inclusion of tables with a _TL extension. These tables are used to house translations of the description fields on the main table. In general, if your Merchandising users all use a single language, then these tables do not need to be loaded as part of the conversion.
However, there are a couple exceptions where the description is not held on the main table and therefore will need to be loaded with the primary language as well. These exceptions are called out below.
Core Foundation
This is data that is required by nearly every Merchandising implementation, with a few exceptions. All the data in this category should be included in your plan or have a rationale for why it is not needed.
Functional Area | Notes |
---|---|
Banners/Channels |
Channel is a mandatory attribute for stores and optional warehouses. A channel is a child of a banner, so both entities are needed prior to store conversion. |
Transfer Zones |
Transfer zones are required for all stores; if you do not wish to leverage this functionality, you can create a single zone to be associated with all stores. |
Diff Types, IDs and Groups |
Usually this is only required for items that you want to manage at a "parent" or style level that are differentiated by color or size and are most common for soft-line items. But, they can also be used in grocery or hard-lines for flavor, color, or size as well, if you want to manage attribution and pricing at a parent level. |
Freight and Payment Terms |
These usually are mastered in your financials system, but also need to be set up in Merchandising. The IDs used for the converted terms of both types should match those used in your financial system to ensure that the details on the purchase orders and invoices sent for payment from Merchandising and Invoice Matching are recognized. Also, if using the Retail Financials Integration (RFI) for integration with PeopleSoft, Oracle Financials, or Cloud Financials, there is a cross-reference that would also need to be maintained and needs to be considered in conversion. See the Merchandising Implementation Guide section on Financials Prerequisites for more details. Additionally, for both types of terms, the description for the primary language is on the translation table not the main entity table, so at least one record must be added in the translation table for your primary language for each term added. |
VAT |
Value Added Tax, or VAT, is required to be set up if you have configured Merchandising to run in the Simple VAT tax mode. This applies for all regions in the worldFoot 1 other than retailers operating in the US only, where VAT is not applicable. Even for US implementations, you may decide to implement using Simple VAT to support future expansion into other regions. In this case, at least one VAT code and region must be defined. US stores and warehouses can be defined as exempt. Also, if using the Retail Financials Integration (RFI) for integration with PeopleSoft, Oracle Financials, or Cloud Financials, there is a cross-reference that would also need to be maintained and needs to be considered in conversion. See the Merchandising Implementation Guide section on Financials Prerequisites for more details. |
Transfer Entity |
If you have configured the Intercompany Transfer Basis system option to be Transfer Entity, then this is required to be associated with all stores, virtual warehouses, and external finishers. Otherwise it is not required. |
Org Units |
All Merchandising implementations will require at least one org unit to be configured. These are usually determined by your financials solution and the codes used in Merchandising should be coordinated with financials for conversion. |
General Ledger Setup |
The General Ledger setup contains key information by set of books used in the GL mapping. One record should be added per org unit to ensure proper mapping to the chart of accounts in your GL. |
General Ledger Account |
Supports integration with the general ledger once the stock ledger is initialized. It will hold all the account related information. Set of books in the load file are validated against General Ledger Setup. |
General Ledger Cross Reference |
This cross-reference supports mapping merchandise hierarchy levels, locations, and transaction codes from Merchandising with the appropriate general ledger accounts within the financial system. This needs to be defined in order to correctly map financial transactions in Merchandising to financials at the end of a day or month. If using the Retail Financials Integration (RFI) for integration with PeopleSoft, Oracle Financials, or Cloud Financials, then the set of books in the load file will be validated against General Ledger Account. |
Currency Rates |
Currency rates are generally sourced from an external party and integrated into both Merchandising and your financials solution. For conversion, at least one active rate will be needed for each currency that is relevant for your implementation, including stores, warehouses, partners, and suppliers. This includes a record for the primary currency you configured in Merchandising as part of the install options. For that currency you should use a rate of 1. |
Footnote 1
Brazilian tax requirements are not supported by this functionality.
Optional Foundation
This is data that is foundational in nature but is not systematically required and may not be needed for your implementation. For example, seasons and phases tend to be used more for soft-line retailers, and less so for grocers and hard-lines. This information can still be setup later if not done at conversion, if you decide to leverage some of the functionality.
Functional Area | Notes |
---|---|
Cost Components |
Only applicable if you have indicated that you are using estimated landed cost in Merchandising as part of the system options configuration. But, even if using ELC, these are not required for initial conversion. |
HTS Setup and Definition |
Only applicable if Import Management functionality is being used, as indicated by the installation options that you will have defined for Merchandising. |
Seasons/Phases |
If loading this data, both seasons and phases should be added. |
Outside Locations |
Generally, only required if you are using estimated landed cost or import management functionality in Merchandising, but may be used for other purposes as well. |
Buyers/Merchants |
Optionally can be associated with levels of the merchandise hierarchy. |
Calculation Value Bases |
Only applicable if you have indicated that you are using estimated landed cost in Merchandising as part of the system options configuration. But, even if using ELC, these are not required for initial conversion. |
Freight Types and Sizes |
Only applicable if Import Management functionality is being used, as indicated by the installation options that you will have defined for Merchandising. Additionally, for both types and sizes, the description for the primary language is on the translation table not the main entity table, so at least one record must be added in the translation table for your primary language for each type and size added. |
Ticket Types |
Optionally can be associated with items. |
Brands |
Optionally can be associated with items. |
Diff Ratios |
Used for purchase order distribution in Merchandising - creates a basic diff profile that works best for sizes. |
Diff Ranges |
Can be used to designate a subset of a diff group or a matrix of diffs in two diff groups. Used in purchase order distribution. |
Country Attributes |
These are really only required for Brazil implementations. For non-Brazil implementations, this is optional. |
Inventory Adjustment Reasons, Statuses, Codes |
For all three of these entities, the description for the primary language is on the translation table not the main entity table, so at least one record must be added in the translation table for your primary language for each reason, status, and code added, respectively. |
Custom Flex Attributes |
If you have configured any custom flex attributes for diff types, ELC components and VAT codes then these can be converted as well. Make sure that the attributes are configured and active prior to conversion. For more information on this functionality, see the Customization and Extension Guide. |
Hierarchies
This data is required prior to setting up any new items or locations. It also contains some optional components, which are highlighted below.
Functional Area | Notes |
---|---|
Company |
Company is seeded during installation and, if required, can be updated during data conversion. Only one company should be loaded; company should always have the ID 1. |
Division |
Top level of the merchandise hierarchy under company; required. |
Groups |
Level below division in the merchandise hierarchy; required. |
Departments |
Level below group in the merchandise hierarchy; required. For retail accounting departments, the Markup % of Cost (BUD_INT) should be used as the initial cumulative mark-on % when initializing stock ledger valuation. |
Classes |
Level below department; required. There are two IDs on this table. Class (CLASS) is the "display ID" for the class that is seen in Merchandising screens. It is unique for a specific department. Class ID (CLASS_ID) on this table is not displayed in Merchandising screens, but is a unique ID across all departments to identify this class. It is used primarily in integration. |
Subclasses |
Level below class; required. There are two IDs on this table. Subclass (SUBCLASS) is the "display ID" for the subclass that is seen in Merchandising screens. It is unique for a specific department/class. Subclass ID (SUBCLASS_ID) on this table is not displayed in Merchandising screens, but is a unique ID across all departments to identify this subclass. It is used primarily in integration. |
Chain |
Top level of the organizational hierarchy below company; required. |
Area |
Level below chain in the organizational hierarchy; required. |
Region |
Level below area in the organizational hierarchy; required. |
District |
Level below region in the organizational hierarchy; required. |
Stores and Warehouses |
For stores and warehouses, there are multiple templates that can be used, depending on how you plan to configure your locations. If you plan to define a default warehouse for your stores, then you'll want to use the Warehouse entity for loading your warehouses first and then load your stores using the Store Add with Default WH entity. This will make sure when the business validations are run that they are processed in the correct order. For the first store loaded, you will need to leave the pricing store value null, since the system will attempt to validate the value included. It is not required for the first store created. The store can be added to the correct price zones as part of the Pricing Foundation conversion described below. For warehouses, if you plan to default in a pricing store for the virtual warehouses, then you'll want to add these after the stores using the Warehouse with Pricing Store entity. |
Optional |
|
UDAs |
Defaults can also be defined as part of the conversion for departments, classes, and subclasses to either make the UDAs you define mandatory or default a value for all new items created in the hierarchy. |
Transit Times |
Used for replenishment and in the Allocation Cloud Service; not needed if not implementing those functions |
Store Format |
Optional attribute of the store. |
Warehouse/ Department |
Only needed if implementing Investment Buy functionality in Merchandising |
Franchise Customers |
Only needed if you have stores that have franchise locations and plan to use the franchise functionality in Merchandising. If so, ensure that the franchise system option is set accordingly. |
Cost Zone Groups, Zones, and Zone Locations |
If you have indicated that you will use Estimated Landed Cost functionality as part of the system options configuration for Merchandising, then cost zone groups will be required for all new items created, and all stores and warehouses will be associated to cost zones in each group defined. Zone groups and zones are used to manage expenses associated with purchasing product and moving it from a lading port to your locations. Two zone groups are added during the installation by default and should be accounted for as part of your conversion, and more can be added, if needed. At a minimum, zones and locations should be added for the seeded groups and locations should be associated with the zones as part of the conversion. See the Merchandising Implementation Guide section on Estimated Landed Cost for more on the cost zone groups seeded as part of the solution. |
Department Up-charges |
Only applicable if you have indicated that you are using estimated landed cost in Merchandising as part of the system options configuration, which also enables the cost components used to setup up-charges. But, even if using ELC, these are not required for initial conversion. |
Custom Flex Attributes |
If you have configured any custom flex attributes for the merchandise or organizational hierarchy, including CFAS attributes for associated addresses, then these can be converted as well. Make sure that the attributes are configured and active prior to conversion. For more information on this functionality, see the Customization and Extension Guide. |
Pricing Foundation
This is required prior to creating items, but after setting up locations, as the zones are used for initial pricing. This should be setup regardless of whether you are implementing the full Pricing Cloud Service or only simplified mode.
Functional Area | Notes |
---|---|
Zone Group |
Create one or more to define how locations will be grouped together for pricing. Needed for initial pricing for your items and for future price changes and clearances. |
Initial Price Zone Definitions |
Every department, class, or subclass that you have converted into Merchandising should have an initial price zone definition that defines the zone group used for new items in that department and the details on how markup is calculated. Additionally, rounding rules can be included as part of this definition, if applicable. If you choose to setup rounding rules for the initial price zone definition, this must be done in the Pricing screens manually. Rounding rules can also be added later as well. |
Zones and Zone/ Locations |
Define the zones you want for the zone groups created as described above. And then add rows for your locations. Not all locations need to be in every zone group. Also, warehouses should only be added to zones if you have configured Pricing to manage price for warehouses (via the Recognize Warehouses as Locations system option). |
Suppliers and Partners
If integrating with a financials solution, a slightly different process for converting is recommended based on the dependency for financials as part of the creation process. The diagram below outlines the recommended process.
![Process for Integrating with Financials Solution Process for Integrating with Financials Solution](img/finance_integ_proc.png)
It is recommended you start with setting up these parties in financials and integrate them to the production version of Merchandising as a way to convert the financials owned data into the solutionFoot 2. Oracle Retail Financial Integration (RFI) has a seeding process that is used for this purpose. This also means that any dependent data for suppliers, like org units, freight terms, and payment terms, should be loaded into production. This could be done using either spreadsheet upload or the data conversion tool.
Loading the suppliers and partners from financials into production directly will generate the cross references and Merchandising IDs needed for these entities, however there still will be some Merchandising specific attributes you will need to configure, as well as you will need to copy the entities that are really partners to their own table.
For suppliers, the recommended approach would be to use the APEX Data Viewer functionalityFoot 3 to query the supplier table to extract the data that was loaded from financials and use this data as a basis to build the conversion data dat files for both suppliers and supplier sites. The same IDs should be used in conversion to reload these suppliers, but they can have the retail specific data added as well.
For partners, the recommended approach also is to extract the data from the production supplier table and use the data to convert into the partner table. The same IDs should also be loaded into the partner table in Merchandising, along with the attribution from Financials and any new attribution that is retail specific as part of the conversion.
For both of these entities, when the converted data is promoted to production, it will overwrite the data in the Merchandising tables, but the key data setup in the cross references would still be accurate and now the data would include the retail specific attributes as well.
Functional Area | Notes |
---|---|
Partner |
This includes both the partner and address information. |
Supplier |
This includes the suppliers, sites, and addresses. Both suppliers and supplier sites are setup here and should leverage information that you get from financials, if you are using the RFI integration described above. Supplier sites would have the supplier ID in the supplier parent column. |
Partner/Org Unit |
One row should exist in this table for every supplier site and partner. A supplier site and partner can only belong to one org unit. |
Optional |
|
Import Attributes |
Only required if you are using import management in Merchandising. |
Inventory Management Parameters |
This is primarily used for configuring defaults for suppliers for replenishment purposes. But, also has some PO and item defaults that you may want to configure. |
Bracket Costing |
Only required if your supplier is flagged as supporting bracket costing. |
Delivery Schedules |
This is used for replenishment only. |
Custom Flex Attributes |
If you have configured any custom flex attributes for supplier or partner, including CFAS attributes for associated addresses, then these can be converted as well. Make sure that the attributes are configured and active prior to conversion. For more information on this functionality, see the Customization and Extension Guide. |
Core Item
This section outlines the data that is either required for all items or needed for the vast majority of them that you will be converting. All the data in this category should be included in your plan or have a rationale for why it is not needed. If you are using parent/child item structures relationships setup at the parent level will not automatically default to the child level when converting using the Data Conversion application. So, if they are applicable to both levels, they will need to be added to both the parent and the child. Some of the attributes below are not applicable for sub-transaction items (such as barcodes).
Functional Area | Notes |
---|---|
Item |
The required data for item includes the item master table as well as the following tables: Item/supplier - for orderable items Item/supplier/origin country - for orderable items Item/supplier/manufacturing country - only required if the Merchandising system option for HTS tracking level is set to Manufacturing (the default is Sourcing if not otherwise configured) Item/VAT - It is defaulted from Department/VAT during item conversion if Simple VAT is configured as the default tax type. Any additional records or changes in VAT rates can be loaded, if desired. Only approved, active items will be converted. It is highly recommended that you do not load all items in one large file, but instead consider building separate files (such as, by department, class, or subclass) for better management of data volumes during the validation stages. |
Price History |
Load initial record with a tran type of 0 on this table for the item with a location of 0 using the Price Hist entity. Location-level records added in the item/location ranging are described below. |
Item Zone Price |
The zone-level pricing details will be used by the item/location ranging process described below to default the regular retail price for the item/location combinations if the retail price is not provided in the input file. If any retails need to be corrected, or if any of the items are on clearance, the selling retail price at the item/location combinations will need to be updated after converting zone pricing and item/location ranging. See also the "Pricing" section below for details on seeding clearance events and future retail. |
Item/Location Ranging |
To associate stores and warehouses with your items as part of conversion, use the Item Ranging entity. Pricing for your location can be provided in the input file during the item/location ranging process. If unit retail is not specified, it will default based on the zone pricing. This will take care of inserting records for the following entities in Merchandising: Item Location (ITEM_LOC) Item Location Stock on Hand (ITEM_LOC_SOH)Foot 4 Item Supplier Country Location (ITEM_SUPP_COUNTRY_LOC) Future Cost (FUTURE_COST) Price History (PRICE_HIST) for tran type 0 It is assumed that only active item/location combinations will be converted. |
Update Item/Location |
Pricing for your location will be established by the item/location ranging process as described above. As needed, you can use this entity to update those values. This also includes setting of the clearance flag and selling unit retail for items on clearance at the time of conversion, as needed. If you update the item/location level price you will also want to add a row in the Price History table for the new price as well. See the "Pricing" section below for more on initializing items on clearance within the Pricing Cloud Service. |
Footnote 4
All stock on hand values will be inserted as zeros. The on hands, along with any adjustments to average cost and weight can be updated later in the process as described in the section on Stock on Hand and Stock Ledger.
Optional Item
This is additional data related to items that may or may not be relevant for your implementation. It can also be added later using the basic Merchandising functionality and mass update capabilities if there is not anything to convert initially. If you are using parent/child item structures relationships setup at the parent level will not automatically default to the child level when converting using the Data Conversion application. So, if they are applicable to both levels, they will need to be added to both the parent and the child. Most of the attributes below are not applicable for sub-transaction items (such as barcodes).
Functional Area | Notes |
---|---|
Supplier/Country Dimensions |
This is not required for most items, but should be defined for catch weight items for cases and eaches. If loading this data, both primary and non-primary supplier/country should be loaded. |
Pack Item Details |
There are two tables that are required for any converted pack items - one is the basic pack details (PACKITEM) and the other goes one level deeper in detail, if the pack contains any inner packs or uses a pack template (PACKITEM_BREAKOUT). Both tables are required for all packs. |
UDAs |
If any UDA defaults were converted for departments, classes, and/or subclasses that make certain UDAs required at the item level, make sure that you convert values for those UDAs for all items in the hierarchy as part of your conversion. |
Seasons |
If loading this data, both seasons and phases should be added. Requires conversion of season information as described above. |
Images |
At least one image link must be flagged as primary, if added. |
Translations |
Translations can be added for item master level records and for item images for languages other than primary, if desired. |
Expenses |
Both header and detail are required if you choose to load zone and country level expenses associated with items. |
HTS |
Only applicable if Import Management functionality is configured on. Requires conversion of HTS information as described above. |
Custom Flex Attributes |
If you have configured any custom flex attributes for item, item/supplier, item/supplier/country, item/supplier/country/location, or item/location then these can be converted as well. Make sure that the attributes are configured and active in advance of conversion. For more information on this functionality, see the Customization and Extension Guide. |
Upcharges |
Only needed if planning to use Estimated Landed Cost functionality, which enables the cost components used to setup up-charges. |
Item Location Traits |
Not required, but there are some key attributes on this table you may want to set for your implementation that are used to drive customer ordering - including whether or not an item can be backordered. |
Tickets |
Requires conversion of ticket types as described above. |
Related Items |
Both header and detail information are required, if you choose to use this functionality. |
Pack Templates |
Only used in the creation of some pack types. |
Other
This section outlines some other entities that are related foundation and item that may be something you consider for conversion. Everything in this section is optional.
Functional Area | Notes |
---|---|
Substitute Items |
Only applicable if you are using Replenishment functionality in Merchandising. |
Item Forecasts |
Only applicable if you have a forecasting solution and wish to initialize forecasting in conversion. Data used for replenishment and allocation. |
Master Replenishment Attributes |
Only applicable if you are using Replenishment functionality in Merchandising. |
Cost Changes |
If you have any pending cost changes that are due to go into effect in your legacy systems that you would like to convert into Merchandising, the scripts in this section will support that conversion. This is optional. |
Pricing
In addition to the pricing related data called out above, there are a couple other components of pricing that you will need to consider for your conversion plan regardless of whether you are planning to use the full Pricing Cloud Service functionality, or operate in simplified mode.
Future Retail
First, you will need to seed the Future Retail table to establish the initial retail prices that were included as part of your item/location conversion.
![Seed Future Retail Table Process Seed Future Retail Table Process](img/seed_future_retail.png)
The diagram above shows how the steps that were performed in the data conversion tool are used as a basis for this process. Seeding the future retail values will be done by executing the DC_RPM_SEED_FUTURE_RETAIL
task from the System Administration screen in the data conversion tool. It is required that this be run after your foundation data and item data is loaded, but before your conversion of stock on hand and stock ledger. This will establish seed records in Pricing for all active item/locations in the system at the time the conversion is run. The task is intended to run a single time, and should be run after the Item Location conversion has been validated. The task looks directly at the ITEM_LOC
table to determine the records to be processed.
Clearance Markdowns
The other conversion for pricing to consider is any items that are currently on clearance. If you will have items that are actively on clearance when you are converting into Merchandising and Pricing, then you will need to create clearance markdowns in Pricing to initialize the markdown to allow it to be used for a future reset of clearance price, or for any inheritance for new item/location relationships. The conversion of active clearances is based on converting data from an input file using the RPM_CLEARANCE
entity in the Data Conversion application. See the "Key Data File Assumptions" section in "Prepare for Conversion" for more details on file layout and assumptions.
Column | Required? | Data Type | Notes |
---|---|---|---|
Item |
Yes |
Char (25) |
Must be a valid sellable, approved item already converted into Merchandising. It can be a parent or transaction level item. |
Diff ID |
No |
Char (10) |
If this is included, the item must contain a parent item ID and the diff must be a valid diff for one or more child items for that parent. |
Location Type |
Yes |
Number (1) |
Valid values are 0 (store), 1 (zone), or 2 (warehouse). |
Location |
Yes |
Number (10) |
Must be a valid location ID for the location type specified. |
Clearance Retail |
Yes |
Number (20,4) |
Must be greater than zero. Assumed to be in the currency of the location. |
Grouping ID |
No |
Number (15) |
Optional; could be used to group certain converted markdown records together. If not assigned, this will be systematically assigned when the processing occurs. |
Clearance Group Description |
No |
Char (250) |
Optional; could be used to describe the grouping of certain converted markdown records together. If not assigned, this will be systematically assigned when the processing occurs. |
Markdown Value |
Conditional |
Char (6) |
If the system option to require a markdown number is set to Yes, then this should be included for data consistency. Valid markdown value codes are defined using code type MKDN in Merchandising. |
Reason |
No |
Char (6) |
If you choose to include a reason code here, it needs to be a valid reason code. Valid clearance reason codes are defined using code type CMRC in Merchandising. |
This process will validate all the required fields in the clearance file are present and that all the data included is valid. It will then insert records into the markdown tables in Pricing based on this data as executed markdowns with an effective date of the current system date. It will also insert a record into the Price History tables to record the markdown details and add future retail records related to the markdowns.
Prerequisites
-
Configure system options for Pricing; unlike Merchandising, there aren't any seeded values for Pricing, so the system options should be reviewed and setup using the Pricing screen.
-
Complete the required Pricing and Item conversion described above to load zone groups, zones, items, and locations fully into the Merchandising tables, not just held in the conversion staging tables.
-
Run the future retail seeding before you do any clearance conversion.
Key Assumptions
-
The future retail data that is seeded as part of the process described above will use the current date -1.
-
The effective date for all converted markdowns will be the current system virtual date. Converted markdowns must be unique by item/location/effective date.
-
All converted clearance markdowns will be created in Executed status.
-
The clearance conversion process is intended to be run a single time and assumes no data will be present on the Pricing Clearance table when the program is run.
-
The clearance conversion process does not update the clearance flag or selling unit retail on the ITEM_LOC table with the clearance prices. The clearance retail should be updated for the item/location combination using the ITEM_LOC entity in the conversion tool as described above.
-
Price changes are not supported as part of this conversion process. It is assumed all price changes will be executed prior to your cutover and any future price changes will be entered in Pricing after cutover. The spreadsheet upload or bulk loads could be used to assist with this process, if there are too many to re-create manually.
-
Promotions are not supported as part of this conversion process. It is assumed that promotions will be halted during the conversion window for conversion and new future dated promotions will be setup in Pricing after cutover. If any promotions will be ongoing during the conversion window, then they can be created with a start date of the current date in order to continue in the new solution. At this time, the only method of creating promotions in Pricing is using the screens.
Purchase Orders
Any open purchase orders that cannot be closed prior to conversion will be able to be converted using this process. This conversion includes the conversion of the order and all line items, as well as related data about the order, such as expenses and assessments. If there is a letter of credit associated with the order, this relationship can also be converted. However, it should be noted that the letters of credit themselves are not supported in the Data Conversion Application (see assumptions below). This will be done using the Create Purchase Orders entity in the Data Conversion Application.
Additionally, if the order has been partially shipped and/or partially received, entities also exist in the Data Conversion Application to convert these as well, using the Ship Purchase Orders and Receive Purchase Orders functions, respectively. The Ship Purchase Orders scripts will create ASN records for the order, while the Receive Purchase Orders function will update the shipment and purchase orders with the receipt details.
There is also a Close Purchase Orders option that will allow for any orders that need to be converted as closed, such as if there are unpaid invoices that have not yet been received, to be updated to closed after the prior steps are completed.
If you have configured any custom flex attributes for Purchase Orders at the header or detail level, then these can be converted as well. Make sure that the attributes are configured and active prior to conversion. For more information on this functionality, see the Customization and Extension Guide.
Key Assumptions
-
All foundation data needed to support purchase orders and invoices has already been converted in a previous phase of conversion. This includes configuration of the following:
-
Procurement related system options
-
Non-Merch Codes
-
Order Context/PO Types
-
-
PO numbers should be able to be converted from legacy, assuming the legacy number is unique and fits the data requirements of Merchandising. If not, the Vendor Order number could be used to hold the legacy number and a unique PO number created to fit the Merchandising numbering scheme.
-
All POs that can be closed in legacy will be closed prior to conversion.
-
The following functional areas do not have conversion support at this time: Contracts, Deals, Customs Entry, Letter of Credit, Transportation, Obligations, Actual Landed Cost, Documents, Timelines, Work Orders.
-
Any off-invoice deals that exist in legacy should be netted out in the converted PO cost.
-
POs received in legacy systems will be reconciled, matched, and paid in legacy. For any open invoices, a process should be run post conversion of purchase orders to load the invoices using the Invoice Matching EDI upload. Invoices will not be converted in this tool.
-
Inventory updates that occur during the receipt processing for POs will update inventory; it is assumed that these will be reset during the inventory conversion to the correct values.
-
Transactional stock ledger (TRAN_DATA) records created out of the shipping and receiving of purchase orders should be cleared prior to promoting your data to production. This can be done using APEX Data Viewer.
-
The relevant item/location records needed to support purchase orders have already been established through the item/location ranging process.
Inventory Transactions
It is expected that inventory transactions will be part of the third phase of conversion, following purchase order conversion. Most inventory transactions are not expected to be converted, as it is expected that transactions in your control, such as RTVs, transfers and allocations are closed in legacy before cutover and that there is a hold on creating new transactions until you have moved over to the new system. The exceptions to this are usually customer orders and some sales history.
Customer Orders
It is likely not possible to put a hold on customer order fulfillment during your cutover to Merchandising. So, in order to support any in process orders you may have, a set of conversion scripts are provided to include the data in Merchandising needed to reserve inventory for the orders.
It is generally assumed that only in process customer orders will be converted into Merchandising and that the history of customer orders for returns purposes would be available in your order management system (OMS).
Create Customer Orders
Customer orders will be created in a number of different ways based on the details you include in the Create Customer Orders template. These are the types supported in this conversion and in Merchandising:
-
Orders to be sourced and fulfilled from the same store (via pickup or shipment from store)
-
Orders to be sourced and fulfilled from the warehouse (shipping to the customer)
-
Orders to be sourced and fulfilled from the supplier (shipping to the customer)
-
Orders to be sourced from a warehouse or store to be fulfilled from another store (via pickup or shipment from the store)
-
Orders to be sourced from a supplier to be fulfilled from a store (via pickup or shipment from the store)
For type 1, only customer order header and detail records are required. For types 2 and 4, a customer order header record is created along with a customer order type of transfer to orchestrate the movement of goods between locations or to move the inventory to a virtual store for sales processing. Types 3 and 5 use a customer order header record, along with a purchase order, to create the details of the order. These additional transactions will be created based on the data you send in the provided templates.
The details below are provided to help you with how to setup the fulfillment orders as part of the conversion for each type.
Type 1 | Types 2 & 4 | Types 3 & 5 | |
---|---|---|---|
Customer Order Number |
The overall order number - usually the customer facing number |
||
Fulfillment Order Number |
The child order number representing this specific fulfillment location combination and items (at detail level); can be one to many with the customer order number |
||
Source Location Type |
For Type 2 = WH For Type 4 = WH or ST |
SU |
|
Source Location ID |
The warehouse or store ID where the goods will be shipped to the fulfillment location or the customer |
Supplier Site ID where the goods will be shipped to the fulfillment location or the customer |
|
Fulfillment Location Type |
S |
Type 2 = V Type 4 = S |
Type 3 = V Type 5 = S |
Fulfillment Location ID |
Physical store where the order will be picked up or shipped to the customer |
Type 2 = ecommerce storeFoot 5 Type 4 = physical store where the order will be picked up or shipped to the customer |
Type 3 = ecommerce store Type 5 = physical store where the order will be picked up or shipped to the customer |
Order Placed Store |
Either the ecommerce store or the physical store locations where the customer placed their order |
Footnote 5
This should be configured as a non-stockholding store.
Ship and Receive Customer Orders
For customer order types 2-5, if there are any open shipments, or if the order was partially received in legacy, then conversion processes should also be run to convert these shipments and receipts. To do this, utilize the Ship Customer Orders and Receive Customer Orders entity templates.
First, the templates to convert any shipments should be run. If carton data has been received as part of the ASN, then that should also be able to be included in the ASN conversion. This template will support both future and backdated shipments.
Then, for those shipments that were previously received, scripts will be run that will allow receipt quantities at the transfer or PO detail level to be entered (along with dates) to process the receipts.
Key Assumptions
-
The customer order number, fulfillment order number, and fulfillment location for type 1 determines a unique order.
-
The customer order number, fulfillment order number, and sourcing location for types 2-5 determine a unique order.
-
Only in progress customer orders will be converted.
-
The conversion of customer order reserved quantities would be part of the stock on hand conversion, not covered by this process.
-
When converting customer orders that involve a warehouse, the warehouse used will be a virtual warehouse.
-
The customer orders have already been sent to the fulfilling locations (store, warehouse, or supplier). The records in Merchandising are just to finalize the fulfillment of the order.
-
The Create Customer Orders process will attempt to validate inventory in Merchandising, if the system option Validate Availability for Customer Orders should be set to unchecked (N) during conversion.
-
Inventory updates that occur during the shipment and receipt processing will update inventory; it is assumed that these will be reset during the inventory conversion to the correct values.
-
Transactional stock ledger (TRAN_DATA) records created out of the shipping and receiving of customer orders should be cleared prior to promoting your data to production. This can be done using APEX Data Viewer.
Sales History and Warehouse Issues
Merchandising doesn't require sales history or warehouse issues (outbound transfers or allocations from a warehouse) in order to operate, but some retailers have chosen to convert a certain amount of history in order to help support forecasting, replenishment, or allocation requirements when first implementing Merchandising. You can do that by utilizing the Item Location History (ITEM_LOC_HIST) template. Sales and issue history are held by item/location/week.
After conversion of sales history, you will want to schedule the history roll up batches to run to roll up history to the subclass, class, and department levels if you are using the Allocation Cloud Service as well, or if you wish to use this information for reporting purposes. Details on these can be found in the Oracle Retail Merchandising System Operations Guide, Volume 1.
Key Assumptions
-
VAT History and Daily Sales Discount tables will not be converted, as they are used for reporting purposes only. They will start to accumulate data after go live when sales begin to be processed.
-
Both store sales and warehouse issues will be able to be converted.
-
The value, gross profit, retail, and average cost columns can be included in the conversion, but as they are not required, they can be left null.
Stock on Hand and Stock Ledger
The final step in conversion is to initialize the stock on hand and stock ledger. This should be done after all the other data described above is loaded. Before this step you should also ensure that you have cleaned up all stock on hand information prior to conversion to ensure that stock levels are accurate and you should validate accurate unit cost and unit retail information has been set in the system as part of the initial item/location conversion.
Non-Sellable Inventory
Before the stock on hand is converted, if there is any non-sellable inventory for item location combinations that you need to convert, it should be done first. The below table outlines the data that is included in the conversion file.
Column | Notes |
---|---|
Inventory Status |
This should be from pre-configured non-sellable inventory status types in Merchandising. |
Quantity |
This should be loaded as positive quantity in standard UOM for the inventory status. |
Stock on Hand
To do this, you'll want to export the Item Location Stock on Hand records that were generated in the Item Location Ranging process described above. Then you will need to update those records with the inventory snapshot values from your legacy solution. Values that should be initialized as part of this process include:
Column | Notes |
---|---|
Average Cost |
If your cost method is configured as standard cost on Merchandising system options, set this to the unit cost value that you used during the item location ranging conversion. Otherwise, set this to the current weighted average cost of the item at this location. |
Stock on Hand |
Set this value to the number of units you have on hand that are not part of pack items for this item at this location. This should be inclusive of all reserved quantity, but not include in transit, expected, or backorder. |
In Transit |
Assumed to be zero unless you have shipped/not received customer orders of type 2 or 4. If so, this should be the total number of units shipped but not received for the receiving store on the customer order transfer. |
Pack Component In-Transit |
Assumed to be zero even if open customer orders exist, as stores don't track pack inventory. |
Pack Component Stock on Hand |
Set this value to the total number of units you have on hand that are part of pack items for this item at this location. If the item is a pack and/or the location is a store, it should always be zero. |
Transfer Reserved |
Assumed to zero unless you have customer orders that have not yet been shipped of types 2 or 4; this would have a value for the shipping store or warehouse only. |
Pack Component Reserved |
Assumed to zero unless you have customer orders that have not yet been shipped of types 2 or 4 involving pack items; this would have a value for the shipping warehouse only. If the item is a pack and/or the location is a store, it should always be zero. |
Transfer Expected |
Assumed to zero unless you have customer orders that have not yet been shipped of types 2 or 4; this would have a value for the receiving store only. |
Pack Component Transfer Expected |
Assumed to be zero even if open customer orders exist, as stores don't track pack inventory. |
Non Sellable Quantity |
Set the quantity to the number of non-sellable units across all inventory statuses for this location. This should match the total converted for the item/location on the INV_STATUS_QTY table. |
Customer Reserved |
Assumed to be zero unless you have customer orders of type 1 that are open. |
Customer Backorder |
Set the quantity to the number of units on backorder you have for this location. Backorders are assumed to be managed in your OMS, there are no other records in Merchandising other than this quantity. |
Pack Component Customer Backorder |
Set the quantity to the number of units for a component of a sellable pack at this location that are on backorder. Backorders are assumed to be managed in your OMS, there are no other records in Merchandising other than this quantity. If the item is a pack and/or the location is a store, it should always be zero. |
First Received |
Optional - could be used for reporting purposes |
Last Received |
Optional - could be used for reporting purposes |
First Sold |
Optional - could be used for reporting purposes |
Last Sold |
Optional - could be used for reporting purposes |
Average Weight |
Should be seeded for catch weight simple pack items in the warehouse only. |
Finisher Average Retail |
Only needed for external finisher locations if inventory is present at the finisher. |
Finisher Units |
Only needed for external finisher locations if inventory is present at the finisher. |
Stock Ledger
Once you have created the files based on the information as described above and it has been loaded and validated by the Data Conversion application, execute the STOCKLEDGER_PROCESSING
task in the System Administration screen to calculate the opening balances for the stock ledger. Stock ledger records will have been seeded based on the conversion of subclasses and locations, but the values will all be zero. This task will calculate an opening inventory balance in terms of local currency for both the month and week (depending on your system option settings) for every subclass/location combination where there is inventory to establish. Details on how to do this can be found in the "Task Execution Engine" section of this document.
Key Assumptions
-
RTVs, allocations, and transfers not related to customer orders are not currently supported in the Data Conversion application. Oracle Retail highly recommends that you close all open transactions in your legacy solutions and hold new activity during the conversion cutover window and then start any new activity once you are live in Merchandising.
-
The relevant item/location records have already been established at this phase of conversion through the item/location ranging process. If not, then this should be a pre-conversion step for stock on hand conversion.
-
Ensure the
SYSTEM_VARIABLES
andPERIOD
tables have correct data before loading store and warehouse data. The stock ledger tables records will be inserted based on this and any incorrect data will result in issues during the stock ledger initialization. -
Stock Ledger conversion should be done in conjunction with the beginning of an accounting period in order to establish a full month's worth of transactions after cutover. There will be no historical balances converted.
-
A primary currency version of opening or closing stock values in the stock ledger is not recorded in Merchandising and so is not required as part of conversion.
-
Half Data records will be added for all subclasses and locations converted as part of the foundation conversion. Additional conversion for this table is not required - it will grow over time.
-
Half Data Budget will be added for all departments and locations converted with null values in the calculated columns in the foundation conversion. If you wish to update the cumulative mark-on percent in advance of the stock ledger conversion, to use that value for calculating the cost complement, then this can be done in the Merchandising UI using the spreadsheet upload functionality. For more details on this, see the Oracle Retail Merchandising Foundation Cloud Service Finance User Guide section on "Managing Budgets".
-
Week Data will only be updated if the system option for Stock Ledger Time Interval is Week (W).
-
Last update datetime and ID will be updated automatically as part of the conversion.
Converting Other Data
For the data entities that are not supported by the Data Conversion application, the following can be leveraged to load data, as needed:
-
Leverage spreadsheet uploads for loading foundational data not supported in the tool. This may be especially helpful if you want to configure some of the data that Merchandising seeds automatically at provisioning, such as countries and states, or in cases where you do not have a data source in legacy that can easily be used for programmatic conversions, like Sales Audit reference fields.
-
Leverage the vast API library for converting data not supported in the conversion tool, such as transfers or promotions. All the Merchandising cloud solutions have a number of different APIs that could be leveraged via different integration methods - web services, RIB messages, and batch uploads.
-
Write custom scripts that can be run in the non-production environment that you are using for conversion. This is similar to the approach described above for Stock Ledger seeding. The APEX Data Viewer in non-production environments allows for the running of PL/SQL scripts that insert, update, or delete records in the Merchandising non-production database. It should be noted, however, that you will not be able to create tables or call base package functions as part of this process.
There are also several areas that are excluded from the eventual export from the non-production environment that you are using for data conversion to the production environment because of dependencies that require them to be set up differently than the data described above.
Sales Audit Totals and Rules
Sales Audit Totals and Rules are normally excluded from the lift and shift process because there are additional dependencies to fully set them up in a new environment. If you have setup and tested totals and rules up in your non-production environment and want to have them migrated to production, then you will need to migrate them separately using these steps:
-
Export the total and rule configurations from the non-production environment by running SA_RULES_TOTAL_EXTRACT_JOB from POM. This will generate sartexp_<table_name>.dat files.
-
Validate the extract and, as necessary, remove unwanted totals and rules.
-
Log an SR with the Oracle Cloud Operations team to upload the extracts to the production environment. As part of the SR, they will load the extract files into production, and then execute the batch required to generate the procedures used by Sales Audit.
For migrating the totals and rules data from one non-production environment to another non-production environment, you are able to execute the full migration process. This is done by executing SA_RULES_TOTAL_EXTRACT_JOB and SA_RULES_TOTAL_UPLOAD_JOB from POM to extract and upload the data respectively in the appropriate environments, using the extracted file similar to what is described above. If there are existing totals and rules records in the target non-production environment, it is recommended that you first purge them using APEX Data Viewer.
Totals and rules can be migrated in worksheet, submitted, or approved status.
Custom Flex Attributes (CFAS)
To migration CFAS attributes from a pre-production to a production environment, leverage CFAS download/upload spreadsheet feature from the UI. Using this approach, download the CFAS foundation data (that is, group sets, groups, record groups, and/or their labels from the pre-prod environments) and upload this spreadsheet to the production environment. Migrate the CFAS attributes in the similar manner. The last step is to create CFAS view by activating the migrated attributes in the production environment. This will also make the attributes visible from the UI. For more information on this process, see the Merchandising Customization and Extension Guide.
Custom Validation Rules
As with Sales Audit Totals and Rules, migration of Custom Validation Rules from a pre-production to a production environment will require you to log an SR to coordinate with the Oracle Cloud Operations team.
Data Filtering
If you plan to configure Merchandising to filter data based on merchandise or the organizational hierarchy and have set up the configuration for your users in non-production, you can migrate this to your production environment by downloading the configurations into a spreadsheet (Foundation Data > Download Foundation Data) using the spreadsheet templates under template type Data Filtering and then uploading the same into the production environment.
Duty and Privilege Configuration for Roles
Often the users and/or the roles and duties of users differ between non-production environments and production environments. However, if you are using a non-production environment to test out the configurations for each of the roles to be used in Merchandising solutions, you can migrate the configuration between instances by exporting the configuration from the non-production environment and then re-importing them into production. This is performed in the Oracle Retail Application Administrator Console using the following steps:
-
Download configurations by accessing Settings > Security > Policy Backups and selecting the Download action.
-
Edit the XML file that results to remove the _PREPROD extension from the user roles, so that they will be valid production role names.
-
Import configurations by accessing Settings > Security > Policy Patching and then selecting Synch with Patch for the latest patch applied in your environment. This will take you to the Base Policies page, where there is a button, Import Custom Policies, that will allow you to import the file created in the download step.
For more on these functions, see the Merchandising Administration Guide chapter on Security.
Converting Non-Merchandising Solutions
If you are implementing other Oracle Retail solutions at the same time as your Merchandising implementation, or shortly thereafter, Merchandising can be used to seed the data into those environments. It should be planned that those solutions are converted after Merchandising conversion has been completed.
To convert other solutions, such as store solutions (SIOCS, Xstore), planning (MFPCS, A&IPCS), or omni-channel (OROMS, OROB), Merchandising has a number of bulk data integration (BDI) scripts intended to support this. For Retail Insights (ORMI), you can run the full load integration using RDE (Retail Data Extractor) from Merchandising.
Footnote Legend
Footnote 2:The RFI Implementation Guide will have details on how to query the suppliers to integration to Merchandising from financials for this initial load.
Footnote 3:
Application Express (APEX) Data Viewer is a tool that allows you to query and update data in your pre-production SaaS environment. Details on how to access and configure users for this environment can be found in the Oracle Retail Merchandising Foundation Administration Guide.