on 05-22-2015 12:36 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.