Time Of Package

 CREATE OR REPLACE package time_of as
 
 function get_hours_form_seconds(p_ss number) return number;
 function get_minutes_form_seconds(p_ss number) return number;
 function get_ss_form_hr_mi(p_hr number , p_mi number) return number;  
 function hours_minutes(p_ss number) return varchar2;
 
 function shift(p_date date) return varchar2;
 
 function shift_prod(p_date date) return varchar2;
 function shift_loading_date(p_date date) return date;
 
 
 function shift_gate(p_date date) return varchar2;
 
 function shift_gate_dispatch_9(p_date date) return varchar2;
 
 function shift_LAB(p_date date) return varchar2;
 function date_gate(p_date date) return date;
 
 function date_prod(p_date date) return date;
 
 
 
 function datetime_gate(p_date date) return date;
 
 function make_time(p_fdate varchar2 , p_tdate varchar2 ) return varchar2; 
 
 function day_first_time return date;
 function day_last_time return date;
 
 function digit_to_time(p_time number ) return number;
 
 function result_to_time(p_time number ) return number;
 
 function result_time_to_time(p_time number ) return number;
 
 function time_to_secoonds(p_time number ) return number;
 
 function digit_to_minutes(p_time number ) return number;
 function minutes_to_hours(p_time number ) return number;
 
 function time_to_minutes(p_time number ) return number;
 
 --hr 28/12/2022
 
 FUNCTION hours_minutes_actual(p_ss NUMBER) RETURN NUMBER ;
 
 function get_time_diff_in_seconds(p_first date , p_second date) return number;
 
end;
/

/*********************************************************************/

CREATE OR REPLACE package body time_of as

 function get_hours_form_seconds(p_ss number) return number as

 retval number;

 begin

  if nvl(p_ss,0) <> 0 then

  retval := trunc(p_ss/ (3600));

else

    retval := 0;

     end if; 

  return retval;

 end;

 

 function get_minutes_form_seconds(p_ss number) return number as

 retval number;

 begin

    if nvl(p_ss,0) <> 0 then

    retval := trunc((p_ss - get_hours_form_seconds(p_ss)*(60*60))/60);

  else

    retval := 0;

  end if;

  

  return retval;

 end;

 function get_ss_form_hr_mi(p_hr number , p_mi number) return number as

 retval number;

 begin    

  retval := nvl(nvl(p_mi,0)*60,0) + nvl(nvl(p_hr,0)*3600,0) ;

  return retval;

 end;  

 function hours_minutes(p_ss number) return varchar2 as

   retval varchar2(10);

 begin

    if nvl(p_ss,0) = 0 then

    retval := '0-0';

  else 

     retval := get_hours_form_seconds(p_ss)||'-'|| get_minutes_form_seconds(p_ss);

  end if;

  return retval;

 end;  

function shift(p_date date) return varchar2 as

retval varchar2(10) := 'E';

begin

if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 401 and 1330 then

retval := 'M';

end if;  

return retval;

end;

function shift_prod(p_date date) return varchar2 as

retval varchar2(10) := 'A';

begin

if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0800 and 1600 then

retval := 'A';

elsif to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 1601 and 2359 then

   retval := 'B';

     else

   retval := 'C';  

end if;  

return retval;

end;

 function shift_loading_date(p_date date) return date as

retval date;

begin

if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0 and 0800 then

retval := sysdate-1;  

else

     retval := sysdate;

end if;  

return trunc(retval);

end;

 function shift_gate(p_date date) return varchar2 as

retval varchar2(10) := 'A';

begin

if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0800 and 2100 then

retval := 'A';

elsif to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 2101 and 2359 then

   retval := 'B';

elsif to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0 and 0759 then

   retval := 'B';

   

     else

   retval := 'C';  

end if;  

return retval;

end;

 function shift_gate_dispatch_9(p_date date) return varchar2 as

retval varchar2(10) := 'A';

begin

     if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0601 and 1400 then

          retval := 'A';

     elsif to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 1401 and 2200 then

            retval := 'B';

     elsif to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 2201 and 2359 then

            retval := 'C';

     elsif to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0000 and 0600 then

            retval := 'C';

     else

            retval := 'C';     

     end if;     

     return retval;

end;

  function shift_LAB(p_date date) return varchar2 as

retval varchar2(10) := 'A';

begin

if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0700 and 1900 then

retval := 'A';

elsif to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 1901 and 2359 then

   retval := 'B';

elsif to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0 and 0659 then

   retval := 'B';

   

     else

   retval := 'C';  

end if;  

return retval;

end;

 

 

 

function date_gate(p_date date) return date as

retval date;

begin

 

     /*if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0 and 0800 then

retval := sysdate-1;  

else

     retval := sysdate;

end if;

     */

     

     select sysdate - ( 8/24) into retval from dual ; 

          

return trunc(retval);

end;

function datetime_gate(p_date date) return date as

retval date;

begin

     if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0 and 0800 then

          retval := sysdate-1;         

     else

         retval := sysdate;

     end if;     

     return retval;

end;

function date_prod(p_date date) return date as

retval date;

begin

     if to_number(to_char(nvl(p_date,sysdate),'HH24MI')) between 0 and 0600 then

          retval := sysdate-1;         

     else

         retval := sysdate;

     end if;     

     return retval;

end;

 

function make_time(p_fdate varchar2 , p_tdate varchar2 ) return varchar2 as

retval varchar2(50);

dd1 date;

dd2 date;

ss number;

hrs number;

mins number;

dys number;

begin

