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
  h.BUDAT   AS "Posting Date",
  h.BLDAT   AS "Document Date",
  h.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",
  h.REFBN   AS "Reference Document",
  l.REFBZ   AS "Reference Line Item",
  h.USNAM   AS "User Name"
FROM <catalog>.<schema>.coep l
LEFT JOIN <catalog>.<schema>.cobk h
  ON  h.MANDT = l.MANDT
  AND h.KOKRS = l.KOKRS
  AND h.BELNR = l.BELNR
LEFT JOIN <catalog>.<schema>.csks cs
  ON  cs.MANDT = l.MANDT
  AND cs.KOKRS = l.KOKRS
  AND cs.KOSTL = SUBSTR(l.OBJNR, 7)
LEFT JOIN <catalog>.<schema>.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 = '<MANDT>'
  AND l.KOKRS = '<KOKRS>'
  AND l.GJAHR = <GJAHR>
  AND l.WRTTP = '04'
  AND h.BUDAT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'
  -- AND cs.KOSTL = '<KOSTL>'
ORDER BY h.BUDAT DESC, l.BELNR, l.BUZEI;