General ledger

1-- core.workday_financial_management.general_ledger
2-- Every line of every posted journal entry, with its ledger account and the
3-- account's classification.
4--
5-- Grain: journal entry x line index.
6--
7-- Included: journals whose status is Posted. Draft, in-progress and cancelled
8-- journals are left out. A cancelled posted journal is undone in Workday by a
9-- posted reversing journal, so both stay in and net to zero.
10--
11-- Currency: ledger_debit, ledger_credit and everything derived from them are in
12-- the ledger currency, the base currency of the line's company (company_currency).
13-- debit and credit are kept in the transaction currency (line_currency) for
14-- reference. Do not sum ledger amounts across companies with different currencies.
15-- When Workday leaves a line's ledger amounts empty, the transaction amounts are
16-- used if the line is in the company's base currency. A foreign-currency line
17-- without ledger amounts is kept at 0 and flagged with is_ledger_amount_missing;
18-- assert_workday_financial_management__no_missing_ledger_amounts lists them.
19--
20-- Signs:
21--   ledger_debit, ledger_credit   positive, except reversals in a company that
22--                                 keeps the debit/credit side and reverses the
23--                                 sign, where they are negative
24--   net_debit                     ledger_debit - ledger_credit; sums to zero over
25--                                 each journal and company
26--   amount_natural                signed by the account's normal balance:
27--                                 debit-positive for Asset and Expense,
28--                                 credit-positive for Liability, Equity and
29--                                 Revenue. Unclassified accounts are treated as
30--                                 debit-positive; fix the mapping in
31--                                 staging.workday_financial_management.ledger_account.
32
33WITH posted AS (
34    SELECT *
35    FROM {{staging.workday_financial_management.journal_entry}}
36    WHERE LOWER(journal_status_code) = 'posted'
37       OR LOWER(journal_status) = 'posted'
38),
39
40lines AS (