if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelNovedadTra]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paDelNovedadTra]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTraTraslados]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paDelTraTraslados]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsCompanias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNovedadTra]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsNovedadTra]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsPeajesClase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsPeajesClase]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTarifasMasivo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTarifasMasivo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTarifasPaq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTarifasPaq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposVeh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTiposVeh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraRemCum_Trr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraRemCum_Trr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraRemesa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraRemesa]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraTraslados]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraTraslados]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompanias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompanias_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompanias_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompaniasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompaniasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexSub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryKardexSub]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNovedadTra]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNovedadTra]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPeajesClase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryPeajesClase]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasMasivoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTarifasMasivoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasPaqLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTarifasPaqLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposVeh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTiposVeh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposVehDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTiposVehDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraRemesa]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraRemesaFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraRemesaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraRemesaRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraRemesaRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraTraslados]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraTraslados]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraTrasladosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraTrasladosFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraTrasladosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraTrasladosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpCompanias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNovedadTra]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpNovedadTra]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpPeajesClase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpPeajesClase]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTarifasMasivo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTarifasMasivo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTarifasPaq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTarifasPaq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposVeh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTiposVeh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraRemesa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTraRemesa]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraTraslados]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTraTraslados]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsFormaspago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpFormaspago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFormaspago]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraRemesaRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT R.TipDoc AS CdTipDoc,R.NumOrden AS Num_Orden,R.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli
,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,IdClieFact,IdRemitente,NomRemite,IdDestinatario,NomDestino,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,IdLocFletes,CF.Localidad AS LugarFletes,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,TipoAfiVehic,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,R.VrDeclarado AS ValDeclarado,R.VrSeguro AS ValSeguro,R.Cantidad AS CantTotal,PesoTotal,Items
,IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,Concepto,CdRutaTarif,TipCom,Comprobante,IdCiaCom,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac
,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario
,RA.IdVend AS CdVend,VN.RazonSocial AS Vendedor,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro
,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipSal,NumSalida,IdCiaSal,TipoRuta,TipoMintrans,CdBodega,Bodega
--Datos del cliente
,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
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraRemesa AS R  INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON R.IdCliente=T.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 CO ON R.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS CF ON R.IdLocFletes=CF.IdLocal  INNER JOIN Terceros AS  CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.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 Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero  LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  LEFT JOIN  Conceptos AS C ON R.CdConcepto=C.IdConcepto 
 LEFT JOIN AlmBodegas AS BG ON R.CdBodega=BG.IdBodega
 WHERE R.TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND R.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 ORDER BY R.IdCia,R.NumOrden

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraRemesaRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT R.TipDoc AS CdTipDoc,R.NumOrden AS Num_Orden,R.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli
,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,IdClieFact,IdLocFletes,CF.Localidad AS LugarFletes,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,TipoAfiVehic,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,R.VrDeclarado AS ValDeclarado,R.VrSeguro AS ValSeguro,R.Cantidad AS CantTotal,PesoTotal,Items
,IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,Concepto,CdRutaTarif,TipCom,Comprobante,IdCiaCom
,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario
,RA.IdVend AS CdVend,VN.RazonSocial AS Vendedor,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro
,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipSal,NumSalida,IdCiaSal,TipoRuta,TipoMintrans,CdBodega,Bodega
--detalles
,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,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,TarifPago,TarifTabla,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3
,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,Cumplido,IdCiaCump,FechaCump,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp,PesoCont
,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.IdMnjo AS CdMnjo,ManejoMcia,D.IdTmcia AS CdTmcia,TipoMcia,CdRango,DescripRango
--Datos del cliente
,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
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraRemesa AS R  INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 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 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 CF ON R.IdLocFletes=CF.IdLocal  INNER JOIN Terceros AS  CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 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 INNER JOIN Empaques AS EMP ON D.IdEmp=EMP.IdEmp
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat INNER JOIN TiposMnjo AS MJ ON D.IdMnjo=MJ.IdMnjo
 INNER JOIN TiposMcia AS TM ON D.IdTmcia=TM.IdTmcia 
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.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 Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero  LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  LEFT JOIN  Conceptos AS C ON R.CdConcepto=C.IdConcepto 
 LEFT JOIN AlmBodegas AS BG ON R.CdBodega=BG.IdBodega
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango
 WHERE R.TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND R.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 ORDER BY R.IdCia,R.NumOrden

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraRemesaFmt] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS CdTipDoc,TipoDoc,R.NumOrden AS Num_Orden,R.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli
,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,IdClieFact,IdRemitente,NomRemite,IdDestinatario,NomDestino,IdLocOrigen,CO.Localidad AS CiuOrigen,IdLocDestino,CD.Localidad AS CiuDestino
,IdLocFletes,CF.Localidad AS LugarFletes,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue
,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,R.VrDeclarado AS ValDeclarado,R.VrSeguro AS ValSeguro,R.Cantidad AS CantTotal,PesoTotal,Items,IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed
,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,Concepto,CdRutaTarif,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario,Leyenda
,RA.IdVend AS CdVend,VN.RazonSocial AS Vendedor,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro
,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipSal,NumSalida,IdCiaSal,NumInvent,IdCiaInv,TipoRuta,TipoMintrans,NitEmpTransp,NumFactEmp,CdBodega,Bodega
--detalle mcias
,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,Cases,Cajas,Palets,NitRemite,Remitente
,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,NitDestntario,Destinatario,DirDestino,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,TarifClie,TarifPago,TarifTabla,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,Cumplido,IdCiaCump,FechaCump,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp,PesoCont
,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.IdMnjo AS CdMnjo,ManejoMcia,D.IdTmcia AS CdTmcia,TipoMcia,CdRango,DescripRango
--Datos del cliente
,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
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraRemesa AS R  INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 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 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 Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc  INNER JOIN Localidades AS CO ON R.IdLocOrigen=CO.IdLocal INNER JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal
 INNER JOIN Localidades AS CF ON R.IdLocFletes=CF.IdLocal  INNER JOIN Terceros AS  CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 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 INNER JOIN Empaques AS EMP ON D.IdEmp=EMP.IdEmp
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat INNER JOIN TiposMnjo AS MJ ON D.IdMnjo=MJ.IdMnjo
 INNER JOIN TiposMcia AS TM ON D.IdTmcia=TM.IdTmcia 
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.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 Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero  LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Agencias AS DA ON D.CdAgencia=DA.IdAgencia
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango
 LEFT JOIN  Conceptos AS C ON R.CdConcepto=C.IdConcepto LEFT JOIN TiposCom AS TCM ON R.TipCom=TCM.IdCom
 LEFT JOIN AlmBodegas AS BG ON R.CdBodega=BG.IdBodega
 LEFT JOIN TercCndtores AS CT ON R.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 WHERE R.TipDoc=@pmTipDoc AND R.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND R.IdCia=@pmIdCia
 ORDER BY R.NumOrden

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraTrasladosFmt] @pmTipDoc VARCHAR(3),@pmTrasladoIni INT,@pmTrasladoFin INT,@pmIdCia CHAR(2)
AS 
 SELECT T.TipDoc AS CdTipDoc,T.Traslado AS NumTraslado,T.IdCia AS CdCia,Compania,Fecha,FecDespacho,TipoTraslado,T.IdBodega AS CdBodega,BG.Bodega AS BodegaOrig,IdBodDtno,BD.Bodega AS BodegaDtno
