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.
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.
Tables used:
Part, EcoMtl, EcoOpDtl
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.
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.
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:
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 )