Any easy way to wite a report that would tell us All part numbers that DO NOT show up on a BOM?

Epicor ERP 9
1

We need a way to find out which part numbers in our system that have a quantity on hand but do NOT show up on any BOM. Is this possible? I could create my own report if I knew where to look. Thanks…

2

You need to look at part mtl table for active revisions.

3

Part, PartRev, PartOpr, PartMtl and PartWhse.

BOM parts will be listed in the PartMtl table.

QOH will be in the PartWhse table.

1 Like
4

as @bmgarver said.

Start by doing a join on PartWhse and PartMtl .

Some thing like this.

select * from erp.PartWhse as pw
left join Erp.PartMtl as pm on pm.MtlPartNum = pw.PartNum and pw.Company = pm.Company
where pw.OnHandQty > 0
and pm.PartNum is null

Then expand the query based on other requirements.

1 Like
5

Brian, thanks….That’ll work…

6

Dont know why you need Partwhse.

Edit:

Nevermind. Missed the 'with QOH" requirement.

Edit #2
Use PartBin, not PartWhse for QOH

7

PartWhse stores the sum of all PartBin quantities. Can use PartBin also, just need to sum the quantities.

2 Likes
8

I learn something new every day!

1 Like
9

That’s why I hang around E10Help too :wink:

10

Question - Do PartWhse records disappear like PartBin records do when the QOH goes to zero?

11

You can find the following in BAQ Designer by selecting the table from the list:

PartWhse : The purpose of this file is to indicate in which warehouses a part may exist. A part can exist in one or more warehouses. This file also holds allocation totals for sales order and job requirements for specific part at the warehouse level. This file is a subset to the Part file. At least one PartWhse record will exist for each record in the Part file.
DELETE: Not allowed referenced in PartBin, PartTrans, or as a Part.PrimWhse

1 Like
12

If you have non-nettable locations, I think they are included in the sum of qoh if you query the warehouse table - if you only want specific bins you need to use partbin and filter out the ones you don’t want.

Really depends on what you want and/or how your system is configured.