We’ve discovered that out of thousands of items, a few dozen in our GP data have a primary vendor identified in the IV00102 table but not in the IV00103 table for the same item and inventory site.

We report some purchasing thresholds from the IV00103 table. This problem affects those reports.

A worried person

Oh no!

Our fix is to assign the primary vendor to a different vendor on the Item Quantities/Site card, save it, then assign it back to the correct vendor and save it again. This corrects the IV00103 table.

A happy chef

Everything worked out.

Here’s a query that we used to find which parts had this problem:

Sherlock Holmes

The game is afoot, Watson!

select 
  locn.itemnmbr,
  current_timestamp,
  'Site ' + RTRIM(locn.locncode) + 
    ' primary vendor mismatch between IV00102 and IV00103'
from (select * from iv00102 where locncode='MAIN' and primvndr<>'') locn
left join (select * from iv00103 where itmvndty=1) vend
on locn.itemnmbr=vend.itemnmbr
and locn.primvndr=vend.vendorid
where vend.vendorid is null
order by locn.itemnmbr
;

Hope this helps someone!

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available