Settlement ledger

1-- core.walmart.settlement_ledger
2-- What Walmart actually paid you: the settlement reconciliation file turned into
3-- a ledger, one row per order line and transaction type per payout.
4--
5-- Grain: settlement date x seller x posted date x purchase order x purchase
6-- order line x SKU x transaction type. Lines that belong to no order (advertising,
7-- WFS storage, account adjustments) keep NULL order and SKU columns rather than
8-- being dropped; they are real deductions from the deposit.
9--
10-- WHY THIS EXISTS. core.walmart.sales_over_time says what customers ordered.
11-- Between that and the deposit sit Walmart's commission (its referral fee), WFS
12-- fulfilment and storage fees, advertising, return shipping charges and
13-- adjustments. None of them is on the order. This is also the only model where
14-- refunds land on the day the money moved (posted_date).
15--
16-- SIGNS ARE WALMART'S. Sales positive, commission, fees and refunds negative.
17-- net_payable is a plain SUM(amount), and summed over a settlement date it should
18-- equal the file's total_payable, the amount deposited
19-- (dbt/tests/assert_walmart__settlement_ties_to_deposits.sql).
20--
21-- A refund is identified by transaction_type, not by a negative amount: fees are
22-- negative too. Refund lines also reverse commission (Walmart returns its
23-- referral fee on a refund), and that reversal stays in the commission column.
24--
25-- unclassified_amount and other_amount should be small. Walmart adds amount
26-- types without notice; when it does, the money is still in net_payable but in
27-- no named column.
28--
29-- Depends on: staging.walmart.recon_report
30
31SELECT
32    settlement_date,
33    walmart_seller,
34    posted_date,
35    currency,
36    purchase_order_id,
37    purchase_order_line,
38    customer_order_id,
39    sku,
40    transaction_type,