--*** antes de ejecutar esta consulta, haga una copia de todos los procedimientos almacenados de la base de datos.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraMovLiquida]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraMovLiquida]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraMovLiquidaCau]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraMovLiquidaCau]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraMovLiquidaDcc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraMovLiquidaDcc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraMovLiquidaDop]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraMovLiquidaDop]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraMovLiquidaOdp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraMovLiquidaOdp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraLiquida]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraLiquida]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenManif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraOrdenManif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraMovLiquida]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_TraMovLiquida]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiquida]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraLiquida]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiquidaFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraLiquidaFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiquidaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraLiquidaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiquidaRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraLiquidaRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiquidaRelo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraLiquidaRelo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenManif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenManif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoNet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoNet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraLiquida]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTraLiquida]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryVehiculosLta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryTraOrdenPagoNet] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
SELECT OP.TipDoc AS CdTipoDoc,TipoDoc,OP.OrdPago AS NumOrden,OP.IdCia AS CdCia,Compania,OP.Fecha AS FecOrden,OP.IdConcepto AS CdConcepto,Concepto,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif
,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaCom,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario
,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja 
,M.Fecha AS FecManif,FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
,ISNULL(EGR.Vrabono,0) AS VrEgreso,ISNULL(FechaEgr,EGR.EGRFEC)
--Datos del vehiculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Direccion AS DirPoseedor,T.Telefono AS TelPoseedor,T.TelMovil AS TelMovilPosee,T.e_mail AS emailPoseedor
,TIC.CdLocdad AS IdIcaOdp,LIC.Localidad AS CiudadICaOdp,CDTR.IdLugar AS IdLugarLic,LLC.Localidad AS LugarLicencia
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,Modelo,Config,TipoAfil,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Sys_TiposDoc AS TD ON OP.TipDoc=TD.IdDoc
 INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca  LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 LEFT JOIN TercCndtores AS CDTR ON O.IdConductor =CDTR.IdConductor LEFT JOIN Localidades AS LLC ON CDTR.IdLugar=LLC.IdLocal 
 LEFT JOIN Tablapor AS TIC ON O.CodTarIca=TIC.IdTarifa  LEFT JOIN Localidades LIC ON TIC.CdLocdad=LIC.IdLocal 
 LEFT JOIN ( SELECT TipOdp,OrdPago,IdCiaOdp,SUM(VrAbonado) AS Vrabono,MAX(Trn_Comprobantes.Fecha) AS EGRFEC FROM Trn_TraEgrOrden 
 LEFT JOIN Trn_Comprobantes ON Trn_TraEgrOrden.TipCom =Trn_Comprobantes.TipCom AND Trn_TraEgrOrden.NumEgreso =Trn_Comprobantes.Comprobante
 AND Trn_TraEgrOrden.IdCia =Trn_Comprobantes.IdCia  GROUP BY TipOdp,OrdPago,IdCiaOdp) AS EGR
 ON OP.TipDoc =EGR.TipOdp AND OP.OrdPago =EGR.OrdPago AND OP.IdCia =EGR.IdCiaOdp 
 WHERE  OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND OP.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
UNION ALL
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,Concepto,O.TipMuc,O.Manifiesto,O.IdCiaMuc,D.IdVehiculo,NumVeh
,O.IdPropietario,NP.RazonSocial,D.IdPoseedor,T.RazonSocial,D.IdConductor,CDT.RazonSocial,VrTotalFletes,O.VrDescuento,O.VrRetencion,O.VrReteIca,VrAnticipos
,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto
,TarifaTabla,O.TarifaFlete,UnidTarifa,O.PesoTotal,Unidades,O.Volumen,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp
,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp,D.CxPagar,0,'','','',FecDoc,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,0,0,Null,D.Observacion,D.IdCiaCrea,D.IdUsuario,Usuario
,TipoLiq,D.TipDoc,D.OrdPago,D.IdCiaDoc,'',Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja 
,M.Fecha,FecDespacho,FecEntrega,M.IdOrigen,CO.Localidad,CO.IdDep,DPO.Departamento,M.IdDestino,CD.Localidad,CD.IdDep,DPD.Departamento,M.IdRuta,R.Ruta,nRemolque,TipoAfiVehic,IdLocFletes
,CF.Localidad,M.Observacion,ISNULL(EGR.Vrabono,0),ISNULL(FechaEgr,EGR.EGRFEC)
--Datos del vehiculo
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,T.Direccion,T.Telefono,T.TelMovil,T.e_mail,TIC.CdLocdad,LIC.Localidad,CDTR.IdLugar,LLC.Localidad
,V.IdTipoVeh,TipoVehiculo,V.IdMarca,MV.Marca,Modelo,Config,TipoAfil,V.Descripcion
 FROM Trn_TraDevOdp AS D INNER JOIN Trn_TraOrdenManif AS O ON D.TipDoc=O.TipDoc AND D.OrdPago=O.OrdPago AND D.IdCiaDoc=O.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Localidades AS CO ON M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.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  LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 LEFT JOIN TercCndtores AS CDTR ON D.IdConductor=CDTR.IdConductor LEFT JOIN Localidades AS LLC ON CDTR.IdLugar=LLC.IdLocal 
 LEFT JOIN Tablapor AS TIC ON O.CodTarIca=TIC.IdTarifa  LEFT JOIN Localidades LIC ON TIC.CdLocdad=LIC.IdLocal 
 LEFT JOIN ( SELECT TipOdp,OrdPago,IdCiaOdp,SUM(VrAbonado) AS Vrabono,MAX(Trn_Comprobantes.Fecha) AS EGRFEC FROM Trn_TraEgrOrden 
 LEFT JOIN Trn_Comprobantes ON Trn_TraEgrOrden.TipCom =Trn_Comprobantes.TipCom AND Trn_TraEgrOrden.NumEgreso =Trn_Comprobantes.Comprobante
 AND Trn_TraEgrOrden.IdCia=Trn_Comprobantes.IdCia GROUP BY TipOdp,OrdPago,IdCiaOdp) AS EGR
 ON D.TipDoc=EGR.TipOdp AND D.OrdPago=EGR.OrdPago AND D.IdCiaDoc=EGR.IdCiaOdp 
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraLiquidaRelo] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
AS 
 SELECT LQ.TipDoc AS TipLiq,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,CN.Compania AS NomCompania,LQ.Fecha AS FechaLiq,LQ.IdConcepto AS CdConcepto,Concepto,LQ.IdVehiculo AS PlacaVeh
