[auf_coda_additions.git] / db / procedures / auf_p_etat_resultats_p4_ex_precedent_2011.sql

USE [coda-prod]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[auf_p_etat_resultats_p4_ex_precedent_2011]
                                            @exercice int,
                                            @periode int,
                                            @code  varchar(14),
                                            @retraitement varchar(3)

  --  @code
  --   1-AUF   : global AUF
  --   2-GRI   : groupé par implantation
  --   3-GRB : groupé par bureau
  --   4-BXX : Bureau BXX
  --   5-XXX : implantation XXX
  -- @retraitement: Oui/Non

AS

DECLARE @lib_periode varchar(100)
DECLARE @categorie varchar(1)
DECLARE @publication varchar(3)

SET NOCOUNT ON

--- extraire la catégorie de 'état

SELECT @categorie = LEFT(ltrim(@code),1)

IF ( @exercice = 0 ) BEGIN
    --    pour permettre aux états sur IWR  de récuperer l'éxercice et la période courants
    SELECT @publication = 'oui'

    SELECT
        @exercice   = num1,
        @periode = num2,
        @lib_periode = texte4
    FROM   auf_iwrconfig
    WHERE  code_etat = 'ICG'
END
ELSE BEGIN
    SELECT @publication = 'non'

    SELECT @lib_periode = texte4
    FROM auf_iwrconfig
    WHERE code_etat = 'ICG'
END


-- activer pour génération des états PDF
-- SELECT @publication = 'oui'

CREATE TABLE #cumul_ecritures (
    imp_bur varchar(50),
    yr  int,
    period int ,
    balcode varchar(12),
    el1 varchar(20),
    el3 varchar(20),
    full_value money
)

IF ltrim(rtrim(@code)) = '1-AUF' BEGIN
    -- état global AUF

    INSERT INTO #cumul_ecritures
    SELECT
        g.grpcode ,
        yr,
        period,
        balcode,
        el1,
        el3,
        full_value
    FROM oas_balance, oas_grplist g
    WHERE
        oas_balance.cmpcode = 'AUF'
        AND (
            (yr = @exercice AND period BETWEEN 1 AND @periode)
            OR (yr = @exercice-1 AND  period BETWEEN 1 AND @periode)
        )
        AND curcode = 'EUR'
        AND repbasis = 5
        AND full_value <> 0
        AND balcode IN ('BUDGET','BUDGET-MENS','REEL')
        AND oas_balance.el3 = g.code
        AND g.grpcode LIKE 'I%'
        AND g.elmlevel = 3
        AND g.cmpcode ='AUF'
END

IF ( ltrim(rtrim(@code)) = '3-GRB') BEGIN
    -- groupé par Bureau
    INSERT INTO #cumul_ecritures
    SELECT
        bur,
        yr,
        period,
        balcode,
        el1,
        el3,
        full_value
    FROM
        oas_balance b,
        oas_grplist  g,
        (
            SELECT DISTINCT codbur, bur
            FROM auf_v_gen_implantations
        ) bureau
    WHERE
        b.cmpcode = 'AUF'
        AND (
            (b.yr = @exercice AND b.period BETWEEN 1 AND @periode)
            OR (b.yr = @exercice-1 AND  b.period BETWEEN 1 AND @periode)
        )
        AND b.curcode = 'EUR'
        AND b.repbasis = 5
        AND b.full_value <> 0
        AND b.balcode IN ('BUDGET','BUDGET-MENS','REEL')
        AND b.el3 = g.code
        AND g.grpcode LIKE 'B%'
        AND g.elmlevel = 3
        AND g.cmpcode ='AUF'
        AND g.grpcode = bureau.codbur

        IF @exercice = 2005
            INSERT INTO #cumul_ecritures
            SELECT 'Services centraux', 2004, 12, 'REEL', '61850', '4101KU', -23595.4800
END

