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.

[kothandaraman_nagarajan]

Hi,

The query which is prosed by Mr. Gordon will suit all warehouse regardless of warehouse code.

Since the  OITM table consists of stock quantity from all warehouses.

Hope you understand.

Thanks & Regards,

Nagarajan

[former_member591057]

Hi ,

Need little bit more help. I want to calcuate this operation based on the two warehouse quantity available warehouse '01' and warehouse '02' though I have two more warehouse '03' and '04' then what will be the proper query.

Thanks and Regards

Utpal maity

[kothandaraman_nagarajan]

Hi,

Before that, please conform, you have tested query that posted by members.

Thanks & Regards,

Nagarajan

[Former Member]

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

     Where T1.WhsCode IN ('01', '02')

) T1 ON T0.Code = T1.Father

Where T0.TreeType = 'P'

Group By  T0.Code

[KennedyT21]

Hi Utpal Maity

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

From OITT T0

INNER JOIN ( select Father, Code, Quantity, OnHand, floor (t1.OnHand / T0.Quantity)  Prod from ITT1 T0

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

) T1 ON T0.Code = T1.Father

Where T0.Code='[%0]'  and T0.TreeType = 'P'

Group By  T0.Code

Regards

Kennedy

[Former Member]

Hi Utpal,

Try:

SELECT T0.Father, Min(t1.onhand/t0.quantity) 'Can Make'

FROM ITT1 T0

JOIN OITM T1 ON T1.ItemCode=T0.Code

WHERE T0.Father= '[%0]'

Group By T0.Father

Thanks,

Gordon

[kothandaraman_nagarajan]

Hi,

It depends upon the planned quantity of production order.

You can produce maximum of 30. Since stock for item A is only 30. System will not allow to release more than stock quantity.

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

No, the question is a sales question: from the recipe, how many theorical BOM am I able to produce with my existing stock, independly of the definition of all my current processes.

Following query should be good:

SELECT

V0.Father, MIN (V0.CanProduce) CanProduce

FROM

(

SELECT T1.Father, T1.Code, T1.Quantity, I0.OnHand, CAST (I0.OnHand / T1.Quantity AS int) CanProduce

FROM ITT1 T1

INNER JOIN OITM I0 ON T1.Code = I0.ItemCode

WHERE T1.Father = 'ABC'

)

V0

GROUP BY V0.Father

Regards,

Eric