MD04

-- ============================================================
-- T-Code : MD04 Display Stock/Requirements List
-- Purpose: Netted live view of current stock vs. open supply and demand by material/plant
-- Grain  : One row per MRP element per material/plant (stock, supply, demand)
-- Tables : MARD, MCHB, MSKA, VBEP, VBAP, LIPS, EBAN, EKET, AFPO, PLAF, RESB
-- Notes  : MD04 is a *live* SAP calculation. The authoritative way to reproduce
--          it outside the GUI is the BAPI MD_STOCK_REQUIREMENTS_LIST_API.
--          This query unions the same source tables and will drift vs. the
--          screen because ABAP netting / ATP / firming logic is not reproduced.
-- ============================================================
WITH stock AS (
  SELECT
    MANDT                 AS client,
    MATNR                 AS material_number,
    WERKS                 AS plant,
    'Stock: Unrestricted' AS mrp_element,
    LABST                 AS quantity,
    CAST(NULL AS DATE)    AS element_date
  FROM <catalog>.<schema>.mard
  WHERE MANDT = '<MANDT>'

  UNION ALL

  SELECT
    MANDT                 AS client,
    MATNR                 AS material_number,
    WERKS                 AS plant,
    'Stock: Batch'        AS mrp_element,
    CLABS                 AS quantity,
    NULL                  AS element_date
  FROM <catalog>.<schema>.mchb
  WHERE MANDT = '<MANDT>'

  UNION ALL

  SELECT
    MANDT                 AS client,
    MATNR                 AS material_number,
    WERKS                 AS plant,
    'Stock: Sales Order'  AS mrp_element,
    KALAB                 AS quantity,
    NULL                  AS element_date
  FROM <catalog>.<schema>.mska
  WHERE MANDT = '<MANDT>'
),
supply AS (
  SELECT
    MANDT                          AS client,
    MATNR                          AS material_number,
    WERKS                          AS plant,
    'Supply: PO Schedule Line'     AS mrp_element,
    (MENGE - WEMNG)                AS quantity,
    EINDT                          AS element_date
  FROM <catalog>.<schema>.eket
  WHERE MANDT = '<MANDT>'
    AND (MENGE - WEMNG) > 0

  UNION ALL

  SELECT
    MANDT                          AS client,
    MATNR                          AS material_number,
    WERKS                          AS plant,
    'Supply: Purchase Requisition' AS mrp_element,
    MENGE                          AS quantity,
    LFDAT                          AS element_date
  FROM <catalog>.<schema>.eban
  WHERE MANDT = '<MANDT>'
    AND LOEKZ = ''
    AND EBELN = ''

  UNION ALL

  SELECT
    MANDT                          AS client,
    MATNR                          AS material_number,
    DWERK                          AS plant,
    'Supply: Production Order'     AS mrp_element,
    (PSMNG - WEMNG)                AS quantity,
    LTRMI                          AS element_date
  FROM <catalog>.<schema>.afpo
  WHERE MANDT = '<MANDT>'
    AND (PSMNG - WEMNG) > 0

  UNION ALL

  SELECT
    MANDT                          AS client,
    MATNR                          AS material_number,
    PWWRK                          AS plant,
    'Supply: Planned Order'        AS mrp_element,
    GSMNG                          AS quantity,
    PEDTR                          AS element_date
  FROM <catalog>.<schema>.plaf
  WHERE MANDT = '<MANDT>'
),
demand AS (
  SELECT
    v.MANDT                       AS client,
    p.MATNR                       AS material_number,
    p.WERKS                       AS plant,
    'Demand: Sales Schedule Line' AS mrp_element,
    v.WMENG                       AS quantity,
    v.EDATU                       AS element_date
  FROM <catalog>.<schema>.vbep v
  INNER JOIN <catalog>.<schema>.vbap p
    ON  p.MANDT = v.MANDT
    AND p.VBELN = v.VBELN
    AND p.POSNR = v.POSNR
  WHERE v.MANDT = '<MANDT>'
    AND v.WMENG > 0

  UNION ALL

  SELECT
    MANDT                         AS client,
    MATNR                         AS material_number,
    WERKS                         AS plant,
    'Demand: Delivery'            AS mrp_element,
    LFIMG                         AS quantity,
    ERDAT                         AS element_date
  FROM <catalog>.<schema>.lips
  WHERE MANDT = '<MANDT>'
    AND LFIMG > 0

  UNION ALL

  SELECT
    MANDT                         AS client,
    MATNR                         AS material_number,
    WERKS                         AS plant,
    'Demand: Reservation'         AS mrp_element,
    BDMNG                         AS quantity,
    BDTER                         AS element_date
  FROM <catalog>.<schema>.resb
  WHERE MANDT = '<MANDT>'
    AND KZEAR = ''
    AND XLOEK = ''
)
SELECT
  mrp.client       AS "Client",
  mrp.material_number AS "Material Number",
  mt.MAKTX         AS "Material Description",
  mrp.plant        AS "Plant",
  pt.NAME1         AS "Plant Name",
  mrp.mrp_element  AS "MRP Element",
  mrp.element_date AS "Date",
  mrp.quantity     AS "Quantity"
FROM (
  SELECT * FROM stock
  UNION ALL SELECT * FROM supply
  UNION ALL SELECT * FROM demand
) mrp
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = mrp.client
  AND mt.MATNR = mrp.material_number
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = mrp.client
  AND pt.WERKS = mrp.plant
WHERE mrp.material_number = '<MATNR>'
  AND mrp.plant = '<WERKS>'
ORDER BY mrp.element_date NULLS FIRST, mrp.mrp_element;