General ledger

1-- core.business_central.general_ledger
2-- The general ledger: every G/L entry with its account, category and company. Recreates
3-- the G/L Entries list (and the Detail Trial Balance report) in Business Central.
4--
5-- Grain: one row per G/L entry (company x entry number).
6-- Currency: the company's local currency (LCY), named in `local_currency_code`. Entries
7-- from different companies are in different currencies when the companies differ; never
8-- sum across companies without converting.
9-- Sign: `amount` is debit minus credit. Assets and expenses positive, liabilities, equity
10-- and income negative. `amount_credit_positive` flips it for revenue-style reading.
11
12SELECT
13    e.company_id,
14    c.company_name,
15    c.local_currency_code,
16    e.entry_number,
17    e.entry_id,
18    e.posting_date,
19    DATE_TRUNC(e.posting_date, MONTH)       AS posting_month,
20    e.document_type,
21    e.document_number,
22    e.description,
23    e.account_id,
24    e.account_number,
25    a.account_name,
26    a.account_category,
27    a.account_subcategory,
28    COALESCE(a.statement, 'unclassified')   AS statement,
29    e.debit,
30    e.credit,
31    e.amount,
32    -1 * e.amount                           AS amount_credit_positive
33FROM {{staging.business_central.general_ledger_entries}} e
34LEFT JOIN {{staging.business_central.accounts}} a
35       ON a.company_id = e.company_id
36      AND a.account_id = e.account_id
37LEFT JOIN {{staging.business_central.company_information}} c
38       ON c.company_id = e.company_id