Skip to Content

Create query for multi-level BOM SAP B1

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    Dec 03, 2012 at 03:19 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 19, 2014 at 11:00 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 03, 2012 at 03:44 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 24, 2016 at 01:11 PM

    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

    Add comment
    10|10000 characters needed characters exceeded