Trial balance

1-- core.workday_financial_management.trial_balance
2-- Month-end balance per ledger account, per company, ledger and book code, built
3-- from posted journal lines.
4--
5-- Grain: company x ledger x book code x account set x ledger account x month,
6-- from the first month with a posting to the current month (or the last posted
7-- month, if later), so balances carry through months without activity.
8--
9-- Book code: journals without one apply to every book; journals with one only to
10-- that book. For the balance of one book, add the NULL book code rows to that
11-- book's rows. For the primary view, filter to book_code IS NULL.
12--
13-- Currency: the company's base currency (company_currency).
14--
15-- Columns, debit-positive (debit - credit):
16--   period_debit, period_credit, period_net_debit   activity in the month
17--   closing_net_debit         cumulative since the first posted journal
18--   closing_balance_natural   the same, signed by the account's normal balance
19--
20-- Months are calendar months of the accounting date. Workday's Trial Balance
21-- reports by fiscal period and shows income and expense accounts for the fiscal
22-- year to date; the fiscal schedule is not part of these tables, so compare
23-- balance sheet accounts here and income and expense accounts through
24-- profit_and_loss_by_month.
25--
26-- Only journals synced since the connection's start date are included. If the
27-- sync does not reach back to the first journal, closing balances are missing the
28-- opening balance and only period amounts can be trusted.
29
30WITH monthly AS (
31    SELECT
32        workday_tenant,
33        company_id,
34        ledger_id,
35        book_code,
36        account_set_code,
37        ledger_account_code,
38        accounting_month  AS month,
39        SUM(ledger_debit)  AS period_debit,
40        SUM(ledger_credit) AS period_credit