MM01

-- ============================================================
-- T-Code : MM01 Create Material
-- Purpose: Create material-master record across client, plant, and storage-location views
-- Grain  : One row per material / plant / storage-location combination
-- Tables : MARA, MAKT, MARC, MARD, MBEW, T001W, T001L, T134T, T023T
-- Notes  : MARA is client-level; MARC adds plant; MARD adds storage location. MAKT carries descriptions by language — filter SPRAS = 'E'.
-- ============================================================
SELECT
  -- Keys
  m.MANDT   AS "Client",
  m.MATNR   AS "Material Number",
  mc.WERKS  AS "Plant",
  md.LGORT  AS "Storage Location",

  -- Descriptive text
  mt.MAKTX  AS "Material Description",
  t134t.MTBEZ AS "Material Type Description",
  t023t.WGBEZ AS "Material Group Description",

  -- Quantities + UOM (paired)
  m.BRGEW   AS "Gross Weight",
  m.GEWEI   AS "Weight Unit",
  m.NTGEW   AS "Net Weight",
  md.LABST  AS "Unrestricted Stock",
  m.MEINS   AS "Base Unit of Measure",

  -- Dates
  m.ERSDA   AS "Created On",
  m.LAEDA   AS "Changed On",

  -- Status / indicators
  m.LVORM   AS "Deletion Flag (Client)",
  mc.MMSTA  AS "Plant Status",
  md.LVORM  AS "Deletion Flag (Storage Loc)",

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

  -- Master data enrichment
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  mc.DISMM  AS "MRP Type",
  mc.DISPO  AS "MRP Controller",
  mc.EKGRP  AS "Purchasing Group",
  mb.STPRS  AS "Standard Price",
  mb.VERPR  AS "Moving Average Price",
  mb.PEINH  AS "Price Unit",
  mb.WAERS  AS "Currency"
FROM mara m
LEFT JOIN makt mt
  ON  mt.MANDT = m.MANDT
  AND mt.MATNR = m.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN marc mc
  ON  mc.MANDT = m.MANDT
  AND mc.MATNR = m.MATNR
LEFT JOIN mard md
  ON  md.MANDT = mc.MANDT
  AND md.MATNR = mc.MATNR
  AND md.WERKS = mc.WERKS
LEFT JOIN mbew mb
  ON  mb.MANDT = m.MANDT
  AND mb.MATNR = m.MATNR
  AND mb.BWKEY = mc.WERKS
LEFT JOIN t001w pt
  ON  pt.MANDT = mc.MANDT
  AND pt.WERKS = mc.WERKS
LEFT JOIN t001l sl
  ON  sl.MANDT = md.MANDT
  AND sl.WERKS = md.WERKS
  AND sl.LGORT = md.LGORT
LEFT JOIN t134t t134t
  ON  t134t.MANDT = m.MANDT
  AND t134t.MTART = m.MTART
  AND t134t.SPRAS = 'E'
LEFT JOIN t023t t023t
  ON  t023t.MANDT = m.MANDT
  AND t023t.MATKL = m.MATKL
  AND t023t.SPRAS = 'E'
WHERE
  m.MANDT = '100'
  AND m.MATNR = '<MATNR>'
  -- AND mc.WERKS = '<WERKS>'
  -- AND m.LVORM <> 'X'
ORDER BY m.MATNR, mc.WERKS, md.LGORT;