INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCAOS','TRAINF','GEN',7,'Anticipos-Ordenes de Servicio','FRMLCAOS','SSSSSSSSSSSSS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCCOS','TRAINF','GEN',8,'Cumplidos-Ordenes de Servicio','FRMLCCOS','SSSSSSSSSSSSS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCOPT','TRAINF','GEN',9,'Liquidaciones de Ordenes','FRMLCOPT','SSSSSSSSSSSSS',0,'')
GO
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('OST','01','Predeterminado','LtOst.rpt',1,1,1,0,1,'paQryTraOrdenServRel','','Ordenes de Servicio')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('OSA','01','Predeterminado','LtOsa.rpt',1,1,1,0,1,'paQryTraOrdenServRel','','Ordenes de Servicio')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('AOS','01','Predeterminado','LtAos.rpt',1,1,1,0,1,'paQryTraOrdenAntRel','','Anticipos-Ordenes de Servicio')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('DAO','01','Predeterminado','LtDao.rpt',1,1,1,0,1,'paQryTraDevAosRel','','Devolución de Anticipos')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('COS','01','Predeterminado','LtCos.rpt',1,1,1,0,1,'paQryTraOrdenCumRel','','Cumplidos - Ordenes de Servicio')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('DCO','01','Predeterminado','LtDco.rpt',1,1,1,0,1,'paQryTraDevCosRel','','Devolución de Cumplidos')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('OPT','01','Predeterminado','LtOpt.rpt',1,2,1,0,1,'paQryTraOrdenLiqRel','','Liquidaciones de Ordenes')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('DLS','01','Predeterminado','LtDls.rpt',1,2,1,0,1,'paQryTraDevLgoRel','','Devolución de Liquidaciones')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('LGO','01','Predeterminado','LtLgo.rpt',1,1,1,0,1,'paQryTraOrdenLiqRel','','Legalizaciones de Ordenes')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('LGO','02','Detalles','LtLgoDet.rpt',1,1,1,0,1,'paQryTraOrdenLiqDet','','Legalizaciones de Ordenes')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('DLO','01','Predeterminado','LtDlo.rpt',1,1,1,0,1,'paQryTraDevLgoRel','','Devolución de Legalizaciones')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevAosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevAosRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevCosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevCosRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLgoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLgoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAntAbo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntAbo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAntRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenCumRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenCumRel]
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].[paQryTraOrdenLiqRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqRel]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAntRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=NULL
 AS 
 SELECT A.TipDoc AS TipoAnt,TipoDoc,Anticipo,A.IdCia AS CdCia,Compania,A.Fecha AS FecAnt,A.IdConcepto AS CdConcepto,Concepto,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,O.IdVehiculo AS PlacaVeh,nRemolque,O.TipoAfiVehic,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,O.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,AG.Agencia,A.VrAnticipo,A.VrAbonado,A.VrAnticipo-A.VrAbonado AS VrSaldo,TipoPago,A.NumCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,PU.NomCuenta,CedBenef,Beneficiario,FechaVence,A.TipCom,A.Comprobante,A.IdCiaCom,A.Anulado AS Anuldo,A.NumDev
,A.FecDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,A.OrigenAdd,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaModif,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario
--datos de la orden
,O.Modalidad,O.TipoOrden,O.VrTotal,O.VrCosto,O.Cantidad,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,IdDestino,LD.Localidad AS NomDestino
,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.Observacion AS OdsObserv
--Cliente
,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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraOrdenAnt AS A INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 INNER JOIN Companias AS CN ON A.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON A.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
 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 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 Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS AG ON O.IdAgencia=AG.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS PU ON A.IdCuenta=PU.IdCuenta
 WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAntAbo] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=NULL
 AS 
 SELECT A.TipDoc AS TipoAnt,TD.TipoDoc,A.Anticipo,A.IdCia AS CdCia,CN.Compania,A.Fecha AS FecAnt,A.IdConcepto AS CdConcepto,Concepto,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,O.IdVehiculo AS PlacaVeh,nRemolque,O.TipoAfiVehic,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,O.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,AG.Agencia,A.VrAnticipo,A.VrAbonado,TipoPago,A.NumCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,PU.NomCuenta,CedBenef,Beneficiario,FechaVence,A.TipCom,A.Comprobante,A.IdCiaCom,A.Anulado AS Anuldo,A.NumDev
