if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercOperarios_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTercOperarios_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraAnticiposRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraAnticiposRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraAnticiposRelAbo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraAnticiposRelAbo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevAntRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevAntRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLegRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLegRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLegRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLegRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemCancelaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemCancelaLta]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT LQ.TipDoc AS TipoLiq,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc
,LQ.IdVehiculo AS PlacaVeh,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,LQ.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,LQ.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Referencia,DescMcias,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario
--detalles
,Item,D.IdConcepto AS DetCodConc,CG.Concepto AS DetNomConc,DescConcepto,D.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito,D.IdTercero AS NitTercero,N.RazonSocial AS NomTercero
,CdCCosto,CCosto,CdSubCos,SubCosto,RubroConc,VrBase,TarifBase,CantComb,TipFac,Factura,IdCiaFac,ItemFac,FecVence,RefConcep
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraLiqViajes AS LQ  INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON LQ.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON LQ.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON LQ.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS ETR ON LQ.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraLiqDetalle AS D ON LQ.TipDoc=D.TipDoc AND LQ.Liquidacion=D.Liquidacion AND LQ.IdCia=D.IdCia
 LEFT JOIN ConcDiversos AS CG ON D.IdConcepto=CG.IdConcepto LEFT JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS N ON  D.IdTercero=N.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 WHERE LQ.TipDoc=@pmTipDoc  AND LQ.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND LQ.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND LQ.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND LQ.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND LQ.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND LQ.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY LQ.IdCia,LQ.Liquidacion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevAntRel] @pmTipDev 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 TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,VrDevolucion,D.TipDoc AS TipoAnt,D.Anticipo AS NumAnticipo,IdCiaDoc,D.FecDoc AS FechaAnt
,D.IdVehiculo AS PlacaVeh,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,CdCta,NumeroCta,CTA.IdBanco,Banco,CdCuenta,NomCuenta,ModdDev,D.TipCom AS TipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp,D.Observacion AS Observ
,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,TipMuc,A.Manifiesto AS NumManif,IdCiaMuc,Modalidad,TipoPago,NumCheque,FecCheque,Beneficiario,A.Cantidad AS CantGals,PreAnticipo
--datos del vehículo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraDevAnt AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Terceros AS  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 Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraAnticipos AS A ON D.TipDoc=A.TipDoc AND D.Anticipo=A.Anticipo AND D.IdCiaDoc=A.IdCia
 LEFT JOIN CtasCorrientes AS CTA ON D.CdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS P ON D.CdCuenta=P.IdCuenta LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 WHERE D.TipDev=@pmTipDev AND  D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%') 
 ORDER BY D.IdCia,D.Devolucion
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraAnticiposRel] @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,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT A.TipDoc AS TipoAnt,A.Anticipo AS NumAnticipo,A.IdCia AS CdCia,Compania,A.Fecha AS FechaAnt,A.IdConcepto AS CdConcepto,Concepto,TipMuc,A.Manifiesto AS NumManif,IdCiaMuc,A.IdVehiculo AS PlacaVeh
,A.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,A.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,A.VrAnticipo AS ValAnticipo,VrAbonado,A.VrAnticipo-VrAbonado AS VrSaldo,Modalidad,TipoPago,NumCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,NomCuenta,CedBenef,Beneficiario,A.Cantidad AS CantGals,FechaVence,NumPresAnt,PreAnticipo,NumCargue,CiaCargue,A.TipCom,TipoCom,A.Comprobante,A.IdCiaCom
,A.Anulado AS Anuldo,NumDev,A.FecDev AS FechaDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaAct,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario
--datos de manifiesto
,FecDespacho,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,VrFletes,TarifaFlete,PesoTotal,EstCumplido,Cumplido,IdCiaCump,FechaCump
--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,V.IdGrupo AS CdGrupo,GrupoProp
--,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraAnticipos AS A  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 Terceros AS  CDT ON A.IdConductor=CDT.IdTercero  INNER JOIN Terceros AS T ON A.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON A.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 Trn_TraManifiesto AS M ON A.TipMuc=M.TipDoc AND A.Manifiesto=M.Manifiesto AND  A.IdCiaMuc=M.IdCia
 LEFT JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON M.IdRuta=R.IdRuta 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS P ON A.IdCuenta=P.IdCuenta LEFT JOIN TiposCom AS TCM ON A.TipCom=TCM.IdCom 
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
-- LEFT JOIN TercCndtores AS CT ON A.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND A.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND A.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND A.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND A.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND A.Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY A.IdCia,A.Anticipo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraAnticiposRelAbo] @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,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT A.TipDoc AS TipoAnt,A.Anticipo AS NumAnticipo,A.IdCia AS CdCia,Compania,A.Fecha AS FechaAnt,A.IdConcepto AS CdConcepto,Concepto,TipMuc,A.Manifiesto AS NumManif,IdCiaMuc,A.IdVehiculo AS PlacaVeh
,A.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,A.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,A.VrAnticipo AS ValAnticipo,VrAbonado,A.VrAnticipo-VrAbonado AS VrSaldo
,AP.TipDoc AS TipDocAbo,AP.Documento AS NumDocAbo,AP.IdCia AS CdCiaAbo,Item,AP.Fecha AS FechaAbo,VrAbono,TipoAplica,Detalle
,Modalidad,TipoPago,NumCheque,FecCheque,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,NomCuenta,CedBenef,Beneficiario,A.Cantidad AS CantGals,FechaVence,NumPresAnt,PreAnticipo,A.TipCom,TipoCom,A.Comprobante,A.IdCiaCom
,A.Anulado AS Anuldo,NumDev,A.FecDev AS FechaDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaAct,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario
--datos de manifiesto
,FecDespacho,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,VrFletes,TarifaFlete,PesoTotal,EstCumplido,Cumplido,IdCiaCump,FechaCump
--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,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraAnticipos AS A  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 Terceros AS  CDT ON A.IdConductor=CDT.IdTercero  INNER JOIN Terceros AS T ON A.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON A.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 Trn_TraManifiesto AS M ON A.TipMuc=M.TipDoc AND  A.Manifiesto=M.Manifiesto AND  A.IdCiaMuc=M.IdCia
 LEFT JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON M.IdRuta=R.IdRuta 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS P ON A.IdCuenta=P.IdCuenta LEFT JOIN TiposCom AS TCM ON A.TipCom=TCM.IdCom 
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Trn_TraAntAbonos AS AP ON A.TipDoc=AP.TipAnt AND A.Anticipo=AP.Anticipo AND A.IdCia=AP.IdCiaAnt
 WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND A.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND A.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND A.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND A.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND A.Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY A.IdCia,A.Anticipo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesRel] @pmTipDoc VARCHAR(3),@pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT LQ.TipDoc AS TipoLiq,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc
,LQ.IdVehiculo AS PlacaVeh,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,LQ.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,LQ.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Referencia,DescMcias,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraLiqViajes AS LQ  INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON LQ.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON LQ.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON LQ.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS ETR ON LQ.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 WHERE LQ.TipDoc=@pmTipDoc  AND LQ.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND LQ.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND LQ.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND LQ.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND LQ.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND LQ.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY LQ.IdCia,LQ.Liquidacion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevLegRel] @pmTipDev VARCHAR(3),@pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipLiq,D.Liquidacion AS NumLiquida,IdCiaDoc,FecDoc,D.Modalidad AS Modaldad
,D.TipMuc AS TipManif,D.Manifiesto AS NumManif,D.IdCiaMuc AS CdCiaManif,D.IdVehiculo AS PlacaVeh,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,D.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario
,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,D.VrAnticipos AS TotalAntcpos,D.VrGastos AS TotalGastos,D.VrCombustible AS TotalComb,D.VrDiferencia AS ValorDif
,D.VrOtrosCred AS TotalOtrosDed,D.galsComb AS CantGals,D.PesoTotal AS Peso_Total,D.Cantidad AS CantTotal,D.Volumen AS VolTotal,ModdDev,D.OrigenAdd AS OrigenCrea,D.TipCom AS CdTipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp
,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
--datos del vehículo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraDevLeg AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero   INNER JOIN Terceros AS NP ON D.IdPropietario=NP.IdTercero   
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraLiqViajes AS LQ ON  D.TipDoc=LQ.TipDoc AND D.Liquidacion=LQ.Liquidacion AND D.IdCiaDoc=LQ.IdCia
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  
 LEFT JOIN Terceros AS ETR ON D.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 WHERE D.TipDev=@pmTipDev  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY D.IdCia,D.Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevLegRelDet] @pmTipDev VARCHAR(3),@pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,D.TipDoc AS TipLiq,D.Liquidacion AS NumLiquida,IdCiaDoc,FecDoc,D.Modalidad AS Modaldad