,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,VrTotal,LQ.CxPagar AS CuePagar,LQ.TipCom AS CdTipCom,TipoCom,LQ.Comprobante AS NumComprob,LQ.IdCiaCom AS CdCiaCom
,LQ.Anulado AS Anuldo,LQ.NumDev AS NumDevoluc,LQ.FecDev AS FechaDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,Estado,FecIniCau,FecFinCau,FecIniOdp,FecFinOdp,ModLiquida,LQ.TimeSys AS FechaCrea,LQ.FecUpdate AS FechaAct,LQ.IdCiaCrea AS CdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario
--datos de la orden
,LP.TipOdp AS TipOrden,LP.OrdPago AS NumOrden,LP.IdCiaOdp AS CdCiaOdp,COP.Compania AS NomCiaOrden,OP.Fecha AS FechaOdp,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,VrTotalFletes,O.VrDescuento AS Vr_Dcto
,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto,VrAbonado,TotAbonado,TarifaTabla
,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,NumCheque,O.Referencia AS Referncia,EdoLiqCausac,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque
--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,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraLiquida AS LQ INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia 
 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 Terceros AS  CDT ON LQ.IdConductor=CDT.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
 INNER JOIN Trn_TraLiquidaOdp AS LP ON LQ.TipDoc=LP.TipDoc AND LQ.Liquidacion=LP.Liquidacion AND LQ.IdCia=LP.IdCia
 INNER JOIN Trn_TraOrdenPago AS OP ON LP.TipOdp=OP.TipDoc AND LP.OrdPago=OP.OrdPago AND LP.IdCiaOdp=OP.IdCia
 INNER JOIN Trn_TraOrdenManif AS O ON LP.TipOdp=O.TipDoc AND LP.OrdPago=O.OrdPago AND LP.IdCiaOdp=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS COP ON LP.IdCiaOdp=COP.IdCia
 INNER JOIN Localidades AS CO ON M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN (SELECT TipDoc,Documento,IdCiaDoc,SUM(VrAbonado) AS TotAbonado FROM Trn_ComFactura WHERE TipFac='ODP' AND TipDoc='ODP' GROUP BY TipDoc,Documento,IdCiaDoc)
 AS ABO ON LP.TipOdp=ABO.TipDoc AND LP.OrdPago=ABO.Documento AND LP.IdCiaOdp=ABO.IdCiaDoc
 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.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND LQ.IdConductor LIKE ISNULL(@pmIdConductor,'%')
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraOrdenPagoRel] @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 OP.TipDoc AS TipoOdp,OP.OrdPago AS NumOdp,OP.IdCia AS CdCia,Compania,OP.Fecha AS FechaOdp,OP.IdConcepto AS CdConcepto,Concepto,Modalidad,LiqRemesas,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence
,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaComp,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdEstado AS CdEstado,OP.TimeSys AS FechaCrea,OP.FecUpdate AS FechaAct,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,TipoLiq,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,CdForma,EdoLiqCausac
,M.Fecha AS FecManif,FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--Datos del vehiculo
,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,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.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 NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 WHERE OP.TipDoc=@pmTipDoc AND OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND OP.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND OP.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND O.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY OP.IdCia,OP.OrdPago

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryTraOrdenPagoFmt] @pmTipDoc VARCHAR(3),@pmOrdPagoIni INT,@pmOrdPagoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT OP.TipDoc AS TipoOdp,TipoDoc,OP.OrdPago AS NumOdp,OP.IdCia AS CdCia,Compania,OP.Fecha AS FechaOdp,OP.IdConcepto AS CdConcepto,Concepto,Modalidad,LiqRemesas,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence
,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaComp,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdEstado AS CdEstado,Estado,OP.TimeSys AS FechaCrea,OP.FecUpdate AS FechaAct,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario,Leyenda
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,TipoLiq,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja,CdForma
,M.Fecha AS FecManif,FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
,TipRem,ORM.Remesa AS NumRemesa,ORM.IdCiaRem AS CdCiaRem,ItemRem,ORM.Cantidad AS Cant,PesoNeto,ORM.UndMed AS CdUMed,UMP.Unidad AS UmPeso,ORM.Volumen AS Volmen,UndVol,Cases,Cajas,Palets,TarifPago,TarifTabla,UndTarifa,Detalle
--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
--Datos del vehiculo
,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,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,NumLiquida
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON OP.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON OP.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.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 NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Trn_TraOrdenRemesas AS ORM ON O.TipDoc=ORM.TipDoc AND   O.OrdPago=ORM.OrdPago AND  O.IdCia=ORM.IdCia 
 AND  O.TipMuc=ORM.TipMuc AND  O.Manifiesto=ORM.Manifiesto AND  O.IdCiaMuc=ORM.IdCiaMuc
 LEFT JOIN Sys_Um AS UMP ON ORM.UndMed=UMP.UndMed  LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 LEFT JOIN (SELECT TipOdp,OrdPago,IdCiaOdp,MAX(Liquidacion) AS NumLiquida,SUM(VrAbonado) AS TotalAbonos FROM Trn_TraLiquidaOdp GROUP BY TipOdp,OrdPago,IdCiaOdp)
 AS LQ ON OP.TipDoc=LQ.TipOdp AND OP.OrdPago=LQ.OrdPago AND OP.IdCia=LQ.IdCiaOdp
 WHERE OP.TipDoc=@pmTipDoc AND OP.OrdPago BETWEEN @pmOrdPagoIni AND @pmOrdPagoFin AND OP.IdCia=@pmIdCia
 ORDER BY OP.OrdPago


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_TraMovLiquidaOdp] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdPoseedor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
 AS 
 INSERT INTO tm_TraMovLiquida (tmEst,tmTipo,tmNumero,tmIdCia,tmItem,tmFecha,tmFecVence,tmIdPoseedor,tmIdVehiculo,tmIdConcepto,tmDescripcion
,tmVrDebito,tmVrCredito,tmVrAbonado,tmTipoVehic,tmVrBase,tmTarifa,tmReferencia,tmTipDocRef,tmDocRef,tmEstado,tmTipoLiq,tmLiquida,tmCiaLiq,tmSaldoAnt,tmSaldoCia,tmSaldoAntVeh,tmSaldoVehCia)
 SELECT @pmtmEst,O.TipDoc,O.OrdPago,O.IdCia,M.Item,O.Fecha,O.Fecha,M.IdPoseedor,M.IdVehiculo,'0','ORDENES DE PAGO'
