if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsConcDiversos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudInventario]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudInventario]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudOrdenVeh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudOrdenVeh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposOperac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposOperac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraLiqViajes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraLiqViajes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraRemAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryConcDiversos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryConcDiversosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryConcDiversosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudInventario]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudInventario]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudInventario_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudInventario_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudInventarioLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudInventarioLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServLta]
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].[paQryMudOrdenVeh_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenVeh_Cr]
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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposOperac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposOperac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqManifFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqManifFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraLiqViajesRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraLiqViajesRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaMuc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaMuc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpConcDiversos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMudInventario]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMudInventario]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposOperac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposOperac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraLiqViajes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraLiqViajes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraRemAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraRemAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosPol]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosPol]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoDet]
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,VH.CdTipoOper,TipoOperacion,VH.TipoIngreso,VH.TarifaIng
,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 TiposOperac AS TP ON VH.CdTipoOper=TP.IdTipoOper
 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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT LQ.TipDoc AS TipoLiq,TipoDoc,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc
,LQ.IdVehiculo AS PlacaVeh,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,LQ.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,LQ.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Referencia,DescMcias,LiqMixto,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,Estado,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario,Leyenda
,Item,D.IdConcepto AS DetCodConc,CG.Concepto AS DetNomConc,DescConcepto,D.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito,D.IdTercero AS NitTercero,N.RazonSocial AS NomTercero
,CdCCosto,CCosto,CdSubCos,SubCosto,D.RubroConc,VrBase,TarifBase,CantComb,TipFac,Factura,IdCiaFac,ItemFac,FecVence,RefConcep
--datos del poseedor
,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,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraLiqViajes AS LQ  INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON LQ.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON LQ.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON LQ.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON LQ.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON LQ.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 Terceros AS ETR ON LQ.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraLiqDetalle AS D ON LQ.TipDoc=D.TipDoc AND LQ.Liquidacion=D.Liquidacion AND LQ.IdCia=D.IdCia
 LEFT JOIN ConcDiversos AS CG ON D.IdConcepto=CG.IdConcepto LEFT JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS N ON  D.IdTercero=N.IdTercero
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 WHERE LQ.TipDoc=@pmTipDoc AND LQ.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND LQ.IdCia=@pmIdCia
 ORDER BY LQ.Liquidacion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesRel] @pmTipDoc VARCHAR(3),@pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT LQ.TipDoc AS TipoLiq,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc
,LQ.IdVehiculo AS PlacaVeh,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,LQ.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,LQ.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Referencia,DescMcias,LiqMixto,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario
--datos del poseedor
,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,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraLiqViajes AS LQ  INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON LQ.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON LQ.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON LQ.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 Terceros AS ETR ON LQ.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 WHERE LQ.TipDoc=@pmTipDoc  AND LQ.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND LQ.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND LQ.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND LQ.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND LQ.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND LQ.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY LQ.IdCia,LQ.Liquidacion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT LQ.TipDoc AS TipoLiq,LQ.Liquidacion AS NumLiquida,LQ.IdCia AS CdCia,Compania,Fecha,LQ.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc
,LQ.IdVehiculo AS PlacaVeh,LQ.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,LQ.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,LQ.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,LQ.NitEmpresa AS Nit_Empresa,ETR.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen
,LQ.IdRuta AS CdRuta,Ruta,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Referencia,DescMcias,LiqMixto,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,LQ.Observacion AS Observ,LQ.IdEstado AS CdEstado,TimeSys,LQ.FecUpdate AS FechaAct,IdCiaCrea,LQ.IdUsuario AS CdUsuario,Usuario
--detalles
,Item,D.IdConcepto AS DetCodConc,CG.Concepto AS DetNomConc,DescConcepto,D.IdCuenta AS CdCuenta,NomCuenta,VrDebito,VrCredito,D.IdTercero AS NitTercero,N.RazonSocial AS NomTercero
,CdCCosto,CCosto,CdSubCos,SubCosto,D.RubroConc,VrBase,TarifBase,CantComb,TipFac,Factura,IdCiaFac,ItemFac,FecVence,RefConcep
--datos del poseedor
,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,V.NitEmpresa AS VehNitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraLiqViajes AS LQ  INNER JOIN Companias AS CN ON LQ.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON LQ.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON LQ.IdConcepto=C.IdConcepto
 INNER JOIN Localidades AS CO ON  LQ.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON LQ.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON LQ.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON LQ.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON LQ.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON LQ.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON LQ.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 Terceros AS ETR ON LQ.NitEmpresa=ETR.IdTercero LEFT JOIN TiposCom AS TCM ON LQ.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraLiqDetalle AS D ON LQ.TipDoc=D.TipDoc AND LQ.Liquidacion=D.Liquidacion AND LQ.IdCia=D.IdCia
 LEFT JOIN ConcDiversos AS CG ON D.IdConcepto=CG.IdConcepto LEFT JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS N ON  D.IdTercero=N.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 WHERE LQ.TipDoc=@pmTipDoc  AND LQ.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND LQ.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND LQ.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND LQ.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND LQ.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND LQ.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY LQ.IdCia,LQ.Liquidacion
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.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,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,D.FechaReg,D.CdUsuario AS DetCdUsuario,UC.Usuario AS DetUsuario,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,FacBaseIngreso,DfcBaseIngreso,FacReajuste,DfcReajuste
--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,U.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
 LEFT JOIN adm_Usuarios AS UC ON D.CdUsuario=UC.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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenVeh_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT VH.TipDoc,VH.NumOrden,VH.IdCia,VH.Item,VH.IdVehiculo,VH.TipoAfiVehic,VH.NitEmpresa,NomEmpresa,VH.nRemolque
,VH.IdConductor,NC.RazonSocial AS Conductor,IdCondAux,NA.RazonSocial AS CondAuxiliar,TipDocRef,VH.NumDocRef,CdCiaRef,VH.CdTipoOper,TipoOperacion,VH.TipoIngreso,VH.TarifaIng
,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.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
 FROM Trn_MudOrdenVeh AS VH 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 TercCndtores AS CDT ON VH.IdConductor=CDT.IdConductor LEFT JOIN TercCndtores AS CDA ON VH.IdCondAux=CDA.IdConductor
 LEFT JOIN TiposOperac AS TP ON VH.CdTipoOper=TP.IdTipoOper
 WHERE VH.TipDoc=@pmTipDoc AND VH.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND VH.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraLiqManifFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
SELECT L.TipDoc,L.Liquidacion,L.IdCia AS CdCia,L.Item,L.TipMuc,L.Manifiesto,L.IdCiaMuc,M.Fecha AS FechaMuc,M.FecDespacho
,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,M.IdRuta AS CdRuta,RT.Ruta AS DescRuta,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,M.nRemolque,M.TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,M.VrFletes,VrAnticipo,VrAntAdic,VrNeto,TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,M.FechaPago,NumMintrans,EdoMintrans,M.EstCumplido,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.VrFleteNeto,M.Anulado,M.FecDev,M.Observacion AS Observ
,MA.VrFleteTon,MA.TipoRuta AS MucTipoRuta,MA.NomRemite AS NomRmtente,MA.NomDestino AS NomDestnatario,NumAnticipo,NumCheque,MA.TipoMintrans AS TipoMucMintrans,MucMintrans,VolumenCarga
 FROM Trn_TraLiqManif AS L INNER JOIN Trn_TraManifiesto AS M ON L.TipMuc=M.TipDoc AND L.Manifiesto=M.Manifiesto AND L.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON L.TipMuc=MA.TipDoc AND L.Manifiesto=MA.Manifiesto AND L.IdCiaMuc=MA.IdCia
 INNER JOIN Localidades AS CO ON M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS RT ON M.IdRuta=RT.IdRuta 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 Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  
 WHERE L.TipDoc=@pmTipDoc AND L.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND L.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 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,O.IdDestino,LD.Localidad AS CiuDestino,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.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,EstadoBod,O.CdBodega,FechaIngBod,FechaRetBod
