Invoices

1-- core.daisycon.invoices
2-- What Daisycon has invoiced the advertiser, one row per invoice, with the line
3-- totals next to the header totals and how long each invoice took to pay.
4--
5-- Grain: advertiser_id x invoice_number. Currency: per invoice.
6-- Depends on: staging.daisycon.financial_invoice, .financial_invoice_line, .advertiser
7--
8-- lines_amount_excl_vat should equal amount_excl_vat. A gap means a line is
9-- missing from the sync, not that Daisycon miscounted.
10
11WITH lines AS (
12    SELECT
13        advertiser_id,
14        invoice_number,
15        COUNT(*)             AS line_count,
16        SUM(amount_excl_vat) AS lines_amount_excl_vat
17    FROM {{staging.daisycon.financial_invoice_line}}
18    GROUP BY 1, 2
19)
20
21SELECT
22    i.advertiser_id,
23    a.advertiser_name,
24    i.invoice_number,
25    i.invoice_date,
26    DATE_TRUNC(i.invoice_date, MONTH)             AS invoice_month,
27    i.period_start_date,
28    i.period_end_date,
29    i.due_date,
30    i.paid_date,
31    i.invoice_status,
32    i.paid_date IS NOT NULL                       AS is_paid,
33    i.paid_date IS NULL AND i.due_date < CURRENT_DATE() AS is_overdue,
34    DATE_DIFF(i.paid_date, i.invoice_date, DAY)   AS days_to_pay,
35    i.currency,
36    i.amount_excl_vat,
37    i.vat,
38    i.amount_incl_vat,
39    l.line_count,
40    l.lines_amount_excl_vat