,IdVehiculo,IdConductor,CDT.RazonSocial AS NomConductor,Manifiesto,CdCiaMuc,EstadoRec,FechaRec,CedRecibido,NomRecibido
,T.Observacion AS Observ,OrigenAdd,TimeSys,IdCiaCrea,T.IdUsuario AS CdUsuario,Usuario
,DR.Item AS DetItem,TipRem,Remesa,IdCiaRem,ItemRem,EdoRecibido,Comentarios,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol
,Cases,Cajas,Palets,NitRemite,Remitente,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,NitDestntario,Destinatario,DirDestino,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,TarifClie,VrDeclarado,VrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdNovedad,Novedad
 FROM Trn_TraTraslados AS T INNER JOIN Companias AS CN ON T.IdCia=CN.IdCia INNER JOIN Terceros AS CDT ON T.IdConductor=CDT.IdTercero 
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 INNER JOIN AlmBodegas AS BG ON T.IdBodega=BG.IdBodega INNER JOIN AlmBodegas AS BD ON T.IdBodDtno=BD.IdBodega
 INNER JOIN Trn_TraTrasladoRem AS DR ON T.TipDoc=DR.TipDoc AND T.Traslado=DR.Traslado AND T.IdCia=DR.IdCia
 LEFT JOIN Trn_TraRemMcias AS D ON DR.TipRem=D.TipDoc AND DR.Remesa=D.NumOrden AND DR.IdCiaRem=D.IdCia AND DR.ItemRem=D.Item
 LEFT JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal LEFT JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal LEFT JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN NovedadTra AS NV ON DR.CdNovedad=NV.IdNovedad
 WHERE T.TipDoc=@pmTipDoc AND T.Traslado BETWEEN @pmTrasladoIni AND @pmTrasladoFin AND T.IdCia=@pmIdCia
 

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNovedadTra] @pmIdNovedad VARCHAR(4),@pmNovedad VARCHAR(50),@pmInactivo BIT
 AS 
 INSERT INTO NovedadTra (IdNovedad,Novedad,Inactivo)
 VALUES (@pmIdNovedad,@pmNovedad,@pmInactivo)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNovedadTra] @pmIdNovedad VARCHAR(4),@pmNovedad VARCHAR(50),@pmInactivo BIT
 AS 
 UPDATE NovedadTra SET Novedad=@pmNovedad,Inactivo=@pmInactivo
 WHERE IdNovedad=@pmIdNovedad

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNovedadTra] @pmIdNovedad VARCHAR(4)
 AS 
 IF @pmIdNovedad IS NULL
  SELECT IdNovedad,Novedad
  FROM NovedadTra
  WHERE Inactivo=0
  ORDER BY IdNovedad
 ELSE
  SELECT IdNovedad,Novedad,Inactivo
  FROM NovedadTra
  WHERE IdNovedad=@pmIdNovedad

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelNovedadTra] @pmIdNovedad VARCHAR(4)
 AS 
 DELETE FROM NovedadTra
 WHERE IdNovedad=@pmIdNovedad

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraTrasladosLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT  Traslado,T.IdCia AS CdCia,Compania,Fecha,FecDespacho,TipoTraslado,T.IdBodega AS CdBodega,BG.Bodega AS BodegaOrig,IdBodDtno,BD.Bodega AS BodegaDtno,IdVehiculo
,IdConductor,CDT.RazonSocial AS NomConductor,Cantidad,PesoTotal,Manifiesto,CdCiaMuc,EstadoRec,FechaRec,CedRecibido,NomRecibido
,T.Observacion AS Observ,OrigenAdd,TimeSys,IdCiaCrea,T.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraTraslados AS T INNER JOIN Companias AS CN ON T.IdCia=CN.IdCia INNER JOIN Terceros AS CDT ON T.IdConductor=CDT.IdTercero 
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 INNER JOIN AlmBodegas AS BG ON T.IdBodega=BG.IdBodega INNER JOIN AlmBodegas AS BD ON T.IdBodDtno=BD.IdBodega
 WHERE TipDoc='TRR' AND T.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND T.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND T.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND T.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelTraTraslados] @pmTipDoc VARCHAR(3),@pmTraslado INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_TraTraslados
 WHERE TipDoc=@pmTipDoc AND Traslado=@pmTraslado AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraTraslados] @pmTipDoc VARCHAR(3),@pmTraslado INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmTipoTraslado INT,@pmIdBodega VARCHAR(4),@pmIdBodDtno VARCHAR(4),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmCantidad INT,@pmPesoTotal DECIMAL(14,4)