IF (LEFT(LTRIM(@code),1) = '4') BEGIN
    -- état pour le Bureau @code
    SELECT @code = rtrim(substring(ltrim(@code),3,5))

    INSERT INTO #cumul_ecritures
    SELECT bur, yr, period, balcode, el1, el3, full_value
    FROM
        oas_balance b,
        oas_grplist  g,
        (
            SELECT DISTINCT codbur, bur
            FROM auf_v_gen_implantations
        ) bureau
    WHERE
        b.cmpcode = 'AUF'
        AND (
            (b.yr = @exercice AND b.period BETWEEN 1 AND @periode)
            OR (b.yr = @exercice-1 AND  b.period BETWEEN 1 AND @periode)
        )
        AND b.curcode = 'EUR'
        AND b.repbasis = 5
        AND b.full_value <> 0
        AND b.balcode IN ('BUDGET','BUDGET-MENS','REEL')
        AND b.el3 = g.code
        AND g.grpcode = @code
        AND g.elmlevel = 3
        AND g.cmpcode ='AUF'
        AND g.grpcode = bureau.codbur

    IF (@code = 'BSC' AND ( @exercice = 2004 OR @exercice = 2005))
        INSERT INTO #cumul_ecritures
        SELECT 'Services centraux', 2004, 12, 'REEL', '61850', '4101KU', -23595.4800
END

IF (LTRIM(RTRIM(@code)) = '2-GRI') BEGIN

    -- groupé par Implantation
    INSERT INTO #cumul_ecritures
    SELECT
        CASE
            WHEN bureau.codbur = 'BAN' THEN 'BA' + '-'+ bureau.name
            ELSE bureau.codbur + '-'+ bureau.name
        END [name],
        yr,
        period,
        balcode,
        el1,
        el3,
        full_value
    FROM
        oas_balance b,
        oas_grplist  g,
        auf_v_gen_implantations bureau
    WHERE
        b.cmpcode = 'AUF'
        AND (
            (b.yr = @exercice AND b.period BETWEEN 1 AND @periode)
            OR (b.yr = @exercice-1 AND  b.period BETWEEN 1 AND @periode)
        )
        AND b.curcode = 'EUR'
        AND b.repbasis = 5
        AND b.full_value <> 0
        AND b.balcode in ('BUDGET', 'BUDGET-MENS', 'REEL')
        AND b.el3 = g.code
        AND g.grpcode LIKE 'I%'
        AND g.elmlevel = 3
        AND g.cmpcode ='AUF'
        AND bureau.code = right(rtrim(g.grpcode),3)

    IF (@exercice = 2004 OR @exercice = 2005)
        INSERT INTO #cumul_ecritures
        SELECT 'Centrale Paris', 2004, 12, 'REEL', '61850', '4101KU', -23595.4800
END

IF (LEFT(ltrim(@code),1) = '5') BEGIN

    -- pour l'implantation  @code
    SELECT @code = rtrim(substring(ltrim(@code),3,3))

    INSERT INTO #cumul_ecritures
    SELECT
        bureau.name,
        yr,
        period,
        balcode,
        el1,
        el3,
        full_value
    FROM
        oas_balance b,
        oas_grplist g,
        auf_v_gen_implantations bureau
    WHERE
        b.cmpcode = 'AUF'
        AND (
            (b.yr = @exercice AND b.period BETWEEN 1 AND @periode)
            OR (b.yr = @exercice-1 AND  b.period BETWEEN 1 AND @periode)
        )
        AND b.curcode = 'EUR'
        AND b.repbasis = 5
        AND b.full_value <> 0
        AND b.balcode in ('BUDGET','BUDGET-MENS','REEL')
        AND b.el3 = g.code
        AND g.grpcode = 'I'+@code
        AND g.elmlevel = 3
        AND g.cmpcode ='AUF'
        AND bureau.code = @code

    IF (@code = 'FR1' AND ( @exercice = 2004 OR @exercice = 2005 ))
        INSERT INTO #cumul_ecritures
        SELECT 'Centrale Paris', 2004, 12, 'REEL','61850','4101KU',-23595.4800
END

CREATE TABLE #etat_section (
    imp_bur  varchar(50),
    exercice int,
    periode  int,
    cumul    varchar(20),
    groupe   varchar(100),
    sgroupe  varchar(20),
    montant  money,
    libelle  varchar(100)
)

-- section PRODUITS

