ALTER TABLE Trn_MudOrdenConc ADD PlacaVehic VARCHAR(10),TarifaPago MONEY DEFAULT(0) NOT NULL
GO
ALTER TABLE CentroCosto ADD CodCias VARCHAR(500)
GO
ALTER TABLE ProdMcias ADD IvaDetCombo BIT DEFAULT(0) NOT NULL, LtaBaseIva INT DEFAULT(0) NOT NULL
GO
ALTER TABLE Trn_Requisicion ADD NivelAprob INT DEFAULT(0) NOT NULL
GO
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('CVO','CONTROL DE NUMERO DE VOUCHER EN VALES CREDITO DE COMBUSTIBLE','BOOLEAN','0',5,'MAIN')
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('VRQ','VIGENCIA PREDETERMINADA EN LA REQUISICION (NORMAL,PERMANENTE)','VARCHAR','PERMANENTE',5,'MAIN')
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('ARN','HABILITAR APROBACION DE REQUISICIONES POR NIVELES','BOOLEAN','0',5,'MAIN')
GO
CREATE TABLE Trn_MudContFact ( 
TipDoc VARCHAR(3) DEFAULT ('CAB') NOT NULL,
NumContrato INT DEFAULT ((0)) NOT NULL,
IdCia CHAR(2) NOT NULL,
Item INT DEFAULT ((0)) NOT NULL,
TipFac VARCHAR(3) DEFAULT ('FCR') NOT NULL,
NumFactura INT DEFAULT ((0)) NOT NULL,
IdCiaFac CHAR(2) DEFAULT ('01') NOT NULL,
Fecha SMALLDATETIME NOT NULL,
ValorTotal MONEY DEFAULT ((0)) NOT NULL,
Anulado BIT DEFAULT ((0)) NOT NULL
 CONSTRAINT PK_Trn_MudContFact PRIMARY KEY CLUSTERED (TipDoc,NumContrato,IdCia,Item),
 CONSTRAINT CK_Trn_MudContFactIdCia CHECK ((len([IdCia])>(0))),
 CONSTRAINT CK_Trn_MudContFactIdCiaFac CHECK ((len([IdCiaFac])>(0))),
 CONSTRAINT CK_Trn_MudContFactTipDoc CHECK ((len([TipDoc])>(0))),
 CONSTRAINT CK_Trn_MudContFactTipFac CHECK ((len([TipFac])>(0))))
GO
ALTER TABLE Trn_MudContFact ADD 
 CONSTRAINT FK_Trn_MudContFact_Trn_MudContratos FOREIGN KEY (TipDoc,NumContrato,IdCia) REFERENCES [dbo].[Trn_MudContratos] (TipDoc,NumContrato,IdCia)

INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMMUDFACT','TRADOC','MUD',3,'Facturación de Contratos','FRMMUDFACT','SSSSSSSSSSSSSSSSNS',0,'')
GO
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMMUDFACT','FEC','Modificar Fecha de Factura')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDRMT','PED','Permitir Incluir pedidos con exceso de peso')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuFisCuentasAge_Veh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuFisCuentasAge_Veh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuNiifCuentasAge_Veh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuNiifCuentasAge_Veh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudOrdenConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudOrdenConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ_Cr]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro,O.IdVend,VN.RazonSocial AS NomVendedor
,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,EstadoBod,O.CdBodega,FechaIngBod,FechaRetBod,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuFisCuentasAge_Veh]  @pmIdVehiculo VARCHAR(10),@pmIdVehiculoDos VARCHAR(10),@pmNewVehiculo VARCHAR(10)
 AS 
 IF EXISTS (SELECT * FROM AcuFisCuentasAge WHERE (CodAgncia=@pmIdVehiculo OR CodAgncia=@pmIdVehiculoDos) AND TipoAcum='NV')
  INSERT INTO AcuFisCuentasAge (nAnno,nMes,IdCia,IdCuenta,IdTercero,CodAgncia,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
  SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,@pmNewVehiculo,TipoAcum,SUM(SaldoAnterior),SUM(TotalDebitos),SUM(TotalCreditos)
  FROM AcuFisCuentasAge WHERE (CodAgncia=@pmIdVehiculo OR CodAgncia=@pmIdVehiculoDos) AND TipoAcum='NV'
  GROUP BY nAnno,nMes,IdCia,IdCuenta,IdTercero,TipoAcum
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuNiifCuentasAge_Veh]  @pmIdVehiculo VARCHAR(10),@pmIdVehiculoDos VARCHAR(10),@pmNewVehiculo VARCHAR(10)
 AS 
 IF EXISTS (SELECT * FROM AcuNiifCuentasAge WHERE (CodAgncia=@pmIdVehiculo OR CodAgncia=@pmIdVehiculoDos) AND TipoAcum='NV')
  INSERT INTO AcuNiifCuentasAge (nAnno,nMes,IdCia,IdCuenta,IdTercero,CodAgncia,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
  SELECT nAnno,nMes,IdCia,IdCuenta,IdTercero,@pmNewVehiculo,TipoAcum,SUM(SaldoAnterior),SUM(TotalDebitos),SUM(TotalCreditos)
  FROM AcuNiifCuentasAge WHERE (CodAgncia=@pmIdVehiculo OR CodAgncia=@pmIdVehiculoDos) AND TipoAcum='NV'
  GROUP BY nAnno,nMes,IdCia,IdCuenta,IdTercero,TipoAcum
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudOrdenConc] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@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),@pmIndFactItem INT,@pmNumDocFac VARCHAR(20),@pmTarifSeguro DECIMAL(14,4),@pmCodTarSeg VARCHAR(4),@pmPlacaVehic VARCHAR(10),@pmTarifaPago MONEY
 AS 
 INSERT INTO Trn_MudOrdenConc (TipDoc,NumOrden,IdCia,Item,IdConcepto,Descripcion,ValorTotal,TarifaIva,TipoConc,TipoRubro,TipoReg,CdCuenta,NitTercero,Referencia,NumDocRef,CodTarIva,IndFactItem,NumDocFac,TarifSeguro,CodTarSeg,PlacaVehic,TarifaPago)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmItem,@pmIdConcepto,@pmDescripcion,@pmValorTotal,@pmTarifaIva,@pmTipoConc,@pmTipoRubro,@pmTipoReg,@pmCdCuenta,@pmNitTercero,@pmReferencia,@pmNumDocRef,@pmCodTarIva,@pmIndFactItem,@pmNumDocFac,@pmTarifSeguro,@pmCodTarSeg,@pmPlacaVehic,@pmTarifaPago)
GO