,0,M.VrNeto,TotAbonado,'TERCEROS',M.VrTotalFletes-M.VrDescuento,0,M.Referencia,M.TipMuc,M.Manifiesto,M.EdoLiqCausac,'0',0,'00',0,0,0,0
 FROM Trn_TraOrdenPago AS O INNER JOIN Trn_TraOrdenManif AS M ON O.TipDoc=M.TipDoc AND O.OrdPago=M.OrdPago AND O.IdCia=M.IdCia
 LEFT JOIN (SELECT TipDoc,Documento,IdCiaDoc,SUM(VrAbonado) AS TotAbonado FROM Trn_ComFactura WHERE TipFac='ODP' AND TipDoc='ODP' GROUP BY TipDoc,Documento,IdCiaDoc)
 AS ABO ON O.TipDoc=ABO.TipDoc AND O.OrdPago=ABO.Documento AND O.IdCia=ABO.IdCiaDoc
 WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraOrdenPagoLta] @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 O.OrdPago AS NumOrden,O.IdCia AS CdCia,Compania,Fecha,TipMuc,Manifiesto,IdCiaMuc,M.IdVehiculo AS PlacaVeh,NumVeh,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,LiqRemesas,TipoLiq,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos
,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,PesoOrigen,UnidOrigen,VolOrigen
,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CxPagar,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,FechaVence
,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,CdForma
,O.IdConcepto AS CdConcepto,Concepto,Modalidad,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraOrdenPago AS O INNER JOIN Trn_TraOrdenManif AS M ON O.TipDoc=M.TipDoc AND O.OrdPago=M.OrdPago AND O.IdCia=M.IdCia
 INNER JOIN Companias AS CI ON O.IdCia=CI.IdCia INNER JOIN Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo   INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero  INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 LEFT JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo
 WHERE O.TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND  M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 ORDER BY O.IdCia,O.OrdPago

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraOrdenPagoRelDet] @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 OP.TipDoc AS TipoOdp,OP.OrdPago AS NumOdp,OP.IdCia AS CdCia,Compania,OP.Fecha AS FechaOdp,OP.IdConcepto AS CdConcepto,Concepto,Modalidad,LiqRemesas,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence
,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaComp,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdEstado AS CdEstado,OP.TimeSys AS FechaCrea,OP.FecUpdate AS FechaAct,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,TipoLiq,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,EdoLiqCausac
,M.Fecha AS FecManif,FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--detalles
,TipRem,ORM.Remesa AS NumRemesa,ORM.IdCiaRem AS CdCiaRem,ItemRem,ORM.Cantidad AS Cant,PesoNeto,ORM.UndMed AS CdUMed,UMP.Unidad AS UmPeso,ORM.Volumen AS Volmen,UndVol,Cases,Cajas,Palets,TarifPago,TarifTabla,UndTarifa,Detalle
--Datos del vehiculo
,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,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.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 NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 LEFT JOIN Trn_TraOrdenRemesas AS ORM ON   O.TipDoc=ORM.TipDoc AND   O.OrdPago=ORM.OrdPago AND  O.IdCia=ORM.IdCia 
 AND  O.TipMuc=ORM.TipMuc AND  O.Manifiesto=ORM.Manifiesto AND  O.IdCiaMuc=ORM.IdCiaMuc
 LEFT JOIN Sys_Um AS UMP ON ORM.UndMed=UMP.UndMed
 WHERE OP.TipDoc=@pmTipDoc AND OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND OP.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND OP.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND O.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY OP.IdCia,OP.OrdPago

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paQryTraLiquidaRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT LQ.TipDoc AS TipLiq,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,Concepto,LQ.IdVehiculo AS PlacaVeh
,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,VrTotal,CxPagar,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,Estado,FecIniCau,FecFinCau,FecIniOdp,FecFinOdp,ModLiquida,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario
,Item,TipCau,Causacion,IdCiaCau,ItemCau,FechaCau,D.IdConcepto AS CdConcCausac,D.Descripcion AS DetDescripcion,VrAbono,NitPoseedor,N.RazonSocial AS NomPoseedor,pVehiculo,EsVence
,TipOdp,OrdenPago,IdCiaOdp,TipMuc,Manifiesto,IdCiaMuc,VrSaldoOrden,CdCuenta,CdCuePagar,NumFactura,CdCCosto,CCosto,CdSubCos,SubCosto
--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,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraLiquida AS LQ INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia 
 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 Terceros AS  CDT ON LQ.IdConductor=CDT.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
 INNER JOIN Trn_TraLiquidaDed AS D ON LQ.TipDoc=D.TipDoc  AND LQ.Liquidacion=D.Liquidacion AND LQ.IdCia=D.IdCia
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 LEFT JOIN Terceros AS N ON  D.NitPoseedor=N.IdTercero  LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero 
 LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 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.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND LQ.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 ORDER BY LQ.IdCia,LQ.Liquidacion



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_TraMovLiquidaCau] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdPoseedor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
 AS 
 INSERT INTO tm_TraMovLiquida (tmEst,tmTipo,tmNumero,tmIdCia,tmItem,tmFecha,tmFecVence,tmIdPoseedor,tmIdVehiculo,tmIdConcepto,tmDescripcion
,tmVrDebito,tmVrCredito,tmVrAbonado,tmTipoVehic,tmVrBase,tmTarifa,tmReferencia,tmTipDocRef,tmDocRef,tmEstado,tmTipoLiq,tmLiquida,tmCiaLiq,tmSaldoAnt,tmSaldoCia,tmSaldoAntVeh,tmSaldoVehCia)
 SELECT @pmtmEst,D.TipDoc,D.Causacion,D.IdCia,D.Item,C.Fecha,D.FecVence,D.NitTercero,D.pVehiculo,D.IdConcepto,D.Descripcion
