Payment lifecycle

1-- core.adyen.payment_lifecycle
2-- One row per payment with every step of its life: when it was received,
3-- authorised or refused, captured, settled, refunded and charged back, and the
4-- amounts Adyen booked at each step. Built from the Payment accounting report.
5--
6-- Grain: merchant account x PSP reference (one payment).
7-- Depends on: staging.adyen.payment_accounting
8--
9-- Refunds and chargebacks are booked under the original payment's PSP reference
10-- (the refund's own reference is in modification_psp_reference), so they roll up
11-- onto the payment they reverse.
12--
13-- AMOUNTS keep Adyen's register signs. net_payable is the sum of the Payable
14-- register over all of the payment's records: what this payment contributed to
15-- your payouts after refunds, chargebacks and fees, in the settlement currency.
16-- Timestamps are wall clock times in the report's time zone.
17--
18-- The report covers the period your report files cover. A payment received before
19-- your first synced file has later steps but no Received or Authorised record.
20
21SELECT
22    a.merchant_account,
23    a.psp_reference,
24    ANY_VALUE(a.company_account)                                                  AS company_account,
25    ANY_VALUE(a.merchant_reference)                                               AS merchant_reference,
26    ANY_VALUE(a.shopper_reference)                                                AS shopper_reference,
27    ANY_VALUE(a.store)                                                            AS store,
28    ANY_VALUE(a.payment_method)                                                   AS payment_method,
29    ANY_VALUE(a.payment_method_variant)                                           AS payment_method_variant,
30    ANY_VALUE(a.payment_currency)                                                 AS payment_currency,
31    ANY_VALUE(a.settlement_currency)                                              AS settlement_currency,
32    ANY_VALUE(a.time_zone)                                                        AS time_zone,
33
34    MIN(CASE WHEN a.record_category = 'received'        THEN a.booked_at_local END) AS received_at_local,
35    MIN(CASE WHEN a.record_category = 'authorised'      THEN a.booked_at_local END) AS authorised_at_local,
36    MIN(CASE WHEN a.record_category = 'refused'         THEN a.booked_at_local END) AS refused_at_local,
37    MIN(CASE WHEN a.record_category = 'not_captured'    THEN a.booked_at_local END) AS cancelled_or_expired_at_local,
38    MIN(CASE WHEN a.record_category = 'sent_for_settle' THEN a.booked_at_local END) AS sent_for_settle_at_local,
39    MIN(CASE WHEN a.record_category = 'settled'         THEN a.booked_at_local END) AS settled_at_local,
40    MIN(CASE WHEN a.record_category = 'refunded'        THEN a.booked_at_local END) AS first_refunded_at_local,