Catalog Numbers - Dashes &/or Leading Zeros

As far as I know, there is no Lawson solution for this. We've got GHX's NuVia and I've been using that to find them.

(You aren't talking about Stryker by any chance? I'm finding that we've managed to load their catalog twice. Regional orders with dash, Regional South orders without.)

There is no easy solution for preventing duplicates but one thing buyers can do is do wildcard searches. I think most will enter the mfg number as they receive it on the request and if it doesn't come up it's good to go in their mind. But if you enter *277* in the search as in your case above you might have to wade through the results but there's a greater chance of catching these. Implementing GS1 standards (GTINs) will hopefully prevent this in the future.

This looks like a Zimmer part number. They use both formats, the one without dashes being used for EDI. Excel removed the leading zero(s) and turned the string into an integer. That is common.
First, your item master specialist is probably doing a blind ADD without appropriate research. I created an item master report that is used for this and other reasons. It makes it easy to determine whether an item is a duplicate, and to see things like whether or not there is an existing CDM number that should be applied.

Second, you might have tools at your disposal to identify these problems for you. Smart Notifications could do this, or, if you have access to the database (SSRS, Crystal, or straight up SQL) you could write an automated query that would identify these for you. Weekly would probably be sufficient. It is relatively easy to write the SQL to find duplicate VEN_ITEM or MANUF_NBR records and then email the results to one or more stakeholders. I prefer the pure SQL approach as it has the least overhead, but you might not have the same DB access that I have.

If you get it working then you can apply the same principle to other data cleansing issues and save your people a lot of time. GTIN would solve these and other issues but I don't think that we are close to its maturity.

When using Add-ins for a new product add, I always make sure that the numbers are text qualified, so the zero doesn't get dropped.  For products like Stryker, Boston Scientific, etc. where there may be two reorder numbers, I use the "box" number in IC11 and the other reorder number as the Vendor Catalog #.  Before anything new is added to Lawson, both IC12 and PO72 are searched.  This also makes both numbers searchable to the end-users.  You can also make user fields searchable depending on your company setup, which would give the ability to add an alias search field.

Thanks everyone for the input!
@Kat- yes, this is a Stryker item, who along with Zimmer gives me the most heartburn with this issue.
@sea2sky- We do not have Smart Notifications but we have Crystal. I'm afraid my report writing skills are at a novice level at best. I use MS Access & you've given me some ideas so thanks for your input, I appreciate it. I agree that GTIN is the answer but from what I hear that is a long way off.
If you're willling to share, would love to know how in Crystal (or SQL) to make my example result as a duplicate. I dont know SQL but have resources who do.
Thanks again, Beth

Me-tooing request for sql.

Hi brupp, Kat,

You have 2 tasks. First, you need to identify the existing duplicate records and inactivate them or delete them if they have no usage. Then you need to have a process to insure that the problem does not re-occur.

The following code will identify duplicate records for vendor 100185 (Zimmer, in our case) in a TSQL database. Our item master is clean so I had to allow for inactive items to get any results. You would uncomment the line "--AND ITE.ACTIVE_STATUS = 'A'" when you ran this.) The syntax is a little different in Oracle but not much. You could make it into a Crystal report if you are concerned about re-occurrence but you might only need to run this once for each implant vendor.

To manage the issue going forward I would suggest creating a report that could be termed an item master bible: one that combines all the information you need (IC11, IC12, PO13, PO25, PC10, etc) that dumps to a spreadsheet every morning or once per week. You can search it for existing items when considering an add to IC11. And of course, when a vendor gives you a new list of items or agreement items to look you could be on the lookout for these kinds of formatting issues.

USE TRAIN
GO

