CREATE TABLE JurCausales ( 
IdCausal VARCHAR(4) NOT NULL,
Causal VARCHAR(50) NOT NULL,
IdTipoPro VARCHAR(4) NOT NULL,
Inactivo BIT DEFAULT (0) NOT NULL
 CONSTRAINT PK_JurCausales PRIMARY KEY CLUSTERED (IdCausal),
 CONSTRAINT CK_JurCausalesCausal CHECK ((len([Causal]) > 0)),
 CONSTRAINT CK_JurCausalesIdCausal CHECK ((len([IdCausal]) > 0)),
 CONSTRAINT CK_JurCausalesIdTipoPro CHECK ((len([IdTipoPro]) > 0)))
CREATE TABLE JurTiposRec ( 
IdTipoRec VARCHAR(4) NOT NULL,
TipReclamante VARCHAR(50) NOT NULL,
Inactivo BIT DEFAULT (0) NOT NULL
 CONSTRAINT PK_JurTiposRec PRIMARY KEY CLUSTERED (IdTipoRec),
 CONSTRAINT CK_JurTiposRecIdTipoRec CHECK ((len([IdTipoRec]) > 0)),
 CONSTRAINT CK_JurTiposRecTipReclamante CHECK ((len([TipReclamante]) > 0)))
CREATE TABLE Trn_JurReclamac ( 
TipDoc VARCHAR(3) DEFAULT ('JRC') NOT NULL,
Reclamac INT DEFAULT (0) NOT NULL,
IdCia CHAR(2) DEFAULT ('01') NOT NULL,
Fecha SMALLDATETIME NOT NULL,
FechaRec SMALLDATETIME NOT NULL,
TipSin VARCHAR(3) DEFAULT ('SIN') NOT NULL,
Siniestro INT DEFAULT (0) NOT NULL,
IdCiaSin CHAR(2) NOT NULL,
IdReclamnte VARCHAR(16) NOT NULL,
IdTipoRec VARCHAR(4) NOT NULL,
VrReclamac MONEY DEFAULT (0) NOT NULL,
Observacion VARCHAR(1000),
OrigenAdd VARCHAR(10) NOT NULL,
TimeSys SMALLDATETIME NOT NULL,
IdCiaCrea CHAR(2) DEFAULT ('01') NOT NULL,
IdUsuario VARCHAR(11) NOT NULL
 CONSTRAINT PK_Trn_JurReclamac PRIMARY KEY CLUSTERED (TipDoc,Reclamac,IdCia),
 CONSTRAINT CK_Trn_JurReclamacIdCia CHECK ((len([IdCia]) > 0)),
 CONSTRAINT CK_Trn_JurReclamacIdCiaCrea CHECK ((len([IdCiaCrea]) > 0)),
 CONSTRAINT CK_Trn_JurReclamacIdCiaSin CHECK ((len([IdCiaSin]) > 0)),
 CONSTRAINT CK_Trn_JurReclamacIdReclamnte CHECK ((len([IdReclamnte]) > 0)),
 CONSTRAINT CK_Trn_JurReclamacIdTipoRec CHECK ((len([IdTipoRec]) > 0)),
 CONSTRAINT CK_Trn_JurReclamacIdUsuario CHECK ((len([IdUsuario]) > 0)),
 CONSTRAINT CK_Trn_JurReclamacOrigenAdd CHECK ((len([OrigenAdd]) > 0)),
 CONSTRAINT CK_Trn_JurReclamacTipDoc CHECK ((len([TipDoc]) > 0)),
 CONSTRAINT CK_Trn_JurReclamacTipSin CHECK ((len([TipSin]) > 0)))