INSERT INTO #etat_section
SELECT
    imp_bur,
    b.yr AS exercice,
    b.period AS periode,
    b.balcode AS cumul,
    '010-Excédents des produits (charges) avant retraitements' AS groupe1,
    '000-PRODUITS' AS groupe2,
    b.full_value * -1 AS solde, -- Corriger pour debit / crédit !!!!!!!!!!
    CASE
        WHEN
            b.el1 IN ('75100', '70500')
            OR b.el1 LIKE '756%'
            OR b.el1 LIKE '708%'
            THEN '110-Ressources propres'
        WHEN b.el1 LIKE '740%' THEN '120-Contributions gouvernementales'
        WHEN
            b.el1 LIKE '745%'
            OR b.el1 = '74998'
            THEN '130-Contributions contractuelles'
        WHEN
            b.el1 LIKE '78%'
            OR b.el1='75700'
            OR (
                b.el1='11100'
                AND rtrim(ltrim(el3)) <> ''
                AND el3 NOT IN (
                    '90003GR', '91022GR', '91203GR',
                    '91024GR','91025GR','91032GR', '9003GR', '9122GR', '9123GR',
                    '9124GR','9125GR','9132GR'
                )
            )
            OR b.el3 IN (
                '91022GR', '91024GR', '91023GR', '91032GR','91025GR',  '9122GR',
                '9124GR', '9123GR', '9132GR','9125GR'
            )
            THEN '140-Apports, autres recettes, provisions'
        WHEN
            b.el1 LIKE '76%'
            OR b.el1 LIKE '77%'
            THEN '150-Produits financiers et exceptionnels'
        WHEN b.el1 = '75800' THEN '160-Exploitation'
        WHEN b.el1 LIKE '87%' THEN '170-Valeurs des biens et services à titre gratuit'
    END AS sgroupe
FROM #cumul_ecritures b
WHERE  -- Filtres globaux
    (
        b.el1 LIKE '7%'
        OR b.el1 LIKE '87%'
        OR (
            b.el1='11100'
            AND rtrim(ltrim(el3)) <> ''
            AND el3 NOT IN (
                '90003GR', '91022GR', '91023GR', '91024GR','9125GR','9132GR',
                '9003GR', '9122GR', '9123GR', '9124GR','9125GR','9132GR'
            )
        )
        OR b.el3 IN (
            '91022GR', '91024GR', '91023GR', '91032GR','91025GR',  '9122GR',
            '9124GR', '9123GR', '9132GR','9125GR'
        )
    )
    AND b.el1 <> '77200'

-- section CHARGES

