Anomalies paiements: 10 règles SQL prêtes à l’emploi

Les petites agences de voyage gèrent des flux de paiement complexes, avec acomptes, multiples fournisseurs, multi‑devises et remboursements. Résultat, les anomalies passent souvent sous le radar, elles grignotent la marge, augmentent le risque de fraude et compliquent le rapprochement bancaire. Voici 10 règles SQL prêtes à l’emploi pour détecter les écarts les plus courants, directement dans votre entrepôt de données ou vos exports comptables.

Avant de démarrer, ces requêtes utilisent une terminologie générique, facilement adaptable à PostgreSQL, MySQL, SQL Server ou BigQuery. Ajustez simplement les noms de tables et colonnes.

Schéma minimal de référence à adapter

• payments : payment_id, booking_id, created_at, payment_method, status, amount, currency, authorized_amount, captured_amount, auth_at, capture_at, 3ds_status, liability_shift, card_id, channel, mcc, supplier_id
• refunds : refund_id, payment_id, amount, currency, created_at, status
• chargebacks : cb_id, payment_id, amount, received_at, reason_code, status
• bank_movements : bank_tx_id, booking_id, operation_date, value_date, amount, currency, label, counterparty_name, direction, source, reconciled, matched_object_type
• bookings : booking_id, dossier_id, sale_amount, sale_currency, expected_cost, travel_date_start, travel_date_end
• supplier_invoices : invoice_id, booking_id, supplier_id, invoice_amount, currency, invoice_date, status, fx_rate_used
• cards : card_id, type, single_use, mcc_whitelist
• exchange_rates : date, from_currency, to_currency, rate
• cashback_earned : id, booking_id, supplier_id, amount, currency, status, created_at, expected_at, received_at

Astuce, si vous utilisez des IBAN virtuels par dossier, ajoutez booking_id sur bank_movements pour un appariement instantané.

1) Doublon de paiement sur un même dossier

Objectif, repérer deux paiements quasi identiques rapprochés dans le temps.

WITH params AS (SELECT interval '10 minutes' AS window)
SELECT p1.payment_id AS p_first,
       p2.payment_id AS p_second,
       p1.booking_id,
       p1.amount,
       p1.currency,
       p1.created_at AS t1,
       p2.created_at AS t2
FROM payments p1
JOIN payments p2
  ON p1.payment_id < p2.payment_id
 AND p1.booking_id = p2.booking_id
 AND p1.currency = p2.currency
 AND p1.payment_method = p2.payment_method
 AND abs(p1.amount - p2.amount) <= 0.01
 AND p1.created_at BETWEEN p2.created_at - (SELECT window FROM params)
                        AND p2.created_at + (SELECT window FROM params)
WHERE p1.status IN ('authorized','captured')
  AND p2.status IN ('authorized','captured');

Quand l’alerte mérite action, dès que le second paiement provient d’une tentative client répétée ou d’un agent ayant recliqué, déclenchez annulation ou remboursement partiel.

2) Capture partielle tardive ou excessive

Objectif, détecter des captures incomplètes qui traînent ou des captures supérieures à l’autorisation.

WITH params AS (
  SELECT interval '7 days' AS max_delay,
         0.02 AS over_tol,
         0.10 AS under_tol
)
SELECT payment_id,
       booking_id,
       authorized_amount,
       captured_amount,
       auth_at,
       capture_at,
       CASE
         WHEN captured_amount > authorized_amount * (1 + (SELECT over_tol FROM params))
           THEN 'capture_superieure_a_autorisation'
         WHEN now() - auth_at > (SELECT max_delay FROM params)
              AND captured_amount < authorized_amount * (1 - (SELECT under_tol FROM params))
           THEN 'capture_incomplete_tardive'
       END AS anomaly
FROM payments
WHERE status IN ('authorized','partially_captured','captured')
  AND (
    captured_amount > authorized_amount * (1 + (SELECT over_tol FROM params))
    OR (now() - auth_at > (SELECT max_delay FROM params)
        AND captured_amount < authorized_amount * (1 - (SELECT under_tol FROM params)))
  );

Bonnes pratiques, paramétrez des règles spécifiques pour les acomptes et soldes si vous distinguez les deux au niveau du dossier.

3) Remboursements supérieurs aux montants capturés

Objectif, bloquer un classique de la billetterie et des séjours packagés.

WITH r AS (
  SELECT payment_id, SUM(amount) AS total_refunded
  FROM refunds
  WHERE status IN ('processed','succeeded')
  GROUP BY payment_id
)
SELECT p.payment_id,
       p.booking_id,
       p.captured_amount,
       r.total_refunded
FROM payments p
JOIN r ON r.payment_id = p.payment_id
WHERE COALESCE(p.captured_amount, 0) + 0.01 < r.total_refunded;

