cancel
Showing results for 
Search instead for 
Did you mean: 

Getting data from SAP b1 database based on BOM .

former_member591057
Participant
0 Kudos
398

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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.

Answers (5)

Answers (5)

kothandaraman_nagarajan
Active Contributor
0 Kudos

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
Participant
0 Kudos

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
Active Contributor
0 Kudos

Hi,

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

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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