Reconstruct cancelled-check return-then-re-debit ledger and net balance impactReconciliation & Settlement

Task prompt

CX escalated a customer complaint in #banking: their check was debited, credited back after the processor returned it, and then debited again. Finance needs to confirm that this customer ultimately paid once, and Ops wants the whole-book exposure before any further corrections are posted. Use the Finoco SQL warehouse to work out how customer-issued checks and their posted money movements are represented. Find every check with an original debit and a positive ledger entry identified as a refunded or returned check. Include cases with no corrective re-debit yet as well as cases with one or several re-debits. Reconstruct the ledger check by check; a shared transfer reference does not make a movement for a different dollar amount part of that check. Classify each check as fully corrected when exactly one check debit remains, under-corrected when a return credit is still outstanding, or over-corrected when the customer has been debited more than once. Report the number of checks in each bucket, the earliest and latest posting dates across the reconstructed legs, gross dollars moved, outstanding under-credit dollars, and outstanding over-debit dollars. Define net_exposure_usd as outstanding_undercredit_usd minus outstanding_overdebit_usd. Post an incident memo to Slack #banking with the totals, a bucketed cohort showing account, check, check amount, and current net, and the exact corrective action for each non-fully-corrected check (re-debit the outstanding credit for under-corrected checks; release or credit the excess debit for over-corrected checks). Use the Confluence check-issue transfer lifecycle SOP to support a likely control-failure diagnosis and a specific preventive guard with its operational tradeoff. Keep documented facts separate from inference. The final response is consumed by an automated review step. Return only one JSON object with exactly these scalar fields and no account list: pattern_check_count, fully_corrected_count, under_corrected_count, over_corrected_count, window_start (YYYY-MM-DD), window_end (YYYY-MM-DD), gross_dollars_moved, outstanding_undercredit_usd, outstanding_overdebit_usd, net_exposure_usd, recommendation (text).