cancel
Showing results for 
Search instead for 
Did you mean: 

Required SQL Query

Former Member
0 Kudos

Dear All,

I want a query which gives me following result.

1) Finished Goods from open sales order with order No.

2) All child item of above (Up to depth 18)

3) Ordered Qty from sales order

4) Instock Qty.

5) Planned Qty

6) Releases Qty

7) Pending to release qty

Please help me for the above query

Ashish Gupte

Accepted Solutions (1)

Accepted Solutions (1)

former_member218051
Active Contributor
0 Kudos

Hi Ashish,

will you pls. explain in more detail ?

Thanks

Malhaar

Former Member
0 Kudos

Hi Malhar,

Basically i want to generate one report in which all items which are in sales order with open status will appear with child component with order quantity, instock, planned quantity and released qty.

depth of BOM is 18 Level

I think its now clear now

Ashish Gupte

Former Member
0 Kudos

Hi Ashish Gupte,

If the depth of BOM is 18 Level, the query will be super long. I doubt it is workable.

What % of the BOM has high level say > 5 levels?

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

I agreed but my requirement is running the MRP based on open sales orders and need to track of inventory of each component where it is staged

i tried this in Standard of SAP's MRP but when the lead time of the item is more ,then sometimes MRP fails to give proper result. Secondly we have almost 800- 1500 Production orders,40 sales order with min 50 line item per day so database is also to heavy it is taking too long time to run the MRP. we dont have any issue with our server and connectivity.

'

i also tried through querry up to 7 level but i need to extend this query. If any help from forum members will be appriciated.

i had gone through your book also for queries, is there any other method to achieve this

Thanks & Regards

Ashish Gupte

former_member204969
Active Contributor
0 Kudos

I donu2019t understand exactly your requirements, but if your main problem is the number of levels, then you can use some recursive code. I think you can start with this:

With BOM (Parent,Child,Quantity,Level) As
(
Select P.Father Parent,P.Code Child, Quantity,0 Level
 From ITT1 P
 Where P.Father =  '[%0]'
Union All
Select c.Father,C.Code,C.Quantity,Level+1
 From Itt1 C
  Inner join BOM B on c.Father=B.Child
)
Select B.Parent,B.Child,B.Quantity,B.Level,
   I.OnHand
 From BOM B Inner join OITM I On B.Child=I.ItemCode
Option (MAXRECURSION 99)

Former Member
0 Kudos

Hi Istvan Koros,

Thanks for this.

But i want to pick all the Finished Goods from open sales Order and then relate that will BOM and required Order Qty, (From Sales Order) Planned Qty (from Production Order if not released) Released Qty (if Released) Instock from (OITW)

Thanks in advance

Ashish Gupte

Former Member
0 Kudos

For 7 levels, you have done a great job already. Your query must very long already. Is the query speed fine? If it is fast enough, you can use the same logic and go to deeper level.

Former Member
0 Kudos

Hi Gordon,

Even if you run the istvan Query you can go upto the last level defined in BOM (What ever the level of BOM). only i dont want to select item by giving [%0] it should pick from the open sales order

Ashish Gupte

former_member204969
Active Contributor
0 Kudos

To show the BOMs for the items in open sales orders you can start from this:

With BOM (Parent,Child,Quantity,Level) As
(
Select P.Father Parent,P.Code Child, Quantity,0 Level
 From ITT1 P
 Where P.Father in (SELECT T0.[ItemCode] FROM RDR1 T0 WHERE T0.[LineStatus] ='O')
Union All
Select c.Father,C.Code,C.Quantity,Level+1
 From Itt1 C
  Inner join BOM B on c.Father=B.Child
)
Select B.Parent,B.Child,B.Quantity,B.Level,
   I.OnHand
 From BOM B Inner join OITM I On B.Child=I.ItemCode
Option (MAXRECURSION 99)

And you can modify and expand the last select to get the other data you need.

Former Member
0 Kudos

I Want to give 100 Points.

Ashish Gupte

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Ashish,

Can you please post the entire query. I am not sure where to add the art that you have chosen as the best answer!

Thanks.