FBL3N

-- ============================================================
-- T-Code : FBL3N G/L Account Line Item Display
-- Purpose: Display G/L account open + cleared line items with account-name enrichment
-- Grain  : One row per G/L line item (BSIS open / BSAS cleared — unioned)
-- Tables : BSIS, BSAS, SKAT, T001, T003T
-- Notes  : BSIS (open items) + BSAS (cleared items) are alternative indices on BSEG. Use UNION ALL to reproduce 'All items'. On S/4HANA, ACDOCA consolidates both — query it instead for new G/L.
-- ============================================================
SELECT
  'OPEN'   AS "Status",
  -- Keys
  i.MANDT  AS "Client",
  i.BUKRS  AS "Company Code",
  i.HKONT  AS "G/L Account",
  i.GJAHR  AS "Fiscal Year",
  i.BELNR  AS "Document Number",
  i.BUZEI  AS "Line Item",

  -- Descriptive text
  gl.TXT50 AS "G/L Account Name",
  cc.BUTXT AS "Company Code Name",
  dt.LTEXT AS "Document Type Description",

  -- Amounts + Currency (paired)
  i.DMBTR  AS "Amount in Local Currency",
  i.WRBTR  AS "Amount in Document Currency",
  i.WAERS  AS "Currency",

  -- Dates
  i.BUDAT  AS "Posting Date",
  i.BLDAT  AS "Document Date",
  i.AUGDT  AS "Clearing Date",

  -- Status / indicators
  i.BLART  AS "Document Type",
  i.SHKZG  AS "Debit/Credit Indicator",
  i.AUGBL  AS "Clearing Document",

  -- Org fields
  i.KOSTL  AS "Cost Center",
  i.AUFNR  AS "Order Number",
  i.PRCTR  AS "Profit Center",

  -- Assignment
  i.ZUONR  AS "Assignment",
  i.XBLNR  AS "Reference Number",
  i.SGTXT  AS "Item Text"
FROM bsis i
LEFT JOIN skat gl
  ON  gl.MANDT = i.MANDT
  AND gl.SAKNR = i.HKONT
  AND gl.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.t001 cc
  ON  cc.MANDT = i.MANDT
  AND cc.BUKRS = i.BUKRS
LEFT JOIN t003t dt
  ON  dt.MANDT = i.MANDT
  AND dt.BLART = i.BLART
  AND dt.SPRAS = 'E'
WHERE i.MANDT = '<MANDT>'
  AND i.BUKRS = '<BUKRS>'
  AND i.HKONT = '<HKONT>'

UNION ALL

SELECT
  'CLEARED' AS "Status",
  c.MANDT, c.BUKRS, c.HKONT, c.GJAHR, c.BELNR, c.BUZEI,
  gl.TXT50, cc.BUTXT, dt.LTEXT,
  c.DMBTR, c.WRBTR, c.WAERS,
  c.BUDAT, c.BLDAT, c.AUGDT,
  c.BLART, c.SHKZG, c.AUGBL,
  c.KOSTL, c.AUFNR, c.PRCTR,
  c.ZUONR, c.XBLNR, c.SGTXT
FROM bsas c
LEFT JOIN skat gl
  ON  gl.MANDT = c.MANDT
  AND gl.SAKNR = c.HKONT
  AND gl.SPRAS = 'E'
LEFT JOIN <catalog>.<schema>.t001 cc
  ON  cc.MANDT = c.MANDT
  AND cc.BUKRS = c.BUKRS
LEFT JOIN t003t dt
  ON  dt.MANDT = c.MANDT
  AND dt.BLART = c.BLART
  AND dt.SPRAS = 'E'
WHERE c.MANDT = '<MANDT>'
  AND c.BUKRS = '<BUKRS>'
  AND c.HKONT = '<HKONT>'
  AND c.AUGDT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'

ORDER BY "G/L Account", "Posting Date" DESC, "Document Number";