Procédure pour l'interrogation de cumul

diff --git a/auf_p_p4_intero_cumul.sql b/auf_p_p4_intero_cumul.sql
new file mode 100644 (file)
index 0000000..49c9b82
--- /dev/null
+++ b/auf_p_p4_intero_cumul.sql
@@ -0,0 +1,169 @@
+
+
+
+
+--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
+GO