Des SQL pour aider à contrôler la DSN

-- Contrôles proposés pour la prévoyance et la mutuelle

with prevoyance_mutuelle as
(
select trim(a.cdcode) cdcode,
b.liblon,
c.nucais,
c.tycais,
case c.tycais 
  when 'A' then 'ARRCO'
  when 'C' then 'AGIRC'
  when 'D' then 'AGIRC-ARRCO'
  when 'M' then 'Mutuelle'
  when 'P' then 'Prévoyance'
  when 'U' then 'Autres caisses retraite'
  when 'Z' then 'Autre'
  else c.tycais
end "Type Caisse"
from zd00 a, zd01 b, zdcc c
where a.nudoss = b.nudoss 
and a.nudoss = c.nudoss
and a.cdstco = 'DRK'
and c.tycais in ('P','M') -- pour avoir uniquement la prévoyance
),

drc_prevoyance_mutuelle as (
select a.cdcode, a.nudoss, b.liblon,
d.cdcode as "Code organisme",
d.liblon as "Libellé organisme",
d.nucais,
d.tycais,
d."Type Caisse"
from zd00 a,
zd01 b,
zdao c,
prevoyance_mutuelle d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DRC'
and trim(c.nuorga) = d.cdcode
and c.rgposb != ' '
),

paie_prevoyance_mutuelle as(
select *
from zx8k
where codrub in ( select cdcode from drc_prevoyance_mutuelle )
and perpai = 'MT202302'
),

-- Avec correction
paie_prevoyance_mutuelle_corr as(
select *
from zx8k
where (codrub in ( select cdcode from drc_prevoyance_mutuelle )
--or codrub in ('')
)
and perpai = 'MT202302'
),

sal_paie_prevoyance_mutuelle as (
-- Début requête pour récupérer les éléments individuels
select a.nudoss,
a.matric,
a.perpai,
b.validt,
c.etabli,
d.codrub,
d.libell,
d.monsal,
d.monpat
from zx00 a,
zx5v b,
zx37 c,
paie_prevoyance_mutuelle d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and c.extc1c = 'HIE'
-- Fin requête individuelle
),

-- Avec corrections
sal_paie_prevoyance_mutuelle_corr as (
-- Début requête pour récupérer les éléments individuels
select a.nudoss,
a.matric,
a.perpai,
b.validt,
c.etabli,
d.codrub,
d.libell,
d.monsal,
d.monpat
from zx00 a,
zx5v b,
zx37 c,
paie_prevoyance_mutuelle_corr d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and c.extc1c = 'HIE'
-- Fin requête individuelle
),

-- Total des cotisations Prévoyance (ZX8K) par etabli et salarié,
tot_paie_prevoyance_mutuelle as(
select etabli, nudoss, matric, sum(monsal) "monsal",
sum(monpat) "monpat"
from sal_paie_prevoyance_mutuelle
group by etabli, nudoss, matric
),

-- Total des cotisations Prévoyance (ZX8K) par etabli et salarié,
-- Avec corrections
tot_paie_prevoyance_mutuelle_corr as(
select etabli, nudoss, matric, sum(monsal) "monsal",
sum(monpat) "monpat"
from sal_paie_prevoyance_mutuelle_corr
group by etabli, nudoss, matric
),

