ALTER TABLE LogMudConc ADD PlacaVehic VARCHAR(10),TarifaPago MONEY DEFAULT(0) NOT NULL
GO
CREATE TABLE LogMudFacon ( 
Id INT IDENTITY ( 1,1 ) NOT NULL,
ClaveReg VARCHAR(20) NOT NULL,
Fecha SMALLDATETIME NOT NULL,
TipDoc VARCHAR(3) NOT NULL,
IdConcepto VARCHAR(4),
EstadoReg INT DEFAULT ((0)) NOT NULL,
FechaCrea SMALLDATETIME NOT NULL,
Concepto VARCHAR(50),
IdUsuario VARCHAR(11) NOT NULL,
Nombre VARCHAR(50) NOT NULL,
Observacion VARCHAR(1000)
 CONSTRAINT PK_LogMudFacon PRIMARY KEY NONCLUSTERED (Id),
 CONSTRAINT CK_LogMudFaconClaveReg CHECK ((len([ClaveReg])>(0))),
 CONSTRAINT CK_LogMudFaconIdUsuario CHECK ((len([IdUsuario])>(0))),
 CONSTRAINT CK_LogMudFaconNombre CHECK ((len([Nombre])>(0))),
 CONSTRAINT CK_LogMudFaconTipDoc CHECK ((len([TipDoc])>(0))))
GO
CREATE TABLE LogMudFaconOrd ( 
ClaveReg VARCHAR(20) NOT NULL,
Item INT DEFAULT ((0)) NOT NULL,
TipCont VARCHAR(3) NOT NULL,
NumContrato INT DEFAULT ((0)) NOT NULL,
IdCia CHAR(2) NOT NULL,
IdCliente VARCHAR(16) NOT NULL,
IdAgencia VARCHAR(16) NOT NULL,
VrServicio MONEY DEFAULT ((2)) NOT NULL,
VrImpuesto MONEY DEFAULT ((0)) NOT NULL,
VrSeguro MONEY DEFAULT ((0)) NOT NULL,
VrOtros MONEY DEFAULT ((0)) NOT NULL,
TipOrden VARCHAR(3),
NumOrden INT DEFAULT ((0)) NOT NULL,
IdCiaOrden CHAR(2),
FechaVence SMALLDATETIME,
Estado INT DEFAULT ((0)) NOT NULL,
TipFac VARCHAR(3) NOT NULL,
Factura INT DEFAULT ((0)) NOT NULL,
IdCiaFac CHAR(2) NOT NULL,
Observacion VARCHAR(1000),
CodTarIva VARCHAR(4),
CodTarRet VARCHAR(4),
CodTarIca VARCHAR(4),
CodTarRiv VARCHAR(4),
CodTarRtc VARCHAR(4),
CodTarCom VARCHAR(4),
IdCCosto VARCHAR(16),
IdSubCos VARCHAR(16),
IdVend VARCHAR(16),
IdLocEnv VARCHAR(8),
IdForma VARCHAR(4),
IdPlazo VARCHAR(4),
IdBodega VARCHAR(4),
ExcIva BIT DEFAULT ((0)) NOT NULL,
Autoret BIT DEFAULT ((0)) NOT NULL,
Inc_Ret BIT DEFAULT ((0)) NOT NULL,
Inc_Ica BIT DEFAULT ((0)) NOT NULL,
Inc_Riv BIT DEFAULT ((0)) NOT NULL,
AutoIca BIT DEFAULT ((0)) NOT NULL,
DirEnvio VARCHAR(250),
DiasEntrega INT DEFAULT ((0)) NOT NULL,
NitContac VARCHAR(16),
NomContac VARCHAR(150),
TelContac VARCHAR(20),
emlContac VARCHAR(100),
CargoContac VARCHAR(50),
NomCliente VARCHAR(150),
Agencia VARCHAR(150),
Vendedor VARCHAR(150)
 CONSTRAINT PK_LogMudFaconOrd PRIMARY KEY CLUSTERED (ClaveReg,Item),
 CONSTRAINT CK_LogMudFaconOrdClaveReg CHECK ((len([ClaveReg])>(0))),
 CONSTRAINT CK_LogMudFaconOrdIdAgencia CHECK ((len([IdAgencia])>(0))),
 CONSTRAINT CK_LogMudFaconOrdIdCia CHECK ((len([IdCia])>(0))),
 CONSTRAINT CK_LogMudFaconOrdIdCiaFac CHECK ((len([IdCiaFac])>(0))),
 CONSTRAINT CK_LogMudFaconOrdIdCliente CHECK ((len([IdCliente])>(0))),
 CONSTRAINT CK_LogMudFaconOrdTipCont CHECK ((len([TipCont])>(0))),
 CONSTRAINT CK_LogMudFaconOrdTipFac CHECK ((len([TipFac])>(0))))
