· 7 years ago · Oct 11, 2018, 08:24 PM
1create procedure arballon.anl_dddcal @i_emp_dde smallint, @i_emp_hta smallint, @i_amb_dde smallint, @i_amb_hta smallint, @i_cli_dde char(16), @i_cli_hta char(16), @i_cls_dde char(6), @i_cls_hta char(6), @i_ges_dde char(6), @i_ges_hta char(6), @i_per_ref int, @i_can_mmm smallint, @i_det_anl smallint, @i_mod smallint, @i_fac_imp decimal(5,4) as
2begin
3 declare @s_per_dde int
4 declare @s_per_hta int
5 declare @s_per_cur int
6 declare @i_fch_ref datetime
7
8 set @i_fch_ref = arballon.gen_perfchini(@i_per_ref)
9 set @i_fch_ref = dateadd(month, 1, @i_fch_ref)
10 create table ##anlddd_9 (
11 cod_emp_9 smallint,
12 cod_amb_9 smallint,
13 tip_opr_9 smallint,
14 cod_opr_9 char(16),
15 cod_cls_9 char(6),
16 cod_ges_9 char(6),
17 ven_nto_9 decimal(18,2),
18 ven_mmm_9 smallint,
19 ven_min_mmm_9 datetime,
20 ven_cur_9 decimal(18,2)
21 )
22 insert into ##anlddd_9
23 select
24 a.cod_emp,
25 a.cod_amb,
26 a.tip_mae,
27 a.cod_mae,
28 a.cod_cls,
29 ' ',
30 0,
31 0,
32 '01/01/1900',
33 0
34 from genmae a
35 where a.cod_emp >= @i_emp_dde
36 and a.cod_emp <= @i_emp_hta
37 and a.cod_amb >= @i_amb_dde
38 and a.cod_amb <= @i_amb_hta
39 and a.tip_mae = 5
40 and a.cod_mae >= @i_cli_dde
41 and a.cod_mae <= @i_cli_hta
42 and a.cod_cls >= @i_cls_dde
43 and a.cod_cls <= @i_cls_hta
44 update ##anlddd_9 set
45 cod_ges_9 = arballon.comcges(cod_emp_9, ' ', 0, 0, cod_amb_9, tip_opr_9, cod_opr_9, 0, ' ', ' ', ' ')
46 delete from ##anlddd_9
47 where cod_ges_9 < @i_ges_dde
48 or cod_ges_9 > @i_ges_hta
49 set @s_per_dde = year(cast(dateadd(month, -@i_can_mmm, @i_fch_ref) as datetime)) * 100 + month(cast(dateadd(month, -@i_can_mmm, @i_fch_ref) as datetime))
50 set @s_per_hta = year(cast(dateadd(month, -1, @i_fch_ref) as datetime)) * 100 + month(cast(dateadd(month, -1, @i_fch_ref) as datetime))
51 set @s_per_cur = year(cast(@i_fch_ref as datetime)) * 100 + month(cast(@i_fch_ref as datetime))
52 update ##anlddd_9 set
53 ven_nto_9 = (
54 select
55 sum(round(cast(((a.div_atc + a.div_tot) * a.cot_mda) * c.sgn as float), 2)) as "ven_nto_9"
56 from anlcab a
57 inner join anlcod b on
58 b.cod_emp = a.cod_emp and
59 b.cod_amb = a.cod_amb and
60 b.cod_anl = a.cod_anl and
61 b.flg_anl = 'C' and
62 b.tip_opr = tip_opr_9
63 inner join logope c on
64 c.cod_ope = a.cod_ope
65 where 1 = 1
66 and a.cod_emp = cod_emp_9
67 and a.cod_amb = cod_amb_9
68 and a.cta_anl = cod_opr_9
69 and a.est = 'S'
70 and (year(cast(a.fch_ope as datetime)) * 100 + month(cast(a.fch_ope as datetime))) >= @s_per_dde
71 and (year(cast(a.fch_ope as datetime)) * 100 + month(cast(a.fch_ope as datetime))) <= @s_per_hta
72 and round(cast(((a.div_atc + a.div_tot) * a.cot_mda) as float), 2) != 0
73 and not exists (
74 select
75 z.cod_emp
76 from genapl z
77 where 1 = 1
78 and z.cod_emp = a.cod_emp
79 and z.cod_ref = a.cod_ope
80 and z.cen_ref = a.cen_ope
81 and z.nro_ref = a.nro_ope
82 and z.anl_ant = a.cod_anl
83 )
84 and exists (
85 select
86 1
87 from comcab x
88 where 1 = 1
89 and x.cod_emp = a.cod_emp
90 and x.cod_ope = a.cod_ope
91 and x.cen_ope = a.cen_ope
92 and x.nro_ope = a.nro_ope
93 )
94 ),
95 ven_min_mmm_9 = (
96 select
97 min(convert(datetime, cast(cast(1 as int) as varchar) + '/' + cast(cast(month(cast(a.fch_ope as datetime)) as int) as varchar) + '/' + cast(cast(year(cast(a.fch_ope as datetime)) as int) as varchar), 103)) as "ven_min_mmm_9"
98 from anlcab a
99 inner join anlcod b on
100 b.cod_emp = a.cod_emp and
101 b.cod_amb = a.cod_amb and
102 b.cod_anl = a.cod_anl and
103 b.flg_anl = 'C' and
104 b.tip_opr = tip_opr_9
105 inner join logope c on
106 c.cod_ope = a.cod_ope
107 where 1 = 1
108 and a.cod_emp = cod_emp_9
109 and a.cod_amb = cod_amb_9
110 and a.cta_anl = cod_opr_9
111 and a.est = 'S'
112 and (year(cast(a.fch_ope as datetime)) * 100 + month(cast(a.fch_ope as datetime))) >= @s_per_dde
113 and (year(cast(a.fch_ope as datetime)) * 100 + month(cast(a.fch_ope as datetime))) <= @s_per_hta
114 and round(cast(((a.div_atc + a.div_tot) * a.cot_mda) as float), 2) != 0
115 and not exists (
116 select
117 z.cod_emp
118 from genapl z
119 where 1 = 1
120 and z.cod_emp = a.cod_emp
121 and z.cod_ref = a.cod_ope
122 and z.cen_ref = a.cen_ope
123 and z.nro_ref = a.nro_ope
124 and z.anl_ant = a.cod_anl
125 )
126 and exists (
127 select
128 1
129 from comcab x
130 where 1 = 1
131 and x.cod_emp = a.cod_emp
132 and x.cod_ope = a.cod_ope
133 and x.cen_ope = a.cen_ope
134 and x.nro_ope = a.nro_ope
135 )
136 ),
137 ven_cur_9 = (
138 select
139 sum(round(cast(((a.div_atc + a.div_tot) * a.cot_mda) * c.sgn as float), 2))
140 from anlcab a
141 inner join anlcod b on
142 b.cod_emp = a.cod_emp and
143 b.cod_amb = a.cod_amb and
144 b.cod_anl = a.cod_anl and
145 b.flg_anl = 'C' and
146 b.tip_opr = tip_opr_9
147 inner join logope c on
148 c.cod_ope = a.cod_ope
149 where 1 = 1
150 and a.cod_emp = cod_emp_9
151 and a.cod_amb = cod_amb_9
152 and a.cta_anl = cod_opr_9
153 and a.est = 'S'
154 and (year(cast(a.fch_ope as datetime)) * 100 + month(cast(a.fch_ope as datetime))) = @s_per_cur
155 and round(cast(((a.div_atc + a.div_tot) * a.cot_mda) as float), 2) != 0
156 and not exists (
157 select
158 z.cod_emp
159 from genapl z
160 where z.cod_emp = a.cod_emp
161 and z.cod_ref = a.cod_ope
162 and z.cen_ref = a.cen_ope
163 and z.nro_ref = a.nro_ope
164 and z.anl_ant = a.cod_anl
165 )
166 and exists (
167 select
168 1
169 from comcab x
170 where 1 = 1
171 and x.cod_emp = a.cod_emp
172 and x.cod_ope = a.cod_ope
173 and x.cen_ope = a.cen_ope
174 and x.nro_ope = a.nro_ope
175 )
176 )
177 update ##anlddd_9 set
178 ven_mmm_9 = month(cast(dateadd(month, -1, @i_fch_ref) as datetime)) - month(cast(ven_min_mmm_9 as datetime)) + (year(cast(dateadd(month, -1, @i_fch_ref) as datetime)) - year(cast(ven_min_mmm_9 as datetime))) * 12 + 1
179 create table ##anlddd_10 (
180 cod_emp_10 smallint,
181 cod_amb_10 smallint,
182 tip_opr_10 smallint,
183 cod_opr_10 char(16),
184 cod_cls_10 char(6),
185 cod_ges_10 char(6),
186 cod_anl_10 smallint,
187 pen_ope_10 decimal(18,2),
188 ven_nto_10 decimal(18,2),
189 ven_cur_10 decimal(18,2),
190 ven_mmm_10 smallint,
191 ven_pro_10 decimal(18,2),
192 ddd_10 decimal(18,2)
193 )
194 if @i_det_anl = 1
195 begin
196 insert into ##anlddd_10
197 select
198 a.cod_emp_9,
199 a.cod_amb_9,
200 a.tip_opr_9,
201 a.cod_opr_9,
202 a.cod_cls_9,
203 a.cod_ges_9,
204 b.cod_anl,
205 0,
206 a.ven_nto_9,
207 a.ven_cur_9,
208 a.ven_mmm_9,
209 0,
210 0
211 from ##anlddd_9 a
212 inner join ##anlddd_1 b on
213 b.cod_emp = a.cod_emp_9 and
214 b.cod_amb = a.cod_amb_9 and
215 b.tip_opr = a.tip_opr_9
216 where 1 = 1
217 and not exists (
218 select
219 1
220 from ##anlddd_1A x
221 where 1 = 1
222 and x.cod_emp = a.cod_emp_9
223 and x.cod_amb = a.cod_amb_9
224 and x.tip_mae = a.tip_opr_9
225 and x.cod_mae = a.cod_opr_9
226 )
227 update ##anlddd_10 set
228 pen_ope_10 = (
229 select
230 sum(a.dal_ope)
231 from anlsdo a
232 where a.tip_mov in ('0', 'S')
233 and a.per_sdo <= @s_per_hta
234 and a.cod_anl = cod_anl_10
235 and a.cta_anl = cod_opr_10
236 and a.cod_emp = cod_emp_10
237 and a.cod_amb = cod_amb_10
238 and a.tip_opr = tip_opr_10
239 )
240 end --if
241 else
242 begin
243 insert into ##anlddd_10
244 select
245 a.cod_emp_9,
246 a.cod_amb_9,
247 a.tip_opr_9,
248 a.cod_opr_9,
249 a.cod_cls_9,
250 a.cod_ges_9,
251 0,
252 0,
253 a.ven_nto_9,
254 a.ven_cur_9,
255 a.ven_mmm_9,
256 0,
257 0
258 from ##anlddd_9 a
259 where 1 = 1
260 and not exists (
261 select
262 1
263 from ##anlddd_1A x
264 where 1 = 1
265 and x.cod_emp = a.cod_emp_9
266 and x.cod_amb = a.cod_amb_9
267 and x.tip_mae = a.tip_opr_9
268 and x.cod_mae = a.cod_opr_9
269 )
270 update ##anlddd_10 set
271 pen_ope_10 = (
272 select
273 sum(a.dal_ope)
274 from anlsdo a
275 where a.tip_mov in ('0', 'S')
276 and a.per_sdo <= @s_per_hta
277 and a.cod_anl in (
278 select
279 cod_anl
280 from ##anlddd_1 b
281 where b.cod_emp = a.cod_emp
282 and b.cod_amb = a.cod_amb
283 )
284 and a.cta_anl = cod_opr_10
285 and a.cod_emp = cod_emp_10
286 and a.cod_amb = cod_amb_10
287 and a.tip_opr = tip_opr_10
288 )
289 end --if
290 update ##anlddd_10 set
291 pen_ope_10 = 0
292 where pen_ope_10 is null
293 delete from ##anlddd_10
294 where pen_ope_10 = 0
295 and (ven_nto_10 = 0
296 or ven_nto_10 is null)
297 if @i_mod = 1
298 begin
299 select *
300 into ##anlddd_11
301 from ##anlddd_10
302 where ven_nto_10 = 0
303 or ven_nto_10 is null
304 delete from ##anlddd_10
305 where ven_nto_10 = 0
306 or ven_nto_10 is null
307 end --if
308 else
309 begin
310 update ##anlddd_10 set
311 ddd_10 = null
312 where ven_nto_10 = 0
313 or ven_nto_10 is null
314 end --if
315 update ##anlddd_10 set
316 ven_pro_10 = cast(ven_nto_10 as float) / cast(ven_mmm_10 as float)
317 where ven_mmm_10 <> 0
318 update ##anlddd_10 set
319 ddd_10 = cast(pen_ope_10 as float) / cast(ven_pro_10 as float) * 30
320 where ven_pro_10 <> 0
321end
322GO