Billing reconciliation

Companies with more than one billing account (per group company, product line or subsidiary) stack the accounts into a single table with a source column. The invoice id is only unique within the account: the key is source + id. Every new account goes into the same table on the day it is connected.

Gateways generate more than one record for the same charge (duplicate copy, reissue, retry). Keep one per charge in this order: paid, chargeback, refunded, in protest, pending, expired; on a tie, the most recent. That way a paid duplicate copy doesn't coexist with the expired original.

Each card attempt becomes a charge row, and an invoice can have several declines before it is paid. Billings and cash count the document (billing invoice, e-commerce order), never the attempt. Declines go into their own table, one row per attempt, with a standardized decline code.

Month billings = gross cash from the month's invoices + what remains open on them. Each number has its own date: billings by accrual or issue date, cash by payment date, open by due date. If the identity doesn't hold in a month, there is a duplicate or a lost invoice, and the report doesn't go out.

Card money lands in the balance weeks after payment, and installment sales land per installment. Build receivables by transaction x installment with a status of settled or to be received. Net = amount minus fee, advance fee and anti-fraud fee. In the projection, remove sales with a chargeback or refund. Pix (instant payment) and boleto (bank payment slip) with no recorded payout land on the next bank business day, using a bank holiday calendar (movable holidays computed from Easter).

The ERP stores the billing invoice id in a custom field on the transaction and the billing customer id on the customer record. The product's invoice stores the gateway charge id (external_id). Match on those ids. To find sold but not received, bring only the paid side of the gateway into the join and list what is left unmatched.

Gateways have a charges table and a balance transactions table, which repeats every payment and adds fees and transfers. Revenue comes from the charges table. In Stripe, link charge to subscription through the invoice lines: the ready-made charges-by-subscription table only has the successful ones.

Stripe, Pagar.me and *_cents fields come in cents: divide by 100 once, in the first layer. Sales order lines in some ERPs come with an inverted sign and must be negated. Reversals and refunds go in as negative in cash and positive in the refunds table, never both in the same total.

When sales are issued by more than one group company, each report comes from one token per company and carries a company column. A global sale code that appears in both companies is a duplicate. If the issuing company changed on a date, the rule for which company applies is by a declared cutoff date.

The reconciliation doesn't delete or fix rows. It publishes one table per type of problem: approved outside the tax invoice issuer, approved with no tax invoice issued, product missing from the mapping, refunded with an active tax invoice, amount mismatch between billing and tax invoice, duplicate across companies. An empty table is the goal.