ALTER TABLE Trn_TraPorteInt ADD Condic_Pago VARCHAR(1000)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraPorteInt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraPorteInt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraPorteInt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraPorteInt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPorteInt_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPorteInt_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPorteInt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPorteInt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPorteIntLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPorteIntLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPorteIntLtd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPorteIntLtd]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraPorteInt_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecEmision,O.FecCargue,O.FecEntrega,DPO.IdPais AS CdPaisOrig,POR.NombrePais AS PaisOrig,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdOrigen,LO.Localidad AS DescOrigen
,DPD.IdPais AS CdPaisDest,PDE.NombrePais AS PaisDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,O.IdDestino,LD.Localidad AS DescDestino,O.IdNotificado,O.Notificado,NF.Telefono AS TelNoti,NF.TelMovil AS MovNoti,O.IdRemitente,O.Remitente,RM.Telefono AS TelRemi,RM.TelMovil AS MovRemi,O.DirOrigen
,O.IdDestinatario,O.Destinatario,DT.Telefono AS TelDest,DT.TelMovil AS MovDest,O.DirDestino,O.IdConsig,O.Consignatario,CS.Telefono AS TelCons,CS.TelMovil AS MovCons,O.DirConsig,O.InsTransporte,O.DocRemitente,O.ValorTotal,M.Simbolo,O.IdMoneda AS CdMoneda,M.Mneda AS Moneda,O.PesoTotal,O.Manifiesto,O.CdCiaManif
,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario,O.Negociacion,O.Condic_Pago
,D.Item,D.CdMercancia,D.DescripMcias,D.Cantidad,D.PesoNeto,D.PesoBruto,D.Volumen,D.ValorMcia,D.IdMoneda AS DetIdMoneda,MN.Mneda AS DetMoneda,MN.Simbolo AS DetMonSimb,D.IdUnd AS CdUnd,UM.Unidad,D.IdEmp AS CdEmp,Empaque
,D.IdNat AS CdNat,Natlzaprod,D.UndMed,UP.Unidad AS DesUndPeso,D.UndVol,UV.Unidad AS DesUndVol,D.Bultos,D.Observacion AS DetObservacion,D.DescripUnid
 FROM Trn_TraPorteInt AS O INNER JOIN Companias AS C ON O.IdCia=C.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Trn_TraPorteIntMcia AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN adm_Paises AS POR ON DPO.IdPais=POR.IdPais
 INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep INNER JOIN adm_Paises AS PDE ON DPD.IdPais=PDE.IdPais
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS E ON O.IdEstado=E.IdEstado
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN adm_monedas AS MN ON D.IdMoneda=MN.IdMneda  
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat
 INNER JOIN Terceros AS RM ON O.IdRemitente=RM.IdTercero INNER JOIN Terceros AS DT ON O.IdDestinatario=DT.IdTercero
 INNER JOIN Terceros AS NF ON O.IdNotificado=NF.IdTercero INNER JOIN Terceros AS CS ON O.IdConsig=CS.IdTercero
 LEFT JOIN Sys_Um AS UP ON D.UndMed=UP.UndMed LEFT JOIN Sys_Um AS UV ON D.UndVol=UV.UndMed
 LEFT JOIN adm_monedas AS M ON O.IdMoneda=M.IdMneda LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraPorteIntLtd] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecEmision,O.FecCargue,O.FecEntrega,DPO.IdPais AS CdPaisOrig,POR.NombrePais AS PaisOrig,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdOrigen,LO.Localidad AS DescOrigen
,DPD.IdPais AS CdPaisDest,PDE.NombrePais AS PaisDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,O.IdDestino,LD.Localidad AS DescDestino,O.IdNotificado,O.Notificado,O.IdRemitente,O.Remitente,O.DirOrigen
,O.IdDestinatario,O.Destinatario,O.DirDestino,O.IdConsig,O.Consignatario,O.DirConsig,O.InsTransporte,O.DocRemitente,O.ValorTotal,M.Simbolo,O.IdMoneda AS CdMoneda,M.Mneda AS Moneda,O.PesoTotal,O.Manifiesto,O.CdCiaManif
,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario,O.Negociacion,O.Condic_Pago
--detalles
,D.Item,D.CdMercancia,D.DescripMcias,D.Cantidad,D.PesoNeto,D.PesoBruto,D.Volumen,D.ValorMcia,D.IdMoneda AS DetIdMoneda,MN.Mneda AS DetMoneda,MN.Simbolo AS DetMonSimb,D.IdUnd AS CdUnd,UM.Unidad,D.IdEmp AS CdEmp,Empaque
,D.IdNat AS CdNat,Natlzaprod,D.UndMed,UP.Unidad AS DesUndPeso,D.UndVol,UV.Unidad AS DesUndVol,D.Bultos,D.Observacion AS DetObservacion,D.DescripUnid
 FROM Trn_TraPorteInt AS O INNER JOIN Companias AS C ON O.IdCia=C.IdCia
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN adm_Paises AS POR ON DPO.IdPais=POR.IdPais
 INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep INNER JOIN adm_Paises AS PDE ON DPD.IdPais=PDE.IdPais
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS E ON O.IdEstado=E.IdEstado
 INNER JOIN Trn_TraPorteIntMcia AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN adm_monedas AS MN ON D.IdMoneda=MN.IdMneda
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat
 LEFT JOIN adm_monedas AS M ON O.IdMoneda=M.IdMneda LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp 
 LEFT JOIN Sys_Um AS UP ON D.UndMed=UP.UndMed LEFT JOIN Sys_Um AS UV ON D.UndVol=UV.UndMed
 WHERE O.TipDoc=@pmTipDoc AND O.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraPorteIntLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecEmision,O.FecCargue,O.FecEntrega,DPO.IdPais AS CdPaisOrig,POR.NombrePais AS PaisOrig,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdOrigen,LO.Localidad AS DescOrigen
