Query to display result based on specific item group of the parent item in production order instead of parent item code itself

[Former Member]

Hi,

Can anyone help with the query so that user can filter based on the item group rather than list of parent item code in the production order field of OWOR.ItemCode.

For eg, if the query written as OWOR.ItemCode, it will display the list of item code, it is possible to show the item group of the items ?

Thanks

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[ItemCode], T1.[ItemName],  T2.[ItmsGrpNam] FROM OWOR T0 inner join  OITM T1 on  T0.[ItemCode]  =  T1.[ItemCode] INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod GROUP BY T0.[ItemCode], T1.[ItemName],  T2.[ItmsGrpNam]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi,

Thank you for the replied and sorry for the unclear explanation.

Let me clarify again, below is the query to generate goods receipt if the items is a semi product group. However the goal of this query is to generate the goods receipt only if the parent item of the production order is in semi product item group, can this be done?

Thanks

SELECT T0.[DocNum], T0.[DocDate], T3.[ItmsGrpNam], T2.[ItemCode], T1.[Price], T2.[AvgPrice], T1.[Quantity], T1.[LineTotal] FROM OIGN T0  INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod WHERE T2.[ItmsGrpCod] = 'semiproduct'

[kothandaraman_nagarajan · Accepted solution]

Not getting exact requirement. Your query is related to Receipt from production. But attached picture is from production order.

Please explain with simple example.

[Former Member · Accepted solution]

Hi,

I want to display the receipt from production of the item if the item in production order is semi product.

Eg, A is semi product, B is full product, both A and B appear in the "product no" field in a production order and the production order had completed. The query will display receipt from production of A only as it is semi product.

Is this example ok?

Thanks

[kothandaraman_nagarajan · Accepted solution]

Try:

SELECT T0.[DocNum], T0.[DocDate], T3.[ItmsGrpNam], T2.[ItemCode], T1.[Price], T2.[AvgPrice], T1.[Quantity], T1.[LineTotal] FROM OIGN T0  INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod LEFT JOIN  OWOR T4 ON  T1.[BaseRef] =  T4.[DocNum]  AND  T4.[ItemCode]  =  T2.[ItemCode] WHERE T3.[ItmsGrpNam]  = 'SEMIPRODUCT'

[Former Member · Accepted solution]

Hi Naga,

Thank you for the replied.

However, when I tried to add the production order type and issue type, the result will be wrong.

For example, for disassembly, it will leave the type field as empty, for item issue type, some will show as backflush but some will show as manual, however the actual result is manual for all records.

Below is the query:

SELECT DISTINCT Case When T4.[Type] = 'S' Then 'Standard' When T4.[Type] = 'P' Then 'Special' When T4.[Type] = 'D' Then 'Disassembly' End 'Type', T0.[DocNum], T0.[DocDate], T3.[ItmsGrpNam], T2.[ItemCode], T5.[IssueType], T1.[Price], T2.[AvgPrice], T1.[Quantity], T1.[LineTotal] FROM OIGN T0 INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod LEFT JOIN OWOR T4 ON T1.[BaseRef] = T4.[DocNum] AND T4.[ItemCode] = T2.[ItemCode] INNER JOIN WOR1 T5 ON T1.ItemCode = T5. ItemCode WHERE T3.[ItmsGrpNam]  = 'SEMIPRODUCT'

[kothandaraman_nagarajan · Accepted solution]

Try:

SELECT DISTINCT Case When T4.[Type] = 'S' Then 'Standard' When T4.[Type] = 'P' Then 'Special' When T4.[Type] = 'D' Then 'Disassembly' End 'Type', T0.[DocNum], T0.[DocDate], T3.[ItmsGrpNam], T2.[ItemCode], T5.[IssueType], T1.[Price], T2.[AvgPrice], T1.[Quantity], T1.[LineTotal] FROM OIGN T0 INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod LEFT JOIN OWOR T4 ON T1.[BaseRef] = T4.[DocNum] AND T4.[ItemCode] = T2.[ItemCode] inner JOIN WOR1 T5 ON T4.Docentry = T5.Docentry WHERE T3.[ItmsGrpNam]  =  'SEMIPRODUCT'

[Former Member · Accepted solution]

Hi,

Thank you for the prompt replied. It work !

But on the issue type field, there will be two result for the same production order, one is Backflush, another is Manual. I had checked few records with the same results, and found out the similarity, the duplicate production records will have more than 2 goods issue or goods receipt. Because of this, it will show 2 lines in the query.

Can query filter this ?

Thanks

[kothandaraman_nagarajan · Accepted solution]

I am not getting such result in my DB. For better understanding, share your query result with two lines.

[Former Member · Accepted solution]

ok, refer below:

Thank you

[kothandaraman_nagarajan · Accepted solution]

Why you need issue type? To avoid repeated lines, remove issue type from the query.

[Former Member]

Hi,

Is this what you want:

SELECT T2.[ItmsGrpNam],SUM(T0.PlannedQty) 'Planned',SUM(T0.CmpltQty) 'Completed'

FROM OWOR T0

join  OITM T1 on T0.[ItemCode] = T1.[ItemCode]

JOIN OITB T2 ON T1.ItmsGrpCod =T2.ItmsGrpCod

WHERE T0.Status='L'

GROUP BY T2.[ItmsGrpNam]

Thanks,

Gordon

[Former Member]

Hi,

Thank you for the replied and sorry for the unclear explanation.

Let me clarify again, below is the query to generate goods receipt if the items is a semi product group. However the goal of this query is to generate the goods receipt only if the related parent item of the production order is in semi product item group, can this be done?

Thanks

SELECT T0.[DocNum], T0.[DocDate], T3.[ItmsGrpNam], T2.[ItemCode], T1.[Price], T2.[AvgPrice], T1.[Quantity], T1.[LineTotal] FROM OIGN T0  INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod WHERE T2.[ItmsGrpCod] = 'semiproduct'