# What is SAP B1 query for Parent Item, Quantity, Child Item & Total sum of BOM Stock Quantity?(e.g. if 00050B,00050BO,00050FG,00050LG is BOM data & in hand quantities are 50, 40, 10 & 15. I want below result from the query) :

Dear Experts,

What is SAP B1 query for Parent Item, Quantity, Child Item & Total sum of BOM Stock Quantity?(e.g. if 00050B,00050BO,00050FG,00050LG is BOM data & in hand quantities are 50, 40, 10 & 15. I want below result from the query) :

Parent Item    Quantity   Child Item   in Stock

00050B             267         RM.....        77220

00050BO           1           ........               456

0005FG             357        ........            4500

00050LG            500        00050B      500

Total Sum of In Stock Qty.           82676

10|10000 characters needed characters exceeded

• May 22, 2015 at 11:54 AM

Hi,

Try this query:

SELECT T0.[Code], sum(T1.[OnHand]), T2.[Code], sum(T3.[OnHand]) FROM OITT T0  INNER JOIN OITM T1 ON T0.[Code] = T1.[ItemCode] INNER JOIN ITT1 T2 ON T0.[Code] = T2.[Father] INNER JOIN OITM T3 ON T0.[Code] = T3.[ItemCode]

group by T0.[Code],T2.[Code]

Thanks.

10|10000 characters needed characters exceeded
• Nagarajan K Prabhakar Patole

Try this:

SELECT T0.[Code], sum(T1.[OnHand]), T2.[Code], sum(T3.[OnHand]) FROM OITT T0  left JOIN OITM T1 ON T0.[Code] = T1.[ItemCode] left JOIN ITT1 T2 ON T0.[Code] = T2.[Father] INNER JOIN OITM T3 ON  T2.[Code] = T3.[ItemCode]

where  T0.[Code] = '[%0]'

group by T0.[Code],T2.[Code]

• May 22, 2015 at 03:19 PM

what are those .... means?

I would suggest you give us one BOM structure. It is not hard to write this query. The hard part is to understand your intention.

BTW, do u have multiple level BOM? If so, please advise the maximum level.

Frank

10|10000 characters needed characters exceeded
• Prabhakar Patole Frank Wang

Dear Frank,

please do the query,I will take care of points u suggested above.

Thanks & Regards,

Prabhakar Patole

• May 23, 2015 at 09:55 AM