Catalog Numbers - Dashes &/or Leading Zeros

Sort:
You are not authorized to post a reply.
Author
Messages
brupp
Veteran Member
Posts: 165
Veteran Member
    Would like some advice on dealing with catalog numbers & avoiding duplication.  For example, we had 0277-010-212 set up in Lawson.  Requester orders same item but uses 277010212.  Buyer does not recognize duplication & vendor will accept either number as the same item.  I gave 277010212 its own Lawson number & months later caught the duplication on some reporting.

    Any thoughts or suggestions would be greatly appreciated.  We are not utilizing GTIN at this time, but if GTIN resolved this scenario for you I'd like to know that. 

    Thanks!  Beth
    Kat V
    Veteran Member
    Posts: 1020
    Veteran Member
      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.)
      JonA
      Veteran Member
      Posts: 1162
      Veteran Member
        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.
        Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
        sea2sky
        Basic Member
        Posts: 15
        Basic Member
          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.
          ststewart2
          New Member
          Posts: 1
          New Member
            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.
            brupp
            Veteran Member
            Posts: 165
            Veteran Member
              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
              Kat V
              Veteran Member
              Posts: 1020
              Veteran Member
                Me-tooing request for sql.
                sea2sky
                Basic Member
                Posts: 15
                Basic Member
                  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
                  brupp
                  Veteran Member
                  Posts: 165
                  Veteran Member
                    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!
                    Kat V
                    Veteran Member
                    Posts: 1020
                    Veteran Member
                      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!
                      brupp
                      Veteran Member
                      Posts: 165
                      Veteran Member
                        @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!!
                        Stephanie
                        Veteran Member
                        Posts: 86
                        Veteran Member
                          @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!
                          Stephanie Kowal | skowal@rpic.com
                          Kat V
                          Veteran Member
                          Posts: 1020
                          Veteran Member
                            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.
                            You are not authorized to post a reply.