On occasion, an Epicor ERP user will want to write a report that requires a hierarchical view of a part’s bill of material. In Epicor, we would prefer to do this using a Business Activity Query (BAQ). The Epicor 10 BAQ tool is much more powerful than the tool from version 9, so it comes close to being able to do this, but just misses the mark. In order to query a hierarchical BOM, we must write the SQL and create a view in the database. Once we create the view, we can use a BAQ to access it, or we can query it directly using an SSRS report. Herewith, we provide the SQL:
with pm as (select x.Company, x.PartNum, x.RevisionNum, QtyPer, FixedQty, MtlPartNum as ChildPartNum from erp.PartMtl x inner join erp.PartRev pr on pr.Company = x.Company and pr.PartNum = x.PartNum and pr.RevisionNum = x.RevisionNum and pr.Approved = 1 and pr.EffectiveDate <= GetDate() and pr.EffectiveDate = (select max(effectiveDate) from erp.PartRev pr2 where pr2.Company = pr.Company and pr2.PartNum = pr.PartNum and pr2.Approved = 1 and pr2.EffectiveDate <= GetDate()) ), bm as (select 1 as BOMLevel, root.PartNum as TopLevelPartNum, root.*, cast(root.QtyPer as decimal(30,10)) as PartQty from pm as root where not exists (select null from Erp.PartMtl b where b.MtlPartNum = root.PartNum) union all select BOMLevel + 1 as BOMLevel, bm.TopLevelPartNum, node.*, cast(node.QtyPer * bm.PartQty as decimal(30,10)) as PartQty from pm as node inner join bm on bm.Company = node.Company and bm.ChildPartNum = node.PartNum)select * from bm
联系客服
