-
-
-
-
---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
projets / auf_coda_additions.git / blobdiff