INSERT INTO #etat_section
SELECT
    imp_bur,
    b.yr AS exercice,
    b.period AS periode,
    b.balcode AS cumul,
    '010-Excédents des produits (charges) avant retraitements' AS groupe1,
    '001-CHARGES' AS groupe2,
    b.full_value * -1 AS solde,
    CASE
        -- Attention séquence
        WHEN b.el1 LIKE '861%' THEN '190-Valeurs des biens et services à titre gratuit'
        WHEN grp1.grpcode = 'HX' OR grpG.grpcode = 'GX' THEN
            -- Attention séquence
            CASE
                WHEN b.el3 LIKE '95%' THEN '010-Frais de personnel'
                WHEN b.el3 LIKE '96%' THEN '020-Frais généraux'
                WHEN
                    b.el1 LIKE '9%'
                    AND (b.el3 LIKE '99099%' OR b.el3 LIKE '9999%')
                    THEN '120-Actions Réserve'
                WHEN
                    b.el1 LIKE '666%'
                    AND (b.el3 LIKE '91006%' OR b.el3 LIKE '9106%')
                    THEN '180-Perte (gain) de change'
                WHEN
                    b.el3 LIKE '91006%'
                    OR b.el3 LIKE '9106%'
                    THEN '160-Charges financières'
                WHEN
                    b.el1 LIKE '67%'
                    AND b.el1 <> '67500'
                    AND (b.el3 LIKE '91007%' OR b.el3 LIKE '9107%')
                    THEN '170-Charges exceptionnelles'
                WHEN
                    b.el1 LIKE '6817%'
                    AND b.el3 LIKE '91002GR%'
                    THEN '155-Charges cotisations'
                ELSE '150-Autres charges, provisions'
            END
        WHEN grp1.grpcode = 'H1' THEN '03A-' + space(8) + lg.[name] + '-' + lg.code
        WHEN grp1.grpcode='H2' THEN '04B-' +space(8)+ lg.[name]+'-'+lg.code
        WHEN grp1.grpcode='H3' THEN '05C-' +space(8)+ lg.[name]+'-'+lg.code
        WHEN grp1.grpcode='H4' THEN '06D-' +space(8)+ lg.[name]+'-'+lg.code
        WHEN grp1.grpcode='H5' THEN '07E-' +space(8)+ lg.[name]+'-'+lg.code
        WHEN grp1.grpcode='H6' THEN '08V-' +space(8)+ lg.[name]+'-'+lg.code
        WHEN grp1.grpcode='H7' THEN '09W-' +space(8)+ lg.[name]+'-'+lg.code
        WHEN grp1.grpcode='H8' THEN '10X-' +space(8)+ lg.[name]+'-'+lg.code
        WHEN grpG.grpcode='G1' THEN '03A-' +space(8)+ lg.[name]
        WHEN grpG.grpcode='G2' THEN '04B-' +space(8)+ lg.[name]
        WHEN grpG.grpcode='G3' THEN '05C-' +space(8)+ lg.[name]
        WHEN grpG.grpcode='G4' THEN '06D-' +space(8)+ lg.[name]
        WHEN grpG.grpcode='G5' THEN '07E-' +space(8)+ lg.[name]
        WHEN grpG.grpcode='G6' THEN '08V-' +space(8)+ lg.[name]
        WHEN grp1.grpcode='H0' OR grpG.grpcode='G0' THEN '130-Actions institutionnelles'
        WHEN grp1.grpcode='HS'  OR grpG.grpcode='GS' THEN '140-Actions administratives'
    END AS sgroupe
FROM
    oas_group lg,
    #cumul_ecritures b
    LEFT OUTER JOIN oas_grplist grp1
        ON grp1.grpcode LIKE 'H%'
        AND grp1.elmlevel = 3
        AND grp1.cmpcode = 'AUF'
        AND b.el3  = grp1.code
    LEFT OUTER JOIN oas_grplist grp2
        ON grp2.grpcode LIKE 'L%'
        AND grp2.elmlevel = 3
        AND grp2.cmpcode = 'AUF'
        AND b.el3  = grp2.code
    LEFT OUTER JOIN oas_grplist grpG
        ON grpG.grpcode LIKE 'G%'
        AND grpG.elmlevel = 3
        AND grpG.cmpcode = 'AUF'
        AND b.el3  = grpG.code
WHERE  -- Filtres globaux
    (
        b.el1 LIKE '6%'
        OR b.el1 LIKE '86%'
        OR b.el1 LIKE '2%'
        OR b.el1 LIKE '1%'
        OR b.el1 LIKE '9%'
    )
    AND b.el1 NOT LIKE '96%'
    AND b.el1 NOT LIKE '97%'
    AND b.el1 NOT LIKE '98%'
    AND b.el3 NOT IN (
        '91022GR', '91023GR', '91024GR', '91032GR','91025GR', '9122GR',
        '9123GR', '9124GR', '9132GR','9125GR'
    )
    AND NOT (
        b.el1='11100'
        AND rtrim(ltrim(el3)) <> ''
        AND el3 NOT IN (
            '90003GR', '91022GR', '91023GR', '91024GR','91025GR','91032GR',
            '9003GR', '9122GR', '9123GR', '9124GR','9125GR','9132GR'
        )
    )
    AND b.el3 NOT LIKE '91009%'
    AND b.el3 NOT LIKE '9109%'
    AND (grp2.grpcode = lg.code OR grpG.grpcode = lg.code)
    AND lg.groupwhat = 3
    AND lg.cmpcode = 'AUF'
    AND b.el3 NOT LIKE '91008%'
    AND b.el3 NOT LIKE '9108%'


