set session_replication_role=replica;

alter table requisicao alter req_tcaalimentos type varchar(20);

alter table requisicao alter req_numerocertificadoalimentos type varchar(40);

alter table requisicao alter req_volumeabateprpproduto type varchar(15);

alter table requisicao add req_programa varchar(50);

alter table convenios add con_cei varchar(12);

insert into atualizacoes(atu_nome) values ('v20010001_171119.sql');

alter table material add mat_ipiultcompra numeric(15,4), add mat_freteultcompra numeric(15,2);

insert into atualizacoes(atu_nome) values ('v20010002_100220.sql');

drop function fn_tu_loteexternorequisicaorastreio() cascade;
drop function fn_ti_loteexternorequisicaorastreio() cascade;
drop table loteexternorequisicaorastreio cascade;


create table loteexternoexamereqrastreio (
       fil_kodigo           integer not null,
       fil_codigo           integer not null,
       lex_codigo           bigint not null,
       lae_codigo           integer not null,
       req_codigo           integer not null,
       exa_codigo           varchar(5) not null,
       mco_codigo           integer not null,
       lrr_codigo           serial not null,
       lrr_idtubo           varchar(14),
       lrr_origempedido     varchar(40),
       lrr_datahorastatus   timestamp,
       lrr_codigostatus     integer,
       lrr_descricaostatus  varchar(150)
);

create unique index xpkloteexternoexamereqrastreio on loteexternoexamereqrastreio
(
       fil_kodigo,
       fil_codigo,
       lex_codigo,
       lae_codigo,
       req_codigo,
       exa_codigo,
       mco_codigo,
       lrr_codigo
);

create index xif1loteexternoexamereqrastreio on loteexternoexamereqrastreio
(
       fil_kodigo,
       fil_codigo,
       lex_codigo,
       lae_codigo,
       req_codigo,
       exa_codigo,
       mco_codigo
);


alter table loteexternoexamereqrastreio
       add primary key (fil_kodigo, fil_codigo, lex_codigo, 
              lae_codigo, req_codigo, exa_codigo, mco_codigo, 
              lrr_codigo);


--drop function fn_ti_loteexternoexamereqrastreio() cascade;

create function fn_ti_loteexternoexamereqrastreio() returns opaque as '
declare
	numrows integer;
	tmp varchar;									 
begin
    select count(*) into numrows
      from loteexternoexamerequisicao
      where
        new.fil_kodigo = loteexternoexamerequisicao.fil_kodigo and
        new.fil_codigo = loteexternoexamerequisicao.fil_codigo and
        new.lex_codigo = loteexternoexamerequisicao.lex_codigo and
        new.lae_codigo = loteexternoexamerequisicao.lae_codigo and
        new.req_codigo = loteexternoexamerequisicao.req_codigo and
        new.exa_codigo = loteexternoexamerequisicao.exa_codigo and
        new.mco_codigo = loteexternoexamerequisicao.mco_codigo;
    tmp:=''loteexternoexamerequisicao|'';
    
      tmp:=tmp || ''fil_kodigo='' || new.fil_kodigo || ''|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''lex_codigo='' || new.lex_codigo || ''|'';
    
      tmp:=tmp || ''lae_codigo='' || new.lae_codigo || ''|'';
    
      tmp:=tmp || ''req_codigo='' || new.req_codigo || ''|'';
    
      tmp:=tmp || ''exa_codigo='' || new.exa_codigo || ''|'';
    
      tmp:=tmp || ''mco_codigo='' || new.mco_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela loteexternoexamerequisicao. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;


return new;
end;'
language 'plpgsql';
					
create trigger ti_loteexternoexamereqrastreio
after insert on loteexternoexamereqrastreio
for each row execute procedure fn_ti_loteexternoexamereqrastreio();


--drop function fn_tu_loteexternoexamereqrastreio() cascade;

create function fn_tu_loteexternoexamereqrastreio() returns opaque as '
declare
	numrows integer;
        tmp varchar;										 
