Re: Duplicate Items in Item Master

Excellent feedback.
 
Thanks to all!

From: "rob@xxxxxxxxx" <rob@xxxxxxxxx>
To: BPCS ERP System <bpcs-l@xxxxxxxxxxxx>
Sent: Monday, September 26, 2011 10:09 AM
Subject: Re: [BPCS-L] Duplicate Items in Item Master

Way back in dinosaur times there was a problem putting keys on physical
files (S/32 perhaps?).  BPCS still follows that philosophy.  They do not
even specify UNIQUE on their logical files.  They assume that no one ever
migrates data to their ERP system or anything else (like custom upgrades
from earlier versions of BPCS) that comes outside of standard item
maintenance.  So, yes it is possible for duplicates to occur - we've had
them.  BPCS uses no constraints (unique, key, referential, etc).  All is
done within Item master maintenance.

While I disagree with their philosophy, if you follow their rules then
duplicates shouldn't be occurring.  You don't have users downloading data,
massaging it, and then uploading it, do you?  That could be another
possible avenue.

The following sql should help you find duplicates:
select iprod, count(*)
from iim
group by iprod
having count(*)>1

You could expand this to include the relative record number to detail it
out with:
with summary as(
select iprod, count(*)
from iim
group by iprod
having count(*)>1)
select iprod, rrn(iim)
from iim
where iprod in(
  select iprod
  from summary)

Happy hunting.  If you journal these files you can find might be able to
find out who did it.

Rob Berendt