auf_p_p4_intero_cumul.sql

   1
   2
   3
   4
   5 --exec  auf_p_p4_intero_cumul 2011,1,12,'91060PJ','','',''
   6
   7
   8 CREATE                 procedure auf_p_p4_intero_cumul
   9
  10         @annee int ,
  11         @periodeD int,
  12         @periodeF int,
  13         @projet varchar(7),
  14         @region  varchar(12),
  15         @implantation  varchar(12),
  16         @el1 varchar(6)
  17
  18
  19 as
  20
  21
  22 SET NOCOUNT ON
  23
  24
  25 create table #cumuls (el1  varchar(10),
  26                       el3  varchar(12),
  27                       balcode  varchar(12),
  28                       nature varchar(30),
  29                       montant money null)
  30
  31
  32 insert into #cumuls
  33
  34 select
  35                   el1,
  36                   el3,
  37                   balcode ,
  38                   case when el1 like '97%' then
  39                     'Recette'
  40                    when  el1 like '2%' then
  41                     'Dépense'
  42                    when  (el1 like '1%' AND el3 like '9003%') then
  43                     'Dépense'
  44                    when  (el1 like '1%' AND el3 not like '9003%') then
  45                     'Recette'
  46                    when el1 like '6%' then
  47                     'Dépense'
  48                    when el1 like '7%' then
  49                     'Recette'
  50                    when el1 like '86%' then
  51                     'Dépense BSTG'
  52                    when el1 like '87%' then
  53                     'Recette BSTG'
  54                    when el1 like '987%' then
  55                     'Recette BSTG'
  56                    when el1 like '986%' then
  57                     'Dépense BSTG'
  58                    else
  59                     'Dépense'
  60                    end ,
  61                    sum(full_value) mont
  62
  63
  64 from    oas_balance
  65
  66 where   yr = @annee and
  67         period between @periodeD and @periodeF and
  68         curcode = 'EUR' and
  69         repbasis = 5 and
  70         balcode not like  'DBM%' and
  71         el3 like @projet + '%' and
  72         el1 not like  '4%' and
  73         el1 like @el1  + '%'
  74 -------------------------------------
  75
  76 group by el1,el3, balcode
  77
  78
  79
  80 select  @annee exercice,
  81         cumul.el1,
  82         cumul.el3,
  83         groupe.nom_court,
  84         groupe.nom,
  85         cumul.nature,
  86         groupe.region,
  87         groupe.implantation,
  88         --groupe.implantation_nc,
  89
  90         sum(isnull(budget.montant,0)) budget,
  91         sum(isnull(estime.montant,0)) estime,
  92         sum(isnull(mensul.montant,0)) mensul,
  93         sum(isnull(reel.montant,0)) reel ,
  94         sum(isnull(engage.montant,0)) engage
  95
  96 from auf_v_p4_projets_groupes groupe,
  97
  98      ( select distinct el1,el3,nature
  99        from   #cumuls ) cumul left join
 100                                    ( select el1,
 101                                             el3,
 102                                             balcode,
 103                                             montant
 104                                      from #cumuls) budget
 105
 106                                      on ( cumul.el1 = budget.el1 and
 107                                           cumul.el3 = budget.el3 and
 108                                           budget.balcode = 'BUDGET' )
 109
 110                             left join
 111                                   ( select el1,
 112                                            el3,
 113                                            balcode,
 114                                            montant
 115
 116                                     from #cumuls) estime
 117
 118                                     on ( cumul.el1 = estime.el1 and
 119                                          cumul.el3 = estime.el3 and
 120                                          estime.balcode = 'ESTIME'  )
 121
 122                            left join
 123                                  ( select el1,
 124                                           el3,
 125                                           balcode,
 126                                           montant
 127
 128                                    from #cumuls) mensul
 129
 130                                    on ( cumul.el1 = mensul.el1 and
 131                                         cumul.el3 = mensul.el3 and
 132                                         mensul.balcode = 'BUDGET-MENS' )
 133
 134                           left join
 135                                 ( select el1,
 136                                          el3,
 137                                          balcode,
 138                                          montant
 139
 140                                   from #cumuls) engage
 141
 142                                   on ( cumul.el1 = engage.el1 and
 143                                        cumul.el3 = engage.el3 and
 144                                        engage.balcode = 'ENGAGE' )
 145
 146                          left join
 147                                ( select el1,
 148                                         el3,
 149                                         balcode,
 150                                         montant
 151                                  from #cumuls) reel
 152
 153                                  on ( cumul.el1 = reel.el1 and
 154                                       cumul.el3 = reel.el3 and
 155                                       reel.balcode = 'REEL'  )
 156
 157 where cumul.el3 = groupe.code_budgetaire and
 158       groupe.region  like  @region + '%'  and
 159       groupe.implantation  like 'I' + rtrim(ltrim(@implantation)) + '%'
 160
 161 group by cumul.el1,
 162          cumul.el3,
 163          groupe.nom_court,
 164          groupe.nom,
 165          cumul.nature,
 166          groupe.region ,
 167          groupe.implantation
 168          --groupe.implantation_nc
 169 GO