begin
  select count(*) into numrows
    from loteexternoexamerequisicao
    where
      new.fil_kodigo = loteexternoexamerequisicao.fil_kodigo and
      new.fil_codigo = loteexternoexamerequisicao.fil_codigo and
      new.lex_codigo = loteexternoexamerequisicao.lex_codigo and
      new.lae_codigo = loteexternoexamerequisicao.lae_codigo and
      new.req_codigo = loteexternoexamerequisicao.req_codigo and
      new.exa_codigo = loteexternoexamerequisicao.exa_codigo and
      new.mco_codigo = loteexternoexamerequisicao.mco_codigo;
    tmp:=''loteexternoexamerequisicao|'';
    
      tmp:=tmp || ''fil_kodigo='' || new.fil_kodigo || ''|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''lex_codigo='' || new.lex_codigo || ''|'';
    
      tmp:=tmp || ''lae_codigo='' || new.lae_codigo || ''|'';
    
      tmp:=tmp || ''req_codigo='' || new.req_codigo || ''|'';
    
      tmp:=tmp || ''exa_codigo='' || new.exa_codigo || ''|'';
    
      tmp:=tmp || ''mco_codigo='' || new.mco_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela loteexternoexamerequisicao. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;


return old;
end;'
language 'plpgsql';
					
create trigger tu_loteexternoexamereqrastreio
after update on loteexternoexamereqrastreio
for each row execute procedure fn_tu_loteexternoexamereqrastreio();


drop function fn_td_loteexternoexamerequisicao() cascade;

create function fn_td_loteexternoexamerequisicao() returns opaque as '
declare
	numrows integer;										 
begin
    delete from loteexternoexamereqrastreio
      where
        loteexternoexamereqrastreio.fil_kodigo = old.fil_kodigo and
        loteexternoexamereqrastreio.fil_codigo = old.fil_codigo and
        loteexternoexamereqrastreio.lex_codigo = old.lex_codigo and
        loteexternoexamereqrastreio.lae_codigo = old.lae_codigo and
        loteexternoexamereqrastreio.req_codigo = old.req_codigo and
        loteexternoexamereqrastreio.exa_codigo = old.exa_codigo and
        loteexternoexamereqrastreio.mco_codigo = old.mco_codigo;

    delete from loteexternoexamerequisicaodesm
      where
        loteexternoexamerequisicaodesm.fil_kodigo = old.fil_kodigo and
        loteexternoexamerequisicaodesm.fil_codigo = old.fil_codigo and
        loteexternoexamerequisicaodesm.lex_codigo = old.lex_codigo and
        loteexternoexamerequisicaodesm.lae_codigo = old.lae_codigo and
        loteexternoexamerequisicaodesm.req_codigo = old.req_codigo and
        loteexternoexamerequisicaodesm.exa_codigo = old.exa_codigo and
        loteexternoexamerequisicaodesm.mco_codigo = old.mco_codigo;

    delete from loteexternoexamerequisicaorecip
      where
        loteexternoexamerequisicaorecip.fil_kodigo = old.fil_kodigo and
        loteexternoexamerequisicaorecip.fil_codigo = old.fil_codigo and
        loteexternoexamerequisicaorecip.lex_codigo = old.lex_codigo and
        loteexternoexamerequisicaorecip.lae_codigo = old.lae_codigo and
        loteexternoexamerequisicaorecip.req_codigo = old.req_codigo and
        loteexternoexamerequisicaorecip.exa_codigo = old.exa_codigo and
        loteexternoexamerequisicaorecip.mco_codigo = old.mco_codigo;


return old;
end;'
language 'plpgsql';
					
create trigger td_loteexternoexamerequisicao
after delete on loteexternoexamerequisicao
for each row execute procedure fn_td_loteexternoexamerequisicao();


drop function fn_ti_loteexternoexamerequisicao() cascade;

create function fn_ti_loteexternoexamerequisicao() returns opaque as '
declare
	numrows integer;
	tmp varchar;									 
