MMBE

-- ============================================================
-- T-Code : MMBE Stock Overview
-- Purpose: Aggregated stock overview across plant, storage location, batch, and sales-order stock
-- Grain  : One row per (material, plant, storage-location, dimension) — unioned across stock dimensions
-- Tables : MARD, MCHB, MSKA, MAKT, MARA, T001W
-- Notes  : MMBE aggregates stock across multiple dimensions on-screen. This query unions MARD (plant/sloc), MCHB (batch), and MSKA (sales-order) so one result set shows every bucket.
-- ============================================================
WITH plant_sloc AS (
  SELECT
    m.MANDT, m.MATNR, m.WERKS, m.LGORT,
    'Plant/SLoc'  AS "Dimension",
    ''            AS "Batch",
    ''            AS "Sales Order",
    0             AS "Sales Order Item",
    m.LABST       AS "Unrestricted",
    m.INSME       AS "Quality Inspection",
    m.SPEME       AS "Blocked"
  FROM <catalog>.<schema>.mard m
  WHERE m.MANDT = '<MANDT>' AND m.MATNR = '<MATNR>' AND m.WERKS = '<WERKS>'
),
batch AS (
  SELECT
    b.MANDT, b.MATNR, b.WERKS, b.LGORT,
    'Batch'       AS "Dimension",
    b.CHARG       AS "Batch",
    ''            AS "Sales Order",
    0             AS "Sales Order Item",
    b.CLABS       AS "Unrestricted",
    b.CINSM       AS "Quality Inspection",
    b.CSPEM       AS "Blocked"
  FROM <catalog>.<schema>.mchb b
  WHERE b.MANDT = '<MANDT>' AND b.MATNR = '<MATNR>' AND b.WERKS = '<WERKS>'
),
sales_order AS (
  SELECT
    s.MANDT, s.MATNR, s.WERKS, s.LGORT,
    'Sales Order' AS "Dimension",
    ''            AS "Batch",
    s.VBELN       AS "Sales Order",
    s.POSNR       AS "Sales Order Item",
    s.KALAB       AS "Unrestricted",
    s.KAINS       AS "Quality Inspection",
    s.KASPE       AS "Blocked"
  FROM <catalog>.<schema>.mska s
  WHERE s.MANDT = '<MANDT>' AND s.MATNR = '<MATNR>' AND s.WERKS = '<WERKS>'
)
SELECT
  -- Keys
  u.MANDT   AS "Client",
  u.MATNR   AS "Material",
  u.WERKS   AS "Plant",
  u.LGORT   AS "Storage Location",
  u."Dimension",

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

  -- Quantities + UOM
  u."Unrestricted",
  u."Quality Inspection",
  u."Blocked",
  ma.MEINS  AS "Base Unit of Measure",

  -- Org fields
  pt.NAME1  AS "Plant Name",

  -- Master data enrichment
  u."Batch",
  u."Sales Order",
  u."Sales Order Item",
  ma.MTART  AS "Material Type",
  ma.MATKL  AS "Material Group"
FROM (SELECT * FROM plant_sloc
      UNION ALL SELECT * FROM batch
      UNION ALL SELECT * FROM sales_order) u
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = u.MANDT
  AND mt.MATNR = u.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara ma
  ON  ma.MANDT = u.MANDT
  AND ma.MATNR = u.MATNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = u.MANDT
  AND pt.WERKS = u.WERKS
ORDER BY u."Dimension", u.LGORT, u."Batch", u."Sales Order";