,@pmManifiesto INT,@pmCdCiaMuc CHAR(2),@pmEstadoRec INT,@pmFechaRec SMALLDATETIME,@pmCedRecibido VARCHAR(16),@pmNomRecibido VARCHAR(150),@pmObservacion VARCHAR(1000),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraTraslados (TipDoc,Traslado,IdCia,Fecha,FecDespacho,TipoTraslado,IdBodega,IdBodDtno,IdVehiculo,IdConductor,Cantidad,PesoTotal,Manifiesto,CdCiaMuc,EstadoRec,FechaRec,CedRecibido,NomRecibido,Observacion,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmTraslado,@pmIdCia,@pmFecha,@pmFecDespacho,@pmTipoTraslado,@pmIdBodega,@pmIdBodDtno,@pmIdVehiculo,@pmIdConductor,@pmCantidad,@pmPesoTotal,@pmManifiesto,@pmCdCiaMuc,@pmEstadoRec,@pmFechaRec,@pmCedRecibido,@pmNomRecibido,@pmObservacion,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraTraslados] @pmTipDoc VARCHAR(3),@pmTraslado INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmTipoTraslado INT,@pmIdBodega VARCHAR(4),@pmIdBodDtno VARCHAR(4),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmCantidad INT,@pmPesoTotal DECIMAL(14,4)
,@pmManifiesto INT,@pmCdCiaMuc CHAR(2),@pmEstadoRec INT,@pmFechaRec SMALLDATETIME,@pmCedRecibido VARCHAR(16),@pmNomRecibido VARCHAR(150),@pmObservacion VARCHAR(1000)
 AS 
 UPDATE Trn_TraTraslados SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,TipoTraslado=@pmTipoTraslado,IdBodega=@pmIdBodega,IdBodDtno=@pmIdBodDtno,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,Cantidad=@pmCantidad,PesoTotal=@pmPesoTotal,Manifiesto=@pmManifiesto
,CdCiaMuc=@pmCdCiaMuc,EstadoRec=@pmEstadoRec,FechaRec=@pmFechaRec,CedRecibido=@pmCedRecibido,NomRecibido=@pmNomRecibido,Observacion=@pmObservacion
 WHERE TipDoc=@pmTipDoc AND Traslado=@pmTraslado AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraTraslados] @pmTipDoc VARCHAR(3),@pmTraslado INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Traslado,IdCia,Fecha,FecDespacho,TipoTraslado,IdBodega,IdBodDtno,IdVehiculo,IdConductor,Cantidad,PesoTotal,Manifiesto,CdCiaMuc,EstadoRec,FechaRec,CedRecibido,NomRecibido,Observacion,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario
 FROM Trn_TraTraslados
 WHERE TipDoc=@pmTipDoc AND Traslado=@pmTraslado AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryCompaniasLta] @pmIdCia CHAR(2)=Null,@pmInactivo BIT=Null
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,Inactivo,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,CodBodega,FechaAdd,FechaUpdate
 FROM Companias
 WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND (Inactivo=ISNULL(@pmInactivo,0) or Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY IdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryCompanias] @pmIdCia CHAR(2)
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,CodBodega,FechaAdd,FechaUpdate,Inactivo
 FROM Companias
 WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY IdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryCompanias_Cr] @pmInactivo BIT=Null
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,C.IdCCosto AS CodCcosto,CCosto,C.IdSubCos AS CodSubCosto,SubCosto
,IdTercero,CodSucsal,DireccSuc,TelefSuc,FaxSuc,C.IdLocal AS CodCiu,Localidad,TipoEDS,CodPlanta,CodBodega
,C.FechaAdd AS Fecha_Add,C.FechaUpdate AS FecUpd,C.Inactivo AS Inactvo
 FROM Companias AS C INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto 
 INNER JOIN  SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal
 WHERE (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY IdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsCompanias] @pmIdCia CHAR(2),@pmCompania VARCHAR(50),@pmDirInterface VARCHAR(255),@pmFechaActual SMALLDATETIME
