MIRO

-- ============================================================
-- T-Code : MIRO Enter Incoming Invoice
-- Purpose: Enter a vendor invoice and post the matching accounting document + PO history
-- Grain  : One row per invoice accounting line (BKPF header + BSEG line)
-- Tables : BKPF, BSEG, LFA1, T001, T003T, EKBE (commented optional)
-- Notes  : MIRO updates EKBE with VGABE = '2' (invoice receipt) asynchronously. Bridge BSEG ↔ EKBE via EBELN + EBELP; primary key to MIRO invoice doc is RBKP/RSEG for the logistics view.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.BUKRS   AS "Company Code",
  h.BELNR   AS "Invoice Accounting Document",
  h.GJAHR   AS "Fiscal Year",
  l.BUZEI   AS "Line Item",

  -- Descriptive text
  dt.LTEXT  AS "Document Type Description",
  h.XBLNR   AS "Vendor Invoice Reference",
  l.SGTXT   AS "Item Text",

  -- Amounts + Currency (paired)
  l.DMBTR   AS "Amount in Local Currency",
  l.WRBTR   AS "Amount in Document Currency",
  h.WAERS   AS "Currency",
  l.MWSKZ   AS "Tax Code",

  -- Dates
  h.BUDAT   AS "Posting Date",
  h.BLDAT   AS "Invoice Document Date",
  h.CPUDT   AS "Entered On",

  -- Status / indicators
  h.BLART   AS "Document Type",
  l.SHKZG   AS "Debit/Credit Indicator",
  l.KOART   AS "Account Type",
  h.STBLG   AS "Reversal Document",

  -- Org fields
  cc.BUTXT  AS "Company Code Name",

  -- Master data enrichment
  l.HKONT   AS "G/L Account",
  l.LIFNR   AS "Vendor",
  v.NAME1   AS "Vendor Name",
  l.EBELN   AS "Purchase Order",
  l.EBELP   AS "PO Item",
  h.AWKEY   AS "Object Key (RBKP|GJAHR)",
  h.USNAM   AS "User Name"
FROM <catalog>.<schema>.bkpf h
INNER JOIN <catalog>.<schema>.bseg l
  ON  l.MANDT = h.MANDT
  AND l.BUKRS = h.BUKRS
  AND l.BELNR = h.BELNR
  AND l.GJAHR = h.GJAHR
LEFT JOIN <catalog>.<schema>.lfa1 v
  ON  v.MANDT = l.MANDT
  AND v.LIFNR = l.LIFNR
LEFT JOIN <catalog>.<schema>.t001 cc
  ON  cc.MANDT = h.MANDT
  AND cc.BUKRS = h.BUKRS
LEFT JOIN t003t dt
  ON  dt.MANDT = h.MANDT
  AND dt.BLART = h.BLART
  AND dt.SPRAS = 'E'
WHERE
  h.MANDT = '<MANDT>'
  AND h.BLART IN ('RE','RN','KR','KG')
  AND h.BUDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND l.LIFNR = '<LIFNR>'
ORDER BY h.BUDAT DESC, h.BELNR, l.BUZEI;

-- Optional: PO history rows created by this invoice (VGABE = '2').
-- SELECT e.EBELN, e.EBELP, e.BELNR AS invoice_doc, e.GJAHR, e.BUDAT,
--        e.MENGE, e.DMBTR, e.WRBTR
--   FROM <catalog>.<schema>.ekbe e
--  WHERE e.MANDT = '<MANDT>'
--    AND e.VGABE = '2'
--    AND e.BUDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>';