When engaging Rubi Works to manage updates between vendor Pricelists and your Q360 Masterno database, there are varying degree to which we can help. We recommend a comprehensive approach which includes Item Cleanup. Rubi Works has designed a standardized process to help ensure a clean Masterno database in Q360.

What Constitutes a Clean Masterno database?

There are several aspects to ensuring a clean Masterno database:

• Deduplication (across like parts)
• Collision Prevention (across unlike parts)
• Manufacturer normalization
• Item EOL (end-of-life) Management
• Correct Item Typing

Deduplication

One of the most common scenarios we find is that customers have multiple distinct Masternos representing the same part.

As anyone who has used Q360 or any inventory system at scale can tell you, there is no hope of relying on the system for accurate inventory when root problems like this are present.

Detection and resolution of duplicates is something we've put considerable effort into developing. Our approach can be summarized as follows: For each item on a newly received price list, we pull in a set of possible matches within the system. To build this dataset of matches, we are leveraging several techniques in tandem:

• Matching of MasterNos across common variations of use of symbols (dashes, periods, spaces, etc.)
• Matching of MasterNos across known variations of Manufacturer based on Rubi Works centralized dictionary of common variations. (e.g. QSC vs QSC Audio)
• Matching of MasterNos using Vendor Part No / Alt Part No
• Matching of MasterNos using UPC as a common identifier (when available)

Following detection of duplicates, we have to make a decision on what the true MasterNo will be, as well as which specific cleanup action/s to take. The decision of which MasterNo to use as the true MasterNo will be made following the unique identfying part number provided on a definitive pricelist. For the purpose of masterno clean up, it is always best to use a Manufacturer-provided list; for example, a parts list from QSC would be more definitive for this purpose than a list from vendor like ADI who happens to sell QSC parts. Rubi Works maintains a dictionary to help us automatically identify which entities are considered definitive for different manufacturers.

The specific cleanup actions include merging items, renaming items, and changing item types. For details, see the section below Cleanup Actions in Detail

Collision Prevention

Most of the Q360 customers we've worked with use an item's Partno as its Masterno. This presents a unique challenge from time to time when working with parts from different manufacturers that happen to share the same Partno.

Without detection and remediation, a masterno collision can lead to problems down the road. Suppose the Bose speakers are uploaded from their pricelist into Q360 as-is. The existing Q360 masterno for the Da-Lite screen would be overwritten with new manufacturer, price, cost, and other details. If there was already stock of those Da-Lite screens, it would thereafter be shown as stock of the Bose speakers.

To combat this, there are a couple of possible solutions:

1. The simplest solution tends to be giving one or both of the parts a prefix or suffix. This prevents a collision scenario. However, this comes at the potential for some confusion among team members and customers when looking at a prefixed/suffixed masterno. Additionally, this solution calls for a consistent prefixing/suffixing strategy to be maintained.
2. A more ideal solution is to transition to a strategy of using uniquely generated masternos for ALL parts within Q360. The challenge with this strategy is that typically many different grids, reports, and printed document templates must be adjusted to use a different field in place of Masterno for cases where an item's part number is expected to be shown.

When resolving masterno conflicts, our default solution is to apply a prefix of the first 3 letters of a part's Manufacturer name to both parts that are in conflict.

Manufacturer Normalization

Across a collection of parts from the same manufacturer, it is common to see some variations in the Manufacturer name:

A scenario like this makes it difficult to run Manufacturer-oriented reports. It also looks sloppy and confusing on quotes, call summaries, and other customer-facing documents.

A variation of this problem occurs when a Vendor is entered in as the Manufacturer. (For example, ADI may have provided a pricelist with QSC parts on it; someone uploaded that pricelist into Q360, incorrectly mapping "ADI" to the Manufacturer field). In such a scenario, we would ensure the vendor is instead mapped to the PRICELIST field (or perhaps we might even use it to drive creation of a Master-Vendor record, depending on your preference).

To ensure Manufacturer consistency, Rubi Works maintains a database of what we consider to be the definitive Manufacturer name across an array of possible alternative names that may need to be adjusted. Additionally, we maintain a list of customer preferences by manufacturer; in doing so, we can ensure that your preference of using "QSC Audio" is maintained whenever we process a QSC pricelist, even when we would normally consider "QSC" to be the definitive Manufacturer name.

Item EOL (End-of-Life) Management

The conventional approach to managing MasterNo databases is to mark items as INACTIVE when they no longer appear on a vendor-provided pricelist. This is a great way to make sure quotes are being built to fit what's currently available on the market, however there are some things to keep in mind:

1. We must be sure the provided pricelist is definitive for a manufacturer's collection. For example, while ADI's pricelist may provide many QSC parts and be a great indicator for current price/cost data, the list direct from QSC would be more definitive for understanding the extent of the manufacturer's collection and thereby making decisions regarding an item's active/inactive status.
2. Just because an item is EOL does not necessarily mean it should be marked as Inactive within Q360. Consider a HDMI cable for which a large quantity of stock exists in your warehouse; although that item is slated to be discontinued, significant inventory still exists and therefore the item needs to maintain an active status so it can continue being sold on quotes for a while longer. One approach some Q360 customers may pursue is to use an "EOL" field on the Master form, whether through a custom field or a designated User field. In this strategy, customers could choose to run periodic reports on EOL items to make informed decisions on when to mark items as fully Inactive.

Correct Item Typing

One of the root challenges of maintaining an item database is understanding what Type an item should be classified as. In the world of Q360, this is primarily a decision whether items should be classified as 'A' or 'Q' types, and to a lesser degree, 'M' types. In an ideal world, all vendors would provide an indicator on their pricelists for whether or not each of their items are serialized. Unfortunately, this is not a widespread practice; also, you may wish to serialize some high-value items even when the manufacturer does not provide a serial number originally.

To help customers manage the typing of their items, Rubi Works created a workflow bucket concept:

This interactive workflow bucket can be integrated into any existing workflow or deployed as a standalone report in its own workflow. The idea here is that as items are added in bulk from Pricelists, a process exists to help ensure those items are landing with the correct Item Type. Typically customers will designate a default cost-based rule for the initial import (e.g. All items with a cost equal to or over $500 should land in Q360 as A types, all items with a cost below $500 should land in Q360 as Q types). This initial rule ensures that items can immediately be used by the Sales/Engineering team to get quotes out the door. However, before any Quotes or POs are fully processed, a check occurs to ensure those items' types have been marked as verified.

Keep in mind - these strategies surrounding Item Typing can be tailored to meet your team's needs.

When/How Does Cleanup Happen?

This cleanup process is applied on a per-Pricelist basis each time we process a pricelist for import into Q360. That is to say, we wouldn't be able to clean up an entire database all at once unless we had up-to-date pricelists for all items in the entire database.

To effectively prioritize cleanup efforts, we recommend starting with a collection of pricelists corresponding to the most commonly used manufacturers.

The need for ongoing cleanup will typically be relative to how many different hands have previously been on the item database. One way to ensure items databases are kept clean is to ensure that only a very limited number of users are able to add Masternos; those users with that privilege would need to exercise it carefully, ensuring that any masternos that get added are consistent with the formatting of those items as they would appear on the vendor's pricelist.

Cleanup Actions in Detail

Merge Master

In Brief:

Merges one MasterNo record into another.

When is this used:

Merge Master is used to resolve incidents of existing duplicate part records.

In Detail:

• Updates MasterNo references globally and retroactively from the old MasterNo to the new MasterNo on the following objects:
• Timebills
• Order Items
• Invoice Items
• Masterno Labor Rate entries
• Products
• Product Detail entries
• Master Kit Detail entries
• Master Profile entries
• PO Items
• Quote Items
• Voucher Items
• Service Contract Items
• Documents with MasterNo references
• Images with MasterNo references
• Vendor Inventory entries
• Service Fee entriesNOTE: Other fields such as the Vendor PartNo and AltPartno are not updated on existing records when performing a Merge. Only the MasterNo field.
• Transfers inventory quantities from the old MasterNo to the new MasterNo:
• Supports translation of inventory for both A and Q types.
• Ensures accuracy at the company, branch and overall master level
• Markes the old MasterNo as Inactive (for A/Q types) or deletes it entirely (for all other types).

Limitations:

• Old MasterNo and New MasterNo must be the same Item Type.

Change MasterNo

In Brief:

Renames an existing MasterNo to have a different MasterNo value.

When is this used:

Change MasterNo is used to:

• Ensure an existing MasterNo in Q360 conforms with the MasterNo value dictated by a vendor's price list, therefore a duplicate part entry is not created on upload of the price list.
• Ensure an existing MasterNo in Q360

What does it do?

• Updates MasterNo references globally and retroactively across virtually EVERY TABLE in Q360 with a MasterNo reference. (This even includes the History table)NOTE: Other fields such as the Vendor PartNo and AltPartno are not updated when performing a Change MasterNo action.

Change Item Type

In Brief:

Changes an existing MaterNo's Item Type. This is used as a pre-requisite step in a scenario where 2 or more pre-existing duplicate parts are in the system with different types; in such a scenario, the item-types need to be made the same before a Merge can be conducted. (for compatibility we favor conversion to A over Q over M)

Following these types of changes we run Q360's MasterByBranch_Calc procedure to ensure accuracy.