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!
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
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
Add a comment