Payables aging

1-- core.fortnox.payables_aging
2-- Accounts payable aging (leverantörsreskontra): every supplier invoice with an open
3-- balance, bucketed by days past due as of today.
4--
5-- Grain: one row per open supplier invoice. A point-in-time snapshot, like
6-- receivables_aging.
7--
8-- Currency: `balance` is in the invoice currency; `balance_sek` uses the booked rate.
9-- Sign: positive is what you owe. Supplier credit notes are negative.
10
11SELECT
12    given_number,
13    supplier_invoice_number,
14    supplier_number,
15    supplier_name,
16    invoice_date,
17    due_date,
18    currency,
19    total,
20    balance,
21    total_sek,
22    balance_sek,
23    is_credit,
24    is_payment_pending,
25    DATE_DIFF(CURRENT_DATE(), due_date, DAY)          AS days_past_due,
26    CASE
27        WHEN due_date IS NULL                                 THEN 'no_due_date'
28        WHEN DATE_DIFF(CURRENT_DATE(), due_date, DAY) <= 0    THEN 'not_due'
29        WHEN DATE_DIFF(CURRENT_DATE(), due_date, DAY) <= 30   THEN '1_30'
30        WHEN DATE_DIFF(CURRENT_DATE(), due_date, DAY) <= 60   THEN '31_60'
31        WHEN DATE_DIFF(CURRENT_DATE(), due_date, DAY) <= 90   THEN '61_90'
32        ELSE 'over_90'
33    END                                               AS aging_bucket,
34    CURRENT_DATE()                                    AS aged_as_of
35FROM {{staging.fortnox.supplier_invoices}}
36WHERE is_booked
37  AND NOT is_cancelled
38  AND balance <> 0