MB51

-- ============================================================
-- T-Code : MB51 Material Document List
-- Purpose: Audit log of material documents over a date range, filtered by material / plant / movement type
-- Grain  : One row per material-document item (MKPF + MSEG)
-- Tables : MKPF, MSEG, MAKT, MARA, T001W, T001L
-- Notes  : SHKZG = 'H' (credit) movements are flipped negative below so outbound quantities appear as negatives. On S/4HANA, swap MKPF/MSEG for MATDOC for a ~10× speed-up on large scans.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.MBLNR   AS "Material Document",
  h.MJAHR   AS "Year",
  l.ZEILE   AS "Item",

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

  -- Quantities + UOM (paired, sign-flipped for credit movements)
  CASE WHEN l.SHKZG = 'H' THEN (l.MENGE * -1) ELSE l.MENGE END AS "Quantity (Signed)",
  l.MEINS   AS "Unit",
  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.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",

  -- Master data enrichment
  l.MATNR   AS "Material",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  l.EBELN   AS "Purchase Order",
  l.AUFNR   AS "Order Number",
  h.USNAM   AS "User"
FROM mkpf h
INNER JOIN mseg l
  ON  l.MANDT = h.MANDT
  AND l.MBLNR = h.MBLNR
  AND l.MJAHR = h.MJAHR
LEFT JOIN makt mt
  ON  mt.MANDT = l.MANDT
  AND mt.MATNR = l.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN mara m
  ON  m.MANDT = l.MANDT
  AND m.MATNR = l.MATNR
LEFT JOIN t001w pt
  ON  pt.MANDT = l.MANDT
  AND pt.WERKS = l.WERKS
LEFT JOIN t001l sl
  ON  sl.MANDT = l.MANDT
  AND sl.WERKS = l.WERKS
  AND sl.LGORT = l.LGORT
WHERE
  h.MANDT = '100'
  AND l.MATNR = '<MATNR>'
  AND h.BUDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND l.WERKS = '<WERKS>'
  -- AND l.BWART IN ('101','102','201','261')
ORDER BY h.BUDAT DESC, h.MBLNR ASC, l.ZEILE ASC;

-- S/4HANA NOTE: on S/4HANA, replace the MKPF/MSEG join above with a direct
-- query against MATDOC for significantly faster performance.