Integration Considerations

Foundation Data

There are a number of basic data elements that are common between the Merchandising and store systems but which are not part of the integration. This is because they are generally a one-time set up at initial implementation with only infrequent updates afterward. However, because this data is foundational to how the solutions work, it is critical that they are set up properly. These data elements fall into a couple different categories:

Note:

The volume of data used in the integration can provide operational challenges if the time required to import and deploy data to stores takes longer than desired. This is generally only a concern when number of items and number of stores are very large, and is mostly a concern when processing initial loads.

Seed Data

Seed data refers to data that is loaded into both solutions on implementation by Oracle Retail provided install scripts. These are coordinated between solutions as part of the base installation, but if any updates are made in one solution to add or remove items, the corresponding change should be made in the other solution. Data elements that fall into this category are:

Currency codes
Country codes
Units of measure

Transaction Details

The mapping of transaction details from Xstore POSlog to Sales Audit RTLog depends on the mappings of valid values. These mappings are detailed in Appendix: Xstore to Sales Audit Mapping Details. It is critical that the mappings are complete. If additional valid values are configured for Xstore in the RTLogMappingConfig.xml, they must also be configured for Sales Audit for the appropriate code types.

Similar to seed data, some initial data is provided for the data entities in this category, but this is an area that is more commonly configured for retailers based on their specific business processes. On initial implementation, the configurations in both Xstore and Sales Audit should be made to be in synch, with any changes made post-implementation continuing to be made in both solutions. The entities in this category include:

Transaction Types
Tender Types
Tender Total IDs
Item Types
Reason Codes
Item Statuses
Sales Types

See the Appendix: Xstore to Sales Audit Mapping Details for details on configuring and mapping these entities.

Multi-Line Text

Use of multi-line descriptions in Merchandising for data flowing to the Xstore Suite must be avoided because it can result in data loading failures by Xstore's DataLoader. All text fields used in the integration are expected to be single-line.

Currency Exchange Rates

Exchanges rates for currencies are not one of the things integrated between Merchandising and Xstore, as Merchandising is not considered the system of record for this information at a retailer - generally that comes from the financial solution. However, if you require currency exchange rates in Xstore, then it is expected that the same source of data used for exchange rates in Merchandising will also be used to load those rates into Xstore, in order to ensure both solutions are operating with the same information and to prevent a financial impact from occurring due to differences in the rates used. Tender exchange transactions that occur in Xstore, where a customer is given USD in exchange for CAD, will be mapped to the transaction type OTHER in Sales Audit.

Stores

By default, Xstore is configured to allow four digit store IDs, but it can be configured to hold up to 5 digit store numbers in the SequenceConfig.xml. Although Merchandising can hold up to a 10 digit store ID, when integrating with Xstore, it is strongly recommended that only four or five digit location IDs are used. Custom modifications would be required to Xstore to support larger store IDs.

Additionally, latitude and longitude information that is used by Xstore to determine nearby stores for its inventory lookup function are not available as part of the integration from Merchandising. If you wish to use this functionality in Xstore, the record type, RETAIL_LOCATION_COORDINATES, is available to DataLoader to populate the latitude and longitude of stores using the .mnt format.

All stores in Merchandising that you expect to get a sales file from Xstore should be set up to have Integrated Sales flag set to checked (Yes) as part of the store setup. Additionally, the store level attribute Unique Transaction Number should be set to Register for all Xstore integrated stores. These are both used by Sales Audit to know which stores to expect to receive files and how to audit the data in the files.

Merchandise Hierarchy Levels

Xstore supports up to four levels of merchandise hierarchy. This means that up to four of the lowest level Merchandise Hierarchy Levels used by the Merchandising System can be mapped to the Xstore Suite Merchandise Hierarchy Levels. The default Merchandise Hierarchy Levels used by Xstore (Department, Sub Department, Class, Subclass ) do not match the bottom four levels of the merchandise hierarchy from Merchandising, which are Group, Department, Class, and Subclass. Retailers should use the Merchandise Hierarchy Level feature under Xadmin’s Hierarchy Manager to configure the desired mapping. The most common mapping is either four levels (Group, Department, Class, and Subclass) or three levels (Department, Class, and Subclass). It is critical that this mapping is established before the integration is activated and any data is imported into the Xstore Suite from the Merchandising system. It is also critical that the Merchandise Hierarchy Levels are not changed once item data has been imported.