begin
    select count(*) into numrows
      from examerequisicao
      where
        new.fil_kodigo = examerequisicao.fil_codigo and
        new.exa_codigo = examerequisicao.exa_codigo and
        new.mco_codigo = examerequisicao.mco_codigo and
        new.req_codigo = examerequisicao.req_codigo;
    tmp:=''examerequisicao|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_kodigo || ''|'';
    
      tmp:=tmp || ''exa_codigo='' || new.exa_codigo || ''|'';
    
      tmp:=tmp || ''mco_codigo='' || new.mco_codigo || ''|'';
    
      tmp:=tmp || ''req_codigo='' || new.req_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela examerequisicao. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;

    select count(*) into numrows
      from loteexternorequisicao
      where
        new.fil_codigo = loteexternorequisicao.fil_codigo and
        new.fil_kodigo = loteexternorequisicao.fil_kodigo and
        new.lex_codigo = loteexternorequisicao.lex_codigo and
        new.lae_codigo = loteexternorequisicao.lae_codigo and
        new.req_codigo = loteexternorequisicao.req_codigo;
    tmp:=''loteexternorequisicao|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''fil_kodigo='' || new.fil_kodigo || ''|'';
    
      tmp:=tmp || ''lex_codigo='' || new.lex_codigo || ''|'';
    
      tmp:=tmp || ''lae_codigo='' || new.lae_codigo || ''|'';
    
      tmp:=tmp || ''req_codigo='' || new.req_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela loteexternorequisicao. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;


return new;
end;'
language 'plpgsql';
					
create trigger ti_loteexternoexamerequisicao
after insert on loteexternoexamerequisicao
for each row execute procedure fn_ti_loteexternoexamerequisicao();


drop function fn_tu_loteexternoexamerequisicao() cascade;

create function fn_tu_loteexternoexamerequisicao() returns opaque as '
declare
	numrows integer;
        tmp varchar;										 
