Project Name
Catching 100% of Fuel-Card Billing Errors Before Payment with Informatica Cloud
The client is a fleet operator running a fuel-card programme that issues cards to drivers across its vehicle fleet. Its business depends on tight control of fuel spend, which is billed nightly by an external card network and settled by direct debit. In parallel, the fleet’s own telematics independently records every litre dispensed at the pump. With fuel among its highest operating costs, the client wanted every settlement verified against what was actually dispensed before payment cleared. Manual, periodic reconciliation could no longer provide that assurance, which led to this engagement.
- Errors surfaced only after payment: Reconciliation was manual and periodic, so disputed charges were found only after the direct debit had cleared. Finance spent its time recovering money instead of preventing losses.
- Missing transactions went unseen: Charges billed with no pump record, and fuel dispensed but never billed, were often the costliest errors. A standard match-up silently dropped exactly these records.
- Duplicate charges slipped through: The same transaction appearing twice in one settlement file could not be caught by comparing the two sources. These duplicates also inflated the matched count and distorted the results.
- Re-runs were unsafe: Re-running a failed night either crashed on duplicate records or doubled the night's figures. A failed run could not simply be repeated.
- Failed jobs reported success: A run that rejected every record still completed without raising an error. Problems stayed hidden because nobody investigates a job that appears successful.
- No reports: When nothing was wrong, no report was produced at all. Finance could not tell whether everything balanced or the job never ran.
Ksolves built a nightly reconciliation pipeline on Informatica Cloud. It compares the full settlement file with pump records, grades every transaction, and reports every night, with alerts only when something is wrong.
- Nightly Taskflow at 22:00: A Cloud Application Integration taskflow runs on a self-managed Secure Agent every night before the direct debit clears. It replaces the manual periodic checks.
- Full Outer Comparison with Null-Side Classification:The Joiner is set to Full Outer with sorted input, so a row that exists on only one side survives with nulls on the other. An Expression tests those nulls to separate "billed, no record" from "dispensed, not billed".
- Aggregator Occurrence Count Before the Join: Grouping on transaction reference collapses the file to one row per reference and emits an occurrence count. This count is tested before any amount comparison, so duplicates are flagged, and the join never fans out.
- Upsert Keyed on Run + Transaction: Both row-level targets, recon_result and recon_exception, use Upsert on the composite key of run and transaction. A replay overwrites the same night, and table counts stay unchanged.
- Derived Run Status with Fail-Loud Handling: The summary mapping derives run status from the exception count instead of hard-coding it. Every data task fails the taskflow rather than passing silently.
- Summary on Every Run, Alert Only on Exceptions: m_recon_run_summary writes one recon_run row per night. The pr_recon_summary process publishes it to a Summary queue every run, and to an Alert queue only when exceptions exist.
Technology Stack
| Category | Technology |
|---|---|
| Platform | Informatica Cloud Data Integration |
| Integration | Informatica Cloud Application Integration |
| Database | MySQL + Reconciliation DB |
| Infrastructure | Informatica Secure Agent |
- Five-Outcome Classification: Every transaction is graded as matched, amount mismatch, duplicate, billed-without-record, or dispensed-without-bill.
- Zero Transactions Dropped: The full outer comparison keeps every transaction, including the single-sided records a standard join silently drops.
- Two-Way Leakage Detection: Overbilling from duplicate charges and underbilling from unbilled fuel are caught in the same run.
- Zero Double Counting on Replay: Failed nights can be re-processed safely, with figures and table counts unchanged.
- 1:1 Parity with SQL: The reconciliation logic was validated against an independent SQL reference and matched it row for row.
- Every Run Reported: A summary is published after every execution, and alerts are raised only when exceptions exist.
Client Testimonial
“We now get a definitive answer every night before the debit clears, and a quiet alert channel finally means a balanced night, not a job that never ran.”
– Head of Finance Operations, fleet operator.
Ksolves replaced slow, after-the-fact manual checks with a nightly Informatica Cloud pipeline that reconciles every fuel-card transaction against telematics pump records before the direct debit clears. Every transaction is graded into one of five outcomes, with none dropped, so overbilling and unbilled fuel are caught in the same run. Replay-safe processing and fail-loud handling give finance an auditable answer every night, and a quiet alert channel now reliably means a balanced night. The same approach can be extended to other high-volume settlements such as tolls and maintenance billing.
Stop Billing Errors Before Payment, Not After.
