if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTraCumRadica]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paDelTraCumRadica]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTarifasFletes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraCumplido]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraCumRadica]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraCumRadica]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenManif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraOrdenManif]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraPedAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraPedAnexo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTarifasFletes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasFletesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTarifasFletesLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraCumplido]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraCumplidoFmt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraCumplidoLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraCumplidoRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraCumplidoRelDet]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumRadica]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraCumRadica]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumRadicaFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraCumRadicaFmt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevOdpFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraDevOdpFmt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevOdpLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraDevOdpLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevOdpRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraDevOdpRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenManif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenManif]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoFmt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoNet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoNet]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenPagoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraOrdenPagoRelDet]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPedAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraPedAnexo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPedidoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraPedidoFmt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPedidoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraPedidoRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPedidoRelCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraPedidoRelCon]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPedidoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraPedidoRelDet]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPedidoRem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraPedidoRem]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraRemesaFac]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTarifasFletes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraAnticiposApp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTraAnticiposApp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTraCumplido]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraCumRadica]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTraCumRadica]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraPedAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTraPedAnexo]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelTraCumRadica @pmNumRadica INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_TraCumRadica
 WHERE NumRadica=@pmNumRadica AND IdCia=@pmIdCia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTarifasFletes @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmNumero INT,@pmTipoTarifa VARCHAR(10),@pmTarifClie MONEY,@pmTarifPago MONEY