,A.FecDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,A.OrigenAdd,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaModif,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario
--datos de la orden
,O.Modalidad,O.TipoOrden,O.VrTotal,O.VrCosto,O.Cantidad,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,IdDestino,LD.Localidad AS NomDestino
,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.Observacion AS OdsObserv
,AP.TipDoc AS CdTipoAbo,TDA.TipoDoc AS TipDocAbo,Documento,AP.IdCia AS CdCiaAbo,CA.Compania AS CompaniaAbo,Item,AP.Fecha AS FechaAbo,VrAbono,TipoAplica,AP.Observacion AS ObservAbono,CodCuenta
--Cliente
,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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraOrdenAnt AS A INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 INNER JOIN Companias AS CN ON A.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON A.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
 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 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 Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS AG ON O.IdAgencia=AG.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS PU ON A.IdCuenta=PU.IdCuenta
 LEFT JOIN Trn_TraOrdenAbo AS AP ON A.TipDoc=AP.TipAnt AND A.Anticipo=AP.Anticipo AND A.IdCia=AP.IdCiaAnt
 LEFT JOIN Companias AS CA ON AP.IdCia=CA.IdCia LEFT JOIN Sys_TiposDoc AS TDA ON AP.TipDoc=TDA.IdDoc
 WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenCumRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT C.TipDoc AS IdTipDoc,TipoDoc,C.Cumplido AS NumCumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FechaCump,C.IdConcepto AS CdConcepto,Concepto,TipOds,C.NumOrden AS Num_Orden,IdCiaOds
