fleet-payments-plsql

A commercial fuel-card and cross-border B2B payments platform whose business logic lives in Oracle PL/SQL, with a .NET 9 data layer that does nothing but call it.

Modelled on the shape of a real fleet-payments programme: card issuing and acceptance, real-time authorization at the pump, velocity and fraud controls, merchant onboarding and underwriting, nightly settlement and interchange, volume-tier rebates and invoicing, cross-border payables with FX fixings, dispute and chargeback handling, regulatory filings, and the overnight batch estate that reconciles all of it. 7,750 lines of PL/SQL across 11 packages and 117 routines, over a 42-table schema with 59 declared foreign keys, CHECK domains and indexes.

Why this is a hard migration

The point of this repo is that the routines are not all the same kind of problem, and a migration that treats them as if they were will ship green and wrong. Run the analyzer and the estate splits five ways:

Disposition	Count	What it means
AUTO_MIGRATABLE	67	No behavioural hazard — port it to a repository method
AUTO_WITH_RUNTIME_LIB	21	Portable, but only against an emulation helper
REFACTOR_REQUIRED	17	Portable only after a redesign
MANUAL_REVIEW_REQUIRED	4	Static conversion is unsafe — a human reads it first
DATABASE_BOUND	8	The behaviour belongs to the database and cannot leave it

12 routines are held — no auto-port task is emitted for them at all. pkg_audit_session is declared AUTHID CURRENT_USER and keeps per-session operator context in package variables, and its two writers carry PRAGMA AUTONOMOUS_TRANSACTION so an audit row survives the rollback of the business transaction that triggered it — none of which a pooled application connection can reproduce. pkg_regulatory_reporting has a PIPELINED table function consumed from SQL by the filing extract views, and two reads across a @fx_hub_link database link. Four more are held because their SQL is assembled at run time: a nightly loader whose staging table is named per acquiring country, a treasury reprice against a candidate rate table, a revaluation cursor opened over a dated snapshot table, and the retention purge.

The rest is the ordinary weight of a twenty-year-old estate: row-by-row cursor loops with WHERE CURRENT OF that should be one set-based statement, BULK COLLECT/FORALL batches whose performance characteristic has to survive the port, a FOR UPDATE SKIP LOCKED queue consumer, COMMIT inside procedures, SQL%ROWCOUNT driving branches, and user-defined exceptions that a caller can already see. The rules worth watching in a demo are pkg_rebate_billing.tiered_rebate — a genuine marginal tier walk, where each slice of volume earns its own rate rather than the top rate applying to everything, which is the rule most often got wrong when this logic is reimplemented by hand, and always in the customer's favour — and pkg_interchange.rate_for, which resolves a rate through network+MCC+country, then network+MCC, then network-wide, then a programme floor.

The starting state

Every method in src/FleetPay.Data/ is a marshalling shim: 116 live CommandType.StoredProcedure call sites across 11 repositories, and not one line of business logic on the .NET side. This is the whole of AuthorizeAsync, the entry point to the decisioning path:

p.Add("p_card_id", cardId);
// … ten more …
p.Add("p_reason", dbType: DbType.String, direction: ParameterDirection.Output, size: 60);

await _db.ExecuteAsync(new CommandDefinition(
    "pkg_card_authorization.authorize", p,
    commandType: CommandType.StoredProcedure, cancellationToken: ct));

Contents

sql/01_schema.sql                    42 tables, FKs, CHECK domains, a GTT, sequences, indexes
sql/02_pkg_card_authorization.sql    the real-time decision path
sql/03_pkg_velocity_fraud.sql        rolling velocity windows and fraud scoring
sql/04_pkg_merchant_onboarding.sql   KYB, underwriting, pricing, activation
sql/05_pkg_settlement.sql            the nightly batch
sql/06_pkg_interchange.sql           rate resolution and fee arithmetic
sql/07_pkg_rebate_billing.sql        marginal tier rebates, invoicing, payments
sql/08_pkg_fx_crossborder.sql        FX fixings and cross-border payables
sql/09_pkg_disputes.sql              dispute and chargeback lifecycle
sql/10_pkg_regulatory_reporting.sql  filings — pipelined and over a db link
sql/11_pkg_audit_session.sql         autonomous audit writer, invoker's rights
sql/12_pkg_batch_operations.sql      the overnight batch estate

src/FleetPay.Data/                   11 repositories — 116 delegating methods
src/FleetPay.App/                    console host that wires them together

Running it

1. Open in CogniDev Workbench (Open from GitHub → cognidevwb/fleet-payments-plsql).
2. Run Understand.
3. Run Move stored procedures to .NET.

The disposition gate runs first and holds the 12 routines it cannot safely hand to the develop loop, with the reason for each. The rest are ported one task at a time into the repository that already calls them, compiled after each one.

dotnet build -c Release   # no database required