Query

[Former Member]

Hi Experts,

For Multilevel BOM there will be a chain of parent items and child items. By what query can we get all these details, like

child items details and parent items for that child items. I am going from BOM OITT and ITT1 table.

How to get details of all the child items and related parent items

[Former Member]

Actually I want something

Component Item Group

Component Item no

Component Item Description

FG Item No

FG Item Description

FG Item Short Text

Sales Opportunity NO

Business Partner

So query needs to be written for that

[Former Member]

Hi,

The above wiki link gave you a good idea what the result query needed. Unless you have only two levels, the query you asked for will be too long.

Thanks,

Gordon

[Former Member]

SELECT

T0.[Father] as 'Assembly',

[%1] as 'BuildQty',

T0.[code] as 'Component1',

t10.[ItemName] 'Description1',

T0.[Quantity] as 'Quantity1',

[%1] * t0.[Quantity] as 'ExtQty1',

t10.OnHand as 'OnHand1',

case

when t10.OnHand - ([%1] * t0.[Quantity]) > 0 then 0

else

-(t10.OnHand - ([%1] * t0.[Quantity])) end as 'Shortage1',

T1.[Code] as 'Component2',

t11.[ItemName] 'Description2',

T1.[Quantity] as 'Quantity2',

[%1] * t0.[Quantity] * t1.[Quantity] as 'ExtQty2',

t11.OnHand as 'OnHand2',

case

when t11.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity]) > 0 then 0

else

-(t11.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity])) end as 'Shortage2',

T2.[Code] as 'Component3',

t12.[ItemName] 'Description3',

T2.[Quantity] as 'Quantity3',

[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] as 'ExtQty3',

t12.OnHand as 'OnHand3',

case

when t12.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity]) > 0 then 0

else

-(t12.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity]))

end as 'Shortage3',

T3.[Code] as 'Component4', t13.[ItemName] 'Description4', T3.[Quantity] as 'Quantity4',

[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] as 'ExtQty4',

t13.OnHand as 'OnHand4',

case when t13.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity]) > 0 then 0 else

-(t13.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity])) end as 'Shortage4',

T4.[Code] as 'Component5', t14.[ItemName] 'Description5', T4.[Quantity] as 'Quantity5',

[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] as 'ExtQty5',

t14.OnHand as 'OnHand5',

case

when t14.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity]) > 0 then 0

else

-(t14.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity])) end as 'Shortage5',

T5.[Code] as 'Component6',

t15.[ItemName] 'Description6',

T5.[Quantity] as 'Quantity6',

[%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] * t5.[Quantity] as 'ExtQty6',

t15.OnHand as 'OnHand6',

case

when t15.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] * t5.[Quantity]) > 0 then 0

else

-(t15.OnHand - ([%1] * t0.[Quantity] * t1.[Quantity] * t2.[Quantity] * t3.[Quantity] * t4.[Quantity] * t5.[Quantity])) end as 'Shortage6'

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

WHERE T0.[Father] = [%0]

[Former Member]

/

• END OF QUERY*/

This query is giving error

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 4). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 5). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 6). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 7). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 8). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 9). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 10). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 11). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 12). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 13). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 14). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 15). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 16). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 17). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 18). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 19). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A00001'. 20). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared.

ok plz guide me for 2 levels

[Former Member]

I am not sure what are the last two fields come from. They may not fit in the query.

[Former Member]

Last two fields?

[Former Member]

HI,

try this if it could help:

SELECT Distinct T0.ItemCode, T0.itemName, T1.Code, T1.Quantity, T2.Code, T2.Quantity, T3.Code, T3.Quantity
FROM dbo.OITM T0
INNER JOIN dbo.ITT1 T1 ON T1.Father = T0.ItemCode
LEFT JOIN dbo.ITT1 T2 ON T1.Code = T2.Father
LEFT JOIN dbo.ITT1 T3 ON T2.Code = T3.Father
WHERE T1.Father=[%0]

Thanks,

Neetu

[Former Member]

Sales Opportunity NO

Business Partner

[Former Member]

Hi Neetu Thanks a lot for your query, I confirmed about the requirement , they told maximum 10-12 levels might get reached. So i think stored procedure is needed where functions to be used......Any Help?

[Former Member]

SELECT Distinct

T0.ItemCode 'Parent',

T0.itemName 'Parent Des',

T1.Code 'Component1',

T1.Quantity,

T2.Code 'Component2',

T2.Quantity,

T3.Code 'Component3',

T3.Quantity,

T4.Code,

T4.Quantity

FROM dbo.OITM T0

INNER JOIN dbo.ITT1 T1 ON T1.Father = T0.ItemCode

LEFT JOIN dbo.ITT1 T2 ON T1.Code = T2.Father

LEFT JOIN dbo.ITT1 T3 ON T2.Code = T3.Father

Left Join dbo.ITT1 T4 ON T3.Code = T4.Father

WHERE T1.Father=[%0]

Should I go on adding ITT1 like this...or any other option is there a per the BOM level plz help

[Former Member]

Any Suggestions?

[Former Member]

Try this:

SELECT T0.Father,

T3.Itemname 'Parent Desc.'

