Trial balance

1-- core.netsuite.trial_balance
2-- Month-end balance per account, per subsidiary and accounting book, built from
3-- the general ledger.
4--
5-- Grain: account x accounting book x subsidiary x month, from the first month the
6-- combination has a posting to the current month (or the last posted month, if
7-- later), so balances carry through months without activity.
8--
9-- Currency: the subsidiary's base currency. Do not sum across subsidiaries
10-- without converting; for a consolidated view, translate each subsidiary with
11-- NetSuite's consolidated exchange rates first.
12--
13-- Columns, debit-positive (debit - credit):
14--   period_debit, period_credit, period_net_debit   activity within the month
15--   closing_net_debit   cumulative since the first posting
16--   closing_balance_natural   the same, signed by the account's normal balance
17--
18-- Months are calendar months of the transaction date. NetSuite reports by posting
19-- period, which usually matches the transaction month but can differ when a
20-- transaction is posted to an earlier or later open period. NetSuite's own Trial
21-- Balance also shows income and expense accounts year-to-date and folds earlier
22-- years into retained earnings, which needs the fiscal calendar; compare balance
23-- sheet accounts (is_profit_and_loss = FALSE) directly, and income and expense
24-- accounts through profit_and_loss_by_month.
25
26WITH monthly AS (
27    SELECT
28        netsuite_account,
29        accounting_book_id,
30        subsidiary_id,
31        account_id,
32        transaction_month AS month,
33        SUM(debit)        AS period_debit,
34        SUM(credit)       AS period_credit
35    FROM {{core.netsuite.general_ledger}}
36    WHERE transaction_month IS NOT NULL
37    GROUP BY 1, 2, 3, 4, 5
38),
39
40bounds AS (