Q: How can I Identify all Duplicate MPNs in my System?
A: There are many utilities to identify all internal parts that use a selected MPN. However, if you want a master list of all parts so that you can review and delete any that are not needed, below is a simple set of instructions you can follow:
1. exported the Parts List by Manufacturer & Mfgr Part Number report to excel (accessible from Inventory Control)
2. delete all non-BUY parts
3. move the MPN column before the part number column
4. sort by MPN, then by internal part number
5. delete uniq_key column data and replace with formula (use the attached spreadsheet to see our example of the formula used)
6. add conditional formatting to highlight each part number change
7. filter out blanks in column with formula
This creates an easy to read list of all MPNs used more than once under different internal part numbers.
Once the data is in SQL, they can use this view to show all buy MPNs that are used on more than one internal part number.
SELECT TOP (100) PERCENT LIST.MFGR_PT_NO, INV.PART_NO, INV.REVISION, INV.PART_CLASS, INV.PART_TYPE, INV.DESCRIPT
FROM (SELECT MFGR_PT_NO
FROM (SELECT TOP (100) PERCENT MPN1.UNIQ_KEY, MPN1.MFGR_PT_NO
FROM dbo.INVTMFHD AS MPN1 INNER JOIN dbo.INVENTOR AS INV1 ON MPN1.UNIQ_KEY = INV1.UNIQ_KEY
WHERE (INV1.PART_SOURC = 'BUY')
GROUP BY MPN1.MFGR_PT_NO, MPN1.UNIQ_KEY
HAVING (MPN1.MFGR_PT_NO <> '')) AS L1
GROUP BY MFGR_PT_NO
HAVING (COUNT(MFGR_PT_NO) > 1)) AS LIST INNER JOIN dbo.INVTMFHD AS MFG ON LIST.MFGR_PT_NO = MFG.MFGR_PT_NO INNER JOIN dbo.INVENTOR AS INV ON MFG.UNIQ_KEY = INV.UNIQ_KEY
GROUP BY LIST.MFGR_PT_NO, INV.PART_NO, INV.REVISION, INV.PART_CLASS, INV.PART_TYPE, INV.DESCRIPT
ORDER BY LIST.MFGR_PT_NO, INV.PART_NO
|