En cas d’écart, vérifiez s’il existe un avoir fournisseur compensant la différence avant d’ordonner le remboursement.

4) Mouvements bancaires orphelins non rapprochés

Objectif, identifier les lignes bancaires sans correspondance comptable après un délai raisonnable.

WITH params AS (SELECT interval '2 days' AS max_age)
SELECT bank_tx_id,
       operation_date,
       amount,
       currency,
       label,
       counterparty_name
FROM bank_movements
WHERE reconciled = false
  AND operation_date < now() - (SELECT max_age FROM params)
  AND direction IN ('credit','debit')
  AND source IN ('Bank','PSP');

Accélérer la revue, ajoutez des règles d’auto‑matching sur booking_id, référence PSP ou IBAN virtuel.

5) Incohérences de devise et de taux de change

Objectif, repérer des débits bancaires qui s’écartent du montant attendu selon le taux FX du jour.

WITH tol AS (SELECT 0.005 AS rel, 2::numeric AS abs_eur)
SELECT i.invoice_id,
       i.booking_id,
       i.currency AS inv_ccy,
       bm.currency AS bank_ccy,
       i.invoice_amount,
       er.rate,
       abs(bm.amount) AS bank_debit,
       i.invoice_amount * er.rate AS expected_debit,
       abs(abs(bm.amount) - i.invoice_amount * er.rate) AS delta
FROM supplier_invoices i
JOIN bank_movements bm
  ON bm.booking_id = i.booking_id
 AND bm.direction = 'debit'
 AND bm.operation_date BETWEEN i.invoice_date - interval '2 days' AND i.invoice_date + interval '7 days'
JOIN exchange_rates er
  ON er.date = date(i.invoice_date)
 AND er.from_currency = i.currency
 AND er.to_currency = bm.currency
WHERE i.status IN ('approved','paid')
  AND abs(abs(bm.amount) - i.invoice_amount * er.rate)
      > GREATEST((SELECT abs_eur FROM tol), i.invoice_amount * er.rate * (SELECT rel FROM tol));

Utile pour détecter des frais FX bancaires non négociés, des erreurs de devise sur une facture ou une double conversion.

6) Usage anormal de cartes virtuelles

Objectif, prévenir les fraudes et mauvaises pratiques sur les cartes single‑use et les MCC autorisés.

WITH card_usage AS (
  SELECT card_id,
         COUNT(*) FILTER (WHERE status IN ('authorized','captured')) AS used_times
  FROM payments
  GROUP BY card_id
)
SELECT p.payment_id,
       p.card_id,
       p.mcc,
       c.single_use,
       cu.used_times,
       c.mcc_whitelist
FROM payments p
JOIN cards c ON c.card_id = p.card_id
JOIN card_usage cu ON cu.card_id = c.card_id
WHERE c.type = 'virtual'
  AND (
    (c.single_use = true AND cu.used_times > 1)
    OR (c.mcc_whitelist IS NOT NULL AND NOT (p.mcc = ANY(c.mcc_whitelist)))
  );

Si votre whitelist de MCC est stockée en JSON, remplacez la condition ANY par un test d’appartenance JSON.

7) Transactions à risque sans SCA ni liability shift

Objectif, pointer les ventes en ligne élevées sans authentification forte ni transfert de responsabilité.

WITH params AS (SELECT 100::numeric AS min_amount)
SELECT payment_id,
       booking_id,
       amount,
       currency,
       channel,
       3ds_status,
       liability_shift
FROM payments
WHERE payment_method = 'card'
  AND channel IN ('web','phone')
  AND amount >= (SELECT min_amount FROM params)
  AND 3ds_status NOT IN ('authenticated','challenge_authenticated')
  AND liability_shift = false;

Revue conseillée, appliquez 3‑D Secure v2.2 dynamique ou changez de route d’acquisition pour améliorer les autorisations.

8) Factures fournisseurs au‑delà du coût attendu du dossier

Objectif, détecter les dérives coûtantes avant paiement.

WITH inv AS (
  SELECT booking_id, SUM(invoice_amount) AS total_invoiced
  FROM supplier_invoices
  WHERE status IN ('approved','paid')
  GROUP BY booking_id
)
SELECT b.booking_id,
       b.expected_cost,
       inv.total_invoiced,
       inv.total_invoiced - b.expected_cost AS delta
FROM bookings b
JOIN inv ON inv.booking_id = b.booking_id
WHERE abs(inv.total_invoiced - b.expected_cost) > GREATEST(50, b.expected_cost * 0.05);

Astuce, ventilez par type de prestation (aérien, hôtel, DMC) pour repérer l’origine de l’écart en un coup d’œil.

9) Vitesse suspecte sur la même carte

Objectif, flairer les rafales d’essais carte souvent corrélées à la fraude ou à une erreur d’encaissement.

