[#2359] Ajouté des colonnes à l'interrogation de cumul avancé

diff --git a/auf_p_p4_intero_cumul.sql b/auf_p_p4_intero_cumul.sql
index 7430ee3..7d84f3e 100644 (file)
--- a/auf_p_p4_intero_cumul.sql
+++ b/auf_p_p4_intero_cumul.sql
@@ -25,7 +25,67 @@ SELECT @annee AS exercice,
             ELSE 'Dépense'
        END AS nature,
        g.region,
-       g.implantation,
+       g.implantation AS code_implantation,
+       gi.sname AS nom_implantation,
+       CASE g.implantation
+            WHEN 'ICM1' THEN '10'
+            WHEN 'IGA1' THEN '11'
+            WHEN 'ITD1' THEN '12'
+            WHEN 'ICD1' THEN '13'
+            WHEN 'ICF1' THEN '14'
+            WHEN 'IBI1' THEN '15'
+            WHEN 'ICG1' THEN '16'
+            WHEN 'ICD2' THEN '18'
+            WHEN 'ICM2' THEN '19'
+            WHEN 'ISN1' THEN '20'
+            WHEN 'ISN2' THEN '21'
+            WHEN 'IGN1' THEN '22'
+            WHEN 'IMR1' THEN '23'
+            WHEN 'IML1' THEN '24'
+            WHEN 'IBF1' THEN '25'
+            WHEN 'INE1' THEN '26'
+            WHEN 'ICI1' THEN '27'
+            WHEN 'ITG1' THEN '28'
+            WHEN 'IBJ1' THEN '29'
+            WHEN 'IHT1' THEN '35'
+            WHEN 'IHT2' THEN '36'
+            WHEN 'IVN1' THEN '40'
+            WHEN 'IVN2' THEN '41'
+            WHEN 'IVN3' THEN '42'
+            WHEN 'IVN4' THEN '43'
+            WHEN 'ILA1' THEN '45'
+            WHEN 'ILA2' THEN '46'
+            WHEN 'IKH1' THEN '47'
+            WHEN 'IVU1' THEN '49'
+            WHEN 'ILB1' THEN '55'
+            WHEN 'ILB3' THEN '59'
+            WHEN 'ISY1' THEN '57'
+            WHEN 'ISY2' THEN '58'
+            WHEN 'IEG1' THEN '52'
+            WHEN 'IRO1' THEN '65'
+            WHEN 'IAM1' THEN '62'
+            WHEN 'IGE1' THEN '64'
+            WHEN 'IBG1' THEN '66'
+            WHEN 'IMD1' THEN '67'
+            WHEN 'IAL1' THEN '68'
+            WHEN 'IBG2' THEN '69'
+            WHEN 'IMG1' THEN '70'
+            WHEN 'IMG2' THEN '71'
+            WHEN 'IMU1' THEN '72'
+            WHEN 'IMU2' THEN '73'
+            WHEN 'IKM1' THEN '74'
+            WHEN 'ICA2' THEN '81'
+            WHEN 'IBR1' THEN '85'
+            WHEN 'IBE1' THEN '93'
+            WHEN 'IMA1' THEN '90'
+            WHEN 'ITN1' THEN '91'
+            WHEN 'IDZ1' THEN '94'
+            WHEN 'IDZ2' THEN '95'
+            WHEN 'IDZ3' THEN '96'
+            WHEN 'ICA1' THEN '01'
+            WHEN 'ICA2' THEN '02'
+       END AS no_implantation,
+       gr.name AS rubrique,
        SUM(CASE WHEN b.balcode = 'BUDGET' THEN b.full_value ELSE 0 END) AS budget,
        SUM(CASE WHEN b.balcode = 'ESTIME' THEN b.full_value ELSE 0 END) AS estime,
        SUM(CASE WHEN b.balcode = 'BUDGET-MENS' THEN b.full_value ELSE 0 END) AS mensul,
@@ -33,6 +93,9 @@ SELECT @annee AS exercice,
        SUM(CASE WHEN b.balcode = 'ENGAGE' THEN b.full_value ELSE 0 END) AS engage
 FROM auf_v_p4_projets_groupes g
      INNER JOIN oas_balance b ON b.el3 = g.code_budgetaire
+     LEFT JOIN oas_group gi ON gi.code = g.implantation
+     LEFT JOIN oas_group glr ON glr.code = b.el3
+     LEFT JOIN oas_group gr ON gr.code = glr.grpcode
 WHERE b.yr = @annee AND
       b.period BETWEEN @periodeD AND @periodeF AND
       b.curcode = 'EUR' AND