,D.VrDebito,D.VrCredito,D.VrAbonado,D.TipoAfiVehic,VrBase,TarifBase,D.Referencia,TipDocRef,DocRef,EstadoReg,D.TipoLiq,D.NumLiquida,D.CiaLiquida,0,0,0,0
 FROM Trn_TraCauDetalle AS D INNER JOIN Trn_TraCausacion AS C ON D.TipDoc=C.TipDoc AND D.Causacion=C.Causacion AND D.IdCia=C.IdCia
 WHERE D.FecVence BETWEEN @pmFechaIni AND @pmFechaFin AND D.TipoConc='CTACOBRAR' AND D.VrDebito>0
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.NitTercero LIKE ISNULL(@pmIdPoseedor,'%') AND D.pVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInstm_TraMovLiquidaDop] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdPoseedor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
 AS 
 INSERT INTO tm_TraMovLiquida (tmEst,tmTipo,tmNumero,tmIdCia,tmItem,tmFecha,tmFecVence,tmIdPoseedor,tmIdVehiculo,tmIdConcepto,tmDescripcion
,tmVrDebito,tmVrCredito,tmVrAbonado,tmTipoVehic,tmVrBase,tmTarifa,tmReferencia,tmTipDocRef,tmDocRef,tmEstado,tmTipoLiq,tmLiquida,tmCiaLiq,tmSaldoAnt,tmSaldoCia,tmSaldoAntVeh,tmSaldoVehCia)
 SELECT @pmtmEst,D.TipDev,D.Devolucion,D.IdCia,M.Item,D.Fecha,D.FecDoc,M.IdPoseedor,M.IdVehiculo,'0','ORDENES DE PAGO'
,M.VrNeto,0,0,'TERCEROS',M.VrTotalFletes-M.VrDescuento,0,M.Referencia,M.TipMuc,M.Manifiesto,M.EdoLiqCausac,'0',0,'00',0,0,0,0
 FROM Trn_TraDevOdp AS D INNER JOIN Trn_TraOrdenManif AS M ON D.TipDoc=M.TipDoc AND D.OrdPago=M.OrdPago AND D.IdCiaDoc=M.IdCia
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paQryTraLiquidaLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT  Liquidacion,L.IdCia AS CdCia,Compania,Fecha,L.IdConcepto AS CdConcepto,Concepto,IdVehiculo,IdPoseedor,T.RazonSocial AS Poseedor,IdConductor,CDT.RazonSocial AS Conductor
,VrTotal,CxPagar,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,L.Observacion AS Observ,L.IdEstado AS CdEstado,FecIniCau,FecFinCau,FecIniOdp,FecFinOdp,ModLiquida
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,L.IdUsuario AS Cdusuario,Usuario
 FROM Trn_TraLiquida AS L  INNER JOIN Terceros AS T ON L.IdPoseedor=T.IdTercero INNER JOIN Terceros AS CDT ON  L.IdConductor=CDT.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
 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,'%')
 ORDER BY L.IdCia,Liquidacion



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraMovLiquida] @pmtmEst CHAR(2)
 AS 
 SELECT tmTipo,TipoDoc,tmNumero,tmIdCia,Compania,tmItem,tmFecha,tmFecVence,tmIdPoseedor,RazonSocial,tmIdVehiculo
,tmIdConcepto,tmDescripcion,tmVrDebito,tmVrCredito,tmVrAbonado,tmTipoVehic,tmVrBase
,tmTarifa,tmReferencia,tmTipDocRef,tmDocRef,tmEstado,tmTipoLiq,tmLiquida,tmCiaLiq
,tmSaldoAnt,tmSaldoCia,tmSaldoAntVeh,tmSaldoVehCia
 FROM tm_TraMovLiquida AS M INNER JOIN Terceros AS T ON M.tmIdPoseedor=T.IdTercero
 INNER JOIN Companias  AS CN ON M.tmIdCia=CN.IdCia
 LEFT JOIN Sys_TiposDoc AS TD ON M.tmTipo=TD.IdDoc
 WHERE tmEst=@pmtmEst
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraMovLiquida] @pmtmEst CHAR(2),@pmtmTipo VARCHAR(3),@pmtmNumero INT,@pmtmIdCia CHAR(2),@pmtmItem INT,@pmtmFecha SMALLDATETIME,@pmtmFecVence SMALLDATETIME,@pmtmIdPoseedor VARCHAR(16),@pmtmIdVehiculo VARCHAR(10),@pmtmIdConcepto VARCHAR(4),@pmtmDescripcion VARCHAR(250),@pmtmVrDebito MONEY,@pmtmVrCredito MONEY,@pmtmVrAbonado MONEY,@pmtmTipoVehic VARCHAR(10)
,@pmtmVrBase MONEY,@pmtmTarifa DECIMAL(14,4),@pmtmReferencia VARCHAR(50),@pmtmTipDocRef VARCHAR(3),@pmtmDocRef INT,@pmtmEstado INT,@pmtmTipoLiq VARCHAR(3),@pmtmLiquida INT,@pmtmCiaLiq CHAR(2),@pmtmSaldoAnt MONEY,@pmtmSaldoCia MONEY,@pmtmSaldoAntVeh MONEY,@pmtmSaldoVehCia MONEY
 AS 
 INSERT INTO tm_TraMovLiquida (tmEst,tmTipo,tmNumero,tmIdCia,tmItem,tmFecha,tmFecVence,tmIdPoseedor,tmIdVehiculo,tmIdConcepto,tmDescripcion,tmVrDebito,tmVrCredito,tmVrAbonado,tmTipoVehic,tmVrBase,tmTarifa,tmReferencia,tmTipDocRef,tmDocRef,tmEstado,tmTipoLiq,tmLiquida,tmCiaLiq,tmSaldoAnt,tmSaldoCia,tmSaldoAntVeh,tmSaldoVehCia)
 VALUES (@pmtmEst,@pmtmTipo,@pmtmNumero,@pmtmIdCia,@pmtmItem,@pmtmFecha,@pmtmFecVence,@pmtmIdPoseedor,@pmtmIdVehiculo,@pmtmIdConcepto,@pmtmDescripcion,@pmtmVrDebito,@pmtmVrCredito,@pmtmVrAbonado,@pmtmTipoVehic,@pmtmVrBase,@pmtmTarifa,@pmtmReferencia,@pmtmTipDocRef,@pmtmDocRef,@pmtmEstado,@pmtmTipoLiq,@pmtmLiquida,@pmtmCiaLiq,@pmtmSaldoAnt,@pmtmSaldoCia,@pmtmSaldoAntVeh,@pmtmSaldoVehCia)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  [dbo].[paInstm_TraMovLiquidaDcc] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdPoseedor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
 AS 
 INSERT INTO tm_TraMovLiquida (tmEst,tmTipo,tmNumero,tmIdCia,tmItem,tmFecha,tmFecVence,tmIdPoseedor,tmIdVehiculo,tmIdConcepto,tmDescripcion
,tmVrDebito,tmVrCredito,tmVrAbonado,tmTipoVehic,tmVrBase,tmTarifa,tmReferencia,tmTipDocRef,tmDocRef,tmEstado,tmTipoLiq,tmLiquida,tmCiaLiq,tmSaldoAnt,tmSaldoCia,tmSaldoAntVeh,tmSaldoVehCia)
SELECT @pmtmEst,D.TipDev,D.Devolucion,D.IdCia,DC.Item,D.Fecha,DC.FecVence,DC.NitTercero,DC.pVehiculo,DC.IdConcepto,DC.Descripcion
,DC.VrCredito,DC.VrDebito,DC.VrAbonado,DC.TipoAfiVehic,DC.VrBase,DC.TarifBase,DC.Referencia,TipDocRef,DocRef,EstadoReg,DC.TipoLiq,DC.NumLiquida,DC.CiaLiquida,0,0,0,0
FROM Trn_TraDevCau AS D INNER JOIN Trn_TraCauDetalle AS DC ON D.TipDoc=DC.TipDoc AND D.Causacion=DC.Causacion AND D.IdCiaDoc=DC.IdCia
WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND DC.TipoConc='CTACOBRAR' AND DC.VrDebito>0
AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND DC.NitTercero LIKE ISNULL(@pmIdPoseedor,'%') AND DC.pVehiculo LIKE ISNULL(@pmIdVehiculo,'%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraOrdenManif] @pmTipDoc VARCHAR(3),@pmOrdPago INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,OrdPago,IdCia,Item,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor,VrTotalFletes,VrDescuento,VrRetencion
,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades
,Volumen,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CodTarRet,CodTarIca
,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos
,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja,CdForma,FechaEgr
 FROM Trn_TraOrdenManif
 WHERE TipDoc=@pmTipDoc AND OrdPago=@pmOrdPago AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsTraOrdenManif] @pmTipDoc VARCHAR(3),@pmOrdPago INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmVrTotalFletes MONEY,@pmVrDescuento MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY
,@pmVrAnticipos MONEY,@pmVrFaltantes MONEY,@pmVrSeguros MONEY,@pmVrFondos MONEY,@pmVrAportes MONEY,@pmVrOtrosDctos MONEY,@pmVrImpuestos MONEY,@pmVrEstampilla MONEY,@pmVrOtrosPagos MONEY,@pmVrNeto MONEY,@pmTarifaTabla MONEY,@pmTarifaFlete MONEY,@pmUnidTarifa VARCHAR(10),@pmPesoTotal DECIMAL(14,4),@pmUnidades DECIMAL(14,4),@pmVolumen DECIMAL(14,4),@pmPesoOrigen DECIMAL(14,4)
,@pmUnidOrigen DECIMAL(14,4),@pmVolOrigen DECIMAL(14,4),@pmPesoDestino  DECIMAL(14,4),@pmUnidDestino DECIMAL(14,4),@pmVolDestino DECIMAL(14,4),@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseSeg MONEY,@pmBaseImp MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaSeg DECIMAL(14,4),@pmTarifaImp DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmTipoLiq INT
,@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmReferencia VARCHAR(50),@pmCantFaltante DECIMAL(14,4),@pmUnidadFalt VARCHAR(10),@pmTolFaltNeto DECIMAL(14,4),@pmVrConcPagos MONEY,@pmVrConcDctos MONEY,@pmVrConcFondo MONEY,@pmVrConcSeguro MONEY,@pmVrConcAporte MONEY,@pmVrConcImpuesto MONEY
,@pmEdoLiqCausac INT,@pmVrRemesas MONEY,@pmVrRecCaja MONEY,@pmCdForma VARCHAR(4),@pmFechaEgr SMALLDATETIME
 AS 
 INSERT INTO Trn_TraOrdenManif (TipDoc,OrdPago,IdCia,Item,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla
,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CodTarRet,CodTarIca,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto
,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja,CdForma,FechaEgr)
 VALUES (@pmTipDoc,@pmOrdPago,@pmIdCia,@pmItem,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmIdConductor,@pmIdPropietario,@pmIdPoseedor,@pmVrTotalFletes,@pmVrDescuento,@pmVrRetencion,@pmVrReteIca,@pmVrAnticipos,@pmVrFaltantes,@pmVrSeguros
,@pmVrFondos,@pmVrAportes,@pmVrOtrosDctos,@pmVrImpuestos,@pmVrEstampilla,@pmVrOtrosPagos,@pmVrNeto,@pmTarifaTabla,@pmTarifaFlete,@pmUnidTarifa,@pmPesoTotal,@pmUnidades,@pmVolumen,@pmPesoOrigen,@pmUnidOrigen,@pmVolOrigen,@pmPesoDestino,@pmUnidDestino,@pmVolDestino,@pmBaseRet,@pmBaseIca,@pmBaseSeg,@pmBaseImp,@pmTarifaRet,@pmTarifaIca,@pmTarifaSeg,@pmTarifaImp,@pmCodTarRet,@pmCodTarIca,@pmTipoLiq
,@pmTipEgr,@pmEgreso,@pmIdCiaEgr,@pmNumCheque,@pmReferencia,@pmCantFaltante,@pmUnidadFalt,@pmTolFaltNeto,@pmVrConcPagos,@pmVrConcDctos,@pmVrConcFondo,@pmVrConcSeguro,@pmVrConcAporte,@pmVrConcImpuesto,@pmEdoLiqCausac,@pmVrRemesas,@pmVrRecCaja,@pmCdForma,@pmFechaEgr)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paQryTraLiquida] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,IdVehiculo,IdPoseedor,IdConductor,VrTotal,CxPagar,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,Observacion,IdEstado,FecIniCau,FecFinCau,FecIniOdp,FecFinOdp,ModLiquida,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraLiquida
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paInsTraLiquida] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16)
,@pmIdConductor VARCHAR(16),@pmVrTotal MONEY, @pmCxPagar BIT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmFecIniCau SMALLDATETIME,@pmFecFinCau SMALLDATETIME,@pmFecIniOdp SMALLDATETIME,@pmFecFinOdp SMALLDATETIME,@pmModLiquida VARCHAR(10)
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraLiquida (TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,IdVehiculo,IdPoseedor,IdConductor,VrTotal,CxPagar,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado
,FecIniCau,FecFinCau,FecIniOdp,FecFinOdp,ModLiquida,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmLiquidacion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdVehiculo,@pmIdPoseedor,@pmIdConductor,@pmVrTotal,@pmCxPagar,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion
,@pmIdEstado,@pmFecIniCau,@pmFecFinCau,@pmFecIniOdp,@pmFecFinOdp,@pmModLiquida,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paUpTraLiquida] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16)
,@pmVrTotal MONEY,@pmCxPagar BIT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmFecIniCau SMALLDATETIME,@pmFecFinCau SMALLDATETIME,@pmFecIniOdp SMALLDATETIME,@pmFecFinOdp SMALLDATETIME,@pmModLiquida VARCHAR(10),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraLiquida SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdVehiculo=@pmIdVehiculo,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,VrTotal=@pmVrTotal,CxPagar=@pmCxPagar,TipCom=@pmTipCom,Comprobante=@pmComprobante
,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecIniCau=@pmFecIniCau,FecFinCau=@pmFecFinCau,FecIniOdp=@pmFecIniOdp,FecFinOdp=@pmFecFinOdp
,ModLiquida=@pmModLiquida,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryVehiculosLta] @pmClaseVeh VARCHAR(10)=Null,@pmIdTipoVeh VARCHAR(4)=Null,@pmIdMarca VARCHAR(4)=Null
,@pmIdTipoMot VARCHAR(4)=Null,@pmIdCrceria VARCHAR(4)=Null,@pmModelo VARCHAR(4)=Null,@pmConfig VARCHAR(5)=Null,@pmIdCat VARCHAR(4)=Null
,@pmClaseMat VARCHAR(10)=Null,@pmIdCom VARCHAR(4)=Null,@pmIdLub VARCHAR(4)=Null,@pmIdTlla VARCHAR(4)=Null,@pmIdMarlla VARCHAR(4)=Null
,@pmIdPropietario VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPpd VARCHAR(4)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFecComIni SMALLDATETIME=Null,@pmFecComFin SMALLDATETIME=Null
,@pmIdAdmon VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
 AS 
 SELECT IdVehiculo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,TC.TipoCar AS TipoCarr,CL.NomColor AS Color,V.Modelo AS ModeloVeh,FecRep,Config,V.IdCat AS CodCatg,CarrCapac
