MB52

-- ============================================================
-- T-Code : MB52 List of Warehouse Stocks on Hand
-- Purpose: Current warehouse stocks across plant / storage location / batch / sales-order dimensions
-- Grain  : One row per material / plant / storage-location, enriched with batch + sales-order stock
-- Tables : MARD, MCHB, MSKA, MAKT, MARA, T001W, T001L
-- Notes  : On S/4HANA, MARD / MCHB / MSKA are served from MATDOC via CDS compatibility views — totals may shift mid-query since the three tables aren't read atomically. Reconcile against a period close if financials need to tie.
-- ============================================================
SELECT
  -- Keys
  m.MANDT   AS "Client",
  m.MATNR   AS "Material Number",
  m.WERKS   AS "Plant",
  m.LGORT   AS "Storage Location",

  -- Descriptive text
  mt.MAKTX  AS "Material Description",

  -- Quantities + UOM (paired)
  m.LABST   AS "Unrestricted Stock",
  m.INSME   AS "Quality Inspection Stock",
  m.SPEME   AS "Blocked Stock",
  m.EINME   AS "Restricted Stock",
  m.UMLME   AS "Stock In Transfer",
  ma.MEINS  AS "Base Unit of Measure",

  -- Status / indicators
  m.LVORM   AS "Deletion Flag",
  m.KZILL   AS "Unrestricted-Use Stock Ind.",

  -- Org fields
  pt.NAME1  AS "Plant Name",
  sl.LGOBE  AS "Storage Location Name",

  -- Master data enrichment
  ma.MTART  AS "Material Type",
  ma.MATKL  AS "Material Group",
  b.CHARG   AS "Batch",
  b.CLABS   AS "Batch Unrestricted",
  b.CINSM   AS "Batch Quality Inspection",
  b.CSPEM   AS "Batch Blocked",
  s.KDAUF   AS "Sales Order",
  s.KDPOS   AS "Sales Order Item",
  s.KALAB   AS "Sales Order Stock"
FROM mard m
LEFT JOIN mchb b
  ON  b.MANDT = m.MANDT
  AND b.MATNR = m.MATNR
  AND b.WERKS = m.WERKS
  AND b.LGORT = m.LGORT
LEFT JOIN mska s
  ON  s.MANDT = m.MANDT
  AND s.MATNR = m.MATNR
  AND s.WERKS = m.WERKS
  AND s.LGORT = m.LGORT
LEFT JOIN makt mt
  ON  mt.MANDT = m.MANDT
  AND mt.MATNR = m.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN mara ma
  ON  ma.MANDT = m.MANDT
  AND ma.MATNR = m.MATNR
LEFT JOIN t001w pt
  ON  pt.MANDT = m.MANDT
  AND pt.WERKS = m.WERKS
LEFT JOIN t001l sl
  ON  sl.MANDT = m.MANDT
  AND sl.WERKS = m.WERKS
  AND sl.LGORT = m.LGORT
WHERE
  m.MANDT = '100'
  AND m.WERKS = '<WERKS>'
  AND m.MATNR = '<MATNR>'
ORDER BY m.WERKS, m.MATNR, m.LGORT;