begin
  if
    (old.fil_kodigo <> new.fil_kodigo or 
     old.fil_codigo <> new.fil_codigo or 
     old.lex_codigo <> new.lex_codigo or 
     old.lae_codigo <> new.lae_codigo or 
     old.req_codigo <> new.req_codigo or 
     old.exa_codigo <> new.exa_codigo or 
     old.mco_codigo <> new.mco_codigo) then
    select count(*)
      from loteexternoexamereqrastreio
      where
        loteexternoexamereqrastreio.fil_kodigo = old.fil_kodigo and
        loteexternoexamereqrastreio.fil_codigo = old.fil_codigo and
        loteexternoexamereqrastreio.lex_codigo = old.lex_codigo and
        loteexternoexamereqrastreio.lae_codigo = old.lae_codigo and
        loteexternoexamereqrastreio.req_codigo = old.req_codigo and
        loteexternoexamereqrastreio.exa_codigo = old.exa_codigo and
        loteexternoexamereqrastreio.mco_codigo = old.mco_codigo into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela loteexternoexamereqrastreio.'';
    end if;
  end if;
  if
    (old.fil_kodigo <> new.fil_kodigo or 
     old.fil_codigo <> new.fil_codigo or 
     old.lex_codigo <> new.lex_codigo or 
     old.lae_codigo <> new.lae_codigo or 
     old.req_codigo <> new.req_codigo or 
     old.exa_codigo <> new.exa_codigo or 
     old.mco_codigo <> new.mco_codigo) then
    select count(*)
      from loteexternoexamerequisicaodesm
      where
        loteexternoexamerequisicaodesm.fil_kodigo = old.fil_kodigo and
        loteexternoexamerequisicaodesm.fil_codigo = old.fil_codigo and
        loteexternoexamerequisicaodesm.lex_codigo = old.lex_codigo and
        loteexternoexamerequisicaodesm.lae_codigo = old.lae_codigo and
        loteexternoexamerequisicaodesm.req_codigo = old.req_codigo and
        loteexternoexamerequisicaodesm.exa_codigo = old.exa_codigo and
        loteexternoexamerequisicaodesm.mco_codigo = old.mco_codigo into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela loteexternoexamerequisicaodesm.'';
    end if;
  end if;
  if
    (old.fil_kodigo <> new.fil_kodigo or 
     old.fil_codigo <> new.fil_codigo or 
     old.lex_codigo <> new.lex_codigo or 
     old.lae_codigo <> new.lae_codigo or 
     old.req_codigo <> new.req_codigo or 
     old.exa_codigo <> new.exa_codigo or 
     old.mco_codigo <> new.mco_codigo) then
    select count(*)
      from loteexternoexamerequisicaorecip
      where
        loteexternoexamerequisicaorecip.fil_kodigo = old.fil_kodigo and
        loteexternoexamerequisicaorecip.fil_codigo = old.fil_codigo and
        loteexternoexamerequisicaorecip.lex_codigo = old.lex_codigo and
        loteexternoexamerequisicaorecip.lae_codigo = old.lae_codigo and
        loteexternoexamerequisicaorecip.req_codigo = old.req_codigo and
        loteexternoexamerequisicaorecip.exa_codigo = old.exa_codigo and
        loteexternoexamerequisicaorecip.mco_codigo = old.mco_codigo into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela loteexternoexamerequisicaorecip.'';
    end if;
  end if;
  select count(*) into numrows
    from examerequisicao
    where
      new.fil_kodigo = examerequisicao.fil_codigo and
      new.exa_codigo = examerequisicao.exa_codigo and
      new.mco_codigo = examerequisicao.mco_codigo and
      new.req_codigo = examerequisicao.req_codigo;
    tmp:=''examerequisicao|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_kodigo || ''|'';
    
      tmp:=tmp || ''exa_codigo='' || new.exa_codigo || ''|'';
    
      tmp:=tmp || ''mco_codigo='' || new.mco_codigo || ''|'';
    
      tmp:=tmp || ''req_codigo='' || new.req_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela examerequisicao. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;

  select count(*) into numrows
    from loteexternorequisicao
    where
      new.fil_codigo = loteexternorequisicao.fil_codigo and
      new.fil_kodigo = loteexternorequisicao.fil_kodigo and
      new.lex_codigo = loteexternorequisicao.lex_codigo and
      new.lae_codigo = loteexternorequisicao.lae_codigo and
      new.req_codigo = loteexternorequisicao.req_codigo;
    tmp:=''loteexternorequisicao|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''fil_kodigo='' || new.fil_kodigo || ''|'';
    
      tmp:=tmp || ''lex_codigo='' || new.lex_codigo || ''|'';
    
      tmp:=tmp || ''lae_codigo='' || new.lae_codigo || ''|'';
    
      tmp:=tmp || ''req_codigo='' || new.req_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela loteexternorequisicao. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;


return old;
end;'
language 'plpgsql';
					
create trigger tu_loteexternoexamerequisicao
after update on loteexternoexamerequisicao
for each row execute procedure fn_tu_loteexternoexamerequisicao();


drop function fn_td_loteexternorequisicao() cascade;

create function fn_td_loteexternorequisicao() returns opaque as '
declare
	numrows integer;										 
begin
    delete from loteexternoexamerequisicao
      where
        loteexternoexamerequisicao.fil_codigo = old.fil_codigo and
        loteexternoexamerequisicao.fil_kodigo = old.fil_kodigo and
        loteexternoexamerequisicao.lex_codigo = old.lex_codigo and
        loteexternoexamerequisicao.lae_codigo = old.lae_codigo and
        loteexternoexamerequisicao.req_codigo = old.req_codigo;


return old;
end;'
language 'plpgsql';
					
create trigger td_loteexternorequisicao
after delete on loteexternorequisicao
for each row execute procedure fn_td_loteexternorequisicao();


drop function fn_ti_loteexternorequisicao() cascade;

create function fn_ti_loteexternorequisicao() returns opaque as '
declare
	numrows integer;
	tmp varchar;									 
