if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraConceptosFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraConceptosFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoOrdenRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoOrdenRelDet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoOrdenRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null
AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,Fecha,FechaOrden,FechaVence,O.IdVehiculo,NumVeh,kmVehiculo,nRemolque,kmRemolque,O.IdTipoOdt AS CdTipoOdt,TipoOrden
,O.IdCenSer AS CdCenSer,CentroServ,NitCenSer,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,IdSupervisor,NSU.RazonSocial AS Supervisor
--<campos de detalles
,D.Item,D.TipoItem,D.IdConc AS CdConc,D.Descripcion,D.Cantidad,D.IdUnd,D.UndMed,D.VrUnitario,D.TipoServ,D.IdOperario AS NitOperario,T.RazonSocial AS Operario,D.IdEstado AS CdEstadoServ,ESV.Estado AS EstadoServ,ESV.NColor AS ES_NumColor
,D.CdMtto,TipoMtto,C.IdSistema AS CdSistema,Sistema,Seccion,Concepto,D.IdProducto,DescripProd,D.FechaServ,D.NumParte,D.TipReq,D.Requisicion,D.IdCiaReq,TipSal,NumSalida,IdCiaSal,CantSalida,D.EstadoReq
,CASE ISNULL(D.TipoReg,0) WHEN 0 THEN 'SERVICIO' ELSE 'REQ' END AS Tipo_Reg
--campos de detalles>
,O.Modalidad,VrServicios,VrRepuestos,VrManoObra,VrOtros,O.IdEstado AS CdEstado,ED.Estado AS EstadoDoc,ED.NColor AS EdNumColor,FechaCierre,O.Anulado,NumDev,FecDev
,O.Observacion AS Observ,O.TimeSys AS FechaCrea,O.FecUpdate AS FecActualiza,O.IdCiaCrea AS CdCIaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario,Leyenda
--campos de 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
,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,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,KmInicial,KmActual,Km2Actual AS kmRuta,CentFinal AS KmOtro,V.Ubicacion
,V.IdEstado AS VehCdEstado,EV.Estado AS VehEstado,EV.NColor AS VehEstColor,EV.OutDemand AS VehNoDisponible,V.Inactivo AS VehInactivo
,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_MttoOrden AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MttoTiposOdt AS TOT ON O.IdTipoOdt=TOT.IdTipoOdt INNER JOIN CentrosServ AS CS ON O.IdCenSer=CS.IdCenSer
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NSU ON O.IdSupervisor=NSU.IdTercero
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
  --<subconsulta detalles
 INNER JOIN (SELECT DS.TipOdt,DS.NumOrden,DS.IdCia,DS.Item,DS.FechaServ,DS.IdConc,DS.Descripcion,DS.Cantidad,DS.VrUnitario,'0' AS IdUnd,DS.UndMed,DS.CdMtto,DS.IdEstado,DS.IdOperario
 ,DS.NumVehic,DS.NumParte,DS.TipoServ,DS.FechaCrea,DS.IdUsuario,'0' AS TipReq,0 AS Requisicion,'00' AS IdCiaReq
 ,CASE DS.TipoServ WHEN 'EXTERNA' THEN 'MO' ELSE 'REPUESTO' END AS TipoItem,'0' AS IdProducto,0 AS EstadoReq,'0' AS TipSal,0 AS NumSalida,'00' AS IdCiaSal,0 AS CantSalida,0 AS TipoReg
 FROM Trn_MttoOrdenDet AS DS INNER JOIN Trn_MttoOrden AS O ON DS.TipOdt=O.TipDoc AND DS.NumOrden=O.NumOrden AND DS.IdCia=O.IdCia
 WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND DS.IdCia LIKE ISNULL(@pmIdCia,'%%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 UNION ALL
 SELECT R.TipOdt,R.NumOrden,R.IdCiaOdt,R.Item,R.FechaCrea,R.CdConcServ,R.Descripcion,R.Cantidad,R.VrUnitario,R.IdUnd,Unidad,C.IdMtto
 ,CASE R.EstadoReq WHEN -1 THEN '9999' WHEN 0 THEN '0001' WHEN 1 THEN '9998' ELSE '0001' END,R.IdOperario
 ,R.NumVehic,R.NumParte,'INTERNA',R.FechaCrea,R.IdUsuario,R.TipReq,R.Requisicion,R.IdCia,R.TipoItem,R.IdProducto,R.EstadoReq
 ,R.TipSal,R.NumSalida,R.IdCiaSal,R.CantSalida,1 AS TipoReg
 FROM Trn_MttoOrdenReq AS R INNER JOIN Trn_MttoOrden AS O ON R.TipOdt=O.TipDoc AND R.NumOrden=O.NumOrden AND R.IdCiaOdt=O.IdCia
 INNER JOIN Trn_Requisicion AS RQ ON R.TipReq=RQ.TipDoc AND R.Requisicion=RQ.Requisicion AND R.IdCia=RQ.IdCia 
 LEFT JOIN MttoConceptos AS C ON R.CdConcServ=C.IdConc LEFT JOIN UndMed AS UM ON R.IdUnd=UM.IdUnd
 WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCiaOdt LIKE ISNULL(@pmIdCia,'%%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') ) AS D ON O.TipDoc=D.TipOdt AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 --subconsulta detalles>
 INNER JOIN Terceros AS T ON D.IdOperario=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
 LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero
 LEFT 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 EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
 LEFT JOIN MttoConceptos AS C ON D.IdConc=C.IdConc LEFT JOIN MttoSistemas AS S ON C.IdSistema=S.IdSistema
 LEFT JOIN MttoTipos AS TM ON D.CdMtto=TM.IdMtto LEFT JOIN MttoEstados AS ESV ON D.IdEstado=ESV.IdEstado
 LEFT JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraConceptosFac] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT RF.Factura,RF.IdCia AS CdCiaFac,C.TipDoc AS TipRem,C.Documento AS NumRemesa,C.IdCia AS CdCia,C.Item,C.Descripcion,Tarifa,TipoConc
,RubroConcep,C.Cantidad,C.VrUnitario,C.TarifIva,C.IdConcepto AS CdConcepto,Concepto,C.CdCuenta,NomCuenta
,C.NitTercero,RazonSocial,CdTipoEsc,TipoEscolta,FechaNov,VrBase,TipoTarif,RefConc
 FROM Trn_TraFacRemesas AS RF INNER JOIN Trn_TraConceptos AS C ON RF.TipRem=C.TipDoc AND RF.Remesa=C.Documento AND RF.IdCiaRem=C.IdCia
 INNER JOIN ConcDiversos AS CD ON C.IdConcepto=CD.IdConcepto
 LEFT JOIN TiposEsc AS TE ON C.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS T ON C.NitTercero=T.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 WHERE RF.TipDoc=@pmTipDoc AND RF.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND RF.IdCia=@pmIdCia
GO