How do I export an entire BOM tree?

[Former Member]

Hi, We're running SAP B1 2007. I'm trying to export and entire BOM list for a specific product. I can easily bring up the BOM and export it to Excel but it just exports the BOM for that item and not its children and grand children. Is there any kind of report that will do this for me?

Thanks!

[Former Member]

Hi Twilson,

Check this one .by using below query you can get the deatails in BOM Format .i Hope this will help you..

 Declare @BOMDetails table(TreeType Nvarchar(MAX),PItem NVARCHAR(Max),PName NVARCHAR(MAX),CItem  NVARCHAR(Max),CName NVARCHAR(MAX),[Quantity] Numeric(18,2),[UoM] NVARCHAR(MAX),[WareHouse] NVARCHAR(MAX),[IssuMethod] NVARCHAR(MAX),[PriceList] NVARCHAR(MAX))
INSERT Into @BOMDetails 
SELECT T1.TreeType ,T0.Father AS [Parent Code], T2.ItemName AS [Parent Description], T0.Code AS [Child Code],
T1.ItemName AS [Child Description], T0.Quantity ,T0.Uom ,T0.Warehouse ,T0.IssueMthd,T0.PriceList   
FROM ITT1 T0 INNER JOIN OITM T1 ON T0.Code = T1.ItemCode 
             INNER JOIN OITM T2 ON T0.Father = T2.ItemCode
Union All
SELECT ' ',T0.Father as [Parent Code], T2.ItemName AS [Parent Description], ' ', ' ', 0, ' ',' ',' ' , 0 FROM ITT1 T0 INNER JOIN OITM T1 
ON T0.Code = T1.ItemCode INNER JOIN OITM T2 ON T0.Father = T2.ItemCode
Group By T0.Father,T2.ItemName 
ORDER BY T0.Father,t0.Code 
update @BOMDetails set PItem='' ,PName='' where TreeType='N' or TreeType='P'
Select PItem as[Parent Code] ,PName as [Parent Description],CItem as [Child Code],CName as [Child Description],Quantity,UoM,IssuMethod ,PriceList    from @BOMDetails 
 

Regards,

Amit

[Former Member]

Hi Amit and all,

I know this is a very old thread but I was trying to use this query. Can I know how I can get the sub-BOM(built for child item) as well. Can anyone give me an improvised query from the above query?

Look forward for suggestions.

Rgds

vitha

[Former Member]

Hi.......

Generally if you export BOM, it gets exported with its child item. The only thing you need to do is you have to expand it to last level.

Anyways if you still has problem then try following query in query generator and get it export in XL......

SELECT *  FROM OITT T0  INNER JOIN ITT1 T1 ON T0.Code = T1.Father

Hope this will help you out....

Regards,

Rahul

[Former Member]

"Generally if you export BOM, it gets exported with its child item."

If I do an export it exports the materials in the current item and no child expansion. If I run the query you supplied, I get a massive list of materials with no structure or specific order.

Is there nothing in SAP that will print or export a structured and complete bill of materials?

Thanks

[Former Member]

Hi,

BOM in B1 has no structure. You can only use query to imitate the structure. If you want to have a good structure, it is a very complicated query depening on your BOM level.

Thanks,

Gordon