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