,@pmPeriodoActual SMALLDATETIME,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdTercero VARCHAR(16)
,@pmCodSucsal VARCHAR(10),@pmDireccSuc VARCHAR(250),@pmTelefSuc VARCHAR(20),@pmFaxSuc VARCHAR(20),@pmIdLocal VARCHAR(8),@pmTipoEDS VARCHAR(10)
,@pmCodPlanta VARCHAR(20),@pmCodBodega VARCHAR(4),@pmFechaAdd SMALLDATETIME,@pmInactivo BIT
 AS 
 INSERT INTO Companias (IdCia,Compania,DirInterface,FechaActual,PeriodoActual,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,CodPlanta,FechaAdd,Inactivo,CodBodega)
 VALUES (@pmIdCia,@pmCompania,@pmDirInterface,@pmFechaActual,@pmPeriodoActual,@pmIdCCosto,@pmIdSubCos,@pmIdTercero
,@pmCodSucsal,@pmDireccSuc,@pmTelefSuc,@pmFaxSuc,@pmIdLocal,@pmTipoEDS,@pmCodPlanta,@pmFechaAdd,@pmInactivo,@pmCodBodega)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpCompanias] @pmIdCia CHAR(2),@pmCompania VARCHAR(50),@pmDirInterface VARCHAR(255),@pmFechaActual SMALLDATETIME,@pmPeriodoActual SMALLDATETIME,@pmIdCCosto VARCHAR(16)
,@pmIdSubCos VARCHAR(16),@pmIdTercero VARCHAR(16),@pmCodSucsal VARCHAR(10),@pmDireccSuc VARCHAR(250),@pmTelefSuc VARCHAR(20),@pmFaxSuc VARCHAR(20),@pmIdLocal VARCHAR(8)
,@pmTipoEDS VARCHAR(10),@pmCodPlanta VARCHAR(20),@pmCodBodega VARCHAR(4),@pmFechaUpdate SMALLDATETIME,@pmInactivo BIT
 AS 
 UPDATE Companias SET Compania=@pmCompania,DirInterface=@pmDirInterface,FechaActual=@pmFechaActual,PeriodoActual=@pmPeriodoActual,IdCCosto=@pmIdCCosto
,IdSubCos=@pmIdSubCos,IdTercero=@pmIdTercero,CodSucsal=@pmCodSucsal,DireccSuc=@pmDireccSuc,TelefSuc=@pmTelefSuc,FaxSuc=@pmFaxSuc, IdLocal=@pmIdLocal,TipoEDS=@pmTipoEDS
,CodPlanta=@pmCodPlanta,CodBodega=@pmCodBodega,FechaUpdate=@pmFechaUpdate,Inactivo=@pmInactivo
 WHERE IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraRemesaLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdCia CHAR(2)=Null,@pmIdVehiculo VARCHAR(10)=Null
 AS 
 SELECT R.NumOrden AS Num_Orden,R.IdCia AS CdCia,Fecha,HoraLlegada,FecDespacho,FecEntrega,IdCliente,NomCliente,R.IdAgencia AS CdAgencia,Agencia,IdClieFact,CF.RazonSocial AS ClienteFact,IdRemitente,NomRemite,IdLocOrigen,LO.Localidad AS CiudadOrigen,LugarCargue
,NomContacto,TelContacto,emlContacto,IdDestinatario,NomDestino,IdLocDestino,LD.Localidad AS CiudadDestino,LugarDescargue,ContacDestino,TelContacDest,emlContacDest,IdLocFletes,LF.Localidad AS LugarFletes
,IdVehiculo,nRemolque,TipoAfiVehic,IdConductor,NC.RazonSocial AS Conductor,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos
,VrDeclarado,VrSeguro,Cantidad,PesoTotal,Items,IdMneda,VrTasa,RA.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura
,CdConcepto,CdRutaTarif,CdBodega,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraRemesa AS R  INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 INNER JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
 INNER JOIN Localidades AS LO ON R.IdLocOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON R.IdLocDestino=LD.IdLocal
 INNER JOIN Localidades AS LF ON R.IdLocFletes=LF.IdLocal  INNER JOIN Terceros AS NC ON R.IdConductor=NC.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero LEFT JOIN Terceros AS CF ON R.IdClieFact=CF.IdTercero
 WHERE R.TipDoc=@pmTipDoc AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 ORDER BY R.IdCia,R.NumOrden

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifasMasivoLta] @pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null
AS
 SELECT IdOrigen,O.Localidad AS CiuOrigen,O.IdDep AS CdDepOrig,DOR.Departamento AS DptoOrigen
