Settlement batches

1-- core.adyen.settlement_batches
2-- Adyen's settlement batches, itemised: what was settled, what came back out as
3-- refunds and chargebacks, what Adyen and the card schemes withheld, and what was
4-- paid to your bank account. Recreates the batch view of the Settlement details
5-- report in the Customer Area.
6--
7-- Grain: merchant account x batch number x net (settlement) currency. Entries with
8-- no batch number, if any, are kept in one row with a NULL batch_number rather than
9-- dropped, so totals still match the report.
10-- Depends on: staging.adyen.settlement_detail
11--
12-- SIGNS. Credits to the batch are positive, debits negative, so every column below
13-- is a plain sum and
14--     settled_net + refunds + chargebacks + adyen_fees + invoice_deductions
15--       + reserve_movements + balance_transfers + other_entries
16-- is the batch's net movement before payout. `payout` is shown as a positive
17-- number (the amount sent to your bank). `unreconciled_amount` is the net movement
18-- minus the payout and should be zero for a batch that has been paid out. A batch
19-- that has not been paid out yet, or whose balance was carried into a later batch,
20-- has no payout and a non-zero net movement.
21--
22-- FEES. `settled_gross` is the settled amount before transaction fees, in the net
23-- currency: net credit plus the fees withheld from it. Transaction fees are already
24-- inside settled_net and refunds, so they are reported separately for analysis and
25-- are not part of the sum above.
26
27SELECT
28    s.merchant_account,
29    s.batch_number,
30    s.net_currency,
31    ANY_VALUE(s.company_account)                                                  AS company_account,
32    MIN(s.created_date_local)                                                     AS first_entry_date,
33    MAX(s.created_date_local)                                                     AS last_entry_date,
34    MAX(CASE WHEN s.entry_category = 'payout' THEN s.created_date_local END)      AS payout_date,
35    ANY_VALUE(s.time_zone)                                                        AS time_zone,
36
37    COUNTIF(s.entry_category = 'settled')                                         AS settled_entries,
38    COUNTIF(s.entry_category = 'refund')                                          AS refund_entries,
39    COUNTIF(s.entry_category = 'chargeback')                                      AS chargeback_entries,
40