Procédure pour l'interrogation de cumul

[auf_coda_additions.git] / auf_p_p4_intero_cumul.sql

--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