dd1 := to_date(p_fdate, 'DD-MON-RRRR HH:MI:SS AM') ;

dd2 := to_date(p_tdate, 'DD-MON-RRRR HH:MI:SS AM') ;

ss := time_diff(dd1,dd2);

select  d , time_of.get_hours_form_seconds(ss)  , time_of.get_minutes_form_seconds(ss)

into  dys , hrs , mins

from (

select trunc(time_diff(dd1,dd2)/(24*60*60)) d,

(time_diff(dd1,dd2)/(24*60*60) - 

trunc(time_diff(dd1,dd2)/(24*60*60))*(60*60*24)) sss 

from dual

 

);

if nvl(dys,0) > 0 then

   retval := dys || ' Days ';

end if;

retval := retval ||  hrs || ' Hours ' || mins || ' Minutes ';

   

return retval;

end;

function day_first_time return date as

retval date;

begin

    

    select date_of.made_date(to_char(sysdate,'dd-mon-rrrr'),'08','00','00','PM')

    into retval from dual;

    

    return retval;

end;     

function day_last_time return date as

retval date;

begin

    

    select date_of.made_date(to_char(sysdate+1,'dd-mon-rrrr'),'07','59','00','PM')

    into retval from dual;

    

    return retval;

end;     

function digit_to_time(p_time number ) return number as

    retval number;

    frmtime number;

begin

    

    frmtime := round(((nvl(p_time,0)-trunc(nvl(p_time,0)))    *100 / 60) ,2);

    retval := trunc(nvl(p_time,0))+frmtime;

    return retval;

end;

function result_to_time(p_time number ) return number as

    retval number;

    frmtime number;

    part number;

begin

    

    frmtime := trunc(nvl(p_time,0)) ;

    part  := (p_time - frmtime ) * ( 60/100 ) ;

    retval := frmtime+part;

    return retval;

end;

     

function result_time_to_time(p_time number ) return number as

    retval number;

    frmtime number;

    part number;

begin

    

    frmtime := trunc(nvl(p_time,0)) ;

    part    := (p_time - frmtime )  ;

    if part >= .6 then

       part := part - .6;

       frmtime := frmtime + 1;

       

    end if;     

    

     retval := frmtime+part;

    

    return retval;

end;

function time_to_secoonds(p_time number ) return number as

    retval number;

    frmtime number;

    part number;

begin

    frmtime := trunc(nvl(p_time,0)) ;

    part    := (p_time - frmtime )  ;

    retval := ( frmtime * 3600 ) + (part *60*100);

    

    return  (retval );

end;       

function digit_to_minutes(p_time number ) return number as

    retval number;

    frmtime number;

    part number;

begin

     

    retval := ( p_time / 100 ) * 60;

    

    return  (retval );

end;       

function minutes_to_hours(p_time number ) return number as

    retval number;

    hrs number;

    part number;

begin

    

      if p_time = 0 then

        retval := '0';

      else      

           if round(p_time) > 60 then 

                hrs := trunc(p_time/60) ;

                part    := (round(p_time) - (hrs*60) )  ;

                retval :=  hrs ||'.'||part;

            elsif round(p_time) = 60 then

                retval := '1.00';

            else

               if round(p_time) < 1 then

                    retval := '0';

               else 

                    retval := '0.'||round(p_time);

               end if;

            end if;

       end if;

               

       retval := round(to_number(retval),2);      

    

        --return rpad(retval,2,'0');

        

        return retval;

end;  

function time_to_minutes(p_time number ) return number as

    retval number;

    hrs number;

    part number;

begin

    

       if p_time >= 1 then 

            hrs := trunc(p_time) ;

            part    := (p_time - hrs)*100  ;

            retval :=  hrs*60 + part;

        else

            retval := (p_time*100);

        end if;

       

       retval := round(to_number(retval),2);      

    

        --return rpad(retval,2,'0');

        

        return retval;

end;  

 FUNCTION hours_minutes_actual(p_ss NUMBER) RETURN NUMBER AS

   retval NUMBER;

 BEGIN

    IF NVL(p_ss,0) = 0 THEN

    retval := '';

  ELSE 

    if sign(get_minutes_form_seconds(p_ss)) = -1 then

  

  if sign(get_hours_form_seconds(p_ss)) = -1 then

     retval := (get_hours_form_seconds(p_ss)||'.'||  abs((NVL(get_minutes_form_seconds(p_ss),0)))) ;

elsif sign(get_hours_form_seconds(p_ss)) = 0 then

     retval := ((get_hours_form_seconds(p_ss)||'.'||  abs((NVL(get_minutes_form_seconds(p_ss),0)))))* -1 ;

elsif sign(get_hours_form_seconds(p_ss)) = 1 then

     retval := (get_hours_form_seconds(p_ss)||'.'||  abs((NVL(get_minutes_form_seconds(p_ss),0)))) ;

end if;  

  else   

    retval := (get_hours_form_seconds(p_ss)||'.'||  ((NVL(get_minutes_form_seconds(p_ss),0)))) ;

  end if;

  END IF;

  RETURN retval;

 END; 

function get_time_diff_in_seconds(p_first date , p_second date) return number as

retval number;

begin

SELECT   (  TO_DATE( to_char(p_first, 'DD-MON-RRRR HH:MI:SS AM') , 'DD-MON-RRRR HH:MI:SS AM' ) -  TO_DATE( to_char(p_second, 'DD-MON-RRRR HH:MI:SS AM') , 'DD-MON-RRRR HH:MI:SS AM' )) * 1440 * 60  

into retval  FROM DUAL ;

return retval;

end;

        

end;

/