CREATE TABLE Trn_JurRecOfrecmto ( 
TipDoc VARCHAR(3) DEFAULT ('JRC') NOT NULL,
Reclamac INT DEFAULT (0) NOT NULL,
IdCia CHAR(2) DEFAULT ('01') NOT NULL,
Item INT DEFAULT (0) NOT NULL,
FechaOfrec SMALLDATETIME NOT NULL,
VrOfrecmto MONEY DEFAULT (0) NOT NULL,
OfcAceptado BIT DEFAULT (0) NOT NULL,
FecAceptado SMALLDATETIME,
OfcPagado BIT DEFAULT (0) NOT NULL,
NumSolGiro VARCHAR(30),
Comentarios VARCHAR(1000),
TimeSys SMALLDATETIME NOT NULL,
IdUsuario VARCHAR(11) NOT NULL
 CONSTRAINT PK_Trn_JurRecOfrecmto PRIMARY KEY CLUSTERED (TipDoc,Reclamac,IdCia,Item),
 CONSTRAINT CK_Trn_JurRecOfrecmtoIdCia CHECK ((len([IdCia]) > 0)),
 CONSTRAINT CK_Trn_JurRecOfrecmtoIdUsuario CHECK ((len([IdUsuario]) > 0)),
 CONSTRAINT CK_Trn_JurRecOfrecmtoTipDoc CHECK ((len([TipDoc]) > 0)))
GO
ALTER TABLE Trn_JurCasos ADD CdCausal VARCHAR(4),Cedtraparte VARCHAR(16),PolizaResCivil VARCHAR(30)
,VigencResCivil SMALLDATETIME,NitEmpAseg VARCHAR(16),NombreAseg VARCHAR(150)
GO
ALTER TABLE Trn_JurCasosHis ADD CedContparte VARCHAR(16)
GO
ALTER TABLE Trn_Disciplinario ADD TipoInf VARCHAR(10) DEFAULT('CONDUCTOR')
GO
ALTER TABLE Trn_JurInvestiga ADD Cedtraparte VARCHAR(16),CdTipoPro VARCHAR(4),CdCausal VARCHAR(4)
GO
ALTER TABLE Trn_JurInvesHis ADD CedContparte VARCHAR(16)
GO
ALTER TABLE JurCausales ADD 
 CONSTRAINT FK_JurCausales_JurTipos FOREIGN KEY (IdTipoPro) REFERENCES [dbo].[JurTipos] (IdTipoPro)
ALTER TABLE Trn_JurReclamac ADD 
 CONSTRAINT FK_Trn_JurReclamac_adm_Usuarios FOREIGN KEY (IdUsuario) REFERENCES [dbo].[adm_Usuarios] (IdUsuario),
 CONSTRAINT FK_Trn_JurReclamac_Companias FOREIGN KEY (IdCia) REFERENCES [dbo].[Companias] (IdCia),
 CONSTRAINT FK_Trn_JurReclamac_Companias1 FOREIGN KEY (IdCiaCrea) REFERENCES [dbo].[Companias] (IdCia),
 CONSTRAINT FK_Trn_JurReclamac_JurTiposRec FOREIGN KEY (IdTipoRec) REFERENCES [dbo].[JurTiposRec] (IdTipoRec),
 CONSTRAINT FK_Trn_JurReclamac_Sys_TiposDoc FOREIGN KEY (TipDoc) REFERENCES [dbo].[Sys_TiposDoc] (IdDoc),
 CONSTRAINT FK_Trn_JurReclamac_Terceros FOREIGN KEY (IdReclamnte) REFERENCES [dbo].[Terceros] (IdTercero)
 CREATE NONCLUSTERED INDEX IX_Trn_JurReclamacFecha ON Trn_JurReclamac(Fecha)
 CREATE NONCLUSTERED INDEX IX_Trn_JurReclamacSiniestro ON Trn_JurReclamac(TipSin,Siniestro,IdCiaSin)
ALTER TABLE Trn_JurRecOfrecmto ADD 
 CONSTRAINT FK_Trn_JurRecOfrecmto_adm_Usuarios FOREIGN KEY (IdUsuario) REFERENCES [dbo].[adm_Usuarios] (IdUsuario),
 CONSTRAINT FK_Trn_JurRecOfrecmto_Trn_JurReclamac FOREIGN KEY (TipDoc,Reclamac,IdCia) REFERENCES [dbo].[Trn_JurReclamac] (TipDoc,Reclamac,IdCia)
 CREATE NONCLUSTERED INDEX IX_Trn_JurRecOfrecmtoFechaOfrec ON Trn_JurRecOfrecmto(FechaOfrec)