Figure 5-1 Merchandise Hierarchy Levels

Merchandise Hierarchy Identifiers

Xstore requires the use of unique indentifiers across all levels of the merchandise hierarchy. Merchandising does not have this requirement, so to create uniqueness the integration transforms keys imported from merchandising by appending a character representing the level as a suffix. An example would be Department 10 in Merchandising becomes Department 10D in Xstore.

Additionally in Merchandising, the ID displayed for Class and Subclass are not by themselves unique, these IDs are only unique in the context of their parents’ ID. In Xstore, and some other Point Of Service systems, merchandise hierarchy IDs must be unique within the same level. Merchandising enables integration with Point Of Service systems having this requirement by also maintaining a unique key for Class and Subclass -- the unique key is held in the Merchandising tables for class and subclass. This unique value is not visible to users of Merchandising. The Xstore Suite imports only the unique key for use in Class and Subclass identifiers.

Items

This section lists considerations regarding items.

Merchandise Items

Physical merchandise items should be mastered in Merchandising and use the integration described in this document to flow the data to Xstore. Xstore Office should not be used to create physical items in order to prevent errors when loading sales data into Sales Audit where the item being sold or returned cannot be identified and accounted for in Merchandising.

Non-Merchandise Items

If using non-merchandise items, such as warranties, fees, and services, in Xstore, special attributes are required that are not available in Merchandising. Therefore to configure these items, the following approach is required:

Create the non-merchandise item in the Xstore Office UI, specifying the required attributes to control its behavior in Xstore.
Create an item in Merchandising with the same ID as that created in Xcenter. The item created in Merchandising should be set up as a non-merchandise item to prevent it from being re-exported to Xstore.

The creation of the item in Merchandising will prevent any errors from occurring in the auditing process. Any maintenance on the non-merchandise items should occur in Xstore Office going forward.

Note:

For more information on how non-merchandise items work between Sales Audit and Merchandising, see the Oracle Retail Sales Audit Implementation Guide.

To allow end users to create non-merchandise items, but be prevented from creating or editing merchandise items in Xstore, the CFG_MERCH_ITEMS privilege should not be granted to any users. The merch items option will still be on the screen, but it will not be accessible.

Kit/Pack Items

Kits, or pack items in Merchandising, are items that contain multiple components but are sold as a single unit. As part of the standard item integration, Xstore does not import the component level information from Merchandising, so these items will appear as standard items in Xstore and the component details will not be available.

Differentiators

Differentiators are used in Merchandising to define how a transaction level item (for example, SKU) differs from its parent (for example, style). For example, a differentiator might be a color, size, or flavor for an item. In Xstore, differentiators are called dimensions. Merchandising supports up to 4 differentiators/dimensions for items, while Xstore can support only three. It is strongly recommended that the 4th differentiator is not used when implementing Merchandising with Xstore, as it will be ignored in the integration.

Additionally, in Merchandising an item can be assigned differentiators without having a parent (style) associated with it. This could be used for hardline or grocery items to indicate the color or size of an item for reporting purposes, for example. However, in Xstore dimensions are primarily used to allow a user to determine the sellable SKU by entering a style ID and selecting the valid dimensions (usually color and size). Therefore, if an item does not have a parent, the dimensions sent from Merchandising will be ignored and will not be visible in Xstore.

Product Restrictions

Product restrictions can be set up in Merchandising to indicate limitations on certain products. For example, a restriction may be set up to limit alcohol from being sold to customers under a certain age. Product restrictions are not currently supported in the integration to Xstore. Custom integration would be required to communicate this information from Merchandising to Xstore.

Related Items

