ME51N

-- ============================================================
-- T-Code : ME51N Create Purchase Requisition
-- Purpose: Create a purchase requisition with account-assignment lines
-- Grain  : One row per purchase requisition item
-- Tables : EBAN, EBKN, MAKT, MARA, T001W, T001L
-- Notes  : EBKN is optional — only populated for account-assigned items. Filter LOEKZ = '' to exclude deleted items; EBELN <> '' means PR has been converted to PO.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.BANFN   AS "Purchase Requisition",
  h.BNFPO   AS "PR Item",

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

  -- Quantities + UOM (paired)
  h.MENGE   AS "Requested Quantity",
  h.MEINS   AS "Base Unit of Measure",
  h.PREIS   AS "Price",
  h.PEINH   AS "Price Unit",
  h.WAERS   AS "Currency",

  -- Dates
  h.BADAT   AS "PR Date",
  h.LFDAT   AS "Delivery Date",
  h.FRGDT   AS "Release Date",
  h.ERDAT   AS "Last Changed On",

  -- Status / indicators
  h.LOEKZ   AS "Deletion Indicator",
  h.STATU   AS "Processing Status",
  h.FRGKZ   AS "Release Indicator",
  h.EBELN   AS "Following PO (if any)",

  -- Org fields
  h.WERKS   AS "Plant",
  pt.NAME1  AS "Plant Name",
  h.LGORT   AS "Storage Location",
  sl.LGOBE  AS "Storage Location Name",
  h.EKGRP   AS "Purchasing Group",

  -- Master data enrichment
  h.BSART   AS "Document Type",
  h.MATNR   AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  h.KNTTP   AS "Account Assignment Category",
  acct.KOSTL AS "Cost Center",
  acct.SAKTO AS "G/L Account",
  acct.AUFNR AS "Order Number",
  h.ERNAM   AS "Created By"
FROM <catalog>.<schema>.eban h
LEFT JOIN <catalog>.<schema>.ebkn acct
  ON  acct.MANDT = h.MANDT
  AND acct.BANFN = h.BANFN
  AND acct.BNFPO = h.BNFPO
LEFT JOIN <catalog>.<schema>.makt mt
  ON  mt.MANDT = h.MANDT
  AND mt.MATNR = h.MATNR
  AND mt.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.mara m
  ON  m.MANDT = h.MANDT
  AND m.MATNR = h.MATNR
LEFT JOIN <catalog>.<schema>.t001w pt
  ON  pt.MANDT = h.MANDT
  AND pt.WERKS = h.WERKS
LEFT JOIN <catalog>.<schema>.t001l sl
  ON  sl.MANDT = h.MANDT
  AND sl.WERKS = h.WERKS
  AND sl.LGORT = h.LGORT
WHERE
  h.MANDT = '<MANDT>'
  AND h.BADAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  AND h.LOEKZ = ''
  -- AND h.WERKS = '<WERKS>'
  -- AND h.EKGRP = '<EKGRP>'
ORDER BY h.BADAT DESC, h.BANFN, h.BNFPO;