Got it. I can draft a full end-to-end test plan that covers POS + QR ingestion into core_transactions, Base-II (switch dump) file creation/parsing, reconciliation, exceptions, and reports.

Before I finalize it, two quick clarifications to keep the plan aligned with the actual flow:

The current sync path in code inserts into core_transactions from pos_settlements (POS) and transactions (QR), not directly from pos_transactions. Do you want the test plan to include the upstream step that creates pos_settlements from pos_transactions, or focus on the sync layer as implemented?
Should the plan include UI checks (LiveView/admin pages) or only API + DB validation?
If you’re OK with those assumptions, here is a complete draft test plan you can start using now. I’ll tailor it once you confirm the two points.

Test Plan: Settlement & Reconciliation End‑to‑End

References

Requirements: Settlement_reports_completion.txt
Switch dump parser format: switch_dump_parser.ex
QR scheme parser format: qr_scheme_dump_parser.ex
Core sync flow: core_transaction_sync.ex
Sample data: sample-table-data.txt
0) Preconditions & Test Data Setup
0.1 Database baseline

Ensure MySQL databases are accessible and migrations are applied.
Confirm tables exist: pos_transactions, pos_settlements, transactions, core_transactions, dump_files, switch_dump_records, qr_scheme_dump_records, reconciliation_exceptions.
0.2 Seed key reference data

BIN table for masked PAN → card_type_id (required for card_type resolution).
MMS rate data for interchange and MDR (or stubs with known values).
TID Master and merchant metadata for at least one MID/TID.
0.3 Test dataset

Create:
3 POS records that will be settled (success).
2 POS records that are void.
3 QR records (success).
1 QR refund.
1 POS duplicate (same RRN+TID in two dumps).
If testing the upstream POS settlement step, include 3–5 entries in pos_transactions and create the matching pos_settlements.
Expected

All sample rows are visible for verification before each test run.
1) POS → core_transactions Sync (POS path)
Goal: Verify transactions move into core_transactions with correct fields, counts, and status.

Steps

Insert/prepare POS settled rows in pos_settlements with a unique settlement_batch_id.
Trigger core sync (POS) via API or worker entry point.
Verify SyncLog status is completed and verified.
Verify rows in core_transactions:
source_type = POS
rrn, tid, auth_number, stan, invoice_number, batch_number match source
settlement_status = unmatched (initial)
card_type_id is backfilled if BIN matches
mdr_amount, vat_amount, net_settlement_amount populated
Expected

All eligible POS settled rows are inserted once.
No duplicates.
BIN and MDR/MCC logic applied where data exists.
2) QR → core_transactions Sync (QR path)
Goal: Verify QR transactions move into core_transactions correctly.

Steps

Insert/prepare QR rows in transactions with settlement_batch_id.
Trigger core sync (QR).
Verify rows in core_transactions:
source_type = QR
rrn, tid, auth_number, amount, scheme_name populated
settlement_status = unmatched
card_type_id is set if masked_pan available
Verify SyncLog counts & totals match expected.
Expected

QR rows inserted exactly once.
SyncLog marked verified when totals match.
3) Sync Integrity & Verification Checks
Goal: Validate integrity checks and mismatch handling.

Steps

Trigger POS sync with incorrect verification counts.
Trigger QR sync with incorrect total amount.
Attempt re-sync on the same batch_id.
Expected

SyncLog status = mismatch for incorrect verification.
Re-sync returns already_synced and no new rows inserted.
4) Base-II (Switch Dump) File Preparation Test
Goal: Prepare a valid sample Base-II file and verify parsing.

Sample file (CSV)

Steps

Save the file locally (e.g., test_switch_dump_20260306.csv).
Run the parser job or upload API.
Verify rows in switch_dump_records:
match_status = unmatched
dump_file_id linked
required fields parsed
Verify any invalid row is skipped and logged.
Expected

All valid rows parsed.
Errors counted for invalid rows.
5) Switch Dump Import → Reconciliation Trigger
Goal: Verify dump import queues reconciliation worker.

Steps

Process a switch dump file (manual upload or job).
Confirm dump_files.status transitions to processed.
Confirm reconciliation worker is enqueued.
Expected

dump_files status = processed
switch_dump_records created
reconciliation worker scheduled
6) Reconciliation Matching (1:1 Match)
Goal: Validate matching logic using RRN + TID + amount + date + auth.

Steps

Ensure core_transactions contains matching records for switch dump rows.
Run reconciliation for dump_file.
Verify:
switch_dump_records match_status = matched
matched_core_transaction_id populated
core_transactions.switch_settled_date updated
core_transactions.settlement_status = matched
Expected

Exact 1:1 matches are marked matched.
7) Reconciliation Exceptions (Types 5.1–5.4)
Goal: Validate exception generation.

7.1 Type 5.1 (In dump, not in core)

Create switch dump record with no matching core txn.
Run reconciliation.
Expect ReconciliationException with type 5.1.
7.2 Type 5.2 (In core, not in dump)

Create core transaction older than today with no switch_settled_date.
Run reconciliation.
Expect exception 5.2 and core_transactions.exception_type = 5.2.
7.3 Type 5.3 (Void matched in dump)

Create core transaction type VOID with matching dump row.
Run reconciliation.
Expect exception 5.3.
7.4 Type 5.4 (Duplicate in dump)

Create prior-day dump record with RRN+TID, then include same combo in current dump.
Run reconciliation.
Expect exception 5.4.
Expected

Exception records created per scenario.
core_transactions moved to exception_hold when applicable.
8) Refund Handling
Goal: Validate refund updates in core_transactions.

Steps

Include REFUND row in switch dump.
Run reconciliation.
Verify core_transactions refund_status = true and refund_date set.
Expected

Refund status properly updated on original transaction.
9) Settlement MIS Generation (Report Data)
Goal: Validate MIS outputs after reconciliation.

Steps

Ensure matched transactions exist with no holds or exceptions.
Run MIS generation for the settlement date.
Verify MIS record totals:
Gross = sum transaction_amount
MDR + VAT computed
Interchange_fee used for cost
Verify MIS item grouping by merchant + scheme + card type.
Expected

MIS record created with correct item aggregation.
10) Finance Approval Workflow (if UI/API implemented)
Goal: Validate approval and rejection flows.

Steps

Approve MIS as L1, then L2.
Attempt L2 approval without L1.
Reject MIS and verify status.
Expected

Correct status transitions and access control enforcement.
11) Exception Release → Next Day Settlement
Goal: Validate release flow for exceptions.

Steps

Mark an exception as released.
Run next-day MIS generation.
Verify released transaction appears in MIS.
Expected

Released exceptions are reintroduced in settlement.
12) Negative & Edge Cases
Missing required fields in dump (RRN, TID, amount).
Duplicate core sync batches.
Missing BIN match (should not block).
Missing MDR/Interchange (should still sync, logs warning).