FBL5N

-- ============================================================
-- T-Code : FBL5N Customer Line Item Display
-- Purpose: Display customer open + cleared line items with due-date and dunning enrichment
-- Grain  : One row per customer line item (BSID open / BSAD cleared — unioned)
-- Tables : BSID, BSAD, KNA1, T001, T003T
-- Notes  : BSID (open) + BSAD (cleared) are alternative secondary indices — never joined, always unioned. UNION ALL to reproduce 'All items'.
-- ============================================================
SELECT
  'OPEN'   AS "Status",
  -- Keys
  i.MANDT  AS "Client",
  i.BUKRS  AS "Company Code",
  i.KUNNR  AS "Customer",
  i.GJAHR  AS "Fiscal Year",
  i.BELNR  AS "Document Number",
  i.BUZEI  AS "Line Item",

  -- Descriptive text
  c.NAME1  AS "Customer 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.ZFBDT  AS "Baseline Payment Date",
  i.AUGDT  AS "Clearing Date",

  -- Status / indicators
  i.BLART  AS "Document Type",
  i.SHKZG  AS "Debit/Credit Indicator",
  i.UMSKS  AS "Special G/L Transaction Type",
  i.UMSKZ  AS "Special G/L Indicator",
  i.AUGBL  AS "Clearing Document",
  i.ZTERM  AS "Payment Terms",
  i.ZLSPR  AS "Payment Block",
  i.MABER  AS "Dunning Area",
  i.MANST  AS "Dunning Level",

  -- Assignment
  i.ZUONR  AS "Assignment",
  i.XBLNR  AS "Reference Number"
FROM bsid i
LEFT JOIN kna1 c
  ON  c.MANDT = i.MANDT
  AND c.KUNNR = i.KUNNR
LEFT JOIN 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 = '100'
  AND i.BUKRS = '<BUKRS>'
  AND i.KUNNR = '<KUNNR>'

UNION ALL

SELECT
  'CLEARED' AS "Status",
  cl.MANDT, cl.BUKRS, cl.KUNNR, cl.GJAHR, cl.BELNR, cl.BUZEI,
  c.NAME1, cc.BUTXT, dt.LTEXT,
  cl.DMBTR, cl.WRBTR, cl.WAERS,
  cl.BUDAT, cl.BLDAT, cl.ZFBDT, cl.AUGDT,
  cl.BLART, cl.SHKZG, cl.UMSKS, cl.UMSKZ, cl.AUGBL, cl.ZTERM, cl.ZLSPR, cl.MABER, cl.MANST,
  cl.ZUONR, cl.XBLNR
FROM bsad cl
LEFT JOIN kna1 c
  ON  c.MANDT = cl.MANDT
  AND c.KUNNR = cl.KUNNR
LEFT JOIN t001 cc
  ON  cc.MANDT = cl.MANDT
  AND cc.BUKRS = cl.BUKRS
LEFT JOIN t003t dt
  ON  dt.MANDT = cl.MANDT
  AND dt.BLART = cl.BLART
  AND dt.SPRAS = 'E'
WHERE cl.MANDT = '100'
  AND cl.BUKRS = '<BUKRS>'
  AND cl.KUNNR = '<KUNNR>'
  AND cl.AUGDT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'

ORDER BY "Customer", "Posting Date" DESC, "Document Number";