,@pmTarifTabla MONEY,@pmIdMneda VARCHAR(5),@pmUndMed VARCHAR(10),@pmNitCliente VARCHAR(16),@pmCdNat VARCHAR(4),@pmCdTipoVeh VARCHAR(4)
,@pmCdRango VARCHAR(4),@pmUndTarifClie VARCHAR(10),@pmUndTarifPago VARCHAR(10),@pmInactivo BIT,@pmCdRuta VARCHAR(4),@pmCdMercancia VARCHAR(16),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TarifasFletes (Numero,IdOrigen,IdDestino,CdRuta,TipoTarifa,TarifClie,TarifPago,TarifTabla,IdMneda,UndMed,NitCliente,CdNat,CdTipoVeh,CdRango,UndTarifClie,UndTarifPago,Inactivo,CdMercancia,FechaCrea,IdUsuario)
 VALUES (@pmNumero,@pmIdOrigen,@pmIdDestino,@pmCdRuta,@pmTipoTarifa,@pmTarifClie,@pmTarifPago,@pmTarifTabla,@pmIdMneda,@pmUndMed,@pmNitCliente
,@pmCdNat,@pmCdTipoVeh,@pmCdRango,@pmUndTarifClie,@pmUndTarifPago,@pmInactivo,@pmCdMercancia,@pmFechaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTraCumplido @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmModalidad VARCHAR(10),@pmDiasPlazo INT
,@pmFecPago SMALLDATETIME,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmNRadicaDoc INT,@pmIdCiaRadic CHAR(2),@pmCdCiaOfic CHAR(2),@pmFecRadic SMALLDATETIME,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraCumplido (TipDoc,Cumplido,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,Modalidad,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,Anulado,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmCumplido,@pmIdCia,@pmFecha,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmModalidad,@pmDiasPlazo,@pmFecPago,@pmTipoMargen,@pmMargenFalt,@pmUndCalcFalt,@pmTarifFaltPago,@pmTarifFaltCobro,@pmNRadicaDoc,@pmIdCiaRadic,@pmCdCiaOfic,@pmFecRadic,@pmAnulado,@pmFecDev,@pmObservacion
,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTraCumRadica @pmNumRadica INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmCdCiaRad CHAR(2),@pmDocAnexos VARCHAR(1000)
,@pmCantDoc INT,@pmFormaEnvio VARCHAR(50),@pmNomRadica VARCHAR(150),@pmCedRadica VARCHAR(16),@pmFuncionario VARCHAR(150),@pmObservacion VARCHAR(2000),@pmNumeroCum INT,@pmIdCiaCum CHAR(2)
,@pmFechaCum SMALLDATETIME,@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraCumRadica (NumRadica,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,CdCiaRad,DocAnexos,CantDoc,FormaEnvio,NomRadica,CedRadica,Funcionario,Observacion,NumeroCum,IdCiaCum,FechaCum,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmNumRadica,@pmIdCia,@pmFecha,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmCdCiaRad,@pmDocAnexos,@pmCantDoc,@pmFormaEnvio,@pmNomRadica,@pmCedRadica,@pmFuncionario,@pmObservacion
,@pmNumeroCum,@pmIdCiaCum,@pmFechaCum,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTraOrdenManif @pmTipDoc VARCHAR(3),@pmOrdPago INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmVrTotalFletes MONEY,@pmVrDescuento MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY
,@pmVrAnticipos MONEY,@pmVrFaltantes MONEY,@pmVrSeguros MONEY,@pmVrFondos MONEY,@pmVrAportes MONEY,@pmVrOtrosDctos MONEY,@pmVrImpuestos MONEY,@pmVrEstampilla MONEY,@pmVrOtrosPagos MONEY,@pmVrNeto MONEY,@pmTarifaTabla MONEY,@pmTarifaFlete MONEY,@pmUnidTarifa VARCHAR(10),@pmPesoTotal DECIMAL(14,4),@pmUnidades DECIMAL(14,4),@pmVolumen DECIMAL(14,4),@pmPesoOrigen DECIMAL(14,4)
,@pmUnidOrigen DECIMAL(14,4),@pmVolOrigen DECIMAL(14,4),@pmPesoDestino  DECIMAL(14,4),@pmUnidDestino DECIMAL(14,4),@pmVolDestino DECIMAL(14,4),@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseSeg MONEY,@pmBaseImp MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaSeg DECIMAL(14,4),@pmTarifaImp DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmTipoLiq INT
,@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmReferencia VARCHAR(50),@pmCantFaltante DECIMAL(14,4),@pmUnidadFalt VARCHAR(10),@pmTolFaltNeto DECIMAL(14,4),@pmVrConcPagos MONEY,@pmVrConcDctos MONEY,@pmVrConcFondo MONEY,@pmVrConcSeguro MONEY,@pmVrConcAporte MONEY,@pmVrConcImpuesto MONEY,@pmEdoLiqCausac INT,@pmVrRemesas MONEY,@pmVrRecCaja MONEY,@pmCdForma VARCHAR(4)
 AS 
 INSERT INTO Trn_TraOrdenManif (TipDoc,OrdPago,IdCia,Item,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla
,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CodTarRet,CodTarIca,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja,CdForma)
 VALUES (@pmTipDoc,@pmOrdPago,@pmIdCia,@pmItem,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmIdConductor,@pmIdPropietario,@pmIdPoseedor,@pmVrTotalFletes,@pmVrDescuento,@pmVrRetencion,@pmVrReteIca,@pmVrAnticipos,@pmVrFaltantes,@pmVrSeguros
,@pmVrFondos,@pmVrAportes,@pmVrOtrosDctos,@pmVrImpuestos,@pmVrEstampilla,@pmVrOtrosPagos,@pmVrNeto,@pmTarifaTabla,@pmTarifaFlete,@pmUnidTarifa,@pmPesoTotal,@pmUnidades,@pmVolumen,@pmPesoOrigen,@pmUnidOrigen,@pmVolOrigen,@pmPesoDestino,@pmUnidDestino,@pmVolDestino,@pmBaseRet,@pmBaseIca,@pmBaseSeg,@pmBaseImp,@pmTarifaRet,@pmTarifaIca,@pmTarifaSeg,@pmTarifaImp,@pmCodTarRet,@pmCodTarIca,@pmTipoLiq
,@pmTipEgr,@pmEgreso,@pmIdCiaEgr,@pmNumCheque,@pmReferencia,@pmCantFaltante,@pmUnidadFalt,@pmTolFaltNeto,@pmVrConcPagos,@pmVrConcDctos,@pmVrConcFondo,@pmVrConcSeguro,@pmVrConcAporte,@pmVrConcImpuesto,@pmEdoLiqCausac,@pmVrRemesas,@pmVrRecCaja,@pmCdForma)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTraPedAnexo @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@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),@pmPolizaEsp BIT
,@pmNumPolizaEsp VARCHAR(50),@pmNitCiaPoliza VARCHAR(16),@pmNomCiaPoliza VARCHAR(150),@pmFecVencePol SMALLDATETIME,@pmVrLimiteDesp MONEY,@pmSeguros VARCHAR(10),@pmCargue VARCHAR(20),@pmDescargue VARCHAR(20),@pmCdTipoEsc VARCHAR(4),@pmNitSIA VARCHAR(16),@pmNombreSIA VARCHAR(150),@pmContactoSIA VARCHAR(150),@pmTelContacSIA VARCHAR(30),@pmIdLocCont VARCHAR(8),@pmPatioCont VARCHAR(50),@pmCdTipoVeh VARCHAR(4)
,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmEmbAdicional VARCHAR(150),@pmTipoRuta VARCHAR(10),@pmTipoTrans VARCHAR(10),@pmEmbarque VARCHAR(10),@pmCdTipCarga VARCHAR(4),@pmDevContenedor BIT,@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmCdRutaTarif VARCHAR(4),@pmTipoServicio VARCHAR(10)
 AS 
 INSERT INTO Trn_TraPedAnexo (TipDoc,Pedido,IdCia,NomCliente,NomRemite,NomDestino,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,PolizaEsp,NumPolizaEsp,NitCiaPoliza,NomCiaPoliza,FecVencePol,VrLimiteDesp,Seguros,Cargue,Descargue,CdTipoEsc,NitSIA,NombreSIA,ContactoSIA,TelContacSIA
,TipoRuta,TipoTrans,Embarque,CdTipCarga,DevContenedor,IdLocCont,PatioCont,CdTipoVeh,MargenFalt,TipoMargen,UndCalcFalt,TarifFaltPago,TarifFaltCobro,EmbAdicional,CdRutaTarif,TipoServicio)
 VALUES (@pmTipDoc,@pmPedido,@pmIdCia,@pmNomCliente,@pmNomRemite,@pmNomDestino,@pmLugarCargue,@pmLugarDescargue,@pmNomContacto,@pmTelContacto,@pmemlContacto,@pmContacDestino,@pmTelContacDest,@pmemlContacDest,@pmPolizaEsp,@pmNumPolizaEsp,@pmNitCiaPoliza,@pmNomCiaPoliza,@pmFecVencePol,@pmVrLimiteDesp,@pmSeguros,@pmCargue,@pmDescargue,@pmCdTipoEsc,@pmNitSIA,@pmNombreSIA,@pmContactoSIA,@pmTelContacSIA
,@pmTipoRuta,@pmTipoTrans,@pmEmbarque,@pmCdTipCarga,@pmDevContenedor, @pmIdLocCont,@pmPatioCont,@pmCdTipoVeh,@pmMargenFalt,@pmTipoMargen,@pmUndCalcFalt,@pmTarifFaltPago,@pmTarifFaltCobro,@pmEmbAdicional,@pmCdRutaTarif,@pmTipoServicio)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTarifasFletes @pmNumero INT
 AS 
 SELECT Numero,IdOrigen,IdDestino,CdRuta,TipoTarifa,TarifClie,TarifPago,TarifTabla,IdMneda,UndMed
,NitCliente,CdNat,CdTipoVeh,CdRango,UndTarifClie,UndTarifPago,Inactivo,CdMercancia,FechaCrea,IdUsuario
 FROM TarifasFletes
 WHERE  Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTarifasFletesLta @pmTipoTarifa VARCHAR(10)=Null,@pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null
,@pmNitCliente VARCHAR(16)=Null,@pmInactivo BIT=Null,@pmCdRuta VARCHAR(4)=Null
 AS 
 SELECT Numero,IdOrigen,O.Localidad AS CiuOrigen,IdDestino,D.Localidad AS CiuDestino,CdRuta,TarifClie,TarifPago,TarifTabla,IdMneda,T.UndMed AS Und_Med,Unidad
,NitCliente,RazonSocial,CdNat,Natlzaprod,CdTipoVeh,TipoVehiculo,CdRango,DescripRango,T.Inactivo AS Inactvo,UndTarifClie,UndTarifPago,CdMercancia,FechaCrea,T.IdUsuario AS CdUsuario,Usuario
,O.IdDep AS CdDep,DPO.Departamento AS DptoOrigen,D.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,TipoTarifa
 FROM TarifasFletes AS T INNER JOIN Localidades AS O ON T.IdOrigen=O.IdLocal INNER JOIN Localidades AS D ON T.IdDestino=D.IdLocal
 INNER JOIN Departamentos AS DPO ON O.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON D.IdDep=DPD.IdDep
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 LEFT JOIN Terceros AS TC ON T.NitCliente=TC.IdTercero LEFT JOIN Sys_Um AS UM ON T.UndMed=UM.UndMed 
 LEFT JOIN TiposNat AS N ON T.CdNat=N.IdNat LEFT JOIN TiposVeh AS TV ON T.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN RangosPeso AS R ON T.CdRango=R.IdRango
 WHERE TipoTarifa LIKE ISNULL(@pmTipoTarifa,'%') AND  IdOrigen LIKE ISNULL(@pmIdOrigen,'%')
 AND IdDestino LIKE ISNULL(@pmIdDestino,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%')
 AND CdRuta LIKE ISNULL(@pmCdRuta,'%')
 AND  (T.Inactivo=ISNULL(@pmInactivo,0) or T.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY O.Localidad,D.Localidad,Numero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTraCumplido @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Cumplido,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,Anulado,FecDev,Observacion,IdEstado
,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraCumplido
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraCumplidoFmt @pmTipDoc VARCHAR(3),@pmCumplidoIni INT,@pmCumplidoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT CU.TipDoc AS TipCum,TipoDoc,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct
,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario,Leyenda,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic
,M.Fecha AS FecManif,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,R.Ruta AS DescRuta
,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS Observ
--detalle de cumplidos
,D.Item AS DetItem,TipRem,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.Cantidad AS Cant,D.PesoNeto AS PesoCump,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolCump,D.UndVol AS Und_Vol
,D.Cases AS CasesCump,D.Cajas AS CajasCump,D.Palets AS PaletsCump,D.TarifClie AS Tarif_Clie,D.TarifPago AS Tarif_Pago,UndTarifClie,D.UndTarifPago AS UndTarifPag,CantCargue,PesoCargue,VolCargue,CasesCargue,CajasCargue,PaletsCargue
,EstadoCump,D.Remision AS NumRemision,D.DocCliente AS CumDocClie,D.Referencia1 AS CumRef1,D.Referencia2 AS CumRef2,D.Referencia3 AS CumRef3,D.Detalle AS CumDetalle
,IdMercancia,DescripMcias,RM.Cantidad AS RemCant,RM.PesoNeto AS RemPeso,NitRemite,Remitente,NitDestntario,Destinatario
--Datos del 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
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON CU.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraCumRemesas AS D ON CU.TipDoc=D.TipDoc  AND CU.Cumplido=D.Cumplido AND CU.IdCia=D.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.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 R ON M.IdRuta=R.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 Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON CU.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN Trn_TraRemMcias AS RM ON D.TipRem=RM.TipDoc  AND D.Remesa=RM.NumOrden AND D.IdCiaRem=RM.IdCia  AND D.ItemRem=RM.Item
 WHERE CU.TipDoc=@pmTipDoc AND CU.Cumplido BETWEEN @pmCumplidoIni AND @pmCumplidoFin AND CU.IdCia=@pmIdCia
 ORDER BY CU.Cumplido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraCumplidoLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT C.Cumplido AS NumCumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FecCumplido,C.Manifiesto AS NumManif,IdCiaMuc,M.Fecha AS FecManif,C.IdVehiculo AS PlacaVeh,Modalidad
,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic
,C.Anulado AS CumAnulado,C.FecDev AS FechAnulado,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaAct,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraCumplido AS C INNER JOIN Trn_TraManifiesto AS M ON C.TipMuc=M.TipDoc  AND C.Manifiesto=M.Manifiesto AND C.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero  INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 WHERE C.TipDoc=@pmTipDoc  AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY C.IdCia,C.Cumplido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraCumplidoRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT CU.TipDoc AS TipCum,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario
,M.Fecha AS FecManif,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,R.Ruta AS DescRuta
,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS MucObserv
--Datos del vehiculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 INNER JOIN 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 R ON M.IdRuta=R.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 Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON CU.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND  CU.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
  ORDER BY CU.IdCia,CU.Cumplido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraCumplidoRelDet @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT CU.TipDoc AS TipCum,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario
,M.Fecha AS FecManif,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,R.Ruta AS DescRuta
,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS MucObserv
--detalle de cumplidos
,D.Item AS DetItem,TipRem,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.Cantidad AS Cant,D.PesoNeto AS PesoCump,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolCump,D.UndVol AS Und_Vol
,D.Cases AS CasesCump,D.Cajas AS CajasCump,D.Palets AS PaletsCump,D.TarifClie AS Tarif_Clie,D.TarifPago AS Tarif_Pago,UndTarifClie,D.UndTarifPago AS UndTarifPag,CantCargue,PesoCargue,VolCargue,CasesCargue,CajasCargue,PaletsCargue
,EstadoCump,D.Remision AS NumRemision,D.DocCliente AS CumDocClie,D.Referencia1 AS CumRef1,D.Referencia2 AS CumRef2,D.Referencia3 AS CumRef3,D.Detalle AS CumDetalle
,IdMercancia,DescripMcias,RM.Cantidad AS RemCant,RM.PesoNeto AS RemPeso,NitRemite,Remitente,NitDestntario,Destinatario
--Datos del vehiculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 INNER JOIN 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 R ON M.IdRuta=R.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 Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON CU.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraCumRemesas AS D ON CU.TipDoc=D.TipDoc  AND CU.Cumplido=D.Cumplido AND CU.IdCia=D.IdCia
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN Trn_TraRemMcias AS RM ON D.TipRem=RM.TipDoc  AND D.Remesa=RM.NumOrden AND D.IdCiaRem=RM.IdCia  AND D.ItemRem=RM.Item
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND  CU.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
  ORDER BY CU.IdCia,CU.Cumplido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTraCumRadica @pmNumRadica INT,@pmIdCia CHAR(2)
 AS 
 SELECT NumRadica,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,CdCiaRad,DocAnexos,CantDoc,FormaEnvio
,NomRadica,CedRadica,Funcionario,Observacion,NumeroCum,IdCiaCum,FechaCum,TimeSys,IdCiaCrea,IdUsuario
 FROM Trn_TraCumRadica
 WHERE NumRadica=@pmNumRadica AND IdCia=@pmIdCia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraCumRadicaFmt @pmNumRadicaIni INT,@pmNumRadicaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT NumRadica,R.IdCia AS CdCia,CN.Compania AS NombreCia,R.Fecha AS FecRadica,R.TipMuc AS TipManif,R.Manifiesto AS NumManif,R.IdCiaMuc AS CdCiaManif,CU.IdVehiculo AS PlacaVeh
,CdCiaRad,CR.Compania AS CiaRadica,DocAnexos,CantDoc,FormaEnvio,NomRadica,CedRadica,Funcionario,R.Observacion AS Observ,NumeroCum,IdCiaCum,CU.Fecha AS FecCump,CU.Observacion AS ObservCump
--datos del manifiesto
,M.Fecha AS FecManif,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.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario
,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,R.TimeSys AS FechaCrea,R.IdUsuario AS CdUsuario,Usuario
,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
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.Descripcion AS VehDescripcion
 FROM Trn_TraCumRadica AS R  INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia INNER JOIN Companias AS CR ON R.CdCiaRad=CR.IdCia
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON R.TipMuc=M.TipDoc AND R.Manifiesto=M.Manifiesto AND R.IdCiaMuc=M.IdCia
 INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero
 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 Localidades AS L ON T.IdLocal=L.IdLocal 
 LEFT JOIN Trn_TraCumplido AS CU ON R.NumeroCum=CU.Cumplido AND R.IdCiaCum=CU.IdCia
 LEFT JOIN Vehiculos AS V ON CU.IdVehiculo=V.IdVehiculo  LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 WHERE NumRadica BETWEEN @pmNumRadicaIni AND @pmNumRadicaFin AND R.IdCia=@pmIdCia


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraDevOdpFmt @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipoOdp,D.OrdPago AS NumOdp,IdCiaDoc,FecDoc
,D.IdVehiculo AS PlacaVeh,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,CxPagar,ModdDev
,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,Leyenda
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos
,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino
,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos
,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,CdForma,EdoLiqCausac
,M.Fecha AS FecManif,FecDespacho,FecEntrega,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,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--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
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevOdp  AS D INNER JOIN Trn_TraOrdenManif AS O ON D.TipDoc=O.TipDoc AND D.OrdPago=O.OrdPago AND D.IdCiaDoc=O.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.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 R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON D.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 NE ON  V.NitEmpresa=NE.IdTercero  LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 WHERE D.TipDev=@pmTipDev AND D.Devolucion  BETWEEN @pmDevolucionIni AND @pmDevolucionFin  AND D.IdCia=@pmIdCia

 ORDER BY D.Devolucion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraDevOdpLta @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT  Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipOdp,D.OrdPago AS NumOrden,IdCiaDoc,FecDoc,TipMuc,Manifiesto,IdCiaMuc,D.IdVehiculo AS PlacaVeh,NumVeh
,D.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,D.CxPagar AS EsCxpagar,FechaVence,LiqRemesas,TipoLiq,VrTotalFletes,VrDescuento
,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla,TarifaFlete
,UnidTarifa,PesoTotal,Unidades,Volumen,VrRemesas,VrRecCaja,CdForma,NumCheque,Referencia,ModdDev,D.TipCom AS Tip_Comp,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaCom,D.Observacion AS Observ
,D.IdEstado AS  CdEstado,Estado,D.TimeSys AS FechaCrea,D.FecUpdate AS FechaAct,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraDevOdp AS D INNER JOIN  Trn_TraOrdenPago AS O ON D.TipDoc=O.TipDoc  AND D.OrdPago=O.OrdPago AND D.IdCiaDoc=O.IdCia
 INNER JOIN Trn_TraOrdenManif AS M ON O.TipDoc=M.TipDoc AND O.OrdPago=M.OrdPago AND O.IdCia=M.IdCia
 INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN Terceros AS NP ON D.IdPoseedor=NP.IdTercero 
 INNER JOIN Terceros AS NC ON D.IdConductor=NC.IdTercero
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 WHERE TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY  D.IdCia,D.Devolucion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraDevOdpRel @pmTipDev 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 TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipoOdp,D.OrdPago AS NumOdp,IdCiaDoc,FecDoc
,D.IdVehiculo AS PlacaVeh,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,CxPagar,ModdDev
,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos
,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino
,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos
,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,CdForma,EdoLiqCausac
,M.Fecha AS FecManif,FecDespacho,FecEntrega,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,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--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
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevOdp  AS D INNER JOIN Trn_TraOrdenManif AS O ON D.TipDoc=O.TipDoc AND D.OrdPago=O.OrdPago AND D.IdCiaDoc=O.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.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 R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON D.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 NE ON  V.NitEmpresa=NE.IdTercero  LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 WHERE D.TipDev=@pmTipDev  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY  D.IdCia,D.Devolucion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTraOrdenManif @pmTipDoc VARCHAR(3),@pmOrdPago INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,OrdPago,IdCia,Item,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdConductor,IdPropietario,IdPoseedor,VrTotalFletes,VrDescuento,VrRetencion
,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades
,Volumen,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CodTarRet,CodTarIca
,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos
,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja,CdForma
 FROM Trn_TraOrdenManif
 WHERE TipDoc=@pmTipDoc AND OrdPago=@pmOrdPago AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraOrdenPagoFmt @pmTipDoc VARCHAR(3),@pmOrdPagoIni INT,@pmOrdPagoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT OP.TipDoc AS TipoOdp,TipoDoc,OP.OrdPago AS NumOdp,OP.IdCia AS CdCia,Compania,OP.Fecha AS FechaOdp,OP.IdConcepto AS CdConcepto,Concepto,Modalidad,LiqRemesas,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence
,TipCom,TipoCom,Comprobante,IdCiaCom,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdEstado AS CdEstado,Estado,OP.TimeSys AS FechaCrea,OP.FecUpdate AS FechaAct,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario,Leyenda
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja,CdForma
,M.Fecha AS FecManif,FecDespacho,FecEntrega,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,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
,TipRem,ORM.Remesa AS NumRemesa,ORM.IdCiaRem AS CdCiaRem,ItemRem,ORM.Cantidad AS Cant,PesoNeto,ORM.UndMed AS CdUMed,UMP.Unidad AS UmPeso,ORM.Volumen AS Volmen,UndVol,Cases,Cajas,Palets,TarifPago,TarifTabla,UndTarifa,Detalle
--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
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON OP.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON OP.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 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 R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.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 NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Trn_TraOrdenRemesas AS ORM ON   O.TipDoc=ORM.TipDoc AND   O.OrdPago=ORM.OrdPago AND  O.IdCia=ORM.IdCia 
 AND  O.TipMuc=ORM.TipMuc AND  O.Manifiesto=ORM.Manifiesto AND  O.IdCiaMuc=ORM.IdCiaMuc
 LEFT JOIN Sys_Um AS UMP ON ORM.UndMed=UMP.UndMed  LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 WHERE OP.TipDoc=@pmTipDoc AND OP.OrdPago BETWEEN @pmOrdPagoIni AND @pmOrdPagoFin AND OP.IdCia=@pmIdCia
 ORDER BY OP.OrdPago
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraOrdenPagoLta @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 O.OrdPago AS NumOrden,O.IdCia AS CdCia,Compania,Fecha,TipMuc,Manifiesto,IdCiaMuc,M.IdVehiculo AS PlacaVeh,NumVeh,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,LiqRemesas,TipoLiq,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos
,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,PesoOrigen,UnidOrigen,VolOrigen
,PesoDestino,UnidDestino,VolDestino,BaseRet,BaseIca,BaseSeg,BaseImp,TarifaRet,TarifaIca,TarifaSeg,TarifaImp,CxPagar,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,FechaVence
,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,CdForma
,O.IdConcepto AS CdConcepto,Concepto,Modalidad,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraOrdenPago AS O INNER JOIN Trn_TraOrdenManif AS M ON O.TipDoc=M.TipDoc AND O.OrdPago=M.OrdPago AND O.IdCia=M.IdCia
 INNER JOIN Companias AS CI ON O.IdCia=CI.IdCia INNER JOIN Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo   INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero  INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 LEFT JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo
 WHERE O.TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND  M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 ORDER BY O.IdCia,O.OrdPago
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraOrdenPagoNet @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
SELECT OP.TipDoc AS CdTipoDoc,TipoDoc,OP.OrdPago AS NumOrden,OP.IdCia AS CdCia,Compania,OP.Fecha AS FecOrden,OP.IdConcepto AS CdConcepto,Concepto,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif
,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence,TipCom,TipoCom,Comprobante,IdCiaCom,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario
,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja 
,M.Fecha AS FecManif,FecDespacho,FecEntrega,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,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--Datos del vehiculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,Modelo,Config,TipoAfil,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Sys_TiposDoc AS TD ON OP.TipDoc=TD.IdDoc
 INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 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 R ON M.IdRuta=R.IdRuta INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca  LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 WHERE  OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND OP.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
UNION ALL
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,Concepto,O.TipMuc,O.Manifiesto,O.IdCiaMuc,D.IdVehiculo,NumVeh
,O.IdPropietario,NP.RazonSocial,D.IdPoseedor,T.RazonSocial,D.IdConductor,CDT.RazonSocial,VrTotalFletes,O.VrDescuento,O.VrRetencion,O.VrReteIca,VrAnticipos
,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto
,TarifaTabla,O.TarifaFlete,UnidTarifa,O.PesoTotal,Unidades,O.Volumen,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp
,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp,D.CxPagar,0,'','','',FecDoc,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,0,0,Null,D.Observacion,D.IdCiaCrea,D.IdUsuario,Usuario
,TipoLiq,D.TipDoc,D.OrdPago,D.IdCiaDoc,'',Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,EdoLiqCausac,VrRemesas,VrRecCaja 
,M.Fecha,FecDespacho,FecEntrega,M.IdOrigen,CO.Localidad,CO.IdDep,DPO.Departamento,M.IdDestino,CD.Localidad,CD.IdDep,DPD.Departamento,M.IdRuta,R.Ruta,nRemolque,TipoAfiVehic,IdLocFletes
,CF.Localidad,M.Observacion
--Datos del vehiculo
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,V.IdTipoVeh,TipoVehiculo,V.IdMarca,MV.Marca,Modelo,Config,TipoAfil,V.Descripcion
 FROM Trn_TraDevOdp AS D INNER JOIN Trn_TraOrdenManif AS O ON D.TipDoc=O.TipDoc AND D.OrdPago=O.OrdPago AND D.IdCiaDoc=O.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.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 R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca  LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraOrdenPagoRel @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 OP.TipDoc AS TipoOdp,OP.OrdPago AS NumOdp,OP.IdCia AS CdCia,Compania,OP.Fecha AS FechaOdp,OP.IdConcepto AS CdConcepto,Concepto,Modalidad,LiqRemesas,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence
,TipCom,TipoCom,Comprobante,IdCiaCom,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdEstado AS CdEstado,OP.TimeSys AS FechaCrea,OP.FecUpdate AS FechaAct,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,CdForma,EdoLiqCausac
,M.Fecha AS FecManif,FecDespacho,FecEntrega,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,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--Datos del vehiculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 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 R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.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 NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 WHERE OP.TipDoc=@pmTipDoc AND OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND OP.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND OP.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND O.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY OP.IdCia,OP.OrdPago
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraOrdenPagoRelDet @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 OP.TipDoc AS TipoOdp,OP.OrdPago AS NumOdp,OP.IdCia AS CdCia,Compania,OP.Fecha AS FechaOdp,OP.IdConcepto AS CdConcepto,Concepto,Modalidad,LiqRemesas,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence
,TipCom,TipoCom,Comprobante,IdCiaCom,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.Observacion AS Observ,OP.IdEstado AS CdEstado,OP.TimeSys AS FechaCrea,OP.FecUpdate AS FechaAct,OP.IdCiaCrea AS CdCiaCrea,OP.IdUsuario AS CdUsuario,Usuario
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,O.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp
,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,EdoLiqCausac
,M.Fecha AS FecManif,FecDespacho,FecEntrega,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,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--detalles
,TipRem,ORM.Remesa AS NumRemesa,ORM.IdCiaRem AS CdCiaRem,ItemRem,ORM.Cantidad AS Cant,PesoNeto,ORM.UndMed AS CdUMed,UMP.Unidad AS UmPeso,ORM.Volumen AS Volmen,UndVol,Cases,Cajas,Palets,TarifPago,TarifTabla,UndTarifa,Detalle
--Datos del vehiculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenPago AS OP INNER JOIN Trn_TraOrdenManif AS O ON OP.TipDoc=O.TipDoc AND OP.OrdPago=O.OrdPago AND OP.IdCia=O.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Companias AS CN ON OP.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON OP.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON OP.IdConcepto=C.IdConcepto INNER JOIN Plazos AS PZ ON OP.IdPlazo=PZ.IdPlazo
 INNER JOIN Terceros AS  CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS T ON O.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NP ON O.IdPropietario=NP.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 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 R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON O.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 NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TiposCom AS TCM ON OP.TipCom=TCM.IdCom 
 LEFT JOIN Trn_TraOrdenRemesas AS ORM ON   O.TipDoc=ORM.TipDoc AND   O.OrdPago=ORM.OrdPago AND  O.IdCia=ORM.IdCia 
 AND  O.TipMuc=ORM.TipMuc AND  O.Manifiesto=ORM.Manifiesto AND  O.IdCiaMuc=ORM.IdCiaMuc
 LEFT JOIN Sys_Um AS UMP ON ORM.UndMed=UMP.UndMed
 WHERE OP.TipDoc=@pmTipDoc AND OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND OP.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND OP.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND O.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY OP.IdCia,OP.OrdPago
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraPedAnexo @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Pedido,IdCia,NomCliente,NomRemite,NomDestino,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,PolizaEsp,NumPolizaEsp
,NitCiaPoliza,NomCiaPoliza,FecVencePol,VrLimiteDesp,Seguros,Cargue,Descargue,CdTipoEsc,NitSIA,NombreSIA,ContactoSIA,TelContacSIA,IdLocCont,PatioCont,CdTipoVeh,TipoMargen,MargenFalt,EmbAdicional
,TipoRuta,TipoTrans,Embarque,CdTipCarga,DevContenedor,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CdRutaTarif,TipoServicio
 FROM Trn_TraPedAnexo
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraPedidoFmt @pmTipDoc VARCHAR(3),@pmPedidoIni INT,@pmPedidoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.TipDoc AS CdTipDoc,TipoDoc,P.Pedido AS NumPedido,P.IdCia AS CdCia,Compania,Fecha,FechaVence,FecDespacho,FecEntrega,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli,P.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
,LugarCargue,LugarDescargue,P.IdVend AS CdVend,VN.RazonSocial AS Vendedor,Modalidad,Vigencia,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos
,P.VrDeclarado AS ValDeclarado,P.VrSeguro AS ValSeguro,P.Cantidad AS CantTotal,CantDesp,IdMneda,VrTasa,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,TipOdc,OCargue,IdCiaOdc,FechaOdc,TipRem,Remesa,IdCiaRem,FechaRem,TipFac,Factura,IdCiaFac,FechaFac
,Anulado,FecDev,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS CdUsuario,Usuario,Leyenda
--datos de anexo
,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,PolizaEsp,NumPolizaEsp,NitCiaPoliza,NomCiaPoliza,FecVencePol,VrLimiteDesp,Seguros,Cargue,Descargue,CdTipoEsc,NitSIA,NombreSIA,ContactoSIA,TelContacSIA
,IdLocCont,LC.Localidad AS CiudadCont,PatioCont,CdTipoVeh,TipoVehiculo,TipoMargen,MargenFalt,EmbAdicional,TipoRuta,TipoTrans,Embarque,CdTipCarga,TipoCarga,DevContenedor,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CdRutaTarif,TipoServicio
--datos de mcias
,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUm,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,Cases,Cajas,Palets,Remitente,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,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,Referencia1,Referencia2,Contenedor1,Contenedor2
,NitRemite,NitDestntario,UndTarifa,UndTarifPago,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.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.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
 FROM  Trn_TraPedido AS P INNER JOIN Trn_TraPedAnexo AS PA ON P.TipDoc=PA.TipDoc  AND P.Pedido=PA.Pedido AND P.IdCia=PA.IdCia
 INNER JOIN Trn_TraPedMcias AS D ON P.TipDoc=D.TipDoc AND P.Pedido=D.Pedido AND P.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero 
 INNER JOIN TercCliente AS CLI ON P.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 Terceros AS VN ON P.IdVend=VN.IdTercero  INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS CO ON P.IdLocOrigen=CO.IdLocal INNER JOIN Localidades AS CD ON P.IdLocDestino=CD.IdLocal
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON P.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 
 LEFT JOIN Agencias AS A ON P.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LC ON PA.IdLocCont=LC.IdLocal LEFT JOIN TiposVeh AS TV ON PA.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN TiposCarga AS TCG ON PA.CdTipCarga=TCG.IdTipCarga
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango
 WHERE P.TipDoc=@pmTipDoc AND P.Pedido BETWEEN @pmPedidoIni AND @pmPedidoFin AND P.IdCia=@pmIdCia
 ORDER BY P.Pedido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraPedidoRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT P.TipDoc AS CdTipDoc,P.Pedido AS NumPedido,P.IdCia AS CdCia,Compania,Fecha,FechaVence,FecDespacho,FecEntrega,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli,P.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
,LugarCargue,LugarDescargue,P.IdVend AS CdVend,VN.RazonSocial AS Vendedor,Modalidad,Vigencia,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos
,P.VrDeclarado AS ValDeclarado,P.VrSeguro AS ValSeguro,P.Cantidad AS CantTotal,CantDesp,IdMneda,VrTasa,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,TipOdc,OCargue,IdCiaOdc,FechaOdc,TipRem,Remesa,IdCiaRem,FechaRem,TipFac,Factura,IdCiaFac,FechaFac
,Anulado,FecDev,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS CdUsuario,Usuario
--datos de anexo
,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,PolizaEsp,NumPolizaEsp,NitCiaPoliza,NomCiaPoliza,FecVencePol,VrLimiteDesp,Seguros,Cargue,Descargue,CdTipoEsc,NitSIA,NombreSIA,ContactoSIA,TelContacSIA
,IdLocCont,LC.Localidad AS CiudadCont,PatioCont,CdTipoVeh,TipoVehiculo,TipoMargen,MargenFalt,EmbAdicional,TipoRuta,TipoTrans,TipoServicio,Embarque,CdTipCarga,TipoCarga,DevContenedor,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CdRutaTarif
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,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.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
 FROM  Trn_TraPedido AS P INNER JOIN Trn_TraPedAnexo AS PA ON P.TipDoc=PA.TipDoc  AND P.Pedido=PA.Pedido AND P.IdCia=PA.IdCia
 INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero 
 INNER JOIN TercCliente AS CLI ON P.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 Terceros AS VN ON P.IdVend=VN.IdTercero  
 INNER JOIN Localidades AS CO ON P.IdLocOrigen=CO.IdLocal  INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON P.IdLocDestino=CD.IdLocal  INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Agencias AS A ON P.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LC ON PA.IdLocCont=LC.IdLocal LEFT JOIN TiposVeh AS TV ON PA.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN TiposCarga AS TCG ON PA.CdTipCarga=TCG.IdTipCarga
 WHERE P.TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND  @pmFechaFin AND   P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdCliente  LIKE ISNULL(@pmIdCliente,'%') AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY P.IdCia,P.Pedido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraPedidoRelCon @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT P.TipDoc AS CdTipDoc,P.Pedido AS NumPedido,P.IdCia AS CdCia,Compania,Fecha,FechaVence,FecDespacho,FecEntrega,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli,P.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
,LugarCargue,LugarDescargue,P.IdVend AS CdVend,VN.RazonSocial AS Vendedor,Modalidad,Vigencia,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos
,P.VrDeclarado AS ValDeclarado,P.VrSeguro AS ValSeguro,P.Cantidad AS CantTotal,CantDesp,IdMneda,VrTasa,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,TipOdc,OCargue,IdCiaOdc,FechaOdc,TipRem,Remesa,IdCiaRem,FechaRem,TipFac,Factura,IdCiaFac,FechaFac
,Anulado,FecDev,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS CdUsuario,Usuario
--datos de anexo
,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,PolizaEsp,NumPolizaEsp,NitCiaPoliza,NomCiaPoliza,FecVencePol,VrLimiteDesp,Seguros,Cargue,Descargue,NitSIA,NombreSIA,ContactoSIA,TelContacSIA
,IdLocCont,LC.Localidad AS CiudadCont,PatioCont,CdTipoVeh,TipoVehiculo,TipoMargen,MargenFalt,EmbAdicional,TipoRuta,TipoTrans,Embarque,CdTipCarga,TipoCarga,TipoServicio,DevContenedor,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CdRutaTarif
--conceptos
,Item,Descripcion,Tarifa,TipoConc,RubroConcep,CT.Cantidad AS Cant,VrUnitario,TarifIva,CT.IdConcepto AS CdConcTrans,Concepto,CdCuenta,NomCuenta,NitTercero,NTE.RazonSocial AS NomTercero,CT.CdTipoEsc AS CodTipoEsc,TipoEscolta,FechaNov,VrBase,TipoTarif,RefConc
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,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.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
 FROM  Trn_TraPedido AS P INNER JOIN Trn_TraPedAnexo AS PA ON P.TipDoc=PA.TipDoc  AND P.Pedido=PA.Pedido AND P.IdCia=PA.IdCia
 INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero 
 INNER JOIN TercCliente AS CLI ON P.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 Terceros AS VN ON P.IdVend=VN.IdTercero  
 INNER JOIN Localidades AS CO ON P.IdLocOrigen=CO.IdLocal  INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON P.IdLocDestino=CD.IdLocal  INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Agencias AS A ON P.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LC ON PA.IdLocCont=LC.IdLocal LEFT JOIN TiposVeh AS TV ON PA.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN TiposCarga AS TCG ON PA.CdTipCarga=TCG.IdTipCarga
 LEFT JOIN  Trn_TraConceptos AS CT ON P.TipDoc=CT.TipDoc AND P.Pedido=CT.Documento AND P.IdCia=CT.IdCia
 LEFT JOIN ConcDiversos AS CV ON CT.IdConcepto=CV.IdConcepto LEFT JOIN Puc AS PU ON CT.CdCuenta=PU.IdCuenta
 LEFT JOIN TiposEsc AS TE ON CT.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS NTE ON CT.NitTercero=NTE.IdTercero
 WHERE P.TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND  @pmFechaFin AND   P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdCliente  LIKE ISNULL(@pmIdCliente,'%') AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY P.IdCia,P.Pedido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraPedidoRelDet  @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null
,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT P.TipDoc AS CdTipDoc,P.Pedido AS NumPedido,P.IdCia AS CdCia,Compania,Fecha,FechaVence,FecDespacho,FecEntrega,IdCliente,NomCliente,T.RazonSocial AS RazonSocialCli,P.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
,LugarCargue,LugarDescargue,P.IdVend AS CdVend,VN.RazonSocial AS Vendedor,Modalidad,Vigencia,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos
,P.VrDeclarado AS ValDeclarado,P.VrSeguro AS ValSeguro,P.Cantidad AS CantTotal,CantDesp,IdMneda,VrTasa,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,TipOdc,OCargue,IdCiaOdc,FechaOdc,TipRem,Remesa,IdCiaRem,FechaRem,TipFac,Factura,IdCiaFac,FechaFac
,Anulado,FecDev,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS CdUsuario,Usuario
--detalles
,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUm,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,Cases,Cajas,Palets,Remitente,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen
,Destinatario,DirDestino,IdDestino,LD.Localidad AS CiudadDestino,TarifClie,TarifPago,TarifTabla,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,TarifSeguro,Referencia1,Referencia2,Contenedor1,Contenedor2
,NitRemite,NitDestntario,UndTarifa,UndTarifPago,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 de anexo
,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,PolizaEsp,NumPolizaEsp,NitCiaPoliza,NomCiaPoliza,FecVencePol,VrLimiteDesp,Seguros,Cargue,Descargue,CdTipoEsc,NitSIA,NombreSIA,ContactoSIA,TelContacSIA
,IdLocCont,LC.Localidad AS CiudadCont,PatioCont,CdTipoVeh,TipoVehiculo,TipoMargen,MargenFalt,EmbAdicional,TipoRuta,TipoTrans,TipoServicio,Embarque,CdTipCarga,TipoCarga,DevContenedor,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CdRutaTarif
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,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.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
 FROM  Trn_TraPedido AS P INNER JOIN Trn_TraPedAnexo AS PA ON P.TipDoc=PA.TipDoc  AND P.Pedido=PA.Pedido AND P.IdCia=PA.IdCia
 INNER JOIN Trn_TraPedMcias AS D ON P.TipDoc=D.TipDoc AND P.Pedido=D.Pedido AND P.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero 
 INNER JOIN TercCliente AS CLI ON P.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 Terceros AS VN ON P.IdVend=VN.IdTercero  
 INNER JOIN Localidades AS CO ON P.IdLocOrigen=CO.IdLocal  INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON P.IdLocDestino=CD.IdLocal  INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal  INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal 
 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 
 LEFT JOIN Agencias AS A ON P.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LC ON PA.IdLocCont=LC.IdLocal LEFT JOIN TiposVeh AS TV ON PA.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN TiposCarga AS TCG ON PA.CdTipCarga=TCG.IdTipCarga
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango
 WHERE P.TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND  @pmFechaFin AND   P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdCliente  LIKE ISNULL(@pmIdCliente,'%') AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY P.IdCia,P.Pedido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraPedidoRem @pmTipRem VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT P.TipDoc AS CdTipPed,P.Pedido AS NumPedido,P.IdCia AS CdCia,Compania,Fecha,FechaVence,FecDespacho,FecEntrega,T.TipoId AS TercTipId,IdCliente,T.Dv AS TercDv,T.RazonSocial AS NomCliente
,P.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,P.IdVend AS CdVend,VN.RazonSocial AS Vendedor,Modalidad,Vigencia
,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos
,P.VrDeclarado AS ValDeclarado,P.VrSeguro AS ValSeguro,P.Cantidad AS CantTotal,CantDesp,NumAprob,IdCiaApr,FecAprob
,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,P.Anulado AS PedAnulado,TimeSys,P.IdUsuario AS CdUsuario,Usuario
,PolizaEsp,NumPolizaEsp,PA.NitCiaPoliza AS NitCiaPlza,PA.NomCiaPoliza AS NomCiaPlza,PA.FecVencePol AS FecVcePlza,VrLimiteDesp,Seguros,Cargue,Descargue
,IdLocCont,LC.Localidad AS CiudadCont,PatioCont,CdTipoVeh,TV.TipoVehiculo AS TipoVehic,TipoMargen,MargenFalt,EmbAdicional,PA.TipoRuta AS TipRutaPed
,TipoTrans,Embarque,CdTipCarga,TipoCarga,DevContenedor,UndCalcFalt,TarifFaltPago,TarifFaltCobro
,Item,IdMercancia,DescripMcias,D.Cantidad AS CantPed,PesoNeto,D.UndMed AS CdUm,UMP.Unidad AS UmPeso,TarifClie,D.UndTarifa AS UndTarifClie,TarifPago,D.UndTarifPago AS UndTarif_Pago
,TarifTabla,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
,Referencia1,Referencia2,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,Cases,Cajas,Palets,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,TarifSeguro
,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
--remesas
,CdTipRem,NumRemesa,CdCiaRem,NomCiaRem,FecRemesa,FecDespRem,FecEntgaRem,PlacaVeh,NumVeh,CdTipVeh,RM.TipoVehiculo AS TipoVehicRem,CedConductor,NomConductor
,nRemolque,TipoAfiVehic,TipoRemesa,NumManif,IdCiaManif,EstCumplido,EstFactura,ManifMintrans,RemMintrans,RM.TipoRuta AS TipoRutaRem,TipoMintrans
,CantRemesa,PesoNetoRem,CdUmPeso,UmPeso,CasesRem,CajasRem,PaletsRem,TarifClieRem,UndTarifRem,TarifPagoRem,TarifTablaRem,UndTarifPagoRem
,NitRemteRem,NomRemteRem,DirOrigenRem,CdCiuOrigRem,CiudadOrigen,CodDepOrigen,DptoOrigen
,NitDtnatario,NomDestnatario,DirDestinoRem,CdDestinoRem,CiudadDestino,CodDepDestino,DptoDestino
,Remision,DocCliente,Refencia1,Refencia2,Refencia3,VlmenRem,CdUndVol,DetVrDeclarado,DetVrSeguro,TarifSegRem
,Cumplido,IdCiaCump,FechaCump,RM.TipFac AS TipFact,RM.Factura AS NumFact,RM.IdCiaFac AS CdCiaFact,RM.FechaFac AS FecFact,TarifClieFac,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp
 FROM  Trn_TraPedido AS P INNER JOIN Trn_TraPedAnexo AS PA ON P.TipDoc=PA.TipDoc  AND P.Pedido=PA.Pedido AND P.IdCia=PA.IdCia
 INNER JOIN Trn_TraPedMcias AS D ON P.TipDoc=D.TipDoc AND P.Pedido=D.Pedido AND P.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero 
 INNER JOIN TercCliente AS CLI ON P.IdCliente=CLI.IdClie  INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero  
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON P.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 
 LEFT JOIN Agencias AS A ON P.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LC ON PA.IdLocCont=LC.IdLocal LEFT JOIN TiposVeh AS TV ON PA.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN TiposCarga AS TCG ON PA.CdTipCarga=TCG.IdTipCarga
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango
--consultas de remesas -detalles
 LEFT JOIN (SELECT R.TipDoc AS CdTipRem,R.NumOrden AS NumRemesa,R.IdCia AS CdCiaRem,CN.Compania AS NomCiaRem,R.Fecha AS FecRemesa,R.FecDespacho AS FecDespRem,R.FecEntrega AS FecEntgaRem
 ,R.IdCliente AS NitClieRem,NomCliente,R.IdAgencia AS CdAgeRem,A.Agencia AS NomAgenRem,R.IdVehiculo AS PlacaVeh,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo
 ,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,R.Modalidad AS TipoRemesa,NumPedido,IdCiaPed,NumManif,IdCiaManif,EstCumplido,EstFactura
,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipoRuta,TipoMintrans
 ,D.IdMercancia AS CdMcia,D.DescripMcias AS DescMcia,D.Cantidad AS CantRemesa,D.PesoNeto AS PesoNetoRem,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Cases AS CasesRem,D.Cajas AS CajasRem,D.Palets AS PaletsRem
 ,D.TarifClie AS TarifClieRem,D.UndTarifa AS UndTarifRem,D.TarifPago AS TarifPagoRem,D.TarifTabla AS TarifTablaRem,D.UndTarifPago AS UndTarifPagoRem
 ,D.NitRemite AS NitRemteRem,D.Remitente AS NomRemteRem,D.DirOrigen AS DirOrigenRem,D.IdOrigen AS CdCiuOrigRem,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
 ,D.NitDestntario AS NitDtnatario,D.Destinatario AS NomDestnatario,D.DirDestino AS DirDestinoRem,D.IdDestino AS CdDestinoRem,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
 ,Remision,DocCliente,D.Referencia1 AS Refencia1,D.Referencia2 AS Refencia2,D.Referencia3 AS Refencia3,D.Volumen AS VlmenRem,D.UndVol AS CdUndVol,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,D.TarifSeguro AS TarifSegRem
 ,Cumplido,IdCiaCump,FechaCump,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp
 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  CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 WHERE R.TipDoc=@pmTipRem AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') ) AS RM ON P.Pedido=RM.NumPedido AND P.IdCia=RM.IdCiaPed AND D.IdMercancia=RM.CdMcia
WHERE P.Anulado=0  AND P.IdCia  LIKE ISNULL(@pmIdCia ,'%%') AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND P.Modalidad LIKE ISNULL(@pmModalidad,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraRemesaFac  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
 AS 
SELECT FR.TipDoc AS CdTipDoc,TipoDoc,Factura,FR.IdCia AS CdCia,CN.Compania AS NomCiaFact,FechaFact,Item,TipoReg,TipRem,Remesa,IdCiaRem,CR.Compania AS NomCiaRem,ItemRem,FecRemesa,FR.Descripcion AS DescripRem
,FR.Cantidad AS CantFact,VrUnitario,UndTarifa,VrCosto,UndCosto,TarifaDct,VrDescuento,TarifaIva,VrImpuesto,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,CantidadFalt,UnidadFalt,Unidades,PesoNeto,FR.UndMed AS CdUmPeso,Unidad
,Volumen,UndVol,Cases,Cajas,Palets,FR.CdConcepto AS CdConcept,CDV.Concepto AS DescConcept,CdMercancia,CdAgencia,Agencia,CdCCosto,CCosto,FR.CdSubCos AS CdSubCentro,SubCosto,FR.TipoAfiVehic AS TipoAfiVeh,pVehiculo
,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,FR.Anulado AS FacAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,NumPedRem,CiaPedRem
--datos de remesa
,Modalidad,IdRemitente,NomRemite,IdDestinatario,NomDestino,R.IdConductor AS RemCedCond,CDT.RazonSocial AS RemConductor,nRemolque,NumManif,IdCiaManif,R.Observacion AS RemObserv
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo
--datos de factura encabezados
,FacCdConc,CF.Concepto AS FacConcepto,T.TipoId AS TercTipo,FacNitClie,T.Dv AS TercDv,T.RazonSocial AS NombreClie,FacNitVend,VN.RazonSocial AS Vendedor,FacCdCiudad,LF.Localidad AS FacCiudad,LF.IdDep AS FacCdDpto,DF.Departamento AS FacDpto,FacVrNeto
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 FROM Trn_TraFacRemesas AS FR INNER JOIN Sys_TiposDoc AS TD ON FR.TipDoc=TD.IdDoc INNER JOIN Companias AS CN ON FR.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON FR.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON FR.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 --SUBQUERY FACTURAS
 INNER JOIN (SELECT F.TipDoc AS FacTipo,F.Factura AS FacNumero,F.IdCia AS FacCdCia,F.IdConcepto AS FacCdConc,F.IdCliente AS FacNitClie,F.IdAgencia AS FacIdAgenc
 ,F.IdVend AS FacNitVend,F.IdLocEnv AS FacCdCiudad,F.VrCargos AS FacVrCargos,F.VrOtrDcto AS FacVrOtrDctos,F.VrNeto AS FacVrNeto
 FROM Trn_Facturas AS F WHERE F.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND  F.Modalidad='TRANSPORTE'  AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,D.IdConcepto,D.IdCliente,D.IdAgencia,D.IdVend,D.IdLocEnv,D.VrCargos,D.VrOtrDcto,D.VrNeto
 FROM Trn_DevFcr AS D WHERE D.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND D.Modalidad='TRANSPORTE' AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 UNION ALL
 SELECT N.TipDoc,N.NumNota,N.IdCia,N.IdConcepto,N.IdCliente,N.IdAgencia,N.IdVend,N.IdLocal,N.VrOtros,0,N.VrNeto
 FROM Trn_Notas AS N WHERE N.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND N.Modalidad='REMESAS' AND  N.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND N.IdCliente LIKE ISNULL(@pmIdCliente,'%')  ) AS F ON FR.TipDoc=F.FacTipo AND FR.Factura=F.FacNumero AND FR.IdCia=F.FacCdCia
 INNER JOIN Terceros AS T ON F.FacNitClie=T.IdTercero INNER JOIN TercCliente AS CLI ON F.FacNitClie=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 Terceros AS VN ON F.FacNitVend=VN.IdTercero INNER JOIN Conceptos AS CF ON F.FacCdConc=CF.IdConcepto
 LEFT JOIN Localidades AS LF ON F.FacCdCiudad=LF.IdLocal LEFT JOIN Departamentos AS DF ON LF.IdDep=DF.IdDep
 LEFT JOIN Companias AS CR ON FR.IdCiaRem=CR.IdCia  LEFT JOIN Sys_Um AS UMP ON FR.UndMed=UMP.UndMed
 LEFT JOIN ConcDiversos AS CDV ON FR.CdConcepto=CDV.IdConcepto  LEFT JOIN Agencias  AS A ON FR.CdAgencia=A.IdAgencia
 LEFT JOIN CentroCosto AS CC ON FR.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON FR.CdSubCos=SC.IdSubCos
 LEFT JOIN Trn_TraRemesa AS R ON FR.TipRem=R.TipDoc AND FR.Remesa=R.NumOrden AND FR.IdCiaRem=R.IdCia
 LEFT JOIN Trn_TraRemAnexo AS RA ON FR.TipRem=RA.TipDoc AND FR.Remesa=RA.NumOrden AND FR.IdCiaRem=RA.IdCia
 LEFT JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero
 LEFT JOIN Vehiculos AS V ON FR.pVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 WHERE FR.FechaFact BETWEEN @pmFechaIni AND @pmFechaFin AND FR.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.FacNitClie LIKE ISNULL(@pmIdCliente,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTarifasFletes @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmNumero INT,@pmTipoTarifa VARCHAR(10),@pmTarifClie MONEY,@pmTarifPago MONEY,@pmTarifTabla MONEY,@pmIdMneda VARCHAR(5)
,@pmUndMed VARCHAR(10),@pmNitCliente VARCHAR(16),@pmCdNat VARCHAR(4),@pmCdTipoVeh VARCHAR(4),@pmCdRango VARCHAR(4)
,@pmUndTarifClie VARCHAR(10),@pmUndTarifPago VARCHAR(10),@pmInactivo BIT,@pmCdRuta VARCHAR(4),@pmCdMercancia VARCHAR(16)
 AS 
 UPDATE TarifasFletes SET TipoTarifa=@pmTipoTarifa,TarifClie=@pmTarifClie,TarifPago=@pmTarifPago,TarifTabla=@pmTarifTabla,IdMneda=@pmIdMneda,UndMed=@pmUndMed,NitCliente=@pmNitCliente
,CdNat=@pmCdNat,CdTipoVeh=@pmCdTipoVeh,CdRango=@pmCdRango,Inactivo=@pmInactivo,UndTarifClie=@pmUndTarifClie,UndTarifPago=@pmUndTarifPago,CdRuta=@pmCdRuta
,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,CdMercancia=@pmCdMercancia
 WHERE  Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTraAnticiposApp @pmIdCia CHAR(2)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 UPDATE Trn_TraAnticipos SET  VrAbonado=(SELECT ISNULL(SUM(Trn_TraAntAbonos.VrAbono),0)
 FROM Trn_TraAntAbonos WHERE Trn_TraAntAbonos.TipAnt=Trn_TraAnticipos.TipDoc AND Trn_TraAntAbonos.Anticipo=Trn_TraAnticipos.Anticipo
  AND Trn_TraAntAbonos.IdCiaAnt=Trn_TraAnticipos.IdCia)
 WHERE  IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpTraCumplido @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmModalidad VARCHAR(10),@pmDiasPlazo INT
,@pmFecPago SMALLDATETIME,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmNRadicaDoc INT,@pmIdCiaRadic CHAR(2),@pmCdCiaOfic CHAR(2),@pmFecRadic SMALLDATETIME,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraCumplido SET Fecha=@pmFecha,TipMuc=@pmTipMuc,Manifiesto=@pmManifiesto,IdCiaMuc=@pmIdCiaMuc,IdVehiculo=@pmIdVehiculo,Modalidad=@pmModalidad,DiasPlazo=@pmDiasPlazo,FecPago=@pmFecPago,TipoMargen=@pmTipoMargen
,MargenFalt=@pmMargenFalt,UndCalcFalt=@pmUndCalcFalt,TarifFaltPago=@pmTarifFaltPago,TarifFaltCobro=@pmTarifFaltCobro,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,NRadicaDoc=@pmNRadicaDoc,IdCiaRadic=@pmIdCiaRadic,CdCiaOfic=@pmCdCiaOfic,FecRadic=@pmFecRadic,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpTraCumRadica @pmNumRadica INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmCdCiaRad CHAR(2),@pmDocAnexos VARCHAR(1000),@pmCantDoc INT,@pmFormaEnvio VARCHAR(50)
,@pmNomRadica VARCHAR(150),@pmCedRadica VARCHAR(16),@pmFuncionario VARCHAR(150),@pmObservacion VARCHAR(2000),@pmNumeroCum INT,@pmIdCiaCum CHAR(2),@pmFechaCum SMALLDATETIME
 AS 
 UPDATE Trn_TraCumRadica SET Fecha=@pmFecha,TipMuc=@pmTipMuc,Manifiesto=@pmManifiesto,IdCiaMuc=@pmIdCiaMuc,CdCiaRad=@pmCdCiaRad,DocAnexos=@pmDocAnexos,CantDoc=@pmCantDoc,FormaEnvio=@pmFormaEnvio,NomRadica=@pmNomRadica,CedRadica=@pmCedRadica,Funcionario=@pmFuncionario
,Observacion=@pmObservacion,NumeroCum=@pmNumeroCum,IdCiaCum=@pmIdCiaCum,FechaCum=@pmFechaCum
 WHERE NumRadica=@pmNumRadica AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTraPedAnexo @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@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),@pmPolizaEsp BIT,@pmNumPolizaEsp VARCHAR(50),@pmNitCiaPoliza VARCHAR(16),@pmNomCiaPoliza VARCHAR(150),@pmFecVencePol SMALLDATETIME
,@pmVrLimiteDesp MONEY,@pmSeguros VARCHAR(10),@pmCargue VARCHAR(20),@pmDescargue VARCHAR(20),@pmCdTipoEsc VARCHAR(4),@pmNitSIA VARCHAR(16),@pmNombreSIA VARCHAR(150),@pmContactoSIA VARCHAR(150),@pmTelContacSIA VARCHAR(30),@pmIdLocCont VARCHAR(8),@pmPatioCont VARCHAR(50)
,@pmCdTipoVeh VARCHAR(4),@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmEmbAdicional VARCHAR(150),@pmTipoRuta VARCHAR(10),@pmTipoTrans VARCHAR(10),@pmEmbarque VARCHAR(10),@pmCdTipCarga VARCHAR(4),@pmDevContenedor BIT,@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY
,@pmTarifFaltCobro MONEY,@pmCdRutaTarif VARCHAR(4),@pmTipoServicio VARCHAR(10)
 AS 
 UPDATE Trn_TraPedAnexo SET NomCliente=@pmNomCliente,NomRemite=@pmNomRemite,NomDestino=@pmNomDestino,LugarCargue=@pmLugarCargue,LugarDescargue=@pmLugarDescargue,NomContacto=@pmNomContacto,TelContacto=@pmTelContacto,emlContacto=@pmemlContacto,ContacDestino=@pmContacDestino,TelContacDest=@pmTelContacDest,emlContacDest=@pmemlContacDest
,PolizaEsp=@pmPolizaEsp,NumPolizaEsp=@pmNumPolizaEsp,NitCiaPoliza=@pmNitCiaPoliza,NomCiaPoliza=@pmNomCiaPoliza,FecVencePol=@pmFecVencePol,VrLimiteDesp=@pmVrLimiteDesp,Seguros=@pmSeguros,Cargue=@pmCargue,Descargue=@pmDescargue,CdTipoEsc=@pmCdTipoEsc,NitSIA=@pmNitSIA,NombreSIA=@pmNombreSIA,ContactoSIA=@pmContactoSIA
,TelContacSIA=@pmTelContacSIA,IdLocCont=@pmIdLocCont,PatioCont=@pmPatioCont,CdTipoVeh=@pmCdTipoVeh,TipoMargen=@pmTipoMargen,MargenFalt=@pmMargenFalt,EmbAdicional=@pmEmbAdicional,UndCalcFalt=@pmUndCalcFalt,TarifFaltPago=@pmTarifFaltPago,TarifFaltCobro=@pmTarifFaltCobro
,TipoRuta=@pmTipoRuta,TipoTrans=@pmTipoTrans,Embarque=@pmEmbarque,CdTipCarga=@pmCdTipCarga,DevContenedor=@pmDevContenedor,CdRutaTarif=@pmCdRutaTarif,TipoServicio=@pmTipoServicio
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO