DECLARE
CURSOR c11 IS
SELECT DISTINCT apellido1,apellido2,nombre,CONCAT(substr(fechanacimiento,7,2)||'/'||SUBSTR(fechanacimiento,5,2)||'/',SUBSTR(fechanacimiento,1,4)),
nombrepadre,nombremadre,UPPER(paisnacimiento),dni,pasaporte,
replace(replace(replace(replace(replace(domicilio,'calle','C/'),'CL','C/'),'CL','C/'),'C\','C/'),'CC','C/') as Domicilio,
--domicilio,
DECODE(UPPER(provincianacimiento),
'ÁLAVA' ,'01','ALAVA' ,'01','ARABA' ,'01','ALBACETE' ,'02','ALICANTE' ,'03','ALMERIA' ,'04','ALMERÍA' , '04',
'AVILA' ,'05','ÁVILA' ,'05','BADAJOZ' ,'06','BALEARES' ,'07','ISLAS BALEARES','07','BARCELONA' ,'08',
'BURGOS' ,'09','CACERES' ,'10','CÁCERES' ,'10','CADIZ' ,'11','CÁDIZ' ,'11','CASTELLON' ,'12','CASTELLÓN' , '12','CIUDAD REAL','13',
'CORDOBA' ,'14','CÓRDOBA' ,'14','A CORUÑA' ,'15','CORUÑA' ,'15','LA CORUÑA' ,'15','CUENCA' ,'16',
'GIRONA' ,'17','GERONA' ,'17','GRANADA' ,'18','GUADALAJARA','19','GIPUZKOA' ,'20','GUIPUZCOA' ,'20',
'HUELVA' ,'21','HUESCA' ,'22','JAEN' ,'23','JAÉN' ,'23','LEÓN' ,'24','LEON' ,'24',
'LA RIOJA' ,'26','RIOJA' ,'26','LUGO' ,'27','MADRID' ,'28','MALAGA' ,'29','MÁLAGA' ,'29','MURCIA' ,'30',
'NAVARRA' ,'31','OURENSE' ,'32','ORENSE' ,'32','ASTURIAS' ,'33','PALENCIA' ,'34','LAS PALMAS','35','PALMAS' ,'35',
'PONTEVEDRA','36','SALAMANCA','37','TENERIFE' ,'38','CANTABRIA' ,'39','SEGOVIA' ,'40','SEVILLA' ,'41',
'SORIA' ,'42','TARRAGONA','43','TERUEL' ,'44','TOLEDO' ,'45','VALENCIA' ,'46','VALLADOLID','47',
'VIZCAYA' ,'48','BIZKAIA' ,'48','ZAMORA' ,'49','ZARAGOZA' ,'50','CEUTA' ,'51','MELILLA' ,'52'),
localidadnacimiento
FROM sica.personasintermedias
--WHERE apellido1 is not null and fvalidaciondoc > '201602240000' and CODEMPRESA NOT IN ('MUN','FGC','DGP','ADN','MPR','DPE');WHERE apellido1 is not null and fvalidaciondoc > (select to_char(sysdate,'yyyymmdd') from dual)||'0000' and CODEMPRESA NOT IN ('MUN','FGC','DGP','ADN','MPR', 'DPE');v_fila VARCHAR2(600);
v_numfila number(5);
v_nombrefichero varchar2(16);
v_basenombrefichero varchar2(9);
v_envio number(2);
v_fichero UTL_FILE.FILE_TYPE;
v_fecha varchar2(6);
v_orden number(5);
-- Variables para recoger valores de la consulta
c_apellido1 PersonasIntermedias.apellido1%TYPE;
c_apellido2 PersonasIntermedias.apellido2%TYPE;
c_nombre PersonasIntermedias.nombre%TYPE;
--c_fechanacimiento PersonasIntermedias.fechanacimiento%TYPE;
c_fechanacimiento VARCHAR2(10);
c_nombrepadre PersonasIntermedias.nombrepadre%TYPE;
c_nombremadre PersonasIntermedias.nombremadre%TYPE;
c_paisnacimiento PersonasIntermedias.paisnacimiento%TYPE;
c_dni PersonasIntermedias.dni%TYPE;
c_pasaporte PersonasIntermedias.pasaporte%TYPE;
c_domicilio PersonasIntermedias.domicilio%TYPE;
--c_provincianacimiento PersonasIntermedias.provincianacimiento%TYPE;
c_provincianacimiento VARCHAR2(3);
c_localidadnacimiento PersonasIntermedias.localidadnacimiento%TYPE;
-- FIN Variables
BEGIN
v_envio:=0;
SELECT to_char(sysdate,'yymmdd') INTO v_fecha from dual;
v_nombrefichero:='MAD'||v_fecha||'1'||'.txt';
dbms_output.enable(500000);
OPEN c11;
FETCH c11 INTO c_apellido1,c_apellido2,c_nombre,c_fechanacimiento,c_nombrepadre,c_nombremadre,c_paisnacimiento,c_dni,c_pasaporte,c_domicilio,c_provincianacimiento,c_localidadnacimiento;
v_fichero := UTL_FILE.FOPEN ('DIR_TEMP',v_nombrefichero,'w',32767);v_orden := 1; -- Si entramos al While es la primera
WHILE c11%FOUND LOOP
IF c_paisnacimiento = 'ESPAÑA' THEN -- DNI
dbms_output.put_line (CONCAT(c_apellido1||'|'||c_apellido2||'|'||c_nombre||'|'||c_fechanacimiento||'|'||c_nombrepadre||'|'||c_nombremadre||'|'||UPPER(c_paisnacimiento)||'|'||c_dni||'|'|| '|'||c_domicilio||'|'||c_provincianacimiento||'|'||c_localidadnacimiento||'|'||'MAD'||v_fecha,v_orden));
v_orden := v_orden+1;
ELSE -- No nacido en España
IF (substr(c_dni,1,1) = 'X') OR (substr(c_dni,1,1) = 'Y') THEN -- NIE
dbms_output.put_line (CONCAT(c_apellido1||'|'|| '|'||c_nombre||'|'||c_fechanacimiento||'|'||c_nombrepadre||'|'||c_nombremadre||'|'||UPPER(c_paisnacimiento)||'|'|| '|'||c_dni||'|'||c_domicilio||'|'||'|'||c_localidadnacimiento||'|'||'MAD'||v_fecha,v_orden));
v_orden := v_orden+1;
ELSE
IF (length(c_dni) >= 5) THEN
dbms_output.put_line (CONCAT(c_apellido1||'|'||c_apellido2||'|'||c_nombre||'|'||c_fechanacimiento||'|'||c_nombrepadre||'|'||c_nombremadre||'|'||UPPER(c_paisnacimiento)||'|'||c_dni||'|'|| '|'||c_domicilio||'|'||c_provincianacimiento||'|'||c_localidadnacimiento||'|'||'MAD'||v_fecha,v_orden));
v_orden := v_orden+1;
ELSE
dbms_output.put_line (CONCAT(c_apellido1||'|'||c_apellido2||'|'||c_nombre||'|'||c_fechanacimiento||'|'||c_nombrepadre||'|'||c_nombremadre||'|'||UPPER(c_paisnacimiento)||'|'||c_pasaporte||'|'|| '|'||c_domicilio||'|'||'|'||c_localidadnacimiento||'|'||'MAD'||v_fecha,v_orden));
v_orden := v_orden+1;
END IF;
END IF;
END IF;
FETCH c11 INTO c_apellido1,c_apellido2,c_nombre,c_fechanacimiento,c_nombrepadre,c_nombremadre,c_paisnacimiento,c_dni,c_pasaporte,c_domicilio,c_provincianacimiento,c_localidadnacimiento;
END LOOP;
UTL_FILE.FCLOSE(v_fichero);
--dbms_output.disable;
CLOSE c11;
END;
/
