AR AP aging

1-- core.netsuite.ar_ap_aging
2-- Open customer invoices and vendor bills, bucketed by days past due as of today.
3-- The document-level detail behind NetSuite's A/R Aging and A/P Aging reports.
4--
5-- Grain: transaction, one row per open invoice (CustInvc) or bill (VendBill).
6-- side is 'receivable' or 'payable'.
7--
8-- This is the current state: NetSuite's open amount as of the last sync. It
9-- cannot be rebuilt for a past date from these tables.
10--
11-- Currency: open_amount is in the transaction currency (currency_id);
12-- open_amount_base is converted to the subsidiary's base currency at the
13-- transaction's own exchange rate, which is how NetSuite carries it on the
14-- receivable or payable account. Sum open_amount_base per subsidiary.
15--
16-- Buckets: Current (not yet due), 1-30, 31-60, 61-90, 91 and over, by days past
17-- the due date. A document without a due date is aged from its transaction date.
18--
19-- Credit memos, vendor credits and unapplied payments are not netted in, so the
20-- totals are gross of credits and can be higher than NetSuite's aging report.
21
22WITH main_line AS (
23    -- The header line carries the subsidiary. One per transaction in NetSuite;
24    -- grouped anyway so a stray second main line cannot duplicate a document.
25    SELECT
26        netsuite_account,
27        transaction_id,
28        ANY_VALUE(subsidiary_id) AS subsidiary_id
29    FROM {{staging.netsuite.transaction_line}}
30    WHERE is_main_line
31    GROUP BY 1, 2
32),
33
34open_documents AS (
35    SELECT
36        t.netsuite_account,
37        ml.subsidiary_id,
38        IF(t.transaction_type = 'CustInvc', 'receivable', 'payable') AS side,
39        t.transaction_id,
40        t.transaction_type,