· 4 years ago · May 14, 2021, 01:04 PM
1
2-- ATENÇÃO: ESSA PROCEDURE É CHAMADA POR OUTRA NO COCKPIT, ANTES DE ALTERÁ-LA VEJA A UP_COCKPIT_HR_DISPONIVEL
3
4create procedure up_fechamento_cartao_producao
5 @dtini datetime,
6 @dtfim datetime,
7 @cl_tp char(1),
8 @emp char(2),
9 @cl_setor char (1),
10 @st2 char(1) = 'N', -- Define se será chamado ou não da procedure do st2 - up_calcula_st2
11 @armazena_tmp char(1) = 'N' -- define se o conteudo da @armazena_tmp será lido pela rotina que chamou a procedure...
12as
13
14-- ATENÇÃO: ESSA PROCEDURE É CHAMADA POR OUTRA NO COCKPIT, ANTES DE ALTERÁ-LA VEJA A UP_COCKPIT_HR_DISPONIVEL
15
16declare @num_dias int
17declare @cont int
18declare @dia datetime
19declare @pm_fabricante varchar(255)
20
21select @pm_fabricante = ltrim(rtrim(pm_valor)) from ger_pm where pm_id = 'PM_FABRICANTE'
22
23-- Considera todos os Tipos de O.S. caso a temporária não exista
24if object_id('tempdb..#tmp_tpos') is null
25 select tpos.tpos_cd
26 into #tmp_tpos
27 from tab_tpos tpos
28
29 -- Considera o periodo que o produtivo esta associado em box
30select
31 box_cd = sof_box.box_cd,
32 fun_box_cd = sof_box.fun_cd,
33 box_ativo = case when sof_box_fun.fun_cd is null then 0 else 1 end,
34 fun_cd = sof_box_fun.fun_cd,
35 dt_vigencia = convert(datetime,convert(varchar,sof_box_fun_periodo.dt_vigencia,23)),
36 dt_vigencia_fim = isnull(( select convert(datetime,convert(varchar,min(fun_periodo.dt_vigencia),23))
37 from sof_box_fun_periodo fun_periodo
38 left join sof_box_fun box_fun on ( fun_periodo.periodo_id = box_fun.periodo_id )
39 where box_fun.box_cd = sof_box_fun.box_cd
40 and fun_periodo.periodo_id > sof_box_fun.periodo_id ),@dtfim)
41into #periodo_box
42from sof_box
43 inner join sof_box_fun on ( sof_box.box_cd = sof_box_fun.box_cd )
44 inner join sof_box_fun_periodo on ( sof_box_fun_periodo.periodo_id = sof_box_fun.periodo_id )
45 left join #tmp_fun on ( sof_box_fun.fun_cd = #tmp_fun.fun_cd )
46
47-- Guardar as marcacoes para serem usadas posteriormente
48
49select sof_osm.fun_mec,
50 sof_osm.os_nr,
51 sof_oss.tpos_cd,
52 sof_osm.osm_dtini,
53 sof_osm.osm_hrini,
54 sof_osm.osm_dtfim,
55 sof_osm.osm_hrfim,
56 sof_oss.tm_cd,
57 sof_tm.tm_ds,
58 sof_tm.tm_revisao,
59 sof_oss.oss_situac,
60 sof_oss.oss_cobra,
61 grupo = case tab_tpos.tpos_grupo
62 when 'GAR' then 'GAR'
63 when 'DEP' then 'DEP'
64 when 'REC' then 'REC'
65 else 'CLI'
66 end,
67 tab_set.set_gr,
68 tab_tpos.tpos_containt,
69 -- inicio alteração, foi alterado este campo, pois as concessionarias pediram para considerar o rateio
70 previsto = IsNull(( Select sof_osse.osse_hrprev/3600.00
71 From sof_osse
72 Where sof_osm.os_nr = sof_osse.os_nr and
73 sof_osm.tm_cd = sof_osse.tm_cd and
74 sof_osm.fun_mec = sof_osse.fun_cd),0), --fim alteração
75 aplicado = round((sof_osm.osm_hrfim - sof_osm.osm_hrini)/3600.00, 2),
76 acumulado = convert(float,0),
77 servico = convert(float,0),
78 vendido = convert(float,0),
79 data_enc = convert(datetime,null),
80 conta_extra = 'S',
81 data_ultmarc = (select max(osm.osm_dtfim)
82 from sof_osm osm
83 where osm.os_nr = sof_osm.os_nr and
84 osm.tm_cd = sof_osm.tm_cd and
85 osm.fun_mec = sof_osm.fun_mec)
86 into #tmp
87 from sof_osm
88 INNER JOIN sof_oss ON ( sof_osm.os_nr = sof_oss.os_nr
89 AND sof_osm.tm_cd = sof_oss.tm_cd)
90 INNER JOIN tab_tpos ON ( tab_tpos.tpos_cd = sof_oss.tpos_cd )
91 INNER JOIN sof_tm ON ( sof_tm.tm_cd = sof_oss.tm_cd )
92 INNER JOIN tab_set ON ( tab_set.set_cd = sof_tm.set_cd )
93 INNER JOIN sof_ostp tp ON ( sof_oss.os_nr = tp.os_nr )
94 INNER JOIN #tmp_tpos ON ( tp.tpos_cd = #tmp_tpos.tpos_cd )
95
96 where
97 sof_osm.osm_dtini between @dtini and @dtfim
98 --exists (select fun_cd from #tmp_fun where #tmp_fun.fun_cd = sof_osm.fun_mec) and
99 and sof_osm.osm_dtfim is not null
100 group by sof_osm.fun_mec, sof_osm.os_nr, sof_oss.tpos_cd, sof_osm.osm_dtini, sof_osm.osm_hrini, sof_osm.osm_dtfim, sof_osm.osm_hrfim, sof_oss.tm_cd,
101 sof_tm.tm_ds, sof_tm.tm_revisao, sof_oss.oss_situac, sof_oss.oss_cobra, tab_tpos.tpos_grupo, tab_set.set_gr, tab_tpos.tpos_containt, sof_osm.tm_cd
102
103-- inicio alteração Maritania/Regis 04/08/2003
104-- Alterado : Para considerar o tempo vendido somente na última marcação de tempo e depois do servico encerrado,
105-- com base no rateio da sof_osse
106
107/* Informar o tempo acumulado e se tem alguma marcacao depois da data final
108update #tmp set
109 acumulado=isnull((select round(sum(sof_osm.osm_hrfim - sof_osm.osm_hrini)/3600.00, 2 )
110 from sof_osm
111 where sof_osm.os_nr = #tmp.os_nr and
112 sof_osm.tm_cd = #tmp.tm_cd and
113 ((sof_osm.osm_dtfim < #tmp.osm_dtfim ) or
114 (sof_osm.osm_dtfim = #tmp.osm_dtfim and
115 sof_osm.osm_hrfim <=#tmp.osm_hrfim )) ),0),
116*/
117update #tmp
118set data_enc = (select max(osm.osm_dtfim)
119 from sof_osm osm
120 where osm.os_nr = #tmp.os_nr
121 and osm.tm_cd = #tmp.tm_cd
122 and #tmp.oss_situac = 'E')
123
124--inicio alteração
125--update #tmp set
126-- vendido = round((previsto * (aplicado/acumulado)),2)
127-- comentado por Maritania - para que o fechamento de cartão de produção fique igual ao cartão de tarefa
128-- Colocar o tempo vendido, de acordo com o tempo aplicado
129-- discriminar pelas férias
130/*
131update #tmp set
132 vendido = case oss_cobra
133 when 'S'
134 then case when ((oss_situac = '') or (servico > 0))
135 then case when (acumulado <= previsto)
136 then aplicado
137 else case when ((acumulado - aplicado) >= previsto)
138 then 0
139 else (previsto * (aplicado/acumulado))
140 end
141 end
142 else case when ((acumulado - aplicado) >= previsto)
143 then 0
144 else (previsto * (aplicado/acumulado))
145 end
146 end
147 else 0
148 end
149--fim alteração
150*/
151
152INSERT INTO #tmp
153SELECT
154 osse.fun_cd,
155 #tmp.os_nr,
156 #tmp.tpos_cd,
157 #tmp.data_enc,
158 0,
159 #tmp.data_enc,
160 0,
161 #tmp.tm_cd,
162 #tmp.tm_ds,
163 #tmp.tm_revisao,
164 #tmp.oss_situac,
165 #tmp.oss_cobra,
166 #tmp.grupo,
167 #tmp.set_gr,
168 #tmp.tpos_containt,
169 previsto = convert(float,0),
170 aplicado = convert(float,0),
171 acumulado = convert(float,0),
172 servico = convert(float,0),
173 vendido = convert(float,0),
174 #tmp.data_enc,
175 conta_extra = 'N',
176 data_ultmarc = null
177 FROM #tmp
178 INNER JOIN sof_osse osse ON ( #tmp.os_nr = osse.os_nr
179 and #tmp.tm_cd = osse.tm_cd
180 and #tmp.fun_mec = osse.fun_cd)
181 INNER JOIN (select os_nr,tm_cd,fun_mec
182 from sof_osm osm
183 group by os_nr,tm_cd,fun_mec ) AS osm ON ( osm.os_nr = #tmp.os_nr
184 and osm.tm_cd = #tmp.tm_cd
185 and osm.fun_mec = #tmp.fun_mec
186 and #tmp.data_ultmarc < #tmp.data_enc
187 and #tmp.data_enc >= @dtini
188 and #tmp.data_enc <= @dtfim )
189 WHERE #tmp.oss_situac = 'E'
190
191GROUP BY
192 osse.fun_cd,
193 #tmp.os_nr,
194 #tmp.tpos_cd,
195 #tmp.data_enc,
196 #tmp.data_enc,
197 #tmp.tm_cd,
198 #tmp.tm_ds,
199 #tmp.tm_revisao,
200 #tmp.oss_situac,
201 #tmp.oss_cobra,
202 #tmp.grupo,
203 #tmp.set_gr,
204 #tmp.tpos_containt,
205 osse.os_nr ,
206 osse.tm_cd,
207 osse.fun_cd,
208 #tmp.data_enc
209
210
211
212update #tmp
213 set previsto = IsNull(( Select isnull(sof_osse.osse_hrprev,0)/3600.00
214 From sof_osse
215 Where #tmp.os_nr = sof_osse.os_nr
216 and #tmp.tm_cd = sof_osse.tm_cd
217 and #tmp.fun_mec = sof_osse.fun_cd ),0)
218
219update #tmp
220 set servico = IsNull((select Count(*)
221 from sof_osm
222 where sof_osm.os_nr = #tmp.os_nr
223 and sof_osm.tm_cd = #tmp.tm_cd
224 and sof_osm.fun_mec = #tmp.fun_mec
225 and ((sof_osm.osm_dtfim > #tmp.osm_dtfim or
226 (sof_osm.osm_dtfim = #tmp.osm_dtfim and
227 sof_osm.osm_hrfim > #tmp.osm_hrfim)))),0)
228
229
230update #tmp
231 set vendido = previsto
232 where oss_cobra = 'S'
233 and oss_situac = 'E'
234 and servico = 0
235 and osm_dtfim = data_enc
236
237if @armazena_tmp = 'S'
238begin
239 insert into #ret_up_fechamento_cartao_producao_tmp
240 select * from #tmp
241end
242
243-- fim alteração Maritania/Regis 04/08/2003
244-- Alterado : Para considerar o tempo vendido somente na última marcação de tempo e depois do servico encerrado,
245-- com base no rateio da sof_osse
246
247
248
249/* Comentado de acordo com chamado
250where grupo <> 'DEP'
251
252update #tmp set
253 vendido = aplicado
254where grupo = 'DEP'
255*/
256
257/****************************************************************************************************
258Verificar se com hor rios alternativos deve pegar os hor rios na tabela tab_eqphr
259 - Quando existir horarios alternativos, para um determidado dia, este deve ser o horario considerado
260 - Horario alternativo esta em tab_eqphr
261 - Horario normal esta em tab_eqp
262****************************************************************************************************/
263
264
265-- Guardar o numero de horas das jornadas das equipes
266--
267-- O update dos hor rios alternativos esta mais em baixo!!!
268
269select tab_fun.eqp_cd,
270 horas_semana=((eqp_chsem_fim1 - eqp_chsem_inicio1) + (eqp_chsem_fim2 - eqp_chsem_inicio2)),
271 horas_sabado=((eqp_chsab_fim1 - eqp_chsab_inicio1) + (eqp_chsab_fim2 - eqp_chsab_inicio2)),
272 num_mec =count(*)
273 into #horas
274 from #tmp_fun, tab_fun, tab_eqp
275 where tab_fun.fun_cd = #tmp_fun.fun_cd and
276 tab_eqp.eqp_cd = tab_fun.eqp_cd
277 group by tab_fun.eqp_cd,
278 eqp_chsem_inicio1,
279 eqp_chsem_fim1,
280 eqp_chsem_inicio2,
281 eqp_chsem_fim2,
282 eqp_chsab_inicio1,
283 eqp_chsab_fim1,
284 eqp_chsab_inicio2,
285 eqp_chsab_fim2
286
287--
288-- Criar tabela com os dias (exceto apenas domingos e feriados nao trabalhados)
289--
290
291select @num_dias = datediff( day, @dtini, @dtfim) + 1
292
293create table #tmp_dias (dia datetime null )
294
295select @dia = @dtini
296select @cont = 1
297while @cont <= @num_dias begin
298
299 insert into #tmp_dias (dia) values (@dia)
300 select @dia = dateadd( day, 1, @dia )
301 select @cont = @cont + 1
302end
303
304-- excluir os domingos nao trabalhados
305delete from #tmp_dias
306 where datepart( dw, dia ) = 1 and
307 not exists (select osm_dtini from #tmp where #tmp.osm_dtini = #tmp_dias.dia)
308
309-- excluir os feriados nao trabalhados
310delete #tmp_dias
311 from tab_fe
312 where tab_fe.fe_dt = #tmp_dias.dia and
313 not exists (select osm_dtini from #tmp where #tmp.osm_dtini = #tmp_dias.dia)
314delete #tmp_dias
315 from tab_fe
316 where tab_fe.fe_dt = convert(datetime, convert(varchar(2), datepart(month,#tmp_dias.dia)) + '/' +
317 case when convert(varchar(2), datepart(month,#tmp_dias.dia)) = '2' and convert(varchar(2), datepart(day,#tmp_dias.dia)) = '29' then '28'
318 else convert(varchar(2), datepart(day,#tmp_dias.dia)) end
319 +'/1900')
320
321 and not exists (select osm_dtini from #tmp where #tmp.osm_dtini = #tmp_dias.dia)
322
323--
324-- Criar tabela do resultado
325--
326/*****Inicio Alteração Chamado 76499 - Jamile Lopes em 16.07.08***/
327select data=#tmp_dias.dia,
328 fun_mec=#tmp_fun.fun_cd,
329 tab_fun.eqp_cd,
330 jornada=convert(float,0),
331 normais=convert(float,0),
332 extras =convert(float,0),
333 teoricas=convert(float,0),
334 disponiveis=convert(float,0),
335 aplicadas=convert(float,0),
336 a360 =convert(float,0),
337 v360 =convert(float,0),
338 a361 =convert(float,0),
339 v361 =convert(float,0),
340 a362 =convert(float,0),
341 v362 =convert(float,0),
342 a363 =convert(float,0),
343 v363 =convert(float,0),
344 a362_r =convert(float,0),
345 v362_r =convert(float,0),
346 a120 =convert(float,0),
347 v120 =convert(float,0),
348 a601_01 =convert(float,0),
349 v601_01 =convert(float,0),
350 a601_02 =convert(float,0),
351 v601_02 =convert(float,0),
352 a624 =convert(float,0),
353 v624 =convert(float,0),
354 a625 =convert(float,0),
355 v625 =convert(float,0),
356 faltas =convert(float,0),
357 abonadas=convert(float,0),
358 treino =convert(float,0),
359 retorno =convert(float,0),
360 inativo =convert(float,0),
361 outros =convert(float,0),
362 calc_alter = 'False',
363 vendidas=convert(float,0),
364 fabr = (select ltrim(rtrim(pm_valor)) from ger_pm where pm_id = 'PM_fabricante') -- Adicionada linha Chamado 76499
365 into #final
366 from #tmp_fun
367 LEFT OUTER JOIN #tmp ON ( #tmp_fun.fun_cd = #tmp.fun_mec )
368 INNER JOIN tab_fun ON ( #tmp_fun.fun_cd = tab_fun.fun_cd )
369 RIGHT OUTER JOIN #tmp_dias ON ( EXISTS (SELECT 1 FROM #tmp WHERE #tmp.osm_dtini = #tmp_dias.dia ))
370
371 and ( not exists ( select 1 from #periodo_box
372 where #tmp_dias.dia between dt_vigencia and dt_vigencia_fim
373 and isnull(#periodo_box.fun_cd,#periodo_box.fun_box_cd) = tab_fun.fun_cd ) -- nao mostrar produtivos em box
374
375 or exists ( select 1 from #periodo_box
376 where #tmp_dias.dia between dt_vigencia and dt_vigencia_fim
377 and #periodo_box.fun_cd = tab_fun.fun_cd
378 and exists ( select 1 from #periodo_box periodo_box2
379 where #tmp_dias.dia between dt_vigencia and dt_vigencia_fim
380 and periodo_box2.box_ativo = 0
381 and #periodo_box.fun_box_cd = periodo_box2.fun_box_cd )) -- ou mostrar com o box inativo
382
383 or #tmp_fun.fun_cd = #tmp.fun_mec and #tmp.osm_dtini = #tmp_dias.dia ) -- ou tenha marcacao no mesmo dia do box
384
385group by #tmp_dias.dia, #tmp_fun.fun_cd, tab_fun.eqp_cd
386/*****Fim Alteração Chamado 76499 - Jamile Lopes em 16.07.08***/
387--
388-- Horas dos servicos
389--
390
391-- Externas
392
393-- Mecanica/Clientes
394update #final set
395 a360 = isnull((select sum(#tmp.aplicado)
396 from #tmp
397 where #tmp.osm_dtini = #final.data and
398 #tmp.fun_mec = #final.fun_mec and
399 #tmp.grupo = 'CLI' and
400 #tmp.set_gr = 'M'),0),
401 v360 = isnull((select sum(#tmp.vendido)
402 from #tmp
403 where #tmp.osm_dtini = #final.data and
404 #tmp.fun_mec = #final.fun_mec and
405 #tmp.grupo = 'CLI' and
406 #tmp.set_gr = 'M'),0)
407
408-- Funilaria e Pintura/Clientes
409update #final set
410 a361 = isnull((select sum(#tmp.aplicado)
411 from #tmp
412 where #tmp.osm_dtini = #final.data and
413 #tmp.fun_mec = #final.fun_mec and
414 #tmp.grupo = 'CLI' and
415 #tmp.set_gr <> 'M'),0),
416 v361 = isnull((select sum(#tmp.vendido)
417 from #tmp
418 where #tmp.osm_dtini = #final.data and
419 #tmp.fun_mec = #final.fun_mec and
420 #tmp.grupo = 'CLI' and
421 #tmp.set_gr <> 'M'),0)
422
423
424-- Mecanica/Garantia
425update #final set
426 a362 = isnull((select sum(#tmp.aplicado)
427 from #tmp
428 where #tmp.osm_dtini = #final.data and
429 #tmp.fun_mec = #final.fun_mec and
430 #tmp.grupo = 'GAR' and
431 #tmp.set_gr = 'M' and
432 #tmp.tm_revisao= 'N'),0),
433 v362 = isnull((select sum(#tmp.vendido)
434 from #tmp
435 where #tmp.osm_dtini = #final.data and
436 #tmp.fun_mec = #final.fun_mec and
437 #tmp.grupo = 'GAR' and
438 #tmp.set_gr = 'M' and
439 #tmp.tm_revisao= 'N'),0)
440
441-- Funilaria e Pintura/Garantia
442update #final set
443 a363 = isnull((select sum(#tmp.aplicado)
444 from #tmp
445 where #tmp.osm_dtini = #final.data and
446 #tmp.fun_mec = #final.fun_mec and
447 #tmp.grupo = 'GAR' and
448 #tmp.set_gr <> 'M' and
449 #tmp.tm_revisao= 'N'),0),
450 v363 = isnull((select sum(#tmp.vendido)
451 from #tmp
452 where #tmp.osm_dtini = #final.data and
453 #tmp.fun_mec = #final.fun_mec and
454 #tmp.grupo = 'GAR' and
455 #tmp.set_gr <> 'M' and
456 #tmp.tm_revisao= 'N'),0)
457
458-- Revisoes Gratuitas cobradas da FIAT
459update #final set
460 a362_r = isnull((select sum(#tmp.aplicado)
461 from #tmp
462 where #tmp.osm_dtini = #final.data and
463 #tmp.fun_mec = #final.fun_mec and
464 #tmp.grupo = 'GAR' and
465 #tmp.tm_revisao= 'S'),0),
466 v362_r = isnull((select sum(#tmp.vendido)
467 from #tmp
468 where #tmp.osm_dtini = #final.data and
469 #tmp.fun_mec = #final.fun_mec and
470 #tmp.grupo = 'GAR' and
471 #tmp.tm_revisao= 'S'),0)
472--
473-- Internas
474--
475
476-- Servicos nos Veiculos Usados existentes no estoque da concessionaria
477--if @pm_fabricante <> 'IVECO'
478--begin
479 update #final set
480 a120 = isnull((select sum(#tmp.aplicado)
481 from #tmp
482 where #tmp.osm_dtini = #final.data and
483 #tmp.fun_mec = #final.fun_mec and
484 #tmp.grupo = 'DEP' and
485 #tmp.tpos_containt like '120%'),0),
486 v120 = isnull((select sum(#tmp.vendido)
487 from #tmp
488 where #tmp.osm_dtini = #final.data and
489 #tmp.fun_mec = #final.fun_mec and
490 #tmp.grupo = 'DEP' and
491 #tmp.tpos_containt like '120%'),0)
492
493 -- Revisoes de Ve¡culos que j receberam comissÆo assistencial
494 update #final set
495 a601_01 = isnull((select sum(#tmp.aplicado)
496 from #tmp
497 where #tmp.osm_dtini = #final.data and
498 #tmp.fun_mec = #final.fun_mec and
499 #tmp.grupo = 'DEP' and
500 #tmp.tpos_containt like '601.01%'),0),
501 v601_01 = isnull((select sum(#tmp.vendido)
502 from #tmp
503 where #tmp.osm_dtini = #final.data and
504 #tmp.fun_mec = #final.fun_mec and
505 #tmp.grupo = 'DEP' and
506 #tmp.tpos_containt like '601.01%'),0)
507
508
509 -- Revisoes Veiculos Usados vendidos pela concession ria
510 update #final set
511 a601_02 = isnull((select sum(#tmp.aplicado)
512 from #tmp
513 where #tmp.osm_dtini = #final.data and
514 #tmp.fun_mec = #final.fun_mec and
515 #tmp.grupo = 'DEP' and
516 #tmp.tpos_containt like '601.02%'),0),
517 v601_02 = isnull((select sum(#tmp.vendido)
518 from #tmp
519 where #tmp.osm_dtini = #final.data and
520 #tmp.fun_mec = #final.fun_mec and
521 #tmp.grupo = 'DEP' and
522 #tmp.tpos_containt like '601.02%'),0)
523
524
525 -- Garantia e Cortesia
526 update #final set
527 a624 = isnull((select sum(#tmp.aplicado)
528 from #tmp
529 where #tmp.osm_dtini = #final.data and
530 #tmp.fun_mec = #final.fun_mec and
531 #tmp.grupo = 'DEP' and
532 #tmp.tpos_containt like '624%'),0),
533 v624 = isnull((select sum(#tmp.vendido)
534 from #tmp
535 where #tmp.osm_dtini = #final.data and
536 #tmp.fun_mec = #final.fun_mec and
537 #tmp.grupo = 'DEP' and
538 #tmp.tpos_containt like '624%'),0)
539
540 -- Manutencao/Conservacao
541 update #final set
542 a625 = isnull((select sum(#tmp.aplicado)
543 from #tmp
544 where #tmp.osm_dtini = #final.data and
545 #tmp.fun_mec = #final.fun_mec and
546 #tmp.grupo = 'DEP' and
547 #tmp.tpos_containt like '625%'),0),
548 v625 = isnull((select sum(#tmp.vendido)
549 from #tmp
550 where #tmp.osm_dtini = #final.data and
551 #tmp.fun_mec = #final.fun_mec and
552 #tmp.grupo = 'DEP' and
553 #tmp.tpos_containt like '625%'),0)
554--end
555--else
556--begin
557-- update #final set
558-- a120 = isnull((select sum(#tmp.aplicado)
559-- from #tmp
560-- where #tmp.osm_dtini = #final.data and
561-- #tmp.fun_mec = #final.fun_mec and
562-- #tmp.grupo = 'DEP' and
563-- #tmp.tpos_containt like 'A94%'),0),
564-- v120 = isnull((select sum(#tmp.vendido)
565-- from #tmp
566-- where #tmp.osm_dtini = #final.data and
567-- #tmp.fun_mec = #final.fun_mec and
568-- #tmp.grupo = 'DEP' and
569-- #tmp.tpos_containt like 'A94%'),0)
570
571-- -- Revisoes de Ve¡culos que j receberam comissÆo assistencial
572-- update #final set
573-- a601_01 = isnull((select sum(#tmp.aplicado)
574-- from #tmp
575-- where #tmp.osm_dtini = #final.data and
576-- #tmp.fun_mec = #final.fun_mec and
577-- #tmp.grupo = 'DEP' and
578-- #tmp.tpos_containt like 'B35%'),0),
579-- v601_01 = isnull((select sum(#tmp.vendido)
580-- from #tmp
581-- where #tmp.osm_dtini = #final.data and
582-- #tmp.fun_mec = #final.fun_mec and
583-- #tmp.grupo = 'DEP' and
584-- #tmp.tpos_containt like 'B35%'),0)
585
586
587-- -- Revisoes Veiculos Usados vendidos pela concession ria
588-- update #final set
589-- a601_02 = isnull((select sum(#tmp.aplicado)
590-- from #tmp
591-- where #tmp.osm_dtini = #final.data and
592-- #tmp.fun_mec = #final.fun_mec and
593-- #tmp.grupo = 'DEP' and
594-- #tmp.tpos_containt like 'F37%'),0),
595-- v601_02 = isnull((select sum(#tmp.vendido)
596-- from #tmp
597-- where #tmp.osm_dtini = #final.data and
598-- #tmp.fun_mec = #final.fun_mec and
599-- #tmp.grupo = 'DEP' and
600-- #tmp.tpos_containt like 'F37%'),0)
601
602
603-- -- Garantia e Cortesia
604-- update #final set
605-- a624 = isnull((select sum(#tmp.aplicado)
606-- from #tmp
607-- where #tmp.osm_dtini = #final.data and
608-- #tmp.fun_mec = #final.fun_mec and
609-- #tmp.grupo = 'DEP' and
610-- #tmp.tpos_containt like 'E41%'),0),
611-- v624 = isnull((select sum(#tmp.vendido)
612-- from #tmp
613-- where #tmp.osm_dtini = #final.data and
614-- #tmp.fun_mec = #final.fun_mec and
615-- #tmp.grupo = 'DEP' and
616-- #tmp.tpos_containt like 'E41%'),0)
617
618-- -- Manutencao/Conservacao
619-- update #final set
620-- a625 = isnull((select sum(#tmp.aplicado)
621-- from #tmp
622-- where #tmp.osm_dtini = #final.data and
623-- #tmp.fun_mec = #final.fun_mec and
624-- #tmp.grupo = 'DEP' and
625-- #tmp.tpos_containt like 'A89%'),0),
626-- v625 = isnull((select sum(#tmp.vendido)
627-- from #tmp
628-- where #tmp.osm_dtini = #final.data and
629-- #tmp.fun_mec = #final.fun_mec and
630-- #tmp.grupo = 'DEP' and
631-- #tmp.tpos_containt like 'A89%'),0)
632--end
633
634
635-- Retorno
636update #final set
637 retorno = isnull((select sum(#tmp.aplicado)
638 from #tmp
639 where #tmp.osm_dtini = #final.data and
640 #tmp.fun_mec = #final.fun_mec and
641 #tmp.grupo = 'REC' ),0)
642
643--
644-- Horas Aplicadas
645update #final set
646 aplicadas=(a360 + a361 + a362 + a363 + a362_r + a120 + a601_01 + a601_02 + a624 + a625)
647
648update #final set
649vendidas=(v360 + v361 + v362 + v363 + v362_r + v120 + v601_01 + v601_02 + v624 + v625)
650
651
652--
653-- Guardar as horas normais da equipe de cada mecanico se o dia nao for feriado.
654-- Obs.: os dias da semana estao separados dos sabados
655
656update #final set
657 jornada = round(#horas.horas_semana/3600.00,2) --soma a qtd de horas da semana.
658 from #horas
659where #horas.eqp_cd = #final.eqp_cd and
660 not exists (select fe_dt from tab_fe where fe_dt=#final.data) and
661 datepart(dw,#final.data) between 2 and 6
662
663update #final set
664 jornada = round(#horas.horas_sabado/3600.00,2) --soma a qtd de horas de sabado.
665 from #horas
666where #horas.eqp_cd = #final.eqp_cd and
667 not exists (select fe_dt from tab_fe where fe_dt=#final.data) and
668 datepart(dw,#final.data) = 7
669
670
671--Esta atualizando #final, para os horarios Alternativos.
672--
673--na data especificada
674--select * from tab_eqphr tabela que pega as marcacoes de horario alternativo
675
676update #final set
677 jornada = round( ( (eqphr_fim1 - eqphr_inicio1) + (eqphr_fim2 - eqphr_inicio2) )/3600.00,2)--soma a qtd de horas do horario alternativo, p.e.: (12-8 + 18-14)
678 from tab_eqphr,ger_freq,tab_freq
679where tab_eqphr.eqp_cd = #final.eqp_cd and
680 eqphr_dt = #final.data
681
682/*update #final set
683 jornada = round((#horas.horas_semana/3600.00),2)
684 from tab_eqphr,#horas,ger_freq,tab_freq
685where tab_eqphr.eqp_cd = #final.eqp_cd and
686 eqphr_dt = #final.data and
687 ger_freq.fun_cd = #final.fun_mec and
688 ger_freq.freq_cd= tab_freq.freq_cd and
689 ger_freq.freq_cd = 'FE'*/
690
691--
692-- Faltas
693--
694
695-- Nao abonadas
696-- Faltas Parciais
697-- calcula as faltas
698update #final set
699 faltas = faltas + isnull((select Sum(Round(ger_freq.freq_hr/3600.00,2))
700 from ger_freq, tab_freq
701 where ger_freq.freq_dt= #final.data and
702 ger_freq.fun_cd = #final.fun_mec and
703 ger_freq.freq_cd= tab_freq.freq_cd and
704 tab_freq.freq_gr= 'F' and--nao abonadas.
705 tab_freq.freq_tp= 'P' and
706 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
707-- Faltas Totais (o numero de horas e o numero de horas de trabalho da equipe do mecanico no dia)
708-- soma com as faltas nao abonadas
709update #final set
710 faltas = faltas + isnull((select Sum(Round(#horas.horas_semana/3600.00,2))
711-- faltas = faltas + isnull((select Sum(Round(#horas.horas_semana/3600.00,2))
712 from ger_freq, tab_freq, #horas
713 where ger_freq.freq_dt= #final.data and
714 ger_freq.fun_cd = #final.fun_mec and
715 #final.eqp_cd = #horas.eqp_cd and
716 ger_freq.freq_cd= tab_freq.freq_cd and
717 tab_freq.freq_gr= 'F' and
718 tab_freq.freq_tp= 'T' and
719 datepart(dw, #final.data) between 2 and 6 and
720 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
721update #final set
722-- faltas = faltas + isnull((select Sum(Round(#horas.horas_sabado/3600.00,2))
723 faltas = faltas + isnull((select Sum(Round(#horas.horas_semana/3600.00,2))
724 from ger_freq, tab_freq, #horas
725 where ger_freq.freq_dt= #final.data and
726 ger_freq.fun_cd = #final.fun_mec and
727 #final.eqp_cd = #horas.eqp_cd and
728 ger_freq.freq_cd= tab_freq.freq_cd and
729 tab_freq.freq_gr= 'F' and
730 tab_freq.freq_tp= 'T' and
731 datepart(dw, #final.data) = 7 and
732 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
733
734-- faltas ok
735-- Abonadas
736--
737-- Faltas Parciais
738--falta abonada deve ser verificada pelos horarios alternados ou pelo fixo? esta pegando pelos alternados.
739update #final set
740 abonadas = abonadas + isnull((select sum(round(ger_freq.freq_hr/3600.00,2))
741 from ger_freq, tab_freq
742 where ger_freq.freq_dt= #final.data and
743 ger_freq.fun_cd = #final.fun_mec and
744 ger_freq.freq_cd= tab_freq.freq_cd and
745 tab_freq.freq_gr in ('A','E') and
746 tab_freq.freq_tp= 'P' and
747 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
748-- Faltas Totais (o numero de horas e o numero de horas de trabalho da equipe do mecanico no dia)
749update #final set --verificar aqui, por causa do sábado.
750 abonadas = abonadas + isnull((select Sum(Round(#horas.horas_semana/3600.00,2))
751 from ger_freq, tab_freq, #horas
752 where ger_freq.freq_dt= #final.data and
753 ger_freq.fun_cd = #final.fun_mec and
754 #final.eqp_cd = #horas.eqp_cd and
755 ger_freq.freq_cd= tab_freq.freq_cd and
756 tab_freq.freq_gr in ('A','E') and
757 tab_freq.freq_tp= 'T' and
758 datepart(dw, #final.data) between 2 and 6 and
759 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
760update #final set
761 abonadas = abonadas + isnull((select Sum(Round(#horas.horas_sabado/3600.00,2))
762 from ger_freq, tab_freq, #horas
763 where ger_freq.freq_dt= #final.data and
764 ger_freq.fun_cd = #final.fun_mec and
765 #final.eqp_cd = #horas.eqp_cd and
766 ger_freq.freq_cd= tab_freq.freq_cd and
767 tab_freq.freq_gr in ('A','E') and
768 tab_freq.freq_tp= 'T' and
769 datepart(dw, #final.data) = 7 and
770 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
771
772-- abonadas ok
773-- Horas Normais
774--
775
776update #final set
777 normais = jornada - faltas
778
779
780/*
781select * from #final
782order by eqp_cd, data
783*/
784
785--
786-- Tempo gasto em treinamento
787-- Treinamento em tempo Parcial
788update #final set
789 treino = isnull((select Sum(Round(ger_freq.freq_hr/3600.00,2))
790 from ger_freq, tab_freq
791 where ger_freq.freq_dt= #final.data and
792 ger_freq.fun_cd = #final.fun_mec and
793 ger_freq.freq_cd= tab_freq.freq_cd and
794 tab_freq.freq_gr= 'T' and
795 tab_freq.freq_tp= 'P' and
796 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
797-- Treinamento em tempo integral (o numero de horas e o numero de horas de trabalho da equipe do mecanico no dia)
798update #final set
799 treino = treino + isnull((select Sum(Round(#horas.horas_semana/3600.00,2))
800 from ger_freq, tab_freq, #horas
801 where ger_freq.freq_dt= #final.data and
802 ger_freq.fun_cd = #final.fun_mec and
803 #final.eqp_cd = #horas.eqp_cd and
804 ger_freq.freq_cd= tab_freq.freq_cd and
805 tab_freq.freq_gr= 'T' and
806 tab_freq.freq_tp= 'T' and
807 datepart(dw, #final.data) between 2 and 6 and
808 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
809update #final set
810 treino = treino + isnull((select Sum(Round(#horas.horas_sabado/3600.00,2))
811 from ger_freq, tab_freq, #horas
812 where ger_freq.freq_dt= #final.data and
813 ger_freq.fun_cd = #final.fun_mec and
814 #final.eqp_cd = #horas.eqp_cd and
815 ger_freq.freq_cd= tab_freq.freq_cd and
816 tab_freq.freq_gr= 'T' and
817 tab_freq.freq_tp= 'T' and
818 datepart(dw, #final.data) = 7 and
819 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
820
821-- Outros (Ferias, etc...) que vao diminuir/somar da jornada e, consequentemente, das horas disponiveis
822--
823update #final set
824 outros = isnull((select Sum(Round(ger_freq.freq_hr*tab_freq.freq_oper/3600.00,2)) --tab_freq.freq_opr é negativo
825 from ger_freq, tab_freq
826 where ger_freq.freq_dt= #final.data and
827 ger_freq.fun_cd = #final.fun_mec and
828 ger_freq.freq_cd= tab_freq.freq_cd and
829 tab_freq.freq_gr= 'O' and
830 tab_freq.freq_tp= 'P' and
831 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
832-- Treinamento em tempo integral (o numero de horas e o numero de horas de trabalho da equipe do mecanico no dia)
833update #final set
834 outros = outros + isnull((select Sum(Round(#horas.horas_semana*tab_freq.freq_oper/3600.00,2))
835 from ger_freq, tab_freq, #horas
836 where ger_freq.freq_dt= #final.data and
837 ger_freq.fun_cd = #final.fun_mec and
838 #final.eqp_cd = #horas.eqp_cd and
839 ger_freq.freq_cd= tab_freq.freq_cd and
840 tab_freq.freq_gr= 'O' and
841 tab_freq.freq_tp= 'T' and
842 datepart(dw, #final.data) between 2 and 6 and
843 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
844update #final set
845 outros = outros + isnull((select Sum(Round(#horas.horas_sabado*tab_freq.freq_oper/3600.00,2))
846 from ger_freq, tab_freq, #horas
847 where ger_freq.freq_dt= #final.data and
848 ger_freq.fun_cd = #final.fun_mec and
849 #final.eqp_cd = #horas.eqp_cd and
850 ger_freq.freq_cd= tab_freq.freq_cd and
851 tab_freq.freq_gr= 'O' and
852 tab_freq.freq_tp= 'T' and
853 datepart(dw, #final.data) = 7 and
854 not exists (select fe_dt from tab_fe where fe_dt=#final.data)),0)
855
856--
857-- Horas Extras
858
859/* 8====================12 14======================18
860a) ----
861 b) --------
862 c) -----
863 d) ---------
864 e) ----------
865 f) -------------------------------------
866 g) -------
867 h) -----------
868*/
869
870
871
872
873/***********************************************************************************************
874 Outro Intervalo - Horario Alternativo
875***********************************************************************************************/
876
877-- O tempo trabalhado antes do inicio do expediente
878-- a) e b)
879--dias normais
880update #final set
881 extras = isnull(( select Round((eqphr_inicio1-min(osm_hrini))/3600.00,2)
882 from #tmp, tab_eqphr
883 where #final.eqp_cd = tab_eqphr.eqp_cd and
884 #final.fun_mec = #tmp.fun_mec and
885 #final.data = #tmp.osm_dtini and
886 #final.data = tab_eqphr.eqphr_dt and
887 #tmp.osm_hrini < eqphr_inicio1 and
888 #tmp.conta_extra = 'S' and
889 datepart( dw, #final.data ) between 2 and 6 and
890 eqphr_inicio1 > 0
891 group by eqphr_inicio1 ),0)
892
893
894
895--sabado
896update #final set
897 extras = extras + isnull(( select round((eqphr_inicio1 - min(osm_hrini))/3600.00,2)
898 from #tmp, tab_eqphr
899 where #final.eqp_cd = tab_eqphr.eqp_cd and
900 #final.fun_mec = #tmp.fun_mec and
901 #final.data = #tmp.osm_dtini and
902 #final.data = tab_eqphr.eqphr_dt and
903 #tmp.osm_hrini < eqphr_inicio1 and
904 #tmp.conta_extra = 'S' and
905 datepart( dw, #final.data ) = 7 and
906 eqphr_inicio1 > 0
907 group by eqphr_inicio1 ),0)
908
909
910/***********************************************************************************************
911 Outro Intervalo - Horario Alternativo
912***********************************************************************************************/
913-- O tempo trabalhado entre o fim do primeiro turno e o inicio do segundo turno
914-- c)
915--dias normais
916update #final set
917 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2))
918 from #tmp, tab_eqphr
919 where #final.eqp_cd = tab_eqphr.eqp_cd and
920 #final.fun_mec = #tmp.fun_mec and
921 #final.data = #tmp.osm_dtini and
922 #final.data = tab_eqphr.eqphr_dt and
923 #tmp.osm_hrini >=eqphr_fim1 and
924 #tmp.osm_hrfim <=eqphr_inicio2 and
925 #tmp.conta_extra = 'S' and
926 datepart( dw, #final.data ) between 2 and 6 ) ,0)
927--sabado
928update #final set
929 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2))
930 from #tmp, tab_eqphr
931 where #final.eqp_cd = tab_eqphr.eqp_cd and
932 #final.fun_mec = #tmp.fun_mec and
933 #final.data = #tmp.osm_dtini and
934 #final.data = tab_eqphr.eqphr_dt and
935 #tmp.osm_hrini >=eqphr_fim1 and
936 #tmp.osm_hrfim <=eqphr_inicio2 and
937 eqphr_inicio2 > 0 and eqphr_fim1 > 0 and
938 #tmp.conta_extra = 'S' and
939 datepart( dw, #final.data ) = 7 ) ,0)
940
941
942/***********************************************************************************************
943 Outro Intervalo - Horario Alternativo
944***********************************************************************************************/
945-- d)
946-- dias normais
947update #final set
948 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - tab_eqphr.eqphr_fim1)/3600.00,2))
949 from #tmp, tab_eqphr
950 where #final.eqp_cd = tab_eqphr.eqp_cd and
951 #final.fun_mec = #tmp.fun_mec and
952 #final.data = #tmp.osm_dtini and
953 #final.data = tab_eqphr.eqphr_dt and
954 #tmp.osm_hrini < eqphr_fim1 and
955 #tmp.osm_hrfim > eqphr_fim1 and
956 #tmp.osm_hrfim <=eqphr_inicio2 and
957 #tmp.conta_extra = 'S' and
958 datepart( dw, #final.data ) between 2 and 6 ) ,0)
959
960--dias normais
961update #final set
962 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - tab_eqphr.eqphr_fim1)/3600.00,2))
963 from #tmp, tab_eqphr
964 where #final.eqp_cd = tab_eqphr.eqp_cd and
965 #final.fun_mec = #tmp.fun_mec and
966 #final.data = #tmp.osm_dtini and
967 #final.data = tab_eqphr.eqphr_dt and
968 #tmp.osm_hrini < eqphr_fim1 and
969 #tmp.osm_hrfim > eqphr_fim1 and
970 #tmp.osm_hrfim <=eqphr_inicio2 and
971 eqphr_inicio2 > 0 and eqphr_fim1 > 0 and
972 #tmp.conta_extra = 'S' and
973 datepart( dw, #final.data ) = 7 ) ,0)
974
975
976/***********************************************************************************************
977 Outro Intervalo - Horario Alternativo
978***********************************************************************************************/
979-- e)
980-- dias normais
981update #final set
982 extras = extras + isnull(( select sum(round((tab_eqphr.eqphr_inicio2 - #tmp.osm_hrini)/3600.00,2))
983 from #tmp, tab_eqphr
984 where #final.eqp_cd = tab_eqphr.eqp_cd and
985 #final.fun_mec = #tmp.fun_mec and
986 #final.data = #tmp.osm_dtini and
987 #final.data = tab_eqphr.eqphr_dt and
988 #tmp.osm_hrini > eqphr_fim1 and
989 #tmp.osm_hrini < eqphr_inicio2 and
990 #tmp.osm_hrfim > eqphr_inicio2 and
991 #tmp.conta_extra = 'S' and
992 datepart( dw, #final.data ) between 2 and 6 ) ,0)
993-- sabado
994update #final set
995 extras = extras + isnull(( select sum(round((tab_eqphr.eqphr_inicio2 - #tmp.osm_hrini)/3600.00,2))
996 from #tmp, tab_eqphr
997 where #final.eqp_cd = tab_eqphr.eqp_cd and
998 #final.fun_mec = #tmp.fun_mec and
999 #final.data = #tmp.osm_dtini and
1000 #final.data = tab_eqphr.eqphr_dt and
1001 #tmp.osm_hrini > eqphr_fim1 and
1002 #tmp.osm_hrini < eqphr_inicio2 and
1003 #tmp.osm_hrfim > eqphr_inicio2 and
1004 eqphr_inicio2 > 0 and eqphr_fim1 > 0 and
1005 #tmp.conta_extra = 'S' and
1006 datepart( dw, #final.data ) = 7 ) ,0)
1007
1008
1009/***********************************************************************************************
1010 Outro Intervalo - Horario Alternativo
1011***********************************************************************************************/
1012-- f)
1013--dias normais
1014update #final set
1015 extras = extras + isnull(( select sum(round((tab_eqphr.eqphr_inicio2 - tab_eqphr.eqphr_fim1 )/3600.00,2))
1016 from #tmp, tab_eqphr
1017 where #final.eqp_cd = tab_eqphr.eqp_cd and
1018 #final.fun_mec = #tmp.fun_mec and
1019 #final.data = #tmp.osm_dtini and
1020 #final.data = tab_eqphr.eqphr_dt and
1021 #tmp.osm_hrini < eqphr_fim1 and
1022 #tmp.osm_hrfim > eqphr_inicio2 and
1023 eqphr_inicio2 > 0 and eqphr_fim1 > 0 and
1024 #tmp.conta_extra = 'S' and
1025 datepart( dw, #final.data ) between 2 and 6 ) ,0)
1026--sabado
1027update #final set
1028 extras = extras + isnull(( select sum(round((tab_eqphr.eqphr_inicio2 - tab_eqphr.eqphr_fim1 )/3600.00,2))
1029 from #tmp, tab_eqphr
1030 where #final.eqp_cd = tab_eqphr.eqp_cd and
1031 #final.fun_mec = #tmp.fun_mec and
1032 #final.data = #tmp.osm_dtini and
1033 #final.data = tab_eqphr.eqphr_dt and
1034 #tmp.osm_hrini < eqphr_fim1 and
1035 #tmp.osm_hrfim > eqphr_inicio2 and
1036 eqphr_inicio2 > 0 and eqphr_fim1 > 0 and
1037 #tmp.conta_extra = 'S' and
1038 datepart( dw, #final.data ) = 7 ) ,0)
1039
1040
1041/***********************************************************************************************
1042 Outro Intervalo - Horario Alternativo
1043***********************************************************************************************/
1044-- O tempo trabalhado depois do fim do expediente
1045-- g) e h)
1046---dias normais
1047update #final set
1048 extras = extras + isnull(( select round((max(osm_hrfim) - eqphr_fim2)/3600.00,2)
1049 from #tmp, tab_eqphr
1050 where #final.eqp_cd = tab_eqphr.eqp_cd and
1051 #final.fun_mec = #tmp.fun_mec and
1052 #final.data = #tmp.osm_dtfim and
1053 #final.data = tab_eqphr.eqphr_dt and
1054 #tmp.osm_hrfim > eqphr_fim2 and
1055 #tmp.conta_extra = 'S' and
1056 datepart( dw, #final.data ) between 2 and 6 and
1057 eqphr_fim2 > 0
1058 group by eqphr_fim2 ),0)
1059--sabado
1060update #final set
1061 extras = extras + isnull(( select round((max(osm_hrfim) - eqphr_fim2)/3600.00,2)
1062 from #tmp, tab_eqphr
1063 where #final.eqp_cd = tab_eqphr.eqp_cd and
1064 #final.fun_mec = #tmp.fun_mec and
1065 #final.data = #tmp.osm_dtfim and
1066 #final.data = tab_eqphr.eqphr_dt and
1067 #tmp.osm_hrfim > eqphr_fim2 and
1068 #tmp.conta_extra = 'S' and
1069 datepart( dw, #final.data ) = 7 and
1070 eqphr_fim2 > 0
1071 group by eqphr_fim2 ),0)
1072
1073/***********************************************************************************************
1074 Outro Intervalo - horario Alternativo
1075***********************************************************************************************/
1076-- i) O tempo trabalhado depois do fim do primeiro turno (quando nao tem segundo turno)
1077--dias normais
1078update #final set
1079 extras = extras + isnull(( select round((max(#tmp.osm_hrfim) - eqphr_fim1)/3600.00,2)
1080 from #tmp, tab_eqphr
1081 where #final.eqp_cd = tab_eqphr.eqp_cd and
1082 #final.fun_mec = #tmp.fun_mec and
1083 #final.data = #tmp.osm_dtini and
1084 #final.data = tab_eqphr.eqphr_dt and
1085 #tmp.osm_hrfim > eqphr_fim1 and
1086 eqphr_fim2 = 0 and
1087 eqphr_fim1 > 0 and
1088 #tmp.conta_extra = 'S' and
1089 datepart( dw, #final.data ) between 2 and 6
1090 group by eqphr_fim1 ) ,0)
1091--sabado
1092update #final set
1093 extras = extras + isnull(( select round((max(#tmp.osm_hrfim) - eqphr_fim1)/3600.00,2)
1094 from #tmp, tab_eqphr
1095 where #final.eqp_cd = tab_eqphr.eqp_cd and
1096 #final.fun_mec = #tmp.fun_mec and
1097 #final.data = #tmp.osm_dtfim and
1098 #final.data = tab_eqphr.eqphr_dt and
1099 #tmp.osm_hrfim > eqphr_fim1 and
1100 eqphr_fim2 = 0 and
1101 --para sabados nao trabalhados mas com extras
1102 eqphr_fim1 > eqphr_inicio1 and
1103 #tmp.conta_extra = 'S' and
1104 datepart( dw, #final.data ) = 7
1105 group by eqphr_fim1 ) ,0)
1106
1107
1108update #final set
1109 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2))
1110 from #tmp, tab_eqphr
1111 where #final.eqp_cd = tab_eqphr.eqp_cd and
1112 #final.fun_mec = #tmp.fun_mec and
1113 #final.data = #tmp.osm_dtfim and
1114 #final.data = tab_eqphr.eqphr_dt and
1115 eqphr_fim1 = 0 and
1116 eqphr_fim2 = 0 and
1117 eqphr_inicio1 = 0 and
1118 #tmp.conta_extra = 'S' and
1119 eqphr_inicio2 = 0),0)
1120
1121
1122update #final set
1123 calc_alter = 'True'
1124from #final, tab_eqphr
1125where #final.data = tab_eqphr.eqphr_dt and
1126 #final.eqp_cd = tab_eqphr.eqp_cd
1127
1128
1129
1130
1131/***********************************************************************************************
1132 Outro Intervalo
1133***********************************************************************************************/
1134-- O tempo trabalhado antes do inicio do expediente
1135-- a) e b)
1136-- dias normais
1137update #final set
1138 extras = isnull(( select Round((eqp_chsem_inicio1-min(osm_hrini))/3600.00,2)
1139 from #tmp, tab_eqp
1140 where #final.eqp_cd = tab_eqp.eqp_cd and
1141 #final.fun_mec = #tmp.fun_mec and
1142 #final.data = #tmp.osm_dtini and
1143
1144 #tmp.osm_hrini < eqp_chsem_inicio1 and
1145 #tmp.conta_extra = 'S' and
1146 datepart( dw, #final.data ) between 2 and 6 and
1147 eqp_chsem_inicio1 > 0
1148 group by eqp_chsem_inicio1 ),0)
1149
1150where #final.Calc_alter = 'False'
1151
1152--sabado
1153update #final set
1154 extras = extras + isnull(( select round((eqp_chsab_inicio1 - min(osm_hrini))/3600.00,2)
1155 from #tmp, tab_eqp
1156 where #final.eqp_cd = tab_eqp.eqp_cd and
1157 #final.fun_mec = #tmp.fun_mec and
1158 #final.data = #tmp.osm_dtini and
1159
1160 #tmp.osm_hrini < eqp_chsab_inicio1 and
1161 #tmp.conta_extra = 'S' and
1162 datepart( dw, #final.data ) = 7 and
1163 eqp_chsab_inicio1 > 0
1164 group by eqp_chsab_inicio1 ),0)
1165
1166where #final.Calc_alter = 'False'
1167
1168
1169
1170/***********************************************************************************************
1171 Outro Intervalo
1172***********************************************************************************************/
1173
1174-- O tempo trabalhado entre o fim do primeiro turno e o inicio do segundo turno
1175-- c)
1176--dias normais
1177update #final set
1178 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2))
1179 from #tmp, tab_eqp
1180 where #final.eqp_cd = tab_eqp.eqp_cd and
1181 #final.fun_mec = #tmp.fun_mec and
1182 #final.data = #tmp.osm_dtini and
1183
1184 #tmp.osm_hrini >=eqp_chsem_fim1 and
1185 #tmp.osm_hrfim <=eqp_chsem_inicio2 and
1186 #tmp.conta_extra = 'S' and
1187 datepart( dw, #final.data ) between 2 and 6 ) ,0)
1188where #final.Calc_alter = 'False'
1189
1190
1191
1192--sabado
1193update #final set
1194 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2))
1195 from #tmp, tab_eqp
1196 where #final.eqp_cd = tab_eqp.eqp_cd and
1197 #final.fun_mec = #tmp.fun_mec and
1198 #final.data = #tmp.osm_dtini and
1199
1200 #tmp.osm_hrini >=eqp_chsab_fim1 and
1201 #tmp.osm_hrfim <=eqp_chsab_inicio2 and
1202 eqp_chsab_inicio2 > 0 and eqp_chsab_fim1 > 0 and
1203 #tmp.conta_extra = 'S' and
1204 datepart( dw, #final.data ) = 7 ) ,0)
1205where #final.Calc_alter = 'False'
1206
1207
1208/***********************************************************************************************
1209 Outro Intervalo
1210***********************************************************************************************/
1211-- d)
1212--dias normais
1213update #final set
1214 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - tab_eqp.eqp_chsem_fim1)/3600.00,2))
1215 from #tmp, tab_eqp
1216 where #final.eqp_cd = tab_eqp.eqp_cd and
1217 #final.fun_mec = #tmp.fun_mec and
1218 #final.data = #tmp.osm_dtini and
1219
1220 #tmp.osm_hrini < eqp_chsem_fim1 and
1221 #tmp.osm_hrfim > eqp_chsem_fim1 and
1222 #tmp.osm_hrfim <=eqp_chsem_inicio2 and
1223 #tmp.conta_extra = 'S' and
1224 datepart( dw, #final.data ) between 2 and 6 ) ,0)
1225where #final.Calc_alter = 'False'
1226
1227
1228--sabado
1229update #final set
1230 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - tab_eqp.eqp_chsab_fim1)/3600.00,2))
1231 from #tmp, tab_eqp
1232 where #final.eqp_cd = tab_eqp.eqp_cd and
1233 #final.fun_mec = #tmp.fun_mec and
1234 #final.data = #tmp.osm_dtini and
1235
1236 #tmp.osm_hrini < eqp_chsab_fim1 and
1237 #tmp.osm_hrfim > eqp_chsab_fim1 and
1238 #tmp.osm_hrfim <=eqp_chsab_inicio2 and
1239 eqp_chsab_inicio2 > 0 and eqp_chsab_fim1 > 0 and
1240 #tmp.conta_extra = 'S' and
1241 datepart( dw, #final.data ) = 7 ) ,0)
1242where #final.Calc_alter = 'False'
1243
1244
1245
1246/***********************************************************************************************
1247 Outro Intervalo
1248***********************************************************************************************/
1249-- e)
1250--dias normais
1251update #final set
1252 extras = extras + isnull(( select sum(round((tab_eqp.eqp_chsem_inicio2 - #tmp.osm_hrini)/3600.00,2))
1253 from #tmp, tab_eqp
1254 where #final.eqp_cd = tab_eqp.eqp_cd and
1255 #final.fun_mec = #tmp.fun_mec and
1256 #final.data = #tmp.osm_dtini and
1257
1258 #tmp.osm_hrini > eqp_chsem_fim1 and
1259 #tmp.osm_hrini < eqp_chsem_inicio2 and
1260 #tmp.osm_hrfim > eqp_chsem_inicio2 and
1261 #tmp.conta_extra = 'S' and
1262 datepart( dw, #final.data ) between 2 and 6 ) ,0)
1263where #final.Calc_alter = 'False'
1264
1265--sabado
1266update #final set
1267 extras = extras + isnull(( select sum(round((tab_eqp.eqp_chsab_inicio2 - #tmp.osm_hrini)/3600.00,2))
1268 from #tmp, tab_eqp
1269 where #final.eqp_cd = tab_eqp.eqp_cd and
1270 #final.fun_mec = #tmp.fun_mec and
1271 #final.data = #tmp.osm_dtini and
1272
1273
1274 #tmp.osm_hrini > eqp_chsab_fim1 and
1275 #tmp.osm_hrini < eqp_chsab_inicio2 and
1276 #tmp.osm_hrfim > eqp_chsab_inicio2 and
1277 eqp_chsab_inicio2 > 0 and eqp_chsab_fim1 > 0 and
1278 #tmp.conta_extra = 'S' and
1279 datepart( dw, #final.data ) = 7 ) ,0)
1280where #final.Calc_alter = 'False'
1281
1282
1283/***********************************************************************************************
1284 Outro Intervalo
1285***********************************************************************************************/
1286-- f)
1287--dias normais
1288update #final set
1289 extras = extras + isnull(( select sum(round((tab_eqp.eqp_chsem_inicio2 - tab_eqp.eqp_chsem_fim1 )/3600.00,2))
1290 from #tmp, tab_eqp
1291 where #final.eqp_cd = tab_eqp.eqp_cd and
1292 #final.fun_mec = #tmp.fun_mec and
1293 #final.data = #tmp.osm_dtini and
1294
1295 #tmp.osm_hrini < eqp_chsem_fim1 and
1296 #tmp.osm_hrfim > eqp_chsem_inicio2 and
1297 #tmp.conta_extra = 'S' and
1298 datepart( dw, #final.data ) between 2 and 6 ) ,0)
1299where #final.Calc_alter = 'False'
1300
1301
1302
1303--sabado
1304update #final set
1305 extras = extras + isnull(( select sum(round((tab_eqp.eqp_chsab_inicio2 - tab_eqp.eqp_chsab_fim1 )/3600.00,2))
1306 from #tmp, tab_eqp
1307 where #final.eqp_cd = tab_eqp.eqp_cd and
1308 #final.fun_mec = #tmp.fun_mec and
1309 #final.data = #tmp.osm_dtini and
1310
1311 #tmp.osm_hrini < eqp_chsab_fim1 and
1312 #tmp.osm_hrfim > eqp_chsab_inicio2 and
1313 eqp_chsab_inicio2 > 0 and eqp_chsab_fim1 > 0 and
1314 #tmp.conta_extra = 'S' and
1315 datepart( dw, #final.data ) = 7 ) ,0)
1316where #final.Calc_alter = 'False'
1317
1318
1319
1320
1321/***********************************************************************************************
1322 Outro Intervalo
1323***********************************************************************************************/
1324-- O tempo trabalhado depois do fim do expediente
1325-- g) e h)
1326--dias normais
1327update #final set
1328 extras = extras + isnull(( select round((max(osm_hrfim) - eqp_chsem_fim2)/3600.00,2)
1329 from #tmp, tab_eqp
1330 where #final.eqp_cd = tab_eqp.eqp_cd and
1331 #final.fun_mec = #tmp.fun_mec and
1332 #final.data = #tmp.osm_dtfim and
1333
1334 #tmp.osm_hrfim > eqp_chsem_fim2 and
1335 #tmp.conta_extra = 'S' and
1336 datepart( dw, #final.data ) between 2 and 6 and
1337 eqp_chsem_fim2 > 0
1338 group by eqp_chsem_fim2 ),0)
1339where #final.Calc_alter = 'False'
1340
1341--sabado
1342update #final set
1343 extras = extras + isnull(( select round((max(osm_hrfim) - eqp_chsab_fim2)/3600.00,2)
1344 from #tmp, tab_eqp
1345 where #final.eqp_cd = tab_eqp.eqp_cd and
1346 #final.fun_mec = #tmp.fun_mec and
1347 #final.data = #tmp.osm_dtfim and
1348
1349 #tmp.osm_hrfim > eqp_chsab_fim2 and
1350 #tmp.conta_extra = 'S' and
1351 datepart( dw, #final.data ) = 7 and
1352 eqp_chsab_fim2 > 0
1353 group by eqp_chsab_fim2 ),0)
1354where #final.Calc_alter = 'False'
1355
1356
1357/***********************************************************************************************
1358 Outro Intervalo
1359***********************************************************************************************/
1360-- i) O tempo trabalhado depois do fim do primeiro turno (quando nao tem segundo turno)
1361--dias normais
1362update #final set
1363 extras = extras + isnull(( select round(sum(#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2)
1364 from #tmp, tab_eqp
1365 where #final.eqp_cd = tab_eqp.eqp_cd and
1366 #final.fun_mec = #tmp.fun_mec and
1367 #final.data = #tmp.osm_dtini and
1368
1369 #tmp.osm_hrfim > eqp_chsem_fim1 and
1370 eqp_chsem_fim2 = 0 and
1371 eqp_chsem_fim1 > 0 and
1372 #tmp.conta_extra = 'S' and
1373 datepart( dw, #final.data ) between 2 and 6
1374 group by eqp_chsem_fim1 ) ,0)
1375where #final.Calc_alter = 'False'
1376
1377
1378--sabado
1379--para concessionárias que tenham expediente no sábado
1380update #final set
1381 extras = extras + isnull(( select round(sum(#tmp.osm_hrfim - eqp_chsab_fim1)/3600.00,2)
1382 from #tmp, tab_eqp
1383 where #final.eqp_cd = tab_eqp.eqp_cd and
1384 #final.fun_mec = #tmp.fun_mec and
1385 #final.data = #tmp.osm_dtfim and
1386 #tmp.osm_hrfim > eqp_chsab_fim1 and
1387 eqp_chsab_fim2 = 0 and
1388 #tmp.conta_extra = 'S' and
1389 datepart( dw, #final.data ) = 7 and
1390 eqp_chsab_fim1 > 0 --chamado 71554
1391 group by eqp_chsab_fim1 ) ,0)
1392where #final.Calc_alter = 'False'
1393
1394--para concessionárias que NÃO tenham expediente no sábado
1395update #final set
1396 extras = extras + isnull(( select round(sum(#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2) --chamado 71554
1397 from #tmp, tab_eqp
1398 where #final.eqp_cd = tab_eqp.eqp_cd and
1399 #final.fun_mec = #tmp.fun_mec and
1400 #final.data = #tmp.osm_dtfim and
1401 #tmp.osm_hrfim > eqp_chsab_fim1 and
1402 eqp_chsab_fim2 = 0 and
1403 #tmp.conta_extra = 'S' and
1404 datepart( dw, #final.data ) = 7 and
1405 eqp_chsab_fim1 = 0 --chamado 71554
1406 group by eqp_chsab_fim1 ) ,0)
1407where #final.Calc_alter = 'False'
1408
1409
1410
1411
1412
1413
1414
1415-- j) O tempo trabalhado nos domingos e feriados
1416--domingo
1417update #final set
1418 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2))
1419 from #tmp
1420 where #final.fun_mec = #tmp.fun_mec and
1421 #final.data = #tmp.osm_dtfim and
1422 #tmp.conta_extra = 'S' and
1423 datepart( dw, #final.data ) = 1 ) ,0)
1424--feriado
1425update #final set
1426 extras = extras + isnull(( select sum(round((#tmp.osm_hrfim - #tmp.osm_hrini)/3600.00,2))
1427 from #tmp, tab_fe
1428 where #final.fun_mec = #tmp.fun_mec and
1429 #final.data = #tmp.osm_dtfim and
1430 #tmp.conta_extra = 'S' and
1431 tab_fe.fe_dt = #tmp.osm_dtfim) ,0)
1432
1433
1434--
1435-- Horas Jornada
1436update #final set
1437 jornada = jornada + outros
1438
1439--
1440-- Horas Teoricas
1441update #final set
1442 teoricas = jornada
1443
1444
1445--
1446-- atualizacao para nao pegar as horas antes da contratacao e depois da demissao
1447update #final set
1448 teoricas = teoricas - jornada
1449 from #final, tab_fun
1450 where #final.fun_mec = tab_fun.fun_cd and
1451 (tab_fun.fun_dtent > #final.data or
1452 tab_fun.fun_dtsai < #final.data)
1453
1454
1455
1456--
1457-- Horas Disponiveis
1458update #final set
1459 disponiveis = teoricas + extras - faltas - abonadas
1460
1461if @pm_fabricante = 'IVECO' -- ABATE TREINAMENTO PARA IVECO
1462begin
1463 update #final set
1464 disponiveis = disponiveis - treino
1465end
1466
1467-- acrescentado a clausula where por Maritania para o chamado 21778 - fiat, para contar horas disponiveis em feriados
1468-- apenas para os funcionarios que trabalharam.
1469
1470update #final set
1471 disponiveis = 0
1472where not exists (select 1 from sof_osm where #final.fun_mec = sof_osm.fun_mec and
1473 #final.data = sof_osm.osm_dtini) and
1474 exists (select 1 from tab_fe where tab_fe.fe_dt = convert(datetime, convert(varchar(2), datepart(month,#final.data)) + '/' +
1475 case when convert(varchar(2), datepart(month,#final.data)) = '2' and convert(varchar(2), datepart(day,#final.data)) = '29' then '28'
1476 else convert(varchar(2), datepart(day,#final.data)) end +'/1900') )
1477
1478
1479
1480--
1481-- Horas de inatividade
1482update #final set
1483 inativo= case when (disponiveis - aplicadas - retorno - treino) > 0
1484 then (disponiveis - aplicadas - retorno - treino)
1485 else 0
1486 end
1487
1488--
1489-- Retornando...
1490
1491/*****Inicio Alteração Chamado 76499 - Jamile Lopes em 16.07.08***/
1492--if (select count(*) from #tmp_fun) = 1 begin
1493if @st2 = 'N' -- Define se será chamado ou não da procedure do st2 - up_calcula_st2
1494begin
1495if @cl_tp = 'A' begin
1496 if @cl_setor = 'M' begin
1497 select data,
1498 mecanico=tab_fun.fun_nm,
1499 registro=tab_fun.fun_matr,
1500 jornada,
1501 normais,
1502 extras,
1503 teoricas,
1504 disponiveis,
1505 aplicadas,
1506 a360,
1507 v360,
1508 a361,
1509 v361,
1510 a362,
1511 v362,
1512 a363,
1513 v363,
1514 a362_r,
1515 v362_r,
1516 a120,
1517 v120,
1518 a601_01,
1519 v601_01,
1520 a601_02,
1521 v601_02,
1522 a624,
1523 v624,
1524 a625,
1525 v625,
1526 faltas,
1527 abonadas,
1528 treino,
1529 retorno,
1530 inativo,
1531 fabr = (select ltrim(rtrim(pm_valor)) from ger_pm where pm_id = 'PM_fabricante') -- Adicionada linha Chamado 76499
1532 from #final
1533 INNER JOIN tab_fun ON ( #final.fun_mec = tab_fun.fun_cd )
1534 INNER JOIN tab_funemp ON ( #final.fun_mec = tab_funemp.fun_cd
1535 AND tab_funemp.funemp_default = 'S'
1536 AND tab_funemp.emp_cd = case when (aplicadas+vendidas) = 0
1537 then @emp else tab_funemp.emp_cd
1538 end)
1539 INNER JOIN (select fun_cd
1540 from #tmp_fun
1541 where #tmp_fun.set_gr = @cl_Setor) AS fun ON ( fun.fun_cd = tab_fun.fun_cd )
1542
1543 ORDER BY mecanico,data
1544
1545 end -- fim if @cl_setor = 'M'
1546
1547 else begin -- else @cl_setor = 'F'
1548
1549 select data,
1550 mecanico=tab_fun.fun_nmguerra,
1551 registro=tab_fun.fun_matr,
1552 jornada,
1553 normais,
1554 extras,
1555 teoricas,
1556 disponiveis,
1557 aplicadas,
1558 a360,
1559 v360,
1560 a361,
1561 v361,
1562 a362,
1563 v362,
1564 a363,
1565 v363,
1566 a362_r,
1567 v362_r,
1568 a120,
1569 v120,
1570 a601_01,
1571 v601_01,
1572 a601_02,
1573 v601_02,
1574 a624,
1575 v624,
1576 a625,
1577 v625,
1578 faltas,
1579 abonadas,
1580 treino,
1581 retorno,
1582 inativo,
1583 fabr = (select ltrim(rtrim(pm_valor)) from ger_pm where pm_id = 'PM_fabricante') -- Adicionada linha Chamado 76499
1584 from #final
1585 INNER JOIN tab_fun ON ( #final.fun_mec = tab_fun.fun_cd )
1586 INNER JOIN tab_funemp ON ( #final.fun_mec = tab_funemp.fun_cd
1587 AND tab_funemp.funemp_default = 'S'
1588 AND tab_funemp.emp_cd = case when (aplicadas+vendidas) = 0
1589 then @emp
1590 else tab_funemp.emp_cd
1591 end )
1592 INNER JOIN (select fun_cd
1593 from #tmp_fun
1594 where #tmp_fun.set_gr = 'F'
1595 or #tmp_fun.set_gr = 'P'
1596 )AS fun ON (fun.fun_cd = tab_fun.fun_cd)
1597
1598 order by mecanico,data
1599 end -- fim if @cl_setor
1600end -- fim if @cl_tp = 'A'
1601else begin -- else if @cl_tp = 'S'
1602 if @cl_setor = 'M' begin
1603 select mecanico = tab_fun.fun_nmguerra,
1604 mecqtd = (select count(*) from #tmp_fun where #tmp_fun.set_gr = @cl_setor),
1605 registro=convert( char(15), '' ),
1606 jornada=sum(jornada),
1607 normais=sum(normais),
1608 extras=sum(extras),
1609 teoricas=sum(teoricas),
1610 disponiveis=isnull(sum(disponiveis),0),
1611 aplicadas=sum(aplicadas),
1612 a360=sum(a360),
1613 v360=sum(v360),
1614 a361=sum(a361),
1615 v361=sum(v361),
1616 a362=sum(a362),
1617 v362=sum(v362),
1618 a363=sum(a363),
1619 v363=sum(v363),
1620 a362_r=sum(a362_r),
1621 v362_r=sum(v362_r),
1622 a120=sum(a120),
1623 v120=sum(v120),
1624 a601_01=sum(a601_01),
1625 v601_01=sum(v601_01),
1626 a601_02=sum(a601_02),
1627 v601_02=sum(v601_02),
1628 a624=sum(a624),
1629 v624=sum(v624),
1630 a625=sum(a625),
1631 v625=sum(v625),
1632 faltas=sum(faltas),
1633 abonadas=sum(abonadas),
1634 treino=sum(treino),
1635 retorno=sum(retorno),
1636 inativo=sum(inativo),
1637 fabr = (select ltrim(rtrim(pm_valor)) from ger_pm where pm_id = 'PM_fabricante') -- Adicionada linha Chamado 76499
1638 from #final
1639 INNER JOIN tab_fun ON ( #final.fun_mec = tab_fun.fun_cd )
1640 INNER JOIN tab_funemp ON ( #final.fun_mec = tab_funemp.fun_cd
1641 AND tab_funemp.funemp_default='S'
1642 AND tab_funemp.emp_cd= case when (aplicadas+vendidas) = 0 then @emp
1643 else tab_funemp.emp_cd end )
1644 INNER JOIN (select fun_cd
1645 from #tmp_fun
1646 where #tmp_fun.set_gr = @cl_setor
1647 group by fun_cd ) AS fun ON (fun.fun_cd = tab_fun.fun_cd )
1648
1649 group by tab_fun.fun_nmguerra
1650 order by tab_fun.fun_nmguerra
1651 end -- fim if @cl_setor = 'M'
1652 else begin -- else if @cl_setor = 'F'
1653 select mecanico = tab_fun.fun_nmguerra,
1654 mecqtd = (select count(*) from #tmp_fun where #tmp_fun.set_gr = @cl_setor),
1655 registro=convert( char(15), '' ),
1656 jornada=sum(jornada),
1657 normais=sum(normais),
1658 extras=sum(extras),
1659 teoricas=sum(teoricas),
1660 disponiveis=isnull(sum(disponiveis),0),
1661 aplicadas=sum(aplicadas),
1662 a360=sum(a360),
1663 v360=sum(v360),
1664 a361=sum(a361),
1665 v361=sum(v361),
1666 a362=sum(a362),
1667 v362=sum(v362),
1668 a363=sum(a363),
1669 v363=sum(v363),
1670 a362_r=sum(a362_r),
1671 v362_r=sum(v362_r),
1672 a120=sum(a120),
1673 v120=sum(v120),
1674 a601_01=sum(a601_01),
1675 v601_01=sum(v601_01),
1676 a601_02=sum(a601_02),
1677 v601_02=sum(v601_02),
1678 a624=sum(a624),
1679 v624=sum(v624),
1680 a625=sum(a625),
1681 v625=sum(v625),
1682 faltas=sum(faltas),
1683 abonadas=sum(abonadas),
1684 treino=sum(treino),
1685 retorno=sum(retorno),
1686 inativo=sum(inativo),
1687 fabr = (select ltrim(rtrim(pm_valor)) from ger_pm where pm_id = 'PM_fabricante') -- Adicionada linha Chamado 76499
1688 from #final
1689 INNER JOIN tab_fun ON ( #final.fun_mec = tab_fun.fun_cd )
1690 INNER JOIN tab_funemp ON ( #final.fun_mec = tab_funemp.fun_cd
1691 and tab_funemp.funemp_default='S'
1692 and tab_funemp.emp_cd= case when (aplicadas+vendidas) = 0
1693 then @emp
1694 else tab_funemp.emp_cd
1695 end )
1696
1697 INNER JOIN (select #tmp_fun.fun_cd
1698 from #tmp_fun
1699 where #tmp_fun.set_gr = 'F'
1700 or #tmp_fun.set_gr = 'P'
1701 GROUP BY #tmp_fun.fun_cd ) AS fun ON ( fun.fun_cd = tab_fun.fun_cd)
1702
1703 group by tab_fun.fun_nmguerra
1704 order by tab_fun.fun_nmguerra
1705 end -- fim else if @cl_setor = 'F'
1706end -- fim else if @cl_tp = 'S'
1707end -- fim if @st2 = 'N'
1708-- Define se será chamado ou não da procedure do st2 - up_calcula_st2
1709/*****Fim Alteração Chamado 76499 - Jamile Lopes em 16.07.08***/
1710
1711else -- if @st2='S'
1712insert #final_st2 select * from #final
1713--drop table #tmp_fun
1714-- ATENÇÃO: ESSA PROCEDURE É CHAMADA POR OUTRA NO COCKPIT, ANTES DE ALTERÁ-LA VEJA A UP_COCKPIT_HR_DISPONIVEL
1715-- ATENÇÃO: ESSA PROCEDURE É CHAMADA POR UP_SOF_RESUMO_PROCESSAMENTO e MD_PCIC_HORAS_TRABALHADAS
1716
1717