,IdDestino,D.Localidad AS CiuDestino,D.IdDep AS CdDepDtno,DDT.Departamento AS DptoDtno
,T.IdTipoVeh AS CdTipoVeh,TipoVehiculo,TarifaUnd,VrFletes
 FROM TarifasMasivo AS T INNER JOIN Localidades AS O ON T.IdOrigen=O.IdLocal INNER JOIN Departamentos AS DOR ON O.IdDep=DOR.IdDep
 INNER JOIN Localidades AS D ON T.IdDestino=D.IdLocal INNER JOIN Departamentos AS DDT ON D.IdDep=DDT.IdDep
 INNER JOIN TiposVeh AS TV ON T.IdTipoVeh=TV.IdTipoVeh
 WHERE T.IdOrigen LIKE ISNULL(@pmIdOrigen,'%') AND T.IdDestino LIKE ISNULL(@pmIdDestino,'%')
 AND T.Inactivo=0
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifasPaqLta] @pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null
AS
 SELECT IdOrigen,O.Localidad AS CiuOrigen,O.IdDep AS CdDepOrig,DOR.Departamento AS DptoOrigen
,IdDestino,D.Localidad AS CiuDestino,D.IdDep AS CdDepDtno,DDT.Departamento AS DptoDtno
,TarifaUnd,TiempoEnt,TarifaMin,FormaPago
 FROM TarifasPaq AS T INNER JOIN Localidades AS O ON T.IdOrigen=O.IdLocal INNER JOIN Departamentos AS DOR ON O.IdDep=DOR.IdDep
 INNER JOIN Localidades AS D ON T.IdDestino=D.IdLocal INNER JOIN Departamentos AS DDT ON D.IdDep=DDT.IdDep
 WHERE T.IdOrigen LIKE ISNULL(@pmIdOrigen,'%') AND T.IdDestino LIKE ISNULL(@pmIdDestino,'%')
 AND T.Inactivo=0
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraRemesa] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,IdAgencia,IdClieFact,IdRemitente,IdDestinatario,IdLocOrigen,IdLocDestino,IdLocFletes,IdVehiculo,IdConductor,nRemolque,TipoAfiVehic,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue
,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,VrDeclarado,VrSeguro,Cantidad,PesoTotal,Items,IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,CdRutaTarif
,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac,CdBodega,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraRemesa
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsTraRemesa] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecEntrega SMALLDATETIME,@pmHoraLlegada SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmIdDestinatario VARCHAR(16),@pmIdLocOrigen VARCHAR(8),@pmIdLocDestino VARCHAR(8)
,@pmIdLocFletes VARCHAR(8),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmModalidad VARCHAR(10),@pmTipoTarifa VARCHAR(10),@pmVrCobro MONEY,@pmVrPagos MONEY,@pmVrFletes MONEY,@pmVrCargue MONEY,@pmVrDesCargue MONEY,@pmVrEscolta MONEY,@pmVrDevContdor MONEY,@pmVrTraUrbano MONEY,@pmVrEmbalajes MONEY,@pmVrCargos MONEY
,@pmVrDctos MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmItems INT,@pmIdMneda VARCHAR(5),@pmVrTasa DECIMAL(14,4),@pmNumPedido INT,@pmIdCiaPed CHAR(2),@pmFechaPed SMALLDATETIME,@pmTipDcm VARCHAR(3),@pmNumDocmto INT,@pmIdCiaDcm CHAR(2),@pmFechaDcm SMALLDATETIME,@pmNumManif INT,@pmIdCiaManif CHAR(2),@pmEstCumplido INT
,@pmEstFactura INT,@pmCdConcepto VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmCdRutaTarif VARCHAR(4),@pmSerieGuia CHAR(2),@pmNumGuia INT,@pmCdForma VARCHAR(4),@pmNumCausac INT,@pmCdCiaCausac CHAR(2),@pmEdoCausac INT,@pmCdBodega VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraRemesa (TipDoc,NumOrden,IdCia,Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,IdAgencia,IdClieFact,IdRemitente,IdDestinatario,IdLocOrigen,IdLocDestino,IdLocFletes,IdVehiculo,IdConductor,nRemolque,TipoAfiVehic,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,VrDeclarado,VrSeguro,Cantidad,PesoTotal,Items,IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed
,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,CdRutaTarif,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac,CdBodega,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmFecha,@pmFecDespacho,@pmFecEntrega,@pmHoraLlegada,@pmIdCliente,@pmIdAgencia,@pmIdClieFact,@pmIdRemitente,@pmIdDestinatario,@pmIdLocOrigen,@pmIdLocDestino,@pmIdLocFletes,@pmIdVehiculo,@pmIdConductor,@pmnRemolque,@pmTipoAfiVehic,@pmModalidad,@pmTipoTarifa,@pmVrCobro,@pmVrPagos,@pmVrFletes,@pmVrCargue,@pmVrDesCargue,@pmVrEscolta,@pmVrDevContdor,@pmVrTraUrbano
,@pmVrEmbalajes,@pmVrCargos,@pmVrDctos,@pmVrDeclarado,@pmVrSeguro,@pmCantidad,@pmPesoTotal,@pmItems,@pmIdMneda,@pmVrTasa,@pmNumPedido,@pmIdCiaPed,@pmFechaPed,@pmTipDcm,@pmNumDocmto,@pmIdCiaDcm,@pmFechaDcm,@pmNumManif,@pmIdCiaManif,@pmEstCumplido,@pmEstFactura,@pmCdConcepto,@pmCdRutaTarif,@pmSerieGuia,@pmNumGuia,@pmCdForma,@pmNumCausac,@pmCdCiaCausac,@pmEdoCausac,@pmCdBodega,@pmTipCom,@pmComprobante,@pmIdCiaCom
,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpTraRemesa] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecEntrega SMALLDATETIME,@pmHoraLlegada SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmIdDestinatario VARCHAR(16),@pmIdLocOrigen VARCHAR(8),@pmIdLocDestino VARCHAR(8),@pmIdLocFletes VARCHAR(8),@pmIdVehiculo VARCHAR(10)
,@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmModalidad VARCHAR(10),@pmTipoTarifa VARCHAR(10),@pmVrCobro MONEY,@pmVrPagos MONEY,@pmVrFletes MONEY,@pmVrCargue MONEY,@pmVrDesCargue MONEY,@pmVrEscolta MONEY,@pmVrDevContdor MONEY,@pmVrTraUrbano MONEY,@pmVrEmbalajes MONEY,@pmVrCargos MONEY,@pmVrDctos MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmCantidad DECIMAL(14,4)
,@pmPesoTotal DECIMAL(14,4),@pmItems INT,@pmIdMneda VARCHAR(5),@pmVrTasa DECIMAL(14,4),@pmNumPedido INT,@pmIdCiaPed CHAR(2),@pmFechaPed SMALLDATETIME,@pmTipDcm VARCHAR(3),@pmNumDocmto INT,@pmIdCiaDcm CHAR(2),@pmFechaDcm SMALLDATETIME,@pmNumManif INT,@pmIdCiaManif CHAR(2),@pmEstCumplido INT,@pmEstFactura INT,@pmCdConcepto VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmCdRutaTarif VARCHAR(4),@pmSerieGuia CHAR(2),@pmNumGuia INT,@pmCdForma VARCHAR(4),@pmNumCausac INT,@pmCdCiaCausac CHAR(2),@pmEdoCausac INT,@pmCdBodega VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraRemesa SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,FecEntrega=@pmFecEntrega,HoraLlegada=@pmHoraLlegada,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdClieFact=@pmIdClieFact,IdRemitente=@pmIdRemitente,IdDestinatario=@pmIdDestinatario,IdLocOrigen=@pmIdLocOrigen,IdLocDestino=@pmIdLocDestino,IdLocFletes=@pmIdLocFletes,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,nRemolque=@pmnRemolque,TipoAfiVehic=@pmTipoAfiVehic,Modalidad=@pmModalidad,TipoTarifa=@pmTipoTarifa
,VrCobro=@pmVrCobro,VrPagos=@pmVrPagos,VrFletes=@pmVrFletes,VrCargue=@pmVrCargue,VrDesCargue=@pmVrDesCargue,VrEscolta=@pmVrEscolta,VrDevContdor=@pmVrDevContdor,VrTraUrbano=@pmVrTraUrbano,VrEmbalajes=@pmVrEmbalajes,VrCargos=@pmVrCargos,VrDctos=@pmVrDctos,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,Cantidad=@pmCantidad,PesoTotal=@pmPesoTotal,Items=@pmItems,IdMneda=@pmIdMneda,VrTasa=@pmVrTasa,NumPedido=@pmNumPedido,IdCiaPed=@pmIdCiaPed,FechaPed=@pmFechaPed
,TipDcm=@pmTipDcm,NumDocmto=@pmNumDocmto,IdCiaDcm=@pmIdCiaDcm,FechaDcm=@pmFechaDcm,NumManif=@pmNumManif,IdCiaManif=@pmIdCiaManif,EstCumplido=@pmEstCumplido,EstFactura=@pmEstFactura,CdConcepto=@pmCdConcepto,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,CdRutaTarif=@pmCdRutaTarif,SerieGuia=@pmSerieGuia,NumGuia=@pmNumGuia,CdForma=@pmCdForma
,NumCausac=@pmNumCausac,CdCiaCausac=@pmCdCiaCausac,EdoCausac=@pmEdoCausac,CdBodega=@pmCdBodega,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].[paInstm_TraRemCum_Trr] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmTraslado INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_TraRemCum (tmNumero,tmItem,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifClie,tmTarifPago,tmUndTarif,tmUndTarifPago,tmCantCargue
,tmPesoCargue,tmVolCargue,tmCasesCargue,tmCajasCargue,tmPaletsCargue,tmEstadoCump,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmDetalle,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino)
SELECT @pmtmNumero,D.Item,D.TipRem,D.Remesa,D.IdCiaRem,D.ItemRem,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets,TarifClie,TarifPago,UndTarifa,UndTarifPago,CantidadCump
,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,0,Remision,DocCliente,Referencia1,Referencia2,Referencia3,D.Comentarios,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino
FROM Trn_TraTrasladoRem AS D INNER JOIN Trn_TraRemMcias AS R ON D.TipRem=R.TipDoc AND D.Remesa=R.NumOrden AND D.IdCiaRem=R.IdCia AND D.ItemRem=R.Item
WHERE D.TipDoc=@pmTipDoc AND D.Traslado=@pmTraslado AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsPeajesClase] @pmIdClase VARCHAR(4),@pmClasePeaje VARCHAR(50),@pmNitConsec VARCHAR(16),@pmInactivo BIT
 AS 
 INSERT INTO PeajesClase (IdClase,ClasePeaje,NitConsec,Inactivo)
 VALUES (@pmIdClase,@pmClasePeaje,@pmNitConsec,@pmInactivo)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpPeajesClase] @pmIdClase VARCHAR(4),@pmClasePeaje VARCHAR(50),@pmNitConsec VARCHAR(16),@pmInactivo BIT
 AS 
 UPDATE PeajesClase SET ClasePeaje=@pmClasePeaje,NitConsec=@pmNitConsec,Inactivo=@pmInactivo
 WHERE IdClase=@pmIdClase

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryPeajesClase] @pmIdClase VARCHAR(4)
 AS 
 IF @pmIdClase IS NULL
 SELECT IdClase,ClasePeaje,NitConsec
 FROM PeajesClase
 WHERE Inactivo=0
 ORDER BY IdClase
 ELSE
 SELECT IdClase,ClasePeaje,NitConsec,Inactivo
 FROM PeajesClase
 WHERE IdClase=@pmIdClase

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsTiposVeh] @pmIdTipoVeh VARCHAR(4),@pmTipoVehiculo VARCHAR(50)
,@pmConfiguracion VARCHAR(6),@pmEjes INT,@pmLlantas INT,@pmCapacPeso DECIMAL(14,4),@pmUndCapac VARCHAR(10)
,@pmCapacVol DECIMAL(14,4),@pmUndCapVol VARCHAR(10),@pmCubAlto DECIMAL(14,4),@pmCubLargo DECIMAL(14,4),@pmCubAncho DECIMAL(14,4)
,@pmPesoBruto DECIMAL(14,4),@pmInactivo BIT
 AS 
 INSERT INTO TiposVeh (IdTipoVeh,TipoVehiculo,Configuracion,Ejes,Llantas,CapacPeso,UndCapac,CapacVol,UndCapVol,CubAlto,CubLargo,CubAncho,PesoBruto,Inactivo)
 VALUES (@pmIdTipoVeh,@pmTipoVehiculo,@pmConfiguracion,@pmEjes,@pmLlantas,@pmCapacPeso,@pmUndCapac,@pmCapacVol,@pmUndCapVol,@pmCubAlto,@pmCubLargo,@pmCubAncho,@pmPesoBruto,@pmInactivo)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTiposVehDso] 
 AS 
 SELECT IdTipoVeh,TipoVehiculo,Configuracion,Ejes,Llantas,PesoBruto,CapacPeso,UndCapac
 ,CubAlto,CubLargo,CubAncho,CapacVol,UndCapVol
 FROM TiposVeh
 WHERE Inactivo=0
 ORDER BY TipoVehiculo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpTiposVeh] @pmIdTipoVeh VARCHAR(4),@pmTipoVehiculo VARCHAR(50)
