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].[paQryTraDevLegLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLegLta]
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].[paQryTraOrdenLiqDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPresAntFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPresAntFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPresAntLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPresAntLta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT L.TipDoc AS IdTipDoc,TipoDoc,L.Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,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,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.NumDocSop,L.CiaDocSop,L.VrTotal,L.VrCosto,L.VrAnticipo,L.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.Referencia2,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,TipEgr,Egreso,IdCiaEgr,NumCheque,VrConcCargos,VrConcDed,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario,Leyenda
--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
,L.CdCCosto,CCosto,L.CdSubCos,SubCosto
 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 
 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 LEFT JOIN CentroCosto AS CC ON L.CdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON L.CdSubCos=SC.IdSubCos
 WHERE L.TipDoc=@pmTipDoc AND L.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND L.IdCia=@pmIdCia
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,L.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.Referencia2,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 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,DV.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,LQ.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,LQ.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,LQ.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,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevLegLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT  Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Liquidacion,IdCiaDoc,FecDoc,Modalidad,TipMuc,Manifiesto,IdCiaMuc
,IdVehiculo,IdConductor,CDT.RazonSocial AS Conductor,IdPropietario,TP.RazonSocial AS Propietario,IdPoseedor,T.RazonSocial AS Poseedor
,NitEmpresa,NE.RazonSocial AS NomEmpresa,VrAnticipos,D.VrGastos,VrCombustible,VrDiferencia,VrOtrosCred,galsComb,PesoTotal,Cantidad,Volumen
,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraDevLeg AS D INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero INNER JOIN Terceros AS CDT ON D.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS TP ON D.IdPropietario=TP.IdTercero 
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia LEFT JOIN Terceros AS NE ON D.NitEmpresa=NE.IdTercero 
 WHERE TipDev=@pmTipDev AND  D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND D.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY D.IdCia,Devolucion
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,L.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,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,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.IdDestino,Localidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.NumDocSop,L.CiaDocSop
,L.VrTotal,L.VrCosto,L.VrAnticipo,L.VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrConcCargos,VrConcDed,VrNeto,L.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,Referencia,L.Referencia2,CxPagar,CdPlazo,FechaVence,UndMed,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,L.CdCCosto,L.CdSubCos
,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
 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 
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraPresAntFmt] @pmNumPstoIni INT,@pmNumPstoFin INT
 AS 
 SELECT P.NumPsto AS NumPrespto,Fecha,IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TipoRuta,CdRuta,Ruta
,FecInicio,FecFinal,P.VrGastos,VrComb,VrPeajes,VrAnticipo,CdCat,Catpeaje,CdTipoVeh,TipoVehiculo,TipoAfilVeh,CdTipoMot,TipoMotor
,CdCom,TipoComb,CdRango,DescripRango,ValorFijo,P.Observacion,P.Inactivo AS Inactvo,NitCliente,CL.RazonSocial AS Cliente,CdMercancia,DescripMcia
,CdTipoCarr,TipoCar,P.TipoServicio,Cantgalones,VrExtralegal,FechaCrea,FechaAct,P.IdUsuario AS CdUsuario,Usuario
,D.IdConcepto AS CdConcepto,Concepto,Descripcion,Cantidad,VrUnitario,VrTotal,TipoRubro,CdClase,ClasePeaje,NitTercero,T.RazonSocial,CdTipOper,TipoOperacion
 FROM Trn_TraPresAnt AS P INNER JOIN Trn_TraPresItems AS D ON P.NumPsto=D.NumPsto
 INNER JOIN Localidades AS CO ON  P.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON P.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Rutas AS R ON P.CdRuta=R.IdRuta LEFT JOIN TiposVeh AS TV ON P.CdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN PeajesCat AS CP ON P.CdCat=CP.IdCat LEFT JOIN TiposMot AS TM ON P.CdTipoMot=TM.IdTipoMot
 LEFT JOIN TiposFuel AS TF ON P.CdCom=TF.IdCom LEFT JOIN RangosPeso AS RP ON P.CdRango=RP.IdRango
 LEFT JOIN ConcDiversos AS C ON D.IdConcepto=C.IdConcepto LEFT JOIN PeajesClase AS PC ON D.CdClase=PC.IdClase
 LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero LEFT JOIN TiposOperac AS TP ON D.CdTipOper=TP.IdTipoOper
 LEFT JOIN Terceros AS CL ON P.NitCliente=CL.IdTercero LEFT JOIN Mercancias AS MC ON P.CdMercancia=MC.IdMercancia
 LEFT JOIN TiposCar AS TCA ON P.CdTipoCarr=TCA.IdCrceria
 WHERE P.NumPsto BETWEEN @pmNumPstoIni AND @pmNumPstoFin
 ORDER BY P.NumPsto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraPresAntLta] @pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null,@pmTipoAfilVeh VARCHAR(10)=Null
