CREATE OR replace FUNCTION Tempo_medio_sla (p_nrorg         IN 
contato.nrorg%TYPE, 
                                            p_nrseqcontato  IN 
contato.nrseqcontato%TYPE, 
                                            p_tnrseqcontato IN VARCHAR2, 
                                            p_cdoperador    IN VARCHAR2) 
RETURN VARCHAR2 
IS 
  v_retorno1                  NUMBER; 
  v_retorno2                  NUMBER; 
  v_retorno3                  NUMBER;  
  v_retorno3_aux              NUMBER; 
  v_retorno4                  NUMBER; 
  v_retorno_geral             VARCHAR2(200); 
  v_ultdiamesant              DATE; 
  v_dtprimeira_resposta       DATE; 
  v_numero_tickets            NUMBER; 
  v_numero_evolucoes          NUMBER; 
  v_numero_tickets_resolvidos NUMBER; 
BEGIN 
    v_retorno1 := 0; 
    v_retorno2 := 0; 
    v_retorno3 := 0; 
    v_retorno3_aux := 0; 
    v_retorno4 := 0; 
    v_numero_tickets := 0; 
    v_numero_evolucoes := 0; 
    v_numero_tickets_resolvidos := 0; 
    v_retorno3_aux := 0; 
    /* Retorna os dados do Ticket necessarios para verifica o SLA */ 
    FOR c_ticket IN(SELECT nrseqcontato, 
                           cdproduto, 
                           nrparcnegocio, 
                           dthrcontato, 
                           nrprioridade, 
                           dthrconccont 
                    FROM   contato 
                    WHERE  Instr(p_cdoperador, cdopercont) > 0 
                           AND nrorg = p_nrorg 
                           AND idtipocontato = 'CHAMADO' 
                           AND idcancelado = 'N' 
                           AND ( nrseqcontato = p_nrseqcontato 
                                  OR p_tnrseqcontato = 'T' )) LOOP 
        v_numero_tickets := v_numero_tickets + 1; 
        /* Busca a data e hora da primeira resposta do ticket */ 
        BEGIN 
            SELECT Min(dthrevolcont) 
            INTO   v_dtprimeira_resposta 
            FROM   evolcont 
            WHERE  nrseqcontato = c_ticket.nrseqcontato 
                   AND idevolpublico = 'S' 
                   AND idrespnota = 'R'; 
        EXCEPTION 
            WHEN no_data_found THEN 
              v_dtprimeira_resposta := NULL; 
        END; 
        IF v_dtprimeira_resposta IS NULL THEN 
          v_retorno1 := v_retorno1 + 0; 
        ELSE 
          v_retorno1 := v_retorno1 + ( ( v_dtprimeira_resposta - 
                                         c_ticket.dthrcontato ) 
                                       * 24 * 60 ); 
        END IF; 
        /* Busca o tempo total de todas as resostas dadas de todos os ticket do operador passado */
        v_numero_evolucoes := 0; 
        FOR c_ticket_evol IN(SELECT dthrevolcont 
                             FROM   evolcont 
                             WHERE  nrseqcontato = c_ticket.nrseqcontato 
                                    AND idevolpublico = 'S' 
                                    AND idrespnota = 'R') LOOP 
            v_numero_evolucoes := v_numero_evolucoes + 1; 
            IF c_ticket_evol.dthrevolcont IS NULL THEN 
              v_retorno2 := v_retorno2 + 0; 
            ELSE 
              v_retorno2 := v_retorno2 + ( c_ticket_evol.dthrevolcont - 
                                         c_ticket.dthrcontato ) * 24 * 60; 
            END IF; 
        END LOOP; 
        IF v_numero_evolucoes > 0 THEN 
          v_retorno3_aux := v_retorno2 / v_numero_evolucoes; 
        END IF; 
        v_retorno3 := v_retorno3 + v_retorno3_aux; 
    END LOOP; 
    /* Calcula o tempo medio de resolucao, buscando apenas tickets resolvidos / encerrados */ 
    v_numero_tickets_resolvidos := 0; 
    FOR c_ticket_reso IN(SELECT nrseqcontato, 
                                dthrcontato, 
                                dthrconccont 
                         FROM   contato 
                         WHERE  Instr(p_cdoperador, cdopercont) > 0 
                                AND nrorg = p_nrorg 
                                AND nrseqsituacao IN ( 3, 4 ) 
                                AND idtipocontato = 'CHAMADO' 
                                AND idcancelado = 'N' 
                                AND ( nrseqcontato = p_nrseqcontato 
                                       OR p_tnrseqcontato = 'T' )) LOOP 
        v_numero_tickets_resolvidos := v_numero_tickets_resolvidos + 1; 
        IF c_ticket_reso.dthrconccont IS NULL THEN 
          v_retorno4 := v_retorno4 + 0; 
        ELSE 
          v_retorno4 := v_retorno4 + ( c_ticket_reso.dthrconccont - 
                                       c_ticket_reso.dthrcontato ) * 24 * 
                                          60; 
        END IF; 
    END LOOP; 
    IF v_numero_tickets = 0 THEN 
      v_numero_tickets := 1; 
    END IF; 
    IF v_numero_tickets_resolvidos = 0 THEN 
      v_numero_tickets_resolvidos := 1; 
    END IF; 
    v_retorno_geral := To_char(v_retorno1 / v_numero_tickets) 
                       || '_' 
                       || To_char(v_retorno3 / v_numero_tickets) 
                       || '_' 
                       || To_char(v_retorno4 / v_numero_tickets_resolvidos) 
                       || '_' 
                       || To_char(v_numero_tickets); 
    RETURN( v_retorno_geral ); 
END;
/
UPDATE CRM_GAMENIVEL
SET NRORG = SYS_CONTEXT('nrorg_ctx','nrorg')
WHERE NRORG = 0 