· 8 years ago · Aug 08, 2018, 02:44 PM
1USE [arballon]
2GO
3/****** Object: StoredProcedure [arballon].[gencapl4] Script Date: 08/08/2018 11:31:52 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER procedure [arballon].[gencapl4] @tabla varchar(116), @i_cod_emp smallint, @i_cod_amb smallint, @i_cod_anl smallint, @i_tip_opr smallint, @i_cta_anl char(8), @i_cod_ope char(6), @i_cen_ope smallint, @i_nro_ope int, @i_res_neg smallint as
9begin
10 declare @s_x smallint
11 declare @s_a smallint
12 declare @s_v smallint
13 declare @s_eof smallint
14 declare @s_tip_ope smallint
15 declare @s_cta_ope char(8)
16 declare @s_anl_ope smallint
17 declare @s_cod_ope char(6)
18 declare @s_cen_ope smallint
19 declare @s_nro_ope int
20 declare @s_fch_ope datetime
21 declare @s_vto_ope datetime
22 declare @s_dal_ope decimal(18,2)
23 declare @s_tip_apl smallint
24 declare @s_cta_apl char(8)
25 declare @s_anl_apl smallint
26 declare @s_cod_apl char(6)
27 declare @s_cen_apl smallint
28 declare @s_nro_apl int
29 declare @s_fch_apl datetime
30 declare @s_vto_apl datetime
31 declare @s_dal_apl decimal(18,2)
32 declare @s_suma_apl decimal(18,2)
33 declare @s_tip_val smallint
34 declare @s_cta_val char(8)
35 declare @s_anl_val smallint
36 declare @s_cod_val char(6)
37 declare @s_cen_val smallint
38 declare @s_nro_val int
39 declare @s_vto_val datetime
40 declare @s_dal_val decimal(18,2)
41 declare @s_suma_val decimal(18,2)
42 declare @s_sum_tot decimal(18,2)
43 declare @s_sum_pos decimal(18,2)
44 declare @s_con_pos smallint
45 declare @s_x_pos smallint
46 declare @s_sum_neg decimal(18,2)
47 declare @s_acu_neg decimal(18,2)
48 declare @return_with_resume varchar(1024)
49
50 create table ##tmp1p_gencapl4 (
51 x smallint,
52 tip_opr smallint,
53 cta_anl char(8),
54 anl_ope smallint,
55 cod_ope char(6),
56 cen_ope smallint,
57 nro_ope int,
58 fch_ope datetime,
59 vto_ope datetime,
60 dal_ope decimal(18,2)
61 )
62 create table ##tmp1_gencapl4 (
63 x smallint,
64 tip_opr smallint,
65 cta_anl char(8),
66 anl_ope smallint,
67 cod_ope char(6),
68 cen_ope smallint,
69 nro_ope int,
70 fch_ope datetime,
71 vto_ope datetime,
72 dal_ope decimal(18,2)
73 )
74 create table ##tmp2p_gencapl4 (
75 x smallint,
76 tip_opr smallint,
77 cta_anl char(8),
78 anl_ope smallint,
79 cod_ope char(6),
80 cen_ope smallint,
81 nro_ope int,
82 vto_ope datetime,
83 dal_ope decimal(18,2)
84 )
85 create table ##tmp2_gencapl4 (
86 x smallint,
87 tip_opr smallint,
88 cta_anl char(8),
89 anl_ope smallint,
90 cod_ope char(6),
91 cen_ope smallint,
92 nro_ope int,
93 vto_ope datetime,
94 dal_ope decimal(18,2)
95 )
96 create table ##tmp3_gencapl4 (
97 x smallint,
98 tip_opr smallint,
99 cta_anl char(8),
100 anl_ope smallint,
101 cod_ope char(6),
102 cen_ope smallint,
103 nro_ope int,
104 fch_ope datetime,
105 vto_ope datetime,
106 dal_ope decimal(18,2)
107 )
108 set @s_a = 0
109 -- begin foreach ----------------------------------------
110 begin
111 declare @foreach_fetch_4 nvarchar(1024)
112 declare @foreach_cursor_4 varchar(116)
113 declare @foreach_temp_table_4 varchar(116)
114 set @foreach_cursor_4 = 'gencapl4_to_gencapl1_4' + cast(@@nestlevel as varchar(2))
115 set @foreach_temp_table_4 = '##gencapl4_to_gencapl1_4' + cast(@@nestlevel as varchar(2))
116 if object_id('tempdb..' + @foreach_temp_table_4) is not null
117 execute('delete from ' + @foreach_temp_table_4)
118 else
119 execute('create table ' + @foreach_temp_table_4 + ' (gencapl1_1 smallint, gencapl1_2 char(8), gencapl1_3 smallint, gencapl1_4 char(6), gencapl1_5 smallint, gencapl1_6 int, gencapl1_7 datetime, gencapl1_8 datetime, gencapl1_9 decimal(18,2))')
120 -- begin execute
121 if exists (select name from sysobjects where name = 'gencapl1' and type = 'P' and uid <> 1)
122 execute arballon.gencapl1 @foreach_temp_table_4, @i_cod_emp, @i_cod_amb, @i_cod_anl, @i_cod_ope, @i_cen_ope, @i_nro_ope
123 else
124 execute gencapl1 @foreach_temp_table_4, @i_cod_emp, @i_cod_amb, @i_cod_anl, @i_cod_ope, @i_cen_ope, @i_nro_ope
125 -- end execute
126 execute('declare ' + @foreach_cursor_4 + ' cursor static for select * from ' + @foreach_temp_table_4)
127 execute('open ' + @foreach_cursor_4)
128 set @foreach_fetch_4 = 'fetch next from ' + @foreach_cursor_4 + ' into @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope'
129 execute sp_executesql @foreach_fetch_4, N'@s_tip_ope smallint output, @s_cta_ope char(8) output, @s_anl_ope smallint output, @s_cod_ope char(6) output, @s_cen_ope smallint output, @s_nro_ope int output, @s_fch_ope datetime output, @s_vto_ope datetime output, @s_dal_ope decimal(18,2) output', @s_tip_ope output, @s_cta_ope output, @s_anl_ope output, @s_cod_ope output, @s_cen_ope output, @s_nro_ope output, @s_fch_ope output, @s_vto_ope output, @s_dal_ope output
130 while @@fetch_status = 0
131 begin
132 begin
133 set @s_a = @s_a + 1
134 insert into ##tmp1p_gencapl4
135 values (@s_a, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope)
136 end
137 execute sp_executesql @foreach_fetch_4, N'@s_tip_ope smallint output, @s_cta_ope char(8) output, @s_anl_ope smallint output, @s_cod_ope char(6) output, @s_cen_ope smallint output, @s_nro_ope int output, @s_fch_ope datetime output, @s_vto_ope datetime output, @s_dal_ope decimal(18,2) output', @s_tip_ope output, @s_cta_ope output, @s_anl_ope output, @s_cod_ope output, @s_cen_ope output, @s_nro_ope output, @s_fch_ope output, @s_vto_ope output, @s_dal_ope output
138 end
139 execute('close ' + @foreach_cursor_4)
140 execute('deallocate ' + @foreach_cursor_4)
141 execute('drop table ' + @foreach_temp_table_4)
142 end
143 -- end foreach ------------------------------------------
144 set @s_sum_tot = (
145 select
146 sum(dal_ope)
147 from ##tmp1p_gencapl4
148 )
149 if @s_sum_tot is null
150 begin
151 set @s_sum_tot = 0
152 end --if
153 if @s_sum_tot < 0
154 begin
155 update ##tmp1p_gencapl4 set
156 dal_ope = dal_ope * -1
157 where 1 = 1
158 end --if
159 if @i_res_neg = 0
160 begin
161 declare foreach_gencapl4_5 cursor local static for
162 select
163 x,
164 tip_opr,
165 cta_anl,
166 anl_ope,
167 cod_ope,
168 cen_ope,
169 nro_ope,
170 fch_ope,
171 vto_ope,
172 dal_ope
173 from ##tmp1p_gencapl4
174 where dal_ope < 0
175 open foreach_gencapl4_5
176 fetch next from foreach_gencapl4_5 into @s_x, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope
177 while @@fetch_status = 0
178 begin
179 begin
180 set @s_dal_ope = @s_dal_ope * -1
181 insert into ##tmp3_gencapl4
182 values (0, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope)
183 delete from ##tmp1p_gencapl4
184 where x = @s_x
185 end
186 fetch next from foreach_gencapl4_5 into @s_x, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope
187 end
188 close foreach_gencapl4_5
189 deallocate foreach_gencapl4_5
190 end --if
191 set @s_x = 0
192 declare foreach_gencapl4_6 cursor local static for
193 select
194 tip_opr,
195 cta_anl,
196 anl_ope,
197 cod_ope,
198 cen_ope,
199 nro_ope,
200 fch_ope,
201 vto_ope,
202 dal_ope
203 from ##tmp1p_gencapl4
204 order by vto_ope
205 open foreach_gencapl4_6
206 fetch next from foreach_gencapl4_6 into @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope
207 while @@fetch_status = 0
208 begin
209 begin
210 set @s_x = @s_x + 1
211 insert into ##tmp1_gencapl4
212 values (@s_x, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope)
213 end
214 fetch next from foreach_gencapl4_6 into @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope
215 end
216 close foreach_gencapl4_6
217 deallocate foreach_gencapl4_6
218 drop table ##tmp1p_gencapl4
219 if @i_res_neg <> 0
220 begin
221 set @s_con_pos = (
222 select
223 count(*)
224 from ##tmp1_gencapl4
225 where dal_ope > 0
226 )
227 set @s_sum_pos = (
228 select
229 sum(dal_ope)
230 from ##tmp1_gencapl4
231 where dal_ope > 0
232 )
233 if @s_sum_pos is null
234 begin
235 set @s_sum_pos = 0
236 end --if
237 set @s_sum_neg = (
238 select
239 sum(dal_ope)
240 from ##tmp1_gencapl4
241 where dal_ope < 0
242 )
243 if @s_sum_neg is null
244 begin
245 set @s_sum_neg = 0
246 end --if
247 set @s_sum_neg = @s_sum_neg * -1
248 if @s_sum_neg > 0
249 begin
250 set @s_x_pos = 1
251 set @s_acu_neg = 0
252 declare foreach_gencapl4_7 cursor local static for
253 select
254 x,
255 tip_opr,
256 cta_anl,
257 anl_ope,
258 cod_ope,
259 cen_ope,
260 nro_ope,
261 fch_ope,
262 vto_ope,
263 dal_ope
264 from ##tmp1_gencapl4
265 where dal_ope > 0
266 order by x
267 open foreach_gencapl4_7
268 fetch next from foreach_gencapl4_7 into @s_x, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope
269 while @@fetch_status = 0
270 begin
271 begin
272 if @s_x_pos < @s_con_pos
273 begin
274 update ##tmp1_gencapl4 set
275 dal_ope = @s_dal_ope - (@s_sum_neg * (cast(@s_dal_ope as float) / cast(@s_sum_pos as float)))
276 where x = @s_x
277 set @s_acu_neg = @s_acu_neg + (@s_sum_neg * (cast(@s_dal_ope as float) / cast(@s_sum_pos as float)))
278 end --if
279 else
280 begin
281 update ##tmp1_gencapl4 set
282 dal_ope = @s_dal_ope - (@s_sum_neg - @s_acu_neg)
283 where x = @s_x
284 end --if
285 set @s_x_pos = @s_x_pos + 1
286 end
287 fetch next from foreach_gencapl4_7 into @s_x, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_fch_ope, @s_vto_ope, @s_dal_ope
288 end
289 close foreach_gencapl4_7
290 deallocate foreach_gencapl4_7
291 update ##tmp1_gencapl4 set
292 dal_ope = 0
293 where dal_ope < 0
294 end --if
295 end --if
296 set @s_v = 0
297 -- begin foreach ----------------------------------------
298 begin
299 declare @foreach_fetch_8 nvarchar(1024)
300 declare @foreach_cursor_8 varchar(116)
301 declare @foreach_temp_table_8 varchar(116)
302 set @foreach_cursor_8 = 'gencapl4_to_gencapl2_8' + cast(@@nestlevel as varchar(2))
303 set @foreach_temp_table_8 = '##gencapl4_to_gencapl2_8' + cast(@@nestlevel as varchar(2))
304 if object_id('tempdb..' + @foreach_temp_table_8) is not null
305 execute('delete from ' + @foreach_temp_table_8)
306 else
307 execute('create table ' + @foreach_temp_table_8 + ' (gencapl2_1 smallint, gencapl2_2 char(8), gencapl2_3 smallint, gencapl2_4 char(6), gencapl2_5 smallint, gencapl2_6 int, gencapl2_7 datetime, gencapl2_8 decimal(18,2))')
308 -- begin execute
309 if exists (select name from sysobjects where name = 'gencapl2' and type = 'P' and uid <> 1)
310 execute arballon.gencapl2 @foreach_temp_table_8, @i_cod_emp, @i_cod_amb, @i_cod_anl, @i_cod_ope, @i_cen_ope, @i_nro_ope
311 else
312 execute gencapl2 @foreach_temp_table_8, @i_cod_emp, @i_cod_amb, @i_cod_anl, @i_cod_ope, @i_cen_ope, @i_nro_ope
313 -- end execute
314 execute('declare ' + @foreach_cursor_8 + ' cursor static for select * from ' + @foreach_temp_table_8)
315 execute('open ' + @foreach_cursor_8)
316 set @foreach_fetch_8 = 'fetch next from ' + @foreach_cursor_8 + ' into @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_vto_ope, @s_dal_ope'
317 execute sp_executesql @foreach_fetch_8, N'@s_tip_ope smallint output, @s_cta_ope char(8) output, @s_anl_ope smallint output, @s_cod_ope char(6) output, @s_cen_ope smallint output, @s_nro_ope int output, @s_vto_ope datetime output, @s_dal_ope decimal(18,2) output', @s_tip_ope output, @s_cta_ope output, @s_anl_ope output, @s_cod_ope output, @s_cen_ope output, @s_nro_ope output, @s_vto_ope output, @s_dal_ope output
318 while @@fetch_status = 0
319 begin
320 begin
321 set @s_v = @s_v + 1
322 insert into ##tmp2p_gencapl4
323 values (@s_v, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_vto_ope, @s_dal_ope)
324 end
325 execute sp_executesql @foreach_fetch_8, N'@s_tip_ope smallint output, @s_cta_ope char(8) output, @s_anl_ope smallint output, @s_cod_ope char(6) output, @s_cen_ope smallint output, @s_nro_ope int output, @s_vto_ope datetime output, @s_dal_ope decimal(18,2) output', @s_tip_ope output, @s_cta_ope output, @s_anl_ope output, @s_cod_ope output, @s_cen_ope output, @s_nro_ope output, @s_vto_ope output, @s_dal_ope output
326 end
327 execute('close ' + @foreach_cursor_8)
328 execute('deallocate ' + @foreach_cursor_8)
329 execute('drop table ' + @foreach_temp_table_8)
330 end
331 -- end foreach ------------------------------------------
332 set @s_sum_tot = (
333 select
334 sum(dal_ope)
335 from ##tmp2p_gencapl4
336 )
337 if @s_sum_tot is null
338 begin
339 set @s_sum_tot = 0
340 end --if
341 if @s_sum_tot < 0
342 begin
343 update ##tmp2p_gencapl4 set
344 dal_ope = dal_ope * -1
345 end --if
346 if @i_res_neg = 0
347 begin
348 insert into ##tmp2p_gencapl4
349 select
350 x,
351 tip_opr,
352 cta_anl,
353 anl_ope,
354 cod_ope,
355 cen_ope,
356 nro_ope,
357 vto_ope,
358 dal_ope
359 from ##tmp3_gencapl4
360 end --if
361 set @s_x = 0
362 declare foreach_gencapl4_9 cursor local static for
363 select
364 tip_opr,
365 cta_anl,
366 anl_ope,
367 cod_ope,
368 cen_ope,
369 nro_ope,
370 vto_ope,
371 dal_ope
372 from ##tmp2p_gencapl4
373 order by vto_ope
374 open foreach_gencapl4_9
375 fetch next from foreach_gencapl4_9 into @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_vto_ope, @s_dal_ope
376 while @@fetch_status = 0
377 begin
378 begin
379 set @s_x = @s_x + 1
380 insert into ##tmp2_gencapl4
381 values (@s_x, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_vto_ope, @s_dal_ope)
382 end
383 fetch next from foreach_gencapl4_9 into @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_vto_ope, @s_dal_ope
384 end
385 close foreach_gencapl4_9
386 deallocate foreach_gencapl4_9
387 drop table ##tmp2p_gencapl4
388 set @s_con_pos = (
389 select
390 count(*)
391 from ##tmp2_gencapl4
392 where dal_ope > 0
393 )
394 set @s_sum_pos = (
395 select
396 sum(dal_ope)
397 from ##tmp2_gencapl4
398 where dal_ope > 0
399 )
400 if @s_sum_pos is null
401 begin
402 set @s_sum_pos = 0
403 end --if
404 set @s_sum_neg = (
405 select
406 sum(dal_ope)
407 from ##tmp2_gencapl4
408 where dal_ope < 0
409 )
410 if @s_sum_neg is null
411 begin
412 set @s_sum_neg = 0
413 end --if
414 set @s_sum_neg = @s_sum_neg * -1
415 if @s_sum_neg > 0
416 begin
417 set @s_x_pos = 1
418 set @s_acu_neg = 0
419 declare foreach_gencapl4_10 cursor local static for
420 select
421 x,
422 tip_opr,
423 cta_anl,
424 anl_ope,
425 cod_ope,
426 cen_ope,
427 nro_ope,
428 vto_ope,
429 dal_ope
430 from ##tmp2_gencapl4
431 where dal_ope > 0
432 order by x
433 open foreach_gencapl4_10
434 fetch next from foreach_gencapl4_10 into @s_x, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_vto_ope, @s_dal_ope
435 while @@fetch_status = 0
436 begin
437 begin
438 if @s_x_pos < @s_con_pos
439 begin
440 update ##tmp2_gencapl4 set
441 dal_ope = @s_dal_ope - (@s_sum_neg * (cast(@s_dal_ope as float) / cast(@s_sum_pos as float)))
442 where x = @s_x
443 set @s_acu_neg = @s_acu_neg + (@s_sum_neg * (cast(@s_dal_ope as float) / cast(@s_sum_pos as float)))
444 end --if
445 else
446 begin
447 update ##tmp2_gencapl4 set
448 dal_ope = @s_dal_ope - (@s_sum_neg - @s_acu_neg)
449 where x = @s_x
450 end --if
451 set @s_x_pos = @s_x_pos + 1
452 end
453 fetch next from foreach_gencapl4_10 into @s_x, @s_tip_ope, @s_cta_ope, @s_anl_ope, @s_cod_ope, @s_cen_ope, @s_nro_ope, @s_vto_ope, @s_dal_ope
454 end
455 close foreach_gencapl4_10
456 deallocate foreach_gencapl4_10
457 update ##tmp2_gencapl4 set
458 dal_ope = 0
459 where dal_ope < 0
460 end --if
461 set @s_eof = 0
462 set @s_suma_apl = 0
463 set @s_a = 1
464 set @s_tip_apl = null
465 set @s_cta_apl = null
466 set @s_anl_apl = null
467 set @s_cod_apl = null
468 set @s_cen_apl = null
469 set @s_nro_apl = null
470 set @s_fch_apl = null
471 set @s_vto_apl = null
472 set @s_dal_apl = null
473 select @s_tip_apl = tip_opr, @s_cta_apl = cta_anl, @s_anl_apl = anl_ope, @s_cod_apl = cod_ope, @s_cen_apl = cen_ope, @s_nro_apl = nro_ope, @s_fch_apl = fch_ope, @s_vto_apl = vto_ope, @s_dal_apl = dal_ope
474 from ##tmp1_gencapl4
475 where x = @s_a
476 if @s_cod_apl is null
477 begin
478 set @s_eof = 1
479 set @s_dal_apl = 0
480 end --if
481 else
482 begin
483 set @s_eof = 0
484 end --if
485 set @s_suma_apl = @s_suma_apl + @s_dal_apl
486 if @s_eof = 1
487 begin
488 declare foreach_gencapl4_11 cursor local static for
489 select
490 x,
491 tip_opr,
492 cta_anl,
493 anl_ope,
494 cod_ope,
495 cen_ope,
496 nro_ope,
497 vto_ope,
498 dal_ope
499 from ##tmp2_gencapl4
500 order by x
501 open foreach_gencapl4_11
502 fetch next from foreach_gencapl4_11 into @s_x, @s_tip_val, @s_cta_val, @s_anl_val, @s_cod_val, @s_cen_val, @s_nro_val, @s_vto_val, @s_dal_val
503 while @@fetch_status = 0
504 begin
505 begin
506 set @return_with_resume = 'insert into ' + @tabla + ' values(' + coalesce(cast(@i_cod_emp as varchar(255)), 'null') + ', ' + coalesce(cast(@s_tip_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cta_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_anl_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cod_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_cen_val as varchar(255)), 'null') + ', ' + coalesce(cast(@s_nro_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_tip_opr as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@i_cta_anl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_cod_anl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(' ' as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(0 as varchar(255)), 'null') + ', ' + coalesce(cast(0 as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_dal_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@i_cod_ope as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_cen_ope as varchar(255)), 'null') + ', ' + coalesce(cast(@i_nro_ope as varchar(255)), 'null') + ')'
507 execute(@return_with_resume)
508 end
509 fetch next from foreach_gencapl4_11 into @s_x, @s_tip_val, @s_cta_val, @s_anl_val, @s_cod_val, @s_cen_val, @s_nro_val, @s_vto_val, @s_dal_val
510 end
511 close foreach_gencapl4_11
512 deallocate foreach_gencapl4_11
513 end --if
514 else
515 begin
516 set @s_suma_val = 0
517 set @s_v = 1
518 set @s_tip_val = null
519 set @s_cta_val = null
520 set @s_anl_val = null
521 set @s_cod_val = null
522 set @s_cen_val = null
523 set @s_nro_val = null
524 set @s_vto_val = null
525 set @s_dal_val = null
526 select @s_tip_val = tip_opr, @s_cta_val = cta_anl, @s_anl_val = anl_ope, @s_cod_val = cod_ope, @s_cen_val = cen_ope, @s_nro_val = nro_ope, @s_vto_val = vto_ope, @s_dal_val = dal_ope
527 from ##tmp2_gencapl4
528 where x = @s_v
529 if @s_cod_val is null
530 begin
531 set @s_eof = 1
532 set @s_dal_val = 0
533 end --if
534 else
535 begin
536 set @s_eof = 0
537 end --if
538 set @s_suma_val = @s_suma_val + @s_dal_val
539 if @s_eof <> 1
540 begin
541 while 1 = 1
542 begin
543 if @s_suma_apl > @s_suma_val
544 begin
545 set @s_dal_apl = @s_dal_apl - @s_dal_val
546 set @return_with_resume = 'insert into ' + @tabla + ' values(' + coalesce(cast(@i_cod_emp as varchar(255)), 'null') + ', ' + coalesce(cast(@s_tip_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cta_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_anl_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cod_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_cen_val as varchar(255)), 'null') + ', ' + coalesce(cast(@s_nro_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_tip_apl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cta_apl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_anl_apl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cod_apl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_cen_apl as varchar(255)), 'null') + ', ' + coalesce(cast(@s_nro_apl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_fch_apl as varchar(255)), 'null') + '''' + ', ' + '''' + coalesce(cast(@s_vto_apl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_dal_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@i_cod_ope as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_cen_ope as varchar(255)), 'null') + ', ' + coalesce(cast(@i_nro_ope as varchar(255)), 'null') + ')'
547 execute(@return_with_resume)
548 set @s_v = @s_v + 1
549 set @s_tip_val = null
550 set @s_cta_val = null
551 set @s_anl_val = null
552 set @s_cod_val = null
553 set @s_cen_val = null
554 set @s_nro_val = null
555 set @s_vto_val = null
556 set @s_dal_val = null
557 select @s_tip_val = tip_opr, @s_cta_val = cta_anl, @s_anl_val = anl_ope, @s_cod_val = cod_ope, @s_cen_val = cen_ope, @s_nro_val = nro_ope, @s_vto_val = vto_ope, @s_dal_val = dal_ope
558 from ##tmp2_gencapl4
559 where x = @s_v
560 if @s_cod_val is null
561 begin
562 set @s_eof = 1
563 set @s_dal_val = 0
564 end --if
565 else
566 begin
567 set @s_eof = 0
568 end --if
569 if @s_eof = 1
570 begin
571 break
572 end --if
573 set @s_suma_val = @s_suma_val + @s_dal_val
574 end --if
575 if @s_suma_val >= @s_suma_apl
576 begin
577 set @s_dal_val = @s_dal_val - @s_dal_apl
578 set @return_with_resume = 'insert into ' + @tabla + ' values(' + coalesce(cast(@i_cod_emp as varchar(255)), 'null') + ', ' + coalesce(cast(@s_tip_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cta_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_anl_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cod_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_cen_val as varchar(255)), 'null') + ', ' + coalesce(cast(@s_nro_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_tip_apl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cta_apl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_anl_apl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cod_apl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_cen_apl as varchar(255)), 'null') + ', ' + coalesce(cast(@s_nro_apl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_fch_apl as varchar(255)), 'null') + '''' + ', ' + '''' + coalesce(cast(@s_vto_apl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_dal_apl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@i_cod_ope as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_cen_ope as varchar(255)), 'null') + ', ' + coalesce(cast(@i_nro_ope as varchar(255)), 'null') + ')'
579 execute(@return_with_resume)
580 set @s_a = @s_a + 1
581 set @s_tip_apl = null
582 set @s_cta_apl = null
583 set @s_anl_apl = null
584 set @s_cod_apl = null
585 set @s_cen_apl = null
586 set @s_nro_apl = null
587 set @s_fch_apl = null
588 set @s_vto_apl = null
589 set @s_dal_apl = null
590 select @s_tip_apl = tip_opr, @s_cta_apl = cta_anl, @s_anl_apl = anl_ope, @s_cod_apl = cod_ope, @s_cen_apl = cen_ope, @s_nro_apl = nro_ope, @s_fch_apl = fch_ope, @s_vto_apl = vto_ope, @s_dal_apl = dal_ope
591 from ##tmp1_gencapl4
592 where x = @s_a
593 if @s_cod_apl is null
594 begin
595 set @s_eof = 1
596 set @s_dal_apl = 0
597 end --if
598 else
599 begin
600 set @s_eof = 0
601 end --if
602 if @s_eof = 1
603 begin
604 if @s_dal_val <> 0
605 begin
606 set @return_with_resume = 'insert into ' + @tabla + ' values(' + coalesce(cast(@i_cod_emp as varchar(255)), 'null') + ', ' + coalesce(cast(@s_tip_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cta_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_anl_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cod_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_cen_val as varchar(255)), 'null') + ', ' + coalesce(cast(@s_nro_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_tip_opr as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@i_cta_anl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_cod_anl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(' ' as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(0 as varchar(255)), 'null') + ', ' + coalesce(cast(0 as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_dal_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@i_cod_ope as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_cen_ope as varchar(255)), 'null') + ', ' + coalesce(cast(@i_nro_ope as varchar(255)), 'null') + ')'
607 execute(@return_with_resume)
608 end --if
609 declare foreach_gencapl4_12 cursor local static for
610 select
611 x,
612 tip_opr,
613 cta_anl,
614 anl_ope,
615 cod_ope,
616 cen_ope,
617 nro_ope,
618 vto_ope,
619 dal_ope
620 from ##tmp2_gencapl4
621 where x > @s_v
622 order by x
623 open foreach_gencapl4_12
624 fetch next from foreach_gencapl4_12 into @s_x, @s_tip_val, @s_cta_val, @s_anl_val, @s_cod_val, @s_cen_val, @s_nro_val, @s_vto_val, @s_dal_val
625 while @@fetch_status = 0
626 begin
627 begin
628 set @return_with_resume = 'insert into ' + @tabla + ' values(' + coalesce(cast(@i_cod_emp as varchar(255)), 'null') + ', ' + coalesce(cast(@s_tip_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cta_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_anl_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_cod_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_cen_val as varchar(255)), 'null') + ', ' + coalesce(cast(@s_nro_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_tip_opr as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@i_cta_anl as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_cod_anl as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(' ' as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(0 as varchar(255)), 'null') + ', ' + coalesce(cast(0 as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + '''' + coalesce(cast(@s_vto_val as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@s_dal_val as varchar(255)), 'null') + ', ' + '''' + coalesce(cast(@i_cod_ope as varchar(255)), 'null') + '''' + ', ' + coalesce(cast(@i_cen_ope as varchar(255)), 'null') + ', ' + coalesce(cast(@i_nro_ope as varchar(255)), 'null') + ')'
629 execute(@return_with_resume)
630 end
631 fetch next from foreach_gencapl4_12 into @s_x, @s_tip_val, @s_cta_val, @s_anl_val, @s_cod_val, @s_cen_val, @s_nro_val, @s_vto_val, @s_dal_val
632 end
633 close foreach_gencapl4_12
634 deallocate foreach_gencapl4_12
635 break
636 end --if
637 set @s_suma_apl = @s_suma_apl + @s_dal_apl
638 end --if
639 end
640 end --if
641 end --if
642 drop table ##tmp1_gencapl4
643 drop table ##tmp2_gencapl4
644 drop table ##tmp3_gencapl4
645end