FBL1N

-- ============================================================
-- T-Code : FBL1N Vendor Line Item Display
-- Purpose: Display vendor open + cleared line items with payment-term and due-date enrichment
-- Grain  : One row per vendor line item (BSIK open / BSAK cleared — unioned)
-- Tables : BSIK, BSAK, LFA1, T001, T003T
-- Notes  : BSIK (open) and BSAK (cleared) are *alternative* secondary indices — never joined, always unioned. A line lives in exactly one at any time. UNION ALL both to reproduce FBL1N 'All items'.
-- ============================================================
SELECT
  'OPEN'   AS "Status",
  -- Keys
  i.MANDT  AS "Client",
  i.BUKRS  AS "Company Code",
  i.LIFNR  AS "Vendor",
  i.GJAHR  AS "Fiscal Year",
  i.BELNR  AS "Document Number",
  i.BUZEI  AS "Line Item",

  -- Descriptive text
  v.NAME1  AS "Vendor 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",

  -- Assignment
  i.ZUONR  AS "Assignment",
  i.XBLNR  AS "Reference Number"
FROM bsik i
LEFT JOIN lfa1 v
  ON  v.MANDT = i.MANDT
  AND v.LIFNR = i.LIFNR
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.LIFNR = '<LIFNR>'

UNION ALL

SELECT
  'CLEARED' AS "Status",
  c.MANDT, c.BUKRS, c.LIFNR, c.GJAHR, c.BELNR, c.BUZEI,
  v.NAME1, cc.BUTXT, dt.LTEXT,
  c.DMBTR, c.WRBTR, c.WAERS,
  c.BUDAT, c.BLDAT, c.ZFBDT, c.AUGDT,
  c.BLART, c.SHKZG, c.UMSKS, c.UMSKZ, c.AUGBL, c.ZTERM, c.ZLSPR,
  c.ZUONR, c.XBLNR
FROM bsak c
LEFT JOIN lfa1 v
  ON  v.MANDT = c.MANDT
  AND v.LIFNR = c.LIFNR
LEFT JOIN 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 = '100'
  AND c.BUKRS = '<BUKRS>'
  AND c.LIFNR = '<LIFNR>'
  AND c.AUGDT BETWEEN '<DATE_FROM>' AND '<DATE_TO>'

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