Calculate Tolerance of Point Card

wilkemaia · Sat, 11 Oct 2014 9:43 am

Good morning guys, I would like your help I need to calculate the daily beats of an employee as long as
10 minutes daily. Is anyone an example of how to do?
: Cry: I have for example the beat 07:55, 12: 05, 14: 00.18: 00 In this case it generated the 10 minutes of tolerance
in the official case entered 08:06, 12: 06,14: 06,18: 06 In this case it would have to stay 24 negatives
I am doing so in my select

Select all

 
hr_hist_inicio:=08:00,hr_hist_intervalo:=12:00,hr_hist_retorno:=14:00, 
 
select hr_inicial,HR_INICIO_INTERVALO,HR_FINAL_INTERVALO,hr_final,hr_hist_saida=18:06 
  
 ((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))- ((subStr(hr_inicial,1,2)*60) + subStr(hr_inicial,4,2)) + 
 ((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2))-((subStr(hr_inicio_intervalo,1,2)*60) + TO_NUMBER(REPLACE(substr(HR_INICIO_INTERVALO,4,2),':',''), '99')) + 
 ((subStr(hr_hist_retorno,1,2)*60) + subStr(hr_hist_retorno,4,2))-((subStr(hr_final_intervalo,1,2)*60) + subStr(hr_final_intervalo,4,2)) + 
 ((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2)) -((subStr(hr_final,1,2)*60) + subStr(hr_final,4,2))min 
 
from tb_ponto_diario 
where cd_chapa='5794' and dt_ponto=fn_conv_data('26/09/2014')

Sat, 11 Oct 2014 4:08 pm

I did not quite understand what it is to do. Do you want to know the delay based on a fixed time?
(or only the number of minutes he worked at least?)

In this example: 08: 06,12: 06,14: 06,18: 06
Theoretically, if it arrived 6 minutes late, but in compensation, it worked 6 minutes more than half day. (balance zero). The same in the afternoon. In my opinion, he stayed with zero balance, because he recovered his hours.

wilkemaia · Mon, 13 Oct 2014 8:42 am

Yes, it would be in minutes of delay but with the calculation of the 10 minutes of tolerance, but also can not compensate. Would you have any example?

Mon, 13 Oct 2014 7:13 pm

Since the goal is to know the delay based on a fixed schedule, I believe that the ideal is always testing:

Upon arrival:
* [color=# FF0000] Hour_Chegou [/ color ] - Time_Correct

At the output:
* Hora_correct - [color=# FF0000] Hourness_Saiu [/color]

In this way, if that Returned value is positive , means that it has arrived late or left before, and this value must tell as not worked.

Let's test an example:

[color=# FF0000] 08:06 [/color] - 08:00 = 6 minutes (arrived late 6 minutes, according to the formula.)
12:00 - [color=# FF0000] 12:06 [/color] [/b] = -6 minutes (ie, He left after the hour at noon, in this case, does not matter, because it will not be compensated! If he left later, he lost, hehehe)
[color=# ff0000] 14:06 [/color] 14:00 = 6 minutes (arrived late afternoon).
18:00 - [color=# FF0000] 18:06 [/color] = -6 minutes (negative again, left after time, lost).

In this case, it should only consider the positive numbers!

In case, it will add 6 + 6 = 12 minutes of delay!
Other schedules should be ignored because it does not compensate.

As has 10 tolerance, it has to discount only 2 minutes from it.

Would this be?

wilkemaia · Mon, 13 Oct 2014 9:36 pm

It would be more or less like this.
If the employee arrived 08: 06- 12:06 - 14: 06- 18:06. I would have to give here 12 positive minutes since it does not compensate.
As the way to check the schedule, I thought it would use Decode, but I could not use it.

Example of tolerance 07:55 - 11: 55-13: 55-17: 55
08:05 - 12: 05- 14: 05-18: 05 I believe I which would have to check if the hit time is within
of this tolerance.
You would have any idea

Tue, 14 Oct 2014 8:58 am

How is the hours arranged in the table?
Send us an example.

wilkemaia · Tue, 14 Oct 2014 10:03 am

Friend the time table has this structure.

The main hours are
08:00 12:00 14:00 18:00
07:00 11:00 13:00 16:20
11:30 15:00 17:00 20:50
08:00 12:00 13:00 16:20

wilkemaia · Sat, 25 Oct 2014 9:02 am

Resolved, thank God and with the help of the Holy Spirit I managed to make the decodes I leave here the codes if
someone needs.
We create two positive hours and the other negative hours

Select all

 
create or replace 
FUNCTION fn_extranegativo(HR_INICIAL_ IN VARCHAR2 ,HR_INICIO_INTERVALO_ in varchar2,HR_FINAL_INTERVALO_ in varchar2,HR_FINAL_ in varchar2 ,HR_HIST_INICIO_ in varchar2 ,HR_HIST_INTERVALO_ in varchar2 ,HR_HIST_RETORNO_  in varchar2,HR_HIST_SAIDA_ in varchar2) 
RETURN number IS  /*Zerar as batidas que estão dentro 10 minutos diários, 5 minutos para mais  e 5 minutos para menos.Wilke 21/10/2014*/ 
vs_saida varchar2(10); 
vsaidanegativo number; 
hr_inicial varchar2(10); 
hr_hist_inicio VARCHAR2(5); 
 
hr_inicio_intervalo varchar2(5); 
hr_hist_intervalo varchar2(5); 
hr_final_intervalo varchar2(5); 
hr_hist_retorno varchar2(5); 
hr_final varchar2(5); 
hr_hist_saida varchar2(5); 
saldonegativo number; 
BEGIN 
      hr_inicial:=HR_INICIAL_; 
      hr_hist_inicio:=HR_HIST_INICIO_; 
       
      hr_inicio_intervalo:=HR_INICIO_INTERVALO_; 
      hr_hist_intervalo:=HR_HIST_INTERVALO_; 
      hr_final_intervalo:=HR_FINAL_INTERVALO_; 
      hr_hist_retorno:=HR_HIST_RETORNO_; 
      hr_final:=HR_FINAL_; 
      hr_hist_saida:=HR_HIST_SAIDA_;  
      saldonegativo:=0; 
       select   decode(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -5/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +5/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -4/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +4/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -3/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +3/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -2/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +2/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -1/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +1/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial))))))))))into hr_inicial 
                from dual; 
                 
       select          
                decode(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -5/(24*60),'hh24:mi')),hr_hist_intervalo,hr_inicio_intervalo, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +5/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -4/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +4/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -3/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +3/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -2/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +2/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -1/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +1/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO))))))))))INTO HR_INICIO_INTERVALO 
                                              
                from dual; 
           
          select  
                decode(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -5/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +5/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -4/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +4/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -3/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +3/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -2/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +2/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -1/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +1/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo))))))))))into hr_final_intervalo 
           from dual; 
           
        select 
                (decode(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -5/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +5/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -4/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +4/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -3/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +3/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -2/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +2/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -1/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +1/(24*60),'hh24:mi')),hr_hist_saida,hr_final)))))))))))into hr_final 
           from dual; 
           
    select     
           
           
          decode(SIGN(((subStr(hr_hist_retorno,1,2)*60) + subStr(hr_hist_retorno,4,2))-((subStr(hr_final_intervalo,1,2)*60) + subStr(hr_final_intervalo,4,2))),-1,    
          ((subStr(hr_hist_retorno,1,2)*60) + subStr(hr_hist_retorno,4,2))-((subStr(hr_final_intervalo,1,2)*60) + subStr(hr_final_intervalo,4,2)), 
          decode(SIGN(((subStr(hr_hist_retorno,1,2)*60) + subStr(hr_hist_retorno,4,2))-((subStr(hr_final_intervalo,1,2)*60) + subStr(hr_final_intervalo,4,2))),-1,NULL,0))+ 
           
          DECODE(((subStr(hr_final_intervalo,1,2)*60) + subStr(hr_final_intervalo,4,2)),NULL,((subStr(hr_hist_retorno,1,2)*60) + subStr(hr_hist_retorno,4,2))-((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2)),0)+ 
 
          DECODE(SIGN(((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))-((subStr(hr_inicial,1,2)*60) + subStr(hr_inicial,4,2))),-1, 
         ((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))-((subStr(hr_inicial,1,2)*60) + subStr(hr_inicial,4,2)), 
          DECODE(SIGN(((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))-((subStr(hr_inicial,1,2)*60) + subStr(hr_inicial,4,2))),-1,NULL,0))+ 
          DECODE(((subStr(hr_inicial,1,2)*60) + subStr(hr_inicial,4,2)),NULL,((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))-((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2)),0)+ 
          
          DECODE(((subStr(HR_INICIO_INTERVALO,1,2)*60) + subStr(HR_INICIO_INTERVALO,4,2)),NULL,((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))-((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2)),0) +    
 
           DECODE(SIGN(((subStr(hr_final,1,2)*60) + subStr(hr_final,4,2))-((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2))),-1, 
         ((subStr(hr_final,1,2)*60) + subStr(hr_final,4,2))-((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2)), 
          DECODE(SIGN(((subStr(hr_final,1,2)*60) + subStr(hr_final,4,2))-((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2))),-1,NULL,0))+ 
           
          DECODE(((subStr(hr_final,1,2)*60) + subStr(hr_final,4,2)),NULL,((subStr(hr_hist_retorno,1,2)*60) + subStr(hr_hist_retorno,4,2))-((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2)),0)+ 
           
          DECODE(SIGN(((subStr(hr_inicio_intervalo,1,2)*60) + TO_NUMBER(REPLACE(substr(HR_INICIO_INTERVALO,4,2),':',''), '99'))-((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2))),-1, 
         ((subStr(hr_inicio_intervalo,1,2)*60) + TO_NUMBER(REPLACE(substr(HR_INICIO_INTERVALO,4,2),':',''), '99'))-((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2)), 
         DECODE(SIGN(((subStr(hr_inicio_intervalo,1,2)*60) + TO_NUMBER(REPLACE(substr(HR_INICIO_INTERVALO,4,2),':',''), '99'))-((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2))),-1,NULL,0))into saldonegativo 
       from dual; 
 
           
         vsaidanegativo:=saldonegativo;  
          
                
                 
         
    return vsaidanegativo; 
end;

Select all

 
create or replace 
FUNCTION fn_extrapositivo(HR_INICIAL_ IN VARCHAR2 ,HR_INICIO_INTERVALO_ in varchar2,HR_FINAL_INTERVALO_ in varchar2,HR_FINAL_ in varchar2 ,HR_HIST_INICIO_ in varchar2 ,HR_HIST_INTERVALO_ in varchar2 ,HR_HIST_RETORNO_  in varchar2,HR_HIST_SAIDA_ in varchar2) 
RETURN number IS  /*Zerar as batidas que estão dentro 10 minutos diários, 5 minutos para mais  e 5 minutos para menos.Wilke 21/10/2014*/ 
vs_saida varchar2(10); 
vSALDOPOSITIVO number; 
hr_inicial varchar2(10); 
hr_hist_inicio VARCHAR2(5); 
 
hr_inicio_intervalo varchar2(5); 
hr_hist_intervalo varchar2(5); 
hr_final_intervalo varchar2(5); 
hr_hist_retorno varchar2(5); 
hr_final varchar2(5); 
hr_hist_saida varchar2(5); 
SALDOPOSITIVO number; 
BEGIN 
      hr_inicial:=HR_INICIAL_; 
      hr_hist_inicio:=HR_HIST_INICIO_; 
       
      hr_inicio_intervalo:=HR_INICIO_INTERVALO_; 
      hr_hist_intervalo:=HR_HIST_INTERVALO_; 
      hr_final_intervalo:=HR_FINAL_INTERVALO_; 
      hr_hist_retorno:=HR_HIST_RETORNO_; 
      hr_final:=HR_FINAL_; 
      hr_hist_saida:=HR_HIST_SAIDA_;  
      SALDOPOSITIVO:=0; 
       select   decode(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -5/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +5/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -4/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +4/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -3/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +3/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -2/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +2/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') -1/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial, 
                DECODE(hr_inicial,(to_char(to_date(hr_hist_inicio,'hh24:mi') +1/(24*60),'hh24:mi')),hr_hist_inicio,hr_inicial))))))))))into hr_inicial 
                from dual; 
                 
       select          
                decode(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -5/(24*60),'hh24:mi')),hr_hist_intervalo,hr_inicio_intervalo, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +5/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -4/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +4/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -3/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +3/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -2/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +2/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') -1/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO, 
                DECODE(hr_inicio_intervalo,(to_char(to_date(hr_hist_intervalo,'hh24:mi') +1/(24*60),'hh24:mi')),hr_hist_intervalo,HR_INICIO_INTERVALO))))))))))INTO HR_INICIO_INTERVALO 
                                              
                from dual; 
           
          select  
                decode(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -5/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +5/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -4/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +4/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -3/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +3/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -2/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +2/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') -1/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo, 
                DECODE(hr_final_intervalo,(to_char(to_date(hr_hist_retorno,'hh24:mi') +1/(24*60),'hh24:mi')),hr_hist_retorno,hr_final_intervalo))))))))))into hr_final_intervalo 
           from dual; 
           
        select 
                (decode(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -5/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +5/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -4/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +4/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -3/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +3/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -2/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +2/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') -1/(24*60),'hh24:mi')),hr_hist_saida,hr_final, 
                 DECODE(hr_final,(to_char(to_date(hr_hist_saida,'hh24:mi') +1/(24*60),'hh24:mi')),hr_hist_saida,hr_final)))))))))))into hr_final 
           from dual; 
           
    select        
          DECODE(SIGN( ((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))-((subStr(hr_inicial,1,2)*60) + subStr(hr_inicial,4,2))),+1, 
         ((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))-((subStr(hr_inicial,1,2)*60) + subStr(hr_inicial,4,2)), 
          DECODE(SIGN(((subStr(hr_hist_inicio,1,2)*60) + subStr(hr_hist_inicio,4,2))-((subStr(hr_inicial,1,2)*60) + subStr(hr_inicial,4,2))),+1,NULL,0))+ 
 
          DECODE(SIGN(((subStr(hr_final,1,2)*60) + subStr(hr_final,4,2))-((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2))),+1, 
         ((subStr(hr_final,1,2)*60) + subStr(hr_final,4,2))-((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2)), 
         DECODE(SIGN(((subStr(hr_final,1,2)*60) + subStr(hr_final,4,2))-((subStr(hr_hist_saida,1,2)*60) + subStr(hr_hist_saida,4,2))),+1,NULL,0))+ 
 
         DECODE(SIGN(((subStr(hr_inicio_intervalo,1,2)*60) + TO_NUMBER(REPLACE(substr(HR_INICIO_INTERVALO,4,2),':',''), '99'))-((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2))),+1, 
         ((subStr(hr_inicio_intervalo,1,2)*60) + TO_NUMBER(REPLACE(substr(HR_INICIO_INTERVALO,4,2),':',''), '99'))-((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2)), 
        DECODE(SIGN(((subStr(hr_inicio_intervalo,1,2)*60) + TO_NUMBER(REPLACE(substr(HR_INICIO_INTERVALO,4,2),':',''), '99'))-((subStr(hr_hist_intervalo,1,2)*60) + subStr(hr_hist_intervalo,4,2))),+1,NULL,0))into SALDOPOSITIVO 
       from dual; 
 
           
         vSALDOPOSITIVO:=SALDOPOSITIVO;  
                     
                
                 
         
    return vSALDOPOSITIVO; 
end;

Calculate Tolerance of Point Card

Who is online