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;