,PesoVacio,NumMotor,SerieChasis,RQ.IdCrceria AS RmqIdCarr,MR.Marca AS MarcaRmq,CR.NomColor AS ColorRmq,NitEmpresa,NE.RazonSocial AS Empresa
,IdPoseedor,NT.RazonSocial AS Poseedor,IdConductor,NC.RazonSocial AS Conductor,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CdTarifa,TarjProp,RespCivil,VigRCivil,VrComcial,V.IdEstado AS CdEstado,Estado,V.Inactivo AS Inactvo,FecPriServ,FecUltServ,TarjOper,FecTarjOper,VigTarjOper
,RegNalCarga,FecRegNal,VigRegNal,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,V.Observacion AS Observ,TipoAfil
,NumVeh,ClaseVeh,V.IdTipoVeh AS CdTipo,V.IdLinea AS CdLinea,LineaVeh,V.IdColor AS CdColor,V.IdTipoMot AS CdTipMotor,TipoMotor
,V.IdCrceria AS CdCarr,VehArtic,NumLlan,NumLlans,Catpeaje,CdCatv,ClaseMat,Cilind,CapTanq,V.IdCom AS CdTipComb,TipoComb,V.IdLub AS CdLub
,TipoLub,V.IdTlla AS CdTipLlantas,TipoLlanta,IdMarlla,ML.Marca AS MarcaLlantas,PesoMax,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo
,V.UndCapc AS UndCapacidad,Comptmtos,CapComp,PasjerosPie,PasjerosSen,V.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,V.IdPpd AS CdTipProp,TipoProp,VehPropio
,Adquisc,V.NitProv AS NitProveedor,NPV.RazonSocial AS Proveedor,V.FecCompra AS FechaCompra,VrAseg,V.VrAvaludo AS VlrAvaludo,V.VidaUtil AS Vida_Util,FecSalida,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdGrupR,FecIngreso,FecVigencia,FecRetiro,FecTProp,VigTProp,CdLugTp,LT.Localidad AS LugarTarjProp
,Ulttramite,FecRCivil,KmInicial,KmActual,Km2Actual,V.Descripcion AS VehDescripcion,V.CdCenSer AS CodCentro,CentroServ,V.CdLocal AS CodCiuUbic,LU.Localidad AS CiuUbicacion,LU.IdDep AS CodDpto,Departamento
,V.Ubicacion,PathFoto,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,FecUltAcc,TieneAcc,FecPagImp,V.IdUsuario AS CdUsuario,Usuario
,V.FechaAdd AS Fec_Add,V.FechaUpdate AS Fec_Upd,EV.NColor AS NumColor,OutDemand,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion
,FecCertMovil,VigCertMovil,CdRutaHab,RQ.IdMarca AS RmqIdMarca,RQ.IdColor AS RmqIdColor,RC.TipoCar AS RmqTipoCar
 FROM Vehiculos  AS V INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposMot AS TM ON V.IdTipoMot=TM.IdTipoMot
 INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria INNER JOIN PeajesCat AS CP ON V.IdCat=CP.IdCat
 INNER JOIN TiposFuel AS TF ON V.IdCom=TF.IdCom INNER JOIN TiposLub AS TL ON V.IdLub=TL.IdLub
 INNER JOIN TiposLla AS TLL ON V.IdTlla=TLL.IdTlla INNER JOIN Marcas AS ML ON V.IdMarlla=ML.IdMarca
 INNER JOIN Terceros AS NP ON  V.IdPropietario=NP.IdTercero  INNER JOIN Terceros AS NT ON  V.IdPoseedor=NT.IdTercero
 INNER JOIN Terceros AS NC ON  V.IdConductor=NC.IdTercero INNER JOIN TiposPpt AS TP ON V.IdPpd=TP.IdPpd
 INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon
 INNER JOIN TiposNivs AS NV ON V.IdNiv=NV.IdNiv
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NPV ON V.NitProv=NPV.IdTercero
 LEFT JOIN Terceros AS NS ON V.NitEmpSoat =NS.IdTercero LEFT JOIN Localidades AS LT ON V.CdLugTp=LT.IdLocal
 LEFT JOIN CentrosServ AS CS ON V.CdCenSer =CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 LEFT JOIN VehRemolq AS RQ ON V.CdRemque=RQ.IdRemque
 LEFT JOIN Marcas AS MR ON RQ.IdMarca=MR.IdMarca LEFT JOIN TiposCol AS CR ON RQ.IdColor=CR.IdColor 
 LEFT JOIN TiposCar AS RC ON RQ.IdCrceria=RC.IdCrceria
 WHERE ClaseVeh LIKE ISNULL(@pmClaseVeh,'%') AND V.IdTipoVeh LIKE ISNULL(@pmIdTipoVeh,'%')
 AND V.IdMarca LIKE ISNULL(@pmIdMarca,'%') AND V.IdTipoMot LIKE ISNULL(@pmIdTipoMot,'%')
 AND V.IdCrceria LIKE ISNULL(@pmIdCrceria,'%')  AND V.Modelo LIKE ISNULL(@pmModelo,'%')
 AND Config LIKE ISNULL(@pmConfig,'%') AND V.IdCat LIKE ISNULL(@pmIdCat ,'%')
 AND ClaseMat  LIKE ISNULL(@pmClaseMat,'%') AND V.IdCom LIKE ISNULL(@pmIdCom,'%')
 AND V.IdLub LIKE ISNULL(@pmIdLub,'%') AND V.IdTlla LIKE ISNULL(@pmIdTlla,'%')
 AND IdMarlla LIKE ISNULL(@pmIdMarlla,'%')  AND V.IdPropietario LIKE ISNULL(@pmIdPropietario,'%')
 AND IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')  AND IdConductor LIKE ISNULL(@pmIdConductor,'%') 
 AND V.IdPpd LIKE ISNULL(@pmIdPpd,'%') AND V.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND V.IdAdmon LIKE ISNULL(@pmIdAdmon,'%') AND V.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND (V.Inactivo=ISNULL(@pmInactivo,0) or V.Inactivo=ISNULL(@pmInactivo,1))
 AND (V.FecCompra>=ISNULL(@pmFecComIni,CAST('19100101' AS SMALLDATETIME)) AND V.FecCompra<=ISNULL(@pmFecComFin,CAST('20781230' AS SMALLDATETIME)))
