Receivables reconciliation

Match on the id that exists on both sides: the transaction id at the processor, the boleto id on the collection platform, the order number at the gateway. When the ERP does not store that id, do it in two steps: first settlement date and gross amount, pairing one to one (each statement line matches a single installment); then, for what is left, normalized name and amount within up to 3 days. A different name with the same amount and date is not posted automatically: it goes to manual review.

Each pair gets a status: matched (difference within tolerance, for example up to R$ 1.00), divergent (both values and the difference side by side), statement only, and core or ERP only. A divergence is not a pipeline error: it is a real disagreement between systems, and the table exists to show it. If the check is exact, with no tolerance, rounding also shows up as divergence, and that must be written down.

What moves the balance is the date the money was settled or transferred, not the payment or order date. Convert the date to America/Sao_Paulo before matching: the ERP often posts card receipts one day before the date the UTC spreadsheet shows. Keep the displayed date for the user and use the converted date only for matching.

A duplicate is the same customer, with the same transaction id and the same amount, with two or more completed payments. For recurring fee or subscription charges, a second charge within 2 days of the first counts as a duplicate. Refunds are excluded, and so are test or validation entries with a token amount (up to R$ 1).

The core records success or failure in its own column, but the processor's raw response carries the real status. Read the status from the response, normalize it (hard and soft failures become error; success becomes completed) and list every day the payments where the two disagree. That is where the contract posted without money, or charged again, shows up.

Build the list of expected days in a rolling window (for example, the last 35 days, skipping the 2 most recent, which may still arrive) and mark each day as present or missing, with row and file counts. Save the result in append mode and alert when a day is missing. A day without a file leaves processor revenue and cost below the invoice.

In a lending core, each payment splits into principal, interest, late fees and fees. The components must add up to the amount paid; when the file carries a residual, allocate it to a declared component (for example, principal) and flag the line. Reconcile the total with the processor and the components with the portfolio.

During a processor or collection platform migration, the same payment appears in both sources. Stacking the two counts the charge twice. Deduplicate on the transaction id preferring the paid record, and cut by date: the old source up to the day before the switch, the new one from then on, excluding the day processed in both.

The receivable carries the expected account and the posting carries the account the money actually landed in; filter by the posting account. Add up only the revenue category: payment method fees, refunds and transfer fees are separate entries. Refunds and disputes use the net amount read straight from the statement line, never recalculated from gross.

Before creating or settling any entry through the connector, list the day in the ERP and compare description and amount, counting occurrences. Create only what is missing; for what exists but is open, just settle it. Creation is often asynchronous and the connector has no delete: a duplicate entry can only be removed by hand. Then check the day: paid receivables equal the statement total, open equals zero.