
CREATE TABLE asurnet.dbo.OPORTUNIDADES (
	OPT_ID int IDENTITY(1,1) NOT NULL,
	CLI_ID int NULL,
	OPT_OPERACION tinyint NULL,
	OPT_ESTADO tinyint NULL,
	OPT_FECHAAPERTURA datetime NULL,
	OPT_FECHACIERRE datetime NULL,
	OPT_FECHAPREVISTA_CIERRE datetime NULL,
	OPT_POREXITO money NULL,
	OPT_FECHALIMITE datetime NULL,
	ETO_ID int NULL,
	TIO_ID int NULL,
	RAM_ID char(5) COLLATE Modern_Spanish_CI_AI NULL,
	COL_ID char(5) COLLATE Modern_Spanish_CI_AI NULL,
	EJE_ID char(5) COLLATE Modern_Spanish_CI_AI NULL,
	POL_ID int NULL,
	OPT_COMPANIA char(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	OPT_DETALLES_COMPANIA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	OPT_OBSERVACIONES text COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	OPT_RIESGO_DESC char(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	OPT_Origen char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	OPT_IMPORTE money NULL,
	USR_FC datetime NULL,
	USR_FM datetime NULL,
	USR_Id_FC int NULL,
	USR_Id_FM int NULL,
	OPT_Precio money NULL,
	OPT_FechaVencimiento datetime NULL,
	MPE_ID int NULL,
	OPT_OfiCode int DEFAULT 0 NULL,
	OPT_Tecnico varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	MON_ID int NULL,
	OPT_OperacionesExentas tinyint DEFAULT 0 NULL,
	OPT_ConfOperacionesExentas varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	OPT_LPBCOperacion tinyint NULL,
	OPT_DocumentacionIngresos tinyint DEFAULT 0 NULL,
	OPT_OperacionValidada tinyint DEFAULT 0 NULL,
    OPT_FECHABAJA datetime NULL,
	CONSTRAINT PK_OPORTUNIDADES PRIMARY KEY (OPT_ID)
);
 CREATE NONCLUSTERED INDEX IDX_OPT_RAM ON asurnet.dbo.OPORTUNIDADES (  RAM_ID ASC  )
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IND_CLI_OPT ON asurnet.dbo.OPORTUNIDADES (  CLI_ID ASC  , OPT_Origen ASC  )
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IND_OPT_CLIID ON asurnet.dbo.OPORTUNIDADES (  CLI_ID ASC  )
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;






CREATE TABLE asurnet.dbo.RIESGOS (
	RIE_Id int IDENTITY(1,1) NOT NULL,
	RIE_Tipo smallint NULL,
	RIE_FC datetime NOT NULL,
	RIE_Short varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	RIE_FM datetime NOT NULL,
	USR_Id_FC int NULL,
	USR_Id_FM int NULL,
	RIE_Descripcion varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT PK_RIESGOS PRIMARY KEY (RIE_Id)
);
 CREATE NONCLUSTERED INDEX IND_FC_USRFC_CLIID ON asurnet.dbo.RIESGOS (  RIE_FC ASC  , USR_Id_FC ASC  , RIE_Id DESC  )
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;




CREATE TABLE asurnet.dbo.OPORTUNIDADES_RIESGOS (
	OPT_ID int NOT NULL,
	RIE_ID int NOT NULL,
    --CORR_Id smallint DEFAULT 0 NOT NULL,
    CONSTRAINT PK_OPORTUNIDADES_RIESGOS PRIMARY KEY (OPT_ID,RIE_ID)
);


ALTER TABLE asurnet.dbo.OPORTUNIDADES_RIESGOS ADD CONSTRAINT FK_OPORTUNIDADES_RIESGOS_OPORTUNIDADES FOREIGN KEY (OPT_ID) REFERENCES asurnet.dbo.OPORTUNIDADES(OPT_ID);
ALTER TABLE asurnet.dbo.OPORTUNIDADES_RIESGOS ADD CONSTRAINT FK_OPORTUNIDADES_RIESGOS_RIESGOS FOREIGN KEY (RIE_ID) REFERENCES asurnet.dbo.RIESGOS(RIE_Id);