ORDER BY  IdVehiculo

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraLiquidaFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT LQ.TipDoc AS TipLiq,TipoDoc,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,CN.Compania AS NomCompania,LQ.Fecha AS FechaLiq,LQ.IdConcepto AS CdConcepto,Concepto
,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,VrTotal,LQ.CxPagar AS CPagar,LQ.TipCom AS CdTipCom,TipoCom,LQ.Comprobante AS NumComp,LQ.IdCiaCom AS CdCiaComp
,LQ.Anulado AS Anuldo,LQ.NumDev AS NDevolucion,LQ.FecDev AS FechDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,Estado,FecIniCau,FecFinCau,FecIniOdp,FecFinOdp,ModLiquida
,LQ.TimeSys AS FechaAdd,LQ.FecUpdate AS FechaAct,LQ.IdCiaCrea AS CdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario,Leyenda
--datos de ordenes de pago
,LP.TipOdp AS TipOrden,LP.OrdPago AS NumOrden,LP.IdCiaOdp AS CdCiaOdp,COP.Compania AS NomCiaOrden,OP.Fecha AS FechaOdp,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif
,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros
,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto,VrAbonado,TotAbonado,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total
,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp,NumCheque,O.Referencia AS Referncia,EdoLiqCausac
,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,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,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,M.Fecha AS FecManif,VrApos,VrFam,VrFsc,VrFgi,VrTarApos ,VrTarFam ,VrTarFGi,VrTarFsc,VrBaseApos ,VrBaseFam,VrBaseFgi,VrBaseFsc,TPV.NumCuenta AS NumCtaProveedor,CTA.ClaseCuenta
,TPV.NitContac,TPV.NomContac,BA.Banco 
 FROM Trn_TraLiquida 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 Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Trn_TraLiquidaOdp AS LP ON LQ.TipDoc=LP.TipDoc AND LQ.Liquidacion=LP.Liquidacion AND LQ.IdCia=LP.IdCia
 INNER JOIN Trn_TraOrdenPago AS OP ON LP.TipOdp=OP.TipDoc AND LP.OrdPago=OP.OrdPago AND LP.IdCiaOdp=OP.IdCia
 INNER JOIN Trn_TraOrdenManif AS O ON LP.TipOdp=O.TipDoc AND LP.OrdPago=O.OrdPago AND LP.IdCiaOdp=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Vehiculos AS V ON O.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 Terceros AS CDT ON O.IdConductor=CDT.IdTercero
 INNER JOIN Companias AS COP ON LP.IdCiaOdp=COP.IdCia
 INNER JOIN Localidades AS CO ON M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN (SELECT TipDoc,Documento,IdCiaDoc,SUM(VrAbonado) AS TotAbonado FROM Trn_ComFactura WHERE TipFac='ODP' AND TipDoc='ODP' GROUP BY TipDoc,Documento,IdCiaDoc)
 AS ABO ON LP.TipOdp=ABO.TipDoc AND LP.OrdPago=ABO.Documento AND LP.IdCiaOdp=ABO.IdCiaDoc
 --fondos-conceptos
 LEFT JOIN (SELECT TipDoc,Documento,IdCia,SUM(CASE WHEN Concepto='FLE' AND Factura='FGI' THEN Valor ELSE 0 END ) AS VrFgi,MAX(CASE WHEN Concepto='FLE' AND Factura='FGI' THEN Vrtarifa ELSE 0 END ) as VrTarFGi,MAX(CASE WHEN Concepto='FLE' AND Factura='FGI' THEN VrBase ELSE 0 END ) as VrBaseFgi
 ,SUM(CASE WHEN Concepto='FLE' AND Factura='FAM' THEN Valor ELSE 0 END ) AS VrFam,MAX(CASE WHEN Concepto='FLE' AND Factura='FAM' THEN Vrtarifa ELSE 0 END ) as VrTarFam,MAX(CASE WHEN Concepto='FLE' AND Factura='FAM' THEN VrBase ELSE 0 END ) as VrBaseFam,
 SUM(CASE WHEN Concepto='FLE' AND Factura='FSC' THEN Valor ELSE 0 END ) AS VrFsc, MAX(CASE WHEN Concepto='FLE' AND Factura='FSC' THEN Vrtarifa ELSE 0 END ) as VrTarFsc,MAX(CASE WHEN Concepto='FLE' AND Factura='FSC' THEN VrBase ELSE 0 END ) as VrBaseFsc
,SUM(CASE WHEN Concepto='OTR' AND Factura='APOS' THEN Valor ELSE 0 END) AS VrApos,MAX(CASE WHEN Concepto='OTR' AND Factura='APOS' THEN Vrtarifa ELSE 0 END ) as VrTarApos,MAX(CASE WHEN Concepto='OTR' AND Factura='APOS' THEN VrBase ELSE 0 END ) as VrBaseApos
 FROM Trn_DetCuentas WHERE TipDoc='ODP' GROUP BY TipDoc,Documento,IdCia) AS FCN ON LP.TipOdp=FCN.TipDoc AND LP.OrdPago=FCN.Documento AND LP.IdCiaOdp=FCN.IdCia
 LEFT JOIN TercProvee TPV ON LQ.IdPoseedor=TPV.IdProv  LEFT JOIN ClaseCta CTA ON TPV.IdClase=CTA.IdClase 
 LEFT JOIN Bancos BA ON TPV.IdBanco =BA.IdBanco 
 WHERE LQ.TipDoc=@pmTipDoc AND LQ.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND LQ.IdCia=@pmIdCia