set echo ON;
spool /datos/p06b/log/salida_20170130P06AP06B61T00.log;

CREATE TABLE P06B61T00_ENT_VINCULADAS (
	P06B61_EV_NUMEXP         	VARCHAR2(15) NOT NULL,
	P06B61_EV_CIF         		VARCHAR2(9) NOT NULL,
	P06B61_EV_ENTIDAD       	VARCHAR2(150) NOT NULL,
	P06B61_EV_CALLE             VARCHAR2(100),
	P06B61_EV_PORTAL            VARCHAR2(10),
	P06B61_EV_PISO         		VARCHAR2(4),
	P06B61_EV_LETRA         	VARCHAR2(4),
	P06B61_EV_CODPRO    		NUMBER(2),
	P06B61_EV_CODMUN    		NUMBER(4),
	P06B61_EV_LOCALID    		NUMBER(4),
	P06B61_EV_CODPST    		VARCHAR2(5),
	P06B61_EV_CLAVECAT    		VARCHAR2(5),
	P06B61_EV_FH_INSCRIP		DATE
)
TABLESPACE P0601E00;

CREATE SYNONYM P06B61S00 FOR P06B61T00_ENT_VINCULADAS;

ALTER TABLE P06B61T00_ENT_VINCULADAS ADD CONSTRAINT P06B61I00 PRIMARY KEY
	(P06B61_EV_NUMEXP,P06B61_EV_CIF) USING INDEX TABLESPACE P0602E00;

insert into P06B61T00_ENT_VINCULADAS (P06B61_EV_NUMEXP,P06B61_EV_CIF,P06B61_EV_ENTIDAD,P06B61_EV_CLAVECAT,P06B61_EV_FH_INSCRIP)
select p06b43_numexp AS P06B61_EV_NUMEXP, NVL(P06B23_EA_CIF,SUBSTR(P06B43_CLAVE,1,9)) AS P06B61_EV_CIF, NVL(P06B23_EA_ENTIDAD,SUBSTR(SUBSTR(P06B43_DENOM,1,130) || '(*' || P06B43_CLAVE || '*)',1,150)) AS P06B61_EV_ENTIDAD, P06B23_EA_CLAVE AS P06B61_EV_CLAVECAT, P06B43_FH_INSCRIP AS P06B61_EV_FH_INSCRIP from P06B23T00_ENT_ASEGURADORAS, P06B43T00_DEC_MOD0_ENT_VINC
where (p06b43_numexp, p06b43_ejercicio) in (select p06b43_numexp, max(p06b43_ejercicio) from P06B20T00_CORREDORES, P06B43T00_DEC_MOD0_ENT_VINC
where P06B20_numexp = p06b43_numexp and
      P06B20_ESTADO != 'B'
      and P06B20_ESTADO != 'R'
group by p06b43_numexp) and P06B23_EA_CLAVE(+) = P06B43_CLAVE
order by 1,4,2;

COMMIT;

exit;
