CREATE OR REPLACE FUNCTION SOMA_DATA_FERI
  (P_CDFILIAL  IN VARCHAR2,
   P_DTINICIAL IN DATE,
   P_DTFINAL   IN DATE) RETURN NUMBER IS

   V_DIAS    NUMBER;
   V_NRHORAS NUMBER;

BEGIN
   SELECT (COUNT(*)) * 11
   INTO V_DIAS
   FROM FERIFILI
   WHERE CDFILIAL = P_CDFILIAL
     AND DTFERIFILI BETWEEN P_DTINICIAL AND P_DTFINAL;

   IF TRUNC(P_DTFINAL) = TRUNC(P_DTINICIAL) THEN
      V_NRHORAS := ((TRUNC(P_DTFINAL - P_DTINICIAL) * 11) + (((P_DTFINAL - P_DTINICIAL) - TRUNC(P_DTFINAL - P_DTINICIAL)) * 24)) - V_DIAS;
   ELSE
      V_NRHORAS := ((TRUNC(P_DTFINAL - P_DTINICIAL) * 11) + ((((P_DTFINAL - P_DTINICIAL) - TRUNC(P_DTFINAL - P_DTINICIAL)) - (13/24)) * 24)) - V_DIAS;
   END IF;
   RETURN(V_NRHORAS);
END;
/

CREATE OR REPLACE FUNCTION TEMPO_RESP_ATEND
  (P_CDFILIAL  IN VARCHAR2,
   P_NRTAREFA  IN VARCHAR2) RETURN NUMBER IS

   V_NRHORAS NUMBER;

BEGIN

   SELECT
      AVG(SOMA_DATA_FERI(P_CDFILIAL, DTHR_ANTERIOR, DTHR_EVOLUCAO))
   INTO
      V_NRHORAS
   FROM
      (SELECT
          EVOL_A.CDFILIAL,
          EVOL_A.NRTAREFA,
          EVOL_A.DTINCATIVTAR AS DTHR_EVOLUCAO,
          EVOL_B.DTFAPATIVTAR AS DTHR_ANTERIOR
       FROM
          (SELECT CDFILIAL, NRTAREFA, NRPLATIVTARE, DTINCATIVTAR, IDSENTCOMUAT FROM PLANATIVTARE WHERE CDFILIAL = P_CDFILIAL AND NRTAREFA = P_NRTAREFA) EVOL_A,
          (SELECT PL.CDFILIAL, PL.NRTAREFA, PL.NRPLATIVTARE, PL.IDSENTCOMUAT, MAX(DTFAPATIVTAR) AS DTFAPATIVTAR
           FROM PLANATIVTARE PL, APROATIVTARE AP
           WHERE PL.CDFILIAL = P_CDFILIAL
             AND PL.NRTAREFA = P_NRTAREFA
             AND PL.CDFILIAL = AP.CDFILIAL
             AND PL.NRTAREFA = AP.NRTAREFA
             AND PL.NRPLATIVTARE = AP.NRPLATIVTARE
           GROUP BY PL.CDFILIAL, PL.NRTAREFA, PL.NRPLATIVTARE, PL.IDSENTCOMUAT) EVOL_B
       WHERE
          EVOL_B.DTFAPATIVTAR = (SELECT MAX(DTFAPATIVTAR) FROM APROATIVTARE
                                 WHERE CDFILIAL = P_CDFILIAL AND NRTAREFA = P_NRTAREFA AND DTFAPATIVTAR < EVOL_A.DTINCATIVTAR) AND
          EVOL_A.CDFILIAL     = EVOL_B.CDFILIAL  AND
          EVOL_A.NRTAREFA     = EVOL_B.NRTAREFA  AND
          EVOL_A.CDFILIAL     = P_CDFILIAL       AND
          EVOL_A.NRTAREFA     = P_NRTAREFA       AND
          EVOL_A.IDSENTCOMUAT||EVOL_B.IDSENTCOMUAT IN ('SE','SS')
       )
    GROUP BY
       CDFILIAL,
       NRTAREFA;

   RETURN(V_NRHORAS);
END;
/

CREATE OR REPLACE FUNCTION TEMPO_CONC_ATEND
  (P_CDFILIAL  IN VARCHAR2,
   P_NRTAREFA  IN VARCHAR2) RETURN NUMBER IS

   V_NRHORAS NUMBER;

BEGIN

   SELECT
      AVG(SOMA_DATA_FERI(P_CDFILIAL, MIN(DTHIAPATIVTA), MAX(DTHFAPATIVTA)))
   INTO
      V_NRHORAS
   FROM
      APROATIVTARE AP
   WHERE AP.CDFILIAL = P_CDFILIAL
     AND AP.NRTAREFA = P_NRTAREFA
    GROUP BY
       CDFILIAL,
       NRTAREFA;

   RETURN(V_NRHORAS);
END;
/
