Query for calculating raw material requirements for the remaining quantities in sale order.

[VinoKR]

Dear SAP Experts,

Clients requirement :

                                     Client wish to know the quantities of raw materials needs to run the production order inorder to complete the remaining quantities in sale order.

Need Clarification:

                              I"m using the below query for this requirement. I wish to know whether this query suits for my clients requirement or not. If its so, I need to know how to group by T4.[Code] (Raw material Name)   and need to get the sum of    T4.[Quantity]  (BOM quantity)      and  (T1.[OpenQty]*T4.[Quantity]) as TOTALQTY under each raw material group

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode],T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity] as salesorderQty , T1.[OpenQty], T2.onhand, T4.[Code] as Raw material Name,T4.[Quantity] as BOMQTY,  (T1.[OpenQty]*T4.[Quantity]) as TOTALQTY FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OITT T3 ON T2.[ItemCode] = T3.[Code] INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father] WHERE  T0.[DocStatus] ='o'

[VinoKR]

Dear SAP & SQL Experts,

When running this query

SELECT distinct T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY',  SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITT T3 ON T3.[Code] = T1.[ItemCode]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

WHERE  T1.[LineStatus] ='o'

GROUP BY T4.[Code], T2.ItemName

The result was as expected and i have created a sample of it and attached below:

Now I need to get the instock of this item from a specific warehouse.

So, I've used the below query

SELECT distinct T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY',  SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY', t5.OnHand

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITT T3 ON T3.[Code] = T1.[ItemCode]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

Inner join oitw t5 on t5.Itemcode= T2.[ItemCode]

WHERE  T1.[LineStatus] ='o'

GROUP BY T4.[Code], T2.ItemName, t5.OnHand

However, all of the row values are repeated and the result of this query seems like duplicated value.

I don't know what to do with this. To avoid the duplication and repeated values, I've used the distinct, even the result doesn't change.

I need ur help experts...

[kothandaraman_nagarajan]

Try this:

SELECT distinct T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY',  SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY', t2.OnHand

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITT T3 ON T3.[Code] = T1.[ItemCode]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

Inner join oitw t5 on t5.Itemcode= T2.[ItemCode]

WHERE  T1.[LineStatus] ='o'

GROUP BY T4.[Code], T2.ItemName, t2.OnHand

[Former Member]

If you join OITW in, you will get multiple rows because onhand is for each warehouse.

You may try:

SELECT T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY',  SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY', T2.Onhand

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITT T3 ON T3.[ItemCode] = T1.[Code]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

WHERE  T1.[LineStatus] ='o'

GROUP BY T4.[Code], T2.ItemName, T2.Onhand

[Former Member]

Hi,

Your query can meet their need if all their orders tied to BOM without change.

Try:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode],T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity] as salesorderQty ,

T1.[OpenQty], T2.onhand, T4.[Code] as 'Raw material Name',SUM(T4.[Quantity]) as 'BOMQTY',  SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OITT T3 ON T2.[ItemCode] = T3.[Code]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

WHERE  T1.[LineStatus] ='o'

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode],T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity] ,

T1.[OpenQty], T2.onhand, T4.[Code]

Thanks,

Gordon

[VinoKR]

Thanks a lot Mr.      

However, I need to get the description of T4.[Code] and this report needs to be grouped by that description(T4.[Code]'s description)

Thanks & Regards,

Vino

9094792472

[Former Member]

Do you mean this:

SELECT T4.[Code] as 'Raw material Name',SUM(T4.[Quantity]) as 'BOMQTY',  SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OITT T3 ON T2.[ItemCode] = T3.[Code]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

WHERE  T1.[LineStatus] ='o'

GROUP BY T4.[Code]

[VinoKR]

exactly. But I need that description of T4.[Code]. I've tried to get that description. HOwevever I got the description of finished goods rather than raw material. so again. I've ended up with no result because of joins between oitm and itt1.

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode],T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity] as salesorderQty ,

T1.[OpenQty], T2.onhand, T4.[Code] as 'Raw material Name',T5.Itemname, SUM(T4.[Quantity]) as 'BOMQTY',  SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OITT T3 ON T2.[ItemCode] = T3.[Code]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father] inner join OITM  T5 on T5.itemcode = T4.[Code]

WHERE  T1.[LineStatus] ='o'

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode],T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity] ,

T1.[OpenQty], T2.onhand, T4.[Code],T5.Itemname

[Former Member]

Try:

SELECT T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY',  SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITT T3 ON T3.[ItemCode] = T1.[Code]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

WHERE  T1.[LineStatus] ='o'

GROUP BY T4.[Code], T2.ItemName

[VinoKR]

Dear experts,

                   Need query for this. Can anyone do this.

[kothandaraman_nagarajan]

Hi,

The answer is NO. You have to link sales and production order to get exact raw material requirement not from BOM.

Thanks & Regards,

Nagarajan

[VinoKR]

Thanks a lot for letting me know this. As I'm fresher, I need your precious guidance in this regard.

After ur advice, I've tried to link ordr, rdr1, owor and wor1 tables. However, I've ended with no result.

If you don't mine, please let me know which join, i need to use to link sale and production.

Query for calculating raw material requirements for the remaining quantities in sale order.