INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCOSM','TRAINF','MUD',0,'Ordenes de Servicio','FRMLCOSM','SSSSSSSSSSSSSSSSNS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCCAB','TRAINF','MUD',1,'Contratos de Bodegas','FRMLCCAB','SSSSSSSSSSSSSSSSNS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCOSR','TRAINF','MUD',2,'Requisiciones de Materiales','FRMLCOSR','SSSSSSSSSSSSSSSSNS',0,'')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudContFactRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudContFactRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudContNovRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudContNovRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudContratosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudContratosRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenOperRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenOperRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServReq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServReq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenVehRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenVehRel]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudContFactRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT C.NumContrato,C.IdCia AS CdCia,C.Fecha,C.FechaInicio,C.FechaFinal,C.IdCliente,C.TipOrden,C.NumOrden,C.IdCiaOrden
,FC.TipFac,FC.NumFactura,FC.IdCiaFac,FC.Fecha AS FecFact,FC.ValorTotal,FC.Anulado
,D.Item AS FacItem,D.CdConcepto,D.Descripcion,CD.Concepto,D.Cantidad,D.VrUnitario,D.VrCosto,D.PesoNeto,D.Volumen,D.TarifaIva,D.VrImpuesto,D.TarifaSeg,D.VrSeguroRem
,D.Remision,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.TipDocRef,D.NumDocRef,D.IdCiaRef,F.VrNeto,F.Observacion AS Observ,F.Anulado AS FacAnulado
 FROM Trn_MudContratos AS C INNER JOIN Trn_MudContFact AS FC ON C.TipDoc=FC.TipDoc AND C.NumContrato=FC.NumContrato AND C.IdCia=FC.IdCia
 INNER JOIN Trn_TraFacRemesas AS D ON FC.TipFac=D.TipDoc AND FC.NumFactura=D.Factura AND FC.IdCiaFac=D.IdCia
 INNER JOIN Trn_Facturas AS F ON FC.TipFac=F.TipDoc AND FC.NumFactura=F.Factura AND FC.IdCiaFac=F.IdCia
 LEFT JOIN ConcDiversos AS CD ON D.CdConcepto=CD.IdConcepto
 WHERE C.TipDoc=@pmTipDoc AND C.FechaInicio BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenVehRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,O.NumOrden,O.IdCia,O.FecDespacho,O.IdCliente,VH.Item,VH.IdVehiculo,VH.TipoAfiVehic,VH.NitEmpresa,NomEmpresa,VH.nRemolque
