FSLEDG

-- ============================================================
-- Table  : FSLEDG — The accounts receivable ledger — one row per customer invoice or payment transaction, carrying the payer, invoice number, due date, and amount; its voucher (division, fiscal year, series, number) can carry more than one such row, and joins it to the general ledger
-- Purpose: Column-selected read of FSLEDG — auto-generated from field metadata
-- Grain  : Transaction-grain — finer than company (CONO) + ESDIVI + ESYEA4 + ESVSER + ESVONO; the full row key is not yet cataloged (see the table description)
-- Notes  : Auto-generated skeleton for Infor Data Lake-landed M3 data. Dates are numeric YYYYMMDD (0 = none, mapped to NULL); status ladders decoded inline where verified. Audit columns (RGDT/RGTM/LMDT/CHNO/CHID) omitted — see the quirks guide.
-- ============================================================
SELECT
  f.ESCONO AS "Company",
  f.ESDIVI AS "Division",
  f.ESYEA4 AS "Fiscal year",
  f.ESVSER AS "Voucher number series",
  f.ESVONO AS "Voucher number — the join back to the general ledger",
  f.ESJRNO AS "Journal number",
  f.ESCUNO AS "Customer the transaction belongs to",
  f.ESPYNO AS "Payer billed for the transaction",
  f.ESCINO AS "Customer invoice number",
  CASE WHEN f.ESACDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(f.ESACDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Accounting date",  -- YYYYMMDD, 0 → NULL
  CASE WHEN f.ESDUDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(f.ESDUDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Due date — the basis of receivables aging",  -- YYYYMMDD, 0 → NULL
  f.ESCUAM AS "Amount in transaction currency, signed"
FROM <catalog>.<schema>.FSLEDG f
WHERE
  f.ESCONO = <company>
  -- AND f.ESDIVI = '<DIVI>'
  -- AND f.ESACDT >= <DATE_FROM>  -- yyyyMMdd numeric
  -- AND f.ESACDT <= <DATE_TO>  -- yyyyMMdd numeric
ORDER BY f.ESYEA4;