FB02

-- ============================================================
-- T-Code : FB02 Change Document
-- Purpose: Change a posted document (header text / assignment / reference only)
-- Grain  : One row per accounting-document line (BKPF header + BSEG line)
-- Tables : BKPF, BSEG, SKAT, T001, T003T, LFA1, KNA1, CSKT
-- Notes  : BSEG is a cluster table on ECC — access it by key, not by scanning. On S/4HANA, prefer ACDOCA (Universal Journal) for reporting; the join predicate stays similar but performance is much better against ACDOCA. Use BSEG for entry-view/source-document or open-item fields that are not in ACDOCA.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.BUKRS   AS "Company Code",
  h.BELNR   AS "Document Number",
  h.GJAHR   AS "Fiscal Year",
  l.BUZEI   AS "Line Item",

  -- Descriptive text
  dt.LTEXT  AS "Document Type Description",
  gl.TXT50  AS "G/L Account Name",
  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 "Document Currency",
  l.MWSKZ   AS "Tax Code",

  -- Dates
  h.BUDAT   AS "Posting Date",
  h.BLDAT   AS "Document Date",
  h.CPUDT   AS "Entered On",
  l.ZFBDT   AS "Baseline Date for Due Date",

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

  -- Org fields
  cc.BUTXT  AS "Company Code Name",
  l.KOSTL   AS "Cost Center",
  ct.KTEXT  AS "Cost Center Name",
  l.PRCTR   AS "Profit Center",
  l.AUFNR   AS "Order Number",
  l.GSBER   AS "Business Area",

  -- Master data enrichment
  l.HKONT   AS "G/L Account",
  l.LIFNR   AS "Vendor",
  v.NAME1   AS "Vendor Name",
  l.KUNNR   AS "Customer",
  c.NAME1   AS "Customer Name",
  l.ZTERM   AS "Payment Terms",
  l.ZUONR   AS "Assignment",
  h.XBLNR   AS "Reference Document Number",
  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 skat gl
  ON  gl.MANDT = l.MANDT
  AND gl.SAKNR = l.HKONT
  AND gl.SPRAS = 'E'
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'
LEFT JOIN <catalog>.<schema>.lfa1 v
  ON  v.MANDT = l.MANDT
  AND v.LIFNR = l.LIFNR
LEFT JOIN <catalog>.<schema>.kna1 c
  ON  c.MANDT = l.MANDT
  AND c.KUNNR = l.KUNNR
LEFT JOIN <catalog>.<schema>.cskt ct
  ON  ct.MANDT = l.MANDT
  AND ct.KOKRS = '<KOKRS>'
  AND ct.KOSTL = l.KOSTL
  AND ct.SPRAS = 'E'
  AND ct.DATBI >= h.BUDAT
WHERE
  h.MANDT = '<MANDT>'
  AND h.BUKRS = '<BUKRS>'
  AND h.GJAHR = <GJAHR>
  AND h.BUDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND h.BLART = '<BLART>'
ORDER BY h.BUDAT DESC, h.BELNR, l.BUZEI;