T2.itmsgrpnam,

T0.code '1st Level',

T1.Itemname 'Component Desc.',

T6.itmsgrpnam,

T4.code '2nd Level,

T5.ItemName 'Component Desc.'

From dbo.ITT1 T0

LEFT JOIN dbo.OITM T1 ON T1.ItemCode=T0.CODE

LEFT JOIN dbo.OITB T2 ON T2.itmsgrpcod=T1.itmsgrpcod

LEFT JOIN dbo.OITM T3 ON T3.ItemCode=T0.Father

LEFT JOIN dbo.ITT1 T4 On T4.Father=T0.Code

LEFT JOIN dbo.OITM T5 ON T5.ItemCode=T4.Code

LEFT JOIN dbo.OITB T6 ON T6.itmsgrpcod=T5.itmsgrpcod

[Former Member]

Hi Akash,

Try this:

declare @temp AS INTEGER

DECLARE @productionOrders TABLE (
                  RowID INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,

                  GrandParentItemCode NVARCHAR(20),               

                  ParentItemCode NVARCHAR(20),

                  ParentItemName NVARCHAR(100),

                  ChildItemCode NVARCHAR(20),

                  ChildItemName NVARCHAR(MAX),

                  ChildLevel INTEGER,

                  InStock NUMERIC(38, 6),                       

                  TreeType NVARCHAR(1),

                   PlannedQty NUMERIC(38, 6)
)
/*SELECT FROM [dbo].[OITT] T0*/
declare  @oworItemCodeFr as Nvarchar(20)
/* WHERE */
set @oworItemCodeFr = /* T0.Code */ '[%0]'

/*SELECT FROM [dbo].[OITT] T1*/
declare  @oworItemCodeTo as nvarchar(20)
/* WHERE */
set @oworItemCodeTo = /* T0.Code */ '[%1]'

;WITH PARENT_OITT AS 

      (SELECT   '' AS GrandParentItemCode, T1.Code  AS ParentItemCode, T4.ItemName AS ParentItemName,

                  T0.Code AS ChildItemCode,  T3.ItemName AS ChildItemName, 0 AS ChildLevel, 

                  T3.OnHand AS InStock,T3.TreeType, T1.Qauntity  AS PlannedQty

            FROM ITT1 T0

                  INNER JOIN OITT T1 ON T1.Code  = T0.Father 

                  INNER JOIN OITM T3 ON T3.ItemCode = T0.Code 

                  INNER JOIN OITM T4 ON T4.ItemCode = T1.Code 

            WHERE  T1.Code  BETWEEN @oworItemCodeFr AND @oworItemCodeTo
          
      UNION ALL

      SELECT   '' AS GrandParentItemCode, T0.ChildItemCode AS ParentItemCode, T5.ItemName AS ParentItemName,

                  T2.Code  AS ChildItemCode, T4.ItemName AS ChildItemName, T0.ChildLevel + 1 AS ChildLevel, 

                  T4.OnHand AS InStock,T4.TreeType, T2.Quantity AS PlannedQty

            FROM PARENT_OITT T0

                  INNER JOIN OITT T1 ON T1.Code  = T0.ChildItemCode

                  INNER JOIN ITT1 T2 ON T2.Father   = T1.Code 

                  INNER JOIN OITM T4 ON T4.ItemCode = T2.Code 

                  INNER JOIN OITM T5 ON T5.ItemCode = T1.Code 
)      

INSERT INTO @productionOrders SELECT GrandParentItemCode, ParentItemCode, ParentItemName, ChildItemCode, ChildItemName, MAX(ChildLevel) AS ChildLevel , 

            MAX(InStock) AS InStock,   MAX(TreeType) AS TreeType,MAX(PlannedQty) AS PlannedQty

      FROM PARENT_OITT T0

      GROUP BY T0.GrandParentItemCode, T0.ParentItemCode, T0.ParentItemName, T0.ChildItemCode, T0.ChildItemName 

SELECT      ParentItemCode AS [Parent Product No.],

            ParentItemName AS [Parent Product Description],

            ChildLevel AS [Child Level],

            ChildItemCode AS [Child Product No.],

            ChildItemName AS [Child Product Description],

            PlannedQty AS [Planned Qty]      

FROM @productionOrders ORDER BY RowID desc

Thanks,

Neetu

[Former Member]

Hi,

try this one:

SELECT T0.[Code], T2.[ItemName], T0.[Qauntity], T0.[ToWH], T1.[Code], T1.[Quantity], T2.[InvntryUom], T1.[Warehouse], T1.[Price] FROM OITT T0 INNER JOIN ITT1 T1 ON T0.Code = T1.Father INNER JOIN OITM T2 ON T0.Code = T2.ItemCode

regards,

Vignesh

[Former Member]

Hi Akash,

Check this link.

http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLI-PRToChecktheComponentsupto6levelsdeepintoaBOM

Thanks,

Srujal Patel

[Former Member]

Hi Akash.....

There is no need to go for Query...

It is already there in Standard B1.

Go to Production> Production Reports> Bill Of Material

Select any final item which has multilevel bill of Material and click on OK......

Hope this will give you right direction.......

Regards,

Rahul