Manufactured parts without a BOO or BOM

Epicor ERP 9
1

Epicor version 9.05.701

I need to build a report that shows manufactured parts without a BOO or BOM

Which tables will give me this BOO or BOM data? I thought “partbom” and “partboo” but they are empty.

Many thanks in advance.

2

Tables used:
Part, EcoMtl, EcoOpDtl

3

PartMtl and PartOpr is for the current revisions.

First check would be be for Manufactured part that do not have a record in the PartRev table.

4

Mark,

The ECOOpr and ECOMtl tables are for part revisions that are currently checked out and available in the Engineering workbench. For this to happen, there has to be a PartRev record.

Once a revision has been checked back in (approved or not), it populates PartMtl and PartOpr (and depending on resource group settings, possibly PartOpDtl).

As @cathy said, check Manufactured, and look for PartRev records… see which ones are approved… see which of those have related PartOpr and/or PartMtl records.

5

Mark, remember that it’s possible to revision track a part without any method under the rev. I think what you want is Part -> PartRev -> PartMtl and:

  • Put a criterion on Part so only ‘M’ items are shown
  • Put a criterion on PartRev so only approved revisions are shown
  • Make both table connections left joins, so you’re seeing parts regardless of whether they have a rev or not, and seeing part revs regardless of whether they have BOMs or not
  • Interpret the trouble cases as those with null fields in PartMtl.
6

Make a sub-query (SubQuery1) of Part -> PartRev -> PartMtl , that has a calculated field CompCount = COUNT(PartMtl.MtlSeq).

Make the main query be Part_1 – SubQuery1 (full join), with a criteria of SubQuery1.Calc_CompCount = 0

Here’s the SQL that E10 generated:

select 
	[Part1].[PartNum] as [Part1_PartNum],
	[SubQuery1].[PartRev_RevisionNum] as [PartRev_RevisionNum]
from Erp.Part as Part1
inner join  (select 
	[Part].[Company] as [Part_Company],
	[Part].[PartNum] as [Part_PartNum],
	[Part].[TypeCode] as [Part_TypeCode],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	(count( PartMtl.MtlSeq )) as [Calculated_ComponentCount]
from Erp.Part as Part
left outer join Erp.PartRev as PartRev on 
	Part.Company = PartRev.Company
And
	Part.PartNum = PartRev.PartNum

left outer join Erp.PartMtl as PartMtl on 
	PartRev.Company = PartMtl.Company
And
	PartRev.PartNum = PartMtl.PartNum
And
	PartRev.RevisionNum = PartMtl.RevisionNum
And
	PartRev.AltMethod = PartMtl.AltMethod

 where (Part.TypeCode = 'M')
group by [Part].[Company],
	[Part].[PartNum],
	[Part].[TypeCode],
	[PartRev].[RevisionNum])  as SubQuery1 on 
	Part1.PartNum = SubQuery1.Part_PartNum
And
	Part1.Company = SubQuery1.Part_Company
 and ( SubQuery1.Calculated_ComponentCount = 0  )