ME2M

-- ============================================================
-- T-Code : ME2M Purchase Orders by Material
-- Purpose: List purchase orders filtered by material
-- Grain  : One row per PO item with delivery schedule line
-- Tables : EKKO, EKPO, EKET, MAKT, MARA, T001W, T001L, LFA1
-- Notes  : EKKO (header) → EKPO (item) → EKET (schedule lines). Filter LOEKZ <> 'L' to exclude deletion-flagged items. Open quantity = EKET.MENGE - EKET.WEMNG at schedule-line grain.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.EBELN   AS "Purchase Order",
  l.EBELP   AS "PO Item",
  s.ETENR   AS "Schedule Line",

  -- Descriptive text
  l.TXZ01   AS "Short Text",
  mt.MAKTX  AS "Material Description",

  -- Quantities + UOM (paired)
  l.MENGE   AS "PO Quantity",
  l.MEINS   AS "Base Unit of Measure",
  l.BPRME   AS "Order Price Unit",
  l.BPUMZ   AS "Numerator (Unit Conv)",
  l.BPUMN   AS "Denominator (Unit Conv)",
  l.NETPR   AS "Net Price",
  l.PEINH   AS "Price Unit",
  h.WAERS   AS "Document Currency",
  (s.MENGE - s.WEMNG) AS "Open Quantity",

  -- Dates
  h.BEDAT   AS "PO Date",
  s.EINDT   AS "Delivery Date",
  h.AEDAT   AS "Last Changed On",

  -- Status / indicators
  l.LOEKZ   AS "Deletion Indicator",
  l.ELIKZ   AS "Delivery Completed",
  l.EREKZ   AS "Final Invoice",
  h.FRGKE   AS "Release Status",

  -- Org fields
  l.WERKS   AS "Plant",
  pt.NAME1  AS "Plant Name",
  l.LGORT   AS "Storage Location",
  sl.LGOBE  AS "Storage Location Name",
  h.EKORG   AS "Purchasing Organization",
  h.EKGRP   AS "Purchasing Group",
  h.BUKRS   AS "Company Code",

  -- Master data enrichment
  h.BSART   AS "Document Type",
  l.MATNR   AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  h.LIFNR   AS "Vendor",
  vnd.NAME1 AS "Vendor Name",
  h.ERNAM   AS "Created By"
FROM <catalog>.<schema>.ekko h
INNER JOIN <catalog>.<schema>.ekpo l
  ON  l.MANDT = h.MANDT
  AND l.EBELN = h.EBELN
LEFT JOIN <catalog>.<schema>.eket s
  ON  s.MANDT = l.MANDT
  AND s.EBELN = l.EBELN
  AND s.EBELP = l.EBELP
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = l.MANDT
  AND mt.MATNR = l.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara m
  ON  m.MANDT = l.MANDT
  AND m.MATNR = l.MATNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = l.MANDT
  AND pt.WERKS = l.WERKS
LEFT JOIN <catalog>.<schema>.t001l sl
  ON  sl.MANDT = l.MANDT
  AND sl.WERKS = l.WERKS
  AND sl.LGORT = l.LGORT
LEFT JOIN <catalog>.<schema>.lfa1 vnd
  ON  vnd.MANDT = h.MANDT
  AND vnd.LIFNR = h.LIFNR
WHERE
  h.MANDT = '<MANDT>'
  AND h.BEDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  AND l.LOEKZ <> 'L'
  -- AND h.LIFNR = '<LIFNR>'
  -- AND l.WERKS = '<WERKS>'
ORDER BY h.BEDAT DESC, h.EBELN, l.EBELP, s.ETENR;