Clean the MRO material master without merging unlike spares

Topics: MRO material master data cleansing; duplicate spare part codes; MRO item master cleanup; spare part attribute standardisation.

Two spare codes look identical in the material master. Both descriptions say “seal assembly, pump”. One has been purchased six times; the other has never moved. A deduplication tool assigns a high similarity score and proposes merging them.

The descriptions may be duplicates, or they may conceal different revisions, materials or approved uses. MRO material master deduplication should separate candidate detection from an authorised equivalence decision. The tool can help find pairs worth reviewing. It cannot make two spares interchangeable.

Choose a family small enough to understand

Start with one item family and the engineering, materials, master-data and finance owners responsible for it. Use an authorised snapshot of item codes, manufacturer, part number, revision, units, specification references, approved asset relationships, stock balances and open transactions. Remove unnecessary supplier or personal information.

Define required attributes with the engineering owner. Their importance varies by equipment and application; a universal list inferred from descriptions is unreliable. Identify fields that are unknown, fields that are genuinely not applicable and fields that conflict.

An approved procurement specification can help establish the attribute basis. Where JIP33 is adopted, its document framework is one source of structured requirements, not evidence that two existing codes are equivalent. JIP33 FAQ

Similarity is the beginning of the investigation

Illustrative example. Four codes appear in a candidate review. The part references and technical distinctions are fictional.

M-101 and M-202 may be duplicates, but the reviewer still needs the supporting manufacturer and approved-use evidence. Normalising punctuation is useful for search; it should not destroy the original value. M-303 cannot be merged solely because its description and base part number match. M-404 belongs in an information-resolution queue rather than receiving guessed attributes from its closest neighbour.

Do not assume that a hyphen, space or leading zero is insignificant in every manufacturer's numbering scheme. Keep the original identifier alongside any search-normalised value, and use a manufacturer-specific rule only after its meaning has been checked. The proposed MX-700/MX700 match remains a candidate until that evidence exists.

Suppose engineering confirms M-101 and M-202 represent the same approved definition. The master-data steward proposes M-101 as the surviving code, with M-202 retained as a historical alias or blocked-for-new-use record according to the ERP's supported method. That decision still needs a migration-impact review.

Follow the mapping into transactions

In the example, M-101 has five units on hand and M-202 has three, with two more units on an open purchase order. The combined physical on-hand quantity would be eight if the approved mapping and unit basis are valid. The open order remains a separate future supply record; it does not make on-hand stock ten.

Finance must review valuation treatment. Combining quantities does not justify averaging costs or rewriting historical postings. Purchasing must decide how the open order will be handled, including whether supplier references and documents need updating. Maintenance must check asset bills of material and work-order reservations.

For serialised repairables, preserve each physical unit's identity through the item-code mapping. Item and individual asset identifiers serve different purposes, as IBM's data-model example illustrates. IBM asset identity

The mapping register should include source code, target code, decision type, evidence, approvers, effective scope and treatment of open references. A supersession may be valid only prospectively or for specified uses. It is not necessarily the same as a duplicate merge.

Record the direction of an approved supersession. Permission to use a newer item in a defined application does not establish permission to use the older one in every application of the new item. Keep that relationship separate from an equivalence group, with its scope and supporting decision.

Compare matching methods honestly

Baseline the current manual review before introducing a matching tool. Record how many candidate pairs the steward examines, the time spent locating evidence, the decisions reached and the pairs left unresolved over a stated period. Separate active review from waiting for engineering input. A tool that produces many more weak candidates can increase the workload even while finding additional duplicates.

Exact identifiers with controlled normalisation can find obvious candidates. Attribute rules can expose conflicting revisions or units. Fuzzy matching can find spelling variants. AI may help parse descriptions or propose candidate groups, but it should retain source text and uncertainty.

Review the whole proposed group before selecting a survivor. A similarity match between A and B, and another between B and C, does not prove A and C are equivalent. A clustering tool can otherwise draw unlike items into one group through a chain of weak matches. Show the attributes and conflicts for every member, and let unresolved members remain separate.

Compare those methods on a labelled set reviewed by qualified owners. Keep training or tuning pairs separate from held-out evaluation pairs, and avoid placing nearly identical members of the same item family on both sides. Include difficult non-duplicates, not just obvious positive matches.

Measure false matches among proposed duplicate candidates, valid duplicate pairs missed, review effort and unresolved pairs. For approved mappings, separately measure missing transaction relationships and orphaned references. Zero false merges observed in a small test is a test result, not proof that the method is infallible.

Agree acceptance criteria before the final evaluation. In this pilot, all equivalence and merge decisions receive human approval. The output is a reviewed mapping and sandbox migration evidence, not autonomous changes to the production master.

Rehearse the migration and reversal

Apply approved mappings in a sandbox using the product's supported method. Check stock by quantity and valuation basis, serial relationships, open purchase orders, reservations, asset links and history queries. A report of fewer material codes is not a sufficient acceptance measure.

Test a mapping withdrawn after review, a code referenced by an open transaction, an item with incompatible units and a user without approval authority. The system should preserve unresolved cases and refuse unsupported automatic changes.

Retain the original snapshot, mapping version, execution log and before/after reconciliation. Stop on an unapproved merge, unexplained valuation change, lost serial relationship or orphaned reference. Return the sandbox to the agreed checkpoint or use the supported reversal process, then investigate. Any later production migration needs a separately authorised cutover and recovery plan; the draft pilot does not perform one.

Summary and next actions

A cleaner material master is one in which people can distinguish genuine duplicates from similar but different spares. Candidate matching helps the review, while approved mappings and transaction reconciliation protect the meaning of the records.

1. Select one item family and agree its required identity attributes with engineering.
2. Label duplicate, distinct and unresolved pairs before comparing matching methods.
3. Trace approved mappings through stock, serials, open orders and asset references.
4. Demonstrate sandbox reconciliation and recovery before proposing any production cleanup.

A focused assessment needs the item family, current ERP/EAM, attribute completeness and review owners. Synthetic candidate pairs are enough to design the first review; a full confidential material master is unnecessary at that stage.