Merchandising has a concept of related items that can be used to define items that are substitutes for one another, or that could be used to cross-sell or up-sell to a customer when purchasing the main item. Substitute items from Merchandising are mapped to the Xstore substitute items to indicate items that may be substituted or offered in place of another item.

The cross-sell and up-sell types of related items are mapped to Xstore's Attached Items and configured as prompt-to-attach. Only transaction level related items are used by Xstore. Those created at the parent item level (for example, style) in Merchandising are ignored.

Other Item Attribute Notes

The set of data entity attributes managed by the Xstore Suite and MFCS overlap but are not identical. Some data and fields supported by Xstore can not be obtained from Merchandising, and some data and attributes are imported but require transformation to bridge differences in the two systems. The following is not an exhausted list but does call out some key differences:

Item Restocking - unlike Xstore, Merchandising does not have a flag that indicates whether an item is subject to an item restocking fee, nor the ability to define what an item's fee would be. Therefore, Xstore would not have the ability to prompt for a restocking fee during returns.
Xstore can support prorated refunds for items, but to do so requires specific attributes sent for an item, which are not currently available in Merchandising. Therefore, this function would not be available in Xstore.
Merchandising has the ability for retailers to extend the available item attribution by creating user defined attributes and custom flex attributes. Although included in the available data from Merchandising, these are currently not used by Xstore.
Translated item descriptions are available from Merchandising as part of the integration but are not currently used by Xstore. Xstore uses the item level description (which is communicated in the primary Merchandising language) for Xoffice and the item/location level descriptions for the store in Xstore. If you have the requirement to send item descriptions in different languages to your stores, it is recommended that the item/location level description in Merchandising be updated to show the localized item description.
Merchandising does not maintain a Quantity Scale item attribute that can be directly mapped to Xstore’s itm_item_options.qty_scale column.

Tax

This section describes considerations regarding taxes.

Value Added Tax (VAT)

Merchandising integration includes VAT rates and the regions in which the stores have been classified for companies with operations in geographies where this type of tax is applicable. For retailers that have operations in both VAT and non-VAT regions - such as stores in the US and Canada - non-VAT regions are configured as exempt in Merchandising and communicated as such to Xstore. For more information on configuration for VAT in Xstore, see the Oracle Retail Xstore Technical Guide.

When Merchandising sends VAT rate updates for an item, it also includes the active date for the rate to be applicable. Retailers sometimes enter new VAT rates in advance for future planning. However, Xstore currently does not support an active date for VAT code and will ignore the active date sent, which means any new codes will go into effect immediately. Therefore, it is recommended that retailers enter the VAT code changes in Merchandising only when needed.

Note:

Buying from a VAT store and returning to a non-VAT store (and vice versa) is not supported in Xstore.

US Sales Tax

Merchandising does not provide US Sales tax information to Xstore; it is assumed that product tax groups are imported into Xstore from a third-party system using Xstore Point of Service DataLoader and .mnt files.

After loading Merchandising data, the following additional steps are required to configure sales tax using the .mnt file format:

Set up sales tax rules. To set up a simple rate based tax rule, use existing record types TAX_LOCATION, TAX_AUTHORITY, TAX_GROUP, TAX_GROUP_RULE, and TAX_RATE_RULE to populate tax tables tax_tax_loc, tax_tax_authority, tax_tax_group, tax_tax_group_rule, and tax_tax_rate_rule. For more details on tax rule configuration, see the TAXING section in the Oracle Retail Xstore Point of Service Host Interface Guide available on My Oracle Support.
Set up retail store and tax location mapping in table tax_rtl_loc_tax_mapping using existing record type TAX_RETAIL_LOCATION_MAPPING. For more details on this record type, see the TAXING section in the Oracle Retail Xstore Point of Service Host Interface Guide available on My Oracle Support.
ITEM_TAX_GROUP is used to update the item record in the itm_item_options table with sales tax group ID. This .mnt file has to be imported after the Merchandising data import. There is no built-in mechanism in DataLoader or Xstore Office to ensure this ordering. It has to be enforced by retailer manually.

Note:

There is also a configuration of tax data that must be done in Sales Audit when using the US Sales Tax configuration. See Appendix: Xstore to Sales Audit Mapping Details for more details.

Inventory

Inventory functionality in Xstore should be disabled when implemented with Merchandising. No inventory information is integrated between Xstore and Merchandising other than sales related data and it is assumed store inventory is managed in another application, such as Oracle Retail Store Inventory Management (SIM) or Store Inventory and Operations Cloud Service (SIOCS), which is also integrated with Merchandising. Therefore, when these systems are all part of a retailer's implementation, the .sim entry in the configuration path should be used in Xstore to turn off Xstore inventory functionality. Inventory integration outside of sales and returns between Merchandising and Xstore is not supported.

Serialized Inventory

Merchandising supports the concept that an item can be a serialized item in one store, but not in another; however in Xstore, the designation for whether or not an item is serialized is held at the item level, so there is not any differentiation by store.

Note:

Merchandising does not support serialized inventory at this time. It only flags items as being serialized or not.

OCDS/MFCS: Unlike Xstore, Merchandising does not have a non-store-specific serialized item flag, so the integration infers this information. Merchandising does have a uinType item-level field that is store-specific. XOCS uses this field to recognized an item as “a serialized item” when receiving item header data for a store. The Xstore Suite’s serialized item flag attribute is stored in its itm_item table. This table is not a store-specific item attribute table, though the data maintained in the table could represent the assortment of a single store, or the assortment of all stores.

A store’s database’s itm_item is populated by the results of requests from MFCS/OCDS for item data for a specific store. The response includes only items in that store’s item-assortment AND an item’s store-specific uinType (if one exists). This allows Xstore to properly set the serialized item flag at the store in the itm_item table
- When Xcenter’s database’s itm_item table is populated by the results of requests from MFCS/OCDS, item records represent the assormtment in all stores and are stored at the *:* level. Since the context of this data is not specific to any one store the concept of a store-specific uinType doesn’t apply, and the serialized item flag will be false.

Flat File: The serialized flag can vary by location for an item in Merchandising, the last location to be dataloaded by the integration code sets the item level serialized flag in the Xcenter database. Since a store database is populated with the item assortement for only for a single store, the serialized flag will be appropriate set.

Customer Orders

When customer orders are initially captured in Xstore, the Xstore RTLog generator sets the Fulfillment order number in the RTLog to UNKNOWN, as the fulfillment order number is not known at the time the order is created, because information has not yet been sent to the order management system.

In-Store Orders

Orders taken in the store on behalf of a customer that do not go through an Order Management System (OMS) for fulfillment will include only a customer order number, but not a fulfillment order number when it the transactions related to it are integrated to Sales Audit.

Recognition of a Sale

For customer orders, Xstore can be configured to recognize a sale at either the time the order is place or at the time of pickup. Integration with Merchandising requires that this configuration be time of pickup, which corresponds to when inventory is decremented from the store, in order to prevent out of synch issues between actual store inventory and what is shown in Merchandising.

In order to configure this in Xstore, the following settings should be set to false (which is the default) under both <Layaway> and <SpecialOrder> in SystemConfig.xml (whose settings are also controllable in Xadmin):

<Layaway>
<BookAsSaleOnSetup dtype="Boolean">false</BookAsSaleOnSetup>
<SpecialOrder>
<BookAsSaleOnSetup dtype="Boolean">false</BookAsSaleOnSetup>

Pricing

In both Merchandising and Pricing the data type for retail prices is NUMBER(20,4), but in Xstore, the standard is to use a data type of NUMBER(17,6). This applies to the following item prices:

Selling Unit Retail (from Merchandising and Pricing)
Manufacturer's Recommended Retail (from Merchandising)

If a Pricing retail value is over 17 digits, dataloading into Xstore will fail. Non-failing records from the same file will continue to be loaded.

Multi-Unit Pricing

Pricing and Xstore have different approaches to multi-unit pricing. Xstore converts multi-unit prices to single price, but cannot accurately do this without rounding information which varies from store to store. As such, Pricing's regular price update information for multiple units is not supported in this integration.

Promotions

