INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('OSN','01','Ordenes-Novedades','CrOsaLNov.rpt',1,1,1,0,1,'paQryTraOrdenServNov','','Novedades de Ordenes')
GO
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('DIA','COMPROBAR FECHA DEL SISTEMA EN INICIO DE SESION','BOOLEAN','0',5,'MAIN')
GO
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDCUM','VTA','Permitir Ver Tarifas de Cobro')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDPDT','VTA','Permitir Ver Tarifas de Cobro')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDPDC','VTA','Permitir Ver Tarifas de Cobro')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServNov] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=NULL
 AS 
 SELECT O.TipDoc AS IdTipDoc,O.NumOrden AS Num_Orden,O.IdCia AS CdCia,Compania,O.Fecha,FecDespacho,FecRecibo,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,A.Agencia
,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,O.TipoAfiVehic,O.Modalidad,O.VrTotal,O.VrCosto,O.Cantidad AS Cant_Total,VrAnticipo,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino
,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,NumCump,CdCiaCum,EstFactura,TipFact,NumFactura,CdCiaFact
,kmtInicial,kmtFinal,Cantkms,EstOrden,TipLiq,NumLiquida,CdCiaLiq,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS FechaCrea
--novedades
,Item,C.Descripcion AS Descrpcion,Tarifa,TipoConc,RubroConcep,C.Cantidad AS CantConc,VrUnitario,TarifIva,C.IdConcepto AS CdConcepto,Concepto,CdCuenta,NomCuenta
,NitTercero,TCN.RazonSocial AS NomTercero,FechaNov,VrBase,TipoTarif,RefConc,Referencia2,Referencia3,Fijos,IncBaseRet
--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.TelMovil AS TelCelular,T.e_mail AS TercEmail
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.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.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.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 EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado 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 M ON V.IdMarca=M.IdMarca 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraConceptos AS C ON O.TipDoc=C.TipDoc AND O.NumOrden=C.Documento AND O.IdCia=C.IdCia
 LEFT JOIN ConcDiversos AS CDV ON C.IdConcepto=CDV.IdConcepto LEFT JOIN Terceros AS TCN ON C.NitTercero=TCN.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 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 A ON O.IdAgencia=A.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
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