,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,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.NumDocFac,D.IndFactItem,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,D.FechaReg,D.CdUsuario AS DetCdUsuario,UC.Usuario AS DetUsuario,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
,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
 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 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 
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia LEFT JOIN adm_Usuarios AS UC ON D.CdUsuario=UC.IdUsuario
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudInventarioLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT M.NumInvent,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,M.IdTipoServ AS CdTipoServ,TipoServicio,M.IdCliente,T.RazonSocial AS NomCliente,M.IdAgencia AS CdAgencia,Agencia
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,M.IdOrigen,LO.Localidad AS CiuOrigen,M.IdDestino,LD.Localidad AS CiuDestino,M.pVehiculo,M.CdConductor,NC.RazonSocial AS Conductor,M.nRemolque,M.TipoAfiVehic,M.NitEmpresa,M.NomEmpresa
,CajasEmp,CajasClie,M.VrDeclarado,M.VrServicio,M.NumCotizac,M.NumPlanilla,NumActa,M.TipOsm,M.NumOrden,M.IdCiaOsm,M.NomContacto,M.TelsContacto,M.emlContacto,M.Responsable,M.FormaPago
,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.OrigenAdd,M.TimeSys AS FechaCrea,M.FecUpdate,M.IdCiaCrea AS CdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
,M.IdRemitente,M.Remitente,M.IdDestinatario,M.Destinatario,M.IdVend,M.ContactoDest
FROM Trn_MudInventario AS M INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN Terceros AS T ON M.IdCliente=T.IdTercero
INNER JOIN MudServicios AS TS ON M.IdTipoServ=TS.IdTipoServ INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal
LEFT JOIN Terceros AS NC ON M.CdConductor=NC.IdTercero LEFT JOIN Agencias AS A ON M.IdAgencia=A.IdAgencia 
WHERE M.TipDoc=@pmTipDoc AND M.FecDespacho BETWEEN @pmFechaIni AND @pmFechaFin AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudInventario_Cr] @pmTipDoc VARCHAR(3),@pmNumInventIni INT,@pmNumInventFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc,TipoDoc,M.NumInvent,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,M.IdTipoServ AS CdTipoServ,TipoServicio,M.IdCliente,T.RazonSocial AS NomCliente,M.IdAgencia AS CdAgencia,Agencia
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,M.IdOrigen,LO.Localidad AS CiuOrigen,DPO.Departamento AS DptoOrigen,M.IdDestino,LD.Localidad AS CiuDestino,DPD.Departamento AS DptoDestino,M.pVehiculo,M.CdConductor,NC.RazonSocial AS Conductor
,M.nRemolque,M.TipoAfiVehic,M.NitEmpresa,M.NomEmpresa,CajasEmp,CajasClie,M.VrDeclarado,M.VrServicio,M.NumCotizac,M.NumPlanilla,NumActa,M.TipOsm,M.NumOrden,M.IdCiaOsm,M.NomContacto,M.TelsContacto
,M.emlContacto,M.Responsable,M.FormaPago,M.Remitente AS ContactoCargue,M.Destinatario AS ContactoDestino,M.ContactoDest,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,ED.Estado
,M.OrigenAdd,M.TimeSys AS FechaCrea,M.FecUpdate,M.IdCiaCrea AS CdCiaCrea,M.IdUsuario AS CdUsuario,U.Usuario
,D.Item,D.IdArticulo AS CdArticulo,D.Descripcion,D.Cantidad,D.CantSalida,D.IdEstado AS CdEstadoArt,EA.Estado AS EstadoArt,D.Observacion AS DetObserv,Articulo,MA.IdSeccion AS CdSeccion,Seccion,EA.NColor AS NColorEst
,D.TipoIngreso,D.FechaReg,D.CdUsuario AS DetCdUsuario,UD.Usuario AS DetUsuario
FROM Trn_MudInventario AS M INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN Terceros AS T ON M.IdCliente=T.IdTercero
INNER JOIN MudServicios AS TS ON M.IdTipoServ=TS.IdTipoServ INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc
INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
INNER JOIN Trn_MudInventDet AS D ON M.TipDoc=D.TipDoc AND M.NumInvent=D.NumInvent AND M.IdCia=D.IdCia
INNER JOIN MudArticulos AS MA ON D.IdArticulo=MA.IdArticulo INNER JOIN MudSecciones AS SC ON MA.IdSeccion=SC.IdSeccion
INNER JOIN MudEstados AS EA ON D.IdEstado=EA.IdEstado
LEFT JOIN Terceros AS NC ON M.CdConductor=NC.IdTercero LEFT JOIN Agencias AS A ON M.IdAgencia=A.IdAgencia 
LEFT JOIN adm_Usuarios AS UD ON D.CdUsuario=UD.IdUsuario 
WHERE M.TipDoc=@pmTipDoc AND M.NumInvent BETWEEN @pmNumInventIni AND @pmNumInventFin AND M.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT 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,O.IdDestino,LD.Localidad AS CiuDestino,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.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,EstadoBod,O.CdBodega,FechaIngBod,FechaRetBod
,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,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 Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ 
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 WHERE O.TipDoc=@pmTipDoc AND O.FecDespacho BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdVend,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,VrServicio,VrEmpaque,VrBodega,VrOtros,VrDescuento,VrImpuesto,VrNeto,VrDeclarado,VrSeguro,Servicios,FormaPago,Referencia,TipInv,NumInvent,IdCiaInv,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact
,TipCausac,Causacion,CdCiaCau,NomContacto,TelsContacto,emlContacto,ContactoDest,emlContDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,EstCumplido,FechaCump,TipoRuta,Volumen,EstadoBod,CdBodega,FechaIngBod,FechaRetBod,NitRemitente,Remitente,NitDestinatario,Destinatario,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_MudOrdenServ
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmVrServicio MONEY,@pmVrEmpaque MONEY,@pmVrBodega MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrNeto MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmServicios VARCHAR(50),@pmFormaPago VARCHAR(50),@pmReferencia VARCHAR(50)
,@pmTipInv VARCHAR(3),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTipRem VARCHAR(3),@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmTipCausac VARCHAR(3),@pmCausacion INT,@pmCdCiaCau CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContactoDest VARCHAR(150),@pmemlContDest VARCHAR(100)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmEstCumplido INT,@pmFechaCump SMALLDATETIME,@pmTipoRuta VARCHAR(10),@pmVolumen VARCHAR(50),@pmEstadoBod INT,@pmCdBodega VARCHAR(4),@pmFechaIngBod SMALLDATETIME,@pmFechaRetBod SMALLDATETIME,@pmNitRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmNitDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_MudOrdenServ SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,IdTipoServ=@pmIdTipoServ,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVend=@pmIdVend,DirCargue=@pmDirCargue,TipoInmRem=@pmTipoInmRem,PisoCargue=@pmPisoCargue,TelsRemite=@pmTelsRemite,CelRemite=@pmCelRemite,DirDescargue=@pmDirDescargue,TipoInmDest=@pmTipoInmDest,PisoDescargue=@pmPisoDescargue,TelsDest=@pmTelsDest,CelDest=@pmCelDest
,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,VrServicio=@pmVrServicio,VrEmpaque=@pmVrEmpaque,VrBodega=@pmVrBodega,VrOtros=@pmVrOtros,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrNeto=@pmVrNeto,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,Servicios=@pmServicios,FormaPago=@pmFormaPago,Referencia=@pmReferencia,TipInv=@pmTipInv,NumInvent=@pmNumInvent,IdCiaInv=@pmIdCiaInv,TipRem=@pmTipRem,NumRemesa=@pmNumRemesa,IdCiaRem=@pmIdCiaRem
,EstFactura=@pmEstFactura,TipFact=@pmTipFact,NumFactura=@pmNumFactura,CdCiaFact=@pmCdCiaFact,TipCausac=@pmTipCausac,Causacion=@pmCausacion,CdCiaCau=@pmCdCiaCau,NomContacto=@pmNomContacto,TelsContacto=@pmTelsContacto,emlContacto=@pmemlContacto,ContactoDest=@pmContactoDest,emlContDest=@pmemlContDest,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,EstCumplido=@pmEstCumplido
,FechaCump=@pmFechaCump,TipoRuta=@pmTipoRuta,Volumen=@pmVolumen,EstadoBod=@pmEstadoBod,CdBodega=@pmCdBodega,FechaIngBod=@pmFechaIngBod,FechaRetBod=@pmFechaRetBod,NitRemitente=@pmNitRemitente,Remitente=@pmRemitente,NitDestinatario=@pmNitDestinatario,Destinatario=@pmDestinatario,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmVrServicio MONEY,@pmVrEmpaque MONEY,@pmVrBodega MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrNeto MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmServicios VARCHAR(50),@pmFormaPago VARCHAR(50),@pmReferencia VARCHAR(50)
,@pmTipInv VARCHAR(3),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTipRem VARCHAR(3),@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmTipCausac VARCHAR(3),@pmCausacion INT,@pmCdCiaCau CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContactoDest VARCHAR(150),@pmemlContDest VARCHAR(100)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmEstCumplido INT,@pmFechaCump SMALLDATETIME,@pmTipoRuta VARCHAR(10),@pmVolumen VARCHAR(50),@pmEstadoBod INT,@pmCdBodega VARCHAR(4),@pmFechaIngBod SMALLDATETIME,@pmFechaRetBod SMALLDATETIME,@pmNitRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmNitDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150)
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_MudOrdenServ (TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdVend,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,VrServicio,VrEmpaque,VrBodega,VrOtros,VrDescuento,VrImpuesto,VrNeto,VrDeclarado,VrSeguro,Servicios,FormaPago,Referencia,TipInv,NumInvent,IdCiaInv
,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact,TipCausac,Causacion,CdCiaCau,NomContacto,TelsContacto,emlContacto,ContactoDest,emlContDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,EstCumplido,FechaCump,TipoRuta,Volumen,EstadoBod,CdBodega,FechaIngBod,FechaRetBod,NitRemitente,Remitente,NitDestinatario,Destinatario)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmFecha,@pmFecDespacho,@pmIdTipoServ,@pmIdCliente,@pmIdAgencia,@pmIdVend,@pmDirCargue,@pmTipoInmRem,@pmPisoCargue,@pmTelsRemite,@pmCelRemite,@pmDirDescargue,@pmTipoInmDest,@pmPisoDescargue,@pmTelsDest,@pmCelDest,@pmIdOrigen,@pmIdDestino,@pmVrServicio,@pmVrEmpaque,@pmVrBodega,@pmVrOtros,@pmVrDescuento,@pmVrImpuesto,@pmVrNeto,@pmVrDeclarado,@pmVrSeguro
,@pmServicios,@pmFormaPago,@pmReferencia,@pmTipInv,@pmNumInvent,@pmIdCiaInv,@pmTipRem,@pmNumRemesa,@pmIdCiaRem,@pmEstFactura,@pmTipFact,@pmNumFactura,@pmCdCiaFact,@pmTipCausac,@pmCausacion,@pmCdCiaCau,@pmNomContacto,@pmTelsContacto,@pmemlContacto,@pmContactoDest,@pmemlContDest,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmEstCumplido,@pmFechaCump
,@pmTipoRuta,@pmVolumen,@pmEstadoBod,@pmCdBodega,@pmFechaIngBod,@pmFechaRetBod,@pmNitRemitente,@pmRemitente,@pmNitDestinatario,@pmDestinatario)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajesLta] @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  Liquidacion,L.IdCia AS CdCia,Compania,Fecha,L.IdConcepto AS CdConcepto,Concepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc,IdVehiculo,IdConductor,CDT.RazonSocial AS Conductor
,IdPropietario,TP.RazonSocial AS Propietario,IdPoseedor,T.RazonSocial AS Poseedor,NitEmpresa,NE.RazonSocial AS NomEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia
,galsComb,PesoTotal,Cantidad,Volumen,IdRuta,IdLocOrigen,LO.Localidad AS CiudadOrigen,IdLocDestino,LD.Localidad AS CiudadDestino,Referencia,DescMcias,LiqMixto,kmtInicial,kmtFinal
,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,L.Observacion AS Observ,L.IdEstado AS CdEstado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,L.IdUsuario AS Cdusuario,Usuario
 FROM Trn_TraLiqViajes AS L  INNER JOIN Terceros AS T ON L.IdPoseedor=T.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS TP ON L.IdPropietario=TP.IdTercero 
 INNER JOIN Conceptos AS C ON L.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario
 INNER JOIN Companias AS CI ON L.IdCia=CI.IdCia LEFT JOIN Terceros AS NE ON L.NitEmpresa=NE.IdTercero 
 LEFT JOIN Localidades AS LO ON L.IdLocOrigen=LO.IdLocal LEFT JOIN Localidades AS LD ON L.IdLocDestino=LD.IdLocal
 WHERE TipDoc=@pmTipDoc   AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND L.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND L.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
  AND L.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 ORDER BY L.IdCia,Liquidacion
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraLiqViajes] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmModalidad VARCHAR(10),@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmFechaMuc SMALLDATETIME,@pmIdVehiculo VARCHAR(10)
,@pmIdConductor VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmNitEmpresa VARCHAR(16),@pmVrAnticipos MONEY,@pmVrGastos MONEY,@pmVrCombustible MONEY,@pmVrDiferencia MONEY,@pmgalsComb DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmCantidad DECIMAL(14,4),@pmVolumen DECIMAL(14,4)
,@pmIdRuta VARCHAR(4),@pmIdLocOrigen VARCHAR(8),@pmIdLocDestino VARCHAR(8),@pmReferencia VARCHAR(50),@pmDescMcias VARCHAR(150),@pmkmtInicial DECIMAL(14,4),@pmkmtFinal DECIMAL(14,4),@pmEstFactura INT,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmFecDocRef SMALLDATETIME
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrOtrosCred MONEY,@pmLiqMixto BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraLiqViajes (TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor,NitEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen,IdRuta,IdLocOrigen,IdLocDestino,Referencia,DescMcias
,kmtInicial,kmtFinal,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,LiqMixto)
 VALUES (@pmTipDoc,@pmLiquidacion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmModalidad,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmFechaMuc,@pmIdVehiculo,@pmIdConductor,@pmIdPropietario,@pmIdPoseedor,@pmNitEmpresa,@pmVrAnticipos,@pmVrGastos,@pmVrCombustible,@pmVrOtrosCred,@pmVrDiferencia,@pmgalsComb
,@pmPesoTotal,@pmCantidad,@pmVolumen,@pmIdRuta,@pmIdLocOrigen,@pmIdLocDestino,@pmReferencia,@pmDescMcias,@pmkmtInicial,@pmkmtFinal,@pmEstFactura,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmFecDocRef,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmLiqMixto)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraLiqViajes] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,Modalidad,TipMuc,Manifiesto,IdCiaMuc,FechaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor
,NitEmpresa,VrAnticipos,VrGastos,VrCombustible,VrOtrosCred,VrDiferencia,galsComb,PesoTotal,Cantidad,Volumen,IdRuta,IdLocOrigen,IdLocDestino,Referencia,DescMcias,kmtInicial,kmtFinal
,EstFactura,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,LiqMixto
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraLiqViajes
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraLiqViajes] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmModalidad VARCHAR(10),@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmFechaMuc SMALLDATETIME,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmNitEmpresa VARCHAR(16),@pmVrAnticipos MONEY,@pmVrGastos MONEY,@pmVrCombustible MONEY
,@pmVrDiferencia MONEY,@pmgalsComb DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmCantidad DECIMAL(14,4),@pmVolumen DECIMAL(14,4),@pmIdRuta VARCHAR(4),@pmIdLocOrigen VARCHAR(8),@pmIdLocDestino VARCHAR(8),@pmReferencia VARCHAR(50),@pmDescMcias VARCHAR(150),@pmkmtInicial DECIMAL(14,4),@pmkmtFinal DECIMAL(14,4),@pmEstFactura INT
,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmFecDocRef SMALLDATETIME,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrOtrosCred MONEY,@pmLiqMixto BIT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraLiqViajes SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,Modalidad=@pmModalidad,TipMuc=@pmTipMuc,Manifiesto=@pmManifiesto,IdCiaMuc=@pmIdCiaMuc,FechaMuc=@pmFechaMuc,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,IdPropietario=@pmIdPropietario,IdPoseedor=@pmIdPoseedor,NitEmpresa=@pmNitEmpresa,VrAnticipos=@pmVrAnticipos,VrGastos=@pmVrGastos,VrCombustible=@pmVrCombustible,VrDiferencia=@pmVrDiferencia,galsComb=@pmgalsComb
,PesoTotal=@pmPesoTotal,Cantidad=@pmCantidad,Volumen=@pmVolumen,IdRuta=@pmIdRuta,IdLocOrigen=@pmIdLocOrigen,IdLocDestino=@pmIdLocDestino,Referencia=@pmReferencia,DescMcias=@pmDescMcias,kmtInicial=@pmkmtInicial,kmtFinal=@pmkmtFinal,EstFactura=@pmEstFactura,TipDocRef=@pmTipDocRef,NumDocRef=@pmNumDocRef,IdCiaRef=@pmIdCiaRef,FecDocRef=@pmFecDocRef,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom
,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,VrOtrosCred=@pmVrOtrosCred,LiqMixto=@pmLiqMixto,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudOrdenVeh] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdVehiculo VARCHAR(10),@pmTipoAfiVehic VARCHAR(10)
,@pmNitEmpresa VARCHAR(16),@pmNomEmpresa VARCHAR(150),@pmnRemolque VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdCondAux VARCHAR(16)
,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmCdCiaRef CHAR(2),@pmCdTipoOper VARCHAR(4),@pmTipoIngreso VARCHAR(10),@pmTarifaIng DECIMAL(14,4)
 AS 
 INSERT INTO Trn_MudOrdenVeh (TipDoc,NumOrden,IdCia,Item,IdVehiculo,TipoAfiVehic,NitEmpresa,NomEmpresa,nRemolque,IdConductor,IdCondAux,TipDocRef,NumDocRef,CdCiaRef,CdTipoOper,TipoIngreso,TarifaIng)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmItem,@pmIdVehiculo,@pmTipoAfiVehic,@pmNitEmpresa,@pmNomEmpresa,@pmnRemolque,@pmIdConductor,@pmIdCondAux,@pmTipDocRef,@pmNumDocRef,@pmCdCiaRef,@pmCdTipoOper,@pmTipoIngreso,@pmTarifaIng)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposOperac] @pmIdTipoOper VARCHAR(4)
 AS 
 IF @pmIdTipoOper IS NULL
  BEGIN
   SELECT IdTipoOper,TipoOperacion,TipoIngreso,TarifaIng 
   FROM TiposOperac WHERE Inactivo=0
   ORDER BY TipoOperacion
  END
 ELSE
  BEGIN
   SELECT IdTipoOper,TipoOperacion,TipoIngreso,TarifaIng,Inactivo
   FROM TiposOperac WHERE IdTipoOper=@pmIdTipoOper
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTiposOperac] @pmIdTipoOper VARCHAR(4),@pmTipoOperacion VARCHAR(50),@pmInactivo BIT
,@pmTipoIngreso VARCHAR(10),@pmTarifaIng DECIMAL(14,4)
 AS 
 UPDATE TiposOperac SET TipoOperacion=@pmTipoOperacion,Inactivo=@pmInactivo,TipoIngreso=@pmTipoIngreso,TarifaIng=@pmTarifaIng
 WHERE IdTipoOper=@pmIdTipoOper
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTiposOperac] @pmIdTipoOper VARCHAR(4),@pmTipoOperacion VARCHAR(50),@pmInactivo BIT,@pmTipoIngreso VARCHAR(10),@pmTarifaIng DECIMAL(14,4)
 AS 
 INSERT INTO TiposOperac (IdTipoOper,TipoOperacion,Inactivo,TipoIngreso,TarifaIng)
 VALUES (@pmIdTipoOper,@pmTipoOperacion,@pmInactivo,@pmTipoIngreso,@pmTarifaIng)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudInventario] @pmTipDoc VARCHAR(3),@pmNumInvent INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmIdDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmIdVend VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmNitEmpresa VARCHAR(16),@pmNomEmpresa VARCHAR(150),@pmCajasEmp INT,@pmCajasClie INT,@pmVrDeclarado MONEY,@pmVrServicio MONEY,@pmNumCotizac VARCHAR(20),@pmNumPlanilla VARCHAR(20),@pmNumActa VARCHAR(20)
,@pmTipOsm VARCHAR(3),@pmNumOrden INT,@pmIdCiaOsm CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmResponsable VARCHAR(150),@pmFormaPago VARCHAR(50),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmContactoDest VARCHAR(150),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_MudInventario (TipDoc,NumInvent,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdRemitente,Remitente,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,IdDestinatario,Destinatario,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,IdVend,pVehiculo,CdConductor,nRemolque,TipoAfiVehic,NitEmpresa,NomEmpresa,CajasEmp,CajasClie,VrDeclarado,VrServicio,NumCotizac,NumPlanilla,NumActa,TipOsm,NumOrden,IdCiaOsm,NomContacto,TelsContacto,emlContacto,Responsable
,FormaPago,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,ContactoDest)
 VALUES (@pmTipDoc,@pmNumInvent,@pmIdCia,@pmFecha,@pmFecDespacho,@pmIdTipoServ,@pmIdCliente,@pmIdAgencia,@pmIdRemitente,@pmRemitente,@pmDirCargue,@pmTipoInmRem,@pmPisoCargue,@pmTelsRemite,@pmCelRemite,@pmIdDestinatario,@pmDestinatario,@pmDirDescargue,@pmTipoInmDest,@pmPisoDescargue,@pmTelsDest,@pmCelDest,@pmIdOrigen,@pmIdDestino,@pmIdVend,@pmpVehiculo,@pmCdConductor,@pmnRemolque,@pmTipoAfiVehic,@pmNitEmpresa,@pmNomEmpresa,@pmCajasEmp,@pmCajasClie,@pmVrDeclarado,@pmVrServicio
,@pmNumCotizac,@pmNumPlanilla,@pmNumActa,@pmTipOsm,@pmNumOrden,@pmIdCiaOsm,@pmNomContacto,@pmTelsContacto,@pmemlContacto,@pmResponsable,@pmFormaPago,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmContactoDest)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMudInventario] @pmTipDoc VARCHAR(3),@pmNumInvent INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmIdDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmIdVend VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmNitEmpresa VARCHAR(16),@pmNomEmpresa VARCHAR(150),@pmCajasEmp INT,@pmCajasClie INT,@pmVrDeclarado MONEY,@pmVrServicio MONEY,@pmNumCotizac VARCHAR(20),@pmNumPlanilla VARCHAR(20),@pmNumActa VARCHAR(20)
,@pmTipOsm VARCHAR(3),@pmNumOrden INT,@pmIdCiaOsm CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmResponsable VARCHAR(150),@pmFormaPago VARCHAR(50),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmContactoDest VARCHAR(150),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_MudInventario SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,IdTipoServ=@pmIdTipoServ,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdRemitente=@pmIdRemitente,Remitente=@pmRemitente,DirCargue=@pmDirCargue,TipoInmRem=@pmTipoInmRem,PisoCargue=@pmPisoCargue,TelsRemite=@pmTelsRemite,CelRemite=@pmCelRemite,IdDestinatario=@pmIdDestinatario,Destinatario=@pmDestinatario,DirDescargue=@pmDirDescargue,TipoInmDest=@pmTipoInmDest,PisoDescargue=@pmPisoDescargue,TelsDest=@pmTelsDest,CelDest=@pmCelDest
,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,IdVend=@pmIdVend,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,nRemolque=@pmnRemolque,TipoAfiVehic=@pmTipoAfiVehic,NitEmpresa=@pmNitEmpresa,NomEmpresa=@pmNomEmpresa,CajasEmp=@pmCajasEmp,CajasClie=@pmCajasClie,VrDeclarado=@pmVrDeclarado,VrServicio=@pmVrServicio,NumCotizac=@pmNumCotizac,NumPlanilla=@pmNumPlanilla,NumActa=@pmNumActa,TipOsm=@pmTipOsm,NumOrden=@pmNumOrden,IdCiaOsm=@pmIdCiaOsm,NomContacto=@pmNomContacto,TelsContacto=@pmTelsContacto,emlContacto=@pmemlContacto
,Responsable=@pmResponsable,FormaPago=@pmFormaPago,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ContactoDest=@pmContactoDest,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumInvent=@pmNumInvent AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudInventario] @pmTipDoc VARCHAR(3),@pmNumInvent INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumInvent,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdRemitente,Remitente,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,IdDestinatario,Destinatario,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,IdVend,pVehiculo,CdConductor,nRemolque
,TipoAfiVehic,NitEmpresa,NomEmpresa,CajasEmp,CajasClie,VrDeclarado,VrServicio,NumCotizac,NumPlanilla,NumActa,TipOsm,NumOrden,IdCiaOsm,NomContacto,TelsContacto,emlContacto,Responsable,FormaPago,ContactoDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_MudInventario
 WHERE TipDoc=@pmTipDoc AND NumInvent=@pmNumInvent AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryConcDiversosLta] @pmGrupo VARCHAR(20)=Null,@pmEsServicio BIT=Null
 AS 
 SELECT IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,TI.Tarifa AS TarfIva,CdRet
