KSB1

-- ============================================================
-- T-Code : KSB1 Cost Centers: Actual Line Items
-- Purpose: List actual postings (primary & secondary costs) against cost centers for a period
-- Grain  : One row per CO line item (COEP)
-- Tables : COEP, COBK, CSKS, CSKT, SKAT
-- Notes  : COEP is the line-item table; COBK is the document header. Filter WRTTP = '04' (actual) for KSB1 (actuals); use WRTTP = '01' for plan lines (KSBP).
-- ============================================================
SELECT
  -- Keys
  l.MANDT   AS "Client",
  l.KOKRS   AS "Controlling Area",
  l.BELNR   AS "CO Document Number",
  l.BUZEI   AS "Line Item",

  -- Descriptive text
  ct.KTEXT  AS "Cost Center Name",
  gl.TXT50  AS "Cost Element Name",
  l.SGTXT   AS "Item Text",

  -- Amounts + Currency (paired)
  l.WOGBTR  AS "Amount in Object Currency",
  l.WTGBTR  AS "Amount in Transaction Currency",
  l.WKGBTR  AS "Amount in Controlling Area Currency",
  l.TWAER   AS "Transaction Currency",

  -- Quantities + UOM (paired)
  l.MEGBTR  AS "Total Quantity",
  l.MEINB   AS "Quantity Unit",

  -- Dates
  l.BUDAT   AS "Posting Date",
  l.BLDAT   AS "Document Date",
  l.CPUDT   AS "Entered On",
  l.PERIO   AS "Period",
  l.GJAHR   AS "Fiscal Year",

  -- Status / indicators
  l.WRTTP   AS "Value Type",
  l.VRGNG   AS "Transaction",
  l.BEKNZ   AS "Debit/Credit Indicator",

  -- Org fields
  cs.KOSTL  AS "Cost Center",
  cs.BUKRS  AS "Company Code",
  cs.WERKS  AS "Plant",

  -- Master data enrichment
  l.KSTAR   AS "Cost Element",
  l.OBJNR   AS "Object Number",
  l.REFBN   AS "Reference Document",
  l.REFBZ   AS "Reference Line Item",
  l.AWREF   AS "Source Document Number",
  l.USNAM   AS "User Name"
FROM coep l
LEFT JOIN cobk h
  ON  h.MANDT = l.MANDT
  AND h.KOKRS = l.KOKRS
  AND h.BELNR = l.BELNR
LEFT JOIN csks cs
  ON  cs.MANDT = l.MANDT
  AND cs.KOKRS = l.KOKRS
  AND cs.KOSTL = SUBSTR(l.OBJNR, 7)
LEFT JOIN cskt ct
  ON  ct.MANDT = cs.MANDT
  AND ct.KOKRS = cs.KOKRS
  AND ct.KOSTL = cs.KOSTL
  AND ct.DATBI = cs.DATBI
  AND ct.SPRAS = 'E'
LEFT JOIN skat gl
  ON  gl.MANDT = l.MANDT
  AND gl.SAKNR = l.KSTAR
  AND gl.SPRAS = 'E'
WHERE
  l.MANDT = '100'
  AND l.KOKRS = '<KOKRS>'
  AND l.GJAHR = <GJAHR>
  AND l.WRTTP = '04'
  AND l.BUDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND cs.KOSTL = '<KOSTL>'
ORDER BY l.BUDAT DESC, l.BELNR, l.BUZEI;