ALTER TABLE Trn_FinFacturas ADD CdCiaFac CHAR(2) DEFAULT ('00')
GO
CREATE TABLE Trn_FinFactint ( 
TipDoc VARCHAR(3) DEFAULT ('FIF') NOT NULL,
Factura INT DEFAULT (0) NOT NULL,
IdCia CHAR(2) DEFAULT ('01') NOT NULL,
Fecha SMALLDATETIME NOT NULL,
IdConcepto VARCHAR(4) NOT NULL,
IdCliente VARCHAR(16) NOT NULL,
IdAgencia VARCHAR(16) DEFAULT ('0') NOT NULL,
FechaVence SMALLDATETIME NOT NULL,
VrSubTotal MONEY DEFAULT (0) NOT NULL,
VrDescuento MONEY DEFAULT (0) NOT NULL,
VrImpuesto MONEY DEFAULT (0) NOT NULL,
VrRetencion MONEY DEFAULT (0) NOT NULL,
VrReteICA MONEY DEFAULT (0) NOT NULL,
VrReteIVA MONEY DEFAULT (0) NOT NULL,
VrCargos MONEY DEFAULT (0) NOT NULL,
VrOtrDcto MONEY DEFAULT (0) NOT NULL,
VrNeto MONEY DEFAULT (0) NOT NULL,
BaseImp MONEY DEFAULT (0) NOT NULL,
BaseRet MONEY DEFAULT (0) NOT NULL,
BaseIca MONEY DEFAULT (0) NOT NULL,
BaseRiv MONEY DEFAULT (0) NOT NULL,
TarifaIva DECIMAL(14,4) DEFAULT (0) NOT NULL,
TarifaRet DECIMAL(14,4) DEFAULT (0) NOT NULL,
TarifaIca DECIMAL(14,4) DEFAULT (0) NOT NULL,
TarifaRiv DECIMAL(14,4) DEFAULT (0) NOT NULL,
CodTarIva VARCHAR(4),
CodTarRet VARCHAR(4),
CodTarIca VARCHAR(4),
CodTarRiv VARCHAR(4),
IdCCosto VARCHAR(16) NOT NULL,
IdSubCos VARCHAR(16) NOT NULL,
IdVend VARCHAR(16) NOT NULL,
TarifaCom DECIMAL(14,4) DEFAULT (0) NOT NULL,
CodTarCom VARCHAR(4),
IdLocal VARCHAR(8) NOT NULL,
MulPlazos BIT DEFAULT (0) NOT NULL,
IdPlazo VARCHAR(4) NOT NULL,
OrigenAdd VARCHAR(10) NOT NULL,
TipCom VARCHAR(3) NOT NULL,
Comprobante INT DEFAULT (0) NOT NULL,
IdCiaCom CHAR(2) NOT NULL,
Anulado BIT DEFAULT (0) NOT NULL,
NumDev INT DEFAULT (0) NOT NULL,
FecDev SMALLDATETIME,
Observacion VARCHAR(250),
IdEstado VARCHAR(4) NOT NULL,
TimeSys SMALLDATETIME NOT NULL,
FecUpdate SMALLDATETIME,
IdCiaCrea CHAR(2) DEFAULT ('01') NOT NULL,
IdUsuario VARCHAR(11) NOT NULL
 CONSTRAINT PK_Trn_FinFactint PRIMARY KEY CLUSTERED (TipDoc,Factura,IdCia),
 CONSTRAINT CK_Trn_FinFactintIdAgencia CHECK ((len([IdAgencia]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdCCosto CHECK ((len([IdCCosto]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdCia CHECK ((len([IdCia]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdCiaCom CHECK ((len([IdCiaCom]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdCiaCrea CHECK ((len([IdCiaCrea]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdCliente CHECK ((len([IdCliente]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdConcepto CHECK ((len([IdConcepto]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdEstado CHECK ((len([IdEstado]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdLocal CHECK ((len([IdLocal]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdPlazo CHECK ((len([IdPlazo]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdSubCos CHECK ((len([IdSubCos]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdUsuario CHECK ((len([IdUsuario]) > 0)),
 CONSTRAINT CK_Trn_FinFactintIdVend CHECK ((len([IdVend]) > 0)),
 CONSTRAINT CK_Trn_FinFactintOrigenAdd CHECK ((len([OrigenAdd]) > 0)),
 CONSTRAINT CK_Trn_FinFactintTipCom CHECK ((len([TipCom]) > 0)),
 CONSTRAINT CK_Trn_FinFactintTipDoc CHECK ((len([TipDoc]) > 0)))
CREATE TABLE FinConcFactura ( 
IdConcepto VARCHAR(4) NOT NULL,
Concepto VARCHAR(250) NOT NULL,
TipoConc VARCHAR(10) DEFAULT ('INTERESES') NOT NULL,
Gravado BIT DEFAULT (0) NOT NULL,
CodTarIva VARCHAR(4),
CodTarRet VARCHAR(4),
CodTarIca VARCHAR(4),
IdCuenta VARCHAR(16) NOT NULL,
CdCueIva VARCHAR(16),
CdCueRet VARCHAR(16),
CdCueIca VARCHAR(16),
Inactivo BIT DEFAULT (0) NOT NULL,
FechaAdd SMALLDATETIME NOT NULL,
FechaUpdate SMALLDATETIME,
IdUsuario VARCHAR(11) NOT NULL
 CONSTRAINT PK_FinConcFactura PRIMARY KEY CLUSTERED (IdConcepto),
 CONSTRAINT CK_FinConcFacturaConcepto CHECK ((len([Concepto]) > 0)),
 CONSTRAINT CK_FinConcFacturaIdConcepto CHECK ((len([IdConcepto]) > 0)),
 CONSTRAINT CK_FinConcFacturaIdCuenta CHECK ((len([IdCuenta]) > 0)),
 CONSTRAINT CK_FinConcFacturaIdUsuario CHECK ((len([IdUsuario]) > 0)),
 CONSTRAINT CK_FinConcFacturaTipoConc CHECK ((len([TipoConc]) > 0)))
CREATE TABLE Trn_FinFactDet ( 
TipDoc VARCHAR(3) DEFAULT ('FIF') NOT NULL,
Factura INT DEFAULT (0) NOT NULL,
IdCia CHAR(2) DEFAULT ('01') NOT NULL,
Item INT DEFAULT (0) NOT NULL,
IdConcepto VARCHAR(4) NOT NULL,
Concepto VARCHAR(250) NOT NULL,
TipoReg VARCHAR(10) DEFAULT ('INTERESES') NOT NULL,
Cantidad DECIMAL(14,4) DEFAULT (0) NOT NULL,
VrUnitario MONEY DEFAULT (0) NOT NULL,
VrTotal MONEY DEFAULT (0) NOT NULL,
TarifIva DECIMAL(14,4) DEFAULT (0) NOT NULL,
VrIva MONEY DEFAULT (0) NOT NULL,
TarifRet DECIMAL(14,4) DEFAULT (0) NOT NULL,
VrRetfte MONEY DEFAULT (0) NOT NULL,
TarifIca DECIMAL(14,4) DEFAULT (0) NOT NULL,
VrRetica MONEY DEFAULT (0) NOT NULL,
TarifDcto DECIMAL(14,4) DEFAULT (0) NOT NULL,
VrDcto MONEY DEFAULT (0) NOT NULL,
nPrestamo INT DEFAULT (0) NOT NULL,
CdCiaPrest CHAR(2) DEFAULT ('00') NOT NULL,
FecIniLiq SMALLDATETIME,
FecFinLiq SMALLDATETIME,
CantDias INT DEFAULT (0) NOT NULL,
CdTarifIva VARCHAR(4),
CdTarifRet VARCHAR(4),
CdTarifIca VARCHAR(4),
CdTarifDct VARCHAR(4)
 CONSTRAINT PK_Trn_FinFactDet PRIMARY KEY CLUSTERED (TipDoc,Factura,IdCia,Item),
 CONSTRAINT CK_Trn_FinFactDetCdCiaPrest CHECK ((len([CdCiaPrest]) > 0)),
 CONSTRAINT CK_Trn_FinFactDetConcepto CHECK ((len([Concepto]) > 0)),
 CONSTRAINT CK_Trn_FinFactDetIdCia CHECK ((len([IdCia]) > 0)),
 CONSTRAINT CK_Trn_FinFactDetIdConcepto CHECK ((len([IdConcepto]) > 0)),
 CONSTRAINT CK_Trn_FinFactDetTipDoc CHECK ((len([TipDoc]) > 0)),
 CONSTRAINT CK_Trn_FinFactDetTipoReg CHECK ((len([TipoReg]) > 0)))
CREATE TABLE tm_PreFactura ( 
tmNumero VARCHAR(5) NOT NULL,
tmItem INT DEFAULT (0) NOT NULL,
tmIdConc VARCHAR(4) NOT NULL,
tmConcepto VARCHAR(250) NOT NULL,
tmTipoConc VARCHAR(10) DEFAULT ('INTERESES') NOT NULL,
tmCantidad DECIMAL(14,4) DEFAULT (0) NOT NULL,
tmVrUnitario MONEY DEFAULT (0) NOT NULL,
tmVrTotal MONEY DEFAULT (0) NOT NULL,
tmTarifIva DECIMAL(14,4) DEFAULT (0) NOT NULL,
tmVrIva MONEY DEFAULT (0) NOT NULL,
tmTarifRet DECIMAL(14,4) DEFAULT (0) NOT NULL,
tmVrRetfte MONEY DEFAULT (0) NOT NULL,
tmTarifIca DECIMAL(14,4) DEFAULT (0) NOT NULL,
tmVrRetica MONEY DEFAULT (0) NOT NULL,
tmTarifDct DECIMAL(14,4) DEFAULT (0) NOT NULL,
tmVrDcto MONEY DEFAULT (0) NOT NULL,
tmnPrestamo INT DEFAULT (0) NOT NULL,
tmCdCiaPrest CHAR(2) DEFAULT ('00') NOT NULL,
tmFecIniLiq SMALLDATETIME,
tmFecFinLiq SMALLDATETIME,
tmCantDias INT DEFAULT (0) NOT NULL,
tmCdTarifIva VARCHAR(4),
tmCdTarifRet VARCHAR(4),
tmCdTarifIca VARCHAR(4),
tmCdTarifDct VARCHAR(4)
 CONSTRAINT PK_tm_PreFactura PRIMARY KEY CLUSTERED (tmNumero,tmItem),
 CONSTRAINT CK_tm_PreFacturatmCdCiaPrest CHECK ((len([tmCdCiaPrest]) > 0)),
 CONSTRAINT CK_tm_PreFacturatmConcepto CHECK ((len([tmConcepto]) > 0)),
 CONSTRAINT CK_tm_PreFacturatmIdConc CHECK ((len([tmIdConc]) > 0)),
 CONSTRAINT CK_tm_PreFacturatmNumero CHECK ((len([tmNumero]) > 0)),
 CONSTRAINT CK_tm_PreFacturatmTipoConc CHECK ((len([tmTipoConc]) > 0)))
CREATE TABLE Trn_FinDevFac ( 
TipDev VARCHAR(3) DEFAULT ('DFI') NOT NULL,
Devolucion INT DEFAULT (0) NOT NULL,
IdCia CHAR(2) DEFAULT ('01') NOT NULL,
Fecha SMALLDATETIME NOT NULL,
IdConcepto VARCHAR(4) NOT NULL,
TipDoc VARCHAR(3) DEFAULT ('FIF') NOT NULL,
Factura INT DEFAULT (0) NOT NULL,
IdCiaDoc CHAR(2) DEFAULT ('01') NOT NULL,
FecDoc SMALLDATETIME NOT NULL,
IdCliente VARCHAR(16) NOT NULL,
IdAgencia VARCHAR(16) DEFAULT ('0') NOT NULL,
VrSubTotal MONEY DEFAULT (0) NOT NULL,
VrDescuento MONEY DEFAULT (0) NOT NULL,
VrImpuesto MONEY DEFAULT (0) NOT NULL,
VrRetencion MONEY DEFAULT (0) NOT NULL,
VrReteICA MONEY DEFAULT (0) NOT NULL,
VrReteIVA MONEY DEFAULT (0) NOT NULL,
VrCargos MONEY DEFAULT (0) NOT NULL,
VrOtrDcto MONEY DEFAULT (0) NOT NULL,
VrNeto MONEY DEFAULT (0) NOT NULL,
IdVend VARCHAR(16) NOT NULL,
IdLocal VARCHAR(8) NOT NULL,
ModdDev VARCHAR(10) DEFAULT ('TOTAL') NOT NULL,
OrigenAdd VARCHAR(10) NOT NULL,
TipCom VARCHAR(3) NOT NULL,
Comprobante INT DEFAULT (0) NOT NULL,
IdCiaCom CHAR(2) NOT NULL,
Observacion VARCHAR(250),
IdEstado VARCHAR(4) NOT NULL,
TimeSys SMALLDATETIME NOT NULL,
FecUpdate SMALLDATETIME,
IdCiaCrea CHAR(2) DEFAULT ('01') NOT NULL,
IdUsuario VARCHAR(11) NOT NULL
 CONSTRAINT PK_Trn_FinDevFac PRIMARY KEY CLUSTERED (TipDev,Devolucion,IdCia),
 CONSTRAINT CK_Trn_FinDevFacIdAgencia CHECK ((len([IdAgencia]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdCia CHECK ((len([IdCia]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdCiaCom CHECK ((len([IdCiaCom]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdCiaCrea CHECK ((len([IdCiaCrea]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdCiaDoc CHECK ((len([IdCiaDoc]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdCliente CHECK ((len([IdCliente]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdConcepto CHECK ((len([IdConcepto]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdEstado CHECK ((len([IdEstado]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdLocal CHECK ((len([IdLocal]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdUsuario CHECK ((len([IdUsuario]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacIdVend CHECK ((len([IdVend]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacModdDev CHECK ((len([ModdDev]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacOrigenAdd CHECK ((len([OrigenAdd]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacTipCom CHECK ((len([TipCom]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacTipDev CHECK ((len([TipDev]) > 0)),
 CONSTRAINT CK_Trn_FinDevFacTipDoc CHECK ((len([TipDoc]) > 0)))
GO
ALTER TABLE FinConcFactura ADD 
 CONSTRAINT FK_FinConcFactura_adm_Usuarios FOREIGN KEY (IdUsuario) REFERENCES [dbo].[adm_Usuarios] (IdUsuario),
 CONSTRAINT FK_FinConcFactura_Puc FOREIGN KEY (IdCuenta) REFERENCES [dbo].[Puc] (IdCuenta)

ALTER TABLE Trn_FinFactint ADD 
 CONSTRAINT FK_Trn_FinFactint_adm_Usuarios FOREIGN KEY (IdUsuario) REFERENCES [dbo].[adm_Usuarios] (IdUsuario),
 CONSTRAINT FK_Trn_FinFactint_CentroCosto FOREIGN KEY (IdCCosto) REFERENCES [dbo].[CentroCosto] (IdCCosto),
 CONSTRAINT FK_Trn_FinFactint_Companias FOREIGN KEY (IdCia) REFERENCES [dbo].[Companias] (IdCia),
 CONSTRAINT FK_Trn_FinFactint_Companias1 FOREIGN KEY (IdCiaCrea) REFERENCES [dbo].[Companias] (IdCia),
 CONSTRAINT FK_Trn_FinFactint_Conceptos FOREIGN KEY (IdConcepto) REFERENCES [dbo].[Conceptos] (IdConcepto),
 CONSTRAINT FK_Trn_FinFactint_EstadoDoc FOREIGN KEY (IdEstado) REFERENCES [dbo].[EstadoDoc] (IdEstado),
 CONSTRAINT FK_Trn_FinFactint_Localidades FOREIGN KEY (IdLocal) REFERENCES [dbo].[Localidades] (IdLocal),
 CONSTRAINT FK_Trn_FinFactint_Plazos FOREIGN KEY (IdPlazo) REFERENCES [dbo].[Plazos] (IdPlazo),
 CONSTRAINT FK_Trn_FinFactint_Sys_TiposDoc FOREIGN KEY (TipDoc) REFERENCES [dbo].[Sys_TiposDoc] (IdDoc),
 CONSTRAINT FK_Trn_FinFactint_Terceros FOREIGN KEY (IdCliente) REFERENCES [dbo].[Terceros] (IdTercero),
 CONSTRAINT FK_Trn_FinFactint_Terceros1 FOREIGN KEY (IdVend) REFERENCES [dbo].[Terceros] (IdTercero)

ALTER TABLE Trn_FinFactDet ADD 
 CONSTRAINT FK_Trn_FinFactDet_FinConcFactura FOREIGN KEY (IdConcepto) REFERENCES [dbo].[FinConcFactura] (IdConcepto),
 CONSTRAINT FK_Trn_FinFactDet_Trn_FinFactint FOREIGN KEY (TipDoc,Factura,IdCia) REFERENCES [dbo].[Trn_FinFactint] (TipDoc,Factura,IdCia)

ALTER TABLE Trn_FinDevFac ADD 
 CONSTRAINT FK_Trn_FinDevFac_adm_Usuarios FOREIGN KEY (IdUsuario) REFERENCES [dbo].[adm_Usuarios] (IdUsuario),
 CONSTRAINT FK_Trn_FinDevFac_Companias FOREIGN KEY (IdCia) REFERENCES [dbo].[Companias] (IdCia),
 CONSTRAINT FK_Trn_FinDevFac_Companias1 FOREIGN KEY (IdCiaCrea) REFERENCES [dbo].[Companias] (IdCia),
 CONSTRAINT FK_Trn_FinDevFac_Conceptos FOREIGN KEY (IdConcepto) REFERENCES [dbo].[Conceptos] (IdConcepto),
 CONSTRAINT FK_Trn_FinDevFac_EstadoDoc FOREIGN KEY (IdEstado) REFERENCES [dbo].[EstadoDoc] (IdEstado),
 CONSTRAINT FK_Trn_FinDevFac_Localidades FOREIGN KEY (IdLocal) REFERENCES [dbo].[Localidades] (IdLocal),
 CONSTRAINT FK_Trn_FinDevFac_Sys_TiposDoc FOREIGN KEY (TipDev) REFERENCES [dbo].[Sys_TiposDoc] (IdDoc),
 CONSTRAINT FK_Trn_FinDevFac_Terceros FOREIGN KEY (IdCliente) REFERENCES [dbo].[Terceros] (IdTercero),
 CONSTRAINT FK_Trn_FinDevFac_Terceros1 FOREIGN KEY (IdVend) REFERENCES [dbo].[Terceros] (IdTercero),
 CONSTRAINT FK_Trn_FinDevFac_Trn_FinFactint FOREIGN KEY (TipDoc,Factura,IdCiaDoc) REFERENCES [dbo].[Trn_FinFactint] (TipDoc,Factura,IdCia)

 CREATE NONCLUSTERED INDEX IX_Trn_FinFactintFecha ON Trn_FinFactint(Fecha)
 CREATE NONCLUSTERED INDEX IX_Trn_FinFactintIdCliente ON Trn_FinFactint(IdCliente)
 CREATE NONCLUSTERED INDEX IX_Trn_FinFactDetIdConcepto ON Trn_FinFactDet(IdConcepto)

 CREATE NONCLUSTERED INDEX IX_Trn_FinDevFacFecha ON Trn_FinDevFac(Fecha)
 CREATE NONCLUSTERED INDEX IX_Trn_FinDevFacFactura ON Trn_FinDevFac(TipDoc,Factura,IdCiaDoc)
 CREATE NONCLUSTERED INDEX IX_Trn_FinDevFacIdCliente ON Trn_FinDevFac(IdCliente)