dfb as (
select a.cdcode,
b.liblon,
c.normdb,
c.normfn,
c.catego,
c.typcod,
case c.typcod
when '001' then 'Exonération de cotisations au titre de l''emploi d''un apprenti (loi de 1979)'
when '002' then 'Exonération de cotisations au titre de l''emploi d''un apprenti (loi de 1987)'
when '003' then 'Exonération de cotisations au titre de l''emploi d''un apprenti (loi de 1992)'
when '004' then 'Exonération de cotisations au titre de l''emploi d''un salarié en contrat d''accès à l''emploi'
when '006' then 'Exonération de cotisations au titre de l''emploi d''un salarié en contrat d''accompagnement dans l''emploi'
when '008' then 'Exonération de cotisations au titre de l''emploi d''un salarié en contrat de professionnalisation'
when '009' then 'Exonération de cotisations applicable aux associations intermédiaires'
when '010' then 'Exonération de cotisations applicable aux entreprises des bassins d''emploi à redynamiser'
when '011' then 'Exonération de cotisations applicable au créateur ou repreneur d''entreprise'
when '012' then 'Exonération de cotisations applicable dans les DOM'
when '013' then 'Exonération de cotisations applicable aux entreprises et associations d''aide à domicile'
when '014' then 'Exonérations de cotisations applicable aux entreprises innovantes ou universitaires'
when '015' then 'Exonération de cotisations applicable aux entreprises en zones franches urbaines'
when '016' then 'Exonération de cotisations applicable aux organismes d''intérêt général en zones de revitalisation rurale'
when '017' then 'Exonération de cotisations applicable aux structures agréées de l''aide sociale'
when '018' then 'Réduction générale des cotisations patronales de sécurité sociale et d''assurance chômage'
when '019' then 'Réduction de cotisations applicable aux entreprises des zones de restructuration de la défense'
when '020' then 'Réduction de cotisations au titre de l''embauche du 1er au 50ème salarié en zones de revitalisation rurale'
when '021' then 'Déduction patronale au titre des heures supplémentaires'
when '022' then 'Exonération de cotisations applicable à une gratification de stage'
when '023' then 'Exonération de cotisation des sommes provenant d''un CET et réaffectées à un plan d''épargne retraite (PERCO, PERECO, PEREO) ou à un régime de retraite supplémentaire'
when '025' then 'Exonération de cotisations au titre de l’emploi d''un salarié en chantier et atelier d''insertion'
when '027' then 'Exonération Personnel technique CUMA, hors ateliers'
when '028' then 'Réduction Travailleur Occasionnel'
when '029' then 'Réduction employeurs petit pool IEG'
when '030' then 'Cotisation employeurs régime spécial maladie IEG Complémentaire'
when '031' then 'Cotisation salariés régime spécial maladie IEG Complémentaire'
when '032' then 'Cotisation salariés régime maladie IEG spécial Solidarité'
when '033' then 'Cotisation employeurs complément d''invalidité IEG'
when '034' then 'Cotisation employeurs régime de droit commun IEG (population adossée)'
when '035' then 'Cotisation employeurs régime spécial IEG (population adossée)'
when '036' then 'Cotisation employeurs régime spécial IEG (population non adossée)'
when '037' then 'Cotisation salariés régime de droit commun IEG (population adossée)'
when '038' then 'Cotisation salariés régime spécial IEG (population non adossée)'
when '039' then 'Cotisations employeurs petit pool IEG'
when '040' then 'Cotisation AC : assurance chômage sur rémunérations brutes après déduction'
when '041' then 'Cotisation AC majorée 1 : application d’une majoration AC + 0,5% sur les contrats d’usage inférieurs ou égaux à 3 mois'
when '042' then 'Cotisation AC majorée 2 : application d’une majoration AC + 3% sur les contrats d’accroissement temporaire d’activité inférieurs ou égaux à 1 mois'
when '043' then 'Cotisation AC majorée 3 : application d’une majoration AC + 1,5% sur les contrats d’accroissement temporaire d’activité supérieurs à 1 mois mais inférieurs ou égaux à 3 mois'
when '044' then 'Exonération de cotisation chômage pour les moins de 26 ans'
when '045' then 'Cotisation Accident du travail'
when '046' then 'Cotisation AEF Bourse de l''emploi'
when '047' then 'Cotisation AEF CESA'
when '048' then 'Cotisation AGS : assurance garantie des salaires sur rémunérations brutes après déduction'
when '049' then 'Cotisation Allocation de logement (FNAL)'
when '051' then 'Cotisation Formation professionnelle ADEFA'
when '053' then 'Cotisation Formation professionnelle additionnelle FAFSEA'
when '054' then 'Cotisation Formation professionnelle AREFA'
when '056' then 'Cotisation Formation professionnelle FAFSEA'
when '057' then 'Cotisation Formation professionnelle FAFSEA CDD'
when '058' then 'Cotisation Formation professionnelle FAFSEA des communes forestières'
when '059' then 'Cotisation individuelle Prévoyance-Assurance-Mutuelle pour la période et l''affiliation concernées'
when '060' then 'Cotisation IRCANTEC Tranche A'
when '061' then 'Cotisation IRCANTEC Tranche B'
when '063' then 'Montant de cotisation Arrco'
when '064' then 'Montant de cotisation Agirc, y compris Apec'
when '065' then 'Cotisation CRPCEN'
when '066' then 'Cotisation caisse de congés spectacles'
when '068' then 'Contribution solidarité autonomie'
when '069' then 'Contribution sur avantage de pré-retraite entreprise à dater du 11/10/2007 (CAPE)'
when '070' then 'Contribution sur avantage de pré-retraite entreprise aux taux normal (CAPE)'
when '071' then 'Contribution forfait social'
when '072' then 'Contribution sociale généralisée/salaires partiellement déductibles'
when '073' then 'CSG/CRDS sur participation intéressement épargne salariale'
when '074' then 'Cotisation Allocation familiale - taux normal  '
when '075' then 'Cotisation Assurance Maladie'
when '076' then 'Cotisation Assurance Vieillesse'
when '077' then 'Montant de la retenue à la source effectuée sur les salaires versés aux personnes domiciliées hors de France'
when '078' then 'Pénalité de 1% emploi sénior'
when '079' then 'Remboursement de la dette sociale'
when '081' then 'Versement mobilité'
when '082' then 'Versement mobilité additionnel'
when '086' then 'Cotisation pénibilité mono exposition'
when '087' then 'Cotisation pénibilité multi exposition'
when '088' then 'Exonération versement mobilité'
when '089' then 'Exonération Contrat Initiative Emploi'
when '090' then 'Exonération accueillants familiaux'
when '091' then 'Cotisation Service de santé au travail'
when '092' then 'Cotisation Association pour l''emploi des cadres ingénieurs et techniciens de l''agriculture (APECITA)'
when '093' then 'Contribution sur indemnités de mise à la retraite'
when '094' then 'Exonération cotisations Allocations familiales (SICAE)'
when '096' then 'Cotisation CRPNPAC au fonds de retraite'
when '097' then 'Cotisation CRPNPAC au fonds d''assurance'
when '098' then 'Cotisation CRPNPAC au fonds de majoration'
when '099' then 'Contribution stock options'
when '100' then 'Contribution au financement du dialogue social'
when '101' then 'Association Mutualisation du Coût Inaptitude'
when '102' then 'Complément de cotisation Allocation Familiale'
when '103' then 'Contribution actions gratuites'
when '104' then 'Pénibilité Cotisation de base'
when '105' then 'Montant de cotisation Régime Unifié Agirc-Arrco, y compris Apec'
when '106' then 'Réduction générale des cotisations patronales de retraite complémentaire'
when '107' then 'Forfait marin '
when '108' then 'Demi-rôle marin'
when '109' then 'Exonération de cotisations salariales de retraite complémentaire au titre de l''emploi d''un apprenti'
when '110' then 'Exonération de cotisations patronales de retraite complémentaire applicable dans les DOM (LODEOM) SMIC 130% à 220% '
when '111' then 'Exonération de cotisations de retraite complémentaire applicable aux entreprises et associations d''aide à domicile'
when '112' then 'Exonération de cotisations patronales de retraite complémentaire applicable dans les DOM (LODEOM) SMIC 170% à 270%'
when '113' then 'Exonération de cotisations patronales de retraite complémentaire applicable dans les DOM (LODEOM) SMIC 170% à 350%'
when '114' then 'Montant de réduction des heures supplémentaires/complémentaires'
when '115' then 'Cotisation Assurance Maladie pour le Régime Local Alsace Moselle'
when '116' then 'Cotisation absente de la norme en cas de régularisation prud''homale'
when '128' then 'Contribution à la formation professionnelle (CFP)'
when '129' then 'Contribution dédiée au financement du Compte Personnel de Formation pour les titulaires de CDD (CPF-CDD)'
when '130' then 'Part principale de la taxe d''apprentissage'
when '131' then 'Cotisation régime unifié Agirc-Arrco'
when '132' then 'Cotisation Apec'
when '133' then 'Contribution maladie spécifique Mayotte'
when '140' then 'Contribution conventionnelle au financement du dialogue social'
when '141' then 'Contribution conventionnelle à la formation professionnelle'
when '300' then '[FP] Cotisations normales (part salariale)'
when '301' then '[FP] Cotisations normales (part patronale)'
when '302' then '[FP] Surcotisation huit trimestres (part salariale)'
when '303' then '[FP] Validation de services (part salariale)'
when '304' then '[FP] Cotisations Nouvelle Bonification Indiciaire (part salariale)'
when '305' then '[FP] Cotisations Nouvelle Bonification Indiciaire (part patronale)'
when '306' then '[FP] Cotisations sur indemnité de feu (part salariale)'
when '307' then '[FP] Cotisations sur indemnité de feu (part patronale)'
when '308' then '[FP] Cotisation sur bonification sapeur pompier (part salariale)'
when '309' then '[FP] Cotisation sur prime sur sujetion des aides soignantes (part salariale)'
when '310' then '[FP] Cotisation sur prime sur sujetion des aides soignantes (part patronale)'
when '311' then '[FP] Cotisation RAFP (part salariale)'
when '312' then '[FP] Cotisation RAFP (part patronale)'
when '313' then '[FP] Cotisations pour pension sur ISS ou PSS (part salariale)'
when '314' then '[FP] Cotisations pour pension sur ISS ou PSS (part patronale)'
when '315' then '[FP] Cotisations pour pension sur IR (part salariale)'
when '316' then '[FP] Cotisations pour pension sur IR (part patronale)'
when '317' then '[FP] Cotisations pour pension sur IMT (part salariale)'
when '318' then '[FP] Cotisations pour pension sur IMT (part patronale)'
when '319' then '[FP] Cotisations pour l''allocation temporaire d''invalidité (part patronale)'
when '320' then '[FP] Surcotisation (part salariale)'
when '321' then '[FP] Rachat des années d''études (part salariale)'
when '322' then '[FP] Exonération de cotisation pour heures d’aide à domicile (part patronale)'
when '323' then '[FP] Cotisation RAEP (part patronale)'
when '324' then '[FP] Cotisation RAEP (part salariale)'
when '325' then '[FP] Validation de services (part patronale)'
when '326' then '[FP] Régularisation de service (part salariale)'
when '327' then '[FP] Régularisation de service (part patronale) '
when '330' then 'Régime de base forfaitaire CNBF'
when '331' then 'Régime de base proportionnelle CNBF'
when '332' then 'Régime complémentaire CNBF'
when '333' then 'Cotisation contrat d’emploi pénitentiaire 1'
when '334' then 'Cotisation contrat d’emploi pénitentiaire 2'
when '901' then 'Cotisation épargne retraite'
when '902' then 'Contribution à la formation professionnelle des Artisans assimilés salariés'
when '903' then 'Cotisation AFNCA'
when '904' then 'Cotisation ANEFA'
when '905' then 'Cotisation ASCPA '
when '906' then 'Cotisation PROVEA'
when '907' then ' Complément de cotisation Assurance Maladie'
when '908' then 'Taxe forfaitaire CDDU Assurance Chômage'
when '909' then 'Cotisation au titre du financement des régimes de retraites supplémentaires à prestation définies'
when '910' then 'Exonération de cotisations patronales pour les entreprises affectées par la crise sanitaire'
when '911' then 'Réduction de cotisations patronales pour les entreprises du secteur de la vigne affectées par la crise sanitaire'
when '912' then 'Exonération du forfait social à 10%'
when '913' then 'Indemnité inflation'
when '914' then 'Potentielle nouvelle cotisation A'
when '915' then 'Potentielle nouvelle cotisation B'
when '916' then 'Potentielle nouvelle cotisation C'
when '917' then 'Potentielle nouvelle cotisation D'
when '918' then 'Potentielle nouvelle cotisation E'
else ''
end DSN_ref_code_cotis
from zd00 a,
zd01 b,
zd7q c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DFB'
),

