Réduire les écarts entre la comptabilité et la DSN

Par AMO
décembre 18, 2020
Un chef comptable a été à l’origine de mes interventions. Il avait un écart de plusieurs k€ entre ce qu’il déclarait à l’URSSAF, via la DSN, et les chiffres qu’il avait en comptabilité.
“A vous de voir: tout sort de HR Access ! Il doit y avoir moyen de comprendre d’où viennent ces écarts.”
“Je vous donne les codes comptables qui reçoivent des éléments destinés à l’URSSAF, et vous me dites.”
Il n’a pas tord. Comment faire ?
Exploiter ZXMM, ZX4K.
Trouver des rubriques en écart. Les ré-introduire facticement.
Itérer.
Le tout en SQL, pour aller vite, pour vérifier si le fait de réintroduire une rubrique présente d’un côté mais absente de l’autre, réduit l’écart global.
Un peu de jus de cerveau, et à la fin, l’écart était inférieur à 70€ (il demeurait quelques anomalies de dossier).
Voici le 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
;