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; batch and sales-order stock are pre-aggregated to that same grain
-- 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. MCHB is keyed at batch grain and MSKA at sales-order-item grain — joining either directly on material/plant/storage-location alone fans out to a batch-by-sales-order cross product and duplicates the MARD quantities, so both are pre-summed to material/plant/storage-location before joining; per-batch and per-sales-order detail is not shown here (see MMBE for that breakdown).
-- ============================================================
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.CLABS   AS "Batch Unrestricted (Aggregated)",
  b.CINSM   AS "Batch Quality Inspection (Aggregated)",
  b.CSPEM   AS "Batch Blocked (Aggregated)",
  s.KALAB   AS "Sales Order Stock (Aggregated)"
FROM <catalog>.<schema>.mard m
LEFT JOIN (
  SELECT
    MANDT, MATNR, WERKS, LGORT,
    SUM(CLABS) AS CLABS,
    SUM(CINSM) AS CINSM,
    SUM(CSPEM) AS CSPEM
  FROM <catalog>.<schema>.mchb
  GROUP BY MANDT, MATNR, WERKS, LGORT
) b
  ON  b.MANDT = m.MANDT
  AND b.MATNR = m.MATNR
  AND b.WERKS = m.WERKS
  AND b.LGORT = m.LGORT
LEFT JOIN (
  SELECT
    MANDT, MATNR, WERKS, LGORT,
    SUM(KALAB) AS KALAB
  FROM <catalog>.<schema>.mska
  GROUP BY MANDT, MATNR, WERKS, LGORT
) s
  ON  s.MANDT = m.MANDT
  AND s.MATNR = m.MATNR
  AND s.WERKS = m.WERKS
  AND s.LGORT = m.LGORT
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = m.MANDT
  AND mt.MATNR = m.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara ma
  ON  ma.MANDT = m.MANDT
  AND ma.MATNR = m.MATNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = m.MANDT
  AND pt.WERKS = m.WERKS
LEFT JOIN <catalog>.<schema>.t001l sl
  ON  sl.MANDT = m.MANDT
  AND sl.WERKS = m.WERKS
  AND sl.LGORT = m.LGORT
WHERE
  m.MANDT = '<MANDT>'
  AND m.WERKS = '<WERKS>'
  AND m.MATNR = '<MATNR>'
ORDER BY m.WERKS, m.MATNR, m.LGORT;