begin
CTX_DDL.CREATE_PREFERENCE ('PERFIL_DATASTORE', 'MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('PERFIL_DATASTORE', 'COLUMNS',
'ONP_DESCRIPCION,OFT_LOGROS');
end;
/
begin
CTX_DDL.CREATE_PREFERENCE ('LEXER_SINTILDES', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('LEXER_SINTILDES', 'BASE_LETTER', 'YES');
end;
/
begin
ctx_ddl.create_preference('DEMANDA_DATASTORE', 'multi_column_datastore');
ctx_ddl.set_attribute('DEMANDA_DATASTORE', 'columns',
'DEM_HABILIDADES,DEM_COMPETENCIAS,DEM_FUNCIONES_ALT');
END;
/
--CON UNA TABLA DE 1400299 REGISTROS Inicio 5:24 Fin 5:42
create index ONP_DESCRIPCION_TEXT_IDX on OFT_NOTAS_PERFIL(ONP_DESCRIPCION)
indextype is ctxsys.context
parameters('DATASTORE PERFIL_DATASTORE SYNC (ON COMMIT) LEXER LEXER_SINTILDES');
CREATE INDEX DEMANDA_TEXT_IDX ON demanda(dem_habilidades)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('DATASTORE DEMANDA_DATASTORE SYNC (ON COMMIT) LEXER LEXER_SINTILDES');
CREATE INDEX TITULO_NOMBRE_TEXT_IDX ON titulo(tit_nombre)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('SYNC (ON COMMIT) LEXER LEXER_SINTILDES');
CREATE INDEX INDICE_NOMBRE_TEXT_IDX ON indice(ind_nombre)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('SYNC (ON COMMIT) LEXER LEXER_SINTILDES')
--CON UNA TABLA DE 1445695 REGISTROS Inicio 5:44 Fin 5:47
CREATE INDEX OEX_FUNCIONES_TEXT_IDX ON OFT_EXPERIENCIA (OEX_FUNCIONES_ALT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('SYNC (ON COMMIT) LEXER LEXER_SINTILDES');
alter user spe default tablespace USERS;
USO DE LOS INDICES EN PROCEDURE
CREATE OR REPLACE PROCEDURE "SPE"."TOKENIZER" ( iStart IN NUMBER, sPattern in VARCHAR2, sBuffer in VARCHAR2, sResult OUT VARCHAR2, iNextPos OUT NUMBER) IS
nPos1 number;
nPos2 number;
BEGIN
nPos1 := Instr (sBuffer ,sPattern ,iStart);
IF nPos1 = 0 then
sResult := NULL ;
ELSE
nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
IF nPos2 = 0 then
sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
iNextPos := nPos2;
ELSE
sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
iNextPos := nPos2;
END IF;
END IF;
END tokenizer ;
/
CREATE OR REPLACE PACKAGE "BUSQUEDAS_PKG" IS
type lista is table of varchar2(12) index by binary_integer;
-- type lista_cno is varray(500) of varchar2(6);
palabras lista;
nro_palabras number;
type rec_oft is record(
oftId cz_oferta.oft_id%type,
porcentaje variable_cruce.vac_porcentaje%type);
type lis_oft is table of rec_oft index by binary_integer; -- lista para las ofertas que cumplen el perfil
function busquedaDemandanteEspecifica(variablesBusqueda reg_var, palabraClave varchar2) return lista_ofertas;
function procesarBusquedaDemandante(ofertas lista_ofertas, variablesBusqueda reg_var) return lis_oft;
procedure qsort(arr in out lis_oft, lo in INTEGER, hi in INTEGER );
FUNCTION funcion_geo(par_depto cz_oferta.dpt_id_residencia%type,
par_municipio cz_oferta.mpo_id_residencia%type,
par_zona cz_oferta.zon_id_residencia%type,
par_barrio cz_oferta.bar_id_residencia%type,
oftId cz_oferta.oft_id%type) RETURN NUMBER;
FUNCTION funcion_etd(oftId oferta.oft_id%TYPE, titulo varchar2) RETURN NUMBER;
FUNCTION funcion_exp(oftId oferta.oft_id%TYPE, experiencia varchar2) RETURN NUMBER;
FUNCTION funcion_sue(oftId oferta.oft_id%TYPE, sueldo number) RETURN NUMBER;
FUNCTION funcion_pes(oftId oferta.oft_id%TYPE, programas_especiales varchar2) RETURN NUMBER;
function obtenerCaracteres(cadena varchar2) return varchar2;
function esNumero(variable_a_averiguar varchar2) return number;
function esLetra(variable_a_averiguar varchar2) return number;
function busquedaDemandanteSencilla(p_palabraClave varchar2) return lista_ofertas;
function normalizarCadena(p_palabraClave varchar2) return varchar2;
function obtenerCargoOferente(p_oferente number, p_palabraClave varchar2) return number;
END BUSQUEDAS_PKG;
/
CREATE OR REPLACE PACKAGE BODY BUSQUEDAS_PKG IS
lista_oft_busqueda cruce_pkg.lis_oft_sen;
lista_oft_id lista_ofertas := lista_ofertas(null);
ofertas_encontradas lista_ofertas;
ofertas_procesadas lis_oft;
function busquedaDemandanteEspecifica(variablesBusqueda reg_var, palabraClave varchar2) return lista_ofertas is
v_max_reg number;
BEGIN
ofertas_encontradas := busquedaDemandanteSencilla(palabraClave);
if (ofertas_encontradas.count > 0) then
ofertas_procesadas := procesarBusquedaDemandante(ofertas_encontradas, variablesBusqueda);
if (ofertas_procesadas.count > 0) then
qsort(ofertas_procesadas, 1, ofertas_procesadas.count);
for i in ofertas_procesadas.first..ofertas_procesadas.last loop
lista_oft_id.extend;
lista_oft_id(lista_oft_id.last) := ofertas_procesadas(i).oftId;
end loop;
end if;
end if;
RETURN lista_oft_id;
END busquedaDemandanteEspecifica;
procedure qsort(arr in out lis_oft, lo in INTEGER, hi in INTEGER ) is
i INTEGER := lo;
j INTEGER := hi;
x NUMBER := arr((lo+hi)/2).porcentaje;
tmp rec_oft;
begin
LOOP
WHILE (arr(i).porcentaje < x) LOOP
i := i + 1;
END LOOP;
WHILE arr(j).porcentaje > x LOOP
j := j - 1;
END LOOP;
IF i <= j THEN
tmp := arr(i);
arr(i) := arr(j);
arr(j) := tmp;
i := i + 1;
j := j - 1;
END IF;
EXIT WHEN i > j;
END LOOP;
IF lo < j THEN
qsort(arr, lo, j);
END IF;
IF i < hi THEN
qsort(arr, i, hi);
END IF;
end qsort;
function procesarBusquedaDemandante(ofertas lista_ofertas, variablesBusqueda reg_var) return lis_oft is
par_ubicacion varchar2(5000);
par_depto number;
par_municipio number;
par_zona number;
par_barrio number;
par_estudio varchar2(100);
par_experiencia varchar2(100);
par_sueldo number;
par_pes varchar(100);
acumulado number(5,2):=0;
bandera boolean;
valor_tmp number(5,2);
cont_ubicacion number(1) := 1;
sepr varchar2(1);
sbuf clob;
sres clob;
pos number;
istart number;
strPalabra clob;
begin
for i in ofertas.first .. ofertas.last loop
bandera := TRUE;
acumulado := 0;
for b in 1 .. variablesBusqueda.count loop
if bandera = true then
CASE substr(variablesBusqueda(b),1,instr(variablesBusqueda(b),'|')-1)
WHEN 'GEO' THEN
par_ubicacion := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
sepr := ';';
sbuf := sepr || par_ubicacion;
istart := 1;
tokenizer (istart,sepr,sbuf,sres,pos);
strPalabra := upper(sres);
par_depto := to_number(strPalabra);
while (pos <> 0) loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
strPalabra := upper(sres);
case cont_ubicacion
when 1 then par_municipio := to_number(strPalabra);
cont_ubicacion:=2;
when 2 then par_zona := to_number(strPalabra);
cont_ubicacion:=3;
when 3 then par_barrio := to_number(strPalabra);
end case;
end loop;
valor_tmp := funcion_geo(par_depto, par_municipio, par_zona, par_barrio, ofertas(i));
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
/*WHEN 'ETD' THEN
par_estudio :=substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_estudio is not null then
valor_tmp := funcion_etd(ofertas(i), par_estudio);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;*/
WHEN 'EXP' THEN
par_experiencia := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
par_estudio :=substr(par_experiencia,1,instr(par_experiencia,';')-1);
if par_experiencia is not null then
valor_tmp := funcion_exp(ofertas(i), par_experiencia);
if valor_tmp > 0 then
acumulado:= acumulado + valor_tmp;
else
--es el caso de cuando viene el parametro ETD pero no excluyente sino encuenta exp busca estudios
if par_estudio is not null then
valor_tmp := funcion_etd(ofertas(i), par_estudio);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
end if;
end if;
WHEN 'PES' THEN
par_pes := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_pes is not null then
valor_tmp := funcion_pes(ofertas(i), par_pes);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
ELSE NULL;
END CASE;
end if;
end loop;
if acumulado > 0 and bandera = true then
ofertas_procesadas(ofertas_procesadas.count+1).oftid := ofertas(i);
ofertas_procesadas(ofertas_procesadas.count+1).porcentaje := acumulado;
end if;
end loop;
return ofertas_procesadas;
end procesarBusquedaDemandante;
FUNCTION funcion_geo(par_depto cz_oferta.dpt_id_residencia%type,
par_municipio cz_oferta.mpo_id_residencia%type,
par_zona cz_oferta.zon_id_residencia%type,
par_barrio cz_oferta.bar_id_residencia%type,
oftId cz_oferta.oft_id%type) RETURN NUMBER IS
porcentaje PLS_INTEGER:=0;
var_porcentaje number(5,2);
o_dpt_id cz_demanda.dpt_id_residencia%type;
o_mpo_id cz_demanda.mpo_id_residencia%type;
o_zon_id cz_demanda.zon_id_residencia%type;
o_bar_id cz_demanda.bar_id_residencia%type;
BEGIN
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='GEO';
if (par_municipio is null) then
select dpt_id_residencia
into o_dpt_id
from cz_oferta
where oft_id = oftId;
if (o_dpt_id = par_depto) then
porcentaje := var_porcentaje;
end if;
elsif (par_zona is null) then
select dpt_id_residencia, mpo_id_residencia
into o_dpt_id, o_mpo_id
from cz_oferta
where oft_id = oftId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio) then
porcentaje := var_porcentaje;
end if;
elsif (par_barrio is null) then
select dpt_id_residencia, mpo_id_residencia, zon_id_residencia
into o_dpt_id, o_mpo_id, o_zon_id
from cz_oferta
where oft_id = oftId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio and o_zon_id = par_zona) then
porcentaje := var_porcentaje;
end if;
else
select dpt_id_residencia, mpo_id_residencia, zon_id_residencia, bar_id_residencia
into o_dpt_id, o_mpo_id, o_zon_id, o_bar_id
from cz_oferta
where oft_id = oftId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio and o_zon_id = par_zona and o_bar_id = par_barrio) then
porcentaje := var_porcentaje;
end if;
end if;
RETURN porcentaje;
END funcion_geo;
FUNCTION funcion_etd(oftId oferta.oft_id%TYPE, titulo varchar2) RETURN NUMBER IS
lista_definitiva lista_ofertas := lista_ofertas();
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
cursor c_titulos is select t.tit_id
from titulo t
where contains(tit_nombre,
'
--order by score(1) DESC;
TYPE cur_typ IS REF CURSOR;
c_estudios cur_typ;
v_titulos_qry varchar(32767) := '';
v_estudios_qry varchar(32767) := '';
encontro number;
begin
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='ETD';
for reg_titulos in c_titulos loop
v_titulos_qry := v_titulos_qry || reg_titulos.tit_id || ',';
end loop;
if (length(v_titulos_qry) > 0 ) then
v_titulos_qry := substr(v_titulos_qry,1,length(v_titulos_qry)-1) || ')';
v_titulos_qry := 'SELECT COUNT(9) FROM CZ_OFT_ETD_FORM WHERE OFT_ID = ' || oftId || ' AND TIT_ID IN (' || v_titulos_qry;
OPEN c_estudios FOR v_titulos_qry;
LOOP
FETCH c_estudios INTO encontro;
EXIT WHEN c_estudios%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_estudios;
end if;
RETURN porcentaje;
END funcion_etd;
FUNCTION funcion_exp(oftId oferta.oft_id%TYPE, experiencia varchar2) RETURN NUMBER IS
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
indice_exp varchar2(1000);
tiempo_exp number;
cursor c_experiencias is select i.ind_id
from indice i
where contains(ind_nombre,
'
--order by score(1) DESC;
TYPE cur_typ IS REF CURSOR;
c_ofertas cur_typ;
v_experiencias_qry varchar(32767) := '';
encontro number;
begin
indice_exp := substr(experiencia, 1, instr(experiencia, ';')-1);
tiempo_exp := nvl(to_number(substr(experiencia, instr(experiencia, ';')+1, length(experiencia))),0);
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='IND';
for reg_experiencias in c_experiencias loop
v_experiencias_qry := v_experiencias_qry || reg_experiencias.ind_id || ',';
end loop;
if (length(v_experiencias_qry) > 0 ) then
v_experiencias_qry := substr(v_experiencias_qry,1,length(v_experiencias_qry)-1) || ')';
v_experiencias_qry := 'SELECT COUNT(9) FROM CZ_OFT_EXP_IND WHERE OFT_ID = ' || oftId || ' AND COE_MESES>= ' || tiempo_exp || ' AND IND_ID IN (' || v_experiencias_qry;
OPEN c_ofertas FOR v_experiencias_qry;
LOOP
FETCH c_ofertas INTO encontro;
EXIT WHEN c_ofertas%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_ofertas;
end if;
RETURN porcentaje;
END funcion_exp;
FUNCTION funcion_sue(oftId oferta.oft_id%TYPE, sueldo number) RETURN NUMBER IS
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
sueldo_cumple number;
begin
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='SUE';
select count(9)
into sueldo_cumple
from oferta where oft_sueldo_aceptaria <= sueldo and oft_id=oftId;
if sueldo_cumple > 0 then
porcentaje := var_porcentaje;
end if;
RETURN porcentaje;
END funcion_sue;
FUNCTION funcion_pes(oftId oferta.oft_id%TYPE, programas_especiales varchar2) RETURN NUMBER IS
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
encontro number;
pes varchar2(32767);
v_pes_qry varchar(32767) := '';
TYPE cur_typ IS REF CURSOR;
c_ofertas cur_typ;
begin
pes := replace (programas_especiales, ';', ',');
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='PES';
v_pes_qry := 'SELECT COUNT(9) FROM CZ_OFTXPES WHERE OFT_ID = ' || oftId || ' AND PES_ID IN (' || pes || ')';
OPEN c_ofertas FOR v_pes_qry;
-- Fetch rows from result set one at a time:
LOOP
FETCH c_ofertas INTO encontro;
EXIT WHEN c_ofertas%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_ofertas;
dbms_output.put_line(porcentaje);
RETURN porcentaje;
END funcion_pes;
function obtenerCaracteres(cadena varchar2) return varchar2 IS
retorno varchar2(32767);
len number(6);
c varchar2(1);
BEGIN
len := length(cadena);
for i in 1 .. len loop
c := substr(cadena,i,1);
if c = '+' or c = '_' or c = '-' or c = '%' then
retorno := concat(retorno,' ');
--elsif(c = ' ' || esNumero(c) = 1 || esLetra(c)) = 1 then
elsif c = ' ' or esNumero(c) = 1 or esLetra(c) = 1 then
retorno := concat(retorno,c);
end if;
end loop;
return retorno;
END obtenerCaracteres;
function esNumero(variable_a_averiguar varchar2) return number IS
esNumero NUMBER;
BEGIN
esNumero := TO_NUMBER(variable_a_averiguar);
esNumero :=1;
-- Sino es numerico entonces genera una exception
EXCEPTION
WHEN VALUE_ERROR THEN
esNumero := 0;
return esNumero;
end esNumero;
function esLetra(variable_a_averiguar varchar2) return number IS
esLetra NUMBER;
BEGIN
for i in 1..length(variable_a_averiguar) loop
if upper(substr(variable_a_averiguar,i,1))
IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','Ñ','O','P','Q','R','S','T','U','V','W','X','Y','Z') THEN
esLetra := 1;
else
esLetra := 0;
end if;
end loop;
return esLetra;
end esLetra;
function normalizarCadena(p_palabraClave varchar2) return varchar2 is
p_cadenaNormalizada varchar2(32767);
len number;
cad varchar2(1);
--
stbRetorno clob;
strPalabra clob;
incluirPrimeraVez number(1);
incluir number(1);
sepr varchar2(1);
sbuf clob;
sres clob;
pos number;
istart number;
palabrasNoIncluir lista;
BEGIN
len := length(p_palabraClave);
for i in 1 .. len loop
cad := substr(p_palabraClave,i,1);
if cad = '+' or cad = '_' or cad = '-' or cad = '%' then
p_cadenaNormalizada := concat(p_cadenaNormalizada,' ');
--elsif(c = ' ' || esNumero(c) = 1 || esLetra(c)) = 1 then
elsif cad = ' ' or esNumero(cad) = 1 or esLetra(cad) = 1 then
p_cadenaNormalizada := concat(p_cadenaNormalizada,cad);
end if;
end loop;
palabrasNoIncluir.delete;
palabrasNoIncluir(1) := 'DE';
palabrasNoIncluir(2) := 'Y';
palabrasNoIncluir(3) := '0';
palabrasNoIncluir(4) := chr(39);
palabrasNoIncluir(5) := ',';
palabrasNoIncluir(6) := 'PARA';
palabrasNoIncluir(7) := '-';
busquedas_pkg.nro_palabras := 1;
sepr := ' ';
sbuf := sepr || p_cadenaNormalizada;
istart := 1;
tokenizer (istart,sepr,sbuf,sres,pos);
strPalabra := upper(sres);
p_cadenaNormalizada := strPalabra;
while (pos <> 0) loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
strPalabra := upper(sres);
incluir := 1;
for i in palabrasNoIncluir.first..palabrasNoIncluir.last loop
if palabrasNoIncluir(i) = strPalabra then
incluir := 0;
end if;
end loop;
if incluir = 1 then
p_cadenaNormalizada := p_cadenaNormalizada || ' ' || strPalabra;
end if;
end loop;
return p_cadenaNormalizada;
end normalizarCadena;
function busquedaDemandanteSencilla(p_palabraClave varchar2) return lista_ofertas IS
lista_definitiva lista_ofertas := lista_ofertas();
v_max_reg number;
existe number;
cursor c_ofertas_per is select o.oft_id
from oft_notas_perfil o, cz_oferta cz
where contains(onp_descripcion,
'
AND o.oft_id = cz.oft_id
AND rownum < v_max_reg;
--ORDER BY score(1) DESC;
cursor c_ofertas_exp is select score(1), o.oft_id
from oft_experiencia o, cz_oferta cz
WHERE contains(oex_funciones_alt,
'
AND o.oft_id = cz.oft_id
AND rownum < v_max_reg;
--ORDER BY score(1) DESC;
begin
select to_number(par_valor) into v_max_reg from spe.parametro where par_id=147;
for reg_oferta in c_ofertas_per loop
lista_definitiva.extend;
lista_definitiva(lista_definitiva.last) := reg_oferta.oft_id;
end loop;
if lista_definitiva.count < v_max_reg then
for reg_oferta in c_ofertas_exp loop
existe := 0;
for i in lista_definitiva.first .. lista_definitiva.last loop
if lista_definitiva(i)= reg_oferta.oft_id then
existe:=1;
end if;
end loop;
if existe = 0 and lista_definitiva.count < v_max_reg and lista_definitiva.last < lista_definitiva.limit then
lista_definitiva.extend;
lista_definitiva(lista_definitiva.last) := reg_oferta.oft_id;
end if;
end loop;
end if;
return lista_definitiva;
end busquedaDemandanteSencilla;
function obtenerCargoOferente(p_oferente number, p_palabraClave varchar2) return number is
p_indice oft_experiencia.ind_id%type := 0;
indice oft_experiencia.ind_id%type;
cursor c_indices is select i.ind_id
from indice i
where contains(ind_nombre,
'
--order by score(1) DESC;
cursor c_indices_exp is select score(1), o.ind_id
from oft_experiencia o
WHERE contains(oex_funciones_alt,
'
AND o.oft_id = p_oferente
AND rownum = 1;
--ORDER BY score(1) DESC;
TYPE cur_typ IS REF CURSOR;
c_indice cur_typ;
v_indices_qry varchar(32767) := '';
begin
for reg_indices in c_indices loop
v_indices_qry := v_indices_qry || reg_indices.ind_id || ',';
end loop;
if (v_indices_qry <> null) then
v_indices_qry := substr(v_indices_qry,1,length(v_indices_qry)-1) || ')';
v_indices_qry := 'SELECT a.ind_id
FROM ( SELECT ind_id, coe_meses
FROM CZ_OFT_EXP_IND
WHERE OFT_ID = ' || p_oferente||
' AND IND_ID IN (' || v_indices_qry || ' ) a ' ||
'WHERE coe_meses = (SELECT MAX(coe_meses)
FROM ( SELECT ind_id, coe_meses
FROM CZ_OFT_EXP_IND
WHERE OFT_ID = ' || p_oferente||
' AND IND_ID IN (' || v_indices_qry || ' )))' ;
OPEN c_indice FOR v_indices_qry;
LOOP
FETCH c_indice INTO p_indice;
EXIT WHEN c_indice%NOTFOUND;
if p_indice <> null then
indice := p_indice;
end if;
END LOOP;
CLOSE c_indice;
end if;
if (indice is null) then
for reg_indice in c_indices_exp loop
indice := reg_indice.ind_id;
end loop;
end if;
if (indice is null) then
select ind_id
into indice
from cz_oft_exp_ind cz
where cz.oft_id= p_oferente
and coe_meses = (select max(coe_meses) from cz_oft_exp_ind where cz_oft_exp_ind.oft_ID = cz.oft_ID)
and rownum=1;
end if;
RETURN indice;
end obtenerCargoOferente;
END BUSQUEDAS_PKG;
/
CREATE OR REPLACE PACKAGE BUSQUEDAS_OFT_PKG AS
type lis_dem_sen is table of varchar2(12) index by binary_integer;
type rec_dem is record(
demId cz_demanda.dem_id%type,
porcentaje variable_cruce.vac_porcentaje%type);
type lis_dem is table of rec_dem index by binary_integer; -- lista para las ofertas que cumplen el perfil
function busquedaOferenteSencilla(p_palabraClave varchar2) return lista_demandas;
procedure qsort(arr in out lis_dem, lo in INTEGER, hi in INTEGER );
function busquedaOferenteEspecifica(variablesBusqueda reg_var, palabraClave varchar2) return lista_demandas;
function procesarBusquedaOferente(demandas lista_demandas, variablesBusqueda reg_var) return lis_dem;
FUNCTION funcion_geo(par_depto cz_demanda.dpt_id_residencia%type,
par_municipio cz_demanda.mpo_id_residencia%type,
par_zona cz_demanda.zon_id_residencia%type,
par_barrio cz_demanda.bar_id_residencia%type,
demId cz_demanda.dem_id%type) RETURN NUMBER;
FUNCTION funcion_etd(demId demanda.dem_id%TYPE, titulo varchar2) RETURN NUMBER;
FUNCTION funcion_exp(demId demanda.dem_id%TYPE, experiencia varchar2) RETURN NUMBER;
FUNCTION funcion_sue(demId cz_demanda.dem_id%type, sueldo varchar2) RETURN NUMBER;
FUNCTION funcion_pes(demId cz_demanda.dem_id%type, programas varchar2) RETURN NUMBER;
END BUSQUEDAS_OFT_PKG;
/
CREATE OR REPLACE PACKAGE BODY BUSQUEDAS_OFT_PKG IS
lista_dem_busqueda lis_dem_sen;
lista_dem_id lista_demandas := lista_demandas(null);
demandas_encontradas lista_demandas;
demandas_procesadas lis_dem;
function busquedaOferenteSencilla(p_palabraClave varchar2) return lista_demandas IS
lista_definitiva lista_demandas := lista_demandas();
v_max_reg number;
existe number;
cursor c_demandas1 is select cz.dem_id
from indice i, cz_demanda cz
where i.ind_id = cz.ind_id and contains(i.ind_nombre,
'
AND rownum < v_max_reg;
cursor c_demandas2 is select cz.dem_id
from demanda d, cz_demanda cz
where contains(dem_habilidades,
'
AND d.dem_id = cz.dem_id
AND rownum < v_max_reg;
--ORDER BY score(1) DESC;
--
begin
select to_number(par_valor) into v_max_reg from spe.parametro where par_id=147;
for reg_demanda in c_demandas1 loop
lista_definitiva.extend;
lista_definitiva(lista_definitiva.last) := reg_demanda.dem_id;
end loop;
--if lista_definitiva.count = 0 then
for reg_demanda2 in c_demandas2 loop
if lista_definitiva.count < v_max_reg then
lista_definitiva.extend;
lista_definitiva(lista_definitiva.last) := reg_demanda2.dem_id;
else
exit;
end if;
end loop;
dbms_output.put_line(lista_definitiva.last || ' ' || lista_definitiva.count);
return lista_definitiva;
end busquedaOferenteSencilla;
function busquedaOferenteEspecifica(variablesBusqueda reg_var, palabraClave varchar2) return lista_demandas is
v_max_reg number;
BEGIN
demandas_encontradas := busquedaOferenteSencilla(palabraClave);
if (demandas_encontradas.count > 0) then
demandas_procesadas := procesarBusquedaOferente(demandas_encontradas, variablesBusqueda);
if (demandas_procesadas.count > 0) then
qsort(demandas_procesadas, 1, demandas_procesadas.count);
for i in demandas_procesadas.first..demandas_procesadas.last loop
if length(demandas_procesadas(i).demId) > 0 then
lista_dem_id(lista_dem_id.last) := demandas_procesadas(i).demId;
lista_dem_id.extend;
end if;
end loop;
end if;
end if;
RETURN lista_dem_id;
END busquedaOferenteEspecifica;
function procesarBusquedaOferente(demandas lista_demandas, variablesBusqueda reg_var) return lis_dem is
par_ubicacion varchar2(5000);
par_depto number;
par_municipio number;
par_zona number;
par_barrio number;
par_estudio varchar2(100);
par_experiencia varchar2(100);
par_sector varchar2(100);
par_sueldo varchar2(100);
par_pes varchar(100);
acumulado number(5,2):=0;
bandera boolean;
valor_tmp number(5,2);
cont_ubicacion number(1):=1;
sepr varchar2(1);
sbuf clob;
sres clob;
pos number;
istart number;
strPalabra clob;
begin
for i in demandas.first .. demandas.last loop
bandera := TRUE;
acumulado := 0;
for b in 1 .. variablesBusqueda.count loop
if bandera = true then
CASE substr(variablesBusqueda(b),1,instr(variablesBusqueda(b),'|')-1)
WHEN 'GEO' THEN
par_ubicacion := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
sepr := ';';
sbuf := sepr || par_ubicacion;
istart := 1;
tokenizer (istart,sepr,sbuf,sres,pos);
strPalabra := upper(sres);
par_depto := to_number(strPalabra);
while (pos <> 0) loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
strPalabra := upper(sres);
case cont_ubicacion
when 1 then par_municipio := to_number(strPalabra);
cont_ubicacion:=2;
when 2 then par_zona := to_number(strPalabra);
cont_ubicacion:=3;
when 3 then par_barrio := to_number(strPalabra);
end case;
end loop;
valor_tmp := funcion_geo(par_depto, par_municipio, par_zona, par_barrio, demandas(i));
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
/*WHEN 'ETD' THEN
par_estudio :=substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_estudio is not null then
valor_tmp := funcion_etd(demandas(i), par_estudio);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;*/
WHEN 'EXP' THEN
par_experiencia := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
par_estudio :=substr(par_experiencia,1,instr(par_experiencia,';')-1);
if par_experiencia is not null then
valor_tmp := funcion_exp(demandas(i), par_experiencia);
if valor_tmp > 0 then
acumulado:= acumulado + valor_tmp;
else
--es el caso de cuando viene el parametro ETD pero no excluyente sino encuenta exp busca estudios
if par_estudio is not null then
valor_tmp := funcion_etd(demandas(i), par_estudio);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
end if;
end if;
WHEN 'SEC' THEN
/* par_sector := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_sector is not null then
valor_tmp := funcion_sec(demandas(i), par_sector);
if valor_tmp > 0 then
acumulado:= acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;*/
null;
WHEN 'SUE' THEN
par_sueldo := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_sueldo is not null then
valor_tmp := funcion_sue(demandas(i), par_sueldo);
if valor_tmp > 0 then
acumulado:= acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
WHEN 'PES' THEN
par_pes := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_pes is not null then
valor_tmp := funcion_pes(demandas(i), par_pes);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
ELSE NULL;
END CASE;
end if;
end loop;
if acumulado > 0 and bandera = true then
demandas_procesadas(demandas_procesadas.count+1).demid := demandas(i);
demandas_procesadas(demandas_procesadas.count+1).porcentaje := acumulado;
end if;
end loop;
return demandas_procesadas;
end procesarBusquedaOferente;
FUNCTION funcion_pes(demId cz_demanda.dem_id%type, programas varchar2) RETURN NUMBER IS
porcentaje PLS_INTEGER:=0;
var_porcentaje number(5,2);
prog_especiales varchar2(5000);
TYPE cur_typ IS REF CURSOR;
c_demandas cur_typ;
v_pes_qry varchar(32767) := '';
encontro number;
BEGIN
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='PES';
prog_especiales:= replace(programas,';',',');
v_pes_qry := 'SELECT COUNT(9) FROM CZ_DEMXPES WHERE DEM_ID = ' || demId || ' AND PES_ID IN (' || prog_especiales || ')';
OPEN c_demandas FOR v_pes_qry;
LOOP
FETCH c_demandas INTO encontro;
EXIT WHEN c_demandas%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_demandas;
RETURN porcentaje;
END funcion_pes;
FUNCTION funcion_geo(par_depto cz_demanda.dpt_id_residencia%type,
par_municipio cz_demanda.mpo_id_residencia%type,
par_zona cz_demanda.zon_id_residencia%type,
par_barrio cz_demanda.bar_id_residencia%type,
demId cz_demanda.dem_id%type) RETURN NUMBER IS
porcentaje PLS_INTEGER:=0;
var_porcentaje number(5,2);
o_dpt_id cz_demanda.dpt_id_residencia%type;
o_mpo_id cz_demanda.mpo_id_residencia%type;
o_zon_id cz_demanda.zon_id_residencia%type;
o_bar_id cz_demanda.bar_id_residencia%type;
BEGIN
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='GEO';
if (par_municipio is null) then
select dpt_id_residencia
into o_dpt_id
from cz_demanda
where dem_id = demId;
if (o_dpt_id = par_depto) then
porcentaje := var_porcentaje;
end if;
elsif (par_zona is null) then
select dpt_id_residencia, mpo_id_residencia
into o_dpt_id, o_mpo_id
from cz_demanda
where dem_id = demId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio) then
porcentaje := var_porcentaje;
end if;
elsif (par_barrio is null) then
select dpt_id_residencia, mpo_id_residencia, zon_id_residencia
into o_dpt_id, o_mpo_id, o_zon_id
from cz_demanda
where dem_id = demId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio and o_zon_id = par_zona) then
porcentaje := var_porcentaje;
end if;
else
select dpt_id_residencia, mpo_id_residencia, zon_id_residencia, bar_id_residencia
into o_dpt_id, o_mpo_id, o_zon_id, o_bar_id
from cz_demanda
where dem_id = demId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio and o_zon_id = par_zona and o_bar_id = par_barrio) then
porcentaje := var_porcentaje;
end if;
end if;
RETURN porcentaje;
END funcion_geo;
FUNCTION funcion_sec(demId cz_demanda.dem_id%type, sector varchar2) RETURN NUMBER IS
porcentaje PLS_INTEGER:=0;
var_porcentaje number(5,2);
d_sec_id division_ciiu.dic_id%type;
BEGIN
/*select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='SEC';
select mpo_id_residencia into o_mpo_id
from cz_demanda
where dem_id = demId;
if par_municipio is not null then
if o_mpo_id = par_municipio then
porcentaje := var_porcentaje;
end if;
else
porcentaje := var_porcentaje;
end if;
RETURN porcentaje;*/
return null;
END funcion_sec;
FUNCTION funcion_sue(demId cz_demanda.dem_id%type, sueldo varchar2) RETURN NUMBER IS
salario_min varchar2(20);
salario_max varchar2(20);
porcentaje PLS_INTEGER:=0;
existe number;
var_porcentaje number(5,2);
begin
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='SUE';
salario_min := substr(sueldo, 1, instr(sueldo, ';')-1);
if (instr(salario_min,'+') > 0) then
select count(9)
into existe
from demanda d
where d.dem_id = demId and dem_sueldo_mes > salario_min;
else
salario_max := to_number(substr(sueldo, instr(sueldo, ';')+1, length(sueldo)));
select count(9)
into existe
from demanda d
where d.dem_id = demId and dem_sueldo_mes between salario_min and salario_max;
end if;
if (existe > 0) then
porcentaje:= var_porcentaje;
end if;
return porcentaje;
END funcion_sue;
FUNCTION funcion_etd(demId demanda.dem_id%TYPE, titulo varchar2) RETURN NUMBER IS
--lista_definitiva lista_demandas := lista_demandas();
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
cursor c_titulos is select t.tit_id
from titulo t
where contains(tit_nombre,
'
--order by score(1) DESC;
TYPE cur_typ IS REF CURSOR;
c_estudios cur_typ;
v_titulos_qry varchar(32767) := '';
v_estudios_qry varchar(32767) := '';
encontro number;
begin
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='ETD';
for reg_titulos in c_titulos loop
v_titulos_qry := v_titulos_qry || reg_titulos.tit_id || ',';
end loop;
if (length(v_titulos_qry) > 0 ) then
v_titulos_qry := substr(v_titulos_qry,1,length(v_titulos_qry)-1) || ')';
v_titulos_qry := 'SELECT COUNT(9) FROM CZ_DEM_ETD_FORM WHERE DEM_ID = ' || demId || ' AND TIT_ID IN (' || v_titulos_qry;
OPEN c_estudios FOR v_titulos_qry;
LOOP
FETCH c_estudios INTO encontro;
EXIT WHEN c_estudios%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_estudios;
end if;
RETURN porcentaje;
END funcion_etd;
FUNCTION funcion_exp(demId demanda.dem_id%TYPE, experiencia varchar2) RETURN NUMBER IS
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
indice_exp varchar2(1000);
tiempo_exp number;
cursor c_experiencias is select i.ind_id
from indice i
where contains(ind_nombre,
'
--order by score(1) DESC;
TYPE cur_typ IS REF CURSOR;
c_ofertas cur_typ;
v_experiencias_qry varchar(32767) := '';
encontro number;
begin
indice_exp := substr(experiencia, 1, instr(experiencia, ';')-1);
tiempo_exp := nvl(to_number(substr(experiencia, instr(experiencia, ';')+1, length(experiencia))),0);
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='IND';
for reg_experiencias in c_experiencias loop
v_experiencias_qry := v_experiencias_qry || reg_experiencias.ind_id || ',';
end loop;
if (length(v_experiencias_qry) > 0) then
v_experiencias_qry := substr(v_experiencias_qry,1,length(v_experiencias_qry)-1) || ')';
v_experiencias_qry := 'SELECT COUNT(9) FROM CZ_DEMANDA WHERE DEM_ID = ' || demId || ' AND CDE_MESES_EXP <= ' || tiempo_exp || ' AND IND_ID IN (' || v_experiencias_qry;
OPEN c_ofertas FOR v_experiencias_qry;
LOOP
FETCH c_ofertas INTO encontro;
EXIT WHEN c_ofertas%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_ofertas;
end if;
RETURN porcentaje;
END funcion_exp;
procedure qsort(arr in out lis_dem, lo in INTEGER, hi in INTEGER ) is
i INTEGER := lo;
j INTEGER := hi;
x NUMBER := arr((lo+hi)/2).porcentaje;
tmp rec_dem;
begin
LOOP
WHILE (arr(i).porcentaje < x) LOOP
i := i + 1;
END LOOP;
WHILE arr(j).porcentaje > x LOOP
j := j - 1;
END LOOP;
IF i <= j THEN
tmp := arr(i);
arr(i) := arr(j);
arr(j) := tmp;
i := i + 1;
j := j - 1;
END IF;
EXIT WHEN i > j;
END LOOP;
IF lo < j THEN
qsort(arr, lo, j);
END IF;
IF i < hi THEN
qsort(arr, i, hi);
END IF;
end qsort;
END BUSQUEDAS_OFT_PKG;
/
