Profit and loss by month

1-- core.workday_financial_management.profit_and_loss_by_month
2-- Income statement by month and ledger account, from posted journal lines.
3--
4-- Grain: company x ledger x book code x account set x ledger account x month,
5-- Revenue and Expense accounts only, months with postings only.
6--
7-- Currency: the company's base currency. Keep company_id in any rollup, or
8-- translate to one currency first.
9--
10-- Signs:
11--   amount            as an income statement prints it: revenue positive on
12--                     revenue accounts, expenses positive on expense accounts
13--   net_income_impact credit - debit: revenue positive, expenses negative, so a
14--                     plain SUM over any rows is their contribution to net income
15--
16-- Which accounts count as Revenue or Expense comes from the keyword mapping in
17-- staging.workday_financial_management.ledger_account. Months are calendar
18-- months of the accounting date, not Workday fiscal periods.
19
20SELECT
21    workday_tenant,
22    company_id,
23    ANY_VALUE(company_name)                AS company_name,
24    ANY_VALUE(company_currency)            AS company_currency,
25    ledger_id,
26    book_code,
27    accounting_month                       AS month,
28    account_set_code,
29    ledger_account_code,
30    ANY_VALUE(ledger_account_name)         AS ledger_account_name,
31    ANY_VALUE(ledger_account_type)         AS ledger_account_type,
32    ANY_VALUE(classification)              AS classification,
33    SUM(ledger_debit)                      AS debit,
34    SUM(ledger_credit)                     AS credit,
35    SUM(amount_natural)                    AS amount,
36    SUM(ledger_credit - ledger_debit)      AS net_income_impact
37FROM {{core.workday_financial_management.general_ledger}}
38WHERE is_profit_and_loss
39  AND accounting_month IS NOT NULL
40GROUP BY workday_tenant, company_id, ledger_id, book_code, accounting_month,