SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[QryVehiculos]
AS
 SELECT IdVehiculo,NumVeh,V.IdTipoVeh AS CdTipo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca,V.IdLinea AS CdLinea,LineaVeh
,V.IdColor AS CdColor,NomColor,V.IdTipoMot AS CdTipMotor,TipoMotor,V.IdCrceria AS CdCarr,TipoCar,Modelo,FecRep,Config
,V.IdCom AS CdTipComb,TipoComb,NitEmpresa,NE.RazonSocial AS Empresa,IdPropietario,NP.RazonSocial AS Propietario,IdPoseedor,NT.RazonSocial AS Poseedor
,V.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,NitProv,PV.RazonSocial AS Proveedor,FecCompra,FecIngreso,TipoAfil,NumSoat,FecSoat,VigSoat
,KmInicial,KmActual,Km2Actual AS KmRuta,CentFinal AS KmOtro,VH.CantHoras,V.Descripcion AS VehDescripcion,V.Observacion AS Observ,CdCenSer,CentroServ
,Ubicacion,CdLocal,LU.Localidad,V.IdEstado AS CdEstado,Estado,EV.NColor AS NumColor,OutDemand AS NoDisponible
 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 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 TiposFuel AS TF ON V.IdCom=TF.IdCom
 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 EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
 LEFT JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal
 LEFT JOIN CentrosServ AS CS ON V.CdCenSer =CS.IdCenSer LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero
 LEFT JOIN Terceros AS PV ON V.NitProv=PV.IdTercero 
 LEFT JOIN (SELECT NumUnidad,CantHoras FROM VehHoras WHERE TipoUnidad='V') AS VH ON V.IdVehiculo=VH.NumUnidad
 UNION ALL
  SELECT IdRemque,'0','0','',R.IdMarca,M.Marca,'0','',R.IdColor,NomColor,'0','',R.IdCrceria,TipoCar,Modelo,NULL,ConfEjes
,'0','','','',R.IdPropietario,NP.RazonSocial,R.IdPropietario,NP.RazonSocial,'0','',NitProv,PV.RazonSocial,FecCompra,FecAdd,'',Hidrostatica,FecPruebaHid,VigPruebaHid
,0,Kilmetraje,kmRuta,kmOtro,VH.CantHoras,R.Descripcion,R.Observacion,CdCenSer,CentroServ,R.Ubicacion,R.CdLocal,LU.Localidad,R.IdEstado,Estado,EV.NColor,EV.OutDemand
FROM VehRemolq AS R INNER JOIN Marcas AS M ON R.IdMarca=M.IdMarca  INNER JOIN TiposCol AS CL ON R.IdColor=CL.IdColor
 INNER JOIN TiposCar AS TC ON R.IdCrceria=TC.IdCrceria  INNER JOIN Terceros AS NP ON R.IdPropietario=NP.IdTercero
 INNER JOIN EstadoVeh AS EV ON R.IdEstado=EV.IdEstado LEFT JOIN Terceros AS PV ON R.NitProv=PV.IdTercero
 LEFT JOIN CentrosServ AS CS ON R.CdCenSer=CS.IdCenSer LEFT JOIN Localidades AS LU ON R.CdLocal=LU.IdLocal
 LEFT JOIN (SELECT NumUnidad,CantHoras FROM VehHoras WHERE TipoUnidad='R') AS VH ON R.IdRemque=VH.NumUnidad