,D.TipMuc AS TipManif,D.Manifiesto AS NumManif,D.IdCiaMuc AS CdCiaManif,D.IdVehiculo AS PlacaVeh,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,D.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario
,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,D.VrAnticipos AS TotalAntcpos,D.VrGastos AS TotalGastos,D.VrCombustible AS TotalComb,D.VrDiferencia AS ValorDif
,D.VrOtrosCred AS TotalOtrosDed,D.galsComb AS CantGals,D.PesoTotal AS Peso_Total,D.Cantidad AS CantTotal,D.Volumen AS VolTotal,ModdDev,D.OrigenAdd AS OrigenCrea,D.TipCom AS CdTipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp
,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
--detales
,Item,DT.IdConcepto AS DetCodConc,CG.Concepto AS DetNomConc,DescConcepto,DT.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito,DT.IdTercero AS NitTercero,N.RazonSocial AS NomTercero
,CdCCosto,CCosto,CdSubCos,SubCosto,RubroConc,VrBase,TarifBase,CantComb,TipFac,Factura,IdCiaFac,ItemFac,FecVence,RefConcep
--datos del vehículo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraDevLeg AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero   INNER JOIN Terceros AS NP ON D.IdPropietario=NP.IdTercero   
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraLiqViajes AS LQ ON  D.TipDoc=LQ.TipDoc AND D.Liquidacion=LQ.Liquidacion AND D.IdCiaDoc=LQ.IdCia
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  
 LEFT JOIN Terceros AS ETR ON D.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraLiqDetalle AS DT ON D.TipDoc=DT.TipDoc AND D.Liquidacion=DT.Liquidacion AND  D.IdCiaDoc=DT.IdCia
 LEFT JOIN ConcDiversos AS CG ON DT.IdConcepto=CG.IdConcepto LEFT JOIN Puc AS P ON DT.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS N ON  DT.IdTercero=N.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN CentroCosto AS CC ON DT.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON DT.CdSubCos=SC.IdSubCos 
 WHERE D.TipDev=@pmTipDev  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY D.IdCia,D.Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsTercOperarios_Sel] @pmIdOperario VARCHAR(16),@pmNewOperario VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdOperario FROM TercOperarios WHERE IdOperario=@pmIdOperario)
  INSERT INTO TercOperarios (IdOperario,IdTipoOper,TipoSalario,VrSalario)
  SELECT @pmNewOperario,IdTipoOper,TipoSalario,VrSalario
  FROM TercOperarios WHERE IdOperario=@pmIdOperario
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemCancelaLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT CR.Fecha,CR.TipDoc AS TipFac,CR.Factura,CR.IdCia AS CdCiaFac,CF.Compania AS CiaFactura,CR.TipRem,CR.Remesa,CR.IdCiaRem,CN.Compania AS CiaRemesa,CR.Item
,D.IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,Volumen,UndVol,NitRemite,Remitente,NitDestntario,Destinatario
,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,DirDestino,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,TarifClie,UndTarifa,TarifPago,UndTarifPago,D.Remision,DocCliente,Referencia1,Referencia2,Referencia3
,R.Fecha AS FecRemesa,R.FecDespacho,R.IdCliente,T.RazonSocial AS NombreCliente,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia
,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,R.nRemolque,R.TipoAfiVehic,R.Modalidad
,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,Cumplido,IdCiaCump,FechaCump,DetalleCump
,TipOdp,NumeroOdp,IdCiaOdp,EstFactura,Anulado,FecDev,R.Observacion AS RemObserv,CR.Observacion,CR.IdUsuario AS CdUsuario,Usuario,CR.FechaCrea,CR.Id
FROM Trn_TraRemCancela AS CR INNER JOIN Companias AS CN ON CR.IdCiaRem=CN.IdCia INNER JOIN adm_Usuarios AS U ON CR.IdUsuario=U.IdUsuario
INNER JOIN Trn_TraRemesa AS R ON CR.TipRem=R.TipDoc AND CR.Remesa=R.NumOrden AND CR.IdCiaRem=R.IdCia
INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero
LEFT JOIN Trn_TraRemMcias AS D ON CR.TipRem=D.TipDoc AND CR.Remesa=D.NumOrden AND CR.IdCiaRem=D.IdCia AND CR.Item=D.Item
LEFT JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
LEFT JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
LEFT JOIN Companias AS CF ON CR.IdCia=CF.IdCia LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
WHERE CR.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND CR.IdCiaRem LIKE ISNULL(@pmIdCia,'%%')
GO