There are certain features of promotions that are supported in Pricing that are not supported in Xstore. These features should be configured off in Pricing to prevent users from creating promotions that cannot be executed. These are the features that should be configured off:

Allow Reward List Exclusions on Transaction Offers

This Pricing system option should be unchecked, as Xstore cannot support receiving reward list exclusions for transaction offers.

Allow Supplier Site and Brand for Offer Item Selection

This Pricing system option should be unchecked, as Xstore does not have information about the brand or supplier for an item; therefore, adding or excluding items by this type of criteria on a promotion cannot be supported in the integration.

Pricing Application - Promotion Offer Distribution Rules (OFDR)

Pricing allows users to define how the discount should be distributed to items on a customer's purchase by selecting a distribution rule as part of the promotional offer creation. However, Xstore always applies the discount to the "get" items on an offer. Therefore, this code should be configured to only allow the Get Items option to be selected by users. See also the Oracle Retail Pricing Implementation Guide for more details.

Sales Audit

This section describes configurations that should be made in Sales Audit, as well as some limitations that exist.

First, in addition to configuring the store setup as described above, there are some Sales Audit system options that must be configured in a particular way when integrating with Xstore:

Transaction Appended with Workstation ID – this should be set to unchecked (No); when checking for duplicate transactions from Xstore, Sales audit will look at the register field in the transaction, along with the transaction number.
Credit Card Masking Character – this should be set to * when integrating with Xstore, as that is how Xstore masks credit card numbers.
Balancing Level - when integrated with Xstore, Sales Audit should be configured with a balancing level of Register and Xstore will always sends the workstation ID as the register.

Store Data Configuration

Sales Audit requires configuration to be set up for each store where you expect to import data. For all Xstore stores, you'll need to include two lines for each store configuration – one for Sales Import and one for Item Level Tax. Sales Import is the normal store sales import configuration, and the Item Level Tax configuration is used in validation of the import file to look for tax at the item level instead of the transaction level. This is how tax data will always be sent from Xstore, regardless of tax type.

Register-level Balancing

Xstore workstation and Sales Audit register are equivalent concepts; however Sales Audit does not have an entity equivalent to the Xstore till, which means that Xstore cannot be configured for till-level balancing when integrated with Sales Audit.

Sales Person

In Xstore, the sales person field length can be up to 60 characters in length, but Sales Audit only allows up to 10 characters. Retailers should, as a business process, not use Xstore sales person IDs with more than 10 characters.

Additionally, Xstore allows multiple sales associates at the line item level, however Sales Audit only supports one. Therefore only the transaction level sales associate is exported to Sales Audit.

Tender Types

Xstore supports a tender type of Home Office Check, which is not supported by Sales Audit. Retailers using this integration should not use the Xstore Home Office Check tender type. See Appendix: Xstore to Sales Audit Mapping Details, for more information on tender type mapping between solutions.

Coupons

Bounce back coupon number length in Xstore can be 60 characters long, but Sales Audit only allows 40 characters. If retailers want to use the integration, they should as a business process, not use IDs with more than 40 characters.

For more Sales Audit related considerations and configurations, see Appendix: Xstore to Sales Audit Mapping Details in this document.

File Format

The RTLOG file that is sent from Xstore to Sales Audit uses a version that does not include the following components in the file format:

Transaction Header level:
- Reference number 28-31
Transaction Item level:
- Fulfillment Location Type
- Fulfillment Location

For more information on how these fields are used in Sales Audit, see the Oracle Retail Merchandising Operations Guide – Volume 1.

Employee IDs

A new employee can be created using the Employee Maintenance function in Xstore. By default, the employee ID is generated automatically based on the employee.seq, for example, 0219001000009. The first four digits are the store ID, the next three digits are the register ID, and the last six digits are the sequence ID. If this new employee is selected as a sales associate, an exception will be thrown in the RTLogGenerator, since the length of a sales person ID, defined in the RTLogFormatConfig file is ten, whereas the length of the auto generated employee ID is 13. In Sales Audit, an associate ID cannot be over ten characters long.