ALTER TABLE Trn_TraGuias ADD EstLiquida INT DEFAULT(0) NOT NULL,TipLiq VARCHAR(3),NumLiquida INT DEFAULT(0) NOT NULL,IdCiaLiq CHAR(2)
GO
ALTER TABLE Trn_TraLiqViajes ADD VrGuiasCe MONEY DEFAULT(0) NOT NULL,VrGuiasSeg MONEY DEFAULT(0) NOT NULL,VrGuiasOtr MONEY DEFAULT(0) NOT NULL
GO
CREATE TABLE Trn_TraOrdenGuias ( 
TipDoc VARCHAR(3) DEFAULT ('ODP') NOT NULL,
NumLiquida INT DEFAULT ((0)) NOT NULL,
IdCia CHAR(2) DEFAULT ('01') NOT NULL,
Item INT DEFAULT ((0)) NOT NULL,
TipGui VARCHAR(3) DEFAULT ('GTC') NOT NULL,
NumGuia INT DEFAULT ((0)) NOT NULL,
IdCiaGuia CHAR(2) NOT NULL,
TipRem VARCHAR(3) DEFAULT ('RMT') NOT NULL,
NumRemesa INT DEFAULT ((0)) NOT NULL,
IdCiaRem CHAR(2) DEFAULT ('01') NOT NULL,
VrLiquidado MONEY DEFAULT ((0)) NOT NULL,
VrSeguro MONEY DEFAULT ((0)) NOT NULL,
VrCargos MONEY DEFAULT ((0)) NOT NULL,
Cantidad DECIMAL(14,4) DEFAULT ((0)) NOT NULL,
PesoTotal DECIMAL(14,4) DEFAULT ((0)) NOT NULL
 CONSTRAINT PK_Trn_TraOrdenGuias PRIMARY KEY CLUSTERED (TipDoc,NumLiquida,IdCia,Item),
 CONSTRAINT CK_Trn_TraOrdenGuiasIdCia CHECK ((len([IdCia])>(0))),
 CONSTRAINT CK_Trn_TraOrdenGuiasIdCiaGuia CHECK ((len([IdCiaGuia])>(0))),
 CONSTRAINT CK_Trn_TraOrdenGuiasIdCiaRem CHECK ((len([IdCiaRem])>(0))),
 CONSTRAINT CK_Trn_TraOrdenGuiasTipDoc CHECK ((len([TipDoc])>(0))),
 CONSTRAINT CK_Trn_TraOrdenGuiasTipGui CHECK ((len([TipGui])>(0))),
 CONSTRAINT CK_Trn_TraOrdenGuiasTipRem CHECK ((len([TipRem])>(0))))
GO
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('LGA',13,'GUIAS CONTRAENTREGA','C')
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('DLG',13,'GUIAS CONTRAENTREGA','D')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraGuias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraLiqViajes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraLiqViajes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuiasFco]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuiasFco]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuiasFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuiasFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraGuias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraLiqViajes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraLiqViajes]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT LQ.TipDoc AS TipoLiq,TipoDoc,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc
,LQ.IdVehiculo AS PlacaVeh,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,LQ.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,LQ.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Referencia,DescMcias,LiqMixto,Num_Ppto,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,VrGuiasCe,VrGuiasSeg,VrGuiasOtr,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,Estado,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario,Leyenda
,Item,D.IdConcepto AS DetCodConc,CG.Concepto AS DetNomConc,DescConcepto,D.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito,D.IdTercero AS NitTercero,N.RazonSocial AS NomTercero
,CdCCosto,CCosto,CdSubCos,SubCosto,D.RubroConc,VrBase,TarifBase,CantComb,TipFac,Factura,IdCiaFac,ItemFac,FecVence,RefConcep,D.TipSop,D.NumSoporte,D.IdCiaSop
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraLiqViajes AS LQ  INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON LQ.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON LQ.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON LQ.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON LQ.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON LQ.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS ETR ON LQ.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraLiqDetalle AS D ON LQ.TipDoc=D.TipDoc AND LQ.Liquidacion=D.Liquidacion AND LQ.IdCia=D.IdCia
 LEFT JOIN ConcDiversos AS CG ON D.IdConcepto=CG.IdConcepto LEFT JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS N ON  D.IdTercero=N.IdTercero
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 WHERE LQ.TipDoc=@pmTipDoc AND LQ.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND LQ.IdCia=@pmIdCia
 ORDER BY LQ.Liquidacion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesRel] @pmTipDoc VARCHAR(3),@pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT LQ.TipDoc AS TipoLiq,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc
,LQ.IdVehiculo AS PlacaVeh,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,LQ.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,LQ.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Referencia,DescMcias,LiqMixto,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,Num_Ppto,VrGuiasCe,VrGuiasSeg,VrGuiasOtr,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraLiqViajes AS LQ INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON LQ.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON LQ.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON LQ.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS ETR ON LQ.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 WHERE LQ.TipDoc=@pmTipDoc  AND LQ.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND LQ.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND LQ.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND LQ.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND LQ.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND LQ.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY LQ.IdCia,LQ.Liquidacion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT LQ.TipDoc AS TipoLiq,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc
,LQ.IdVehiculo AS PlacaVeh,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,LQ.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,LQ.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Referencia,DescMcias,LiqMixto,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,Num_Ppto,VrGuiasCe,VrGuiasSeg,VrGuiasOtr,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario
--detalles
,Item,D.IdConcepto AS DetCodConc,CG.Concepto AS DetNomConc,DescConcepto,D.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito,D.IdTercero AS NitTercero,N.RazonSocial AS NomTercero
,CdCCosto,CCosto,CdSubCos,SubCosto,D.RubroConc,VrBase,TarifBase,CantComb,TipFac,Factura,IdCiaFac,ItemFac,FecVence,RefConcep
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraLiqViajes AS LQ INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON LQ.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON LQ.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON LQ.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS ETR ON LQ.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraLiqDetalle AS D ON LQ.TipDoc=D.TipDoc AND LQ.Liquidacion=D.Liquidacion AND LQ.IdCia=D.IdCia
 LEFT JOIN ConcDiversos AS CG ON D.IdConcepto=CG.IdConcepto LEFT JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS N ON  D.IdTercero=N.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 WHERE LQ.TipDoc=@pmTipDoc  AND LQ.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND LQ.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND LQ.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND LQ.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND LQ.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND LQ.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY LQ.IdCia,LQ.Liquidacion
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmModalidad VARCHAR(10),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmNomRemitente VARCHAR(150),@pmIdDestinatario VARCHAR(16),@pmNomDestinatario VARCHAR(150),@pmDirOrigen VARCHAR(250),@pmIdLocOrigen VARCHAR(8),@pmDirDestino VARCHAR(250),@pmIdLocDestino VARCHAR(8),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(150),@pmTelRemitente VARCHAR(30),@pmemlRemitente VARCHAR(150),@pmTelDestinatario VARCHAR(30),@pmemlDestinatario VARCHAR(150),@pmIdResponsable VARCHAR(16),@pmNomResponsable VARCHAR(150),@pmVrTotal MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmVrCargos MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmTipRem VARCHAR(3)
,@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFac VARCHAR(3),@pmNumFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFac SMALLDATETIME,@pmCdConcepto VARCHAR(4),@pmCdForma VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmEstLiquida INT,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmIdCiaLiq CHAR(2),@pmOrigenAdd VARCHAR(10),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraGuias (TipDoc,NumGuia,IdCia,Fecha,FecDespacho,FecVigencia,Modalidad,IdCliente,IdAgencia,IdRemitente,NomRemitente,IdDestinatario,NomDestinatario,DirOrigen,IdLocOrigen,DirDestino,IdLocDestino,NomContacto,TelContacto,emlContacto,TelRemitente,emlRemitente,TelDestinatario,emlDestinatario,IdResponsable,NomResponsable,VrTotal,VrDeclarado,VrSeguro,VrCargos,Cantidad,PesoTotal,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFac,NumFactura,IdCiaFac,FechaFac,EstLiquida,TipLiq,NumLiquida,IdCiaLiq,CdConcepto,CdForma,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,FechaCrea,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmNumGuia,@pmIdCia,@pmFecha,@pmFecDespacho,@pmFecVigencia,@pmModalidad,@pmIdCliente,@pmIdAgencia,@pmIdRemitente,@pmNomRemitente,@pmIdDestinatario,@pmNomDestinatario,@pmDirOrigen,@pmIdLocOrigen,@pmDirDestino,@pmIdLocDestino,@pmNomContacto,@pmTelContacto,@pmemlContacto,@pmTelRemitente,@pmemlRemitente,@pmTelDestinatario,@pmemlDestinatario,@pmIdResponsable,@pmNomResponsable,@pmVrTotal,@pmVrDeclarado,@pmVrSeguro,@pmVrCargos,@pmCantidad,@pmPesoTotal,@pmTipRem,@pmNumRemesa,@pmIdCiaRem,@pmEstFactura,@pmTipFac,@pmNumFactura,@pmIdCiaFac,@pmFechaFac,@pmEstLiquida,@pmTipLiq,@pmNumLiquida,@pmIdCiaLiq,@pmCdConcepto,@pmCdForma,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmFechaCrea,@pmIdCiaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumGuia,IdCia,Fecha,FecDespacho,FecVigencia,Modalidad,IdCliente,IdAgencia,IdRemitente,NomRemitente,IdDestinatario,NomDestinatario,DirOrigen,IdLocOrigen,DirDestino,IdLocDestino,NomContacto,TelContacto,emlContacto,TelRemitente,emlRemitente,TelDestinatario,emlDestinatario
,IdResponsable,NomResponsable,VrTotal,VrDeclarado,VrSeguro,VrCargos,Cantidad,PesoTotal,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFac,NumFactura,IdCiaFac,FechaFac,EstLiquida,TipLiq,NumLiquida,IdCiaLiq,CdConcepto,CdForma,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,FechaCrea,FechaModif,IdCiaCrea,IdUsuario
 FROM Trn_TraGuias
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmModalidad VARCHAR(10),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmNomRemitente VARCHAR(150),@pmIdDestinatario VARCHAR(16),@pmNomDestinatario VARCHAR(150),@pmDirOrigen VARCHAR(250),@pmIdLocOrigen VARCHAR(8),@pmDirDestino VARCHAR(250),@pmIdLocDestino VARCHAR(8),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(150),@pmTelRemitente VARCHAR(30),@pmemlRemitente VARCHAR(150),@pmTelDestinatario VARCHAR(30),@pmemlDestinatario VARCHAR(150),@pmIdResponsable VARCHAR(16),@pmNomResponsable VARCHAR(150),@pmVrTotal MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmVrCargos MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmTipRem VARCHAR(3)
,@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFac VARCHAR(3),@pmNumFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFac SMALLDATETIME,@pmCdConcepto VARCHAR(4),@pmCdForma VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmEstLiquida INT,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmIdCiaLiq CHAR(2),@pmFechaModif SMALLDATETIME
 AS 
 UPDATE Trn_TraGuias SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,FecVigencia=@pmFecVigencia,Modalidad=@pmModalidad,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdRemitente=@pmIdRemitente,NomRemitente=@pmNomRemitente,IdDestinatario=@pmIdDestinatario,NomDestinatario=@pmNomDestinatario,DirOrigen=@pmDirOrigen,IdLocOrigen=@pmIdLocOrigen,DirDestino=@pmDirDestino,IdLocDestino=@pmIdLocDestino,NomContacto=@pmNomContacto,TelContacto=@pmTelContacto,emlContacto=@pmemlContacto,TelRemitente=@pmTelRemitente,emlRemitente=@pmemlRemitente,TelDestinatario=@pmTelDestinatario,emlDestinatario=@pmemlDestinatario,IdResponsable=@pmIdResponsable,NomResponsable=@pmNomResponsable,VrTotal=@pmVrTotal,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,VrCargos=@pmVrCargos,Cantidad=@pmCantidad,PesoTotal=@pmPesoTotal,TipRem=@pmTipRem,NumRemesa=@pmNumRemesa,IdCiaRem=@pmIdCiaRem
,EstFactura=@pmEstFactura,TipFac=@pmTipFac,NumFactura=@pmNumFactura,IdCiaFac=@pmIdCiaFac,FechaFac=@pmFechaFac,EstLiquida=@pmEstLiquida,TipLiq=@pmTipLiq,NumLiquida=@pmNumLiquida,IdCiaLiq=@pmIdCiaLiq
,CdConcepto=@pmCdConcepto,CdForma=@pmCdForma,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FechaModif=@pmFechaModif
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuiasFco] @pmTipDoc VARCHAR(3),@pmNumGuiaIni INT,@pmNumGuiaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT G.TipDoc,TipoDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecDespacho,G.FecVigencia,G.Modalidad,G.IdCliente,T.RazonSocial AS Nom_Cliente,G.IdAgencia,A.Agencia AS NomAgencia
,G.IdRemitente,G.NomRemitente,G.DirOrigen,G.TelRemitente,G.emlRemitente,G.IdDestinatario,G.NomDestinatario,G.DirDestino,G.TelDestinatario,G.emlDestinatario
,G.IdLocOrigen,LO.Localidad AS CiudadOrigen,DP.Departamento AS DptoOrigen,G.IdLocDestino,LD.Localidad AS CiudadDestino,DD.Departamento AS DptoDestino,G.NomContacto,G.TelContacto,G.emlContacto
,G.IdResponsable,G.NomResponsable,G.VrTotal,G.VrDeclarado AS VrTotDeclarado,G.VrSeguro AS VrTotSeguro,G.VrCargos,G.Cantidad AS Cant_Total,G.PesoTotal,G.TipRem,G.NumRemesa,G.IdCiaRem,G.EstFactura,G.TipFac,G.NumFactura,G.IdCiaFac,G.FechaFac
,G.EstLiquida,G.TipLiq,G.NumLiquida,G.IdCiaLiq,G.CdConcepto,C.Concepto,G.CdForma,FormaPago,G.TipCom,TipoCom,G.Comprobante,G.IdCiaCom,G.Anulado,G.FecDev,G.Observacion,G.IdEstado,ED.Estado
,G.FechaCrea,G.FechaModif,G.IdCiaCrea,G.IdUsuario,Usuario,G.OrigenAdd
--Detalle
,D.Item,D.IdMercancia,D.DescripcionMcia,D.Cantidad,D.PesoNeto,D.UndMed,UMP.Unidad AS UmPeso,D.dmsAlto,D.dmsAncho,D.dmsLargo,D.Volumen,D.UndVol,D.IdUnd,UM.Unidad AS UndMedida,D.IdEmp,EP.Empaque
,D.VrUnitario,D.VrDeclarado,D.VrSeguro,D.TarifSeguro,D.UndTarifa,D.Remision,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.CantFactura,D.VrFactura,D.CdConcepto AS CdConcFactura,DC.Concepto AS ConcFactura
--datos del tercero
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
--enc fact cont
,FE.EstadoFE,FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia AS FecVigNum,FE.CUFE_QR AS QR,FE.FechaValidacion
 FROM Trn_TraGuias AS G INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON G.TipDoc=TD.IdDoc
 INNER JOIN EstadoGuia AS ED ON G.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraGuiaMcia AS D ON G.TipDoc=D.TipDoc AND G.NumGuia=D.NumGuia AND G.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS LO ON G.IdLocOrigen=LO.IdLocal INNER JOIN Departamentos AS DP ON LO.IdDep=DP.IdDep
 INNER JOIN Localidades AS LD ON G.IdLocDestino=LD.IdLocal INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep
 LEFT JOIN Conceptos AS C ON G.CdConcepto=C.IdConcepto LEFT JOIN TiposCom AS TC ON G.TipCom=TC.IdCom
 LEFT JOIN Agencias AS A ON G.IdAgencia=A.IdAgencia LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal
 LEFT JOIN Formaspago AS F ON G.CdForma=F.IdForma LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 LEFT JOIN ConcDiversos AS DC ON D.CdConcepto=DC.IdConcepto 
 LEFT JOIN Trn_Face AS FE ON G.TipFac=FE.TipDoc AND G.NumFactura=FE.Documento AND G.IdCiaFac=FE.IdCia
 WHERE G.TipDoc=@pmTipDoc AND G.NumGuia BETWEEN @pmNumGuiaIni AND @pmNumGuiaFin AND G.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuiasFmt] @pmTipDoc VARCHAR(3),@pmNumGuiaIni INT,@pmNumGuiaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT G.TipDoc,TipoDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecDespacho,G.FecVigencia,G.Modalidad,G.IdCliente,T.RazonSocial AS Nom_Cliente,G.IdAgencia,A.Agencia AS NomAgencia
,G.IdRemitente,G.NomRemitente,G.DirOrigen,G.TelRemitente,G.emlRemitente,G.IdDestinatario,G.NomDestinatario,G.DirDestino,G.TelDestinatario,G.emlDestinatario
,G.IdLocOrigen,LO.Localidad AS CiudadOrigen,DP.Departamento AS DptoOrigen,G.IdLocDestino,LD.Localidad AS CiudadDestino,DD.Departamento AS DptoDestino,G.NomContacto,G.TelContacto,G.emlContacto
,G.IdResponsable,G.NomResponsable,G.VrTotal,G.VrDeclarado AS VrTotDeclarado,G.VrSeguro AS VrTotSeguro,G.VrCargos,G.Cantidad AS Cant_Total,G.PesoTotal,G.TipRem,G.NumRemesa,G.IdCiaRem,G.EstFactura,G.TipFac,G.NumFactura,G.IdCiaFac,G.FechaFac
,G.EstLiquida,G.TipLiq,G.NumLiquida,G.IdCiaLiq,G.CdConcepto,C.Concepto,G.CdForma,FormaPago,G.TipCom,TipoCom,G.Comprobante,G.IdCiaCom,G.Anulado,G.FecDev,G.Observacion,G.IdEstado,ED.Estado
,G.FechaCrea,G.FechaModif,G.IdCiaCrea,G.IdUsuario,Usuario,G.OrigenAdd
--Detalle
,D.Item,D.IdMercancia,D.DescripcionMcia,D.Cantidad,D.PesoNeto,D.UndMed,UMP.Unidad AS UmPeso,D.dmsAlto,D.dmsAncho,D.dmsLargo,D.Volumen,D.UndVol,D.IdUnd,UM.Unidad AS UndMedida,D.IdEmp,EP.Empaque--,D.IdNat,D.IdTmcia,D.IdMnjo
,D.VrUnitario,D.VrDeclarado,D.VrSeguro,D.TarifSeguro,D.UndTarifa,D.Remision,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.CantFactura,D.VrFactura,D.CdConcepto AS CdConcFactura,DC.Concepto AS ConcFactura
--datos del tercero
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
 FROM Trn_TraGuias AS G INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON G.TipDoc=TD.IdDoc
 INNER JOIN EstadoGuia AS ED ON G.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraGuiaMcia AS D ON G.TipDoc=D.TipDoc AND G.NumGuia=D.NumGuia AND G.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS LO ON G.IdLocOrigen=LO.IdLocal INNER JOIN Departamentos AS DP ON LO.IdDep=DP.IdDep
 INNER JOIN Localidades AS LD ON G.IdLocDestino=LD.IdLocal INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep
 LEFT JOIN Conceptos AS C ON G.CdConcepto=C.IdConcepto LEFT JOIN TiposCom AS TC ON G.TipCom=TC.IdCom
 LEFT JOIN Agencias AS A ON G.IdAgencia=A.IdAgencia LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal
 LEFT JOIN Formaspago AS F ON G.CdForma=F.IdForma LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 LEFT JOIN ConcDiversos AS DC ON D.CdConcepto=DC.IdConcepto 
 WHERE G.TipDoc=@pmTipDoc AND G.NumGuia BETWEEN @pmNumGuiaIni AND @pmNumGuiaFin AND G.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT  Liquidacion,L.IdCia AS CdCia,Compania,Fecha,L.IdConcepto AS CdConcepto,Concepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc,IdVehiculo,IdConductor,CDT.RazonSocial AS Conductor
