BAQ for BOM cost

Epicor ERP 10
1

im trying to build a BAQ so i can pull BoM Cost and compare to Selling price more easily.

i got the BAQ to pull the material but the Cost is pulling from the parent part. Where did i go wrong

select
[PartRev].[PartNum] as [PartRev_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[UnitPrice] as [Part_UnitPrice],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartCost].[AvgMaterialCost] as [PartCost_AvgMaterialCost]
from Erp.PartMtl as PartMtl
inner join Erp.PartRev as PartRev on
PartMtl.Company = PartRev.Company
and PartMtl.PartNum = PartRev.PartNum
and PartMtl.RevisionNum = PartRev.RevisionNum
and PartMtl.AltMethod = PartRev.AltMethod
and ( PartRev.PartNum like PartRev.PartNum )

inner join Erp.Part as Part on
PartRev.Company = Part.Company
and PartRev.PartNum = Part.PartNum
inner join Erp.PartCost as PartCost on
PartMtl.Company = PartCost.Company
and PartMtl.PartNum = PartCost.PartNum
where PartRev.PartNum like @BeginsWith

2

image

3

Try joining to the partcost using the MtlPartNum field.

The PartNum is the parent part
The MtlPartNum is the child part

2 Likes
4

perfect. thank you.

5

Just a heads up on your cost calcs…

If the BOM has any non-system parts (ones that are always Buy to Job), they would be excluded.

To fix this, make the join between PartMtl and PartCost be a left outer join. Then make a calculated field for the part cost, where if the PartCost.StdMtl… is null (no PartCost record exists), then use the PartMtl.EstCost (or whatever that fields name is).

Also, you query doesn’t drill down beyond 1 level.

1 Like
6

i just noticed that. i am not sure if i need that.