GO
CREATE TABLE LogMudFaconDet ( 
ClaveReg VARCHAR(20) NOT NULL,
Item INT DEFAULT ((0)) NOT NULL,
TipCont VARCHAR(3) NOT NULL,
NumContrato INT DEFAULT ((0)) NOT NULL,
IdCia CHAR(2) NOT NULL,
IdConcepto VARCHAR(4) NOT NULL,
Descripcion VARCHAR(500) NOT NULL,
ValorTotal MONEY DEFAULT ((0)) NOT NULL,
TarifaIva DECIMAL(14,4) DEFAULT ((0)) NOT NULL,
TarifSeguro DECIMAL(14,4) DEFAULT ((0)) NOT NULL,
TipoRubro VARCHAR(20),
CdCuenta VARCHAR(16),
CodTarIva VARCHAR(4),
CodTarSeg VARCHAR(4)
 CONSTRAINT PK_LogMudFaconDet PRIMARY KEY CLUSTERED (ClaveReg,Item),
 CONSTRAINT CK_LogMudFaconDetClaveReg CHECK ((len([ClaveReg])>(0))),
 CONSTRAINT CK_LogMudFaconDetDescripcion CHECK ((len([Descripcion])>(0))),
 CONSTRAINT CK_LogMudFaconDetIdCia CHECK ((len([IdCia])>(0))),
 CONSTRAINT CK_LogMudFaconDetIdConcepto CHECK ((len([IdConcepto])>(0))),
 CONSTRAINT CK_LogMudFaconDetTipCont CHECK ((len([TipCont])>(0))))
GO
CREATE CLUSTERED INDEX IX_LogMudFaconClaveReg ON LogMudFacon(ClaveReg)
CREATE NONCLUSTERED INDEX IX_LogMudFaconOrdNumContrato ON LogMudFaconOrd(TipCont,NumContrato,IdCia)

--sp
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsLogMudConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsLogMudConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpLogMudConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpLogMudConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryLogMudConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryLogMudConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsLogMudFaconOrd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsLogMudFaconOrd]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsLogMudConc] @pmId VARCHAR(5),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDescripcion VARCHAR(500),@pmValorTotal MONEY,@pmTarifaIva DECIMAL(14,4),@pmTipoConc VARCHAR(10),@pmTipoRubro VARCHAR(20)
,@pmTipoReg INT,@pmCdCuenta VARCHAR(16),@pmNitTercero VARCHAR(16),@pmReferencia VARCHAR(50),@pmNumDocRef VARCHAR(20),@pmCodTarIva VARCHAR(4),@pmRazonSocial VARCHAR(250),@pmConcepto VARCHAR(250),@pmTarifSeguro DECIMAL(14,4),@pmCodTarSeg VARCHAR(4)
,@pmPlacaVehic VARCHAR(10),@pmTarifaPago MONEY
 AS 
 INSERT INTO LogMudConc (Id,Item,IdConcepto,Descripcion,ValorTotal,TarifaIva,TipoConc,TipoRubro,TipoReg,CdCuenta,NitTercero,Referencia,NumDocRef,CodTarIva,RazonSocial,Concepto,TarifSeguro,CodTarSeg,PlacaVehic,TarifaPago)
 VALUES (@pmId,@pmItem,@pmIdConcepto,@pmDescripcion,@pmValorTotal,@pmTarifaIva,@pmTipoConc,@pmTipoRubro,@pmTipoReg,@pmCdCuenta,@pmNitTercero,@pmReferencia,@pmNumDocRef,@pmCodTarIva,@pmRazonSocial,@pmConcepto,@pmTarifSeguro,@pmCodTarSeg,@pmPlacaVehic,@pmTarifaPago)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpLogMudConc] @pmId VARCHAR(5),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDescripcion VARCHAR(500),@pmValorTotal MONEY,@pmTarifaIva DECIMAL(14,4),@pmTipoConc VARCHAR(10),@pmTipoRubro VARCHAR(20)
