From f7493cc2df0e1f5ab348be1cfcd9c9e3735bd36f Mon Sep 17 00:00:00 2001
From: Eric Mc Sween <eric.mcsween@auf.org>
Date: Thu, 24 Nov 2011 15:06:45 -0500
Subject: [PATCH] =?utf8?q?[#2359]=20Ajout=C3=A9=20des=20colonnes=20=C3=A0=20?=
 =?utf8?q?l'interrogation=20de=20cumul=20avanc=C3=A9?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 auf_p_p4_intero_cumul.sql |   65 ++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 64 insertions(+), 1 deletion(-)

diff --git a/auf_p_p4_intero_cumul.sql b/auf_p_p4_intero_cumul.sql
index 7430ee3..7d84f3e 100644
--- 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
-- 
1.7.10.4