Skip to Content

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • 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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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

    Add comment
    10|10000 characters needed characters exceeded

  • May 23, 2015 at 09:55 AM

    If my previous does not meet your requirement,please check this thread. Modify as per your requirement.

    Add comment
    10|10000 characters needed characters exceeded