Payables aging

1-- core.netvisor.payables_aging
2-- Accounts payable aging (ostoreskontra, avoimet ostolaskut): every purchase invoice
3-- with an unpaid amount, bucketed by days past due as of today.
4--
5-- Grain: one row per open purchase invoice. A point-in-time snapshot: buckets move every
6-- day and no history is kept. Materialise a dated copy on a schedule for aging over time.
7--
8-- Currency: the company's accounting currency (EUR); the purchase invoice payload has no
9-- currency code. Sign: positive is what you owe; credit notes are negative and kept, so
10-- totals net them off.
11--
12-- Open means `amount - paid_amount` is not zero. Invoices still awaiting approval are
13-- included, because they are owed regardless; filter on `approval_status` if you want
14-- only approved ones.
15
16SELECT
17    purchase_invoice_key,
18    purchase_invoice_number,
19    vendor_key,
20    vendor_code,
21    vendor_name,
22    invoice_date,
23    due_date,
24    amount,
25    paid_amount,
26    open_amount,
27    invoice_status,
28    approval_status,
29    is_accounted,
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.netvisor.purchase_invoices}}