Requêtes de vérification de disparition de transactions

Ces requêtes doivent être exécutées sur la base de données en production du client afin de prendre connaissance d'une possible disparition de transactions.

Vérification de toutes les écritures historisées et leurs opérations manquantes sur une période

SELECT

  distinct ho.numero_operation AS numero_operation, 
  ho.date_created AS date_operation, 
  he.numero_operation AS historique_ecriture_numero_operation, 
  he.date_ecriture

FROM

  historique_ecriture he

LEFT JOIN

  historique_operation ho ON he.numero_operation = ho.numero_operation

WHERE

  ho.numero_operation IS NULL 
  AND EXTRACT(YEAR FROM he.date_created) in ('2023','2024')

ORDER BY

  he.date_ecriture DESC, 
  ho.date_created DESC;

Nombre d'operations par mois sur une année données

SELECT

  EXTRACT(YEAR FROM o.date_created) AS Annee, 
  EXTRACT(MONTH FROM o.date_created) AS Mois, 
  COUNT(*) AS Nombre_transaction

FROM

  operation o

WHERE

  EXTRACT(YEAR FROM o.date_created) = '2022'

GROUP BY

  EXTRACT(YEAR FROM o.date_created), 
  EXTRACT(MONTH FROM o.date_created)

ORDER BY

  Annee,
  Mois;

Nombre d'opérations historisées par mois sur une année

SELECT

  EXTRACT(YEAR FROM ho.date_created) AS Annee, 
  EXTRACT(MONTH FROM ho.date_created) AS Mois, 
  COUNT(*) AS Nombre_transaction

FROM

  historique_operation ho

WHERE

  EXTRACT(YEAR FROM ho.date_created) = '2023'

GROUP BY

  EXTRACT(YEAR FROM ho.date_created), 
  EXTRACT(MONTH FROM ho.date_created)

ORDER BY

  Annee,
  Mois;

Liste historique opérations sans écritures sur une années

SELECT

  ho.date_created, 
  ho.numero_operation AS hist_operation, 
  he.numero_operation as hist_ecriture_numOperation, 
  he.date_ecriture

FROM

   historique_operation ho

LEFT JOIN

  historique_ecriture he ON ho.numero_operation = he.numero_operation

where

  EXTRACT(YEAR FROM ho.date_created) = '2020'
  and he.numero_operation is null

ORDER BY

  ho.date_created DESC,
  he.date_ecriture DESC;

Historique opérations par mois et sur un intervalle d'années

SELECT

  EXTRACT(YEAR FROM ho.date_created) AS Annee, 
  EXTRACT(MONTH FROM ho.date_created) AS Mois, 
  COUNT(*) AS Nombre_transaction

FROM

  historique_operation ho

WHERE

  EXTRACT(YEAR FROM ho.date_created) IN ('2023', '2024')

GROUP BY

  EXTRACT(YEAR FROM ho.date_created), EXTRACT(MONTH FROM ho.date_created)

ORDER BY

  Annee, Mois;

Les opérations sans leurs écritures

SELECT

  o.id, o.date_created, 
  o.numero_operation AS operation, 
  e.operation_id as ecriture_operation_id, 
  e.date_ecriture

FROM operation o LEFT JOIN

  ecriture e ON o.id = e.operation_id

where

  e.operation_id is null

ORDER BY

  o.date_created, 
  e.date_ecriture;

Vérification de toutes les opérations historisées ayant un statut <validé> et qui sont sans écritures

SELECT

  ho.numero_operation AS numero_operation, 
  ho.date_created AS date_operation, 
  he.numero_operation AS hist_ecriture_numOperation, 
  he.date_ecriture, 
  ho.statut AS statut

FROM

  historique_operation ho

LEFT JOIN

  historique_ecriture he ON ho.numero_operation = he.numero_operation

WHERE

  ho.statut = 'VA' 
  AND he.numero_operation IS NULL

ORDER BY

  he.date_ecriture ASC, 
  ho.date_created ASC;

Liste des opérations et leurs nombres d'écritures

SELECT

  EXTRACT(YEAR FROM o.date_created) AS Annee, 
  EXTRACT(MONTH FROM o.date_created) AS Mois, 
  COUNT(*) AS Nombre_transaction,
  (SELECT 
      COUNT(*) 
   FROM 
       ecriture e
   WHERE 
       EXTRACT(YEAR FROM e.date_created) = '2024' 
       AND EXTRACT(MONTH FROM e.date_created) = EXTRACT(MONTH FROM o.date_created)
  ) AS nombre_ecriture

FROM

  operation o

WHERE

  EXTRACT(YEAR FROM o.date_created) = '2024'

GROUP BY

  EXTRACT(YEAR FROM o.date_created), EXTRACT(MONTH FROM o.date_created)

ORDER BY

  Annee, Mois;

Liste des opérations historisées et leurs nombres d'écritures

SELECT

  EXTRACT(YEAR FROM ho.date_created) AS Annee, 
  EXTRACT(MONTH FROM ho.date_created) AS Mois, 
  COUNT(*) AS Nombre_transaction,
  (SELECT 
      COUNT(*) 
   FROM 
       historique_ecriture he 
   WHERE 
       EXTRACT(YEAR FROM he.date_created) = '2023' 
       AND EXTRACT(MONTH FROM he.date_created) = EXTRACT(MONTH FROM ho.date_created)
  ) AS Nombre_historique_ecriture

FROM

  historique_operation ho

WHERE

  EXTRACT(YEAR FROM ho.date_created) = '2023'

GROUP BY

  EXTRACT(YEAR FROM ho.date_created), EXTRACT(MONTH FROM ho.date_created)

ORDER BY

  Annee, Mois;