+++ /dev/null
-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 AS code_implantation,
- gi.sname AS nom_implantation,
- CASE g.implantation
- WHEN 'ICM1' THEN '10'
- WHEN 'IGA1' THEN '11'
- WHEN 'ITD1' THEN '12'
- WHEN 'ICD1' THEN '13'
- WHEN 'ICF1' THEN '14'
- WHEN 'IBI1' THEN '15'
- WHEN 'ICG1' THEN '16'
- WHEN 'ICD2' THEN '18'
- WHEN 'ICM2' THEN '19'
- WHEN 'ISN1' THEN '20'
- WHEN 'ISN2' THEN '21'
- WHEN 'IGN1' THEN '22'
- WHEN 'IMR1' THEN '23'
- WHEN 'IML1' THEN '24'
- WHEN 'IBF1' THEN '25'
- WHEN 'INE1' THEN '26'
- WHEN 'ICI1' THEN '27'
- WHEN 'ITG1' THEN '28'
- WHEN 'IBJ1' THEN '29'
- WHEN 'IHT1' THEN '35'
- WHEN 'IHT2' THEN '36'
- WHEN 'IVN1' THEN '40'
- WHEN 'IVN2' THEN '41'
- WHEN 'IVN3' THEN '42'
- WHEN 'IVN4' THEN '43'
- WHEN 'ILA1' THEN '45'
- WHEN 'ILA2' THEN '46'
- WHEN 'IKH1' THEN '47'
- WHEN 'IVU1' THEN '49'
- WHEN 'ILB1' THEN '55'
- WHEN 'ILB3' THEN '59'
- WHEN 'ISY1' THEN '57'
- WHEN 'ISY2' THEN '58'
- WHEN 'IEG1' THEN '52'
- WHEN 'IRO1' THEN '65'
- WHEN 'IAM1' THEN '62'
- WHEN 'IGE1' THEN '64'
- WHEN 'IBG1' THEN '66'
- WHEN 'IMD1' THEN '67'
- WHEN 'IAL1' THEN '68'
- WHEN 'IBG2' THEN '69'
- WHEN 'IMG1' THEN '70'
- WHEN 'IMG2' THEN '71'
- WHEN 'IMU1' THEN '72'
- WHEN 'IMU2' THEN '73'
- WHEN 'IKM1' THEN '74'
- WHEN 'ICA2' THEN '81'
- WHEN 'IBR1' THEN '85'
- WHEN 'IBE1' THEN '93'
- WHEN 'IMA1' THEN '90'
- WHEN 'ITN1' THEN '91'
- WHEN 'IDZ1' THEN '94'
- WHEN 'IDZ2' THEN '95'
- WHEN 'IDZ3' THEN '96'
- WHEN 'ICA1' THEN '01'
- WHEN 'ICA2' THEN '02'
- END AS no_implantation,
- gr.name AS rubrique,
- 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
- LEFT JOIN oas_group gi ON gi.code = g.implantation
- LEFT JOIN oas_group glr ON glr.code = b.el3
- LEFT JOIN oas_group gr ON gr.code = glr.grpcode
-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