if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecBuses_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRecBuses_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecBuses_Crl]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRecBuses_Crl]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecBuses_Crr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRecBuses_Crr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraAnticiposFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraAnticiposFmt]
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].[paQryTraDevAntFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevAntFmt]
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].[paQryTraDevAosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevAosFmt]
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].[paQryTraOrdenAntAbo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntAbo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAntFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntFmt]
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


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryRecBuses_Cr] @pmTipDoc VARCHAR(3), @pmReciboIni INT,@pmReciboFin INT, @pmIdCia CHAR(2)
AS
 SELECT  R.TipDoc AS IdTipRec,TipoDoc, Recibo,R.IdCia AS CodCia,Compania, R.Fecha AS FechaRec, R.IdConcepto AS IdConcept,Concepto, R.IdCliente AS NitPropietario,NP.RazonSocial AS Propietario, R.IdAgencia, R.Modalidad AS RecModalidad, TipCar, Cartulina, IdCiaCar,R.IdRuta,Ruta,IdRelevador
, R.IdVehiculo AS PlacaVeh, R.IdConductor AS CedConductor,NC.RazonSocial AS Conductor, R.VehPropio AS VehcPropio, R.CentInicial AS CenInicial, R.CentFinal AS CenFinal, NPasajeros, Recorridos,ValorRecibido, ValorTotal, ValorDcto, ValorReteFte, ValorReteIva, ValorReteIca, ValorOtros
, ValorOtrosDct, ValorPagMas, ValorNeto,ValorAplicado, ValorAnticipo, ValorAbono, TotalProducido, TotalGastos, TotalAhorro, TotalOtros, TotalVales, TotalAbono, ValorPasaje, IdVendedor,R.Comision,IdTarCom, R.IdCCosto AS IdCCost,CCosto,EnEfectivo,R.Referencia AS RefeRecibo, BaseIva
, BaseRet, BaseOtr, TarifaIva, TarifaRet, TarifaOtr, IdTarIva,IdTarRet, IdTarOtr,R.IdClase AS CodClase, ClaseCuenta,R.TipCom AS IdTipoCom,TM.TipoCom AS TipoComprobante, Comprobante, IdCiaCom, Anulado, NumDev, FecDev, R.Observacion AS Observ, R.IdEstado AS IdEstad,ED.Estado AS EstadDoc, R.TimeSys, FecUpdate, IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario,Leyenda
,Item,F.IdForma AS IdForm,FormaPago,VrPagado,VrCambio,EsCaja,FP.NumForma AS NumeroCheque, FP.IdBanco AS IdBanc,Banco,CtaForma,Beneficiario,NitCliente,FecForma,CdCta,NumeroCta,FP.CdLocal AS CodPlaza,PZA.Localidad AS Plaza
, FP.Referncia1 AS RefCheque,Referncia2,VrDenom,Cantidad,TipDenom,FP.Detalle AS DetallePago,TipRef,DocRef,IdCiaRef
--Datos del vehículo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen
,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TPR.TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,V.CentInicial AS VehCentInicio,V.CentFinal AS CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado
,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo
--datos del propietario
,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep
,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil
--datos de conductor
,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto
,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula
,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic
 FROM  Trn_RecBuses AS R INNER JOIN Sys_TiposDoc  AS TD ON R.TipDoc=TD.IdDoc
 INNER JOIN Trn_Pagos AS FP ON R.TipDoc=FP.TipDoc AND R.Recibo=FP.Documento AND R.IdCia=FP.IdCia
 INNER JOIN Companias  AS CI ON R.IdCia=CI.IdCia  INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado  INNER JOIN adm_Usuarios AS U ON  R.IdUsuario=U.IdUsuario
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN Terceros AS NP ON R.IdCliente=NP.IdTercero
 INNER JOIN Terceros AS NC ON R.IdConductor=NC.IdTercero  INNER JOIN BusClaseCue AS CU ON R.IdClase=CU.IdClase
 INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto  INNER JOIN Bancos AS B ON FP.IdBanco=B.IdBanco
 INNER JOIN Formaspago AS F ON FP.IdForma=F.IdForma 
 INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep
 INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor 
  INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
  INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
  LEFT JOIN TiposFuel AS TC ON  V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR  ON V.IdPpd=TPR.IdPpd
  LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv
  LEFT JOIN  TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa 
  LEFT JOIN TercCndtores AS CT ON  R.IdConductor=CT.IdConductor
  LEFT JOIN  Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd
  LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar
 LEFT JOIN CtasCorrientes AS CTA ON FP.CdCta=CTA.IdCta  LEFT JOIN Localidades AS PZA ON FP.CdLocal=PZA.IdLocal
 LEFT JOIN Rutas AS RT ON  R.IdRuta=RT.IdRuta LEFT JOIN TiposCom AS TM ON R.TipCom=TM.IdCom
 WHERE R.TipDoc=@pmTipDoc AND  Recibo BETWEEN @pmReciboIni AND @pmReciboFin AND   R.IdCia=@pmIdCia
 ORDER BY R.IdCia,Recibo,item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryRecBuses_Crl] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME , @pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null, @pmEnEfectivo BIT=Null,@pmAnulado BIT=Null
,@pmIdEstado VARCHAR(4)=Null,@pmIdUsuario VARCHAR(11)=Null,@pmIdAdmon VARCHAR(4)=Null
AS
 SELECT Recibo, R.IdCia AS CodCia, R.Fecha AS FecRecibo,TipCar, R.Cartulina AS NumCartulina, IdCiaCar,CL.Fecha AS FecCartulina,NumOrden,FechaRec,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,R.IdRuta AS CodRuta,Ruta
,R.CentInicial AS CentenaIni,R.CentFinal AS CentenaFin,R.NPasajeros AS NPasajers,R.Recorridos AS Recorrdos,R.ValorPasaje AS ValorPasje,TotalProducido,TotalGastos ,TotalVales,TotalGastos+TotalVales AS TtGastos,TotalAhorro,TotalOtros,TotalAbono,ValorTotal,R.ValorOtros AS ValorOtrs
,ValorOtrosDct,ValorPagMas,ValorNeto,ValorAplicado,ValorAnticipo,ValorRecibido,EnEfectivo,NumCheque, R.IdBanco AS CodBanco,Banco,Referencia,TipCom,TipoCom, Comprobante, IdCiaCom, R.Anulado AS Anlado, R.NumDev As Ndevolucion, R.FecDev AS FechDev,R.Observacion AS Observ
,R.IdEstado AS IdEstad, ED.Estado AS EstadDoc,R.TimeSys AS Time_sys, R.FecUpdate AS FechUpdate, R.IdCiaCrea AS CodCiaCrea,R.IdUsuario,Usuario,Compania,R.IdRelevador AS IdRelevadr,NR.RazonSocial AS Relevador,R.IdConcepto AS IdConcep,Concepto,R.IdClase AS IdClaCue,ClaseCuenta,R.VehPropio AS VPropio
,IdCliente,NP.RazonSocial AS Propietario,NumAutoriza
--Datos del vehículo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen
,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TPR.TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TA.TipoAdmon AS VehTipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,V.CentInicial AS VehCentInicio,V.CentFinal AS VehCentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado
,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo
--datos de conductor
,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto
,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula
,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic
  FROM Trn_RecBuses AS R INNER JOIN Trn_Despachos AS CL ON R.TipCar=CL.TipDoc AND R.Cartulina=CL.Cartulina AND R.IdCiaCar=CL.IdCia
  INNER JOIN Companias  AS CI ON R.IdCia=CI.IdCia
  INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto  INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
  INNER JOIN adm_Usuarios AS U ON  R.IdUsuario=U.IdUsuario 
  INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo   INNER JOIN Terceros AS NP ON R.IdCliente=NP.IdTercero
  INNER JOIN Terceros AS NC ON R.IdConductor=NC.IdTercero  INNER JOIN BusClaseCue AS CU ON R.IdClase=CU.IdClase
  INNER JOIN Bancos AS B ON R.IdBanco=B.IdBanco  INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
    INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor 
  INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
  INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
  LEFT JOIN TiposFuel AS TC ON  V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR  ON V.IdPpd=TPR.IdPpd
  LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv
  LEFT JOIN  TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa   LEFT JOIN TercCndtores AS CT ON R.IdConductor=CT.IdConductor
  LEFT JOIN  Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd
  LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar
  LEFT JOIN Rutas AS RT ON  R.IdRuta=RT.IdRuta LEFT JOIN Terceros AS NR ON R.IdRelevador=NR.IdTercero
  LEFT JOIN TiposCom AS TM ON R.TipCom=TM.IdCom
 WHERE R.TipDoc=@pmTipDoc  AND  R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND  R.Modalidad like ISNULL(@pmModalidad,'%') 
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND R.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND R.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') 
 AND  (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 AND  (R.Anulado=ISNULL(@pmAnulado,0) or R.Anulado=ISNULL(@pmAnulado,1))
 AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND R.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 AND R.IdConductor LIKE ISNULL(@pmIdConductor,'%')  AND V.IdAdmon LIKE ISNULL(@pmIdAdmon,'%')
 ORDER BY R.IdCia,Recibo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryRecBuses_Crr] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME , @pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null, @pmEnEfectivo BIT=Null,@pmAnulado BIT=Null
,@pmIdEstado VARCHAR(4)=Null,@pmIdUsuario VARCHAR(11)=Null,@pmIdAdmon VARCHAR(4)=Null
AS
 SELECT  Recibo, R.IdCia AS CodCia, Fecha, R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,IdCliente,NP.RazonSocial AS Propietario,ValorRecibido, ValorTotal, ValorDcto, ValorReteFte, ValorReteIva, ValorReteIca, ValorOtros, ValorOtrosDct
, ValorPagMas, TotalAhorro, TotalOtros, ValorNeto,ValorAplicado,ValorAnticipo,R.IdCta AS CodCta,NumeroCta,EnEfectivo, NumCheque, R.IdBanco AS IdBanc,Banco,Referencia,R.IdClase AS IdClasCue,ClaseCuenta,R.Modalidad AS RecModalidad,R.IdConcepto AS IdConcep,Concepto
,TipCom,TipoCom, Comprobante, IdCiaCom, Anulado, NumDev, FecDev, R.Observacion AS Observ, R.IdEstado AS IdEstad,ED.Estado AS EstadDoc, TimeSys, FecUpdate, IdCiaCrea,R.IdUsuario,Usuario,Compania,BaseIva, BaseRet, BaseOtr
,TarifaIva, TarifaRet, TarifaOtr, IdTarIva,IdTarRet, IdTarOtr,TipCar, Cartulina, IdCiaCar,R.IdRuta AS CodRuta,Ruta,IdRelevador,NR.RazonSocial AS Relevador,R.CentInicial AS CentenaIni, R.CentFinal AS CentenaFin, NPasajeros, Recorridos,ValorPasaje,TotalProducido
,TotalGastos ,TotalVales,TotalGastos+TotalVales AS TtGastos,TotalAbono,R.VehPropio AS VhPropio
--inf. vehículo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen
,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TPR.TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,V.CentInicial AS VehCentInicio,V.CentFinal AS VehCentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado
,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo
--datos de conductor
,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto
,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula
,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic
  FROM Trn_RecBuses AS R  INNER JOIN Companias  AS CI ON R.IdCia=CI.IdCia
  INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto  INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
  INNER JOIN adm_Usuarios AS U ON  R.IdUsuario=U.IdUsuario
  INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo   INNER JOIN Terceros AS NP ON R.IdCliente=NP.IdTercero
  INNER JOIN Terceros AS NC ON R.IdConductor=NC.IdTercero  INNER JOIN BusClaseCue AS CU ON R.IdClase=CU.IdClase
  INNER JOIN Bancos AS B ON R.IdBanco=B.IdBanco   INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
  INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor 
  INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
  INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
  LEFT JOIN TiposFuel AS TC ON  V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR  ON V.IdPpd=TPR.IdPpd
  LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv
  LEFT JOIN  TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa 
  LEFT JOIN TercCndtores AS CT ON R.IdConductor=CT.IdConductor
  LEFT JOIN  Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd
  LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar
  LEFT JOIN Rutas AS RT ON  R.IdRuta=RT.IdRuta LEFT JOIN CtasCorrientes AS CTA ON R.IdCta=CTA.IdCta LEFT JOIN Terceros AS NR ON R.IdRelevador=NR.IdTercero
  LEFT JOIN TiposCom AS TM ON R.TipCom=TM.IdCom
  WHERE TipDoc=@pmTipDoc AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND R.Modalidad like ISNULL(@pmModalidad,'%') 
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND R.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND R.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') 
 AND  (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 AND  (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND R.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 AND R.IdConductor LIKE ISNULL(@pmIdConductor,'%')  AND V.IdAdmon LIKE ISNULL(@pmIdAdmon,'%')
 ORDER BY R.IdCia,Recibo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevAosFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
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,A.Cantidad AS CantGalones
,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden,O.IdVehiculo AS PlacaVeh,nRemolque,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,TPR.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.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAntFmt] @pmTipDoc VARCHAR(3),@pmAnticipoIni INT,@pmAnticipoFin INT,@pmIdCia CHAR(2)
 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,TipoPago,A.NumCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,PU.NomCuenta,CedBenef,Beneficiario,FechaVence,A.TipoAncpo,A.NumPresAnt,A.Cantidad AS CantGalones,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,Leyenda
--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
--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,TPR.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
--detalles
,Item,DC.IdCuenta AS Cd_Cuenta,PC.NomCuenta AS Nom_Cuenta,Detalle,VrDebito,VrCredito,DC.IdCCosto AS DetCodCentCost,DCC.CCosto AS DetCentCost
,DC.IdSubCos AS DetCodSubCentro,DS.SubCosto AS DetSubCentro,VrBase,TarifaBase,Referncia
 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_ComDetalle AS DC ON A.TipCom=DC.TipCom AND A.Comprobante=DC.Comprobante AND A.IdCiaCom=DC.IdCia
 LEFT JOIN Puc AS PC ON DC.IdCuenta=PC.IdCuenta LEFT JOIN CentroCosto AS DCC ON DC.IdCCosto=DCC.IdCCosto
 LEFT JOIN SubCentros AS DS ON DC.IdSubCos=DS.IdSubCos
 WHERE A.TipDoc=@pmTipDoc AND A.Anticipo BETWEEN @pmAnticipoIni AND @pmAnticipoFin AND A.IdCia=@pmIdCia
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,TPR.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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraAnticiposFmt] @pmTipDoc VARCHAR(3),@pmAnticipoIni INT,@pmAnticipoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT A.TipDoc AS TipoAnt,TipoDoc,Anticipo,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,VrDeduccion,Modalidad,TipoPago,A.NumCheque AS NroCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,P.NomCuenta AS DescCuenta,CedBenef,Beneficiario,A.Cantidad AS CantGals,FechaVence,NumPresAnt,PreAnticipo,NumCargue,CiaCargue,A.TipCom AS CdTipCom,TipoCom,A.Comprobante AS NumComp,A.IdCiaCom AS CdCiaComp
,A.Anulado AS Anuldo,NumDev,A.FecDev AS FechaDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaAct,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario,Leyenda
--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
--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,TPR.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
--comprobante
,Item,DC.IdCuenta AS CodCuenta,PC.NomCuenta AS Nom_Cuenta,Detalle,VrDebito,VrCredito,DC.IdTercero AS NitTercero,DT.RazonSocial AS NomTercero,DC.IdCCosto AS DetCodCentCost,DCC.CCosto AS DetCentCost
,DC.IdSubCos AS DetCodSubCentro,DS.SubCosto  AS DetSubCentro,VrBase,TarifaBase,TipFac,Factura,IdCiaFac,ItemFac,FecVence,DC.NumCheque AS DetNumCheque,TipoAplica,NitOtros,Referncia
 FROM Trn_TraAnticipos AS A  INNER JOIN Companias AS CN ON A.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado 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 TercCndtores AS CT ON A.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 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  Trn_ComDetalle AS DC ON A.TipCom=DC.TipCom AND A.Comprobante=DC.Comprobante AND A.IdCiaCom=DC.IdCia
 LEFT JOIN Puc AS PC ON DC.IdCuenta=PC.IdCuenta LEFT JOIN Terceros AS DT ON DC.IdTercero=DT.IdTercero
 LEFT JOIN CentroCosto AS DCC ON DC.IdCCosto=DCC.IdCCosto  LEFT JOIN SubCentros AS DS ON DC.IdSubCos=DS.IdSubCos
 WHERE A.TipDoc=@pmTipDoc AND A.Anticipo BETWEEN @pmAnticipoIni AND @pmAnticipoFin AND A.IdCia=@pmIdCia
 ORDER BY A.Anticipo
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
,@pmtmEst CHAR(2)=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.VrDeduccion,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,TPR.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
--Apr 10/2018 Deducciones de anticipos ,tmConcepto1,tmValor1,tmConcepto2,tmValor2,tmConcepto3,tmValor3,tmConcepto4,tmValor4,tmConcepto5,tmValor5
 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
--Apr 10/2018 Deducciones de anticipos LEFT JOIN tm_TraDedAnt AS DA ON A.TipDoc=DA.tmTipAnt AND A.Anticipo=DA.tmAnticipo AND A.IdCia=DA.tmIdCia 
 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].[paQryTraDevAntFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Anticipo,IdCiaDoc,FecDoc
,D.IdVehiculo AS PlacaVeh,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,VrDevolucion
,CdCta,NumeroCta,CTA.IdBanco,Banco,CdCuenta,NomCuenta,ModdDev,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ
,TimeSys,IdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,Leyenda
--Datos poseedor y 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,TPR.TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevAnt AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 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
 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
 WHERE D.TipDev=@pmTipDev AND D.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
  ORDER BY D.Devolucion
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,A.VrDeduccion,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,TPR.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].[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,TipoAncpo,NumPresAnt,A.Cantidad AS CantGalones,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
,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.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,TPR.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 Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 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].[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,A.Cantidad AS CantGalones
,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,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.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,TPR.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
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 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].[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,TPR.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