MB31

-- ============================================================
-- T-Code : MB31 Goods Receipt for Production Order
-- Purpose: Post goods receipt from a production order (movement types 101/131)
-- Grain  : One row per material document item (MKPF + MSEG)
-- Tables : MKPF, MSEG, MAKT, MARA, T001W, T001L, LFA1
-- Notes  : SHKZG = 'H' (credit) movements flip the quantity negative on paired reports like MB51. On S/4HANA, read MATDOC directly instead of MKPF+MSEG for better S/4HANA-native performance and semantics; benchmark in your system.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.MBLNR   AS "Material Document",
  h.MJAHR   AS "Material Doc Year",
  l.ZEILE   AS "Item",

  -- Descriptive text
  mt.MAKTX  AS "Material Description",
  h.BKTXT   AS "Header Text",

  -- Quantities + UOM (paired)
  CASE WHEN l.SHKZG = 'H' THEN (l.MENGE * -1) ELSE l.MENGE END AS "Quantity (Signed)",
  l.MEINS   AS "Unit of Measure",
  l.DMBTR   AS "Amount in Local Currency",
  l.WAERS   AS "Currency",

  -- Dates
  h.BUDAT   AS "Posting Date",
  h.BLDAT   AS "Document Date",
  h.CPUDT   AS "Entered On",

  -- Status / indicators
  l.BWART   AS "Movement Type",
  l.SHKZG   AS "Debit/Credit Indicator",
  l.XAUTO   AS "Auto-Created Flag",
  l.SMBLN   AS "Reversal Document",

  -- Org fields
  l.WERKS   AS "Plant",
  pt.NAME1  AS "Plant Name",
  l.LGORT   AS "Storage Location",
  sl.LGOBE  AS "Storage Location Name",
  l.BUKRS   AS "Company Code",

  -- Master data enrichment
  l.MATNR   AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  l.CHARG   AS "Batch",
  l.EBELN   AS "Purchase Order",
  l.EBELP   AS "PO Item",
  l.AUFNR   AS "Order Number",
  l.KDAUF   AS "Sales Order",
  l.LIFNR   AS "Vendor",
  vnd.NAME1 AS "Vendor Name",
  h.USNAM   AS "User Name"
FROM <catalog>.<schema>.mkpf h
INNER JOIN <catalog>.<schema>.mseg l
  ON  l.MANDT = h.MANDT
  AND l.MBLNR = h.MBLNR
  AND l.MJAHR = h.MJAHR
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = l.MANDT
  AND mt.MATNR = l.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara m
  ON  m.MANDT = l.MANDT
  AND m.MATNR = l.MATNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = l.MANDT
  AND pt.WERKS = l.WERKS
LEFT JOIN <catalog>.<schema>.t001l sl
  ON  sl.MANDT = l.MANDT
  AND sl.WERKS = l.WERKS
  AND sl.LGORT = l.LGORT
LEFT JOIN <catalog>.<schema>.lfa1 vnd
  ON  vnd.MANDT = l.MANDT
  AND vnd.LIFNR = l.LIFNR
WHERE
  h.MANDT = '<MANDT>'
  AND h.BUDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  AND l.BWART IN ('101','131')
  -- AND l.WERKS = '<WERKS>'
  -- AND l.MATNR = '<MATNR>'
ORDER BY h.BUDAT DESC, h.MBLNR, l.ZEILE;