Transactions

1-- core.paypal.transactions
2-- Every PayPal transaction record, classified: the itemised activity behind
3-- PayPal's activity download and balance report.
4-- Grain: transaction id x event code.
5--
6-- Classification uses the first three characters of PayPal's transaction event
7-- code (T00 payments, T01 fees, T02 currency conversions, T03 bank deposits, T04
8-- bank withdrawals, T11 reversals, T12 adjustments, T13 authorizations, T15 and T21
9-- holds and releases, T98 informational). T1107 is split out as a refund.
10--
11-- is_balance_affecting is TRUE for completed (S) or later-reversed (V) records
12-- outside the authorization (T13) and informational (T98) groups. Pending (P) and
13-- denied (D) records are kept but do not count. The reversal of a V record is its
14-- own T11 record, so counting both is what makes the balance net to zero.
15--
16-- Amounts are in the transaction currency, major units, signed from your account's
17-- point of view: in is positive, out is negative. net_amount = gross_amount +
18-- fee_amount. Dates are UTC.
19
20SELECT
21    transaction_id,
22    event_code,
23    DATE(initiated_at)                          AS transaction_date,
24    initiated_at,
25    updated_at,
26    status,
27    SUBSTR(event_code, 1, 3)                    AS event_group,
28    CASE
29        WHEN event_code = 'T1107'                  THEN 'refund'
30        WHEN STARTS_WITH(event_code, 'T00')        THEN IF(gross_amount < 0, 'payment_sent', 'payment_received')
31        WHEN STARTS_WITH(event_code, 'T01')        THEN 'fee'
32        WHEN STARTS_WITH(event_code, 'T02')        THEN 'currency_conversion'
33        WHEN STARTS_WITH(event_code, 'T03')        THEN 'bank_deposit'
34        WHEN STARTS_WITH(event_code, 'T04')        THEN 'bank_withdrawal'
35        WHEN STARTS_WITH(event_code, 'T11')
36          OR STARTS_WITH(event_code, 'T12')        THEN 'chargeback_or_reversal'
37        WHEN STARTS_WITH(event_code, 'T13')        THEN 'authorization'
38        WHEN STARTS_WITH(event_code, 'T15')
39          OR STARTS_WITH(event_code, 'T21')        THEN 'hold_or_release'
40        WHEN STARTS_WITH(event_code, 'T98')        THEN 'informational'