X-Git-Url: http://git.auf.org/?p=auf_coda_additions.git;a=blobdiff_plain;f=auf_p_p4_intero_cumul.sql;h=7430ee311e7e283341a6b1898981a76800c478c6;hp=49c9b828a83d3cb0cb42764291d0857467052f7b;hb=9c0c19e6ac35cdd5320059d74a23ab90a5273561;hpb=2295d18677c8500c8b47374c06f93106f5c42074

diff --git a/auf_p_p4_intero_cumul.sql b/auf_p_p4_intero_cumul.sql
index 49c9b82..7430ee3 100644
--- a/auf_p_p4_intero_cumul.sql
+++ b/auf_p_p4_intero_cumul.sql
@@ -1,169 +1,48 @@
-
-
-
-
---exec  auf_p_p4_intero_cumul 2011,1,12,'91060PJ','','',''
-
-
-CREATE                 procedure auf_p_p4_intero_cumul
-
-        @annee int ,
-        @periodeD int,
-        @periodeF int,
-        @projet varchar(7),
-        @region  varchar(12),
-        @implantation  varchar(12),
-        @el1 varchar(6)
-      
-        
-as
-       
-
-SET NOCOUNT ON
-
-
-create table #cumuls (el1  varchar(10),
-                      el3  varchar(12),
-                      balcode  varchar(12),
-                      nature varchar(30),
-                      montant money null)
-
-
-insert into #cumuls
-
-select 
-                  el1,
-                  el3, 
-                  balcode ,
-                  case when el1 like '97%' then
-                    'Recette'
-		   when  el1 like '2%' then
-                    'Dépense'  
-		   when  (el1 like '1%' AND el3 like '9003%') then
-                    'Dépense'  
-                   when  (el1 like '1%' AND el3 not like '9003%') then
-                    'Recette'
-                   when el1 like '6%' then
-                    'Dépense'  
-                   when el1 like '7%' then
-                    'Recette'  
-                   when el1 like '86%' then
-                    'Dépense BSTG'  
-                   when el1 like '87%' then
-                    'Recette BSTG'  
-		   when el1 like '987%' then
-                    'Recette BSTG'  
-		   when el1 like '986%' then
-                    'Dépense BSTG'  		 
-                   else 
-                    'Dépense'  		 
-		   end ,             
-                   sum(full_value) mont  
-
-
-from    oas_balance
-
-where   yr = @annee and
-        period between @periodeD and @periodeF and
-        curcode = 'EUR' and 
-        repbasis = 5 and  
-        balcode not like  'DBM%' and
-        el3 like @projet + '%' and
-        el1 not like  '4%' and
-        el1 like @el1  + '%' 
--------------------------------------
-       
-group by el1,el3, balcode
-
-
-
-select  @annee exercice,
-        cumul.el1,
-        cumul.el3,
-        groupe.nom_court,
-        groupe.nom,
-        cumul.nature,
-        groupe.region,
-        groupe.implantation,
-        --groupe.implantation_nc,
-
-        sum(isnull(budget.montant,0)) budget,
-        sum(isnull(estime.montant,0)) estime,
-        sum(isnull(mensul.montant,0)) mensul,
-        sum(isnull(reel.montant,0)) reel ,
-        sum(isnull(engage.montant,0)) engage
-
-from auf_v_p4_projets_groupes groupe,
-
-     ( select distinct el1,el3,nature
-       from   #cumuls ) cumul left join 
-	                           ( select el1,
-	                                    el3,
-	                                    balcode,
-	                                    montant 
-	                             from #cumuls) budget
-
-			             on ( cumul.el1 = budget.el1 and
-			                  cumul.el3 = budget.el3 and
-			                  budget.balcode = 'BUDGET' )
-			
-			    left join 
-                                  ( select el1, 
-                                           el3, 
-                                           balcode,
-                                           montant 
-
-                                    from #cumuls) estime
-				  
-                                    on ( cumul.el1 = estime.el1 and
-				         cumul.el3 = estime.el3 and
-				         estime.balcode = 'ESTIME'  )
-
-			   left join 
-                                 ( select el1,
-                                          el3,
-                                          balcode,
-                                          montant  
-
-                                   from #cumuls) mensul
-
-			           on ( cumul.el1 = mensul.el1 and
-			                cumul.el3 = mensul.el3 and
-			                mensul.balcode = 'BUDGET-MENS' )
-
-			  left join 
-                                ( select el1,
-                                         el3,
-                                         balcode,
-                                         montant
-                             
-                                  from #cumuls) engage
-
- 			          on ( cumul.el1 = engage.el1 and
-			               cumul.el3 = engage.el3 and
-			               engage.balcode = 'ENGAGE' )
-
-			 left join 
-                               ( select el1,
-                                        el3,
-                                        balcode,
-                                        montant 
-                                 from #cumuls) reel
-			        
-                                 on ( cumul.el1 = reel.el1 and
-			              cumul.el3 = reel.el3 and
-			              reel.balcode = 'REEL'  )
-
-where cumul.el3 = groupe.code_budgetaire and 
-      groupe.region  like  @region + '%'  and
-      groupe.implantation  like 'I' + rtrim(ltrim(@implantation)) + '%'  
-      
-group by cumul.el1,
-         cumul.el3,
-         groupe.nom_court,
-         groupe.nom,
-         cumul.nature,
-         groupe.region ,
-         groupe.implantation
-         --groupe.implantation_nc
+CREATE PROCEDURE auf_p_p4_intero_cumul
+    @annee int,
+    @periodeD int,
+    @periodeF int,
+    @projet varchar(7),
+    @region varchar(12),
+    @implantation varchar(12),
+    @el1 varchar(6)
+
+AS
+
+SET NOCOUNT ON;
+
+SELECT @annee AS exercice,
+       b.el1,
+       b.el3,
+       g.nom_court,
+       g.nom,
+       CASE WHEN b.el1 LIKE '97%' OR
+                 (b.el1 LIKE '1%' AND b.el3 NOT LIKE '9003%') OR
+                 b.el1 LIKE '7%'
+            THEN 'Recette'
+            WHEN b.el1 LIKE '86%' OR b.el1 LIKE '986%' THEN 'Dépense BSTG'
+            WHEN b.el1 LIKE '87%' OR b.el1 LIKE '987%' THEN 'Recette BSTG'
+            ELSE 'Dépense'
+       END AS nature,
+       g.region,
+       g.implantation,
+       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,
+       SUM(CASE WHEN b.balcode = 'REEL' THEN b.full_value ELSE 0 END) AS reel,
+       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
+WHERE b.yr = @annee AND
+      b.period BETWEEN @periodeD AND @periodeF AND
+      b.curcode = 'EUR' AND
+      b.repbasis = 5 AND
+      b.balcode NOT LIKE 'DBM%' AND
+      b.el3 LIKE @projet + '%' AND
+      b.el1 NOT LIKE '4%' AND
+      b.el1 LIKE @el1 + '%' AND
+      g.region LIKE @region + '%' AND
+      g.implantation LIKE 'I' + RTRIM(LTRIM(@implantation)) + '%'
+GROUP BY b.el1, b.el3, g.nom_court, g.nom, g.region, g.implantation
+ORDER BY b.el1, b.el3;
 GO