IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraAnticiposFmt]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTraAnticiposFmt]
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,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,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,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 idCuenta,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