cancel
Showing results for 
Search instead for 
Did you mean: 

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) :

caspian005
Participant
0 Kudos


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

Accepted Solutions (0)

Answers (3)

Answers (3)

kothandaraman_nagarajan
Active Contributor
0 Kudos

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

caspian005
Participant
0 Kudos

Dear Nagarajan K,

The query you suggested is about multi level BOM but I want multilevel BOM with Inventory Value?

Thanks & Regards,

Prabhakar Patole

frank_wang6
Active Contributor
0 Kudos

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

frank_wang6
Active Contributor
0 Kudos

Actually i saw 00050B is both parent and child, right? so you are using multiple level BOM.

If that's the case, you will need to advise the max level, this is due to BOM expansion. To do the calculation correctly, you will have to expand the BOM to the smallest child.

Frank

caspian005
Participant
0 Kudos

Dear Frank Wang,

We are using multiple level BOM.

in that case the maximum level of BOM is up to 15.

so, u can write the query according to 15 level.

It will be better if item code come with Description.

Thanks & Regards,

Prabhakar Patole

frank_wang6
Active Contributor
0 Kudos

oh, man, 15 levels of bom expansion.

Ok, if you want to do it in real time, you will have to SQL Function to do that.

And to do a 15 level BOM expansion in real time is high CPU load work, I would NOT suggest u do that. Basically you are inner join ITT1 itself 15 times  to get the result. If manually join table 15 times is tedious to write, dynamic SQL is recommended, but the performance will be even slower.

Since this is for reporting purpose, I would suggest you to use a customized table to hold the expansion result, and refresh the table every day or on some schedule to reduce the system load.

With these directions above, if you can finish it, then you should be able to figure a way out. If you can not, I would suggest you to find a consultant to do it for you.

Frank

caspian005
Participant
0 Kudos

Dear Frank,

Don't worry about CPU work load.

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

Thanks & Regards,

Prabhakar Patole

kothandaraman_nagarajan
Active Contributor
0 Kudos

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.

caspian005
Participant
0 Kudos

Dear Nagarajan K,

I want query for sum of inventory of one Particular BOM(0005) item one by one as discussed below:

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

Regards,

Prabhakar Patole

kothandaraman_nagarajan
Active Contributor
0 Kudos

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]