Receivables aging

1-- core.fortnox.receivables_aging
2-- Accounts receivable aging (kundreskontra, förfallna fakturor): every customer invoice
3-- with an open balance, bucketed by days past due as of today.
4--
5-- Grain: one row per open customer invoice. A point-in-time snapshot: the buckets move
6-- every day and the model keeps no history. Materialise a dated copy on a schedule if
7-- you want aging over time.
8--
9-- Currency: `balance` is in the invoice currency (`currency`); `balance_sek` converts it
10-- at the rate booked on the invoice, which is what the ledger carries. Sum `balance_sek`
11-- across currencies, never `balance`.
12-- Sign: positive is owed to you. Credit invoices with an unused balance are negative and
13-- are kept, so the total nets them off as Fortnox does.
14--
15-- Open means booked, not cancelled and a non-zero balance. Unbooked invoices are drafts
16-- and are not in the ledger yet.
17
18SELECT
19    invoice_number,
20    customer_number,
21    customer_name,
22    invoice_date,
23    due_date,
24    currency,
25    total,
26    balance,
27    total_sek,
28    balance_sek,
29    is_credit,
30    DATE_DIFF(CURRENT_DATE(), due_date, DAY)          AS days_past_due,
31    CASE
32        WHEN due_date IS NULL                                 THEN 'no_due_date'
33        WHEN DATE_DIFF(CURRENT_DATE(), due_date, DAY) <= 0    THEN 'not_due'
34        WHEN DATE_DIFF(CURRENT_DATE(), due_date, DAY) <= 30   THEN '1_30'
35        WHEN DATE_DIFF(CURRENT_DATE(), due_date, DAY) <= 60   THEN '31_60'
36        WHEN DATE_DIFF(CURRENT_DATE(), due_date, DAY) <= 90   THEN '61_90'
37        ELSE 'over_90'
38    END                                               AS aging_bucket,
39    CURRENT_DATE()                                    AS aged_as_of
40FROM {{staging.fortnox.invoices}}