Skip to content
Help center
All templates

e-conomic Ledger

Every posted entry and both legs of every draft voucher, with account name, type and chart section.

What it answers

  • What was posted to an account in a period, and from which voucher?
  • Which entries are still drafts?

The view is named after the company, such as "Acme ApS Ledger", and reads the company's e-conomic data.

How to add it

Gatlas adds it for a company when the company connects e-conomic, and it fills in after the first sync. For a company that connected earlier, ask an AI assistant on a Read/write MCP connection:

Add the e-conomic Ledger view for Acme ApS.

The assistant creates it with the template id economic/ledger, after checking that the company's data has every column the template reads.

Columns

  • posting_status: posted (booked) or draft (in a journal, not booked yet).
  • leg: account, or contra for the opposite leg of a draft voucher. Posted entries are always account.
  • connection_label: The e-conomic agreement the row comes from; a company can hold several.
  • accounting_year: e-conomic accounting year of a posted entry; null on drafts.
  • date: Posting date.
  • voucher_number: Voucher number.
  • entry_number: Entry number of a posted entry; null on drafts.
  • entry_type: e-conomic entry type, e.g. financeVoucher, customerInvoice, supplierInvoice, openingEntry.
  • is_opening: True on openingEntry and transferredOpeningEntry rows, which restate a balance account at the start of a year.
  • account_number: Account the amount posts to.
  • account_name: Account name from the chart.
  • account_type: Account type from the chart: profitAndLoss or status for posting accounts.
  • chart_section: Nearest heading at or above the account in the company's chart.
  • counter_account_number: On a draft leg, the account on the voucher's other leg.
  • narrative: Entry text.
  • amount: Amount in the entry's currency, signed: positive is debit.
  • amount_base: Amount in the company's base currency, signed: positive is debit.
  • currency: Currency of amount.
  • vat_code: VAT code on this leg, if any.
  • department_number: Department, if the entry carries one.
  • journal_number: Journal a draft sits in; null on posted entries.
  • journal_name: Name of that journal.
  • supplier_number: Supplier on a draft voucher line, if any.
  • customer_number: Customer on a draft voucher line, if any.
  • supplier_invoice_number: Supplier invoice number on a draft, if any.
  • data_as_of: When this row was last synced.

What the AI assistant is told

The company's general ledger from e-conomic: one row per posted entry, and one row per leg of each draft voucher still in a journal.

posting_status = 'posted' is the booked ledger; 'draft' rows are not booked yet. For booked figures filter posting_status = 'posted'.

A draft voucher is a double entry: leg 'account' carries amount on account_number, leg 'contra' carries the opposite amount on the contra account. Both legs are here, so drafts sum correctly per account (cash included). A draft that is already posted is left out.

On a supplier or customer draft the account leg has no account_number: it is the supplier's or customer's balance (supplier_number, customer_number), which e-conomic books to the group's control account. A draft leg with a VAT code carries the gross amount; e-conomic splits the VAT out when it books. A draft line entered with only one side (often just the bank) has one leg, so its voucher does not balance until it is completed in e-conomic.

amount_base is in the company's base currency; amount is in the entry's currency. Sum amount_base, not amount, across currencies.

NEVER sum a balance-sheet (account_type 'status') account across accounting years: is_opening rows restate the cumulative balance at the start of every year, so a closing balance is the sum within ONE accounting_year (drafts carry no accounting_year; place them by date). P&L accounts carry no opening rows.

Monthly balances per account are ready-made in the Monthly Trial Balance view.

The SQL

__SCHEMA__ stands for the company's schema. To adapt the view, copy the SQL, put the schema in its place and change what your company needs.

WITH accts AS (
  SELECT
    COALESCE(a."_gatlas_connection_label", '') AS label_key,
    a."accountNumber" AS account_number,
    a."name" AS account_name,
    a."accountType" AS account_type,
    (SELECT h."name"
       FROM __SCHEMA__.economic_accounts h
      WHERE COALESCE(h."_gatlas_connection_label", '') = COALESCE(a."_gatlas_connection_label", '')
        AND h."accountType" IN ('heading', 'headingStart')
        AND h."accountNumber" <= a."accountNumber"
      ORDER BY h."accountNumber" DESC
      LIMIT 1) AS chart_section
  FROM __SCHEMA__.economic_accounts a
),
posted AS (
  SELECT
    'posted'::text AS posting_status,
    'account'::text AS leg,
    e."_gatlas_connection_label" AS connection_label,
    e."accountingYear" AS accounting_year,
    e."date"::date AS date,
    e."voucherNumber" AS voucher_number,
    e."entryNumber" AS entry_number,
    e."entryType" AS entry_type,
    e."accountNumber" AS account_number,
    e."accountName" AS source_account_name,
    NULL::bigint AS counter_account_number,
    e."text" AS narrative,
    e."amount" AS amount,
    e."amountInBaseCurrency" AS amount_base,
    e."currency" AS currency,
    NULLIF(btrim(e."vatCode"), '') AS vat_code,
    e."departmentNumber" AS department_number,
    NULL::bigint AS journal_number,
    NULL::text AS journal_name,
    NULL::bigint AS supplier_number,
    NULL::bigint AS customer_number,
    NULL::text AS supplier_invoice_number,
    e."_gatlas_synced_at" AS data_as_of
  FROM __SCHEMA__.economic_accounting_year_entries e
),
vouchers AS (
  SELECT
    v."_gatlas_connection_label" AS connection_label,
    COALESCE(v."_gatlas_connection_label", '') AS label_key,
    v."date"::date AS date,
    v."voucherNumber" AS voucher_number,
    v."entryType" AS entry_type,
    v."accountNumber" AS account_number,
    v."accountName" AS account_name,
    v."contraAccountNumber" AS contra_account_number,
    v."text" AS narrative,
    v."amount" AS amount,
    v."amountInBaseCurrency" AS amount_base,
    v."currency" AS currency,
    NULLIF(btrim(v."vatCode"), '') AS vat_code,
    NULLIF(btrim(v."contraVatCode"), '') AS contra_vat_code,
    v."departmentNumber" AS department_number,
    v."journalNumber" AS journal_number,
    v."journalName" AS journal_name,
    v."supplierNumber" AS supplier_number,
    v."customerNumber" AS customer_number,
    v."supplierInvoiceNumber" AS supplier_invoice_number,
    v."_gatlas_synced_at" AS data_as_of
  FROM __SCHEMA__.economic_journal_vouchers v
),
-- Posted rows that share a voucher number with a draft, built once so the
-- exclusions below are hash anti-joins rather than a scan per draft.
posted_keys AS (
  SELECT DISTINCT COALESCE(p.connection_label, '') AS label_key, p.voucher_number,
         p.date, p.account_number, p.amount
  FROM posted p
  WHERE p.voucher_number IN (SELECT d.voucher_number FROM vouchers d)
),
-- A draft that is already posted stays in the journal until the next sync.
-- The whole voucher goes when either of its legs is found posted.
open_vouchers AS (
  SELECT d.*
  FROM vouchers d
  WHERE NOT EXISTS (
          SELECT 1 FROM posted_keys k
          WHERE k.label_key = d.label_key AND k.voucher_number = d.voucher_number
            AND k.date = d.date AND k.account_number = d.account_number
            AND k.amount = d.amount)
    AND NOT EXISTS (
          SELECT 1 FROM posted_keys k
          WHERE k.label_key = d.label_key AND k.voucher_number = d.voucher_number
            AND k.date = d.date AND k.account_number = d.contra_account_number
            AND k.amount = -d.amount)
),
draft_legs AS (
  SELECT 'draft'::text AS posting_status, 'account'::text AS leg,
    o.connection_label, NULL::text AS accounting_year, o.date, o.voucher_number,
    NULL::bigint AS entry_number, o.entry_type, o.account_number,
    o.account_name AS source_account_name, o.contra_account_number AS counter_account_number,
    o.narrative, o.amount, o.amount_base, o.currency, o.vat_code, o.department_number,
    o.journal_number, o.journal_name, o.supplier_number, o.customer_number,
    o.supplier_invoice_number, o.data_as_of
  FROM open_vouchers o
  -- A line's account side is a ledger account, or a supplier or customer
  -- (account_number null) whose control account e-conomic picks when it
  -- books. A line with neither has only its contra side: its counterpart is
  -- another line of the same voucher.
  WHERE o.account_number IS NOT NULL
     OR o.supplier_number IS NOT NULL
     OR o.customer_number IS NOT NULL
  UNION ALL
  SELECT 'draft'::text, 'contra'::text,
    o.connection_label, NULL::text, o.date, o.voucher_number,
    NULL::bigint, o.entry_type, o.contra_account_number,
    NULL::text, o.account_number,
    o.narrative, -o.amount, -o.amount_base, o.currency, o.contra_vat_code, o.department_number,
    o.journal_number, o.journal_name, o.supplier_number, o.customer_number,
    o.supplier_invoice_number, o.data_as_of
  FROM open_vouchers o
  WHERE o.contra_account_number IS NOT NULL
),
ledger AS (
  SELECT * FROM posted
  UNION ALL
  SELECT * FROM draft_legs
)
SELECT
  l.posting_status,
  l.leg,
  l.connection_label,
  l.accounting_year,
  l.date,
  l.voucher_number,
  l.entry_number,
  l.entry_type,
  l.entry_type IN ('openingEntry', 'transferredOpeningEntry') AS is_opening,
  l.account_number,
  COALESCE(ac.account_name, l.source_account_name) AS account_name,
  ac.account_type,
  ac.chart_section,
  l.counter_account_number,
  l.narrative,
  l.amount,
  l.amount_base,
  l.currency,
  l.vat_code,
  l.department_number,
  l.journal_number,
  l.journal_name,
  l.supplier_number,
  l.customer_number,
  l.supplier_invoice_number,
  l.data_as_of
FROM ledger l
LEFT JOIN accts ac
  ON ac.label_key = COALESCE(l.connection_label, '')
 AND ac.account_number = l.account_number