Closing the gap between accounting and the DSN

By AMO
December 18, 2020
The involvment of a head accountant triggered one of my missions. There was a discrepancy of several k€ between what he reported to URSSAF, via the DSN, and the figures he had in the accounting records.
“It’s up to you to see: everything comes from HR Access! There must be a way to understand where these deviations come from.”
“I’m giving you the accounting codes that receive items intended for URSSAF, and you tell me.”
He wasn’t wrong. How to proceed?
Exploit ZXMM, ZX4K.
Identify discrepant categories. Reintroduce them artificially.
Iterate.
All of this using SQL, to speed up the process, to verify if reintroducing a category present on one side but absent on the other reduces the overall discrepancy.
A bit of brainstorming, and in the end, the discrepancy was less than 70€ (there were still a few anomalies in the records).
Here’s the SQL:
with dsn as (
select a.nudoss,
a.matric,
sum(b.monta2) as "TotalDSN"
from zx00 a, zxmm b, zx5v c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.cptdsn in ('78X03','81X022')
and c.validt = 'D' 
group by a.nudoss, a.matric
order by a.nudoss, a.matric 
),
compta as (
select a.nudoss,
a.matric,
sum(case b.debcre when 'C' then -1 * b.amount else b.amount end ) as "TotalCompta"
from zx00 a, zx4k b, zx5v c, zx0m d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and a.perpai = 'MT202005'
and c.validt = 'D'
and (b.idglar in ('641100', '641101')
or (b.idglar like '6413%')
or (b.idglar like '6417%'))
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
c641100 as (
select a.nudoss,
a.matric,
sum(case b.debcre when 'C' then -1 * b.amount else b.amount end ) as "C641100"
from zx00 a, zx4k b, zx5v c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and c.validt = 'D'
and b.idglar = '641100'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
c641101 as (
select a.nudoss,
a.matric,
sum(case b.debcre when 'C' then -1 * b.amount else b.amount end ) as "C641101"
from zx00 a, zx4k b, zx5v c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and c.validt = 'D'
and b.idglar = '641101'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
-- En prenant tous ces comptes, on corrige les erreurs rencontrées sur la prime PEPA
-- Avec les rubriques PEB et PEY
c6413xx as (
select a.nudoss,
a.matric,
sum(case b.debcre when 'C' then -1 * b.amount else b.amount end ) as "C6413xx"
from zx00 a, zx4k b, zx5v c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and c.validt = 'D'
and b.idglar like '6413%'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
c6417xx as (
select a.nudoss,
a.matric,
sum(case b.debcre when 'C' then -1 * b.amount else b.amount end ) as "C6417xx"
from zx00 a, zx4k b, zx5v c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and c.validt = 'D'
and b.idglar like '6417%'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcPVS as (
select a.nudoss, a.matric, sum(c.monsal) as PVS
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'PVS'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcEXJ as (
select a.nudoss, a.matric, sum(c.monsal) as EXJ
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'EXJ'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcISK as (
select a.nudoss, a.matric, sum(c.monsal) as ISK
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'ISK'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcRSP as (
select a.nudoss, a.matric, sum(c.monsal) as RSP
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'RSP'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcIRA as (
select a.nudoss, a.matric, sum(c.monsal) as IRA
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'IRA'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcIAR as (
select a.nudoss, a.matric, sum(c.monsal) as IAR
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'IAR'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
-- KCL Allocation de reclassement
drcKCL as (
select a.nudoss, a.matric, sum(c.monsal) as KCL
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'KCL'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
-- IRE Abattement absence stage HR
drcIRE as (
select a.nudoss, a.matric, sum(c.monsal) as IRE
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'IRE'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
-- UMR et UMQ qui se déclenchent en même temps => double le montant pris
-- Attention: pour ces 2 rubriques, on prends la base et pas le montant salarial
drcUMR as (
select a.nudoss, a.matric, sum(c.nbrbas) as UMR
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'UMR'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcUMQ as (
select a.nudoss, a.matric, sum(c.nbrbas) as UMQ
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'UMQ'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcIRJ as (
select a.nudoss, a.matric, sum(c.monsal) as IRJ
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'IRJ'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
)
,
drcINX as (
select a.nudoss, a.matric, sum(c.monsal) as INX
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'INX'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcIRD as (
select a.nudoss, a.matric, sum(c.monsal) as IRD
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'IRD'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
)
,
drcISK as (
select a.nudoss, a.matric, sum(c.monsal) as ISK
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'ISK'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcAP2 as (
select a.nudoss, a.matric, sum(c.monsal) as AP2
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'AP2'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
drcAP4 as (
select a.nudoss, a.matric, sum(c.monsal) as AP4
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'AP4'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
-- Il y a eu une correction sur la AP7 entre la paie d'avril et la paie de mai
-- D'où la limitation prise sur l'horodatage des paies 
-- avec "a.tiload < to_date('01/05/2020','dd/MM/YYYY')"
drcAP7 as (
select a.nudoss, a.matric, sum(c.monsal) as AP7
from zx00 a, zx5v b, zx8k c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.tiload < to_date('01/05/2020','dd/MM/YYYY')
and a.perpai = 'MT202005'
and b.validt = 'D'
and c.codrub = 'AP7'
group by a.nudoss, a.matric
order by a.nudoss, a.matric
),
-- Attention: ici, il s'agit de neutraliser des EV saisis pour corriger des cotisations, avec du rappel sur l'année antérieure
-- d'où la clause "b.perval < '202001'" (janvier 2020). Veiller à la maintenir ou autrement dit, se méfier d'un chercher / remplacer
-- mal formé
rappelCotisEV as (
select a.nudoss,
b.perpai,
b.codrub,
sum(b.nbrbas) as "CotisEvA-1"
from zx00 a, zx8k b, zx5v c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.perpai = 'MT202005'
and b.perval < '202001'
and c.validt = 'D'
and b.codrub in ('UML','UMX')
and b.source = 'V'
group by a.nudoss, b.perpai, b.codrub
)
select zx00.perpai "Période de paie",
zx37.etabli "Etablissement",
zx00.nudoss,
zx00.matric "Matricule",
zx00.nomsal "Nom", 
zx0m.typcon "Type de contrat",
zx0m.natcon "Nature de contrat",
coalesce(dsn."TotalDSN",0) as "Total DSN (78X03,81X022)",
coalesce(c641100."C641100",0) as "C641100",
coalesce(c641101."C641101",0) as "C641101",
coalesce(c6413xx."C6413xx",0) as "C6413xx",
coalesce(c6417xx."C6417xx",0) as "6417xx",
coalesce(c641100."C641100",0)
+ coalesce(c641101."C641101",0) 
+ coalesce(c6413xx."C6413xx",0)
+ coalesce(c6417xx."C6417xx",0)
as "Total Compta (pour CTL)", -- Que tous les comptes utilisés au niveau d'Oracle sont bien repris ici
coalesce(compta."TotalCompta",0) as "Total Compta (Oracle)",  -- Utilisé par les calculs Oracle
(
    coalesce(dsn."TotalDSN",0) -
    coalesce(compta."TotalCompta",0)
) as "Ecart DSN / Compta Oracle",
-- CORRECTIONS Stagiaires
coalesce(drcIRA."IRA",0) as "- IRA",  -- Gratification
coalesce(drcIAR."IAR",0) as "- IAR",  -- Abattement TP
coalesce(drcIRE."IRE",0) as "- IRE",  -- Abattement pour absence stage
coalesce(drcIRD."IRD",0) as "- IRD",
coalesce(drcISK."ISK",0) as "+ ISK",
-- Somme intermédiaire des corrections - celles nécessaires pour les stagiaires
-- Si des corrections sont effectuées à ce niveau, il ne faut pas oublier de les répercuter plus bas.
    coalesce(compta."TotalCompta",0)
    - coalesce(drcIRA."IRA",0)
    - coalesce(drcIAR."IAR",0)
    - coalesce(drcIRE."IRE",0)
    - coalesce(drcIRD."IRD",0)
    + coalesce(drcISK."ISK",0)
as "Total Compta corrigé",
(   coalesce(dsn."TotalDSN",0) -
    (
        coalesce(compta."TotalCompta",0)
        - coalesce(drcIRA."IRA",0)
        - coalesce(drcIAR."IAR",0)
        - coalesce(drcIRE."IRE",0)
        - coalesce(drcIRD."IRD",0)
        + coalesce(drcISK."ISK",0)
    )
)as "Ecart",
-- CORRECTION Autres
coalesce(drcPVS."PVS",0) as "+ PVS", -- Indemnité de prévoyance soumis
coalesce(drcEXJ."EXJ",0) as "- EXJ", -- Journalistes
coalesce(drcKCL."KCL",0) as "- KCL", -- Indemnité de reclassement
coalesce(drcRSP."RSP",0) as "+ RSP", -- Réintégration sociale
coalesce(drcIRJ."IRJ",0) as "+ IRJ", -- Indemnité de rupture conventionnelle soumise
coalesce(drcINX."INX",0) as "+ INX", -- Indemnité transactionnelle soumise
-- CORRECTION UMR et UMQ
-- UMR et UMQ se déclenchent en même temps.
-- Elles sont prises en compte également pour la DSN => double le montant. Il y aura une correction => date de fin ?
coalesce(drcUMR."UMR",0) as "+ UMR", -- Maladie exo
-- CORRECTION Activité partielle
coalesce(drcAP2."AP2",0) as "- AP2",
coalesce(drcAP4."AP4",0) as "- AP4",
coalesce(drcAP7."AP7",0) as "- AP7 limite temps",  -- Il y a eu une correction sur AP7 effective avec la paie de mai 2020
-- CORRECTION Rappel cotis en EV
-- Bornée dans le temps aux périodes d'origine antérieures à janvier 2020 (voir le SQL de sélection)
coalesce(rappelCotisEV."CotisEvA-1",0) as "+ CotisEvA-1",
-- SOMME des corrections
    coalesce(compta."TotalCompta",0)
    - coalesce(drcIRA."IRA",0)
    - coalesce(drcIAR."IAR",0)
    - coalesce(drcIRE."IRE",0)
    - coalesce(drcIRD."IRD",0)
    + coalesce(drcISK."ISK",0)
    + coalesce(drcPVS."PVS",0)
    - coalesce(drcEXJ."EXJ",0)
    - coalesce(drcKCL."KCL",0)
    + coalesce(drcRSP."RSP",0)
    + coalesce(drcIRJ."IRJ",0)
    + coalesce(drcINX."INX",0)
    + coalesce(drcUMR."UMR",0)
    - coalesce(drcAP2."AP2",0)
    - coalesce(drcAP4."AP4",0)
    - coalesce(drcAP7."AP7",0)
    + coalesce(rappelCotisEV."CotisEvA-1",0)
as "Total Compta corrigé",
(   coalesce(dsn."TotalDSN",0) -
    (
    coalesce(compta."TotalCompta",0)
    - coalesce(drcIRA."IRA",0)
    - coalesce(drcIAR."IAR",0)
    - coalesce(drcIRE."IRE",0)
    - coalesce(drcIRD."IRD",0)
    + coalesce(drcISK."ISK",0)
    + coalesce(drcPVS."PVS",0)
    - coalesce(drcEXJ."EXJ",0)
    - coalesce(drcKCL."KCL",0)
    + coalesce(drcRSP."RSP",0)
    + coalesce(drcIRJ."IRJ",0)
    + coalesce(drcINX."INX",0)
    + coalesce(drcUMR."UMR",0)
    - coalesce(drcAP2."AP2",0)
    - coalesce(drcAP4."AP4",0)
    - coalesce(drcAP7."AP7",0)
    + coalesce(rappelCotisEV."CotisEvA-1",0)
    )
)as "Ecart DSN / Compta Oracle"
from zx00, 
zx5v,
zx37,
zx0m, -- l'info ZX0M est répétitive. Mais en mettant le nudoss de ZX00, a priori, il n'y en a qu'une seule occurrence par dossier de paie
dsn,
compta,
c641100,
c641101,
c6413xx,
c6417xx,
drcPVS,
drcEXJ,
drcISK,
drcRSP,
drcIRA,
drcIAR,
drcIRE,
drcKCL,
drcUMR,
drcUMQ,
drcIRJ,
drcINX,
drcIRD,
drcAP2,
drcAP4,
drcAP7,
rappelCotisEV
where zx00.nudoss = zx5v.nudoss
and zx00.perpai = 'MT202005' 
and zx5v.validt = 'D'
and zx00.nudoss = zx37.nudoss
and zx00.nudoss = zx0m.nudoss
and zx00.nudoss = dsn.nudoss(+)
and zx00.nudoss = compta.nudoss(+)
and zx00.nudoss = c641100.nudoss(+)
and zx00.nudoss = c641101.nudoss(+)
and zx00.nudoss = c6413xx.nudoss(+)
and zx00.nudoss = c6417xx.nudoss(+)
and zx00.nudoss = drcPVS.nudoss(+)
and zx00.nudoss = drcEXJ.nudoss(+)
and zx00.nudoss = drcISK.nudoss(+)
and zx00.nudoss = drcRSP.nudoss(+)
and zx00.nudoss = drcIRA.nudoss(+)
and zx00.nudoss = drcIAR.nudoss(+)
and zx00.nudoss = drcKCL.nudoss(+)
and zx00.nudoss = drcIRE.nudoss(+)
and zx00.nudoss = drcUMR.nudoss(+)
and zx00.nudoss = drcUMQ.nudoss(+)
and zx00.nudoss = drcIRJ.nudoss(+)
and zx00.nudoss = drcINX.nudoss(+)
and zx00.nudoss = drcIRD.nudoss(+)
and zx00.nudoss = drcISK.nudoss(+)
and zx00.nudoss = drcAP2.nudoss(+)
and zx00.nudoss = drcAP4.nudoss(+)
and zx00.nudoss = drcAP7.nudoss(+)
and zx00.nudoss = rappelCotisEV.nudoss(+)
order by zx00.matric, zx00.nudoss
;