General ledger

1-- core.netvisor.general_ledger
2-- The general ledger (pääkirja): every line of every valid voucher, with its account's
3-- report line. Recreates the transaction list behind Netvisor's ledger reports.
4--
5-- Grain: one row per voucher line (voucher_key x line_index).
6-- Currency: the company's accounting currency, EUR for a Finnish company.
7-- Sign: `amount` is Netvisor's own line sum, debit positive and credit negative, so
8-- every voucher sums to zero. `amount_credit_positive` flips it for revenue-style
9-- reading.
10--
11-- Invalidated and deleted vouchers are excluded. No account names: Netvisor's chart of
12-- accounts is not part of the synced data, so accounts are identified by number and
13-- grouped through staging.netvisor.finnish_account_groups.
14
15SELECT
16    l.voucher_key,
17    l.voucher_number,
18    l.voucher_date,
19    DATE_TRUNC(l.voucher_date, MONTH)     AS voucher_month,
20    l.voucher_class,
21    l.voucher_description,
22    l.line_index,
23    l.line_key,
24    l.account_number,
25    g.statement,
26    g.report_line,
27    g.sort_order                          AS report_line_sort_order,
28    l.line_description,
29    l.debit,
30    l.credit,
31    l.amount,
32    -1 * l.amount                         AS amount_credit_positive,
33    l.vat_percent,
34    l.vat_code,
35    l.account_dimension,
36    l.dimensions_json,
37    l.linked_source_type,
38    l.linked_source_key
39FROM {{staging.netvisor.voucher_lines}} l
40-- Ranges do not overlap, so this join adds at most one group per line.