begin
    select count(*) into numrows
      from usuario
      where
        new.usr_codestorno = usuario.usr_codigo;
    tmp:=''usuario|'';
    
      tmp:=tmp || ''usr_codigo='' || new.usr_codestorno || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      new.usr_codestorno is not null and
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela usuario. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;

    select count(*) into numrows
      from requisicao
      where
        new.fil_kodigo = requisicao.fil_codigo and
        new.req_codigo = requisicao.req_codigo;
    tmp:=''requisicao|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_kodigo || ''|'';
    
      tmp:=tmp || ''req_codigo='' || new.req_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela requisicao. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;

    select count(*) into numrows
      from loteexterno
      where
        new.fil_codigo = loteexterno.fil_codigo and
        new.lae_codigo = loteexterno.lae_codigo and
        new.lex_codigo = loteexterno.lex_codigo;
    tmp:=''loteexterno|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''lae_codigo='' || new.lae_codigo || ''|'';
    
      tmp:=tmp || ''lex_codigo='' || new.lex_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);

    if (
      
      numrows = 0
    ) then
      raise exception ''o registro nao pode ser inserido, pois um valor informado nao existe na tabela loteexterno. chave para consulta:[%]'',tmp using errcode=upper(''ifkne'');
    end if;


return new;
end;'
language 'plpgsql';
					
create trigger ti_loteexternorequisicao
after insert on loteexternorequisicao
for each row execute procedure fn_ti_loteexternorequisicao();


drop function fn_tu_loteexternorequisicao() cascade;

create function fn_tu_loteexternorequisicao() returns opaque as '
declare
	numrows integer;
        tmp varchar;										 
begin
  if
    (old.fil_codigo <> new.fil_codigo or 
     old.fil_kodigo <> new.fil_kodigo or 
     old.lex_codigo <> new.lex_codigo or 
     old.lae_codigo <> new.lae_codigo or 
     old.req_codigo <> new.req_codigo) then
    select count(*)
      from loteexternoexamerequisicao
      where
        loteexternoexamerequisicao.fil_codigo = old.fil_codigo and
        loteexternoexamerequisicao.fil_kodigo = old.fil_kodigo and
        loteexternoexamerequisicao.lex_codigo = old.lex_codigo and
        loteexternoexamerequisicao.lae_codigo = old.lae_codigo and
        loteexternoexamerequisicao.req_codigo = old.req_codigo into numrows;
    if (numrows > 0) then
     raise exception ''o registro nao pode ser alterado, pois um valor esta sendo utilizado na tabela loteexternoexamerequisicao.'';
    end if;
  end if;
  select count(*) into numrows
    from usuario
    where
      new.usr_codestorno = usuario.usr_codigo;
    tmp:=''usuario|'';
    
      tmp:=tmp || ''usr_codigo='' || new.usr_codestorno || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    new.usr_codestorno is not null and
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela usuario. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;

  select count(*) into numrows
    from requisicao
    where
      new.fil_kodigo = requisicao.fil_codigo and
      new.req_codigo = requisicao.req_codigo;
    tmp:=''requisicao|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_kodigo || ''|'';
    
      tmp:=tmp || ''req_codigo='' || new.req_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela requisicao. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;

  select count(*) into numrows
    from loteexterno
    where
      new.fil_codigo = loteexterno.fil_codigo and
      new.lae_codigo = loteexterno.lae_codigo and
      new.lex_codigo = loteexterno.lex_codigo;
    tmp:=''loteexterno|'';
    
      tmp:=tmp || ''fil_codigo='' || new.fil_codigo || ''|'';
    
      tmp:=tmp || ''lae_codigo='' || new.lae_codigo || ''|'';
    
      tmp:=tmp || ''lex_codigo='' || new.lex_codigo || ''|'';
    
    tmp:=substring(tmp,1,length(tmp)-1);
  if (
    
    numrows = 0
  ) then
    raise exception ''o registro nao pode ser alterado, pois um valor nao existe na tabela loteexterno. chave para consulta:[%]'',tmp using errcode=upper(''ufkne'');
  end if;


return old;
end;'
language 'plpgsql';
					
create trigger tu_loteexternorequisicao
after update on loteexternorequisicao
for each row execute procedure fn_tu_loteexternorequisicao();



insert into atualizacoes(atu_nome) values ('v20010003_130220.sql');