,@pmConfiguracion VARCHAR(6),@pmEjes INT,@pmLlantas INT,@pmCapacPeso DECIMAL(14,4),@pmUndCapac VARCHAR(10)
,@pmCapacVol DECIMAL(14,4),@pmUndCapVol VARCHAR(10),@pmCubAlto DECIMAL(14,4),@pmCubLargo DECIMAL(14,4),@pmCubAncho DECIMAL(14,4)
,@pmPesoBruto DECIMAL(14,4),@pmInactivo BIT
 AS 
 UPDATE TiposVeh SET TipoVehiculo=@pmTipoVehiculo,Configuracion=@pmConfiguracion,Ejes=@pmEjes,Llantas=@pmLlantas
,CapacPeso=@pmCapacPeso,UndCapac=@pmUndCapac,CapacVol=@pmCapacVol,UndCapVol=@pmUndCapVol,CubAlto=@pmCubAlto
,CubLargo=@pmCubLargo,CubAncho=@pmCubAncho,PesoBruto=@pmPesoBruto,Inactivo=@pmInactivo
 WHERE IdTipoVeh=@pmIdTipoVeh
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTiposVeh] @pmIdTipoVeh VARCHAR(4)
 AS 
 SELECT IdTipoVeh,TipoVehiculo,Configuracion,Ejes,Llantas,CapacPeso,UndCapac,CapacVol
,UndCapVol,CubAlto,CubLargo,CubAncho,PesoBruto,Inactivo
 FROM TiposVeh
 WHERE IdTipoVeh=@pmIdTipoVeh
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTarifasMasivo] @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmIdTipoVeh VARCHAR(4),@pmTarifaUnd DECIMAL(14,4),@pmVrFletes DECIMAL(14,4),@pmInactivo BIT
 AS 
 UPDATE TarifasMasivo SET TarifaUnd=@pmTarifaUnd,VrFletes=@pmVrFletes,Inactivo=@pmInactivo
 WHERE IdOrigen=@pmIdOrigen AND IdDestino=@pmIdDestino AND IdTipoVeh=@pmIdTipoVeh

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTarifasMasivo] @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmIdTipoVeh VARCHAR(4),@pmTarifaUnd DECIMAL(14,4),@pmVrFletes DECIMAL(14,4),@pmInactivo BIT
 AS 
 INSERT INTO TarifasMasivo (IdOrigen,IdDestino,IdTipoVeh,TarifaUnd,VrFletes,Inactivo)
 VALUES (@pmIdOrigen,@pmIdDestino,@pmIdTipoVeh,@pmTarifaUnd,@pmVrFletes,@pmInactivo)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTarifasPaq] @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmTarifaUnd DECIMAL(14,4),@pmTiempoEnt DECIMAL(14,4)