,O.Fecha AS FecOrden,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdVehiculo AS PlacaVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor
,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,O.Modalidad,O.TipoOrden,O.VrAnticipo AS Vr_Anticipo,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,C.TipoAfiVehic,C.FecDespacho,C.FecRecibo,C.FecIngCertif,C.FecCertificado,DescServicio,C.Cantidad AS CumCantidad,C.VrTotal AS CumValor,C.VrCosto AS CumValTercero,C.RemCliente
,Funcionario,O.NumRemesa,O.CdCiaRem,O.kmtInicial,O.kmtFinal,O.IdDestino,LD.Localidad,C.TipCom,C.Comprobante AS NumComp,C.IdCiaCom,C.Anulado AS CumAnulado,C.NumDev,C.FecDev,C.Observacion AS Observ
,C.IdEstado AS CdEstado,Estado,C.OrigenAdd,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaModif,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
--detalles
,Item,D.IdMercancia,D.Descripcion,D.Cantidad,D.UndMed AS Und_Med,Unidad,D.VrUnitario,D.TarifaPago,D.DocCliente
--Datos del Cliente
,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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
FROM Trn_TraOrdenCum AS C INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Conceptos AS CT ON C.IdConcepto=CT.IdConcepto
INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
INNER JOIN Trn_TraOrdenServ AS O ON C.TipOds=O.TipDoc AND C.NumOrden=O.NumOrden  AND C.IdCiaOds=O.IdCia
INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero
INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion  INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
INNER JOIN Trn_TraOrdenCumDet AS D ON C.TipDoc=D.TipDoc AND C.Cumplido=D.Cumplido AND C.IdCia=D.IdCia
INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed INNER JOIN Localidades AS L ON T.IdLocal=L.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 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 Localidades AS LD ON O.IdDestino=LD.IdLocal
LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
WHERE C.TipDoc=@pmTipDoc AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevAosRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT D.TipDev AS IdTipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Anticipo AS NumAnticipo,IdCiaDoc,FecDoc
,VrDevolucion,A.VrAnticipo,A.VrAbonado,A.VrAnticipo-A.VrAbonado AS SaldoActual,TipoPago,NumCheque,FecCheque,CdCta,NumeroCta,CT.IdBanco,Banco,CdCuenta,NomCuenta
,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden,O.IdVehiculo AS PlacaVeh,nRemolque,O.TipoAfiVehic,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor
,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,O.VrTotal,O.VrCosto,O.Cantidad
,CedBenef,Beneficiario,FechaVence,D.ModdDev,D.OrigenAdd,D.TipCom,D.Comprobante AS NumComp,D.IdCiaCom,D.Observacion AS Observ
,D.IdEstado AS CdEstado,Estado,D.TimeSys AS FechaCrea,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,Leyenda
--datos orden
,O.Modalidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,IdDestino,LD.Localidad AS NomDestino
,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.Observacion AS OdsObserv
--Cliente
,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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevAos AS D INNER JOIN Trn_TraOrdenAnt AS A ON D.TipDoc=A.TipDoc AND D.Anticipo=A.Anticipo AND D.IdCiaDoc=A.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Localidades AS L ON T.IdLocal=L.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 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 EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN CtasCorrientes AS CT ON D.CdCta=CT.IdCta LEFT JOIN Bancos AS B ON CT.IdBanco=B.IdBanco
 LEFT JOIN Puc AS PU ON D.CdCuenta=PU.IdCuenta LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
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,VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
--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 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevCosRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev AS IdTipDev,TipoDoc,D.Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Cumplido AS NumCump,D.IdCiaDoc,D.FecDoc
,TipOds,C.NumOrden AS Num_Orden,IdCiaOds,O.Fecha AS FecOrden,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdVehiculo AS PlacaVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor
,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,O.Modalidad,O.TipoOrden,O.VrAnticipo AS Vr_Anticipo,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,C.TipoAfiVehic,C.FecDespacho,C.FecRecibo,C.FecIngCertif,C.FecCertificado,DescServicio,C.Cantidad AS CumCantidad,C.VrTotal AS CumValor,C.VrCosto AS CumValTercero,C.RemCliente
,Funcionario,O.NumRemesa,O.CdCiaRem,O.kmtInicial,O.kmtFinal,O.IdDestino,LD.Localidad,D.ModdDev,D.TipCom,D.Comprobante AS NumComp,D.IdCiaCom,D.Observacion AS Observ
,D.OrigenAdd,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
--detalles
,Item,DC.IdMercancia,DC.Descripcion,DC.Cantidad,DC.UndMed AS Und_Med,Unidad,DC.VrUnitario,DC.TarifaPago,DC.DocCliente
--Datos del Cliente
,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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
FROM Trn_TraDevCos AS D INNER JOIN Trn_TraOrdenCum AS C ON D.TipDoc=C.TipDoc AND D.Cumplido=C.Cumplido AND D.IdCiaDoc=C.IdCia
INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
INNER JOIN Conceptos AS CT ON D.IdConcepto=CT.IdConcepto INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
INNER JOIN Trn_TraOrdenServ AS O ON C.TipOds=O.TipDoc AND C.NumOrden=O.NumOrden  AND C.IdCiaOds=O.IdCia
INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero
INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion  INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
INNER JOIN Trn_TraOrdenCumDet AS DC ON C.TipDoc=DC.TipDoc AND C.Cumplido=DC.Cumplido AND C.IdCia=DC.IdCia
INNER JOIN Sys_Um AS UM ON DC.UndMed=UM.UndMed INNER JOIN Localidades AS L ON T.IdLocal=L.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 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 Localidades AS LD ON O.IdDestino=LD.IdLocal
LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@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,VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
,DC.Item,DC.IdConcepto AS DetCodConc,DescConcepto,DC.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito
,DC.IdTercero AS NitTercero,TDT.RazonSocial AS NomTercero,DC.CodAgncia,CdCCosto,CCosto,CdSubCos,SubCosto,pVehiculo
,RubroConc,DC.VrBase,DC.TarifBase,DC.TipFac,DC.Factura,DC.IdCiaFac,ItemFac,DC.FecVence,RefConcep,CD.Concepto AS DetConcep
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Sys_TiposDoc AS TD ON L.TipDoc=TD.IdDoc INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN Trn_TraLiqDetalle AS DC ON L.TipDoc=DC.TipDoc AND L.Liquidacion=DC.Liquidacion AND L.IdCia=DC.IdCia
 INNER JOIN ConcDiversos AS CD ON DC.IdConcepto=CD.IdConcepto 
 LEFT JOIN Puc AS PC ON DC.IdCuenta=PC.IdCuenta LEFT JOIN Terceros AS TDT ON DC.IdTercero=TDT.IdTercero
 LEFT JOIN CentroCosto AS CC ON DC.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON DC.CdSubCos=SC.IdSubCos 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevLgoRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev AS IdTipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Liquidacion AS NumLiquida,D.IdCiaDoc,FecDoc
,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
,D.VrTotal,D.VrCosto,D.VrAnticipo,D.VrGastos,D.VrIva,D.VrRetencion,D.VrReteIca,D.VrReteCREE,D.VrReteIva,D.VrOtros,D.VrDescuento,D.VrNeto,D.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,L.Referencia,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad
,D.CxPagar,D.TipCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.OrigenAdd,D.TimeSys AS FechaCrea
,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.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
 FROM Trn_TraDevLgo AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON D.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Trn_TraOrdenLiq AS L ON D.TipDoc=L.TipDoc AND D.Liquidacion=L.Liquidacion AND D.IdCiaDoc=L.IdCia
 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 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 Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo  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
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO