We successfully implemented automated part number cleanup using PX scripts in our 9.3.4 Part Management module, dramatically improving data quality. Our legacy migration had created 3,200+ duplicate part records with slight variations in part numbers - trailing spaces, inconsistent delimiters, mixed case. This caused BOM errors, procurement issues, and inventory discrepancies.
We developed a PX script that runs weekly to detect duplicate part patterns, consolidate references, and update BOMs to point to the canonical part record. The script uses fuzzy matching algorithms to identify duplicates, validates that parts are functionally identical before merging, and automatically updates all BOM references to maintain data integrity. After three months, we’ve eliminated 85% of duplicate parts and reduced BOM errors by 60%. Our procurement team reports significantly fewer supplier confusion issues.
The fuzzy matching uses Levenshtein distance to identify part numbers within 2 characters of each other, then applies business rules to validate functional equivalence. We check that parts have identical: item type, commodity code, unit of measure, and key attributes like material and finish. If any critical attribute differs, they’re flagged for manual review rather than auto-merged. We also exclude parts with active ECOs or open orders from automated cleanup. The script generates a preview report for engineers to review before executing the actual merge.
Great question about audit trail preservation. Our script creates a comprehensive change log before making any updates:
ChangeLog log = new ChangeLog();
log.setOriginalPart(duplicatePart);
log.setCanonicalPart(masterPart);
log.setAffectedBOMs(bomList);
log.setConsolidationDate(new Date());
We maintain a PART_CONSOLIDATION_HISTORY table that records every merge operation with references to original parts, target parts, affected BOMs, and the business justification. BOM change history is preserved by creating consolidation change orders that document the reference updates. This gives us full traceability for audits while cleaning up the duplicate mess.
From a procurement perspective, this type of cleanup is incredibly valuable. We were constantly dealing with suppliers confused about which part number variant to quote. One assembly might reference ‘BRKT-001’, another 'BRKT-001 ', and a third ‘brkt-001’ - all the same part but our systems treated them as different. This created quote discrepancies, ordering errors, and inventory fragmentation. After your cleanup initiative, our supplier error rate dropped significantly and we’ve been able to consolidate inventory better.