VF02

-- ============================================================
-- T-Code : VF02 Change Billing Document
-- Purpose: Change an existing billing document
-- Grain  : One row per billing-document item
-- Tables : VBRK, VBRP, MAKT, MARA, KNA1, T001, TVKOT
-- Notes  : VBRK.FKSTO = 'X' indicates a cancelled billing document — filter it out for revenue reports. RFBSK = 'C' means fully transferred to accounting. VBRK has no posting-date field — posting date lives on the FI document (BKPF.BUDAT), keyed from VBRK via AWKEY.
-- ============================================================
SELECT
  -- Keys
  h.MANDT   AS "Client",
  h.VBELN   AS "Billing Document",
  l.POSNR   AS "Item",

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

  -- Quantities + UOM (paired)
  l.FKIMG   AS "Billed Quantity",
  l.VRKME   AS "Sales Unit",
  l.NETWR   AS "Net Value",
  l.MWSBP   AS "Tax Amount",
  h.WAERK   AS "Document Currency",
  h.KURRF   AS "Exchange Rate",

  -- Dates
  h.FKDAT   AS "Billing Date",
  h.ERDAT   AS "Created On",

  -- Status / indicators
  h.FKSTO   AS "Cancellation Flag",
  h.RFBSK   AS "Accounting Transfer Status",
  h.SFAKN   AS "Cancellation Reference",

  -- Org fields
  h.VKORG   AS "Sales Organization",
  so.VTEXT  AS "Sales Organization Name",
  h.VTWEG   AS "Distribution Channel",
  h.SPART   AS "Division",
  h.BUKRS   AS "Company Code",
  cc.BUTXT  AS "Company Code Name",
  l.WERKS   AS "Plant",

  -- Master data enrichment
  h.FKART   AS "Billing Type",
  h.KUNAG   AS "Sold-To Party",
  h.KUNRG   AS "Payer",
  c.NAME1   AS "Payer Name",
  l.MATNR   AS "Material Number",
  m.MTART   AS "Material Type",
  m.MATKL   AS "Material Group",
  l.VGBEL   AS "Reference Document (Delivery)",
  l.VGPOS   AS "Reference Item"
FROM <catalog>.<schema>.vbrk h
INNER JOIN <catalog>.<schema>.vbrp l
  ON  l.MANDT = h.MANDT
  AND l.VBELN = h.VBELN
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>.kna1 c
  ON  c.MANDT = h.MANDT
  AND c.KUNNR = h.KUNRG
LEFT JOIN <catalog>.<schema>.t001 cc
  ON  cc.MANDT = h.MANDT
  AND cc.BUKRS = h.BUKRS
LEFT JOIN <catalog>.<schema>.tvkot so
  ON  so.MANDT = h.MANDT
  AND so.VKORG = h.VKORG
  AND so.SPRAS = 'E'
WHERE
  h.MANDT = '<MANDT>'
  AND h.FKDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  AND h.FKSTO = ''
  -- AND h.VKORG = '<VKORG>'
ORDER BY h.FKDAT DESC, h.VBELN, l.POSNR;