,VH.IdConductor,NC.RazonSocial AS Conductor,IdCondAux,NA.RazonSocial AS CondAuxiliar,VH.TipDocRef AS TipRem,VH.NumDocRef AS NumRemesa,VH.CdCiaRef AS CdCiaRem
,V.NumVeh,V.IdMarca AS CdMarca,MV.Marca,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.Modelo,V.IdColor AS CdColor,NomColor,V.Config
,V.IdPoseedor AS CdPoseedor,T.RazonSocial AS Poseedor,V.NumSoat,VigSoat,V.NitEmpSoat,EST.RazonSocial AS NomEmpSoat,V.SerieChasis,V.CarrCapac,V.UndCapc
,CDT.Licencia AS NumLicencia,CDT.CatLicencia,CDT.VigLicencia
,CDA.Licencia AS AuxNumLicencia,CDA.CatLicencia AS AuxCategLic,CDA.VigLicencia AS AuxVigLicencia
--datos de remesas
,ISNULL(RM.RemVrTotal,0) AS RemVrTotal,ISNULL(RM.RemVrPago,0) AS RemVrPago,ISNULL(RM.MucNumero,0) AS MucNumero,ISNULL(RM.MucVrPago,0) AS MucVrPago
,ISNULL(OP.OdpNumero,0) AS NumOrdPago,ISNULL(OP.OdpVrPago,0) AS OdpVrPago,ISNULL(OP.OdpTarifa,0) AS OdpTarifa,ISNULL(OP.OdpVrTotal,0) AS OdpTotal
 FROM Trn_MudOrdenServ AS O INNER JOIN Trn_MudOrdenVeh AS VH ON O.TipDoc=VH.TipDoc AND O.NumOrden=VH.NumOrden AND O.IdCia=VH.IdCia
 INNER JOIN Vehiculos AS V ON VH.IdVehiculo=V.IdVehiculo
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN TiposCol AS C ON V.IdColor=C.IdColor
 INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Terceros AS T ON V.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NC ON VH.IdConductor=NC.IdTercero 
 LEFT JOIN Terceros AS NA ON VH.IdCondAux=NA.IdTercero LEFT JOIN Terceros AS EST ON V.NitEmpSoat=EST.IdTercero
 LEFT JOIN TercCndtores AS CDT ON VH.IdConductor=CDT.IdConductor LEFT JOIN TercCndtores AS CDA ON VH.IdCondAux=CDA.IdConductor
 LEFT JOIN (SELECT D.TipDoc AS RemTipo,D.NumOrden AS RemNumero,D.IdCia AS RemIdCia
 ,SUM(CASE D.UndTarifa WHEN 'PESO' THEN D.PesoNeto*D.TarifClie WHEN 'UNIDADES' THEN D.Cantidad*D.TarifClie WHEN 'VOLUMEN' THEN D.Volumen*D.TarifClie ELSE D.PesoNeto*D.TarifClie END) AS RemVrTotal
 ,SUM(CASE D.UndTarifPago WHEN 'PESO' THEN D.PesoNeto*D.TarifPago WHEN 'UNIDADES' THEN D.Cantidad*D.TarifPago WHEN 'VOLUMEN' THEN D.Volumen*D.TarifPago ELSE D.PesoNeto*D.TarifPago END) AS RemVrPago
 ,SUM(CASE D.UndTarifPago WHEN 'PESO' THEN D.PesoNeto*MR.TarifPago WHEN 'UNIDADES' THEN D.Cantidad*MR.TarifPago WHEN 'VOLUMEN' THEN D.Volumen*MR.TarifPago ELSE D.PesoNeto*MR.TarifPago END) AS MucVrPago
 ,MAX(MR.Manifiesto) AS MucNumero
  FROM Trn_TraRemMcias AS D INNER JOIN Trn_TraRemesa AS R ON D.TipDoc=R.TipDoc AND D.NumOrden=R.NumOrden AND D.IdCia=R.IdCia
  LEFT JOIN Trn_TraManifRem AS MR ON D.TipDoc=MR.TipRem AND D.NumOrden=MR.Remesa AND D.IdCia=MR.IdCiaRem AND D.Item=MR.ItemRem
  LEFT JOIN Trn_TraManifiesto AS M ON MR.TipDoc=M.TipDoc AND MR.Manifiesto=M.Manifiesto AND MR.IdCia=M.IdCia
  WHERE D.TipDoc='RMT' AND R.Anulado=0 AND ISNULL(M.Anulado,0)=0 GROUP BY D.TipDoc,D.NumOrden,D.IdCia) AS RM ON VH.TipDocRef=RM.RemTipo AND VH.NumDocRef=RM.RemNumero AND VH.CdCiaRef=RM.RemIdCia
 LEFT JOIN (SELECT OD.TipRem,OD.Remesa,OD.IdCiaRem,MAX(OD.OrdPago) AS OdpNumero,MAX(OD.Manifiesto) AS OdpNumManif
 ,SUM(CASE OD.UndTarifa WHEN 'PESO' THEN OD.PesoNeto*OD.TarifPago WHEN 'UNIDADES' THEN OD.Cantidad*OD.TarifPago WHEN 'VOLUMEN' THEN OD.Volumen*OD.TarifPago ELSE OD.PesoNeto*OD.TarifPago END) AS OdpVrPago
 ,MAX(OM.TarifaFlete) AS OdpTarifa,SUM(OM.VrTotalFletes-OM.VrDescuento) AS OdpVrTotal
 FROM Trn_TraOrdenRemesas AS OD INNER JOIN Trn_TraOrdenManif AS OM ON OD.TipDoc=OM.TipDoc AND OD.OrdPago=OM.OrdPago AND OD.IdCia=OM.IdCia
 INNER JOIN Trn_TraOrdenPago AS O ON OD.TipDoc=O.TipDoc AND OD.OrdPago=O.OrdPago AND OD.IdCia=O.IdCia
 WHERE OD.TipRem='RMT' AND O.Anulado=0 GROUP BY OD.TipRem,OD.Remesa,OD.IdCiaRem) AS OP ON VH.TipDocRef=OP.TipRem AND VH.NumDocRef=OP.Remesa AND VH.CdCiaRef=OP.IdCiaRem
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudContNovRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
SELECT C.NumContrato,C.IdCia AS CdCia,C.Fecha,C.FechaInicio,C.FechaFinal,C.IdCliente,C.TipOrden,C.NumOrden,C.IdCiaOrden
,D.Item,D.Fecha,D.TipoNov,D.Descripcion,D.TarifaServ,D.TipInv,D.NumInvent,D.CdCiaInv,D.ItemInv
,D.CdArticulo,Articulo,A.IdSeccion AS CdSeccion,Seccion,D.Cantidad,D.CdEstado,Estado,D.CdBodega,B.Bodega AS BodegaOrig
,D.CdBodDest,BD.Bodega AS BodegaDest,D.NumDocRef,D.IdUsuario,Usuario,D.Notas
FROM Trn_MudContratos AS C INNER JOIN Trn_MudContNov AS D ON C.TipDoc=D.TipDoc AND C.NumContrato=D.NumContrato AND C.IdCia=D.IdCia
INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
LEFT JOIN AlmBodegas AS B ON D.CdBodega=B.IdBodega LEFT JOIN AlmBodegas AS BD ON D.CdBodDest=BD.IdBodega
LEFT JOIN MudEstados AS E ON D.CdEstado=E.IdEstado LEFT JOIN MudArticulos AS A ON D.CdArticulo=A.IdArticulo
LEFT JOIN MudSecciones AS SC ON A.IdSeccion=SC.IdSeccion
WHERE C.TipDoc=@pmTipDoc AND C.FechaInicio BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudContratosRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT C.NumContrato,C.IdCia AS CdCia,Compania,C.Fecha,C.FechaInicio,C.FechaFinal,C.IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia
,C.IdBodega,B.Bodega,CdBodega2,BA.Bodega AS BodegaAdic,C.VrServicio,C.VrImpuesto,C.VrDeclarado,C.VrSeguro,C.TipOrden,C.NumOrden,C.IdCiaOrden
,O.FecDespacho AS OrdFecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen
,O.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,O.IdVend,VN.RazonSocial AS NomVendedor,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipoRuta,O.EstadoBod
--detalle
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TarifSeguro,D.TipoRubro,D.CdCuenta,D.CodTarIva,D.CodTarSeg
,C.Anulado,C.FecDev,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,C.FechaRetiro,C.TimeSys AS FechaCrea,C.FecUpdate,C.IdCiaCrea,C.OrigenAdd,C.IdUsuario AS CdUsuario,Usuario
--datos del cliente
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupo,GrupoClie,CLI.IdPlazo AS CdPlazo,Plazo
,ISNULL(FC.FacNumero,0) AS FacNumero,ISNULL(FC.FacTotal,0) AS FacVrTotal
 FROM Trn_MudContratos AS C INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN TercCliente AS CLI ON C.IdCliente=CLI.IdClie
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Trn_MudContConc AS D ON C.TipDoc=D.TipDoc AND C.NumContrato=D.NumContrato AND C.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto INNER JOIN AlmBodegas AS B ON C.IdBodega=B.IdBodega
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN AlmBodegas AS BA ON C.CdBodega2=BA.IdBodega LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia 
 LEFT JOIN Trn_MudOrdenServ AS O ON C.TipOrden=O.TipDoc AND C.NumOrden=O.NumOrden AND C.IdCiaOrden=O.IdCia
 LEFT JOIN Terceros AS VN ON O.IdVend=VN.IdTercero LEFT JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep LEFT JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN (SELECT TipDoc,NumContrato,IdCia,COUNT(Item) AS FacItems,MAX(NumFactura) AS FacNumero,SUM(ValorTotal) AS FacTotal FROM Trn_MudContFact
 WHERE Anulado=0 GROUP BY TipDoc,NumContrato,IdCia) AS FC ON C.TipDoc=FC.TipDoc AND C.NumContrato=FC.NumContrato AND C.IdCia=FC.IdCia
 WHERE C.TipDoc=@pmTipDoc AND C.FechaInicio BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro
,O.IdVend,VN.RazonSocial AS NomVendedor,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipCausac,O.Causacion,O.CdCiaCau,O.EstCumplido,O.FechaCump,O.TipoRuta,O.Volumen,EstadoBod,O.CdBodega,B.Bodega AS NomBodega,FechaIngBod,FechaRetBod,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
--Detalles de orden
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,D.NumDocFac,D.IndFactItem,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
--datos de facturas
,FC.FacNumero,FC.FacFecha,ISNULL(FC.FacValor,0) AS FacVrTotal,ISNULL(FC.FacCosto,0) AS FacVrCosto,ISNULL(FC.FacImpuesto,0) AS FacVrIva,ISNULL(FC.FacSeguro,0) AS FacVrSeguro
--datos del tercero
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupo,GrupoClie,CLI.IdPlazo AS CdPlazo,Plazo
,V.NumVeh,V.TipoAfil,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,V.Modelo,V.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,V.IdConductor AS CedConductor,CDT.RazonSocial AS Conductor,V.NumSoat,V.VigSoat
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN AlmBodegas AS B ON O.CdBodega=B.IdBodega
 LEFT JOIN Vehiculos AS V ON D.PlacaVehic=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 CLR ON V.IdColor=CLR.IdColor
 LEFT JOIN Terceros AS NP ON V.IdPoseedor=NP.IdTercero LEFT JOIN Terceros AS CDT ON V.IdConductor=CDT.IdTercero
 --Facturas item por item
 LEFT JOIN (SELECT TipRem AS FacTipOrd,Remesa AS FacNumOrden,IdCiaRem AS FacIdCiaOrd,ItemRem AS FacItemOrd,MAX(FecRemesa) AS FacFecha
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValor
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCosto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrImpuesto*-1 ELSE VrImpuesto END) AS FacImpuesto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrSeguroRem*-1 ELSE VrSeguroRem END) AS FacSeguro
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN VrDeclMcia ELSE 0 END) AS FacDeclarado
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Factura ELSE 0 END) AS FacNumero
 FROM Trn_TraFacRemesas WHERE TipRem='OSM' GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS FC ON D.TipDoc=FC.FacTipOrd AND D.NumOrden=FC.FacNumOrden AND D.IdCia=FC.FacIdCiaOrd AND D.Item=FC.FacItemOrd
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenOperRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
SELECT O.TipDoc,O.NumOrden,O.IdCia,O.FecDespacho,O.IdCliente,D.Item,D.IdOperario,NomOperario,D.IdTipoOper,TipoOperario
,D.CdConcepto,C.Concepto,C.IdCuenta,C.TipoConc,D.TarifaCargo,D.PlacaVehic
,V.NumVeh,V.IdMarca AS CdMarca,M.Marca,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.Modelo,V.IdColor AS CdColor,NomColor,V.Config
,V.IdPoseedor AS CdPoseedor,T.RazonSocial AS Poseedor,V.TipoAfil
FROM Trn_MudOrdenServ AS O INNER JOIN Trn_MudOrdenOper AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
INNER JOIN TiposOperarios AS TP ON D.IdTipoOper=TP.IdOper LEFT JOIN TraConcCausac AS C ON D.CdConcepto=C.IdConcepto
LEFT JOIN Vehiculos AS V ON D.PlacaVehic=V.IdVehiculo
LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor
LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN Terceros AS T ON V.IdPoseedor=T.IdTercero
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServReq] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro
,O.IdVend,VN.RazonSocial AS NomVendedor,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipCausac,O.Causacion,O.CdCiaCau,O.EstCumplido,O.FechaCump,O.TipoRuta,O.Volumen,EstadoBod,O.CdBodega,B.Bodega AS NomBodega,FechaIngBod,FechaRetBod,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,ED.Estado
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
--datos de la requisc
,RQ.TipReq,RQ.Requisicion,RQ.CdCiaReq,RQ.FechaReq,RQ.IdRespons,NR.RazonSocial AS Responsable,RQ.CdDep,Dependencia,RQ.NumAprob,RQ.FecAprob,RQ.TipoVigencia,RQ.Observacion AS ReqObservacion,RQ.IdEstado AS ReqIdEstado,ER.Estado AS ReqEstado
,RQ.Item,RQ.CdProducto,DescripProd,RQ.Descripcion,RQ.IdSubgrupo,Subgrupo,RQ.CdBodega AS ReqCdBodega,BG.Bodega AS ReqBodega,RQ.Cantidad,RQ.IdUnd AS CdUnd,Unidad,RQ.VrUnitario,RQ.TipOrd AS TipOdc,RQ.OrdenComp,RQ.IdCiaOc
,RQ.TipSal,RQ.NSalida,RQ.IdCiaSal,RQ.FechaSal,RQ.CantSalida,RQ.CantDevSal,RQ.Referencia AS ReqReferencia
--datos del tercero
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupo,GrupoClie,CLI.IdPlazo AS CdPlazo,Plazo
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia LEFT JOIN AlmBodegas AS B ON O.CdBodega=B.IdBodega
 --requisiciones
 LEFT JOIN (SELECT R.NContrato AS NumOrden,R.IdCiaCont AS CdCiaOrden,R.TipDoc AS TipReq,R.Requisicion,R.IdCia AS CdCiaReq,R.Fecha AS FechaReq
,R.IdRespons,R.IdDep AS CdDep,R.NumAprob,R.FecAprob,R.NivelAprob,R.TipoVigencia,R.Num_Vehic,R.Observacion,R.IdEstado
,D.Item,D.CdProducto,D.Descripcion,D.IdSubgrupo,D.CdBodega,D.Cantidad,D.IdUnd,D.VrUnitario,D.TipOrd,D.NumOrden AS OrdenComp,D.IdCiaOrd AS IdCiaOc
,D.TipSal,D.NSalida,D.IdCiaSal,D.FechaSal,D.CantSalida,D.CantDevSal,D.Referencia
FROM Trn_Requisicion AS R INNER JOIN Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
WHERE R.TipDoc='REQ' AND R.Modalidad='MUDANZAS' AND R.Anulado=0) AS RQ ON O.NumOrden=RQ.NumOrden AND O.IdCia=RQ.CdCiaOrden
 LEFT JOIN Terceros AS NR ON RQ.IdRespons=NR.IdTercero LEFT JOIN Dependencias AS DP ON RQ.CdDep=DP.IdDep
 LEFT JOIN EstadoDoc AS ER ON RQ.IdEstado=ER.IdEstado LEFT JOIN ProdMcias AS P ON RQ.CdProducto=P.IdProducto
 LEFT JOIN UndMed AS UM ON RQ.IdUnd=UM.IdUnd LEFT JOIN SubGrupos AS SG ON RQ.IdSubgrupo=SG.IdSubgrupo
 LEFT JOIN Bodegas AS BG ON RQ.CdBodega=BG.IdBodega
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO