· 8 years ago · May 16, 2018, 03:42 PM
1create procedure arballon.anl_dddcal_2 @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 and
65 round(cast(((a.div_atc + a.div_tot) * a.cot_mda) * c.sgn as float), 2) != 0
66 where 1 = 1
67 and a.cod_emp = cod_emp_9
68 and a.cod_amb = cod_amb_9
69 and a.cta_anl = cod_opr_9
70 and a.est = 'S'
71 and a.fch_ope >= @s_per_dde
72 and a.fch_ope <= @s_per_hta
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 ),
85 ven_min_mmm_9 = (
86 select
87 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"
88 from anlcab a
89 inner join anlcod b on
90 b.cod_emp = a.cod_emp and
91 b.cod_amb = a.cod_amb and
92 b.cod_anl = a.cod_anl and
93 b.flg_anl = 'C' and
94 b.tip_opr = tip_opr_9
95 inner join logope c on
96 c.cod_ope = a.cod_ope and
97 round(cast(((a.div_atc + a.div_tot) * a.cot_mda) * c.sgn as float), 2) != 0
98 where 1 = 1
99 and a.cod_emp = cod_emp_9
100 and a.cod_amb = cod_amb_9
101 and a.cta_anl = cod_opr_9
102 and a.est = 'S'
103 and a.fch_ope >= @s_per_dde
104 and a.fch_ope <= @s_per_hta
105 and not exists (
106 select
107 z.cod_emp
108 from genapl z
109 where 1 = 1
110 and z.cod_emp = a.cod_emp
111 and z.cod_ref = a.cod_ope
112 and z.cen_ref = a.cen_ope
113 and z.nro_ref = a.nro_ope
114 and z.anl_ant = a.cod_anl
115 )
116 ),
117 ven_cur_9 = (
118 select
119 sum(round(cast(((a.div_atc + a.div_tot) * a.cot_mda) * c.sgn as float), 2))
120 from anlcab a
121 inner join anlcod b on
122 b.cod_emp = a.cod_emp and
123 b.cod_amb = a.cod_amb and
124 b.cod_anl = a.cod_anl and
125 b.flg_anl = 'C' and
126 b.tip_opr = tip_opr_9
127 inner join logope c on
128 c.cod_ope = a.cod_ope and
129 round(cast(((a.div_atc + a.div_tot) * a.cot_mda) * c.sgn as float), 2) != 0
130 where 1 = 1
131 and a.cod_emp = cod_emp_9
132 and a.cod_amb = cod_amb_9
133 and a.cta_anl = cod_opr_9
134 and a.est = 'S'
135 and (year(cast(a.fch_ope as datetime)) * 100 + month(cast(a.fch_ope as datetime))) = @s_per_cur
136 and not exists (
137 select
138 z.cod_emp
139 from genapl z
140 where z.cod_emp = a.cod_emp
141 and z.cod_ref = a.cod_ope
142 and z.cen_ref = a.cen_ope
143 and z.nro_ref = a.nro_ope
144 and z.anl_ant = a.cod_anl
145 )
146 )
147 update ##anlddd_9 set
148 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
149 create table ##anlddd_10 (
150 cod_emp_10 smallint,
151 cod_amb_10 smallint,
152 tip_opr_10 smallint,
153 cod_opr_10 char(16),
154 cod_cls_10 char(6),
155 cod_ges_10 char(6),
156 cod_anl_10 smallint,
157 pen_ope_10 decimal(18,2),
158 ven_nto_10 decimal(18,2),
159 ven_cur_10 decimal(18,2),
160 ven_mmm_10 smallint,
161 ven_pro_10 decimal(18,2),
162 ddd_10 decimal(18,2)
163 )
164 if @i_det_anl = 1
165 begin
166 insert into ##anlddd_10
167 select
168 a.cod_emp_9,
169 a.cod_amb_9,
170 a.tip_opr_9,
171 a.cod_opr_9,
172 a.cod_cls_9,
173 a.cod_ges_9,
174 b.cod_anl,
175 0,
176 a.ven_nto_9,
177 a.ven_cur_9,
178 a.ven_mmm_9,
179 0,
180 0
181 from ##anlddd_9 a
182 inner join ##anlddd_1 b on
183 b.cod_emp = a.cod_emp_9 and
184 b.cod_amb = a.cod_amb_9 and
185 b.tip_opr = a.tip_opr_9
186 update ##anlddd_10 set
187 pen_ope_10 = (
188 select
189 sum(a.dal_ope)
190 from anlsdo a
191 where a.tip_mov in ('0', 'S')
192 and a.per_sdo <= @s_per_hta
193 and a.cod_anl = cod_anl_10
194 and a.cta_anl = cod_opr_10
195 and a.cod_emp = cod_emp_10
196 and a.cod_amb = cod_amb_10
197 and a.tip_opr = tip_opr_10
198 )
199 end --if
200 else
201 begin
202 insert into ##anlddd_10
203 select
204 a.cod_emp_9,
205 a.cod_amb_9,
206 a.tip_opr_9,
207 a.cod_opr_9,
208 a.cod_cls_9,
209 a.cod_ges_9,
210 0,
211 0,
212 a.ven_nto_9,
213 a.ven_cur_9,
214 a.ven_mmm_9,
215 0,
216 0
217 from ##anlddd_9 a
218 update ##anlddd_10 set
219 pen_ope_10 = (
220 select
221 sum(a.dal_ope)
222 from anlsdo a
223 where a.tip_mov in ('0', 'S')
224 and a.per_sdo <= @s_per_hta
225 and a.cod_anl in (
226 select
227 cod_anl
228 from ##anlddd_1 b
229 where b.cod_emp = a.cod_emp
230 and b.cod_amb = a.cod_amb
231 )
232 and a.cta_anl = cod_opr_10
233 and a.cod_emp = cod_emp_10
234 and a.cod_amb = cod_amb_10
235 and a.tip_opr = tip_opr_10
236 )
237 end --if
238 update ##anlddd_10 set
239 pen_ope_10 = 0
240 where pen_ope_10 is null
241 delete from ##anlddd_10
242 where pen_ope_10 = 0
243 if @i_mod = 1
244 begin
245 select *
246 into ##anlddd_11
247 from ##anlddd_10
248 where ven_nto_10 = 0
249 or ven_nto_10 is null
250 delete from ##anlddd_10
251 where ven_nto_10 = 0
252 or ven_nto_10 is null
253 end --if
254 else
255 begin
256 update ##anlddd_10 set
257 ddd_10 = null
258 where ven_nto_10 = 0
259 or ven_nto_10 is null
260 end --if
261 update ##anlddd_10 set
262 ven_pro_10 = cast(ven_nto_10 as float) / cast(ven_mmm_10 as float)
263 where ven_mmm_10 <> 0
264 update ##anlddd_10 set
265 ddd_10 = cast(pen_ope_10 as float) / cast(ven_pro_10 as float) * 30
266 where ven_pro_10 <> 0
267end
268GO