General ledger

1-- core.quickbooks.general_ledger
2-- Every debit and credit QuickBooks Online posted, one row per journal line, with
3-- the account's classification attached. This is the base for the trial balance
4-- and the P&L, and the table to filter when someone asks "what hit this account".
5--
6-- Grain: company x transaction id x document number x line position. Line position
7-- 0 is the transaction's first line; later lines keep the order of the report.
8--
9-- Source: QuickBooks' Journal report, which covers every transaction type that
10-- posts to the ledger (invoices, sales receipts, payments, bills, bill payments,
11-- checks, expenses, deposits, transfers, journal entries). Reading it avoids
12-- rebuilding the ledger from a dozen transaction streams, each with its own line
13-- layout and its own idea of which account is hit.
14--
15-- Currency: the company's home currency. Foreign-currency transactions are already
16-- converted by QuickBooks at the transaction's exchange rate.
17--
18-- Signs:
19--   debit, credit     both positive, exactly as QuickBooks shows them
20--   net_debit         debit - credit. Sums to zero over any complete transaction,
21--                     and over the whole ledger.
22--   amount_natural    signed by the account's normal balance: debit - credit for
23--                     Asset and Expense accounts, credit - debit for Liability,
24--                     Equity and Revenue. Positive means the account grew.
25
26WITH lines AS (
27    SELECT
28        quickbooks_company,
29        transaction_id,
30        transaction_number,
31        0           AS line_position,
32        memo,
33        account_id,
34        account_name,
35        debit,
36        credit
37    FROM {{staging.quickbooks.journal_report}}
38
39    UNION ALL
40