,TR.Tarifa AS TarfRet,CdIca,TC.Tarifa AS TarfIca,CdAutoret,IdCueDeb,PD.NomCuenta AS NomCueDeb,IdCueCre,PC.NomCuenta AS NomCueCre
,D.Inactivo AS Inactvo,OrdenServ,NovedadNom,CdConcDeveng,CdConcDed,RubroConc,MudReajuste,D.IdUsuario AS IdUsuari,Usuario,D.FechaAdd AS Fech_Add,D.FechaUpdate  AS Fech_Update
 FROM ConcDiversos AS D INNER JOIN  adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 LEFT JOIN  Tablapor AS TI ON D.CdIva=TI.IdTarifa
 LEFT JOIN  Tablapor AS TR ON D.CdRet=TR.IdTarifa
 LEFT JOIN  Tablapor AS TC ON D.CdIca=TC.IdTarifa
 LEFT JOIN Puc AS PD ON D.IdCueDeb=PD.IdCuenta
 LEFT JOIN Puc AS PC ON D.IdCueCre=PC.IdCuenta
 WHERE Grupo LIKE ISNULL(@pmGrupo,'%')
 AND  (EsServicio=ISNULL(@pmEsServicio,0) or EsServicio=ISNULL(@pmEsServicio,1))
 ORDER BY Concepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsConcDiversos] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmGrupo VARCHAR(20),@pmEsServicio BIT,@pmDeManfsto BIT,@pmCdIva VARCHAR(4)
,@pmCdRet VARCHAR(4),@pmCdIca VARCHAR(4),@pmIdCueDeb VARCHAR(16),@pmIdCueCre VARCHAR(16),@pmInactivo BIT
,@pmCdAutoret VARCHAR(4),@pmOrdenServ INT,@pmNovedadNom BIT,@pmCdConcDeveng VARCHAR(4),@pmCdConcDed VARCHAR(4),@pmRubroConc VARCHAR(50),@pmMudReajuste BIT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO ConcDiversos (IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,CdRet,CdIca,CdAutoret,IdCueDeb,IdCueCre,Inactivo,FechaAdd,IdUsuario,OrdenServ,NovedadNom,CdConcDeveng,CdConcDed,RubroConc,MudReajuste)
 VALUES (@pmIdConcepto,@pmConcepto,@pmGrupo,@pmEsServicio,@pmDeManfsto,@pmCdIva,@pmCdRet,@pmCdIca,@pmCdAutoret,@pmIdCueDeb,@pmIdCueCre,@pmInactivo,@pmFechaAdd
,@pmIdUsuario,@pmOrdenServ,@pmNovedadNom,@pmCdConcDeveng,@pmCdConcDed,@pmRubroConc,@pmMudReajuste)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpConcDiversos] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmGrupo VARCHAR(20),@pmEsServicio BIT
,@pmDeManfsto BIT,@pmCdIva VARCHAR(4),@pmCdRet VARCHAR(4),@pmCdIca VARCHAR(4),@pmIdCueDeb VARCHAR(16),@pmIdCueCre VARCHAR(16)
,@pmInactivo BIT,@pmCdAutoret VARCHAR(4),@pmOrdenServ INT,@pmNovedadNom BIT,@pmCdConcDeveng VARCHAR(4),@pmCdConcDed VARCHAR(4)
,@pmRubroConc VARCHAR(50),@pmMudReajuste BIT,@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE ConcDiversos SET Concepto=@pmConcepto,Grupo=@pmGrupo,EsServicio=@pmEsServicio,DeManfsto=@pmDeManfsto,CdIva=@pmCdIva
,CdRet=@pmCdRet,CdIca=@pmCdIca,Inactivo=@pmInactivo,IdCueDeb=@pmIdCueDeb,IdCueCre=@pmIdCueCre,CdAutoret=@pmCdAutoret
,NovedadNom=@pmNovedadNom,CdConcDeveng=@pmCdConcDeveng,CdConcDed=@pmCdConcDed,FechaUpdate=@pmFechaUpdate,OrdenServ=@pmOrdenServ,RubroConc=@pmRubroConc,MudReajuste=@pmMudReajuste
 WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryConcDiversos] @pmIdConcepto VARCHAR(4)
 AS 
 SELECT IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,CdRet,CdIca,IdCueDeb,IdCueCre,Inactivo
,CdAutoret,OrdenServ,NovedadNom,CdConcDeveng,CdConcDed,RubroConc,MudReajuste,FechaAdd,FechaUpdate,IdUsuario
 FROM ConcDiversos
 WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmIdVend VARCHAR(16),@pmIdTarifCom VARCHAR(4),@pmNomCliente VARCHAR(150),@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarCargue VARCHAR(150),@pmLugarDescargue VARCHAR(150)
,@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContacDestino VARCHAR(150),@pmTelContacDest VARCHAR(30),@pmemlContacDest VARCHAR(100),@pmPolizaSeguro VARCHAR(50),@pmNitCiaPoliza VARCHAR(16),@pmNomCiaPoliza VARCHAR(150),@pmFecVencePol SMALLDATETIME
,@pmManifMintrans DECIMAL(14,2),@pmRemMintrans DECIMAL(14,2),@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTomadorPoliza CHAR(1),@pmTipoRuta VARCHAR(10),@pmTipoMintrans VARCHAR(10),@pmNitEmpTransp VARCHAR(16),@pmNumFactEmp VARCHAR(20)
,@pmMvoAnulacion VARCHAR(3),@pmPactoCargue BIT,@pmHorasPactoCargue INT,@pmMinPactoCargue INT,@pmHoraCitaCargue SMALLDATETIME,@pmHoraLlegaCargue SMALLDATETIME,@pmHoraEntraCargue SMALLDATETIME,@pmHoraSaleCargue SMALLDATETIME
,@pmPactoDescargue BIT,@pmHorasPactoDescargue INT,@pmMinPactoDescargue INT,@pmHoraCitaDescargue SMALLDATETIME,@pmHoraLlegaDescargue SMALLDATETIME,@pmHoraEntraDescargue SMALLDATETIME,@pmHoraSaleDescargue SMALLDATETIME
,@pmTipoDevRemesa VARCHAR(3),@pmCodCiuTransb VARCHAR(8),@pmMvoTransbordo VARCHAR(3),@pmCantViajes INT,@pmTipOrdMud VARCHAR(3),@pmItemOsm INT
 AS 
 INSERT INTO Trn_TraRemAnexo (TipDoc,NumOrden,IdCia,IdVend,IdTarifCom,NomCliente,NomRemite,NomDestino,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans
,TipSal,NumSalida,IdCiaSal,NumInvent,IdCiaInv,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,MvoAnulacion,PactoCargue,HorasPactoCargue,MinPactoCargue,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue
,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue,TipoDevRemesa,CodCiuTransb,MvoTransbordo,CantViajes,TipOrdMud,ItemOsm)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmIdVend,@pmIdTarifCom,@pmNomCliente,@pmNomRemite,@pmNomDestino,@pmLugarCargue,@pmLugarDescargue,@pmNomContacto,@pmTelContacto,@pmemlContacto,@pmContacDestino,@pmTelContacDest,@pmemlContacDest,@pmTomadorPoliza,@pmPolizaSeguro,@pmNitCiaPoliza,@pmNomCiaPoliza
,@pmFecVencePol,@pmManifMintrans,@pmRemMintrans,@pmTipSal,@pmNumSalida,@pmIdCiaSal,@pmNumInvent,@pmIdCiaInv,@pmTipoRuta,@pmTipoMintrans,@pmNitEmpTransp,@pmNumFactEmp,@pmMvoAnulacion,@pmPactoCargue,@pmHorasPactoCargue,@pmMinPactoCargue,@pmHoraCitaCargue,@pmHoraLlegaCargue,@pmHoraEntraCargue
,@pmHoraSaleCargue,@pmPactoDescargue,@pmHorasPactoDescargue,@pmMinPactoDescargue,@pmHoraCitaDescargue,@pmHoraLlegaDescargue,@pmHoraEntraDescargue,@pmHoraSaleDescargue,@pmTipoDevRemesa,@pmCodCiuTransb,@pmMvoTransbordo,@pmCantViajes,@pmTipOrdMud,@pmItemOsm)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,IdVend,IdTarifCom,NomCliente,NomRemite,NomDestino,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino
,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipSal,NumSalida,IdCiaSal,NumInvent,IdCiaInv
,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,MvoAnulacion,PactoCargue,HorasPactoCargue,MinPactoCargue,HoraCitaCargue,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue
,PactoDescargue,HorasPactoDescargue,MinPactoDescargue,HoraCitaDescargue,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue,TipoDevRemesa,CodCiuTransb,MvoTransbordo,CantViajes,TipOrdMud,ItemOsm
 FROM Trn_TraRemAnexo
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraRemAnexo] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmIdVend VARCHAR(16),@pmIdTarifCom VARCHAR(4),@pmNomCliente VARCHAR(150),@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarCargue VARCHAR(150),@pmLugarDescargue VARCHAR(150),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30)
,@pmemlContacto VARCHAR(100),@pmContacDestino VARCHAR(150),@pmTelContacDest VARCHAR(30),@pmemlContacDest VARCHAR(100),@pmPolizaSeguro VARCHAR(50),@pmNitCiaPoliza VARCHAR(16),@pmNomCiaPoliza VARCHAR(150),@pmFecVencePol SMALLDATETIME,@pmManifMintrans DECIMAL(14,2),@pmRemMintrans DECIMAL(14,2),@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2)
,@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTomadorPoliza CHAR(1),@pmTipoRuta VARCHAR(10),@pmTipoMintrans VARCHAR(10),@pmNitEmpTransp VARCHAR(16),@pmNumFactEmp VARCHAR(20)
,@pmMvoAnulacion VARCHAR(3),@pmPactoCargue BIT,@pmHorasPactoCargue INT,@pmMinPactoCargue INT,@pmHoraCitaCargue SMALLDATETIME,@pmHoraLlegaCargue SMALLDATETIME,@pmHoraEntraCargue SMALLDATETIME,@pmHoraSaleCargue SMALLDATETIME
,@pmPactoDescargue BIT,@pmHorasPactoDescargue INT,@pmMinPactoDescargue INT,@pmHoraCitaDescargue SMALLDATETIME,@pmHoraLlegaDescargue SMALLDATETIME,@pmHoraEntraDescargue SMALLDATETIME,@pmHoraSaleDescargue SMALLDATETIME,@pmTipoDevRemesa VARCHAR(3),@pmCodCiuTransb VARCHAR(8),@pmMvoTransbordo VARCHAR(3),@pmCantViajes INT,@pmTipOrdMud VARCHAR(3),@pmItemOsm INT
 AS 
 UPDATE Trn_TraRemAnexo SET IdVend=@pmIdVend,IdTarifCom=@pmIdTarifCom,NomCliente=@pmNomCliente,NomRemite=@pmNomRemite,NomDestino=@pmNomDestino,LugarCargue=@pmLugarCargue,LugarDescargue=@pmLugarDescargue,NomContacto=@pmNomContacto,TelContacto=@pmTelContacto,emlContacto=@pmemlContacto,ContacDestino=@pmContacDestino,TelContacDest=@pmTelContacDest
