if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLegFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLegFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLegRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLegRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqDetalleFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqDetalleFmt]
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].[paQryTraLiqViajesRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqDet]
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,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevLegFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,DV.IdCia AS CdCia,Compania,Fecha,DV.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,DV.TipDoc AS TipoLiq,DV.Liquidacion AS NumLiquida,IdCiaDoc,FecDoc,Modalidad,TipMuc,Manifiesto,IdCiaMuc
,DV.IdVehiculo AS PlacaVeh,DV.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,DV.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,DV.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,DV.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrDiferencia,VrOtrosCred,galsComb,PesoTotal,Cantidad,Volumen
,ModdDev,TipCom,TipoCom,Comprobante,IdCiaCom,DV.Observacion AS Observ,TimeSys,IdCiaCrea,DV.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
--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_TraDevLeg AS DV  INNER JOIN Companias AS CN ON DV.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON DV.TipDev=TD.IdDoc
 INNER JOIN adm_Usuarios AS U ON DV.IdUsuario=U.IdUsuario INNER JOIN Conceptos AS C ON DV.IdConcepto=C.IdConcepto
 INNER JOIN Terceros AS  CDT ON DV.IdConductor=CDT.IdTercero  INNER JOIN Terceros AS NP ON DV.IdPropietario=NP.IdTercero 
 INNER JOIN Terceros AS T ON DV.IdPoseedor=T.IdTercero   INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON DV.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 DV.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON DV.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraLiqDetalle AS D ON DV.TipDoc=D.TipDoc AND DV.Liquidacion=D.Liquidacion AND DV.IdCiaDoc=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 DV.TipDev=@pmTipDev AND DV.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin  AND DV.IdCia=@pmIdCia
 ORDER BY  DV.Devolucion
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,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,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
--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].[paQryTraDevLegRelDet] @pmTipDev 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 TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,D.TipDoc AS TipLiq,D.Liquidacion AS NumLiquida,IdCiaDoc,FecDoc,D.Modalidad AS Modaldad
,D.TipMuc AS TipManif,D.Manifiesto AS NumManif,D.IdCiaMuc AS CdCiaManif,D.IdVehiculo AS PlacaVeh,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,D.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario
,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,D.VrAnticipos AS TotalAntcpos,D.VrGastos AS TotalGastos,D.VrCombustible AS TotalComb,D.VrDiferencia AS ValorDif
,D.VrOtrosCred AS TotalOtrosDed,D.galsComb AS CantGals,D.PesoTotal AS Peso_Total,D.Cantidad AS CantTotal,D.Volumen AS VolTotal,ModdDev,D.OrigenAdd AS OrigenCrea,D.TipCom AS CdTipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp
,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,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
--detales
,Item,DT.IdConcepto AS DetCodConc,CG.Concepto AS DetNomConc,DescConcepto,DT.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito,DT.IdTercero AS NitTercero,N.RazonSocial AS NomTercero
,CdCCosto,CCosto,CdSubCos,SubCosto,DT.RubroConc,VrBase,TarifBase,CantComb,TipFac,Factura,IdCiaFac,ItemFac,FecVence,RefConcep
--datos del vehículo
,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_TraDevLeg AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero   INNER JOIN Terceros AS NP ON D.IdPropietario=NP.IdTercero   
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON D.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
 INNER JOIN Trn_TraLiqViajes AS LQ ON  D.TipDoc=LQ.TipDoc AND D.Liquidacion=LQ.Liquidacion AND D.IdCiaDoc=LQ.IdCia
 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  
 LEFT JOIN Terceros AS ETR ON D.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraLiqDetalle AS DT ON D.TipDoc=DT.TipDoc AND D.Liquidacion=DT.Liquidacion AND  D.IdCiaDoc=DT.IdCia
 LEFT JOIN ConcDiversos AS CG ON DT.IdConcepto=CG.IdConcepto LEFT JOIN Puc AS P ON DT.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS N ON  DT.IdTercero=N.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN CentroCosto AS CC ON DT.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON DT.CdSubCos=SC.IdSubCos 
 WHERE D.TipDev=@pmTipDev  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY D.IdCia,D.Devolucion
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT L.TipDoc AS IdTipDoc,TipoDoc,L.Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,CT.Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,A.Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.Modalidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.Cantidad AS CantOrden,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.VrTotal,L.VrCosto,L.VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
,DC.Item,DC.IdConcepto AS DetCodConc,DescConcepto,DC.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito
,DC.IdTercero AS NitTercero,TDT.RazonSocial AS NomTercero,DC.CodAgncia,DC.CdCCosto,CCosto,DC.CdSubCos,SubCosto,pVehiculo
,DC.RubroConc,DC.VrBase,DC.TarifBase,DC.TipFac,DC.Factura,DC.IdCiaFac,ItemFac,DC.FecVence,RefConcep,CD.Concepto AS DetConcep
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Sys_TiposDoc AS TD ON L.TipDoc=TD.IdDoc INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN Trn_TraLiqDetalle AS DC ON L.TipDoc=DC.TipDoc AND L.Liquidacion=DC.Liquidacion AND L.IdCia=DC.IdCia
 INNER JOIN ConcDiversos AS CD ON DC.IdConcepto=CD.IdConcepto 
 LEFT JOIN Puc AS PC ON DC.IdCuenta=PC.IdCuenta LEFT JOIN Terceros AS TDT ON DC.IdTercero=TDT.IdTercero
 LEFT JOIN CentroCosto AS CC ON DC.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON DC.CdSubCos=SC.IdSubCos 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraLiqDetalleFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
AS 
 SELECT TipDoc,Liquidacion,D.IdCia,Compania,D.Item,D.IdConcepto AS CdConcepto,DescConcepto,D.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito
,D.IdTercero AS NitTercero,RazonSocial,D.CodAgncia,CdCCosto,CCosto,CdSubCos,SubCosto,pVehiculo
,D.RubroConc,D.VrBase,D.TarifBase,D.TipFac,D.Factura,D.IdCiaFac,ItemFac,D.FecVence,RefConcep,Concepto
 FROM Trn_TraLiqDetalle AS D INNER JOIN ConcDiversos AS C ON D.IdConcepto=C.IdConcepto 
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia
 INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta LEFT JOIN Terceros AS T ON D.IdTercero=T.IdTercero
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 WHERE D.TipDoc=@pmTipDoc AND D.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND D.IdCia=@pmIdCia
GO