,DPD.IdPais AS CdPaisDest,PDE.NombrePais AS PaisDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,O.IdDestino,LD.Localidad AS DescDestino,O.IdNotificado,O.Notificado,O.IdRemitente,O.Remitente,O.DirOrigen
,O.IdDestinatario,O.Destinatario,O.DirDestino,O.IdConsig,O.Consignatario,O.DirConsig,O.InsTransporte,O.DocRemitente,O.ValorTotal,M.Simbolo,O.IdMoneda AS CdMoneda,M.Mneda AS Moneda,O.PesoTotal,O.Manifiesto,O.CdCiaManif
,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.Negociacion,O.Condic_Pago,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraPorteInt AS O INNER JOIN Companias AS C ON O.IdCia=C.IdCia
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN adm_Paises AS POR ON DPO.IdPais=POR.IdPais
 INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep INNER JOIN adm_Paises AS PDE ON DPD.IdPais=PDE.IdPais
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS E ON O.IdEstado=E.IdEstado
 LEFT JOIN adm_monedas AS M ON O.IdMoneda=M.IdMneda 
 WHERE O.TipDoc=@pmTipDoc AND O.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraPorteInt] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecEmision SMALLDATETIME,@pmFecCargue SMALLDATETIME,@pmFecEntrega SMALLDATETIME,@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmIdNotificado VARCHAR(16),@pmNotificado VARCHAR(150),@pmIdRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmDirOrigen VARCHAR(250)
,@pmIdDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150),@pmDirDestino VARCHAR(250),@pmIdConsig VARCHAR(16),@pmConsignatario VARCHAR(150),@pmDirConsig VARCHAR(250),@pmInsTransporte VARCHAR(1000),@pmDocRemitente VARCHAR(500),@pmIdMoneda VARCHAR(5),@pmValorTotal MONEY,@pmPesoTotal DECIMAL(14,4),@pmManifiesto INT,@pmCdCiaManif CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmNegociacion VARCHAR(1000),@pmCondic_Pago VARCHAR(1000),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraPorteInt (TipDoc,NumOrden,IdCia,Fecha,FecEmision,FecCargue,FecEntrega,IdOrigen,IdDestino,IdNotificado,Notificado,IdRemitente,Remitente,DirOrigen,IdDestinatario,Destinatario,DirDestino,IdConsig,Consignatario,DirConsig,InsTransporte,DocRemitente,IdMoneda,ValorTotal,PesoTotal,Manifiesto,CdCiaManif,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,Negociacion,Condic_Pago)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmFecha,@pmFecEmision,@pmFecCargue,@pmFecEntrega,@pmIdOrigen,@pmIdDestino,@pmIdNotificado,@pmNotificado,@pmIdRemitente,@pmRemitente,@pmDirOrigen,@pmIdDestinatario,@pmDestinatario,@pmDirDestino,@pmIdConsig,@pmConsignatario,@pmDirConsig,@pmInsTransporte,@pmDocRemitente,@pmIdMoneda,@pmValorTotal,@pmPesoTotal,@pmManifiesto,@pmCdCiaManif,@pmOrigenAdd
,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNegociacion,@pmCondic_Pago)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraPorteInt] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecEmision SMALLDATETIME,@pmFecCargue SMALLDATETIME,@pmFecEntrega SMALLDATETIME,@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmIdNotificado VARCHAR(16),@pmNotificado VARCHAR(150),@pmIdRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmDirOrigen VARCHAR(250)
,@pmIdDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150),@pmDirDestino VARCHAR(250),@pmIdConsig VARCHAR(16),@pmConsignatario VARCHAR(150),@pmDirConsig VARCHAR(250),@pmInsTransporte VARCHAR(1000),@pmDocRemitente VARCHAR(500),@pmIdMoneda VARCHAR(5),@pmValorTotal MONEY,@pmPesoTotal DECIMAL(14,4),@pmManifiesto INT,@pmCdCiaManif CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmNegociacion VARCHAR(1000),@pmCondic_Pago VARCHAR(1000),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraPorteInt SET Fecha=@pmFecha,FecEmision=@pmFecEmision,FecCargue=@pmFecCargue,FecEntrega=@pmFecEntrega,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,IdNotificado=@pmIdNotificado,Notificado=@pmNotificado,IdRemitente=@pmIdRemitente,Remitente=@pmRemitente,DirOrigen=@pmDirOrigen,IdDestinatario=@pmIdDestinatario,Destinatario=@pmDestinatario,DirDestino=@pmDirDestino,IdConsig=@pmIdConsig
,Consignatario=@pmConsignatario,DirConsig=@pmDirConsig,InsTransporte=@pmInsTransporte,DocRemitente=@pmDocRemitente,IdMoneda=@pmIdMoneda,ValorTotal=@pmValorTotal,PesoTotal=@pmPesoTotal,Manifiesto=@pmManifiesto,CdCiaManif=@pmCdCiaManif,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,Negociacion=@pmNegociacion,Condic_Pago=@pmCondic_Pago
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraPorteInt] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecEmision,FecCargue,FecEntrega,IdOrigen,IdDestino,IdNotificado,Notificado,IdRemitente,Remitente,DirOrigen,IdDestinatario,Destinatario,DirDestino,IdConsig,Consignatario,DirConsig,InsTransporte,DocRemitente
,IdMoneda,ValorTotal,PesoTotal,Manifiesto,CdCiaManif,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,Negociacion,Condic_Pago,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraPorteInt
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO