INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMAPITAU','NOMPRO','GEN',27,'Archivo Plano-Banco Itaú','FRMAPITAU','S',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCOSV','TRAINF','MUD',3,'Ordenes de Servicio/Ingresos','FRMLCOSV','SSSSSSSSSSSSSSSSNS',0,'')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServVeh] @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.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv
,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 vehículos
,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
,VH.CdTipoOper,TipoOperacion,VH.TipoIngreso,VH.TarifaIng,VH.VrIngreso,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,NP.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
--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
,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
 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 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 
 INNER JOIN Vehiculos AS V ON VH.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS NC ON VH.IdConductor=NC.IdTercero  
 INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
 INNER JOIN TiposCol AS CLR ON V.IdColor=CLR.IdColor INNER JOIN Terceros AS NP ON V.IdPoseedor=NP.IdTercero
 LEFT JOIN TercCndtores AS CDT ON VH.IdConductor=CDT.IdConductor
 LEFT JOIN Terceros AS NA ON VH.IdCondAux=NA.IdTercero LEFT JOIN TercCndtores AS CDA ON VH.IdCondAux=CDA.IdConductor
 LEFT JOIN Terceros AS EST ON V.NitEmpSoat=EST.IdTercero LEFT JOIN TiposOperac AS TP ON VH.CdTipoOper=TP.IdTipoOper
 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
 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].[paQryMudOrdenConcLta] @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.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,D.NumDocRef,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago
,D.NumDocFac,D.IndFactItem,D.FechaReg,D.CdUsuario,U.Usuario,CD.RubroConc,CD.MudReajuste
--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
,FacBaseIngreso,DfcBaseIngreso,FacReajuste,DfcReajuste
 FROM Trn_MudOrdenServ AS O 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
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero LEFT JOIN adm_Usuarios AS U ON D.CdUsuario=U.IdUsuario
 --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(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValor
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCosto
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN VrImpuesto*-1 ELSE VrImpuesto END) AS FacImpuesto
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN VrSeguroRem*-1 ELSE VrSeguroRem END) AS FacSeguro
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='FC' THEN VrDeclMcia ELSE 0 END) AS FacDeclarado
,MAX(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='FC' THEN FR.Factura ELSE 0 END) AS FacNumero
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND SUBSTRING(FR.TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacBaseIngreso
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE 0 END) AS DfcBaseIngreso
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND C.MudReajuste=1 AND SUBSTRING(FR.TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacReajuste
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND C.MudReajuste=1 AND SUBSTRING(FR.TipDoc,1,2)='DF' THEN Cantidad*VrUnitario ELSE 0 END) AS DfcReajuste
 FROM Trn_TraFacRemesas AS FR LEFT JOIN ConcDiversos AS C ON FR.CdConcepto=C.IdConcepto
 WHERE FR.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