Ask Deft Flux: How do I Query a Hierarchical Bill of Material in Epicor? |

Ask Deft Flux: How do I Query a Hierarchical Bill of Material in Epicor?

October 16, 2015 / Adam / Comments Off on Ask Deft Flux: How do I Query a Hierarchical Bill of Material in Epicor?

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