,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
 SELECT P.NumPsto AS NumPrespto,Fecha,IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TipoRuta,CdRuta,Ruta
,FecInicio,FecFinal,P.VrGastos,VrComb,VrPeajes,VrAnticipo,CdCat,Catpeaje,CdTipoVeh,TipoVehiculo,TipoAfilVeh,CdTipoMot,TipoMotor
,CdCom,TipoComb,CdRango,DescripRango,ValorFijo,P.Observacion,P.Inactivo AS Inactvo,NitCliente,CL.RazonSocial AS Cliente,CdMercancia,DescripMcia
,CdTipoCarr,TipoCar,Cantgalones,VrExtralegal,FechaCrea,FechaAct,P.IdUsuario AS CdUsuario,Usuario
,D.IdConcepto AS CdConcepto,Concepto,Descripcion,Cantidad,VrUnitario,VrTotal,TipoRubro,CdClase,ClasePeaje,NitTercero,T.RazonSocial,CdTipOper,TipoOperacion
 FROM Trn_TraPresAnt AS P INNER JOIN Trn_TraPresItems AS D ON P.NumPsto=D.NumPsto
 INNER JOIN Localidades AS CO ON  P.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON P.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Rutas AS R ON P.CdRuta=R.IdRuta LEFT JOIN TiposVeh AS TV ON P.CdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN PeajesCat AS CP ON P.CdCat=CP.IdCat LEFT JOIN TiposMot AS TM ON P.CdTipoMot=TM.IdTipoMot
 LEFT JOIN TiposFuel AS TF ON P.CdCom=TF.IdCom LEFT JOIN RangosPeso AS RP ON P.CdRango=RP.IdRango
 LEFT JOIN ConcDiversos AS C ON D.IdConcepto=C.IdConcepto LEFT JOIN PeajesClase AS PC ON D.CdClase=PC.IdClase
 LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero LEFT JOIN TiposOperac AS TP ON D.CdTipOper=TP.IdTipoOper
 LEFT JOIN Terceros AS CL ON P.NitCliente=CL.IdTercero LEFT JOIN Mercancias AS MC ON P.CdMercancia=MC.IdMercancia
 LEFT JOIN TiposCar AS TCA ON P.CdTipoCarr=TCA.IdCrceria
 WHERE IdOrigen LIKE ISNULL(@pmIdOrigen,'%') AND IdDestino LIKE ISNULL(@pmIdDestino,'%') AND TipoAfilVeh LIKE ISNULL(@pmTipoAfilVeh,'%')
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY P.NumPsto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqRel] @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,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,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,L.VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrConcCargos,VrConcDed,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.Referencia2,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.TipEgr,L.Egreso,L.IdCiaEgr,L.NumCheque,ISNULL(VC.VrTotalAbo,0) AS CxpAbonado,ISNULL(VC.VrSaldo,0) AS CxpSaldo
,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
--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,L.CdCCosto,CCosto,L.CdSubCos,SubCosto
 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 
 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
 LEFT JOIN CentroCosto AS CC ON L.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON L.CdSubCos=SC.IdSubCos
 --Subconsulta egresos-pagos
 LEFT JOIN (SELECT TipFac,Factura,IdCia,IdProveedor,Documento,SUM(VrFactura) AS VrTotalFac,SUM(VrAbonado) AS VrTotalAbo,SUM(VrFactura-VrAbonado) AS VrSaldo FROM Trn_ComFactura
 WHERE TipFac=@pmTipDoc AND TipDoc=@pmTipDoc AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 GROUP BY TipFac,Factura,IdCia,IdProveedor,Documento) AS VC ON L.TipDoc=VC.TipFac AND L.Liquidacion=VC.Documento AND L.IdCia=VC.IdCia
 -- FIN Subconsulta
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO