e-conomic Cash Position

• How much cash do we have, and how much of the overdraft is drawn?
• How did cash move month by month?
• What is our available liquidity, given the overdraft facility?

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

Ask an AI assistant on a Read/write MCP connection:

Add the e-conomic Cash Position view for Acme ApS.

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

• posting_status: posted (booked) or draft (an unposted journal voucher). A draft already posted is left out, so summing across both is safe.
• connection_label: The e-conomic agreement the row comes from; a company can hold several, and the same account number is a different bank in each.
• date: Date the movement hit the account: the entry date when posted, the voucher date on a draft. Forward-dated legs exist.
• account_number: Account number: within 5800 to 5899, or an overdraft facility account found by name elsewhere in the chart.
• account_name: Account name from the chart; null when the account row is missing.
• account_kind: bank, payment_provider, overdraft or not_own_cash, from the account's name. See the AI context for how each counts.
• amount_dkk: Signed movement in the company's base currency: positive is cash in, negative is cash out. Contra legs are already inverted. The sum per account is its balance.
• currency: Transaction currency. Sum amount_dkk, never a raw transaction amount.
• voucher_number: e-conomic voucher number; traces both legs of one transaction.
• entry_number: Entry number of a posted entry; null on drafts.
• journal_name: Journal a draft sits in; null on posted entries.
• narrative: Entry text. On a bank-fed line, usually the counterparty as the bank reported it.
• entry_type: e-conomic entry type, e.g. financeVoucher, supplierPayment or openingEntry (a kept carry-in).
• source_leg: account when the row is the voucher's account side, contra when it is the contra side with the amount inverted. Posted entries are always account.
• source_id: A key for the row, prefixed by connection_label; draft ids end in :a or :c and are not stable between syncs.
• data_as_of: When the underlying row was last synced.
• latest_posted_date: The latest posted date across the whole view. The gap to the newest draft shows how much of the balance rests on unbooked activity.

The company's cash from e-conomic: every leg on an account in the liquid-funds range 5800 to 5899, plus every leg on an overdraft facility account (a balance-sheet account named kassekredit, driftskredit, overtræk, overdraft, credit facility or kreditfacilitet) wherever it sits in the chart. Posted entries and both legs of draft journal vouchers are in one sign-normalized shape: positive is cash in, negative is cash out, in base currency (amount_dkk).

BALANCES. An account's balance at a date is SUM(amount_dkk) WHERE date <= <as of>, grouped by connection_label and account_number: the same number is a different bank in each agreement, so never add accounts across agreements without saying so. Use date <= CURRENT_DATE for today, since forward-dated legs are in the view. economic_accounts.balance is the posted balance only and ignores every draft; do not use it for cash.

ACCOUNT KINDS. account_kind comes from the account's name, first match wins:

• overdraft: a facility account (kassekredit, driftskredit, overtræk, overdraft, credit facility, kreditfacilitet). Its balance is debt.
• not_own_cash: money held for others or not a bank balance (client or klient accounts, havne and midler accounts, escrow, deposit, trust, cashback).
• payment_provider: money held by a payment provider or card program (Stripe, PayPal, Shopify, Klarna, MobilePay, Pleo, card accounts and similar), unless the name says bank or checking. It can read negative between a payout and the booking of the sales behind it: that is money in transit, not debt.
• bank: every other account in the range.

A name can mislead: say which accounts you counted when you quote a figure.

THE RECIPE, per connection_label at an as-of date:

• gross cash = the sum of the POSITIVE balances on bank and payment_provider accounts.
• overdraft drawn (debt, reported as a positive number) = minus the sum of the NEGATIVE balances on bank accounts, plus minus the balance of every overdraft account. A bank account that runs negative is an overdraft being drawn, which is how most companies book it.
• net cash = gross cash minus overdraft drawn, plus the negative payment_provider balances (they lower net cash and are not debt). This equals the sum of amount_dkk over every row except not_own_cash.
• the view's total, SUM(amount_dkk) over every row, is net cash plus not_own_cash. Quote net cash as the company's own cash and name what not_own_cash holds when it is not zero.
• A small negative bank balance can be timing, such as a payment booked before the transfer that funds it, rather than a facility. Say so when you quote overdraft drawn.
• available liquidity = net cash + (facility limit - overdraft drawn). No e-conomic table holds the facility limit: ask the user for it, and say the figure rests on what they told you.

OPENING ENTRIES ARE EXCLUDED. e-conomic writes openingEntry rows at the start of each accounting year that restate an account's cumulative balance, so a cumulative sum over the raw entries counts every closed year again. This view keeps an opening entry only when it is dated at the very start of an agreement's synced history, where it is a real carry-in. Never sum the raw tables yourself.

DRAFTS. A draft journal voucher is a full double entry: amount applies to accountNumber and its inverse to contraAccountNumber, so a draft row can give two legs here (source_leg account and contra), and a transfer between two cash accounts nets to zero. A draft already posted is anti-joined out on connection_label, voucher number, date, account and amount, except a reversal-and-rebooking pair in one draft voucher, which is kept. Drafts are mutable until booked; bank-fed journals carry matched statement lines on the bookkeeper's cadence. GROUP BY posting_status to separate booked from pending.

LIMITS. This is the ledger's account of cash, not the bank's: bank lines nobody has matched yet are not here, and Gatlas holds no independent bank balance. Treat every figure as ledger-derived and say so. amount_dkk on a foreign-currency account is e-conomic's conversion at booking, not today's rate. A chart that keeps its bank accounts outside 5800 to 5899 needs a custom view built from the Chart of Accounts view.

PATTERNS

• Balance by account now: SUM(amount_dkk) WHERE date <= CURRENT_DATE GROUP BY connection_label, account_number, account_name, account_kind
• Net cash now: SUM(amount_dkk) WHERE date <= CURRENT_DATE AND account_kind <> 'not_own_cash' GROUP BY connection_label
• Monthly cash flow: GROUP BY connection_label, date_trunc('month', date)
• Booked against pending: GROUP BY posting_status
• Trace one voucher: WHERE connection_label = '<agreement>' AND voucher_number = <n>

__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 facility AS (
  -- Overdraft facility accounts outside the bank range, found by name. The
  -- pattern avoids a bank's name (Nykredit), payables (Kreditorer) and a
  -- term loan (Banklån).
  SELECT x."_gatlas_connection_label" AS connection_label, x."accountNumber" AS account_number
  FROM __SCHEMA__.economic_accounts x
  WHERE x."accountType" = 'status'
    AND x."accountNumber" NOT BETWEEN 5800 AND 5899
    AND x."name" ~* '(kassekredit|driftskredit|overtr(æ|ae)k|overdraft|credit facility|kreditfacilitet)'
),
first_entry AS (
  -- The start of each agreement's synced history. An opening entry dated
  -- there is a real carry-in; every later one restates a balance the rows
  -- before it already carry, and is dropped below.
  SELECT e."_gatlas_connection_label" AS connection_label, MIN(e."date"::date) AS first_date
  FROM __SCHEMA__.economic_accounting_year_entries e
  GROUP BY 1
),
legs AS (
  SELECT 'posted'::text AS posting_status,
         e."_gatlas_connection_label" AS connection_label,
         e."date"::date AS date,
         e."accountNumber" AS account_number,
         COALESCE(e."amountInBaseCurrency", e."amount") AS amount_dkk,
         e."currency" AS currency,
         e."voucherNumber" AS voucher_number,
         e."entryNumber" AS entry_number,
         NULL::text AS journal_name,
         e."text" AS narrative,
         e."entryType" AS entry_type,
         'account'::text AS source_leg,
         COALESCE(e."_gatlas_connection_label", '') || ':' || e."_gatlas_entry_id" AS source_id,
         e."_gatlas_synced_at" AS data_as_of
  FROM __SCHEMA__.economic_accounting_year_entries e
  LEFT JOIN first_entry f ON f.connection_label IS NOT DISTINCT FROM e."_gatlas_connection_label"
  LEFT JOIN facility fa
    ON fa.connection_label IS NOT DISTINCT FROM e."_gatlas_connection_label"
   AND fa.account_number = e."accountNumber"
  WHERE (e."accountNumber" BETWEEN 5800 AND 5899 OR fa.account_number IS NOT NULL)
    AND NOT (e."entryType" IS NOT DISTINCT FROM 'openingEntry' AND e."date"::date > f.first_date)
  UNION ALL
  SELECT 'draft'::text, v."_gatlas_connection_label", v."date"::date, v."accountNumber",
         COALESCE(v."amountInBaseCurrency", v."amount"), v."currency", v."voucherNumber",
         NULL::integer, v."journalName", v."text", v."entryType", 'account'::text,
         COALESCE(v."_gatlas_connection_label", '') || ':' || v."_gatlas_voucher_id" || ':a', v."_gatlas_synced_at"
  FROM __SCHEMA__.economic_journal_vouchers v
  LEFT JOIN facility fa
    ON fa.connection_label IS NOT DISTINCT FROM v."_gatlas_connection_label"
   AND fa.account_number = v."accountNumber"
  WHERE v."accountNumber" BETWEEN 5800 AND 5899 OR fa.account_number IS NOT NULL
  UNION ALL
  SELECT 'draft'::text, v."_gatlas_connection_label", v."date"::date, v."contraAccountNumber",
         -COALESCE(v."amountInBaseCurrency", v."amount"), v."currency", v."voucherNumber",
         NULL::integer, v."journalName", v."text", v."entryType", 'contra'::text,
         COALESCE(v."_gatlas_connection_label", '') || ':' || v."_gatlas_voucher_id" || ':c', v."_gatlas_synced_at"
  FROM __SCHEMA__.economic_journal_vouchers v
  LEFT JOIN facility fa
    ON fa.connection_label IS NOT DISTINCT FROM v."_gatlas_connection_label"
   AND fa.account_number = v."contraAccountNumber"
  WHERE v."contraAccountNumber" BETWEEN 5800 AND 5899 OR fa.account_number IS NOT NULL
),
posted_keys AS (
  SELECT DISTINCT connection_label AS k_label, voucher_number AS k_voucher,
         date AS k_date, account_number AS k_account, amount_dkk AS k_amount
  FROM legs
  WHERE posting_status = 'posted'
    AND voucher_number IN (SELECT voucher_number FROM legs WHERE posting_status = 'draft')
)
SELECT l.posting_status,
       l.connection_label,
       l.date,
       l.account_number,
       a."name" AS account_name,
       CASE
         WHEN fac.account_number IS NOT NULL
           OR a."name" ~* '(kassekredit|driftskredit|overtr(æ|ae)k|overdraft|credit facility|kreditfacilitet)'
           THEN 'overdraft'
         WHEN a."name" ~* '(klient|client|havne|midler|escrow|deposit|trust|cashback)'
           THEN 'not_own_cash'
         WHEN a."name" ~* '(shopify|paypal|klarna|stripe|clearhaus|nets|mobilepay|amazon|reserve|kortindbetal|quickpay|reepay|adyen|viabill|anyday|payment provider|pleo|card)'
          AND a."name" !~* '(bank|checking)'
           THEN 'payment_provider'
         ELSE 'bank'
       END AS account_kind,
       l.amount_dkk,
       l.currency,
       l.voucher_number,
       l.entry_number,
       l.journal_name,
       l.narrative,
       l.entry_type,
       l.source_leg,
       l.source_id,
       l.data_as_of,
       MAX(l.date) FILTER (WHERE l.posting_status = 'posted') OVER () AS latest_posted_date
FROM legs l
LEFT JOIN __SCHEMA__.economic_accounts a
  ON a."accountNumber" = l.account_number
 AND a."_gatlas_connection_label" IS NOT DISTINCT FROM l.connection_label
LEFT JOIN facility fac
  ON fac.connection_label IS NOT DISTINCT FROM l.connection_label
 AND fac.account_number = l.account_number
LEFT JOIN posted_keys k
  ON l.posting_status = 'draft'
 AND COALESCE(k.k_label, '') = COALESCE(l.connection_label, '')
 AND k.k_voucher = l.voucher_number
 AND k.k_date = l.date
 AND k.k_account = l.account_number
 AND k.k_amount = l.amount_dkk
WHERE k.k_voucher IS NULL
   -- A draft leg with a posted twin is a copy of a booked line, unless the
   -- same draft voucher also carries the opposite leg on the same account:
   -- then it is a reversal-and-rebooking pair, and dropping it would cancel
   -- a real movement.
   OR EXISTS (
     SELECT 1 FROM legs r
     WHERE r.posting_status = 'draft'
       AND COALESCE(r.connection_label, '') = COALESCE(l.connection_label, '')
       AND r.voucher_number = l.voucher_number
       AND r.account_number = l.account_number
       AND r.amount_dkk = -l.amount_dkk
       AND r.source_id <> l.source_id
   )
ORDER BY l.date DESC, l.account_number

Before it is created, this query must return a row, or the company needs a view of its own:

SELECT 1 FROM __SCHEMA__.economic_accounts a
WHERE a."accountType" = 'status' AND a."accountNumber" BETWEEN 5800 AND 5899
LIMIT 1