-- Paramétrage DSN des rubriques ayant un code organisme de prévoyance ou de mutuelle
drc_param_dsn as 
(select a.cdcode, a.liblon,
b.cptdsn,
c.liblon "Lib compteur HRA",
c.typcod,
coalesce(c.DSN_ref_code_cotis,'') DSN_ref_code_cotis,
b.normdb, b.normfn, b.datdeb, b.datfin
from drc_prevoyance_mutuelle a,
zd7p b,
dfb c
where a.nudoss = b.nudoss
and b.cptdsn = c.cdcode
),

-- Montants ZXMM filtrés pour les compteurs identifiés comme ceux à destination d'un organisme de prévoyance
sal_dsn_prevoyance_mutuelle as (
select d.etabli,
a.nudoss,
a.matric,
b.cptdsn,
e.liblon,
b.catego,
b.typcod,
b.typpar,
b.codrub,
b.monta1
from zx00 a,
zxmm b,
zx5v c,
zx37 d,
dfb e
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and a.perpai = 'MT202302'
and b.cptdsn in (
'79P10P31',       	-- Salaire brut Prévoyance                      
'79P11P31',       	-- Tranche A Prévoyance                         
'79P12P31',       	-- Tranche 2 Prévoyance                         
'79P13P31',       	-- Tranche B Prévoyance                         
'79P14P31',       	-- Tranche C Prévoyance                         
'79P15P31',       	-- Tranche D Prévoyance                         
'79P16P31',       	-- Tranche D1 Prévoyance                        
'79P17P31',       	-- Base spécifique Prévoyance                   
'79P18P31',       	-- Base forfaitaire Prévoyance                  
'79P19P31',       	-- Base fictive Prévoyance reconstituée         
'79P20P31',       	-- Montant forfaitaire Prévoyance               
'79P21P31',       	--  Montant Prévoyance libre ou exceptionnel     
'79P23P31',       	-- Sans composant de base assujettie en paie    
'79P24P31'       	  -- OCs, 24 - Tranche 2 Unifiée Prévoyance       
)
and d.extc1c = 'HIE' -- Critère pour les établissements indispensables
and b.cptdsn = e.cdcode(+) -- Jointure sur le répertoire des compteurs DSN, DFB
),

-- DSN blocs 79 à destination de la prévoyance, par salarié
tot_dsn_prevoyance_mutuelle as
(
select etabli, nudoss, matric, sum(monta1) monta1
from sal_dsn_prevoyance_mutuelle
group by etabli, nudoss, matric
),

-- Comparaison Paie / DSN pour la prévoyance, sans correction
compPaieDSN as (
select a.etabli,
a.matric,
a."monsal" + a."monpat" as "Paie",
b.monta1 as "DSN",
a."monsal" + a."monpat" + b.monta1 as "Ecart"
from tot_paie_prevoyance_mutuelle a, tot_dsn_prevoyance_mutuelle b
where a.nudoss = b.nudoss
),

-- Corrections calculées par SQL et introduites côté DSN
tot_corr_dsn as (
select c.etabli,
a.nudoss,
a.matric,
'CORRECTION' as CPTDSN,
'Montants réintroduits fictivement' as LIBLON,
' ' as codrub,
sum(b.monsal) + sum(b.monpat) "monta1"
from zx00 a,
zx8k b,
zx37 c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and b.codrub in ('UT7')
and c.extc1c = 'HIE'
group by c.etabli, a.nudoss, a.matric
order by c.etabli, a.nudoss, a.matric
),

-- Comparaison Paie / DSN pour la prévoyance,
-- AVEC corrections
compPaieDSN_corr as (
select a.etabli,
a.matric,
a."monsal" + a."monpat" as "Paie",
b.monta1 as "DSN",
b.monta1 - coalesce(c."monta1", 0) as "DSN corr",
a."monsal" + a."monpat" + b.monta1 - coalesce(c."monta1", 0) as "Ecart après correction"
from tot_paie_prevoyance_mutuelle a,
tot_dsn_prevoyance_mutuelle b,
tot_corr_dsn c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss(+)
)


-- Liste des organismes de prévoyance / mutuelle présents dans DRK, le répertoire des organismes
-- select * from prevoyance_mutuelle ;
 
-- Liste des rubriques ayant un code organisme prévoyance ou mutuelle
-- select * from drc_prevoyance_mutuelle ;

-- Liste des rubriques ayant un code organisme associé à la prévoyance ou à la mutuelle, et le paramétrage DSN associé
-- select * from drc_param_dsn ;

-- Répertoire DFB des compteurs DSN
-- select * from dfb order by cdcode;

