Mike raises a valid point about automation risk. Let me provide the complete implementation details addressing all the key focus areas:
PX Scripting for Cleanup:
Our PX script architecture has multiple safety layers. The core script runs in three phases:
Phase 1 - Detection and Analysis:
// Pseudocode - Duplicate detection logic:
1. Query all active parts from ITEM table
2. Normalize part numbers (trim spaces, convert to uppercase)
3. Calculate Levenshtein distance for all part pairs
4. Flag pairs with distance <= 2 as potential duplicates
5. Apply attribute comparison rules to validate functional equivalence
// Generates candidate list without making changes
Phase 2 - Validation and Approval:
The script generates a detailed report showing each potential duplicate pair with side-by-side attribute comparison, BOM usage analysis, and recommended merge direction (which part becomes canonical). This report goes to designated data stewards for review. They can approve, reject, or modify merge recommendations. Only approved merges proceed to Phase 3.
Phase 3 - Execution and Documentation:
// Pseudocode - Safe merge execution:
1. Begin database transaction
2. Create audit log entry with pre-merge state snapshot
3. Update BOM_ITEM references from duplicate to canonical part
4. Update manufacturer part references and sourcing records
5. Set duplicate part status to 'Obsolete' with reference to canonical
6. Commit transaction or rollback on any error
// All-or-nothing execution preserves data integrity
The weekly schedule runs Phase 1 automatically. Phases 2 and 3 only execute when stewards explicitly approve merges through a web interface we built. This gives us automation efficiency while maintaining human oversight for critical decisions.
Automated Duplicate Detection:
Our detection algorithm balances sensitivity and specificity carefully. The Levenshtein distance threshold of 2 catches most common duplicate patterns: trailing spaces, case differences, single character typos, missing/extra delimiters. But distance alone isn’t sufficient - we layer on multiple validation rules:
Attribute Validation: Parts must match on item type, commodity code, UOM, and 8 custom attributes we defined as ‘identity attributes’ for each item type. For example, fasteners must match thread size, length, and material. Electronic components must match manufacturer and MPN. This prevents false positives where part numbers are similar but parts are functionally different.
Usage Pattern Analysis: We analyze where duplicate parts are used in BOMs. If two suspected duplicates appear together in the same BOM, they’re likely distinct parts and get excluded from auto-merge. If they appear in mutually exclusive BOMs for different product families, they’re stronger merge candidates.
Lifecycle State Filtering: Parts in certain lifecycle states are excluded from automated cleanup - anything with active ECOs, open work orders, or pending procurement orders. We don’t want the script changing parts that are actively being engineered or manufactured.
The script logs detailed scoring for each potential duplicate showing why it was flagged and what validation rules it passed/failed. This transparency helps data stewards make informed approval decisions and helps us tune the detection algorithm over time.
Reference Updates in BOMs:
BOM reference updating is the most critical and risky part of the cleanup process. We implemented several safeguards:
Effectivity Preservation: When updating BOM item references, we maintain all effectivity date ranges exactly as they were. If a duplicate part was effective from 2020-01-01 to 2022-12-31, the canonical part reference inherits those same dates. We never modify effectivity as part of cleanup - that requires separate engineering change orders.
Change Order Documentation: Every BOM update performed by the cleanup script is documented through an automated change order. We create a special ‘Data Quality Cleanup’ change order type that references the PART_CONSOLIDATION_HISTORY record and lists all affected BOMs. This provides full audit traceability and allows rollback if needed.
Multi-level BOM Validation: Before merging parts, the script validates that the consolidation won’t create invalid BOM structures. We check for circular references, validate that consolidated BOMs don’t exceed configured depth limits, and ensure that BOM quantities remain valid after substitution.
The BOM update logic uses Agile’s standard API rather than direct database manipulation to ensure all business rules and triggers execute properly. This is slower but much safer than SQL updates.
Results and Lessons Learned:
After three months, our metrics show: 85% reduction in duplicate parts (from 3,200 to ~480 remaining), 60% reduction in BOM errors flagged during engineering review, 40% reduction in procurement quote discrepancies, 25% improvement in inventory accuracy as duplicate stock gets consolidated.
The remaining 15% of duplicates are legitimate edge cases: parts that look similar but have subtle functional differences, parts with complex change history where consolidation risk outweighs benefit, parts in active development where engineers need time to determine canonical version. We’re okay with these remaining as manual cleanup tasks.
Key lessons learned: (1) Automated detection works great but automated execution needs human oversight, (2) Comprehensive audit logging is essential for organizational trust in the process, (3) Phased rollout with extensive testing on non-production data prevented major issues, (4) Stakeholder communication was critical - we involved engineering, procurement, and quality teams in defining validation rules, (5) The script itself needs version control and change management just like production code.
For others implementing similar cleanup initiatives, I recommend starting with a small pilot - maybe one product family or item type. Perfect your detection and validation rules on that subset before expanding to full part database. Build extensive reporting so stakeholders can monitor what the script is doing. And invest in comprehensive audit logging - you’ll need it to answer questions and build confidence in the automated process.