; WITH DuplicateVenItems AS
(
SELECT
POC.PROCURE_GROUP
, PIV.VENDOR
, SUBSTRING(REPLACE(REPLACE(PIV.VEN_ITEM, '-',''), '.', ''), PATINDEX('%[^0 ]%', REPLACE(REPLACE(PIV.VEN_ITEM, '-',''), '.', '')), 32) AS VEN_ITEM_NBR
FROM dbo.POITEMVEN PIV
JOIN dbo.POCOMPANY POC ON POC.PROCURE_GROUP = PIV.PROCURE_GROUP
JOIN dbo.ICCOMPANY ICC ON ICC.COMPANY = POC.COMPANY
JOIN dbo.APCOMPANY CPY ON CPY.COMPANY = POC.COMPANY
JOIN dbo.APVENMAST VEN ON VEN.VENDOR_GROUP = CPY.VENDOR_GROUP
AND VEN.VENDOR = PIV.VENDOR
JOIN dbo.ITEMMAST ITE ON ITE.ITEM_GROUP = ICC.ITEM_GROUP
AND ITE.ITEM = PIV.ITEM
WHERE
POC.COMPANY = 1

/* Put your vendor number here: no padded spaces. */
AND LTRIM(PIV.VENDOR) = '100185'

/* Uncomment this if you want to limit your search to active IC11 items. */
--AND ITE.ACTIVE_STATUS = 'A'

/* Use this for testing when tweaking this query.
AND PIV.VEN_ITEM LIKE '%[.-]%'
AND PIV.VEN_ITEM LIKE '0%'
*/
GROUP BY POC.PROCURE_GROUP
, PIV.VENDOR
, SUBSTRING(REPLACE(REPLACE(PIV.VEN_ITEM, '-',''), '.', ''), PATINDEX('%[^0 ]%', REPLACE(REPLACE(PIV.VEN_ITEM, '-',''), '.', '')), 32)
HAVING COUNT(DISTINCT PIV.ITEM) > 1
)
SELECT ITE.ACTIVE_STATUS
, PIV.PROCURE_GROUP, PIV.ITEM, PIV.VENDOR
, PIV.VEN_ITEM, PIV.VEN_ITEM_DESC
, PIV.MANUF_CODE, PIV.MANUF_NBR
FROM DuplicateVenItems
JOIN dbo.POITEMVEN PIV ON PIV.PROCURE_GROUP = DuplicateVenItems.PROCURE_GROUP AND DuplicateVenItems.VEN_ITEM_NBR = SUBSTRING(REPLACE(REPLACE(PIV.VEN_ITEM, '-',''), '.', ''), PATINDEX('%[^0 ]%', REPLACE(REPLACE(PIV.VEN_ITEM, '-',''), '.', '')), 32)
JOIN dbo.POCOMPANY POC ON POC.PROCURE_GROUP = PIV.PROCURE_GROUP
JOIN dbo.ICCOMPANY ICC ON ICC.COMPANY = POC.COMPANY

/* Use these JOINS if you want to identify various vendors. */
--JOIN dbo.APCOMPANY CPY ON CPY.COMPANY = POC.COMPANY
--JOIN dbo.APVENMAST VEN ON VEN.VENDOR_GROUP = CPY.VENDOR_GROUP
-- AND VEN.VENDOR = PIV.VENDOR
JOIN dbo.ITEMMAST ITE ON ITE.ITEM_GROUP = ICC.ITEM_GROUP
AND ITE.ITEM = PIV.ITEM
ORDER BY DuplicateVenItems.VEN_ITEM_NBR, ITE.ACTIVE_STATUS

Hi sea2sky,
Thanks so much! I follow most of the code & with some help I think we can get it up & running. I can think of a few vendors to run this for & might use it quarterly or something like that.

We have a "bible" report but we currently have > 170K active items so running it daily isn't reasonable but I try to create an Access file at least once per month & run duplicate queries.

My biggest issue is rushing through the "piecemeal" Lawson maintenance such as adds & not taking the time to do proper IC12/PO72 searches. I wear many hats (as we all do) & sometimes need to remind myself to slow down ) I did read where with Lawson/Infor 10 there will be a new feature where if you try to add a duplicate item to IC11 you'll get a warning that a duplicate mfg number already exists. Should be very helpful.

Thanks again!

Thank you! We're on Oracle so I'll have to tweak the verbiage a little - but thanks so much for most of the legwork!

@Kat - Any chance you'd be willing to share your query? We're on Oracle as well & my IS&T did not have any luck with what I provided to them. I do not want the query to update anything; only to identify. Ultimately, would love to have this in a Crystal Report or in MS Access. Thanks in advance!!

@Kat - I'd also like to see what you have if you are willing to share! Thanks eveyrone of this great detail, we are always working through data cleansing but it is difficult at times to find the best tool for our Inventory Control department.

Thanks!

As there are several variations of SQL - The one I'm on doesn't seem to recognize regexp_replace function --- if yours does, you apparently can tell it to remove all punctuation -- as it is, I have an ever growing statement.



select PROCURE_GROUP, ITEM,VENDOR,VEN_ITEM, replace(replace(replace(replace(replace(replace(PIV.VEN_ITEM, '-','1'),'.','1'),' '),'/'),'*'),'&') as "STRIPPED-PART"
from lawson.POITEMVEN piv

Will take out all - . [spaces] / * & Basically as we find a special character you have to add a replace( in front and a ,'CHARACTER') at the end. For example just replace(PIV.VEN_ITEM, '-','1') only takes out the dashes. The rest was just piled on as we go.

At that point I find it easier to dump into excel and use conditional formatting to highlight duplicates and give to the buyers to work.

ETA - I didn't include the where statements but we filter for vendors or just the default flag = '1' as needed.  I've got the full list with only the default flag filter on it on our report center so the buyers can search for the stripped part number to see if something has a Lawson item.