Receivables aging

1-- core.business_central.receivables_aging
2-- Accounts receivable aging at invoice level: every open posted sales invoice with a
3-- remaining amount, bucketed by days past due as of today.
4--
5-- Grain: one row per open sales invoice (company x invoice). A point-in-time snapshot:
6-- buckets move every day and no history is kept.
7-- Currency: the invoice's own currency (`currency_code`, filled with the company's local
8-- currency where Business Central leaves it blank). Amounts are not converted, so sum
9-- per currency. Business Central's own Aged Accounts Receivable (the
10-- `aged_accounts_receivable` stream) reports per customer in LCY and is the figure to
11-- reconcile totals against.
12-- Sign: positive is owed to you.
13--
14-- Unapplied credit memos and unapplied payments are not in the sales invoice stream, so
15-- they are not netted here; Business Central's aged report nets them. Expect this model
16-- to be higher by exactly those amounts.
17
18SELECT
19    i.company_id,
20    c.company_name,
21    i.invoice_id,
22    i.invoice_number,
23    i.customer_id,
24    i.customer_number,
25    i.customer_name,
26    i.invoice_date,
27    i.due_date,
28    COALESCE(i.currency_code, c.local_currency_code)  AS currency_code,
29    i.currency_code IS NULL                           AS is_local_currency,
30    i.total_amount_including_tax,
31    i.remaining_amount,
32    DATE_DIFF(CURRENT_DATE(), i.due_date, DAY)        AS days_past_due,
33    CASE
34        WHEN i.due_date IS NULL                               THEN 'no_due_date'
35        WHEN DATE_DIFF(CURRENT_DATE(), i.due_date, DAY) <= 0  THEN 'not_due'
36        WHEN DATE_DIFF(CURRENT_DATE(), i.due_date, DAY) <= 30 THEN '1_30'
37        WHEN DATE_DIFF(CURRENT_DATE(), i.due_date, DAY) <= 60 THEN '31_60'
38        WHEN DATE_DIFF(CURRENT_DATE(), i.due_date, DAY) <= 90 THEN '61_90'
39        ELSE 'over_90'
40    END                                               AS aging_bucket,