-- section RETRAITEMENTS
-- mettre @publication <> 'oui'  dans prod

IF @retraitement = 'Oui' BEGIN
  INSERT INTO #etat_section
  SELECT
    imp_bur,
    yr AS exercice,
    period AS periode,
    balcode AS cumul,
    '020-E' AS groupe1,
    '002-RETRAITEMENTS' AS groupe2,
    CASE
       WHEN (el1 LIKE '6%' AND el3 != '9003GR') OR el1 LIKE '77200' THEN full_value *-1
       ELSE full_value
    END AS Solde,
    CASE
        WHEN
            (el1 LIKE '11%' AND el3 <> '')
            AND NOT (el1 LIKE '11%' AND el3 IN ('90003GR', '91031GR', '91025GR'))
            THEN '510-Moins : apports'
        WHEN el1 LIKE '2%' THEN '020-Plus : investissements en immobilisations'
        WHEN
            el3 IN ('90003GR', '91031GR', '91025GR')
            THEN '010-Plus : affectation AG et au fonds de réserve'
        WHEN el1 LIKE '16%' THEN '030-Plus : remboursement de la dette à long terme'
        ELSE '550-Moins : amortis. immob. et pertes sur disposition'
    END AS sgroupe
FROM #cumul_ecritures
WHERE  -- Filtres globaux
    (
        (el1 = '67500' AND el3 LIKE '91008%')
        OR (el1 = '68152')
        OR el1 LIKE '6811%'
        OR el1 LIKE '1%'
        OR el1 LIKE '2%'
        OR (el1 = '77200' AND el3 LIKE '91008%')
    )
    AND el1 NOT LIKE    '28%'
    AND el1 NOT LIKE    '96%'
    AND el1 NOT LIKE    '97%'
    AND el1 NOT LIKE    '98%'
    AND NOT ( el1 LIKE '2%' AND el3 LIKE '91008%' )
    AND NOT ( el1 = '21820' AND el3 LIKE '91%')
    AND NOT ( el1 =  '68114' AND el3 LIKE '96%')
    AND el3 NOT LIKE  '91009%'
    AND NOT ( el3 = '91033GR'  AND  yr =2005 )
    AND NOT (
        yr = 2004 AND
        period = 12 AND
        imp_bur  in ('BXX','IXX1') AND
        el1 LIKE '21%'
    )
    AND NOT ( el1 = '67500' AND ( el3 LIKE '96%' OR el3 ='30040QI'))
END

-- construire les colonne de l'état du résultats IWR

IF (ltrim(rtrim(@code)) = '1-AUF')
    -- si catégorie d'état = global AUF
    UPDATE #etat_section SET imp_bur = ''

SELECT
    @categorie categorie,
    @exercice exercice,
    @lib_periode titre,
    @periode periode,
    Sec.imp_bur,
    Sec.groupe,
    Sec.sgroupe,
    Sec.libelle,
    CASE
        WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
        ELSE ISNULL(budget.budget,0)
    END AS budget,    -- (1)
    ISNULL(budget_mens.mensuel,0) AS mensuel, -- (2)
    CASE
        WHEN
            budget.budget IS NULL OR
            budget.budget = 0 OR
            budget_mens.mensuel IS NULL
            THEN 0
        ELSE
            CONVERT(numeric(18,5),budget_mens.mensuel)/ CONVERT(numeric(18,5),budget.budget)
            -- budget_mens.mensuel/budget.budget
    END AS pourc_mens, -- (2)/(1)
    ISNULL(reel_cour.reel,0) reel ,   -- (3)
    ISNULL(reel_cumul.reel_cumul,0) AS reel_cumul, -- (4)
    CASE
        WHEN
            budget.budget IS NULL
            OR budget.budget = 0
            OR reel_cumul.reel_cumul IS NULL
            THEN 0
        ELSE CONVERT(numeric(18,5),reel_cumul.reel_cumul)/ CONVERT(numeric(18,5),budget.budget)
    END AS pourc_r_cour,  -- (4)/(1)
    ISNULL(budget_mens.mensuel,0)- isnull(reel_cumul.reel_cumul,0) AS ecart_planif, -- (2)-(4)
    CASE
        WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
        ELSE ISNULL(budget.budget,0)- isnull(reel_cumul.reel_cumul,0)
    END AS solde_total, --(1)-(4)
    CASE
        WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
        WHEN ( budget.budget IS NULL OR  budget.budget = 0 ) THEN 0
        ELSE
            CONVERT(numeric(18,5),(budget.budget- ISNULL(reel_cumul.reel_cumul,0))) /
            CONVERT(numeric(18,5),budget.budget)
    END  AS pourc_r_total, -- [(1)-(4)]/(1)
    ISNULL(budget_dern.budget_dern,0) budget_dern, -- (5)
    ISNULL(reel_dern.reel_dern,0) reel_dern, -- (6)
    CASE
        WHEN
            budget_dern.budget_dern IS NULL
            OR budget_dern.budget_dern = 0
            OR reel_dern.reel_dern IS NULL
            THEN 0
        ELSE
            CONVERT(numeric(18,5),reel_dern.reel_dern) /
            CONVERT(numeric(18,5),budget_dern.budget_dern)
    END AS pourc_r_dern  -- (6)/(5)
