SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[paQryVehiculosPol] @pmFEIni SMALLDATETIME=Null,@pmFEFin SMALLDATETIME=Null,@pmFVIni SMALLDATETIME=Null,@pmFVFin SMALLDATETIME=Null
 AS 
 SELECT V.IdVehiculo AS PlacaVeh,NumVeh,ClaseVeh,V.IdTipoVeh AS CdTipo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinea,LineaVeh
,V.IdColor AS CdColor,NomColor,V.IdTipoMot AS CdTipMotor,TipoMotor,V.IdCrceria AS CdCarr,TipoCar,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans
,V.IdCat AS CodCatg,Catpeaje,CdCatv,ClaseMat,Cilind,CapTanq,V.IdCom AS CdTipComb,TipoComb,V.IdLub AS CdLub,TipoLub,V.IdTlla AS CdTipLlantas,TipoLlanta
,IdMarlla,ML.Marca AS MarcaLlantas,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen
,NitEmpresa,NE.RazonSocial AS Empresa,IdPropietario,NP.RazonSocial AS Propietario,IdPoseedor,NT.RazonSocial AS Poseedor,IdConductor,NC.RazonSocial AS Conductor,V.IdPpd AS CdTipProp,TipoProp
,TP.VehPropio,V.TipoAfil,Adquisc,NitProv,NPV.RazonSocial AS Proveedor,FecCompra,VrComcial,VrAseg,VrAvaludo,VidaUtil,FecSalida,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon,V.IdNiv AS CdNivel,NivelServicio
,V.IdGrupo AS CdGrupo,GrupoProp,CdGrupR,CdTarifa,FecIngreso,V.FecVigencia AS FechVigCont,V.FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,TarjProp,FecTProp,VigTProp
,CdLugTp,LT.Localidad AS LugarTarjProp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,V.RevTecMec,V.FecTecMec,V.VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper
,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,V.VrLmtCred,V.VrSaldoAct,V.Descripcion,V.Observacion AS Observ,V.CdCenSer,CentroServ,V.CdLocal,LU.Localidad AS CiuUbicacion
,LU.IdDep AS CodDpto,Departamento,V.Ubicacion,V.PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil
,CdRutaHab,DeclaracImp,TipoIngreso,V.IdOrgTra AS CdOrgTra,NomOrgTrans,GPSoperador,GPSUsuario,GPSClave,CantFiltros,V.IdEstado AS CdEstado,Estado,V.Inactivo AS Inactvo,V.IdUsuario AS CdUsuario,Usuario
,V.FechaAdd AS Fec_Add,V.FechaUpdate AS Fec_Upd,EV.NColor AS NumColor,OutDemand
--información de polizas
,VP.IdClase AS CodClase,ClasePoliza,Numero,FecEmision,VP.FecVigencia AS FecVencePoliza,NitCompania,TCPX.RazonSocial AS NombreCia,Valor,Comntarios,DiasAviso
 FROM Vehiculos AS V INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposMot AS TM ON V.IdTipoMot=TM.IdTipoMot
 INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria INNER JOIN PeajesCat AS CP ON V.IdCat=CP.IdCat
 INNER JOIN TiposFuel AS TF ON V.IdCom=TF.IdCom INNER JOIN TiposLub AS TL ON V.IdLub=TL.IdLub
 INNER JOIN TiposLla AS TLL ON V.IdTlla=TLL.IdTlla INNER JOIN Marcas AS ML ON V.IdMarlla=ML.IdMarca
 INNER JOIN Terceros AS NP ON V.IdPropietario=NP.IdTercero  INNER JOIN Terceros AS NT ON  V.IdPoseedor=NT.IdTercero
 INNER JOIN Terceros AS NC ON V.IdConductor=NC.IdTercero INNER JOIN TiposPpt AS TP ON V.IdPpd=TP.IdPpd
 INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon
 INNER JOIN TiposNivs AS NV ON V.IdNiv=NV.IdNiv
 LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NPV ON V.NitProv=NPV.IdTercero
 LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero LEFT JOIN Localidades AS LT ON V.CdLugTp=LT.IdLocal
 LEFT JOIN CentrosServ AS CS ON V.CdCenSer=CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 LEFT JOIN OrgTransito AS OG ON V.IdOrgTra=OG.IdOrgTra
 --subconsulta polizas  LEFT JOIN VehPolizas AS VP ON V.IdVehiculo=VP.IdVehiculo
 LEFT JOIN (SELECT * FROM VehPolizas WHERE FecEmision BETWEEN ISNULL(@pmFEIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFEFin,CAST('20781230' AS SMALLDATETIME))
 AND FecVigencia BETWEEN ISNULL(@pmFVIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFVFin,CAST('20781230' AS SMALLDATETIME))) AS VP ON V.IdVehiculo=VP.IdVehiculo
 LEFT JOIN ClasePol AS CLP ON VP.IdClase=CLP.IdClase LEFT JOIN Terceros AS TCPX ON VP.NitCompania=TCPX.IdTercero