- SAP Community
- Products and Technology
- Enterprise Resource Planning
- ERP Q&A
- Create query for multi-level BOM SAP B1
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Create query for multi-level BOM SAP B1
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 12-03-2012 3:10 AM
Hi,
Can anybody share if you have created a query for multi-level BOM? Ideally this query should show only component items where BOM item is at least 4 levels and excluding sub levels parent item. I liked the idea of combining BOM window and BOM report. BOM window has the ability to check pricing while BOM report have the ability to show all components for multi-level BOM; currently BOM window only shows you 1 level. Thoughts everyone?
Regards
Didy
- SAP Managed Tags:
- SAP Business One
Accepted Solutions (1)
Accepted Solutions (1)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi,
Try this:
SELECT T0.[Father] as 'Assembly',T0.[code] as 'Component1', t10.[ItemName] 'Description1',T1.[Code] as 'Component2', t11.[ItemName] 'Description2', T2.[Code] as 'Component3', t12.[ItemName] 'Description3', T3.[Code] as 'Component4', t13.[ItemName] 'Description4',T4.[Code] as 'Component5', t14.[ItemName] 'Description5', T5.[Code] as 'Component6', t15.[ItemName] 'Description6' FROM ITT1 T0 LEFT OUTER JOIN ITT1 T1 on T0.Code = T1.Father LEFT OUTER JOIN ITT1 T2 on T1.Code = T2.Father LEFT OUTER JOIN ITT1 T3 on T2.Code = T3.Father LEFT OUTER JOIN ITT1 T4 on T3.Code = T4.Father LEFT OUTER JOIN ITT1 T5 on T4.Code = T5.Father LEFT OUTER JOIN ITT1 T6 on T5.Code = T6.Father left outer join oitm t20 on t0.father = t20.itemcode left outer join oitm t10 on t0.code = t10.itemcode left outer join oitm t11 on t1.code = t11.itemcode left outer join oitm t12 on t2.code = t12.itemcode left outer join oitm t13 on t3.code = t13.itemcode left outer join oitm t14 on t4.code = t14.itemcode left outer join oitm t15 on t5.code = t15.itemcode
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Answers (4)
Answers (4)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Try Below Query with 10 Level of BOM & you can Add as much you required.
Only the thing you have to decide max BOM Level
DECLARE @Father VARCHAR(max), @Father_Name VARCHAR(max),@Child VARCHAR(50),@Child_Name VARCHAR(max),@BOMType VARCHAR(50),@LEVEL INT,@Quantity as Float,@UOM as varchar(max),
@Child1 VARCHAR(50),@Child_Name1 VARCHAR(max),@BOMType1 VARCHAR(50),@LEVEL1 INT,@Quantity1 as Float,@UOM1 as varchar(max),
@Child2 VARCHAR(50),@Child_Name2 VARCHAR(max),@BOMType2 VARCHAR(50),@LEVEL2 INT,@Quantity2 as Float,@UOM2 as varchar(max),
@Child3 VARCHAR(50),@Child_Name3 VARCHAR(max),@BOMType3 VARCHAR(50),@LEVEL3 INT,@Quantity3 as Float,@UOM3 as varchar(max),
@Child4 VARCHAR(50),@Child_Name4 VARCHAR(max),@BOMType4 VARCHAR(50),@LEVEL4 INT,@Quantity4 as Float,@UOM4 as varchar(max),
@Child5 VARCHAR(50),@Child_Name5 VARCHAR(max),@BOMType5 VARCHAR(50),@LEVEL5 INT,@Quantity5 as Float,@UOM5 as varchar(max),
@Child6 VARCHAR(50),@Child_Name6 VARCHAR(max),@BOMType6 VARCHAR(50),@LEVEL6 INT,@Quantity6 as Float,@UOM6 as varchar(max),
@Child7 VARCHAR(50),@Child_Name7 VARCHAR(max),@BOMType7 VARCHAR(50),@LEVEL7 INT,@Quantity7 as Float,@UOM7 as varchar(max),
@Child8 VARCHAR(50),@Child_Name8 VARCHAR(max),@BOMType8 VARCHAR(50),@LEVEL8 INT,@Quantity8 as Float,@UOM8 as varchar(max),
@Child9 VARCHAR(50),@Child_Name9 VARCHAR(max),@BOMType9 VARCHAR(50),@LEVEL9 INT,@Quantity9 as Float,@UOM9 as varchar(max),
@Child10 VARCHAR(50),@Child_Name10 VARCHAR(max),@BOMType10 VARCHAR(50),@LEVEL10 INT,@Quantity10 as Float,@UOM10 as varchar(max)
CREATE TABLE #BOMTable
(
Father VARCHAR(max) ,
FatherName VARCHAR(max),Child VARCHAR(max),ChildName VARCHAR(max),BomType VARCHAR(max),LEVELX int,Quantity float,UOM varchar(max)
)
DECLARE LEVEL0 CURSOR
FOR SELECT T0.Code as 'Father',T2.ItemName as 'Father Name ',t1.Code as 'Child',T3.ItemName as 'Child Name',t3.TreeType as 'BOM Type',2 AS 'LEVEL',t1.Quantity,t3.InvntryUom
FROM OITT T0
LEFT JOIN ITT1 T1 on T0.Code=T1.Father
INNER join OITM T2 on T0.Code = T2.ItemCode
INNER join OITM T3 on T1.Code = T3.ItemCode
--WHERE T0.Code='NNFGLDBN00000001'
ORDER by t1.VisOrder
OPEN LEVEL0
FETCH NEXT FROM LEVEL0 INTO @Father,@Father_Name,@Child,@Child_Name,@BOMType,@LEVEL,@Quantity,@UOM
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child,@Child_Name,@BOMType,@LEVEL,@Quantity,@UOM)
------------------Level 1-----------------------------
IF @BOMType = 'P'
BEGIN
DECLARE LEVEL1 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child order by L1.VisOrder
OPEN LEVEL1
FETCH NEXT FROM LEVEL1 INTO @Child1,@Child_Name1,@BOMType1,@Quantity1,@UOM1
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child1,@Child_Name1,@BOMType1,3,@Quantity1,@UOM1)
------------------Level 2-----------------------------
IF @BOMType1 = 'P'
BEGIN
DECLARE LEVEL2 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child1 order by L1.VisOrder
OPEN LEVEL2
FETCH NEXT FROM LEVEL2 INTO @Child2,@Child_Name2,@BOMType2,@Quantity2,@UOM2
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child2,@Child_Name2,@BOMType2,4,@Quantity2,@UOM2)
------------------Level 3-----------------------------
IF @BOMType2 = 'P'
BEGIN
DECLARE LEVEL3 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child2 order by L1.VisOrder
OPEN LEVEL3
FETCH NEXT FROM LEVEL3 INTO @Child3,@Child_Name3,@BOMType3,@Quantity3,@UOM3
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child3,@Child_Name3,@BOMType3,5,@Quantity3,@UOM3)
------------------Level 4-----------------------------
IF @BOMType3 = 'P'
BEGIN
DECLARE LEVEL4 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child3 order by L1.VisOrder
OPEN LEVEL4
FETCH NEXT FROM LEVEL4 INTO @Child4,@Child_Name4,@BOMType4,@Quantity4,@UOM4
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child4,@Child_Name4,@BOMType4,6,@Quantity4,@UOM4)
------------------Level 5-----------------------------
IF @BOMType4 = 'P'
BEGIN
DECLARE LEVEL5 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child4 order by L1.VisOrder
OPEN LEVEL5
FETCH NEXT FROM LEVEL5 INTO @Child5,@Child_Name5,@BOMType5,@Quantity5,@UOM5
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child5,@Child_Name5,@BOMType5,7,@Quantity5,@UOM5)
------------------Level 6-----------------------------
IF @BOMType5 = 'P'
BEGIN
DECLARE LEVEL6 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child5 order by L1.VisOrder
OPEN LEVEL6
FETCH NEXT FROM LEVEL6 INTO @Child6,@Child_Name6,@BOMType6,@Quantity6,@UOM6
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child6,@Child_Name6,@BOMType6,8,@Quantity6,@UOM6)
------------------Level 7-----------------------------
IF @BOMType6 = 'P'
BEGIN
DECLARE LEVEL7 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child6 order by L1.VisOrder
OPEN LEVEL7
FETCH NEXT FROM LEVEL7 INTO @Child7,@Child_Name7,@BOMType7,@Quantity7,@UOM7
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child7,@Child_Name7,@BOMType7,9,@Quantity7,@UOM7)
------------------Level 7-----------------------------
IF @BOMType7 = 'P'
BEGIN
DECLARE LEVEL8 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child7 order by L1.VisOrder
OPEN LEVEL8
FETCH NEXT FROM LEVEL8 INTO @Child8,@Child_Name8,@BOMType8,@Quantity8,@UOM8
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO #BOMTable values(@Father,@Father_Name,@Child8,@Child_Name8,@BOMType8,10,@Quantity8,@UOM8)
FETCH NEXT FROM LEVEL8 INTO @Child8,@Child_Name8,@BOMType8,@Quantity8,@UOM8
END
CLOSE LEVEL8
DEALLOCATE LEVEL8
END
------------------Level 7-----------------------------
FETCH NEXT FROM LEVEL7 INTO @Child7,@Child_Name7,@BOMType7,@Quantity7,@UOM7
END
CLOSE LEVEL7
DEALLOCATE LEVEL7
END
------------------Level 7-----------------------------
FETCH NEXT FROM LEVEL6 INTO @Child6,@Child_Name6,@BOMType6,@Quantity6,@UOM6
END
CLOSE LEVEL6
DEALLOCATE LEVEL6
END
------------------Level 6-----------------------------
FETCH NEXT FROM LEVEL5 INTO @Child5,@Child_Name5,@BOMType5,@Quantity5,@UOM5
END
CLOSE LEVEL5
DEALLOCATE LEVEL5
END
------------------Level 5-----------------------------
FETCH NEXT FROM LEVEL4 INTO @Child4,@Child_Name4,@BOMType4,@Quantity4,@UOM4
END
CLOSE LEVEL4
DEALLOCATE LEVEL4
END
------------------Level 4-----------------------------
FETCH NEXT FROM LEVEL3 INTO @Child3,@Child_Name3,@BOMType3,@Quantity3,@UOM3
END
CLOSE LEVEL3
DEALLOCATE LEVEL3
END
------------------Level 3-----------------------------
FETCH NEXT FROM LEVEL2 INTO @Child2,@Child_Name2,@BOMType2,@Quantity2,@UOM2
END
CLOSE LEVEL2
DEALLOCATE LEVEL2
END
------------------Level 2-----------------------------
FETCH NEXT FROM LEVEL1 INTO @Child1,@Child_Name1,@BOMType1,@Quantity1,@UOM1
END
CLOSE LEVEL1
DEALLOCATE LEVEL1
END
------------------Level 1-----------------------------
FETCH NEXT FROM LEVEL0 INTO @Father,@Father_Name,@Child,@Child_Name,@BOMType,@LEVEL,@Quantity,@UOM
END
SELECT * FROM #BOMTable T0 order by T0.Father
DROP TABLE #BOMTable
CLOSE LEVEL0
DEALLOCATE LEVEL0
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
hi,
I need quary that will give me the BOM with all degries, like the BOM report of SBO, but i want to add colums fro OITM and from UTF tables (the contact file is the child PN).
the output colums are: father(ITT1), depth(ITT1), sun(ITT1), qty(ITT1), comment (ITT1), Barcode (OITM), manpn(UDF table)
thanks
Oded
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
You can try this Query also.
Output column's are
Header Item ,Component Name,Quantity, warehouse,Current Stock in BOM warehouse
Query/* SELECT FROM [dbo].[OITT] T0 */
Declare @BOM nvarchar(max)
/* WHERE */
Set @BOM=/* T0.Code */ '[%0]'
select B.Assembly [Product No.],B.Component,(select ItemName from OITM where ItemCode=B.Component)[Component Name],B.[BOM Quantity],
B.[BOM warehouse],
(Select sum (OnHand) from OITW where ItemCode=B.Component and WhsCode=B.[BOM warehouse])[Current Stock in BOM warehouse]
from (select A.Assembly,CASE when isnull(A.Component6,'')='' then
(case when isnull(A.Component5,'')='' then
(case when isnull(A.Component4,'')='' then
(case when isnull(A.Component3,'')='' then
(case when isnull(A.Component2,'')='' then
A.Component1 else A.Component2 end)else A.Component3 end)
else A.Component4 end) else A.Component5 end ) else A.Component6 end Component
,CASE when isnull(A.Qty6,0)=0 then
(case when isnull(A.Qty5,0)=0 then
(case when isnull(A.Qty4,0)=0 then
(case when isnull(A.Qty3,0)=0 then
(case when isnull(A.Qty2,0)=0 then
A.Qty1 else A.Qty2 end)else A.Qty3 end)
else A.Qty4 end) else A.Qty5 end ) else A.Qty6 end [BOM Quantity]
,CASE when isnull(A.whs6,'')='' then
(case when isnull(A.whs5,'')='' then
(case when isnull(A.whs4,'')='' then
(case when isnull(A.whs3,'')='' then
(case when isnull(A.whs2,'')='' then
A.whs1 else A.whs2 end)else A.whs3 end)
else A.whs4 end) else A.whs5 end ) else A.whs6 end [BOM warehouse]
from (SELECT T0.[Father] as 'Assembly',T0.[code] as 'Component1', T0.Quantity as 'Qty1', T0.warehouse as 'whs1',
t10.[ItemName] 'Description1',T1.[Code] as 'Component2', T1.Quantity as 'Qty2', T1.warehouse as 'whs2',
t11.[ItemName] 'Description2', T2.[Code] as 'Component3', T2.Quantity as 'Qty3', T2.warehouse as 'whs3',
t12.[ItemName] 'Description3', T3.[Code] as 'Component4', T3.Quantity as 'Qty4', T3.warehouse as 'whs4',
t13.[ItemName] 'Description4',T4.[Code] as 'Component5', T4.Quantity as 'Qty5', T4.warehouse as 'whs5',
t14.[ItemName] 'Description5', T5.[Code] as 'Component6', T5.Quantity as 'Qty6', T5.warehouse as 'whs6',
t15.[ItemName] 'Description6' FROM ITT1 T0 LEFT OUTER JOIN
ITT1 T1 on T0.Code = T1.Father LEFT OUTER JOIN ITT1 T2 on
T2.Father =T1.Code LEFT OUTER JOIN ITT1 T3 on T2.Code = T3.Father
LEFT OUTER JOIN ITT1 T4 on T3.Code = T4.Father LEFT OUTER JOIN
ITT1 T5 on T4.Code = T5.Father LEFT OUTER JOIN ITT1 T6 on
T5.Code = T6.Father left outer join oitm t20 on t0.father = t20.itemcode
left outer join oitm t10 on t0.code = t10.itemcode left outer join oitm t11
on t1.code = t11.itemcode left outer join oitm t12 on t2.code = t12.itemcode
left outer join oitm t13 on t3.code = t13.itemcode left outer join
oitm t14 on t4.code = t14.itemcode left outer join oitm t15 on t5.code = t15.itemcode
where T0.Father=@BOM)A)B
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Didy Arfiono
Try this query
SELECT Distinct T1.Code, T0.ItemName, T1.Father Father1, T1.Quantity BOM1, T2.Father Father2, T2.Quantity BOM2, T3.Father Father3, T2.Quantity BOM3
FROM dbo.OITM T0
INNER JOIN dbo.ITT1 T1 ON T1.Code = T0.ItemCode
INNER JOIN dbo.ITT1 T2 ON T2.Code = T0.ItemCode AND T2.Code = T1.Code
LEFT JOIN dbo.ITT1 T3 ON T3.Code = T0.ItemCode AND T3.Code = T2.Code
WHERE T1.Father = '[%0]' AND T2.Father = '[%1]' AND T3.Father = '[%2]'
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- SAP S/4HANA Cloud, public edition, ABAP Environment Case 8: Material Shelf Life Management in Enterprise Resource Planning Blogs by SAP
- Flexible Forecast consumption BADI not working in Enterprise Resource Planning Q&A
- Hiding rows in RDR1 - UDF in Enterprise Resource Planning Q&A
- can I explode BOM in repair order process in customer services in Enterprise Resource Planning Q&A
- CDS View for Planned Values & Actual Values in Enterprise Resource Planning Q&A
| User | Count |
|---|---|
| 99 | |
| 11 | |
| 11 | |
| 6 | |
| 6 | |
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.