CREATE OR REPLACE PACKAGE UTILIT AS
   FUNCTION SUBT_HORA(HORA1 IN VARCHAR2, HORA2 IN VARCHAR2, DIA IN OUT NUMBER)
            RETURN VARCHAR2;
   FUNCTION SUBT_HORA2(HORA1 IN VARCHAR2, HORA2 IN VARCHAR2)
            RETURN VARCHAR2;
   FUNCTION SOMA_HORA(HORA1 IN VARCHAR2, HORA2 IN VARCHAR2)
            RETURN VARCHAR2;
   FUNCTION SOMA_HORA2(HORA1 IN VARCHAR2, HORA2 IN VARCHAR2)
            RETURN VARCHAR2;
   FUNCTION MULT_HORA(HORA IN VARCHAR2, VRMULT IN NUMBER)
            RETURN VARCHAR2;
   PRAGMA RESTRICT_REFERENCES (SUBT_HORA, WNDS);
   PRAGMA RESTRICT_REFERENCES (SUBT_HORA2, WNDS);
   PRAGMA RESTRICT_REFERENCES (SOMA_HORA, WNDS);
   PRAGMA RESTRICT_REFERENCES (SOMA_HORA2, WNDS);
   PRAGMA RESTRICT_REFERENCES (MULT_HORA, WNDS);
   -- ESTE COMANDO PERMITE AO ORACLE UTILIZAR UMA FUNCAO DENTRO DE UM SELECT
END UTILIT;
/
CREATE OR REPLACE PACKAGE BODY UTILIT AS
   FUNCTION SUBT_HORA(HORA1 IN VARCHAR2, HORA2 IN VARCHAR2, DIA IN OUT NUMBER)
   RETURN VARCHAR2
   AS
      HH1 NUMBER(4);
      HH2 NUMBER(4);
      MM1 NUMBER(4);
      MM2 NUMBER(4);
      RHH NUMBER(4);
      RMM NUMBER(4);
      HORAJUST1 VARCHAR2(6);
      HORAJUST2 VARCHAR2(6);
   BEGIN
--DBMS_OUTPUT.PUT_LINE ('hora1 -'||HORA1||' HORA2 - '||HORA2);
      HORAJUST1 := LPAD(HORA1,6,'0');
      HORAJUST2 := LPAD(HORA2,6,'0');
      RHH := 0;
      RMM := 0;
      DIA := 0;
      HH1 := TO_NUMBER(SUBSTR(HORAJUST1,1,4));
      HH2 := TO_NUMBER(SUBSTR(HORAJUST2,1,4));
      MM1 := TO_NUMBER(SUBSTR(HORAJUST1,5,2));
      MM2 := TO_NUMBER(SUBSTR(HORAJUST2,5,2));
      DIA := FLOOR(HH2 / 24) * -1;
      HH2 := MOD(HH2,24);
      IF (MM2 > MM1) THEN
         RMM := 60 - (MM2 - MM1);
         HH1 := HH1 - 1;
      ELSE
         RMM := MM1 - MM2;
      END IF;
      IF (HH2 > HH1) THEN
         RHH := 24 - (HH2 - HH1);
         DIA := DIA - 1;
      ELSE
         RHH := HH1 - HH2;
      END IF;
--DBMS_OUTPUT.PUT_LINE('VALOR 1 '||LPAD(TO_CHAR(RHH),2,'0'));
--DBMS_OUTPUT.PUT_LINE('VALOR 2 '||LPAD(TO_CHAR(RMM),2,'0'));
      RETURN(LPAD(TO_CHAR(RHH),2,'0')||LPAD(TO_CHAR(RMM),2,'0'));
   END;
 
   FUNCTION SUBT_HORA2(HORA1 IN VARCHAR2, HORA2 IN VARCHAR2)
   RETURN VARCHAR2
   AS
      RESULT VARCHAR2(5);
      DIA NUMBER(2);
   BEGIN
      RESULT := UTILIT.SUBT_HORA(HORA1,HORA2,DIA);
      RETURN(RESULT);
   END;
 
   FUNCTION SOMA_HORA (HORA1 IN VARCHAR2, HORA2 IN VARCHAR2)
   RETURN VARCHAR2
   AS
      HH1 NUMBER(4);
      HH2 NUMBER(4);
      MM1 NUMBER(4);
      MM2 NUMBER(4);
      RHH NUMBER(4);
      RMM NUMBER(4);
   BEGIN
      RHH := 0;
      RMM := 0;
      HH1 := TO_NUMBER(SUBSTR(HORA1,1,2));
      HH2 := TO_NUMBER(SUBSTR(HORA2,1,2));
      MM1 := TO_NUMBER(SUBSTR(HORA1,3,2));
      MM2 := TO_NUMBER(SUBSTR(HORA2,3,2));
      RMM := MOD((MM1 + MM2),60);
      RHH := HH1 + HH2 + TRUNC((MM1 + MM2)/60);
      RETURN(LPAD(TO_CHAR(RHH),2,'0')||LPAD(TO_CHAR(RMM),2,'0'));
   END;
   
   FUNCTION SOMA_HORA2 (HORA1 IN VARCHAR2, HORA2 IN VARCHAR2)
   RETURN VARCHAR2
   AS
      MM1 NUMBER;
      MM2 NUMBER;
      MMT NUMBER;
      RHH NUMBER;
      RMM NUMBER;
      TAM_RHH NUMBER;
   BEGIN
      RHH := 0;
      RMM := 0;
      
      MM1 := TO_NUMBER(SUBSTR(HORA1, LENGTH(HORA1)-1, 2))+(ABS(TO_NUMBER(SUBSTR(HORA1, 1, LENGTH(HORA1)-2)))*60);
      MM2 := TO_NUMBER(SUBSTR(HORA2, LENGTH(HORA2)-1, 2))+(ABS(TO_NUMBER(SUBSTR(HORA2, 1, LENGTH(HORA2)-2)))*60);
      
      IF SUBSTR(HORA1,1,1) = '-' THEN
         MM1 := -1 * MM1;
      END IF;
      
      IF SUBSTR(HORA2,1,1) = '-' THEN
         MM2 := -1 * MM2;
      END IF;
      
      MMT := MM1 + MM2;
      RMM := MOD(ABS(MMT),60);
      RHH := TRUNC(ABS(MMT)/60);
      
      IF LENGTH(TO_CHAR(RHH)) < 2 THEN
         TAM_RHH := 2;
      ELSE
         TAM_RHH := LENGTH(TO_CHAR(RHH));
      END IF;      
      
      IF MMT < 0 THEN
         RETURN('-'||LPAD(TO_CHAR(RHH),TAM_RHH,'0')||LPAD(TO_CHAR(RMM),2,'0'));
      ELSE
         RETURN(LPAD(TO_CHAR(RHH),TAM_RHH,'0')||LPAD(TO_CHAR(RMM),2,'0')); 
      END IF;     
   END;
   
   FUNCTION MULT_HORA (HORA IN VARCHAR2, VRMULT IN NUMBER)
   RETURN VARCHAR2
   AS
      MM NUMBER;
      MMT NUMBER;
      RHH NUMBER;
      RMM NUMBER;
      TAM_RHH NUMBER;
		SEP VARCHAR2(01);
   BEGIN
      RHH := 0;
      RMM := 0;
		
		SEP := '';
		IF INSTR(HORA, ':') > 0 THEN 
			SEP := ':';
		END IF;
      
      IF SEP = ':' THEN
			MM := TO_NUMBER(SUBSTR(HORA, LENGTH(HORA)-1, 2))+(ABS(TO_NUMBER(SUBSTR(HORA, 1, LENGTH(HORA)-3)))*60);
		ELSE
			MM := TO_NUMBER(SUBSTR(HORA, LENGTH(HORA)-1, 2))+(ABS(TO_NUMBER(SUBSTR(HORA, 1, LENGTH(HORA)-2)))*60);
		END IF;	
      
      IF SUBSTR(HORA,1,1) = '-' THEN
         MM := -1 * MM;
      END IF;
      
      MMT := MM * VRMULT;
      RMM := ROUND(MOD(ABS(MMT),60));
      RHH := TRUNC(ABS(MMT)/60);
		
		IF TO_CHAR(RMM) = '60' THEN
			RMM := 0;
			RHH := RHH + 1;	
		END IF;
      
      IF LENGTH(TO_CHAR(RHH)) < 2 THEN
         TAM_RHH := 2;
      ELSE
         TAM_RHH := LENGTH(TO_CHAR(RHH));
      END IF;      
      
      IF MMT < 0 THEN
         RETURN('-'||LPAD(TO_CHAR(RHH),TAM_RHH,'0')||SEP||LPAD(TO_CHAR(RMM),2,'0'));
      ELSE
         RETURN(LPAD(TO_CHAR(RHH),TAM_RHH,'0')||SEP||LPAD(TO_CHAR(RMM),2,'0')); 
      END IF;     
   END;
END UTILIT;
/


