Commit | Line | Data |
---|---|---|
2295d186 EMS |
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 |