Getting data from SAP b1 database based on BOM .

[former_member591057]

Hi All,

I have a mother item ABC and i have defined BOM for this mother item with the child items A,B,C. I have defined the following quantity in the bill of materials for the child items as mentioned below .

For Product A : 1

For Product B : 2

For Product C : 1

and in the child items have below stocks in the item master

For Product A : 30

For Product B :70

For Product C : 34

Now I want to find how many mother items 'ABC '  I can made based  on the BOM items defined by using SQL query.

Thanks and Regards

Utpal Maity

[Former Member · Accepted solution]

Try this to get a list of all BOMs and available for production qtys:

Select T0.Code, Min (Floor (AvailForProd / T0.Qauntity)) AvailForProd

From OITT T0 INNER JOIN (

     select Father, Code, Quantity, OnHand, floor (t1.OnHand / T0.Quantity) AvailForProd 

     from ITT1 T0 INNER JOIN OITW T1 ON T0.Code = T1.ItemCode

) T1 ON T0.Code = T1.Father

Where T0.TreeType = 'P'

Group By  T0.Code

Beni.