-- Pour tous les salariés passés en paie, extraction de ZXMM, filtrée sur les occurrences ayant un code compteur DSN identifié comme un de ceux
-- à destination de la prévoyance ou d'une mutuelle
-- select * from sal_dsn_prevoyance_mutuelle ;

-- SQL1-a : Comparaison Paie / DSN au niveau établissement, pour tous les établissements
-- select etabli,sum("Paie") "Paie", sum("DSN") "DSN", sum("Ecart") "Ecart" from compPaieDSN group by etabli ;

-- SQL1-b : Comparaison Paie / DSN au niveau établissement, pour tous les établissements
-- AVEC Corrections
 select etabli,sum("Paie") "Paie", sum("DSN") "DSN", sum("DSN corr") "DSN corr", sum("Ecart après correction") "Ecart après correction" from compPaieDSN_corr group by etabli ;

-- SQL2-a : Comparaison Paie / DSN en listant tous les salariés d'un établissement donné
-- (sans corrections simulées)
-- select * from compPaieDSN where etabli = 'ABS' ;

-- SQL2-b : Comparaison Paie / DSN en listant tous les salariés d'un établissement donné
-- (AVEC corrections simulées)
-- select * from compPaieDSN_corr where etabli = 'ABS' ;

-- SQL3
-- select * from compPaieDSN where matric = '0R2122' ;

-- SQL4.a : requête pour "creuser" au niveau individuel, par extraction des données paie à destination de la prévoyance
-- (Sélection par les types d'organisme = P ou M)
-- select etabli, matric, codrub, libell, monsal, monpat from sal_paie_prevoyance_mutuelle where matric = '0D2735';

-- SQL4.b : requête pour "creuser" au niveau individuel, par extraction des données paie à destination de la prévoyance
-- Avec corrections
-- select etabli, matric, codrub, libell, monsal, monpat from sal_paie_prevoyance_mutuelle_corr where matric = '0D2699 ';

-- SQL5.a : requête pour "creuser" au niveau individuel, à destination de la DSN
-- select etabli, matric, cptdsn, liblon, codrub, monta1 from sal_dsn_prevoyance_mutuelle where matric = '0D2735';
 
 -- SQL5.b : requête pour "creuser" au niveau individuel, à destination de la DSN
 -- Avec les corrections
 select etabli, nudoss, matric, cptdsn, liblon, codrub, monta1 from sal_dsn_prevoyance_mutuelle where matric = '0D2699'
 union
 select etabli, nudoss, matric, cptdsn, liblon, codrub, "monta1" as monta1 from tot_corr_dsn  where matric = '0D2699' ;
 
 select * from zx00 where nudoss = 223317 ;
 select * from zx00 where matric in ('0D2699','D14159', '0D0799', '0D5656') ;  

with paie_urssaf as(
select *
from zx8k
where codrub in (select a.cdcode
  from zd00 a,
  zdao b
  where a.nudoss = b.nudoss
  and a.cdstco = 'DRC'
  and b.nuorga in ('001', '002', '210', '200')
  and b.rgposb != ' '
  )
),

sal_paie_urssaf as (
-- Début requête pour récupérer les éléments individuels
select a.nudoss,
a.matric,
a.perpai,
b.validt,
c.etabli,
d.codrub,
d.libell,
d.monsal,
d.monpat
from zx00 a,
zx5v b,
zx37 c,
paie_urssaf d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and c.extc1c = 'HIE'
-- Fin requête individuelle
),

-- Total des cotisations URSSAF par etabli et salarié
tot_paie_urssaf as(
select etabli, nudoss, matric, sum(monsal) "monsal",
sum(monpat) "monpat"
from sal_paie_urssaf
group by etabli, nudoss, matric
),

-- Référentiel des codes CTP (pour avoir les libellés)
CTP as (
select trim(a.cdcode) "Code CTP",
b.liblon,
c.natu23
from zd00 a, zd01 b, zd7t c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'HMD'
and b.cdlang = 'F'
order by a.cdcode
),

-- Blocs 23 par salarié (Issus de ZXMM), avec codes CTP pour contrôles individuels
sal_bloc23 as (
select d.etabli,
a.nudoss,
a.matric,
b.catego,
b.typcod,
e.liblon "Lib CTP",
b.codrub,
case
 when b.catego = '23C' then b.monta1
 when b.catego = '23R' then -1 * b.monta1 -- A priori, les réductions
end monta1
from zx00 a,
zxmm b,
zx5v c,
zx37 d,
CTP e
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and a.perpai = 'MT202302'
and b.catego in ('23C', '23R') -- URSSAF, mais quelle est la différence entre 23C et 23R ?
and d.extc1c = 'HIE' -- Critère pour les établissements indispensables
and 'U' || trim(b.typcod) = e."Code CTP"(+)
),

-- Blocs 23, ventilés par salarié et par CTP
sal_ctp_bloc23 as (
select etabli, nudoss, matric, typcod, "Lib CTP",
sum(monta1) "monta1"
from sal_bloc23
group by etabli, nudoss, matric, typcod, "Lib CTP"
order by etabli, nudoss, matric, typcod
),

-- Corrections calculées par SQL et introduites côté DSN
tot_corr_dsn as (
select c.etabli,
a.nudoss,
a.matric,
sum(b.monsal) + sum(b.monpat) "monta1"
from zx00 a,
zx8k b,
zx37 c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and b.codrub in ('UTB', 'BMO', 'UTY', 'UWE')
and c.extc1c = 'HIE'
group by c.etabli, a.nudoss, a.matric
order by c.etabli, a.nudoss, a.matric
),

-- DSN blocs 23, par salarié, sans les codes CTP
tot_dsn_bloc23 as
(
select etabli, nudoss, matric, sum("monta1") "monta1"
from sal_ctp_bloc23
group by etabli, nudoss, matric
),

-- Comparaison Paie / DSN pour l'URSSAF, sans correction
compPaieDSN as (
select a.etabli,
a.matric,
a."monsal" + a."monpat" as "Paie",
b."monta1" as "DSN",
a."monsal" + a."monpat" + b."monta1" as "Ecart"
from tot_paie_urssaf a, tot_dsn_bloc23 b
where a.nudoss = b.nudoss
),

-- Comparaison Paie / DSN pour l'URSSAF, avec corrections
compPaieDSN_corr as (
select a.etabli,
a.matric,
a."monsal" + a."monpat" as "Paie",
b."monta1" as "DSN",
b."monta1" - coalesce(c."monta1", 0) as "DSN corr",
a."monsal" + a."monpat" + b."monta1" - coalesce(c."monta1", 0) as "Ecart après correction"
from tot_paie_urssaf a,
tot_dsn_bloc23 b,
tot_corr_dsn c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss(+)
)

-- !!! Dans ce qui suit, ce qui est comparé sont les chiffres pour l'URSSAF
-- !!! en calculant 
-- !!! * les chiffres pour la "paie" à partir de ZX8K, pour une sélection de codes organismes, 
-- !!! * et les chiffres pour la DSN à partir de ZXMM, à destination du bloc 23

-- Utilisation:
-- Requête SQL3-a pour identifier les établissements en écart
-- Requête SQL2-a pour identifier, pour un établissement donné, les salariés en écart, et en choisir un à analyser
-- Requêtes SQL4 et SQL5 pour extraire les données au niveau individuel Paie et DSN, et chercher la ou les rubriques pouvant constituer le montant de l'écart individuel
-- Introduire correction dans 'tot_corr_dsn'
-- Mesurer l'impact de la correction avec requête SQL2-b, SQL3-b

-- SQL1-a : Comparaison Paie / DSN au niveau individuel
-- (sans corrections simulées)
-- select * from compPaieDSN where matric = '1L0021';

-- SQL1-b : Comparaison Paie / DSN au niveau individuel AVEC corrections simulées
-- (pour la liste des rubriques introduites en correction, voir 'tot_corr_dsn' ci-dessus
-- select * from compPaieDSN_corr where matric = '1L0021';

-- SQL2-a : Comparaison Paie / DSN en listant tous les salariés d'un établissement donné
-- (sans corrections simulées)
-- select * from compPaieDSN where etabli = 'LEA' ;

-- SQL2-b : Comparaison Paie / DSN en listant tous les salariés d'un établissement donné
-- AVEC les corrections simulées
-- select * from compPaieDSN_corr where etabli = 'NAH' ;

-- SQL3-a : Comparaison Paie / DSN au niveau établissement, pour tous les établissements
-- (sans corrections simulées)
 select etabli,sum("Paie") "Paie", sum("DSN") "DSN", sum("Ecart") "Ecart" from compPaieDSN group by etabli ;

-- SQL3-b : Comparaison Paie / DSN au niveau établissement, pour tous les établissements
-- AVEC les corrections simulées
-- select etabli,sum("Paie") "Paie", sum("DSN corr") "DSN corr", sum("Ecart après correction") "Ecart après correction" from compPaieDSN_corr group by etabli ;

-- SQL4: requête pour "creuser" au niveau individuel, par extraction des données paie à destination de l'URSSAF (sélection par codes organimes)
-- select etabli, matric, codrub, libell, monsal, monpat from sal_paie_urssaf where matric = '0N0001';

-- SQL5: requête pour "creuser" au niveau individuel, à destination de la DSN (blocs 23 par code CTP)
-- select etabli, matric, typcod, "Lib CTP", "monta1" from sal_ctp_bloc23 where matric = '0N0001';

-- Liste des codes INSEE présents au niveau individuel
select distinct(b.cdinse)
from zx00 a,
zxmm b
where a.nudoss = b.nudoss
and b.cptdsn = '81C081P57' 
order by b.cdinse;

-- Liste des codes INSEE présents au niveau agrégé
select distinct(b.cdinse)
from zx00 a,
zxmm b
where a.nudoss = b.nudoss
and b.catego in ('23C', '23R')
and b.typcod = '900' ;

-- Codes communes présents au niveau agrégé et absents au niveau individuel
select distinct(b.cdinse)
from zx00 a,
zxmm b
where a.nudoss = b.nudoss
and b.catego in ('23C', '23R')
and b.typcod = '900' 
minus
select distinct(b.cdinse)
from zx00 a,
zxmm b
where a.nudoss = b.nudoss
and b.cptdsn = '81C081P57'
;

-- Codes communes présents au niveau individuel et absents au niveau agrégé
select distinct(b.cdinse)
from zx00 a,
zxmm b
where a.nudoss = b.nudoss
and b.cptdsn = '81C081P57'
minus
select distinct(b.cdinse)
from zx00 a,
zxmm b
where a.nudoss = b.nudoss
and b.catego in ('23C', '23R')
and b.typcod = '900'
;

-- Liste des salariés sans code INSEE dans ZXMM
select a.matric,
a.nomsal,
b.etabli
from zx00 a,
zx37 b,
zxmm c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and b.extc1c = 'HIE'
and c.cptdsn = '81C081P57'
and c.cdinse = ' ' 
order by b.etabli ;

-- Contrôles proposés pour l'AGIRC / ARRCO
-- * vérifier que toutes les rubriques ayant un code organisme associé à l'AGIRC-ARCCO ont bien une ZD7P (paramétrage DSN), et afficher le code compteur utilisé
-- * comparer les montants calculés en paie (ZX8K) pour les rubriques avec un code organisme AGIRC-ARRCO et les comparer à ce qui va passer en DSN via ZXMM sur une sélection de compteurs (!! liste compteurs à fiabiliser)

with agirc_arrco as
(
select trim(a.cdcode) cdcode,
b.liblon,
c.nucais,
c.tycais,
case c.tycais 
  when 'A' then 'ARRCO'
  when 'C' then 'AGIRC'
  when 'D' then 'AGIRC-ARRCO'
  when 'M' then 'Mutuelle'
  when 'P' then 'Prévoyance'
  when 'U' then 'Autres caisses retraite'
  when 'Z' then 'Autre'
  else c.tycais
end "Type Caisse"
from zd00 a, zd01 b, zdcc c
where a.nudoss = b.nudoss 
and a.nudoss = c.nudoss
and a.cdstco = 'DRK'
and c.tycais = 'D' -- pour avoir uniquement l'ARRCO / AGIRC
),

drc_agirc_arrco as (
select a.cdcode, a.nudoss, b.liblon
from zd00 a,
zd01 b,
zdao c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DRC'
and trim(c.nuorga) in (select cdcode from agirc_arrco )
and c.rgposb != ' '
),

paie_agirc_arrco as(
select *
from zx8k
where codrub in ( select cdcode from drc_agirc_arrco )
and perpai = 'MT202302'
),

-- Avec correction
paie_agirc_arrco_corr as(
select *
from zx8k
where (codrub in ( select cdcode from drc_agirc_arrco )
or codrub in ('WDC', 'WD6'))
and perpai = 'MT202302'
),

sal_paie_agirc_arrco as (
-- Début requête pour récupérer les éléments individuels
select a.nudoss,
a.matric,
a.perpai,
b.validt,
c.etabli,
d.codrub,
d.libell,
d.monsal,
d.monpat
from zx00 a,
zx5v b,
zx37 c,
paie_agirc_arrco d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and c.extc1c = 'HIE'
-- Fin requête individuelle
),

-- Avec corrections
sal_paie_agirc_arrco_corr as (
-- Début requête pour récupérer les éléments individuels
select a.nudoss,
a.matric,
a.perpai,
b.validt,
c.etabli,
d.codrub,
d.libell,
d.monsal,
d.monpat
from zx00 a,
zx5v b,
zx37 c,
paie_agirc_arrco_corr d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and c.extc1c = 'HIE'
-- Fin requête individuelle
),

-- Total des cotisations AGIRC ARRCO (ZX8K) par etabli et salarié,
tot_paie_agirc_arrco as(
select etabli, nudoss, matric, sum(monsal) "monsal",
sum(monpat) "monpat"
from sal_paie_agirc_arrco
group by etabli, nudoss, matric
),

-- Total des cotisations AGIRC ARRCO (ZX8K) par etabli et salarié,
-- Avec corrections
tot_paie_agirc_arrco_corr as(
select etabli, nudoss, matric, sum(monsal) "monsal",
sum(monpat) "monpat"
from sal_paie_agirc_arrco_corr
group by etabli, nudoss, matric
),

dfb as (
select a.cdcode,
b.liblon,
c.normdb,
c.normfn,
c.catego,
c.typcod,
case c.typcod
when '001' then 'Exonération de cotisations au titre de l''emploi d''un apprenti (loi de 1979)'
when '002' then 'Exonération de cotisations au titre de l''emploi d''un apprenti (loi de 1987)'
when '003' then 'Exonération de cotisations au titre de l''emploi d''un apprenti (loi de 1992)'
when '004' then 'Exonération de cotisations au titre de l''emploi d''un salarié en contrat d''accès à l''emploi'
when '006' then 'Exonération de cotisations au titre de l''emploi d''un salarié en contrat d''accompagnement dans l''emploi'
when '008' then 'Exonération de cotisations au titre de l''emploi d''un salarié en contrat de professionnalisation'
when '009' then 'Exonération de cotisations applicable aux associations intermédiaires'
when '010' then 'Exonération de cotisations applicable aux entreprises des bassins d''emploi à redynamiser'
when '011' then 'Exonération de cotisations applicable au créateur ou repreneur d''entreprise'
when '012' then 'Exonération de cotisations applicable dans les DOM'
when '013' then 'Exonération de cotisations applicable aux entreprises et associations d''aide à domicile'
when '014' then 'Exonérations de cotisations applicable aux entreprises innovantes ou universitaires'
when '015' then 'Exonération de cotisations applicable aux entreprises en zones franches urbaines'
when '016' then 'Exonération de cotisations applicable aux organismes d''intérêt général en zones de revitalisation rurale'
when '017' then 'Exonération de cotisations applicable aux structures agréées de l''aide sociale'
when '018' then 'Réduction générale des cotisations patronales de sécurité sociale et d''assurance chômage'
when '019' then 'Réduction de cotisations applicable aux entreprises des zones de restructuration de la défense'
when '020' then 'Réduction de cotisations au titre de l''embauche du 1er au 50ème salarié en zones de revitalisation rurale'
when '021' then 'Déduction patronale au titre des heures supplémentaires'
when '022' then 'Exonération de cotisations applicable à une gratification de stage'
when '023' then 'Exonération de cotisation des sommes provenant d''un CET et réaffectées à un plan d''épargne retraite (PERCO, PERECO, PEREO) ou à un régime de retraite supplémentaire'
when '025' then 'Exonération de cotisations au titre de l’emploi d''un salarié en chantier et atelier d''insertion'
when '027' then 'Exonération Personnel technique CUMA, hors ateliers'
when '028' then 'Réduction Travailleur Occasionnel'
when '029' then 'Réduction employeurs petit pool IEG'
when '030' then 'Cotisation employeurs régime spécial maladie IEG Complémentaire'
when '031' then 'Cotisation salariés régime spécial maladie IEG Complémentaire'
when '032' then 'Cotisation salariés régime maladie IEG spécial Solidarité'
when '033' then 'Cotisation employeurs complément d''invalidité IEG'
when '034' then 'Cotisation employeurs régime de droit commun IEG (population adossée)'
when '035' then 'Cotisation employeurs régime spécial IEG (population adossée)'
when '036' then 'Cotisation employeurs régime spécial IEG (population non adossée)'
when '037' then 'Cotisation salariés régime de droit commun IEG (population adossée)'
when '038' then 'Cotisation salariés régime spécial IEG (population non adossée)'
when '039' then 'Cotisations employeurs petit pool IEG'
when '040' then 'Cotisation AC : assurance chômage sur rémunérations brutes après déduction'
when '041' then 'Cotisation AC majorée 1 : application d’une majoration AC + 0,5% sur les contrats d’usage inférieurs ou égaux à 3 mois'
when '042' then 'Cotisation AC majorée 2 : application d’une majoration AC + 3% sur les contrats d’accroissement temporaire d’activité inférieurs ou égaux à 1 mois'
when '043' then 'Cotisation AC majorée 3 : application d’une majoration AC + 1,5% sur les contrats d’accroissement temporaire d’activité supérieurs à 1 mois mais inférieurs ou égaux à 3 mois'
when '044' then 'Exonération de cotisation chômage pour les moins de 26 ans'
when '045' then 'Cotisation Accident du travail'
when '046' then 'Cotisation AEF Bourse de l''emploi'
when '047' then 'Cotisation AEF CESA'
when '048' then 'Cotisation AGS : assurance garantie des salaires sur rémunérations brutes après déduction'
when '049' then 'Cotisation Allocation de logement (FNAL)'
when '051' then 'Cotisation Formation professionnelle ADEFA'
when '053' then 'Cotisation Formation professionnelle additionnelle FAFSEA'
when '054' then 'Cotisation Formation professionnelle AREFA'
when '056' then 'Cotisation Formation professionnelle FAFSEA'
when '057' then 'Cotisation Formation professionnelle FAFSEA CDD'
when '058' then 'Cotisation Formation professionnelle FAFSEA des communes forestières'
when '059' then 'Cotisation individuelle Prévoyance-Assurance-Mutuelle pour la période et l''affiliation concernées'
when '060' then 'Cotisation IRCANTEC Tranche A'
when '061' then 'Cotisation IRCANTEC Tranche B'
when '063' then 'Montant de cotisation Arrco'
when '064' then 'Montant de cotisation Agirc, y compris Apec'
when '065' then 'Cotisation CRPCEN'
when '066' then 'Cotisation caisse de congés spectacles'
when '068' then 'Contribution solidarité autonomie'
when '069' then 'Contribution sur avantage de pré-retraite entreprise à dater du 11/10/2007 (CAPE)'
when '070' then 'Contribution sur avantage de pré-retraite entreprise aux taux normal (CAPE)'
when '071' then 'Contribution forfait social'
when '072' then 'Contribution sociale généralisée/salaires partiellement déductibles'
when '073' then 'CSG/CRDS sur participation intéressement épargne salariale'
when '074' then 'Cotisation Allocation familiale - taux normal  '
when '075' then 'Cotisation Assurance Maladie'
when '076' then 'Cotisation Assurance Vieillesse'
when '077' then 'Montant de la retenue à la source effectuée sur les salaires versés aux personnes domiciliées hors de France'
when '078' then 'Pénalité de 1% emploi sénior'
when '079' then 'Remboursement de la dette sociale'
when '081' then 'Versement mobilité'
when '082' then 'Versement mobilité additionnel'
when '086' then 'Cotisation pénibilité mono exposition'
when '087' then 'Cotisation pénibilité multi exposition'
when '088' then 'Exonération versement mobilité'
when '089' then 'Exonération Contrat Initiative Emploi'
when '090' then 'Exonération accueillants familiaux'
when '091' then 'Cotisation Service de santé au travail'
when '092' then 'Cotisation Association pour l''emploi des cadres ingénieurs et techniciens de l''agriculture (APECITA)'
when '093' then 'Contribution sur indemnités de mise à la retraite'
when '094' then 'Exonération cotisations Allocations familiales (SICAE)'
when '096' then 'Cotisation CRPNPAC au fonds de retraite'
when '097' then 'Cotisation CRPNPAC au fonds d''assurance'
when '098' then 'Cotisation CRPNPAC au fonds de majoration'
when '099' then 'Contribution stock options'
when '100' then 'Contribution au financement du dialogue social'
when '101' then 'Association Mutualisation du Coût Inaptitude'
when '102' then 'Complément de cotisation Allocation Familiale'
when '103' then 'Contribution actions gratuites'
when '104' then 'Pénibilité Cotisation de base'
when '105' then 'Montant de cotisation Régime Unifié Agirc-Arrco, y compris Apec'
when '106' then 'Réduction générale des cotisations patronales de retraite complémentaire'
when '107' then 'Forfait marin '
when '108' then 'Demi-rôle marin'
when '109' then 'Exonération de cotisations salariales de retraite complémentaire au titre de l''emploi d''un apprenti'
when '110' then 'Exonération de cotisations patronales de retraite complémentaire applicable dans les DOM (LODEOM) SMIC 130% à 220% '
when '111' then 'Exonération de cotisations de retraite complémentaire applicable aux entreprises et associations d''aide à domicile'
when '112' then 'Exonération de cotisations patronales de retraite complémentaire applicable dans les DOM (LODEOM) SMIC 170% à 270%'
when '113' then 'Exonération de cotisations patronales de retraite complémentaire applicable dans les DOM (LODEOM) SMIC 170% à 350%'
when '114' then 'Montant de réduction des heures supplémentaires/complémentaires'
when '115' then 'Cotisation Assurance Maladie pour le Régime Local Alsace Moselle'
when '116' then 'Cotisation absente de la norme en cas de régularisation prud''homale'
when '128' then 'Contribution à la formation professionnelle (CFP)'
when '129' then 'Contribution dédiée au financement du Compte Personnel de Formation pour les titulaires de CDD (CPF-CDD)'
when '130' then 'Part principale de la taxe d''apprentissage'
when '131' then 'Cotisation régime unifié Agirc-Arrco'
when '132' then 'Cotisation Apec'
when '133' then 'Contribution maladie spécifique Mayotte'
when '140' then 'Contribution conventionnelle au financement du dialogue social'
when '141' then 'Contribution conventionnelle à la formation professionnelle'
when '300' then '[FP] Cotisations normales (part salariale)'
when '301' then '[FP] Cotisations normales (part patronale)'
when '302' then '[FP] Surcotisation huit trimestres (part salariale)'
when '303' then '[FP] Validation de services (part salariale)'
when '304' then '[FP] Cotisations Nouvelle Bonification Indiciaire (part salariale)'
when '305' then '[FP] Cotisations Nouvelle Bonification Indiciaire (part patronale)'
when '306' then '[FP] Cotisations sur indemnité de feu (part salariale)'
when '307' then '[FP] Cotisations sur indemnité de feu (part patronale)'
when '308' then '[FP] Cotisation sur bonification sapeur pompier (part salariale)'
when '309' then '[FP] Cotisation sur prime sur sujetion des aides soignantes (part salariale)'
when '310' then '[FP] Cotisation sur prime sur sujetion des aides soignantes (part patronale)'
when '311' then '[FP] Cotisation RAFP (part salariale)'
when '312' then '[FP] Cotisation RAFP (part patronale)'
when '313' then '[FP] Cotisations pour pension sur ISS ou PSS (part salariale)'
when '314' then '[FP] Cotisations pour pension sur ISS ou PSS (part patronale)'
when '315' then '[FP] Cotisations pour pension sur IR (part salariale)'
when '316' then '[FP] Cotisations pour pension sur IR (part patronale)'
when '317' then '[FP] Cotisations pour pension sur IMT (part salariale)'
when '318' then '[FP] Cotisations pour pension sur IMT (part patronale)'
when '319' then '[FP] Cotisations pour l''allocation temporaire d''invalidité (part patronale)'
when '320' then '[FP] Surcotisation (part salariale)'
when '321' then '[FP] Rachat des années d''études (part salariale)'
when '322' then '[FP] Exonération de cotisation pour heures d’aide à domicile (part patronale)'
when '323' then '[FP] Cotisation RAEP (part patronale)'
when '324' then '[FP] Cotisation RAEP (part salariale)'
when '325' then '[FP] Validation de services (part patronale)'
when '326' then '[FP] Régularisation de service (part salariale)'
when '327' then '[FP] Régularisation de service (part patronale) '
when '330' then 'Régime de base forfaitaire CNBF'
when '331' then 'Régime de base proportionnelle CNBF'
when '332' then 'Régime complémentaire CNBF'
when '333' then 'Cotisation contrat d’emploi pénitentiaire 1'
when '334' then 'Cotisation contrat d’emploi pénitentiaire 2'
when '901' then 'Cotisation épargne retraite'
when '902' then 'Contribution à la formation professionnelle des Artisans assimilés salariés'
when '903' then 'Cotisation AFNCA'
when '904' then 'Cotisation ANEFA'
when '905' then 'Cotisation ASCPA '
when '906' then 'Cotisation PROVEA'
when '907' then ' Complément de cotisation Assurance Maladie'
when '908' then 'Taxe forfaitaire CDDU Assurance Chômage'
when '909' then 'Cotisation au titre du financement des régimes de retraites supplémentaires à prestation définies'
when '910' then 'Exonération de cotisations patronales pour les entreprises affectées par la crise sanitaire'
when '911' then 'Réduction de cotisations patronales pour les entreprises du secteur de la vigne affectées par la crise sanitaire'
when '912' then 'Exonération du forfait social à 10%'
when '913' then 'Indemnité inflation'
when '914' then 'Potentielle nouvelle cotisation A'
when '915' then 'Potentielle nouvelle cotisation B'
when '916' then 'Potentielle nouvelle cotisation C'
when '917' then 'Potentielle nouvelle cotisation D'
when '918' then 'Potentielle nouvelle cotisation E'
else ''
end DSN_ref_code_cotis
from zd00 a,
zd01 b,
zd7q c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DFB'
),

-- Paramétrage DSN des rubriques ayant un code organisme associé à l'AGIRC ARRCO
-- Le champ 'DSN_ref_code_cotis' est déduit du champ TYPCOD du compteur HRA, et est associé au libellé trouvé dans la feuille Excel des datatypes émis par DSN Info.
-- Dans cette Excel, S21.G00.81.001 est contrôlée par une énumération, DSN_ref_code_cotis.
drc_param_dsn as 
(select a.cdcode, a.liblon,
b.cptdsn,
c.liblon "Lib compteur HRA",
c.typcod,
coalesce(c.DSN_ref_code_cotis,'') DSN_ref_code_cotis,
b.normdb, b.normfn, b.datdeb, b.datfin
from drc_agirc_arrco a,
zd7p b,
dfb c
where a.nudoss = b.nudoss
and b.cptdsn = c.cdcode
),

-- Montants ZXMM filtrés pour les compteurs identifiés comme ceux de l'AGIRC ARRCO
sal_dsn_agirc_arrco as (
select d.etabli,
a.nudoss,
a.matric,
b.cptdsn,
e.liblon,
b.catego,
b.typcod,
b.typpar,
b.codrub,
b.monta1
from zx00 a,
zxmm b,
zx5v c,
zx37 d,
dfb e
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and a.perpai = 'MT202302'
and b.cptdsn in (
'81C063P02', -- Arrco : cotis. sur base standard (ratt:78-02)
'81C063P11', -- Arrco : cotis. sur base forfait. SS (78-11)
'81C063P24', -- Arrco : cotis. sur b. plaf. specfique (78-24)
'81C063P25', -- Arrco : cotis. /contrib libératoire(78-25)
'81C063P43', -- Arrco : cotis. /base exceptionnelle (78-43)
'81C063P45', -- Arrco : cotis. /base plaf ICP (78-45)
'81C064P03', -- Agirc,base standard (ratt 78-03),compris Apec
'81C064P22', -- Agirc, base specifique (78-22), compris Apec
'81C064P23', -- Agirc,base exceptionnelle(78-23),compris Apec
'81C105P02', -- Assiette brute déplafonnée
'81C105P03', -- Assiette brute déplafonnée
'81C105P11', -- Base forfaitaire soumise aux cotis.Séc.Social
'81C105P22', -- Base brute spécifique
'81C105P23', -- Base exceptionnelle (Agirc Arrco)
'81C105P24', -- Base plafonnée spécifique ICP Agirc-Arrco
'81C105P25', -- Assiette de contribution libératoire
'81C105P43', -- Base plafonnée ICP Agirc-Arrco
'81C105P45', -- Base plafonnée ICP Agirc-Arrco
'81E109P02', -- Exo cot retrai compl d'un apprenti
'81E109P03', -- Exo cot retrai compl d'un apprenti
'81R106P03', -- Assiette brute déplafonnée (ne plus utiliser)
'81R106P03A', -- Assiette réduction générale cotis. patron. RC
'81R106P03C', -- Montant réduction générale cotis. patron. RC
'81R110P03A', -- Exo.retraite.complém(LODEOM) SMIC 130% à 220%
'81R110P03C', -- Mont.Exo.retr.compl(LODEOM).SMIC 130% à 220%
'81R111P03A', -- Ret.Comp.appli.entrep et associations à domic
'81R111P03C', -- Ret.Comp.appli.entrep et associations à domic
'81R112P03A', -- Exo.retraite.complém(LODEOM) SMIC 170% à 270%
'81R112P03C', -- Mont.Exo.retr.compl(LODEOM).SMIC 170% à 270%
'81R113P03A', -- Exo.retraite.complém(LODEOM) SMIC 170% à 350%
'81R113P03C' -- Mont.Exo.retr.compl(LODEOM).SMIC 170% à 350%
)
and d.extc1c = 'HIE' -- Critère pour les établissements indispensables
and b.cptdsn = e.cdcode(+) -- Jointure sur le répertoire des compteurs DSN, DFB
),

-- DSN blocs 81 à destination de l'AGIRC ARRCO, par salarié
tot_dsn_agirc_arrco as
(
select etabli, nudoss, matric, sum(monta1) monta1
from sal_dsn_agirc_arrco
group by etabli, nudoss, matric
),

-- Comparaison Paie / DSN pour l'AGIRC ARRCO, sans correction
compPaieDSN as (
select a.etabli,
a.matric,
a."monsal" + a."monpat" as "Paie",
b.monta1 as "DSN",
a."monsal" + a."monpat" + b.monta1 as "Ecart"
from tot_paie_agirc_arrco a, tot_dsn_agirc_arrco b
where a.nudoss = b.nudoss
),

-- Comparaison Paie / DSN pour l'AGIRC ARRCO,
-- AVEC corrections
compPaieDSN_corr as (
select a.etabli,
a.matric,
a."monsal" + a."monpat" as "Paie",
b.monta1 as "DSN",
a."monsal" + a."monpat" + b.monta1 as "Ecart"
from tot_paie_agirc_arrco_corr a, tot_dsn_agirc_arrco b
where a.nudoss = b.nudoss
)

-- Liste des rubriques ayant un code organisme associé à l'AGIRC ARRCO, et le paramétrage DSN associé
-- select * from drc_param_dsn ;

-- Pour tous les salariés passés en paie, extraction de ZXMM, filtrée sur les occurrences ayant un code compteur DSN identifié comme un de ceux
-- à destination de l'AGIRC ARRCO
-- select * from sal_dsn_agirc_arrco ;

-- SQL1-a : Comparaison Paie / DSN au niveau établissement, pour tous les établissements
-- select etabli,sum("Paie") "Paie", sum("DSN") "DSN", sum("Ecart") "Ecart" from compPaieDSN group by etabli ;

-- SQL1-b : Comparaison Paie / DSN au niveau établissement, pour tous les établissements
-- AVEC Corrections
-- select etabli,sum("Paie") "Paie", sum("DSN") "DSN", sum("Ecart") "Ecart" from compPaieDSN_corr group by etabli ;

-- SQL2-a : Comparaison Paie / DSN en listant tous les salariés d'un établissement donné
-- (sans corrections simulées)
-- select * from compPaieDSN where etabli = '434' ;

-- SQL2-b : Comparaison Paie / DSN en listant tous les salariés d'un établissement donné
-- (AVEC corrections simulées)
-- select * from compPaieDSN_corr where etabli = 'ODF' ;

-- SQL3
-- select * from compPaieDSN where matric = '0R2122' ;

-- SQL4.a : requête pour "creuser" au niveau individuel, par extraction des données paie à destination de l'AGIRC-ARRCO
-- (Sélection par les types d'organisme = D)
-- select etabli, matric, codrub, libell, monsal, monpat from sal_paie_agirc_arrco where matric = '0R0619';

-- SQL4.b : requête pour "creuser" au niveau individuel, par extraction des données paie à destination de l'AGIRC-ARRCO
-- Avec corrections
-- select etabli, matric, codrub, libell, monsal, monpat from sal_paie_agirc_arrco_corr where matric = 'D31966';

-- SQL5.a : requête pour "creuser" au niveau individuel, à destination de la DSN
-- Les codes cotisations retenus sont: 063, 064, 105, 106, 109, 110, 111, 112, 113)
 select etabli, matric, cptdsn, liblon, codrub, monta1 from sal_dsn_agirc_arrco where matric = 'D31966';