,@pmTarifaMin DECIMAL(14,4),@pmFormaPago VARCHAR(20),@pmInactivo BIT
 AS 
 UPDATE TarifasPaq SET TarifaUnd=@pmTarifaUnd,TiempoEnt=@pmTiempoEnt,TarifaMin=@pmTarifaMin,FormaPago=@pmFormaPago,Inactivo=@pmInactivo
 WHERE IdOrigen=@pmIdOrigen AND IdDestino=@pmIdDestino
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTarifasPaq] @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmTarifaUnd DECIMAL(14,4),@pmTiempoEnt DECIMAL(14,4)
,@pmTarifaMin DECIMAL(14,4),@pmFormaPago VARCHAR(20),@pmInactivo BIT
 AS 
 INSERT INTO TarifasPaq (IdOrigen,IdDestino,TarifaUnd,TiempoEnt,TarifaMin,FormaPago,Inactivo)
 VALUES (@pmIdOrigen,@pmIdDestino,@pmTarifaUnd,@pmTiempoEnt,@pmTarifaMin,@pmFormaPago,@pmInactivo)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryKardexSub] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,Combo,EsProdBase AS tmEsProdBase
,IdProv,COUNT(Item) AS SCANT,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(CASE TipDoc WHEN 'ENT' THEN VrCostoEnt-(NumInicial*Entradas) WHEN 'COM' THEN VrCostoEnt-(NumInicial*Entradas)
 ELSE VrCostoEnt END) AS SCOSENT
,SUM(CASE TipDoc WHEN 'DVE' THEN VrCostoSal-(NumInicial*Salidas) WHEN 'DEI' THEN VrCostoSal-(NumInicial*Salidas)
 ELSE VrCostoSal END) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(Entradas*ImpGlobal) AS SGLOENT,SUM(Salidas*ImpGlobal) AS SGLOSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
,SUM(OtroImpto*Entradas) AS SOTRENT,SUM(OtroImpto*Salidas) AS SOTRSAL
,SUM(Rec_Costo*Entradas) AS SRCOSENT,SUM(Rec_Costo*Salidas) AS SRCOSSAL
,SUM(MgenCont*Entradas) AS SMGENENT,SUM(MgenCont*Salidas) AS SMGENSAL
--Para los ajustes con cantidad en cero (0)
,SUM(VrUnitario) AS SCOSAJU,SUM(OtroImpto) AS SDVEAJU
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,Combo,EsProdBase,IdProv
 ORDER BY IdSubgrupo,K.IdProducto

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInsFormaspago] @pmIdForma VARCHAR(4),@pmFormaPago VARCHAR(50),@pmConsecutivo INT,@pmCuadreCaja BIT,@pmRefDcmto BIT
,@pmRefNit BIT,@pmRefBco BIT,@pmRefFech BIT,@pmRefAtza BIT,@pmRefOtro BIT,@pmVldRangos BIT,@pmVleConsumo BIT,@pmCodIntegrity VARCHAR(20),@pmInactivo BIT
 AS 
 INSERT INTO Formaspago (IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo,CodIntegrity,Inactivo)
 VALUES (@pmIdForma,@pmFormaPago,@pmConsecutivo,@pmCuadreCaja,@pmRefDcmto,@pmRefNit,@pmRefBco,@pmRefFech,@pmRefAtza
,@pmRefOtro,@pmVldRangos,@pmVleConsumo,@pmCodIntegrity,@pmInactivo)



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paUpFormaspago] @pmIdForma VARCHAR(4),@pmFormaPago VARCHAR(50),@pmConsecutivo INT,@pmCuadreCaja BIT
,@pmRefDcmto BIT,@pmRefNit BIT,@pmRefBco BIT,@pmRefFech BIT,@pmRefAtza BIT,@pmRefOtro BIT,@pmVldRangos BIT,@pmVleConsumo BIT,@pmCodIntegrity VARCHAR(20),@pmInactivo BIT
 AS 
 UPDATE Formaspago SET FormaPago=@pmFormaPago,Consecutivo=@pmConsecutivo,CuadreCaja=@pmCuadreCaja,RefDcmto=@pmRefDcmto,RefNit=@pmRefNit
,RefBco=@pmRefBco,RefFech=@pmRefFech,RefAtza=@pmRefAtza,RefOtro=@pmRefOtro,VldRangos=@pmVldRangos,VleConsumo=@pmVleConsumo
,CodIntegrity=@pmCodIntegrity,Inactivo=@pmInactivo
 WHERE IdForma=@pmIdForma
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryFormaspago] @pmIdForma VARCHAR(4)
 AS 
 IF @pmIdForma IS NULL
  SELECT IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo,CodIntegrity
  FROM Formaspago
  WHERE Inactivo=0
  ORDER BY Consecutivo
 ELSE
  SELECT IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo,CodIntegrity,Inactivo
  FROM Formaspago
  WHERE IdForma=@pmIdForma



