INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('ROP','01','Predeterminado','CrLrmOdp.rpt',1,2,1,0,1,'paQryTraRemesaOdp','','Remesas/Ordenes de pago')
GO
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLRMODP','TRAINF','TRA',27,'Rentabilidad de Remesas','FRMLRMODP','SSSSSSSSSSSSS',0,'')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoControlesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoControlesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaOdp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaOdp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoLta]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaOdp] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
AS 
SELECT R.TipDoc,R.NumOrden,R.IdCia AS CdCia,Compania,R.Fecha AS FecRemesa,D.Item,D.IdMercancia AS CdMercancia,D.DescripMcias,D.Cantidad,D.PesoNeto,D.UndMed,UMP.Unidad AS UmdPeso,D.Volumen,R.IdCliente AS NitCliente,T.RazonSocial AS NomCliente
,D.NitRemite,D.Remitente,D.DirOrigen,D.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,D.NitDestntario,D.Destinatario,D.DirDestino,D.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,D.TarifClie,D.TarifPago,D.VrDeclarado,D.VrSeguro,D.TarifSeguro,D.UndTarifa,D.UndTarifPago,Remision,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida
,R.IdAgencia AS CdAgencia,A.Agencia,R.IdVehiculo,R.IdConductor,CDT.RazonSocial AS NomConductor,R.nRemolque,R.TipoAfiVehic,R.Modalidad,NumPedido,IdCiaPed,R.TipDcm,R.NumDocmto,R.IdCiaDcm,R.EstCumplido,D.Cumplido AS NumCumplido,D.IdCiaCump,R.EstFactura
--datos de factura
,RF.TipoFact,RF.NumFactura,RF.IdCiaFact,F.Fecha AS FacFecha,RF.FacCantidad,RF.FacValorTotal,F.IdCliente AS FacIdCliente,NCL.RazonSocial AS FacNomCliente,F.IdAgencia AS FacIdAgencia,AF.Agencia AS FacAgencia,F.IdVend AS FacIdVend,VN.RazonSocial AS Vendedor
--Manifiesto y ordenes
,MC.TipDoc AS MucTipo,MC.Manifiesto,MC.IdCia AS MucIdCia,MC.Fecha AS MucFecha,MC.IdRuta,Ruta,MC.IdPoseedor,NP.RazonSocial AS NomPoseedor,MC.Cumplido AS MucCumplido,MC.IdCiaCump AS MucIdCiaCump,MC.EstCumplido AS MucEstCumplido,MC.EstOrden AS MucEstOrden
,OP.OdpTipo,OP.OrdPago,OP.OdpIdCia,OP.OdpVrFlete,OP.OdpDescuento,OP.OdpRetencion,OP.OdpReteIca,OP.OdpVrCREE,OP.OdpAnticipos,OP.OdpFaltantes,OP.OdpSeguros,OP.OdpFondos,OP.OdpAportes,OP.OdpOtrosDcto,OP.OdpImpuestos
,OP.OdpEstampillas,OP.OdpOtrosPagos,OP.OdpVrNeto,OP.OdpTarifaUnd,OP.OdpPesoTotal,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,EG.TotEgresos
FROM Trn_TraRemesa AS R INNER JOIN Trn_TraRemMcias AS D ON R.TipDoc=D.TipDoc AND R.NumOrden=D.NumOrden AND R.IdCia=D.IdCia
INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero
INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero 
INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd  
LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
--Remeasa/Facturas
LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE Factura END) AS NumFactura
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFact
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN '0' ELSE TipDoc END) AS TipoFact
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN '00' ELSE IdCia END) AS IdCiaFact
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*-1 ELSE Cantidad END) AS FacCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades*-1 ELSE Unidades END) AS FacUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto*-1 ELSE PesoNeto END) AS FacPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen*-1 ELSE Volumen END) AS FacVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante*-1 ELSE VrFaltante END) AS FacFaltantes
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE TarifaIva END) AS FacTarifIva
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE VrImpuesto END) AS FacImpuesto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE TarifaRet END) AS FacTarifRet
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE VrRetencion END) AS FacRetencion
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE TarifaIca END) AS FacTarifIca
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE VrReteIca END) AS FacReteIca
FROM Trn_TraFacRemesas WHERE TipoReg=0 GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON D.TipDoc=RF.TipRem AND D.NumOrden=RF.Remesa AND D.IdCia=RF.IdCiaRem AND D.Item=RF.ItemRem
LEFT JOIN (SELECT TipDoc,Factura,IdCia,Fecha,IdCliente,IdAgencia,IdVend,IdPlazo,BaseImp,BaseRet FROM Trn_Facturas WHERE Modalidad='TRANSPORTE' AND Anulado=0) AS F ON RF.TipoFact=F.TipDoc
AND RF.NumFactura=F.Factura AND RF.IdCiaFact=F.IdCia
--remesa/manifiesto
LEFT JOIN (SELECT MR.TipRem,MR.Remesa,MR.IdCiaRem,MR.ItemRem,MR.TipDoc,MR.Manifiesto,MR.IdCia,MR.TarifPago,MR.RemMintrans,M.Fecha,M.IdRuta,M.IdVehiculo,M.nRemolque,M.TipoAfiVehic,M.IdPoseedor,M.IdConductor
,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrAnticipo,M.VrAntAdic,M.VrCargos,M.VrDctos,M.VrFleteNeto,M.PesoTotal,M.TarifaRet,M.TarifaIca,M.Cumplido,M.IdCiaCump,M.FechaCump,M.EstCumplido,M.EstOrden
FROM Trn_TraManifRem AS MR INNER JOIN Trn_TraManifiesto AS M ON MR.TipDoc=M.TipDoc AND MR.Manifiesto=M.Manifiesto AND MR.IdCia=M.IdCia WHERE M.Anulado=0) AS MC ON D.TipDoc=MC.TipRem AND D.NumOrden=MC.Remesa AND D.IdCia=MC.IdCiaRem AND D.Item=MC.ItemRem
--Remesa/Manif/ordnenes
LEFT JOIN (SELECT OM.TipMuc,OM.Manifiesto,OM.IdCiaMuc,OM.TipDoc AS OdpTipo,OM.OrdPago,OM.IdCia AS OdpIdCia,OM.VrTotalFletes AS OdpVrFlete,OM.VrDescuento AS OdpDescuento,OM.VrRetencion AS OdpRetencion,OM.VrReteIca AS OdpReteIca
,OM.VrReteCREE AS OdpVrCREE,OM.VrAnticipos AS OdpAnticipos,OM.VrFaltantes AS OdpFaltantes,OM.VrSeguros AS OdpSeguros,OM.VrFondos AS OdpFondos,OM.VrAportes AS OdpAportes,OM.VrOtrosDctos AS OdpOtrosDcto,OM.VrImpuestos AS OdpImpuestos,OM.VrEstampilla AS OdpEstampillas
,OM.VrOtrosPagos AS OdpOtrosPagos,OM.VrNeto AS OdpVrNeto,OM.TarifaFlete AS OdpTarifaUnd,OM.PesoTotal AS OdpPesoTotal,OM.Unidades AS OdpCantidad,OM.Volumen AS OdpVolumen,OM.TipEgr,OM.Egreso,OM.IdCiaEgr
FROM Trn_TraOrdenManif AS OM INNER JOIN Trn_TraOrdenPago AS O ON OM.TipDoc=O.TipDoc AND OM.OrdPago=O.OrdPago AND OM.IdCia=O.IdCia WHERE O.Anulado=0) AS OP ON MC.TipDoc=OP.TipMuc AND MC.Manifiesto=OP.Manifiesto AND MC.IdCia=OP.IdCiaMuc
--Egresos
LEFT JOIN (SELECT EO.TipOdp,EO.OrdPago,EO.IdCiaOdp,SUM(VrAbonado) AS TotEgresos FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EG ON OP.OdpTipo=EG.TipOdp AND OP.OrdPago=EG.OrdPago AND OP.OdpIdCia=EG.IdCiaOdp
LEFT JOIN Terceros AS NCL ON F.IdCliente=NCL.IdTercero LEFT JOIN Agencias AS AF ON F.IdAgencia=AF.IdAgencia
LEFT JOIN Terceros AS VN ON F.IdVend=VN.IdTercero LEFT JOIN Terceros AS NP ON MC.IdPoseedor=NP.IdTercero
LEFT JOIN Rutas AS RT ON MC.IdRuta=RT.IdRuta
WHERE R.TipDoc='RMT' AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (@pmIdCia IS NULL OR R.IdCia=@pmIdCia)
AND (@pmIdCliente IS NULL OR R.IdCliente=@pmIdCliente) AND R.Anulado=0
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT CU.TipDoc AS TipCum,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,TipoComp,NumComp,NumRadicaMT,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario
,M.Fecha AS FecManif,FecDespacho,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS MucObserv
,MA.TipoRuta,MA.kmsTotal,MA.NomRemite,MA.NomDestino,MA.LugarFletes,MA.NumAnticipo AS NumAnticipo,MA.NumCheque AS Num_Cheque,MA.TipoMintrans,MA.WsSeguro,MA.NumRadSeguro
--Datos del vehiculo
,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
,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,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupoPro,GrupoProp
,M.Remesa,M.IdCiaRem,RMT.Fecha AS Fecremesa,RMT.Comprobante AS Cmpremesa,RMT.TipCom AS TipComRmt
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON CU.TipMuc=MA.TipDoc AND CU.Manifiesto=MA.Manifiesto AND CU.IdCiaMuc=MA.IdCia
 INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON CU.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 Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN (SELECT NumOrden,IdCia,Fecha,TipCom,Comprobante,IdCiaCom FROM Trn_TraRemesa WHERE TipDoc='RMT') AS RMT ON M.Remesa=RMT.NumOrden AND M.IdCiaRem=RMT.IdCia 
 WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND  CU.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
  ORDER BY CU.IdCia,CU.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT CU.TipDoc AS TipCum,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,TipoComp,NumComp,NumRadicaMT,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario
,M.Fecha AS FecManif,FecDespacho,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS MucObserv
,MA.TipoRuta,MA.kmsTotal,MA.NomRemite,MA.NomDestino,MA.LugarFletes,MA.NumAnticipo AS NumAnticipo,MA.NumCheque AS Num_Cheque,MA.TipoMintrans,MA.WsSeguro,MA.NumRadSeguro
--detalle de cumplidos
,D.Item AS DetItem,TipRem,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.Cantidad AS Cant,D.PesoNeto AS PesoCump,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolCump,D.UndVol AS Und_Vol
,D.Cases AS CasesCump,D.Cajas AS CajasCump,D.Palets AS PaletsCump,D.TarifClie AS Tarif_Clie,D.TarifPago AS Tarif_Pago,TarifFlete,UndTarifClie,D.UndTarifPago AS UndTarifPag,CantCargue,PesoCargue,VolCargue,CasesCargue,CajasCargue,PaletsCargue
,EstadoCump,D.Remision AS NumRemision,D.DocCliente AS CumDocClie,D.Referencia1 AS CumRef1,D.Referencia2 AS CumRef2,D.Referencia3 AS CumRef3,D.Detalle AS CumDetalle
,IdMercancia,DescripMcias,RM.Cantidad AS RemCant,RM.PesoNeto AS RemPeso,NitRemite,Remitente,NitDestntario,Destinatario
--Datos del vehiculo
,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
,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,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupoPro,GrupoProp
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON CU.TipMuc=MA.TipDoc AND CU.Manifiesto=MA.Manifiesto AND CU.IdCiaMuc=MA.IdCia
 INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON CU.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
 INNER JOIN Trn_TraCumRemesas AS D ON CU.TipDoc=D.TipDoc  AND CU.Cumplido=D.Cumplido AND CU.IdCia=D.IdCia
 LEFT JOIN Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN Trn_TraRemMcias AS RM ON D.TipRem=RM.TipDoc  AND D.Remesa=RM.NumOrden AND D.IdCiaRem=RM.IdCia  AND D.ItemRem=RM.Item
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND  CU.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
  ORDER BY CU.IdCia,CU.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT C.Cumplido AS NumCumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FecCumplido,C.Manifiesto AS NumManif,IdCiaMuc,M.Fecha AS FecManif,C.IdVehiculo AS PlacaVeh,Modalidad
,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic
,C.Anulado AS CumAnulado,C.FecDev AS FechAnulado,TipoComp,NumComp,CodConcepto,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,IdLocFletes,LP.Localidad AS LugarPago,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaAct,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MA.TipoRuta
 FROM Trn_TraCumplido AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON C.TipMuc=M.TipDoc AND C.Manifiesto=M.Manifiesto AND C.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON C.TipMuc=MA.TipDoc AND C.Manifiesto=MA.Manifiesto AND C.IdCiaMuc=MA.IdCia
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 LEFT JOIN Localidades AS CO ON C.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON C.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON C.CdRuta=R.IdRuta LEFT JOIN Localidades AS LP ON M.IdLocFletes=LP.IdLocal 
 WHERE C.TipDoc=@pmTipDoc  AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY C.IdCia,C.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoControlesLta] @pmNumUnidad VARCHAR(10)=Null,@pmIdConc VARCHAR(4)=Null
 AS 
 SELECT M.TipoUnidad,M.NumUnidad AS IdVehiculo,M.IdConc AS CodConc,Concepto,C.IdSistema AS CdSistema,Sistema,C.IdMtto AS CdMtto,TipoMtto
,M.TipoControl,M.Intervalo_km,M.Intervalo_dias,M.Intervalo_Horas,M.kmUltMtto,M.kmUltMttoRuta,M.kmUltMttoSat,M.FecUltMtto,HsUltMtto
,C.TipoControl AS ConcControl,C.Intervalo_km AS ConcKm,C.Intervalo_dias AS ConcDias,C.Intervalo_Horas AS ConcHoras,C.Req_Parte,C.CantHoras,C.TarifaCosto
,M.Comentarios,M.Inactivo,M.FechaCrea,M.IdUsuario AS CdUsuario,Usuario
--campos de subconsulta vehiculos
,CdTipoVeh,TipoVehiculo,CdMarca,Marca,CdColor,NomColor,Modelo,Config,CdComb,TipoComb,SerieChasis,IdPropietario,NP.RazonSocial AS Propietario
,VigSoat,VigRCivil,VigRegNal,VigTecMec,KmInicial,VehKmActual,VehKmRuta,VehKmOtro,VH.Descripcion,VehInactivo
,VehEstado,Estado,EV.NColor AS EdoNumColor,EV.OutDemand AS EdoDisponible,HR.CantHoras AS AcumHoras,VH.IdGrupo AS CdGrupo,GrupoProp
FROM MttoControles AS M INNER JOIN MttoConceptos AS C ON M.IdConc=C.IdConc INNER JOIN MttoSistemas AS S ON C.IdSistema=S.IdSistema
INNER JOIN MttoTipos AS TM ON C.IdMtto=TM.IdMtto INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
--subconsulta vehiculos
LEFT JOIN (SELECT IdVehiculo AS VehPlaca,NumVeh,IdTipoVeh AS CdTipoVeh,IdMarca AS CdMarca,IdLinea AS CdLinea,IdColor AS CdColor,Modelo,Config,IdCom AS CdComb
,NumMotor,SerieChasis,CdRemque,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,TipoAfil,FecCompra,FecIngreso,NumSoat,VigSoat,VigRCivil,VigRegNal,VigTecMec
,KmInicial,KmActual AS VehKmActual,Km2Actual AS VehKmRuta,CentFinal AS VehKmOtro,Descripcion,Inactivo AS VehInactivo,IdEstado AS VehEstado,IdGrupo
FROM Vehiculos UNION ALL SELECT IdRemque,'0','0',IdMarca,'0',IdColor,Modelo,ConfEjes,'0','',NumSerial,'',NitProv,IdPropietario,IdPropietario,'0','',FecCompra
,FecAdd,'',Null,Null,Null,VigPruebaHid,0,Kilmetraje,kmRuta,kmOtro,Descripcion,Inactivo,IdEstado,'0' FROM VehRemolq) AS VH ON M.NumUnidad=VH.VehPlaca
LEFT JOIN TiposVeh AS TV ON VH.CdTipoVeh=TV.IdTipoVeh LEFT JOIN Marcas AS MV ON VH.CdMarca=MV.IdMarca
LEFT JOIN TiposCol AS CL ON VH.CdColor=CL.IdColor LEFT JOIN TiposFuel AS TC ON VH.CdComb=TC.IdCom
LEFT JOIN Terceros AS NP ON VH.IdPropietario=NP.IdTercero LEFT JOIN EstadoVeh AS EV ON VH.VehEstado=EV.IdEstado
LEFT JOIN VehHoras AS HR ON M.TipoUnidad=HR.TipoUnidad AND M.NumUnidad=HR.NumUnidad
LEFT JOIN GruposPro AS GP ON VH.IdGrupo=GP.IdGrupo
WHERE M.NumUnidad LIKE ISNULL(@pmNumUnidad,'%') AND M.IdConc LIKE ISNULL(@pmIdConc,'%')
GO