,emlContacDest=@pmemlContacDest,PolizaSeguro=@pmPolizaSeguro,NitCiaPoliza=@pmNitCiaPoliza,NomCiaPoliza=@pmNomCiaPoliza,FecVencePol=@pmFecVencePol,ManifMintrans=@pmManifMintrans,RemMintrans=@pmRemMintrans,TipSal=@pmTipSal,NumSalida=@pmNumSalida,IdCiaSal=@pmIdCiaSal,NumInvent=@pmNumInvent,IdCiaInv=@pmIdCiaInv,TomadorPoliza=@pmTomadorPoliza
,TipoRuta=@pmTipoRuta,TipoMintrans=@pmTipoMintrans,NitEmpTransp=@pmNitEmpTransp,NumFactEmp=@pmNumFactEmp,MvoAnulacion=@pmMvoAnulacion,PactoCargue=@pmPactoCargue,HorasPactoCargue=@pmHorasPactoCargue,MinPactoCargue=@pmMinPactoCargue
,HoraCitaCargue=@pmHoraCitaCargue,HoraLlegaCargue=@pmHoraLlegaCargue,HoraEntraCargue=@pmHoraEntraCargue,HoraSaleCargue=@pmHoraSaleCargue,PactoDescargue=@pmPactoDescargue,HorasPactoDescargue=@pmHorasPactoDescargue,MinPactoDescargue=@pmMinPactoDescargue
,HoraCitaDescargue=@pmHoraCitaDescargue,HoraLlegaDescargue=@pmHoraLlegaDescargue,HoraEntraDescargue=@pmHoraEntraDescargue,HoraSaleDescargue=@pmHoraSaleDescargue,TipoDevRemesa=@pmTipoDevRemesa,CodCiuTransb=@pmCodCiuTransb,MvoTransbordo=@pmMvoTransbordo,CantViajes=@pmCantViajes,TipOrdMud=@pmTipOrdMud,ItemOsm=@pmItemOsm
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaMuc] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
SELECT R.TipDoc AS CdTipDoc,R.NumOrden AS NumRemesa,R.IdCia AS CdCia,Compania,R.Fecha AS FecRemesa,FecDespacho,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia
,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,R.TipoAfiVehic AS TipoAfiVeh,R.Modalidad AS TipoRemesa,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue
,EstCumplido,EstFactura,CdConcepto,Concepto,SerieGuia,NumGuia,R.Observacion AS Observ,R.IdEstado AS CdEdstado,RA.TipoRuta,RA.TipoMintrans,RA.TipOrdMud,RA.NumInvent,RA.IdCiaInv AS CdCiaInv
--detalles
,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS Volmen,UndVol,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,Cases,Cajas,Palets
,NitRemite,Remitente,NitDestntario,Destinatario,DirOrigen,IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,DirDestino,IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TarifClie,D.TarifPago AS RemTarifPago
,D.TarifTabla AS RemTarifTabla,D.VrDeclarado AS ValDeclarado,D.VrSeguro AS ValSeguro,TarifSeguro,UndTarifa,UndTarifPago
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Cumplido,IdCiaCump,FechaCump,DetalleCump
,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
--columnas ocultas
--,NumManif,IdCiaManif,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp
--datos del pedido
,NumPedido,IdCiaPed,R.FechaPed AS FecPedido,ModalidadPed
--datos de factura
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,NumFactura,NumDevFact,FacCantidad,FacValorTotal,FacCostoTotal,FacFaltantes,FacPesoNeto
--datos de manifiesto
,TipoMuc,ISNULL(MucNumero,0) AS MucNumero,MucCdCia,FechaMuc,FecDespMuc,MucPlacaVeh,MucCdPoseedor,NP.RazonSocial AS NomPoseedor,MucCdRuta,MucTarifTabla,MucTarifPago,MUC.VrFletes AS VrTotFletes,MUC.VrRetencion AS MucVrRetencion
,MUC.VrReteIca AS MucVrReteIca,MUC.VrDescuento AS MucVrDcto,MUC.VrAnticipo AS MucVrAnticipo,VrAntAdic,MUC.TarifaRet AS MucTarifRet,MUC.TarifaIca AS MucTarifIca
,NumMintrans,EdoMintrans,MucObserv,MucTipOdp,ISNULL(MucOrdPago,0) AS MucOrdPago,MucCdCiaOdp,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,TotalEgresos
,dbo.FuncMucListaAntcipos(ISNULL(MucNumero,0),MucCdCia) AS Anticipos
--datos del cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 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 L ON T.IdLocal=L.IdLocal
 INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 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 Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Conceptos AS C ON R.CdConcepto=C.IdConcepto 
 LEFT JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
--==== Consulta de pedidos
 LEFT JOIN (SELECT P.TipDoc AS TipoPed,P.Pedido AS nPedido,P.IdCia AS CdCiaPed,P.FecDespacho AS FechaPed,P.IdVend AS CdVend,P.Modalidad AS ModalidadPed
,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,P.Observacion AS PedObserv,D.IdMercancia AS PedCdMcia,D.DescripMcias AS PedDescMcia
,SUM(D.Cantidad) AS PedCantidad,SUM(D.PesoNeto) AS PedPesoNeto,SUM(D.Volumen) AS PedVolumen,SUM(D.Cases) AS PedCases
,SUM(D.Cajas) AS PedCajas,SUM(D.Palets) AS PedPalets,MAX(D.TarifClie) AS PedTarifClie,MAX(D.TarifPago) AS PedTarifPago
 FROM Trn_TraPedido AS P INNER JOIN Trn_TraPedMcias AS D ON P.TipDoc=D.TipDoc AND P.Pedido=D.Pedido AND P.IdCia=D.IdCia
 WHERE P.TipDoc='PDT' AND P.Anulado=0 GROUP BY P.TipDoc,P.Pedido,P.IdCia,P.FecDespacho,P.IdVend,P.Modalidad,Cotizacion,IdCiaCot
,NumAprob,IdCiaApr,FecAprob,P.Observacion,D.IdMercancia,D.DescripMcias) AS PD ON R.NumPedido=PD.nPedido AND R.IdCiaPed=PD.CdCiaPed AND D.IdMercancia=PD.PedCdMcia
--==== consulta de facturas-detalles
 LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem,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 Cases*-1 ELSE Cases END) AS FacCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cajas*-1 ELSE Cajas END) AS FacCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Palets*-1 ELSE Palets END) AS FacPalets
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante*-1 ELSE VrFaltante END) AS FacFaltantes
,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
FROM Trn_TraFacRemesas GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON R.TipDoc=RF.TipRem AND R.NumOrden=RF.Remesa AND R.IdCia=RF.IdCiaRem AND D.Item=RF.ItemRem
--==== consulta de manifiestos 
 LEFT JOIN (SELECT RM.TipRem AS MucTipoRem,RM.Remesa AS MucNumRemesa,RM.IdCiaRem AS MucCdCiaRem,RM.ItemRem AS MucItemRem,RM.TipDoc AS TipoMuc,RM.Manifiesto AS MucNumero,RM.IdCia AS MucCdCia
,RM.TarifTabla AS MucTarifTabla,RM.TarifPago AS MucTarifPago,RemMintrans,M.Fecha AS FechaMuc,M.FecDespacho AS FecDespMuc,M.IdRuta AS MucCdRuta,M.IdOrigen AS MucCdOrigen,M.IdDestino AS MucCdDestino,M.IdVehiculo AS MucPlacaVeh
,M.IdConductor AS MucCedCond,M.nRemolque AS MucnRemolque,TipoAfiVehic,M.IdPoseedor AS MucCdPoseedor,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos
,VrCargos,VrDctos,TarifaFlete,TarifaRet,TarifaIca,NumMintrans,EdoMintrans,M.TipOdp AS MucTipOdp,M.OrdPago AS MucOrdPago,M.IdCiaOdp AS MucCdCiaOdp,M.FechaOdp AS MucFecOdp,M.Observacion AS MucObserv
FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
WHERE M.Anulado=0) AS MUC ON R.TipDoc=MUC.MucTipoRem AND R.NumOrden=MUC.MucNumRemesa AND R.IdCia=MUC.MucCdCiaRem AND D.Item=MUC.MucItemRem
--===== Orden de pago
 LEFT JOIN (SELECT M.TipMuc AS OdpTipMuc,M.Manifiesto AS OdpManif,M.IdCiaMuc AS OdpCdCiaMuc,M.TipDoc AS TipOdp,M.OrdPago AS NumOPago,M.IdCia AS OdpCdCia,OP.Fecha AS FechaOdp
,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto
,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,TarifaRet,TarifaIca,TipoLiq,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,OP.Observacion AS OdpObserv
 FROM Trn_TraOrdenManif AS M INNER JOIN Trn_TraOrdenPago AS OP ON M.TipDoc=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCia=OP.IdCia
 WHERE OP.Anulado=0) AS ODP ON MUC.MucTipOdp=ODP.TipOdp AND MUC.MucOrdPago=ODP.NumOPago AND MUC.MucCdCiaOdp=ODP.OdpCdCia
 LEFT JOIN Terceros AS NP ON MUC.MucCdPoseedor=NP.IdTercero 
-- Egresos
 LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
 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 EGT ON MUC.MucTipOdp=EGT.TipOrden AND MUC.MucOrdPago=EGT.NumOPago AND MUC.MucCdCiaOdp=EGT.CdCiaOpago

 WHERE R.TipDoc='RMT' AND R.Fecha BETWEEN @pmFechaIni AND  @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.Anulado=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryVehiculosPol]
 AS 
 SELECT V.IdVehiculo AS PlacaVeh,NumVeh,ClaseVeh,V.IdTipoVeh AS CdTipo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinea,LineaVeh
,V.IdColor AS CdColor,NomColor,V.IdTipoMot AS CdTipMotor,TipoMotor,V.IdCrceria AS CdCarr,TipoCar,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans
,V.IdCat AS CodCatg,Catpeaje,CdCatv,ClaseMat,Cilind,CapTanq,V.IdCom AS CdTipComb,TipoComb,V.IdLub AS CdLub,TipoLub,V.IdTlla AS CdTipLlantas,TipoLlanta
,IdMarlla,ML.Marca AS MarcaLlantas,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen
,NitEmpresa,NE.RazonSocial AS Empresa,IdPropietario,NP.RazonSocial AS Propietario,IdPoseedor,NT.RazonSocial AS Poseedor,IdConductor,NC.RazonSocial AS Conductor,V.IdPpd AS CdTipProp,TipoProp
,TP.VehPropio,V.TipoAfil,Adquisc,NitProv,NPV.RazonSocial AS Proveedor,FecCompra,VrComcial,VrAseg,VrAvaludo,VidaUtil,FecSalida,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon,V.IdNiv AS CdNivel,NivelServicio
,V.IdGrupo AS CdGrupo,GrupoProp,CdGrupR,CdTarifa,FecIngreso,V.FecVigencia AS FechVigCont,V.FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,TarjProp,FecTProp,VigTProp
,CdLugTp,LT.Localidad AS LugarTarjProp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,V.RevTecMec,V.FecTecMec,V.VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper
,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,V.VrLmtCred,V.VrSaldoAct,V.Descripcion,V.Observacion AS Observ,V.CdCenSer,CentroServ,V.CdLocal,LU.Localidad AS CiuUbicacion
,LU.IdDep AS CodDpto,Departamento,V.Ubicacion,V.PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil
,CdRutaHab,DeclaracImp,TipoIngreso,V.IdOrgTra AS CdOrgTra,NomOrgTrans,GPSoperador,GPSUsuario,GPSClave,CantFiltros,V.IdEstado AS CdEstado,Estado,V.Inactivo AS Inactvo,V.IdUsuario AS CdUsuario,Usuario
,V.FechaAdd AS Fec_Add,V.FechaUpdate AS Fec_Upd,EV.NColor AS NumColor,OutDemand
--información de polizas
,VP.IdClase AS CodClase,ClasePoliza,Numero,FecEmision,VP.FecVigencia AS FecVencePoliza,NitCompania,TCPX.RazonSocial AS NombreCia,Valor,Comntarios,DiasAviso
 FROM Vehiculos AS V INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposMot AS TM ON V.IdTipoMot=TM.IdTipoMot
 INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria INNER JOIN PeajesCat AS CP ON V.IdCat=CP.IdCat
 INNER JOIN TiposFuel AS TF ON V.IdCom=TF.IdCom INNER JOIN TiposLub AS TL ON V.IdLub=TL.IdLub
 INNER JOIN TiposLla AS TLL ON V.IdTlla=TLL.IdTlla INNER JOIN Marcas AS ML ON V.IdMarlla=ML.IdMarca
 INNER JOIN Terceros AS NP ON V.IdPropietario=NP.IdTercero  INNER JOIN Terceros AS NT ON  V.IdPoseedor=NT.IdTercero
 INNER JOIN Terceros AS NC ON V.IdConductor=NC.IdTercero INNER JOIN TiposPpt AS TP ON V.IdPpd=TP.IdPpd
 INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon
 INNER JOIN TiposNivs AS NV ON V.IdNiv=NV.IdNiv
 LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NPV ON V.NitProv=NPV.IdTercero
 LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero LEFT JOIN Localidades AS LT ON V.CdLugTp=LT.IdLocal
 LEFT JOIN CentrosServ AS CS ON V.CdCenSer=CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 LEFT JOIN OrgTransito AS OG ON V.IdOrgTra=OG.IdOrgTra
 LEFT JOIN VehPolizas AS VP ON V.IdVehiculo=VP.IdVehiculo LEFT JOIN ClasePol AS CLP ON VP.IdClase=CLP.IdClase
 LEFT JOIN Terceros AS TCPX ON VP.NitCompania=TCPX.IdTercero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoDet] @pmTipDoc VARCHAR(3),@pmManifiestoIni INT,@pmManifiestoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc AS CdTipMuc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,M.Fecha AS FechaMuc,M.FecDespacho,M.IdRuta AS CdRuta,RT.Ruta AS DescRuta
,M.IdVehiculo AS PlacaVeh,M.nRemolque,M.TipoAfiVehic,M.EstCumplido,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Anulado,M.FecDev,M.Observacion AS Observ
,M.VrFletes,VrRetencion,VrReteIca,VrAnticipo,VrAntAdic,VrNeto,TarifaFlete
,MR.Item AS ItemMuc,TipRem,MR.Remesa AS NumRemesa,MR.IdCiaRem AS CdCiaRem,ItemRem,D.IdMercancia AS CdMercancia,DescripMcias,CodigoMcia,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol
,Cases,Cajas,Palets,NitRemite,Remitente,DirOrigen,D.IdOrigen AS RemCodOrigen,LO.Localidad AS RemCiudadOrigen,LO.IdDep AS RemCdepOrigen,DOR.Departamento AS RemDptoOrigen,NitDestntario,Destinatario,DirDestino
,D.IdDestino AS RemCodDestino,LD.Localidad AS RemCiudadDestino,LD.IdDep AS RemCdepDestino,DDN.Departamento AS RemDptoDestino,TarifClie,MR.TarifTabla AS TarifaTabla,MR.TarifPago AS TarifaPago,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro
,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,DA.Agencia AS DetAgencia
,D.Cumplido AS RemCumplido,D.IdCiaCump AS RemCiaCump,D.FechaCump AS RemFechaCum,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,D.TipOdp AS RemTipoOdp,D.NumeroOdp AS RemNumOdp,D.IdCiaOdp AS RemCiaOdp,TarifOdp,PesoCont,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida
,IdCliente,CL.RazonSocial AS RemNomCliente,R.IdClieFact AS RemIdPropMcia,PM.RazonSocial AS RemPropMcia
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND M.Manifiesto=MA.Manifiesto AND M.IdCia=MA.IdCia
 INNER JOIN Trn_TraManifRem AS MR ON M.TipDoc=MR.TipDoc AND M.Manifiesto=MR.Manifiesto AND M.IdCia=MR.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON MR.TipRem=D.TipDoc  AND MR.Remesa=D.NumOrden AND MR.IdCiaRem=D.IdCia AND MR.ItemRem=D.Item
 INNER JOIN Trn_TraRemesa AS R ON MR.TipRem=R.TipDoc AND MR.Remesa=R.NumOrden AND MR.IdCiaRem=R.IdCia
 INNER JOIN Rutas AS RT ON M.IdRuta=RT.IdRuta 
 INNER JOIN Mercancias AS MCA ON D.IdMercancia=MCA.IdMercancia
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DOR ON LO.IdDep=DOR.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DDN ON LD.IdDep=DDN.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo 
 INNER JOIN Terceros AS CL ON R.IdCliente=CL.IdTercero LEFT JOIN Terceros AS PM ON R.IdClieFact=PM.IdTercero
 LEFT JOIN Agencias AS DA ON D.CdAgencia=DA.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 WHERE M.TipDoc=@pmTipDoc AND M.Manifiesto BETWEEN @pmManifiestoIni AND @pmManifiestoFin AND M.IdCia=@pmIdCia
GO