INTO #etat
FROM
    (
        SELECT DISTINCT imp_bur, groupe, sgroupe, libelle
        FROM #etat_section
    ) Sec

    -- budget année en cours
    LEFT JOIN (
        SELECT imp_bur, sgroupe, libelle, SUM(montant) budget
        FROM #etat_section
        WHERE exercice = @exercice AND cumul = 'BUDGET'
        GROUP BY imp_bur, sgroupe, libelle
    ) budget
        ON sec.imp_bur = budget.imp_bur
        AND sec.sgroupe = budget.sgroupe
        AND sec.libelle = budget.libelle

    -- planification mensuel
    LEFT JOIN (
        SELECT imp_bur, sgroupe, libelle, SUM(montant) mensuel
        FROM #etat_section
        WHERE exercice = @exercice AND cumul = 'BUDGET-MENS'
        GROUP BY imp_bur, sgroupe, libelle
    ) budget_mens
        ON sec.imp_bur = budget_mens.imp_bur
        AND sec.sgroupe = budget_mens.sgroupe
        AND sec.libelle = budget_mens.libelle

    -- budget réalisé période en cours
    LEFT JOIN (
        SELECT imp_bur, sgroupe, libelle, SUM(montant) reel
        FROM #etat_section
        WHERE exercice = @exercice AND periode = @periode AND cumul = 'REEL'
        GROUP BY imp_bur, sgroupe, libelle
    ) reel_cour
        ON sec.imp_bur = reel_cour.imp_bur
        AND sec.sgroupe = reel_cour.sgroupe
        AND sec.libelle = reel_cour.libelle

    -- budget réalisé cumulé en cours
    LEFT JOIN (
        SELECT imp_bur, sgroupe, libelle, SUM(montant) reel_cumul
        FROM #etat_section
        WHERE exercice = @exercice AND cumul = 'REEL'
        GROUP BY imp_bur, sgroupe, libelle
    ) reel_cumul
        ON sec.imp_bur = reel_cumul.imp_bur
        AND sec.sgroupe = reel_cumul.sgroupe
        AND sec.libelle = reel_cumul.libelle

    -- budget année dernière
    LEFT JOIN (
        SELECT imp_bur, sgroupe, libelle, SUM(montant) budget_dern
        FROM #etat_section
        WHERE exercice = @exercice-1 AND cumul = 'BUDGET'
        GROUP BY imp_bur, sgroupe, libelle
    ) budget_dern
        ON sec.imp_bur = budget_dern.imp_bur
        AND sec.sgroupe = budget_dern.sgroupe
        AND sec.libelle = budget_dern.libelle

    -- réalisé cumulé année dernière
    LEFT JOIN (
        SELECT imp_bur, sgroupe, libelle, SUM(montant) reel_dern
        FROM #etat_section
        WHERE exercice = @exercice-1 AND cumul = 'REEL'  AND periode <= @periode
        GROUP BY imp_bur, sgroupe, libelle
    ) reel_dern
        ON sec.imp_bur = reel_dern.imp_bur
        AND sec.sgroupe = reel_dern.sgroupe
        AND sec.libelle = reel_dern.libelle

-- requete final

SELECT
    categorie, exercice execice, titre, periode, imp_bur, groupe, sgroupe,
    libelle, budget, mensuel, pourc_mens, reel, reel_cumul, pourc_r_cour,
    ecart_planif, solde_total, pourc_r_total, budget_dern, reel_dern,
    pourc_r_dern
INTO #requete
FROM (
    SELECT
        categorie, exercice, titre, periode, imp_bur, groupe, sgroupe, libelle,
        budget, mensuel, pourc_mens, reel, reel_cumul, pourc_r_cour,
        ecart_planif, solde_total, pourc_r_total, budget_dern, reel_dern,
        pourc_r_dern
    FROM #etat
    UNION
    SELECT
        categorie,
        exercice,
        titre,
        periode,
        imp_bur,
        groupe,
        sgroupe,
        CASE
            WHEN substring(libelle,3,1) = 'A' THEN '031-Projets institutnnels des établismts'
            WHEN substring(libelle,3,1) = 'B' THEN '041-Renforcemt capacités des établismts'
            WHEN substring(libelle,3,1) = 'C' THEN '051-Renforcemt coopération scientifique'
            WHEN substring(libelle,3,1) = 'D' THEN '061-Langue Fr et dévlpmt Francophonie'
            WHEN substring(libelle,3,1) = 'E' THEN '071-Coop univ pour form° et/ou recherche'
            WHEN substring(libelle,3,1) = 'V' THEN '081-Valo ressources pédagogiques et sc'
            WHEN substring(libelle,3,1) = 'W' THEN '091-Renforcmt capacités individuelles'
            WHEN substring(libelle,3,1) = 'X' THEN '101-Valo bénéficiaires Intégration pro'
        END libelle,
        SUM(budget) budget,
        SUM(mensuel) mensuel,
        CASE
            WHEN
                SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL
                THEN 0
            ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
        END AS pourc_mens, -- (2)/(1)
        SUM(reel) reel,
        SUM(reel_cumul) reel_cumul,
        CASE
            WHEN
                SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL
                THEN 0
            ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
        END  AS pourc_r_cour,  -- (4)/(1)
        SUM(ecart_planif) ecart_planif,
        SUM(solde_total) solde_total,
        CASE
            WHEN sgroupe = '002-RETRAITEMENTS' THEN 0
            WHEN SUM(budget) IS NULL OR SUM(budget) = 0 THEN 0
            ELSE
                CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
                CONVERT(numeric(18,5),SUM(budget))
        END AS pourc_r_total, -- [(1)-(4)]/(1)
        SUM(budget_dern) budget_dern,
        SUM(reel_dern) reel_dern,
        CASE
            WHEN
                SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
                THEN 0
            ELSE
                CONVERT(numeric(18,5),SUM(reel_dern)) /
                CONVERT(numeric(18,5),SUM(budget_dern))
        END AS pourc_r_dern  -- (6)/(5)
    FROM #etat
    WHERE  substring(libelle,3,1) in ('A','B','C','D','E','V', 'W', 'X')
    GROUP BY
        categorie,
        exercice,
        titre,
        periode,
        imp_bur,
        groupe,
        sgroupe,
        CASE
            WHEN substring(libelle,3,1) = 'A' THEN '031-Projets institutnnels des établismts'
            WHEN substring(libelle,3,1) = 'B' THEN '041-Renforcemt capacités des établismts'
            WHEN substring(libelle,3,1) = 'C' THEN '051-Renforcemt coopération scientifique'
            WHEN substring(libelle,3,1) = 'D' THEN '061-Langue Fr et dévlpmt Francophonie'
            WHEN substring(libelle,3,1) = 'E' THEN '071-Coop univ pour form° et/ou recherche'
            WHEN substring(libelle,3,1) = 'V' THEN '081-Valo ressources pédagogiques et sc'
            WHEN substring(libelle,3,1) = 'W' THEN '091-Renforcmt capacités individuelles'
            WHEN substring(libelle,3,1) = 'X' THEN '101-Valo bénéficiaires Intégration pro'
        END
    UNION
    SELECT
        categorie,
        exercice,
        titre,
        periode,
        imp_bur,
        groupe,
        sgroupe,
        CASE
            WHEN sgroupe ='000-PRODUITS' THEN '151-Total des produits hors BSTG'
            ELSE '181-Total des charges hors BSTG'
        END libelle,
        SUM(budget) budget,
        SUM(mensuel) mensuel,
        CASE
            WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL THEN 0
            ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
        END AS pourc_mens, -- (2)/(1)
        SUM(reel) reel,
        SUM(reel_cumul) reel_cumul,
        CASE
            WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL THEN 0
            ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
        END AS pourc_r_cour,  -- (4)/(1)
        SUM(ecart_planif) ecart_planif,
        SUM(solde_total) solde_total,
        CASE
            WHEN SUM(budget) IS NULL OR SUM(budget) = 0 THEN 0
            ELSE
                CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
                CONVERT(numeric(18,5),SUM(budget))
        END  AS pourc_r_total, -- [(1)-(4)]/(1)
        SUM(budget_dern) budget_dern,
        SUM(reel_dern) reel_dern,
        CASE
            WHEN
                SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
                THEN 0
            ELSE
                CONVERT(numeric(18,5),SUM(reel_dern)) /
                CONVERT(numeric(18,5),SUM(budget_dern))
        END  AS pourc_r_dern  -- (6)/(5)
    FROM #etat
    WHERE
        sgroupe IN ( '000-PRODUITS','001-CHARGES')
        AND NOT ( sgroupe = '000-PRODUITS' AND substring(libelle,1,3) = '160')
        AND NOT ( sgroupe = '001-CHARGES' AND substring(libelle,1,3) = '190')
    GROUP BY
        categorie,
        exercice,
        titre,
        periode,
        imp_bur,
        groupe,
        sgroupe,
        CASE
            WHEN sgroupe ='000-PRODUITS' THEN '151-Total des produits hors BSTG'
            ELSE '181-Total des charges hors BSTG'
        END
    UNION
    SELECT
        categorie,
        exercice,
        titre,
        periode,
        imp_bur,
        groupe,
        sgroupe,
        '021-Actions des programmes' AS libelle,
        SUM(budget) budget,
        SUM(mensuel) mensuel,
        CASE
            WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL THEN 0
            ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
        END AS pourc_mens, -- (2)/(1)
        SUM(reel) reel,
        SUM(reel_cumul) reel_cumul,
        CASE
            WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL THEN 0
            ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
        END AS pourc_r_cour,  -- (4)/(1)
        SUM(ecart_planif) ecart_planif,
        SUM(solde_total) solde_total,
        CASE
            WHEN SUM(budget) IS NULL OR  SUM(budget) = 0 THEN 0
            ELSE
                CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
                CONVERT(numeric(18,5),SUM(budget))
        END AS pourc_r_total, -- [(1)-(4)]/(1)
        SUM(budget_dern) budget_dern,
        SUM(reel_dern) reel_dern,
        CASE
            WHEN
                SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
                THEN 0
            ELSE
                CONVERT(numeric(18,5),SUM(reel_dern))/ CONVERT(numeric(18,5),SUM(budget_dern))
        END  AS pourc_r_dern  -- (6)/(5)
    FROM #etat
    WHERE
        sgroupe = '001-CHARGES'
        AND substring(libelle,3,1) in ('A','B','C','D','E','V', 'W', 'X')
    GROUP BY categorie, exercice, titre, periode, imp_bur, groupe, sgroupe
) etat

SELECT
    r.*,
    CASE
        WHEN LEFT(r.libelle,2) IN ('03','04','05','06','07','08', '09', '10') THEN 0
        WHEN LEFT(r.libelle,3) IN ('151','181') THEN  0
        ELSE 1
    END  AS calc_total
FROM #requete r
ORDER BY imp_bur,groupe,sgroupe,libelle,reel_dern

DROP TABLE #etat_section
DROP TABLE #cumul_ecritures
DROP TABLE #etat
DROP TABLE #requete
GO