db/procedures/auf_p_intero_cumul_dep_rec.sql

   1 USE [coda-prod]
   2 GO
   3
   4 SET ANSI_NULLS OFF
   5 GO
   6 SET QUOTED_IDENTIFIER OFF
   7 GO
   8
   9 ALTER PROCEDURE [dbo].[auf_p_intero_cumul_dep_rec]
  10     @projet varchar(6),
  11     @exercice int,
  12     @groupe varchar(10)
  13 AS
  14 SET NOCOUNT ON
  15 DECLARE @periode int
  16 DECLARE @like_el3 varchar(20)
  17
  18 SELECT @periode = 12
  19
  20 IF isnumeric(@projet) = 1 OR len(@projet) > 2
  21     SELECT @like_el3 = @projet+'%'
  22 ELSE
  23     SELECT @like_el3 = '%'+@projet
  24
  25 CREATE TABLE #cumuls (
  26     el3  varchar(12),
  27     typecumul varchar(12) ,
  28     montant money null
  29 )
  30
  31 -- DEPENSES BUDGET
  32 INSERT INTO #cumuls
  33 SELECT
  34     el3,
  35     'DEP-BUDGET' typecumul,
  36     SUM(full_value) mont
  37 FROM oas_balance, oas_grplist imp
  38 WHERE
  39     oas_balance.cmpcode = 'AUF'
  40     AND imp.cmpcode = 'AUF'
  41     AND yr = @exercice
  42     AND period BETWEEN 1 AND @periode
  43     AND curcode = 'EUR'
  44     AND repbasis = 5
  45     AND balcode ='BUDGET'
  46     AND (el1 LIKE '2%' OR el1 LIKE '6%' OR el1 LIKE '9%')
  47     -- on exclut les repartitions --
  48     AND el1 NOT LIKE '96%'
  49     AND el1 NOT LIKE '97%'
  50     AND el1 NOT LIKE '98%'
  51     AND el3 LIKE @like_el3
  52     AND el3 = imp.code
  53     AND imp.elmlevel = 3
  54     AND imp.grpcode LIKE @groupe
  55 GROUP BY el3
  56
  57 -- DEPENSES REEL
  58 INSERT INTO #cumuls
  59 SELECT
  60     el3,
  61     'DEP-REEL' typecumul,
  62     SUM(full_value) mont
  63 FROM oas_balance, oas_grplist imp
  64 WHERE
  65     oas_balance.cmpcode = 'AUF'
  66     AND imp.cmpcode = 'AUF'
  67     AND yr = @exercice
  68     AND period BETWEEN 1 AND @periode
  69     AND curcode = 'EUR'
  70     AND repbasis = 5
  71     AND balcode ='REEL'
  72     AND (el1 LIKE '2%' OR el1 LIKE '6%' OR el1 LIKE '9%')
  73     -- on exclut les repartitions
  74     AND el1 NOT LIKE '96%'
  75     AND el1 NOT LIKE '97%'
  76     AND el1 NOT LIKE '98%'
  77     AND el3 LIKE @like_el3
  78     AND el3 = imp.code
  79     AND imp.elmlevel = 3
  80     AND imp.grpcode LIKE @groupe
  81 GROUP BY el3
  82
  83 -- RECETTES BUDGET
  84 INSERT INTO #cumuls
  85 SELECT
  86     el3,
  87     'REC-BUDGET' typecumul,
  88     SUM(full_value) mont
  89 FROM oas_balance, oas_grplist imp
  90 WHERE
  91     oas_balance.cmpcode = 'AUF'
  92     AND imp.cmpcode = 'AUF'
  93     AND yr = @exercice
  94     AND period BETWEEN 1 AND @periode
  95     AND curcode = 'EUR'
  96     AND repbasis = 5
  97     AND balcode ='BUDGET'
  98     AND (el1 LIKE '1%' OR el1 LIKE '7%')
  99     AND el3 LIKE @like_el3
 100     AND el3 = imp.code
 101     AND imp.elmlevel = 3
 102     AND imp.grpcode LIKE @groupe
 103 GROUP BY el3
 104
 105 -- RECETTES REEL
 106 INSERT INTO #cumuls
 107 SELECT
 108     el3,
 109     'REC-REEL' typecumul,
 110     SUM(full_value) mont
 111 FROM oas_balance, oas_grplist imp
 112 WHERE
 113     oas_balance.cmpcode = 'AUF'
 114     AND imp.cmpcode = 'AUF'
 115     AND yr = @exercice
 116     AND period BETWEEN 1 AND @periode
 117     AND curcode = 'EUR'
 118     AND repbasis = 5
 119     AND balcode ='REEL'
 120     AND (el1 LIKE '1%' OR el1 LIKE '7%')
 121     AND el3 LIKE @like_el3
 122     AND el3 = imp.code
 123     AND imp.elmlevel = 3
 124     AND imp.grpcode LIKE @groupe
 125 GROUP BY el3
 126
 127 -- ENGAGEMENT --
 128 INSERT INTO #cumuls
 129 SELECT
 130     el3,
 131     'ENGAGEMENT' typecumul,
 132     SUM(full_value) mont
 133 FROM oas_balance, oas_grplist imp
 134 WHERE
 135     oas_balance.cmpcode = 'AUF'
 136     AND imp.cmpcode = 'AUF'
 137     AND yr = @exercice
 138     AND period BETWEEN 1 AND @periode
 139     AND curcode = 'EUR'
 140     AND repbasis = 5
 141     AND balcode = 'ENGAGEMENT'
 142     AND el1 NOT LIKE '4%'
 143     AND el1 NOT LIKE '96%'
 144     AND el1 NOT LIKE '97%'
 145     AND el1 NOT LIKE '98%'
 146     AND len(rtrim(el3)) <> 8
 147     AND LEFT(el3,2) <> '95'
 148     AND el3 LIKE @like_el3
 149     AND el3 = imp.code
 150     AND imp.elmlevel = 3
 151     AND imp.grpcode LIKE @groupe
 152 GROUP BY el3
 153
 154 SELECT
 155     cumul.el3 projetPoste,
 156     e.sname nomProjet,
 157     e.[name] nomPoste,
 158     SUM(ISNULL(budget_depenses.montant,0)) budget_depense,
 159     SUM(ISNULL(budget_recettes.montant,0)) budget_recette,
 160     SUM(ISNULL(reel_recettes.montant,0)) reel_recette,
 161     SUM(ISNULL(reel_depenses.montant,0)) reel_depense,
 162     SUM(ISNULL(engages.montant,0)) engage
 163 FROM
 164     (
 165         SELECT DISTINCT el3
 166         FROM #cumuls
 167     ) cumul
 168     LEFT JOIN (
 169         SELECT el3, typecumul, montant
 170         FROM #cumuls
 171     ) budget_depenses
 172         ON cumul.el3 = budget_depenses.el3
 173         AND budget_depenses.typecumul = 'DEP-BUDGET'
 174     LEFT JOIN (
 175         SELECT el3, typecumul, montant
 176         FROM #cumuls
 177     ) budget_recettes
 178         ON cumul.el3 = budget_recettes.el3
 179         AND budget_recettes.typecumul = 'REC-BUDGET'
 180     LEFT JOIN (
 181         SELECT el3, typecumul, montant
 182         FROM #cumuls
 183     ) reel_recettes
 184         ON cumul.el3 = reel_recettes.el3
 185         AND reel_recettes.typecumul = 'REC-REEL'
 186     LEFT JOIN (
 187         SELECT el3, typecumul, montant
 188         FROM #cumuls
 189     ) reel_depenses
 190         ON cumul.el3 = reel_depenses.el3
 191         AND reel_depenses.typecumul = 'DEP-REEL'
 192     LEFT JOIN (
 193         SELECT el3, typecumul, montant
 194         FROM #cumuls
 195     ) engages
 196         ON cumul.el3 = engages.el3
 197         AND engages.typecumul = 'ENGAGEMENT'
 198     LEFT JOIN  oas_element e
 199         ON cumul.el3 = e.code
 200         AND e.cmpcode = 'AUF'
 201         AND e.elmlevel = 3
 202 WHERE LEFT(cumul.el3,2) <> '95'
 203 GROUP BY cumul.el3, e.sname, e.[name]
 204 ORDER BY cumul.el3
 205 GO