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].[paInsTraDevCum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraDevCum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevCum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraDevCum]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTraDevCum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paDelTraDevCum]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[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.TipDoc='PDT' AND P.Anulado=0  AND P.IdCia  LIKE ISNULL(@pmIdCia ,'%%') AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND P.Modalidad LIKE ISNULL(@pmModalidad,'%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraDevCum] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME
,@pmModdDev VARCHAR(10),@pmOrigenAdd VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraDevCum (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Cumplido,IdCiaDoc,FecDoc,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmCumplido,@pmIdCiaDoc,@pmFecDoc,@pmModdDev,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevCum] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Cumplido,IdCiaDoc,FecDoc,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,TimeSys,IdCiaCrea,IdUsuario
 FROM Trn_TraDevCum
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelTraDevCum] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_TraDevCum
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia