Pacote de Funções DOX (Package) - Oracle

Pensando em facilitar a busca de informações dentro do software por meio de instruções SQL, ao longo do tempo foram criadas várias funções e procedures que simplificaram ainda mais a busca de informações, seja por nossos consultores internos, ou por nossos clientes em suas automações.

A Ema disponibiliza um pacote para criar no banco de dados que executará todas as funções e procedures instantaneamente e de uma só vez.

Para isso, basta executar os scripts abaixo para o banco especificado:

• Primeiramente, executar a criação da package

create or replace package pkg_ema as
  --variaveis
  function retorna_valor_variavel(xidprocesso in integer, xidvariavel in integer)  return varchar2;
  function retorna_valor_variavel_texto(xidprocesso in integer, xidvariavel in integer)  return varchar2;
  function retorna_valor_variavel_int(xidprocesso in integer, xidvariavel in integer)  return number;
  function retorna_valor_variavel_valor(xidprocesso in integer, xidvariavel in integer)  return number;
  function retorna_valor_variavel_data(xidprocesso in integer, xidvariavel in integer)  return date;
  function retorna_valor_variavel_datahr(xidprocesso in integer, xidvariavel in integer)  return date;
  
  --grade de dados
  function retorna_coluna_grade(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return varchar;
  function retorna_coluna_grade_texto(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return varchar;
  function retorna_coluna_grade_int(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return number;
  function retorna_coluna_grade_valor(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return number;
  function retorna_coluna_grade_data(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return date;
  function retorna_coluna_grade_datahr(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return date;
end;

• Em seguida, executar a criação do body

create or replace package body pkg_ema as
--variaveis
  function retorna_valor_variavel(xidprocesso in integer, xidvariavel in integer) return varchar2 is valor varchar2(5000);
  begin
    select (select valoratual from dual) into valor 
    from crm_processo_variavel  
    where crm_processo_variavel.idprocesso = xidprocesso 
      and crm_processo_variavel.idvariavel = xidvariavel ;
    return valor;
  end;
  
  function retorna_valor_variavel_texto(xidprocesso in integer, xidvariavel in integer) return varchar2 is valor varchar2(5000);
  begin
    select (select valoratual from dual) into valor 
    from crm_processo_variavel  
    where crm_processo_variavel.idprocesso = xidprocesso 
      and crm_processo_variavel.idvariavel = xidvariavel ;
    return valor;
  end;

  function retorna_valor_variavel_int(xidprocesso in integer, xidvariavel in integer)  return number is valor number(15,2);
  begin
    select (select to_number(decode(valoratual,'.',0,valoratual)) from dual)
      into valor 
      from crm_processo_variavel  
     where crm_processo_variavel.idprocesso = xidprocesso 
       and crm_processo_variavel.idvariavel = xidvariavel ;
    return valor;
  end;

  function retorna_valor_variavel_valor(xidprocesso in integer, xidvariavel in integer)  return number is valor number(15,2);
  begin
    select replace(replace(decode(upper(valoratual),'.','0',upper(valoratual)) , '.', ''), ',', '.')
      into valor 
      from crm_processo_variavel  
     where crm_processo_variavel.idprocesso = xidprocesso 
       and crm_processo_variavel.idvariavel = xidvariavel ;
    return valor;
  end;
  
  function retorna_valor_variavel_data(xidprocesso in integer, xidvariavel in integer)  return date is valor date;
  begin
    if (xidprocesso > 0 and xidvariavel > 0) then
    begin 
      select case when valoratual <> '.' then to_date(valoratual,'dd/mm/yyyy HH24:MI')
                                         else to_date('01-01-1900','dd/mm/yyyy HH24:MI') end into valor 
        from crm_processo_variavel  
       where crm_processo_variavel.idprocesso = xidprocesso 
         and crm_processo_variavel.idvariavel = xidvariavel ;
    end;
    else
      valor := to_date('01/01/1900','dd/mm/yyyy');
    end if;
    return trunc(valor);
  end;
  
  function retorna_valor_variavel_datahr(xidprocesso in integer, xidvariavel in integer)  return date is valor date;
  begin
    if (xidprocesso > 0 and xidvariavel > 0) then
    begin 
      select case when valoratual <> '.' then to_date(valoratual,'dd/mm/yyyy HH24:MI')
                                         else to_date('01-01-1900','dd/mm/yyyy HH24:MI') end into valor 
        from crm_processo_variavel  
       where crm_processo_variavel.idprocesso = xidprocesso 
         and crm_processo_variavel.idvariavel = xidvariavel ;
    end;
    else
      valor := to_date('01/01/1900','dd/mm/yyyy HH24:MI');
    end if;
    return valor;
  end;

--grade de dados
  function retorna_coluna_grade(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return varchar is leitura varchar(4000);
  begin
    select resposta into leitura  
      from crm_processo_grade_valor  
     where idprocesso   = xidprocesso and 
           idformulario = xidformulario and 
           idatividade  = xidatividade and
           idgrade      = xidgrade and 
           idvalor      = xidvalor and 
           idrepeticao  = (select max(idrepeticao) 
                            from crm_processo_grade_valor xx 
                            where xx.idatividade  = crm_processo_grade_valor.idatividade 
                              and xx.idprocesso   = crm_processo_grade_valor.idprocesso
                              and xx.idformulario = crm_processo_grade_valor.idformulario
                              and xx.idgrade      = crm_processo_grade_valor.idgrade);

    return(trim(leitura));   
  end;
  
  function retorna_coluna_grade_texto(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return varchar is leitura varchar(4000);
  begin
    select resposta into leitura  
      from crm_processo_grade_valor  
     where idprocesso   = xidprocesso and 
           idformulario = xidformulario and 
           idatividade  = xidatividade and
           idgrade      = xidgrade and 
           idvalor      = xidvalor and 
           idrepeticao  = (select max(idrepeticao) 
                            from crm_processo_grade_valor xx 
                            where xx.idatividade  = crm_processo_grade_valor.idatividade 
                              and xx.idprocesso   = crm_processo_grade_valor.idprocesso
                              and xx.idformulario = crm_processo_grade_valor.idformulario
                              and xx.idgrade      = crm_processo_grade_valor.idgrade);

    return(trim(leitura));   
  end;

  function retorna_coluna_grade_int(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return number is leitura number(15,2);
  begin
    leitura := 0 ;

    select (select to_number(decode(respostainteiro,'.',0,respostainteiro)) from dual) into leitura  
      from crm_processo_grade_valor  
     where idprocesso   = xidprocesso and 
           idformulario = xidformulario and 
           idatividade  = xidatividade and
           idgrade      = xidgrade and 
           idvalor      = xidvalor and 
           idrepeticao  = (select max(idrepeticao) 
                            from crm_processo_grade_valor xx 
                            where xx.idatividade  = crm_processo_grade_valor.idatividade 
                              and xx.idprocesso   = crm_processo_grade_valor.idprocesso
                              and xx.idformulario = crm_processo_grade_valor.idformulario
                              and xx.idgrade      = crm_processo_grade_valor.idgrade);
    return(leitura );   
  end;
  
  function retorna_coluna_grade_valor(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return number is leitura number(15,2);
  begin
    leitura := 0;
    select respostavalor into leitura  
      from crm_processo_grade_valor  
      where idprocesso  =  xidprocesso and 
           idformulario = xidformulario and 
           idatividade  = xidatividade and
           idgrade      = xidgrade and 
           idvalor      = xidvalor and 
           idrepeticao  = (select max(idrepeticao) 
                            from crm_processo_grade_valor xx 
                            where xx.idatividade  = crm_processo_grade_valor.idatividade
                              and xx.idprocesso   = crm_processo_grade_valor.idprocesso
                              and xx.idformulario = crm_processo_grade_valor.idformulario
                              and xx.idgrade      = crm_processo_grade_valor.idgrade);
    return(leitura);   
  end;
  
  function retorna_coluna_grade_data(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return date is leitura date;
  begin
    select trunc(respostadatahora) into leitura  
      from crm_processo_grade_valor  
     where idprocesso   = xidprocesso and 
           idformulario = xidformulario and 
           idatividade  = xidatividade and
           idgrade      = xidgrade and 
           idvalor      = xidvalor and 
           idrepeticao  = (select max(idrepeticao) 
                            from crm_processo_grade_valor xx 
                            where xx.idatividade  = crm_processo_grade_valor.idatividade
                              and xx.idprocesso   = crm_processo_grade_valor.idprocesso
                              and xx.idformulario = crm_processo_grade_valor.idformulario); 
    return(leitura);   
  end;
  
  function retorna_coluna_grade_datahr(xidprocesso in number, xidatividade in number,  xidformulario in number, xidgrade in number, xidvalor in number) return date is leitura date;
  begin
    select respostadatahora into leitura  
      from crm_processo_grade_valor  
     where idprocesso   = xidprocesso and 
           idformulario = xidformulario and 
           idatividade  = xidatividade and
           idgrade      = xidgrade and 
           idvalor      = xidvalor and 
           idrepeticao  = (select max(idrepeticao) 
                            from crm_processo_grade_valor xx 
                            where xx.idatividade  = crm_processo_grade_valor.idatividade
                              and xx.idprocesso   = crm_processo_grade_valor.idprocesso
                              and xx.idformulario = crm_processo_grade_valor.idformulario); 
    return(leitura);   
  end;
end;

Chamando as Funções

• Variável:

select pkg_ema.retorna_valor_variavel(idprocesso, 1) as padrao,
       pkg_ema.retorna_valor_variavel_texto(idprocesso, 2) as texto,
       pkg_ema.retorna_valor_variavel_int(idprocesso, 3) as inteiro,
       pkg_ema.retorna_valor_variavel_valor(idprocesso, 4) as valor,
       pkg_ema.retorna_valor_variavel_data(idprocesso, 5) as data,
       pkg_ema.retorna_valor_variavel_datahr(idprocesso, 6) as datahr
  from crm_processo where idprocesso = 0/*IDPROCESSO*/

• Grade:

select pkg_ema.retorna_coluna_grade(x.idprocesso, x.idatividade, x.idformulario, 1, x.idvalor) as padrao,
       pkg_ema.retorna_coluna_grade_texto(x.idprocesso, x.idatividade, x.idformulario, 2, x.idvalor) as texto,
       pkg_ema.retorna_coluna_grade_int(x.idprocesso, x.idatividade, x.idformulario, 3, x.idvalor) as inteiro,
       pkg_ema.retorna_coluna_grade_valor(x.idprocesso, x.idatividade, x.idformulario, 4, x.idvalor) as valor,
       pkg_ema.retorna_coluna_grade_data(x.idprocesso, x.idatividade, x.idformulario, 5, x.idvalor) as data,
       pkg_ema.retorna_coluna_grade_datahr(x.idprocesso, x.idatividade, x.idformulario, 6, x.idvalor) as datahr
  from crm_processo_grade_valor x
 where idprocesso = 0/*IDPROCESSO*/ and idatividade = 0/*IDATIVIDADE*/ and idformulario = 0/*IDFORMULARIO*/
   and idrepeticao = (select max(idrepeticao) from crm_processo_grade_valor where idprocesso = x.idprocesso and idatividade = x.idatividade)
   and idgrade = 1 order by idvalor