Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 29, 2014 at 01:04 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 30, 2014 at 01:31 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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'

      1.jpg (20.0 kB)