AR AP aging

1-- core.quickbooks.ar_ap_aging
2-- Open invoices and open bills, bucketed by days past due as of today. The
3-- document-level detail behind QuickBooks' A/R Aging and A/P Aging reports.
4--
5-- Grain: company x side x document. side is 'receivable' (invoices) or 'payable'
6-- (bills). Only documents with an open balance are kept.
7--
8-- This is the current state, not history: QuickBooks only exposes today's open
9-- balance, so the aging cannot be rebuilt for a past date from these tables.
10--
11-- Currency: open_balance is in the document's own currency (currency column);
12-- open_balance_home is in the company's home currency and is the one to sum.
13--
14-- Buckets follow the report's defaults: Current (not yet due), 1-30, 31-60,
15-- 61-90, 91 and over, by days past the due date. A document without a due date
16-- is aged from its transaction date.
17--
18-- Differences from QuickBooks' own aging reports: unapplied customer payments,
19-- open credit memos and vendor credits are not included, so the totals here are
20-- gross of credits and can be higher than the report's.
21
22WITH open_documents AS (
23    SELECT
24        quickbooks_company,
25        'receivable'  AS side,
26        invoice_id    AS document_id,
27        doc_number,
28        customer_id   AS counterparty_id,
29        customer_name AS counterparty_name,
30        txn_date,
31        due_date,
32        currency,
33        exchange_rate,
34        total_amount,
35        open_balance,
36        open_balance_home
37    FROM {{staging.quickbooks.invoice}}
38    WHERE open_balance != 0
39
40    UNION ALL