ELSE 'Dépense'
END AS nature,
g.region,
- g.implantation,
+ 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 = '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