WITH params AS (SELECT interval '10 minutes' AS window, 3 AS max_attempts),
     events AS (
       SELECT payment_id, card_id, created_at
       FROM payments
       WHERE payment_method = 'card' AND status IN ('authorized','refused')
     )
SELECT e1.card_id,
       MIN(e2.created_at) AS first_at,
       MAX(e2.created_at) AS last_at,
       COUNT(e2.payment_id) AS attempts
FROM events e1
JOIN events e2
  ON e2.card_id = e1.card_id
 AND e2.created_at BETWEEN e1.created_at - (SELECT window FROM params)
                        AND e1.created_at + (SELECT window FROM params)
GROUP BY e1.card_id, e1.payment_id
HAVING COUNT(e2.payment_id) > (SELECT max_attempts FROM params);

Si vous journalisez l’IP ou le pays d’émission, ajoutez une alerte pour paniers avec pays divergents dans la même fenêtre de 10 minutes.

10) Cashback attendu non encaissé

Objectif, sécuriser vos marges en vérifiant que les cashbacks B2B ont bien été versés dans les délais contractuels.

WITH params AS (SELECT interval '60 days' AS max_delay)
SELECT booking_id,
       supplier_id,
       SUM(amount) FILTER (WHERE status = 'expected') AS expected,
       SUM(amount) FILTER (WHERE status = 'received') AS received,
       MAX(created_at) AS last_event
FROM cashback_earned
GROUP BY booking_id, supplier_id
HAVING COALESCE(SUM(amount) FILTER (WHERE status = 'received'), 0)
       < COALESCE(SUM(amount) FILTER (WHERE status = 'expected'), 0)
   AND now() > MAX(COALESCE(expected_at, created_at) + (SELECT max_delay FROM params));

Bon à savoir, si vous explorez des programmes externes, lisez cette ressource utile sur les pièges des bonus d’inscription, elle détaille les conditions qui font dérailler les gains annoncés, Les pièges des offres de bienvenue cashback, analyse de 30 programmes.

Résumé des 10 règles et où les brancher

Mise en production en 5 étapes

1. Paramètres, ajustez fenêtres temporelles, seuils monétaires et tolérances FX à vos pratiques.
2. Scheduling, exécutez en continu les règles temps réel et planifiez les autres dans votre orchestrateur préféré.
3. Alerting, envoyez une ligne synthétique vers Slack ou email avec lien vers la source de données.
4. Rapprochement, poussez les anomalies résolues vers votre outil comptable pour tracer la correction.
5. Revue mensuelle, suivez 3 KPI simples, taux d’anomalies par 1 000 transactions, montant brut à risque, délai moyen de résolution.

Avec une plateforme spécialisée tourisme, l’exercice devient bien plus simple. Les exports CSV ou API unifiés d’Elia Pay, l’IBAN français par dossier, les cartes virtuelles et le rapprochement automatique réduisent la friction et rendent ces règles quasi plug and play. Vous gardez le contrôle, avec moins de tableurs et plus d’alertes actionnables.

FAQ

Ces requêtes fonctionnent‑elles sur MySQL ou SQL Server? Oui, la logique est générique. Remplacez les fonctions spécifiques, par exemple interval pour DATEDIFF, les filtres de fenêtre et les agrégats conditionnels selon votre moteur.

Que faire si je n’ai pas booking_id dans les mouvements bancaires? Utilisez les IBAN virtuels par dossier ou, à défaut, combinez fournisseur, fenêtre de date et montant avec une tolérance. L’idéal est d’enrichir vos flux pour injecter une référence de dossier.

Comment éviter les faux positifs sur la capture partielle? Définissez des règles d’exemption pour les dossiers avec acompte planifié. Taggez vos paiements en « deposit » et « balance » et appliquez des seuils distincts.

Puis‑je brancher ces règles dans un outil BI? Oui. Créez des vues matérialisées ou des modèles dans votre BI puis des alertes. La plupart des outils acceptent des rafraîchissements planifiés et des webhooks de notification.

Faut‑il arrêter un paiement dès l’alerte? Non, privilégiez une approche en deux temps, alerte puis revue humaine sur les montants élevés, blocage automatique seulement pour les doublons évidents, les remboursements excessifs et les cartes virtuelles hors politique.

Passez de la détection à l’action avec Elia Pay

Vous souhaitez réduire les anomalies tout en fluidifiant la gestion des paiements, du rapprochement et des remboursements, sans alourdir vos équipes, Elia Pay rassemble IBAN français, cartes virtuelles, rapprochement bancaire automatisé, prévention de fraude et intégrations tourisme, avec jusqu’à 1 % de cashback sur vos achats éligibles.

Réservez une démonstration, nous vous montrons comment brancher ces 10 règles à vos exports Elia et comment gagner du temps dès le premier mois.