Looking for SQL SP or Code to list full BOM for a part

This has probably been done and asked a hundred ways, but most research on past threads are for BOM Report or export.
I am looking for just SQL Code to generate full Bill of Materials for all Assy and Sub materials in one list.

We are doing forecasting and need the complete list of materials for a given list of master part numbers.

Any advice or code appreciated.

Regards,
George Hicks
Visionaire Lighting

Give this a try. It will list each part from the top select and then the BOM for that part. If you have a classid for parts you can narrow down your query there.

WITH PartsExplosion (PartNum, MtlPartNum, lv, QtyPer, rowid, Name, SORT)
AS (
-- Anchor
  SELECT Erp.Part.PartNum, 
         Erp.Part.PartNum, 
         0,
         cast(Erp.Part.PurchasingFactor as int) as QtyPer,
         Erp.Part.SysRowID,
         CAST(Erp.Part.PartDescription as nvarchar(100)) as Name, 
         CAST('\' + Erp.Part.PartDescription as nvarchar(254)) as Sort
FROM Erp.Part
-- Recursive Call
UNION ALL
SELECT BOM.PartNum, BOM.MtlPartNum, lv + 1, cast(BOM.QtyPer as int) as QtyPer, BOM.SysRowID,
       CAST(REPLICATE ('|    ' , lv +1 ) + BOM.PartDescription as nvarchar(100)),
       CAST(cte.Sort + '\' +  BOM.PartDescription as nvarchar(254)) 
FROM PartsExplosion CTE
JOIN (SELECT Erp.PartMtl.PartNum , Erp.PartMtl.MtlPartNum, cast(Erp.PartMtl.QtyPer as int) as QtyPer, Erp.PartMtl.SysRowID, b.PartDescription
FROM Erp.PartMtl Join Erp.Part AS b on Erp.PartMtl.MtlPartNum = b.PartNum
inner join dbo.PartRev on Erp.PartMtl.RevisionNum = dbo.PartRev.RevisionNum and Erp.PartMtl.PartNum = dbo.PartRev.PartNum and Erp.PartMtl.Company = dbo.PartRev.Company 
WHERE dbo.PartRev.Approved = 1
) AS BOM
ON CTE.MtlPartNum = BOM.PartNum
)
select Name,pe.QtyPer, pe.rowid, PartNum,PE.MtlPartNum,  lv,sort
FROM PartsExplosion AS PE
ORDER BY sort
1 Like

Thanks Ken! Some pretty involved code, but it parses OK.
Where can I put the WHERE clause to only look at one PN to test it? This will run against all parts as is, but I can’t see to get the WHERE clause right to just test on one PN.

Regards,
George Hicks

IN the anchor
– Anchor

SELECT Erp.Part.PartNum,
Erp.Part.PartNum,
0,
cast(Erp.Part.PurchasingFactor as int) as QtyPer,
Erp.Part.SysRowID,
CAST(Erp.Part.PartDescription as nvarchar(100)) as Name,
CAST(’’ + Erp.Part.PartDescription as nvarchar(254)) as Sort
FROM Erp.Part
Where partnum in (‘part1’, ‘part2’)

The anchor potion of the query will be all of your top level parts you want to see BOM’s for. Hope that helps.

Hi @knash

I just came across this post and it covers exactly what a member of staff requested today.

Many thanks

Matt

@knash

I realize the answer to this is subjective to the size of the database it is run in, but in general what kind of runtimes can be expected with this query? Should it run within seconds? Minutes? Hours? I copied the query as written and attempted to run and am seeing run times that are far too long to ever query from a BAQ.

it usually runs in second to minutes for all boms fewer boms it will run quicker

did you try just one part?

@knash

I did try for one part. Its running for hours without results. I think part of the problem is that each layer of subassembly has multiple revisions and the query is cross referencing too much.

For example…
Top level has 3 revisions. Rev 1, 2, 3
1 layer deep, 4 parts, each part is a sub-assembly with multiple revisions, Rev 1, 2, 3 for each.

How does the query know that Top Level Rev 3 uses Rev 3 for the sub-assembly part? I don’t see anything in the Method Tracker or in the table that tracks which revision of the sub-assembly should be followed. Does Epicor just assume to use the most recently approved revision? I think the query is trying to build a BOM that includes each revision of the sub assembly.

correct Epicor doesn’t track the rev of the child part.

that version is the query is a bit out of date. let me grab a new one.

Here is a new base query we use. It has held up for about a year now. You may need to adjust the query a bit for your environment.

with [BOMReviewParent] as 
(select 
	(Part.ShortChar10) as [Calculated_Customer],
	(PartRev.PartNum) as [Calculated_TopPart],
	(PartRev.RevisionNum) as [Calculated_TopPartRev],
	(part.PartNum) as [Calculated_ParentPartNum],
	(PartRev.RevisionNum) as [Calculated_ParentRevNum],
	(part.PartNum) as [Calculated_ChildPartNum],
	(PartRev.RevisionNum) as [Calculated_ChildRevNum],
    (CAST(Part.TypeCode as nvarchar(500))) as [Calculated_TypeCode],
	(0) as [Calculated_lv],
	(Cast(part.PurchasingFactor as  decimal(10,2))) as [Calculated_QtyPer],
	(Cast(part.PartDescription as nvarchar(500))) as [Calculated_PartName],
	(0) as [Calculated_OpSeq],
	(CAST(part.PartNum + '-' + partrev.RevisionNum as nvarchar(500))) as [Calculated_Sort]
from dbo.Part as Part
inner join Erp.PartRev as PartRev on 
	Part.Company = PartRev.Company
	and Part.PartNum = PartRev.PartNum
 --and ( PartRev.PartNum = '003-718')
  and Part.InActive = 0 
  and Part.ClassID in ( 'FGD',  'FGA')
  and PartRev.Approved = 1
  --and PartRev.PartNum <> '001-855'
union all
select 
	[BOMReviewParent].[Calculated_Customer] as [Calculated_Customer],
	[BOMReviewParent].[Calculated_TopPart] as [Calculated_TopPart],
	[BOMReviewParent].[Calculated_TopPartRev] as [Calculated_TopPartRev],
	[BOMChildren].[PartMtl_PartNum] as [PartMtl_PartNum],
	[BOMChildren].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
	[BOMChildren].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[BOMChildren].[Calculated_RevNum] as [Calculated_RevNum],
   (CAST(BOMChildren.TypeCode as nvarchar(500))) as PlaceHolder,
	(BOMReviewParent.Calculated_lv + 1) as [Calculated_BOMlv],
	(cast(BOMChildren.Calculated_ChildQtyPer as  decimal(10,2))) as [Calculated_BOMQtyPer],
	(CAST(REPLICATE ('|     ' , BOMReviewParent.Calculated_lv +1) + BOMChildren.PartBOMChild_PartDescription as nvarchar(500))) as [Calculated_BOMPartName],
	(BOMChildren.Calculated_MtlSeq) as [Calculated_BOMMtlSeq],
	(CAST(BOMReviewParent.Calculated_Sort + '-' + RIGHT( '0000'+ Convert(varchar, BOMChildren.Calculated_SeqNum), 4)as nvarchar(500))) as [Calculated_BOMSort]
from  (select PartBOMChild.TypeCode as TypeCode,
	[PartMtl].[PartNum] as [PartMtl_PartNum],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	(((select 
	[PartRevA].[RevisionNum] as [PartRevA_RevisionNum]
from Erp.PartRev as PartRevA
inner join  (select PartRevB.Company,
	[PartRevB].[PartNum] as [PartRevB_PartNum],
	[PartRevB].[RevisionNum] as [PartRevB_RevisionNum],
	(ROW_NUMBER() OVER (PARTITION BY partrevb.PartNum ORDER BY  partrevb.ApprovedDate DESC)) as [Calculated_PartRevB_RowNum]
from Erp.PartRev as PartRevB
where (PartRevB.Approved = 1  and PartRevB.EffectiveDate <= getdate() and PartRevB.PartNum = [PartMtl].[MtlPartNum] ))  as PartRevB1 on 
	PartRevA.PartNum = PartRevB1.PartRevB_PartNum
	and PartRevA.RevisionNum = PartRevB1.PartRevB_RevisionNum
         and PartRevA.Company = PartRevB1.Company
where PartRevB1.Calculated_PartRevB_RowNum = 1))) as [Calculated_RevNum],
	(cast(PartMtl.QtyPer as  decimal(10,2))) as [Calculated_ChildQtyPer],
	[PartBOMChild].[PartDescription] as [PartBOMChild_PartDescription],
	(cast(PartMtl.MtlSeq as nvarchar(254))) as [Calculated_SeqNum],
	(CAST(PartMtl.MtlSeq as Int)) as [Calculated_MtlSeq]
from Erp.PartMtl as PartMtl
inner join dbo.Part as PartBOMChild on 
	PartMtl.Company = PartBOMChild.Company
	and PartMtl.MtlPartNum = PartBOMChild.PartNum
	
  )  as BOMChildren
inner join  BOMReviewParent  as BOMReviewParent on 
	BOMChildren.PartMtl_PartNum = BOMReviewParent.Calculated_ChildPartNum
	and BOMChildren.PartMtl_RevisionNum = BOMReviewParent.Calculated_ChildRevNum
   )
 
 select BOMReviewParentTOP.[Calculated_Customer],
	BOMReviewParentTOP.[Calculated_TopPart],
	BOMReviewParentTOP.[Calculated_TopPartRev],
	BOMReviewParentTOP.[Calculated_ParentPartNum],
	BOMReviewParentTOP.[Calculated_ParentRevNum],
	BOMReviewParentTOP.[Calculated_ChildPartNum],
	BOMReviewParentTOP.[Calculated_ChildRevNum],
	BOMReviewParentTOP.[Calculated_lv],
	BOMReviewParentTOP.[Calculated_QtyPer],
	BOMReviewParentTOP.[Calculated_PartName],
	BOMReviewParentTOP.[Calculated_OpSeq],
	BOMReviewParentTOP.[Calculated_Sort]
 from  BOMReviewParent  as BOMReviewParentTOP