,IdPropietario,TP.RazonSocial AS Propietario,IdPoseedor,T.RazonSocial AS Poseedor,NitEmpresa,NE.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia
,galsComb,PesoTotal,Cantidad,Volumen,IdRuta,IdLocOrigen,LO.Localidad AS CiudadOrigen,IdLocDestino,LD.Localidad AS CiudadDestino,Referencia,DescMcias,LiqMixto,kmtInicial,kmtFinal
,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,Num_Ppto,VrGuiasCe,VrGuiasSeg,VrGuiasOtr,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,L.Observacion AS Observ,L.IdEstado AS CdEstado
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,L.IdUsuario AS Cdusuario,Usuario
 FROM Trn_TraLiqViajes AS L  INNER JOIN Terceros AS T ON L.IdPoseedor=T.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS TP ON L.IdPropietario=TP.IdTercero 
 INNER JOIN Conceptos AS C ON L.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario
 INNER JOIN Companias AS CI ON L.IdCia=CI.IdCia LEFT JOIN Terceros AS NE ON L.NitEmpresa=NE.IdTercero 
 LEFT JOIN Localidades AS LO ON L.IdLocOrigen=LO.IdLocal LEFT JOIN Localidades AS LD ON L.IdLocDestino=LD.IdLocal
 WHERE TipDoc=@pmTipDoc   AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND L.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND L.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
  AND L.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 ORDER BY L.IdCia,Liquidacion
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajes] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor
,NitEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen,IdRuta,IdLocOrigen,IdLocDestino,Referencia,DescMcias,kmtInicial,kmtFinal
,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,LiqMixto,Num_Ppto,VrGuiasCe,VrGuiasSeg,VrGuiasOtr
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraLiqViajes
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraLiqViajes] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmModalidad VARCHAR(10),@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmFechaMuc SMALLDATETIME,@pmIdVehiculo VARCHAR(10)
,@pmIdConductor VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmNitEmpresa VARCHAR(16),@pmVrAnticipos MONEY,@pmVrGastos MONEY,@pmVrCombustible MONEY,@pmVrDiferencia MONEY,@pmgalsComb DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmCantidad DECIMAL(14,4),@pmVolumen DECIMAL(14,4)
,@pmIdRuta VARCHAR(4),@pmIdLocOrigen VARCHAR(8),@pmIdLocDestino VARCHAR(8),@pmReferencia VARCHAR(50),@pmDescMcias VARCHAR(150),@pmkmtInicial DECIMAL(14,4),@pmkmtFinal DECIMAL(14,4),@pmEstFactura INT,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmFecDocRef SMALLDATETIME
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrOtrosCred MONEY,@pmLiqMixto BIT,@pmNum_Ppto INT,@pmVrGuiasCe MONEY,@pmVrGuiasSeg MONEY,@pmVrGuiasOtr MONEY
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraLiqViajes (TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor,NitEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen,IdRuta,IdLocOrigen,IdLocDestino,Referencia,DescMcias
,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,LiqMixto,Num_Ppto,VrGuiasCe,VrGuiasSeg,VrGuiasOtr)
 VALUES (@pmTipDoc,@pmLiquidacion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmModalidad,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmFechaMuc,@pmIdVehiculo,@pmIdConductor,@pmIdPropietario,@pmIdPoseedor,@pmNitEmpresa,@pmVrAnticipos,@pmVrGastos,@pmVrCombustible,@pmVrOtrosCred,@pmVrDiferencia,@pmgalsComb
,@pmPesoTotal,@pmCantidad,@pmVolumen,@pmIdRuta,@pmIdLocOrigen,@pmIdLocDestino,@pmReferencia,@pmDescMcias,@pmkmtInicial,@pmkmtFinal,@pmEstFactura,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmFecDocRef,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmLiqMixto,@pmNum_Ppto,@pmVrGuiasCe,@pmVrGuiasSeg,@pmVrGuiasOtr)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraLiqViajes] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmModalidad VARCHAR(10),@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmFechaMuc SMALLDATETIME,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmNitEmpresa VARCHAR(16),@pmVrAnticipos MONEY,@pmVrGastos MONEY,@pmVrCombustible MONEY
,@pmVrDiferencia MONEY,@pmgalsComb DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmCantidad DECIMAL(14,4),@pmVolumen DECIMAL(14,4),@pmIdRuta VARCHAR(4),@pmIdLocOrigen VARCHAR(8),@pmIdLocDestino VARCHAR(8),@pmReferencia VARCHAR(50),@pmDescMcias VARCHAR(150),@pmkmtInicial DECIMAL(14,4),@pmkmtFinal DECIMAL(14,4),@pmEstFactura INT
,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmFecDocRef SMALLDATETIME,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrOtrosCred MONEY,@pmLiqMixto BIT,@pmNum_Ppto INT,@pmVrGuiasCe MONEY,@pmVrGuiasSeg MONEY,@pmVrGuiasOtr MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraLiqViajes SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,Modalidad=@pmModalidad,TipMuc=@pmTipMuc,Manifiesto=@pmManifiesto,IdCiaMuc=@pmIdCiaMuc,FechaMuc=@pmFechaMuc,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,IdPropietario=@pmIdPropietario,IdPoseedor=@pmIdPoseedor,NitEmpresa=@pmNitEmpresa,VrAnticipos=@pmVrAnticipos,VrGastos=@pmVrGastos,VrCombustible=@pmVrCombustible,VrDiferencia=@pmVrDiferencia,galsComb=@pmgalsComb
,PesoTotal=@pmPesoTotal,Cantidad=@pmCantidad,Volumen=@pmVolumen,IdRuta=@pmIdRuta,IdLocOrigen=@pmIdLocOrigen,IdLocDestino=@pmIdLocDestino,Referencia=@pmReferencia,DescMcias=@pmDescMcias,kmtInicial=@pmkmtInicial,kmtFinal=@pmkmtFinal,EstFactura=@pmEstFactura,TipDocRef=@pmTipDocRef,NumDocRef=@pmNumDocRef,IdCiaRef=@pmIdCiaRef,FecDocRef=@pmFecDocRef,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom
,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,VrOtrosCred=@pmVrOtrosCred,LiqMixto=@pmLiqMixto,Num_Ppto=@pmNum_Ppto,VrGuiasCe=@pmVrGuiasCe,VrGuiasSeg=@pmVrGuiasSeg,VrGuiasOtr=@pmVrGuiasOtr,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO