CREATE OR REPLACE PROCEDURE empleados_emt (empresaw varchar2,categoriaw varchar2,nombrew varchar2,seccionw number, apellido1w varchar2,antiguedadw number,apellido2w varchar2) AS consulta varchar2(2048); consulta2 varchar2(2048); control number; seq number; BEGIN htp.print(' '); consulta:=null; if empresaw is not null then consulta:='and t.num_ident like '||chr(39)||upper(empresaw)||chr(39); end if; if categoriaw is not null then consulta:=consulta||' and ct.denom_cat like '||chr(39)||'%'||upper(categoriaw)||'%'||chr(39); end if; if nombrew is not null then consulta:=consulta||' and t.nombre like '||chr(39)||'%'||upper(nombrew)||'%'||chr(39); end if; if seccionw is not null then consulta:=consulta||'and c.num_escalafon = '||seccionw; end if; if apellido1w is not null then consulta:=consulta||' and t.apellido_1 like '||chr(39)||'%'||upper(apellido1w)||'%'||chr(39); end if; if apellido2w is not null then consulta:=consulta||' and t.apellido_2 like '||chr(39)||'%'||upper(apellido2w)||'%'||chr(39); end if; if antiguedadw is not null then consulta:=consulta||' and (sysdate - c.fec_antig) / 365 = '||antiguedadw; end if; execute immediate 'drop table empleadosportal'; execute immediate 'create table empleadosportal as select t.num_ident, t.nombre, t.apellido_1, t.apellido_2,t.seq_tarjeta Tarjeta, (t.num_dni||t.cnt_dni) DNI, c.cod_estr Estruc, ct.denom_cat Categoria, c.num_escalafon Escalafon, (sysdate - c.fec_antig) / 365 años from trabajador@servicios t, contratos@servicios c, categoria@servicios ct where t.num_empresa = 1 and c.num_seq_trab = t.num_seq_trab and nvl(c.fec_fin_val,to_date('||chr(39)||'31122100'||chr(39)||','||chr(39)||'ddmmyyyy'||chr(39)||')) = '||chr(39)||'31122100'||chr(39)||' and nvl(c.fecha_baja,to_date('||chr(39)||'31122100'||chr(39)||','||chr(39)||'ddmmyyyy'||chr(39)||')) = '||chr(39)||'31122100'||chr(39)||' and ct.num_conv = t.num_empresa and ct.grupo_cat = c.grupo_cat and ct.orden_cat = c.orden_cat '||consulta||' order by t.num_ident'; select nvl(count(*),0) into control from empleadosportal; if nvl(control,0) = 0 then htp.print(''); else select min(num_ident) into seq from empleadosportal; htp.print(''); end if; end; /