Skip to Content
Author's profile photo Nagarajan K

Production order – Issued and Received quantity with batch number

Dear all,

The below query is useful for production planner who want know the issued and/or received quantity for particular project  from production order.

Query:

SELECT

T0.[PostDate], T0.[DocNum], T0.[PlannedQty],case when (T3.[BaseType] = ’59’ and  T3.[Direction] = ‘0’ and T0.[ItemCode] = T3.[ItemCode] ) then  ‘Received’ end as Direction,T0.[ItemCode], T3.[Quantity], T3.[BatchNum], case when (T2.[BaseType] = ’60’ and T2.[Direction] = ‘1’ and T1.[ItemCode] = T2.[ItemCode] ) then ‘issued’ end Direction, T2.[Quantity], T2.[ItemCode], T2.[BatchNum]

FROM

OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry left  join IBT1 T2 on T2.[BsDocEntry]  = t0.docentry and T2.[BsDocLine]  =  T1.[LineNum] left  join IBT1 T3 on T3.[BsDocEntry]  = t0.docentry and T0.[ItemCode] = T3.[ItemCode]

WHERE

T2.[BsDocType]  = ‘202’ and  (T0.[Status] = ‘R’ OR T0.[Status] = ‘L’ )  and  T0.[Project]  = [%0]

Hope it will be helpful.

Thanks & Regards,

Nagarajan

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michael White
      Michael White

      Thank you! Great report. Here is the HANA equivalent. Cheers

       

      SELECT
      T0."PostDate",
      T0."DocNum",
      T0."PlannedQty",
      CASE
      WHEN (T3."BaseType" = '59' AND T3."Direction" = '0' AND T0."ItemCode" = T3."ItemCode") THEN 'Received'
      END "Direction",
      T0."ItemCode",
      T3."Quantity",
      T3."BatchNum",
      CASE
      WHEN (T2."BaseType" = '60' AND T2."Direction" = '1' AND T1."ItemCode" = T2."ItemCode") THEN 'issued'
      END "Direction",
      T2."Quantity",
      T2."ItemCode",
      T2."BatchNum"
      FROM "OWOR" T0
      INNER JOIN WOR1 T1 ON T0."DocEntry" = T1."DocEntry"
      LEFT OUTER JOIN IBT1 T2 ON T2."BsDocEntry" = T0."DocEntry" AND T2."BsDocLine" = T1."LineNum"
      LEFT OUTER JOIN IBT1 T3 ON T3."BsDocEntry" = T0."DocEntry" AND T0."ItemCode" = T3."ItemCode" WHERE T2."BsDocType" = '202' AND (T0."Status" = 'R' OR T0."Status" = 'L') AND T0."Project" = [%0]