,@pmTipoReg INT,@pmCdCuenta VARCHAR(16),@pmNitTercero VARCHAR(16),@pmReferencia VARCHAR(50),@pmNumDocRef VARCHAR(20),@pmCodTarIva VARCHAR(4),@pmRazonSocial VARCHAR(250),@pmConcepto VARCHAR(250),@pmTarifSeguro DECIMAL(14,4),@pmCodTarSeg VARCHAR(4)
,@pmPlacaVehic VARCHAR(10),@pmTarifaPago MONEY
 AS 
 UPDATE LogMudConc SET IdConcepto=@pmIdConcepto,Descripcion=@pmDescripcion,ValorTotal=@pmValorTotal,TarifaIva=@pmTarifaIva,TipoConc=@pmTipoConc,TipoRubro=@pmTipoRubro,TipoReg=@pmTipoReg,CdCuenta=@pmCdCuenta
,NitTercero=@pmNitTercero,Referencia=@pmReferencia,NumDocRef=@pmNumDocRef,CodTarIva=@pmCodTarIva,RazonSocial=@pmRazonSocial,Concepto=@pmConcepto,TarifSeguro=@pmTarifSeguro,CodTarSeg=@pmCodTarSeg,PlacaVehic=@pmPlacaVehic,TarifaPago=@pmTarifaPago
 WHERE Id=@pmId AND Item=@pmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryLogMudConc] @pmId VARCHAR(5),@pmItem INT
 AS 
 SELECT Id,Item,IdConcepto,Descripcion,ValorTotal,TarifaIva,TipoConc,TipoRubro,TipoReg,CdCuenta,NitTercero,Referencia,NumDocRef,CodTarIva
,TarifSeguro,CodTarSeg,RazonSocial,Concepto,PlacaVehic,TarifaPago
 FROM LogMudConc
 WHERE Id=@pmId AND Item=@pmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsLogMudFaconOrd] @pmClaveReg VARCHAR(20),@pmItem INT,@pmTipCont VARCHAR(3),@pmNumContrato INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrServicio MONEY,@pmVrImpuesto MONEY,@pmVrSeguro MONEY,@pmVrOtros MONEY,@pmTipOrden VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrden CHAR(2),@pmFechaVence SMALLDATETIME,@pmEstado INT,@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmObservacion VARCHAR(1000),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCodTarRtc VARCHAR(4),@pmCodTarCom VARCHAR(4)
,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmIdLocEnv VARCHAR(8),@pmIdForma VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdBodega VARCHAR(4),@pmExcIva BIT,@pmAutoret BIT,@pmInc_Ret BIT,@pmInc_Ica BIT,@pmInc_Riv BIT,@pmDirEnvio VARCHAR(250),@pmDiasEntrega INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmNomCliente VARCHAR(150),@pmAgencia VARCHAR(150),@pmVendedor VARCHAR(150),@pmAutoIca BIT
 AS 
 INSERT INTO LogMudFaconOrd (ClaveReg,Item,TipCont,NumContrato,IdCia,IdCliente,IdAgencia,VrServicio,VrImpuesto,VrSeguro,VrOtros,TipOrden,NumOrden,IdCiaOrden,FechaVence,Estado,TipFac,Factura,IdCiaFac,Observacion,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc,CodTarCom,IdCCosto,IdSubCos,IdVend,IdLocEnv,IdForma,IdPlazo,IdBodega,ExcIva,Autoret,Inc_Ret,Inc_Ica,Inc_Riv,AutoIca,DirEnvio,DiasEntrega,NitContac,NomContac,TelContac,emlContac,CargoContac,NomCliente,Agencia,Vendedor)
 VALUES (@pmClaveReg,@pmItem,@pmTipCont,@pmNumContrato,@pmIdCia,@pmIdCliente,@pmIdAgencia,@pmVrServicio,@pmVrImpuesto,@pmVrSeguro,@pmVrOtros,@pmTipOrden,@pmNumOrden,@pmIdCiaOrden,@pmFechaVence,@pmEstado,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmObservacion,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmCodTarRtc,@pmCodTarCom,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmIdLocEnv,@pmIdForma,@pmIdPlazo,@pmIdBodega,@pmExcIva,@pmAutoret,@pmInc_Ret,@pmInc_Ica,@pmInc_Riv,@pmAutoIca,@pmDirEnvio,@pmDiasEntrega,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmNomCliente,@pmAgencia,@pmVendedor)
GO
