if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraRemFact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraRemFact]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraRemFact_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraRemFact_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraFacRemesas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraFacRemesas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposDocAut]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposDocAut]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraRemFact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraRemFact]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraRemFactLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraRemFactLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraFacRemesasItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFacRemesasItem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevAosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevAosRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAntRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntRel]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro
,O.IdVend,VN.RazonSocial AS NomVendedor,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipCausac,O.Causacion,O.CdCiaCau,O.EstCumplido,O.FechaCump,O.TipoRuta,O.Volumen,EstadoBod,O.CdBodega,B.Bodega AS NomBodega,FechaIngBod,FechaRetBod,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
--Detalles de orden
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,D.NumDocFac,D.IndFactItem,D.FechaReg,D.CdUsuario AS DetCdUsuario,UC.Usuario AS DetUsuario,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
--datos de facturas
,FC.FacNumero,FC.FacFecha,ISNULL(FC.FacValor,0) AS FacVrTotal,ISNULL(FC.FacCosto,0) AS FacVrCosto,ISNULL(FC.FacImpuesto,0) AS FacVrIva,ISNULL(FC.FacSeguro,0) AS FacVrSeguro,FacBaseIngreso,DfcBaseIngreso,FacReajuste,DfcReajuste
--datos del tercero
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupo,GrupoClie,CLI.IdPlazo AS CdPlazo,Plazo
,V.NumVeh,V.TipoAfil,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,V.Modelo,V.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,V.IdConductor AS CedConductor,CDT.RazonSocial AS Conductor,V.NumSoat,V.VigSoat
,O.NumAutCupo,O.NumAutMora,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN AlmBodegas AS B ON O.CdBodega=B.IdBodega
 LEFT JOIN Vehiculos AS V ON D.PlacaVehic=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CLR ON V.IdColor=CLR.IdColor
 LEFT JOIN Terceros AS NP ON V.IdPoseedor=NP.IdTercero LEFT JOIN Terceros AS CDT ON V.IdConductor=CDT.IdTercero
 LEFT JOIN adm_Usuarios AS UC ON D.CdUsuario=UC.IdUsuario
 --Facturas item por item
 LEFT JOIN (SELECT TipRem AS FacTipOrd,Remesa AS FacNumOrden,IdCiaRem AS FacIdCiaOrd,ItemRem AS FacItemOrd,MAX(FecRemesa) AS FacFecha
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValor
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCosto
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN VrImpuesto*-1 ELSE VrImpuesto END) AS FacImpuesto
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN VrSeguroRem*-1 ELSE VrSeguroRem END) AS FacSeguro
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='FC' THEN VrDeclMcia ELSE 0 END) AS FacDeclarado
,MAX(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='FC' THEN FR.Factura ELSE 0 END) AS FacNumero
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND SUBSTRING(FR.TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacBaseIngreso
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE 0 END) AS DfcBaseIngreso
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND C.MudReajuste=1 AND SUBSTRING(FR.TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacReajuste
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND C.MudReajuste=1 AND SUBSTRING(FR.TipDoc,1,2)='DF' THEN Cantidad*VrUnitario ELSE 0 END) AS DfcReajuste
 FROM Trn_TraFacRemesas AS FR LEFT JOIN ConcDiversos AS C ON FR.CdConcepto=C.IdConcepto
 WHERE FR.TipRem='OSM' GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS FC ON D.TipDoc=FC.FacTipOrd AND D.NumOrden=FC.FacNumOrden AND D.IdCia=FC.FacIdCiaOrd AND D.Item=FC.FacItemOrd
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro,O.IdVend,VN.RazonSocial AS NomVendedor
,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,EstadoBod,O.CdBodega,FechaIngBod,FechaRetBod
,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.NumAutCupo,O.NumAutMora
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ 
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 WHERE O.TipDoc=@pmTipDoc AND O.FecDespacho BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro,O.IdVend,VN.RazonSocial AS NomVendedor
,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,EstadoBod,O.CdBodega,FechaIngBod,FechaRetBod
,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.NumAutCupo,O.NumAutMora
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.NumDocFac,D.IndFactItem,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,D.FechaReg,D.CdUsuario AS DetCdUsuario,UC.Usuario AS DetUsuario,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia LEFT JOIN adm_Usuarios AS UC ON D.CdUsuario=UC.IdUsuario
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmVrServicio MONEY,@pmVrEmpaque MONEY,@pmVrBodega MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrNeto MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmServicios VARCHAR(50),@pmFormaPago VARCHAR(50),@pmReferencia VARCHAR(50)
,@pmTipInv VARCHAR(3),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTipRem VARCHAR(3),@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmTipCausac VARCHAR(3),@pmCausacion INT,@pmCdCiaCau CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContactoDest VARCHAR(150),@pmemlContDest VARCHAR(100)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmEstCumplido INT,@pmFechaCump SMALLDATETIME,@pmTipoRuta VARCHAR(10),@pmVolumen VARCHAR(50),@pmEstadoBod INT,@pmCdBodega VARCHAR(4),@pmFechaIngBod SMALLDATETIME,@pmFechaRetBod SMALLDATETIME,@pmNitRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmNitDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150)
,@pmNumAutCupo INT,@pmNumAutMora INT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_MudOrdenServ (TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdVend,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,VrServicio,VrEmpaque,VrBodega,VrOtros,VrDescuento,VrImpuesto,VrNeto,VrDeclarado,VrSeguro,Servicios,FormaPago,Referencia,TipInv,NumInvent,IdCiaInv
,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact,TipCausac,Causacion,CdCiaCau,NomContacto,TelsContacto,emlContacto,ContactoDest,emlContDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,EstCumplido,FechaCump,TipoRuta,Volumen,EstadoBod,CdBodega,FechaIngBod,FechaRetBod,NitRemitente,Remitente,NitDestinatario,Destinatario,NumAutCupo,NumAutMora)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmFecha,@pmFecDespacho,@pmIdTipoServ,@pmIdCliente,@pmIdAgencia,@pmIdVend,@pmDirCargue,@pmTipoInmRem,@pmPisoCargue,@pmTelsRemite,@pmCelRemite,@pmDirDescargue,@pmTipoInmDest,@pmPisoDescargue,@pmTelsDest,@pmCelDest,@pmIdOrigen,@pmIdDestino,@pmVrServicio,@pmVrEmpaque,@pmVrBodega,@pmVrOtros,@pmVrDescuento,@pmVrImpuesto,@pmVrNeto,@pmVrDeclarado,@pmVrSeguro
,@pmServicios,@pmFormaPago,@pmReferencia,@pmTipInv,@pmNumInvent,@pmIdCiaInv,@pmTipRem,@pmNumRemesa,@pmIdCiaRem,@pmEstFactura,@pmTipFact,@pmNumFactura,@pmCdCiaFact,@pmTipCausac,@pmCausacion,@pmCdCiaCau,@pmNomContacto,@pmTelsContacto,@pmemlContacto,@pmContactoDest,@pmemlContDest,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmEstCumplido,@pmFechaCump
,@pmTipoRuta,@pmVolumen,@pmEstadoBod,@pmCdBodega,@pmFechaIngBod,@pmFechaRetBod,@pmNitRemitente,@pmRemitente,@pmNitDestinatario,@pmDestinatario,@pmNumAutCupo,@pmNumAutMora)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmVrServicio MONEY,@pmVrEmpaque MONEY,@pmVrBodega MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrNeto MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmServicios VARCHAR(50),@pmFormaPago VARCHAR(50),@pmReferencia VARCHAR(50)
,@pmTipInv VARCHAR(3),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTipRem VARCHAR(3),@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmTipCausac VARCHAR(3),@pmCausacion INT,@pmCdCiaCau CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContactoDest VARCHAR(150),@pmemlContDest VARCHAR(100)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmEstCumplido INT,@pmFechaCump SMALLDATETIME,@pmTipoRuta VARCHAR(10),@pmVolumen VARCHAR(50),@pmEstadoBod INT,@pmCdBodega VARCHAR(4),@pmFechaIngBod SMALLDATETIME,@pmFechaRetBod SMALLDATETIME,@pmNitRemitente VARCHAR(16),@pmRemitente VARCHAR(150),@pmNitDestinatario VARCHAR(16),@pmDestinatario VARCHAR(150),@pmNumAutCupo INT,@pmNumAutMora INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_MudOrdenServ SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,IdTipoServ=@pmIdTipoServ,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVend=@pmIdVend,DirCargue=@pmDirCargue,TipoInmRem=@pmTipoInmRem,PisoCargue=@pmPisoCargue,TelsRemite=@pmTelsRemite,CelRemite=@pmCelRemite,DirDescargue=@pmDirDescargue,TipoInmDest=@pmTipoInmDest,PisoDescargue=@pmPisoDescargue,TelsDest=@pmTelsDest,CelDest=@pmCelDest
,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,VrServicio=@pmVrServicio,VrEmpaque=@pmVrEmpaque,VrBodega=@pmVrBodega,VrOtros=@pmVrOtros,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrNeto=@pmVrNeto,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,Servicios=@pmServicios,FormaPago=@pmFormaPago,Referencia=@pmReferencia,TipInv=@pmTipInv,NumInvent=@pmNumInvent,IdCiaInv=@pmIdCiaInv,TipRem=@pmTipRem,NumRemesa=@pmNumRemesa,IdCiaRem=@pmIdCiaRem
,EstFactura=@pmEstFactura,TipFact=@pmTipFact,NumFactura=@pmNumFactura,CdCiaFact=@pmCdCiaFact,TipCausac=@pmTipCausac,Causacion=@pmCausacion,CdCiaCau=@pmCdCiaCau,NomContacto=@pmNomContacto,TelsContacto=@pmTelsContacto,emlContacto=@pmemlContacto,ContactoDest=@pmContactoDest,emlContDest=@pmemlContDest,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,EstCumplido=@pmEstCumplido
,FechaCump=@pmFechaCump,TipoRuta=@pmTipoRuta,Volumen=@pmVolumen,EstadoBod=@pmEstadoBod,CdBodega=@pmCdBodega,FechaIngBod=@pmFechaIngBod,FechaRetBod=@pmFechaRetBod,NitRemitente=@pmNitRemitente,Remitente=@pmRemitente,NitDestinatario=@pmNitDestinatario,Destinatario=@pmDestinatario,NumAutCupo=@pmNumAutCupo,NumAutMora=@pmNumAutMora,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdVend,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,VrServicio,VrEmpaque,VrBodega,VrOtros,VrDescuento,VrImpuesto,VrNeto,VrDeclarado,VrSeguro,Servicios,FormaPago,Referencia,TipInv,NumInvent,IdCiaInv,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact
,TipCausac,Causacion,CdCiaCau,NomContacto,TelsContacto,emlContacto,ContactoDest,emlContDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,EstCumplido,FechaCump,TipoRuta,Volumen,EstadoBod,CdBodega,FechaIngBod,FechaRetBod,NitRemitente,Remitente,NitDestinatario,Destinatario,NumAutCupo,NumAutMora,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_MudOrdenServ
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposDocAut]
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('FCR','DFO','ODB','OD2','PR1','NTS','MUC','OSA','OCT','OSM')
 --todas las fac.credito con este codigo FCR
 ORDER BY IdDoc
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraFacRemesas] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipoReg INT,@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemesa INT,@pmIdCiaRem CHAR(2),@pmItemRem INT,@pmFecRemesa SMALLDATETIME,@pmDescripcion VARCHAR(250),@pmCantidad DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrCosto MONEY,@pmUndTarifa VARCHAR(10),@pmUndCosto VARCHAR(10),@pmUnidades DECIMAL(14,4),@pmPesoNeto DECIMAL(14,4),@pmUndMed VARCHAR(10),@pmVolumen DECIMAL(14,4),@pmUndVol VARCHAR(10)
,@pmCases INT,@pmCajas INT,@pmPalets INT,@pmTarifaIva DECIMAL(14,4),@pmVrImpuesto MONEY,@pmTarifaDct DECIMAL(14,4),@pmVrDescuento MONEY,@pmTarifaRet DECIMAL(14,4),@pmVrRetencion MONEY,@pmTarifaIca DECIMAL(14,4),@pmVrReteIca MONEY,@pmVrFaltante MONEY,@pmRemision DECIMAL(18,2),@pmDocCliente VARCHAR(30),@pmReferencia1 VARCHAR(50),@pmReferencia2 VARCHAR(50),@pmReferencia3 VARCHAR(50),@pmCdMercancia VARCHAR(16),@pmCdConcepto VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16)
,@pmNitTercero VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmAnulado INT,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmFecDocRef SMALLDATETIME,@pmCantidadFalt DECIMAL(14,4),@pmUnidadFalt VARCHAR(10),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarDct VARCHAR(4),@pmNumPedRem INT,@pmCiaPedRem CHAR(2),@pmVrDeclMcia MONEY,@pmTarifaSeg DECIMAL(14,4),@pmVrSeguroRem MONEY
,@pmNitAsegurad VARCHAR(16),@pmCantPago DECIMAL(14,4),@pmVrAutRetIca MONEY
 AS 
 INSERT INTO Trn_TraFacRemesas (TipDoc,Factura,IdCia,Item,TipoReg,FechaFact,TipRem,Remesa,IdCiaRem,ItemRem,FecRemesa,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero,CdAgencia,pVehiculo,TipoAfiVehic,IdOrigen,IdDestino
,Anulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,CantPago,VrAutRetIca)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmItem,@pmTipoReg,@pmFechaFact,@pmTipRem,@pmRemesa,@pmIdCiaRem,@pmItemRem,@pmFecRemesa,@pmDescripcion,@pmCantidad,@pmVrUnitario,@pmVrCosto,@pmUndTarifa,@pmUndCosto,@pmUnidades,@pmPesoNeto,@pmUndMed,@pmVolumen,@pmUndVol,@pmCases,@pmCajas,@pmPalets,@pmTarifaIva,@pmVrImpuesto,@pmTarifaDct,@pmVrDescuento,@pmTarifaRet,@pmVrRetencion,@pmTarifaIca,@pmVrReteIca,@pmVrFaltante,@pmRemision,@pmDocCliente,@pmReferencia1,@pmReferencia2,@pmReferencia3
,@pmCdMercancia,@pmCdConcepto,@pmCdCCosto,@pmCdSubCos,@pmNitTercero,@pmCdAgencia,@pmpVehiculo,@pmTipoAfiVehic,@pmIdOrigen,@pmIdDestino,@pmAnulado,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmFecDocRef,@pmCantidadFalt,@pmUnidadFalt,@pmCodTarRet,@pmCodTarIca,@pmCodTarDct,@pmNumPedRem,@pmCiaPedRem,@pmVrDeclMcia,@pmTarifaSeg,@pmVrSeguroRem,@pmNitAsegurad,@pmCantPago,@pmVrAutRetIca)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraRemFact_Sel] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraRemFact (tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets
,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia
,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido
,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmCantPago,tmVrAutoIca)
 SELECT @pmtmNumero,Item,TipoReg,TipRem,Remesa,IdCiaRem,ItemRem,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets
,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero,CdAgencia
,pVehiculo,TipoAfiVehic,'0','','',IdOrigen,'0','','',IdDestino,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,0,FecRemesa,0,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,0,TipDocRef,NumDocRef,IdCiaRef,CantPago,VrAutRetIca
 FROM Trn_TraFacRemesas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraFacRemesasItem] @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,Factura,IdCia,Item,TipoReg,FechaFact,TipRem,Remesa,IdCiaRem,ItemRem,FecRemesa,Descripcion,Cantidad,VrUnitario,VrCosto,UndTarifa,UndCosto,Unidades,PesoNeto,UndMed,Volumen,UndVol,Cases,Cajas,Palets
,TarifaIva,VrImpuesto,TarifaDct,VrDescuento,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdMercancia,CdConcepto,CdCCosto,CdSubCos,NitTercero
,CdAgencia,pVehiculo,TipoAfiVehic,IdOrigen,IdDestino,Anulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,CodTarRet,CodTarIca,CodTarDct,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,CantPago,VrAutRetIca
 FROM Trn_TraFacRemesas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraRemFact] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto
,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen
,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmCantPago,tmVrAutoIca
 FROM tm_TraRemFact
 WHERE tmNumero=@pmtmNumero AND tmItem=@pmtmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraRemFactLta] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto
,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen
,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCantPago,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmVrAutoIca,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd
 FROM tm_TraRemFact
 WHERE tmNumero=@pmtmNumero
 ORDER BY tmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraRemFact] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmTipoReg INT,@pmtmTipRem VARCHAR(3),@pmtmRemesa INT,@pmtmIdCiaRem CHAR(2),@pmtmItemRem INT,@pmtmDescripcion VARCHAR(250),@pmtmCantidad DECIMAL(14,4),@pmtmVrUnitario MONEY,@pmtmVrCosto MONEY,@pmtmUndTarifa VARCHAR(10),@pmtmUndCosto VARCHAR(10),@pmtmUnidades DECIMAL(14,4),@pmtmPesoNeto DECIMAL(14,4),@pmtmUndMed VARCHAR(10),@pmtmVolumen DECIMAL(14,4)
,@pmtmUndVol VARCHAR(10),@pmtmCases INT,@pmtmCajas INT,@pmtmPalets INT,@pmtmTarifaIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmTarifaDct DECIMAL(14,4),@pmtmVrDscto MONEY,@pmtmTarifaRet DECIMAL(14,4),@pmtmVrRetFte MONEY,@pmtmTarifaIca DECIMAL(14,4),@pmtmVrReteIca MONEY,@pmtmVrFaltante MONEY,@pmtmRemision DECIMAL(18,2),@pmtmDocCliente VARCHAR(30),@pmtmReferencia1 VARCHAR(50),@pmtmReferencia2 VARCHAR(50),@pmtmReferencia3 VARCHAR(50)
,@pmtmCdMercancia VARCHAR(16),@pmtmCdConc VARCHAR(4),@pmtmCdCCosto VARCHAR(16),@pmtmCdSubCos VARCHAR(16),@pmtmNitTercero VARCHAR(16),@pmtmCdAgencia VARCHAR(16),@pmtmpVehiculo VARCHAR(10),@pmtmTipoVehic VARCHAR(10),@pmtmNitRemite VARCHAR(16),@pmtmRemitente VARCHAR(250),@pmtmDirOrigen VARCHAR(250),@pmtmIdOrigen VARCHAR(8),@pmtmNitDestntario VARCHAR(16),@pmtmDestinatario VARCHAR(250),@pmtmDirDestino VARCHAR(250)
,@pmtmIdDestino VARCHAR(8),@pmtmCantFalt DECIMAL(14,4),@pmtmUnidadFalt VARCHAR(10),@pmtmCdTarRet VARCHAR(4),@pmtmCdTarIca VARCHAR(4),@pmtmCdTarDct VARCHAR(4),@pmtmPedRem INT,@pmtmCiaPed CHAR(2),@pmtmEtdoFact INT,@pmtmFecRemesa SMALLDATETIME,@pmtmIntCumplido INT,@pmtmVrDeclarado MONEY,@pmtmTarifSeguro DECIMAL(14,4),@pmtmVrSeguro MONEY,@pmtmNitAsegurad VARCHAR(16),@pmtmCostoCump MONEY,@pmtmTipoOrd VARCHAR(3),@pmtmNumOrden INT,@pmtmIdCiaOrd CHAR(2)
,@pmtmCantPago DECIMAL(14,4),@pmtmVrAutoIca MONEY
 AS 
 INSERT INTO tm_TraRemFact (tmNumero,tmItem,tmTipoReg,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmDescripcion,tmCantidad,tmVrUnitario,tmVrCosto,tmUndTarifa,tmUndCosto,tmUnidades,tmPesoNeto,tmUndMed,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDscto,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrReteIca,tmVrFaltante,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmCdMercancia,tmCdConc,tmCdCCosto,tmCdSubCos,tmNitTercero,tmCdAgencia
,tmpVehiculo,tmTipoVehic,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCantFalt,tmUnidadFalt,tmCdTarRet,tmCdTarIca,tmCdTarDct,tmPedRem,tmCiaPed,tmEtdoFact,tmFecRemesa,tmIntCumplido,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmNitAsegurad,tmCostoCump,tmTipoOrd,tmNumOrden,tmIdCiaOrd,tmCantPago,tmVrAutoIca)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmTipoReg,@pmtmTipRem,@pmtmRemesa,@pmtmIdCiaRem,@pmtmItemRem,@pmtmDescripcion,@pmtmCantidad,@pmtmVrUnitario,@pmtmVrCosto,@pmtmUndTarifa,@pmtmUndCosto,@pmtmUnidades,@pmtmPesoNeto,@pmtmUndMed,@pmtmVolumen,@pmtmUndVol,@pmtmCases,@pmtmCajas,@pmtmPalets,@pmtmTarifaIva,@pmtmVrIva,@pmtmTarifaDct,@pmtmVrDscto,@pmtmTarifaRet,@pmtmVrRetFte,@pmtmTarifaIca,@pmtmVrReteIca,@pmtmVrFaltante,@pmtmRemision
,@pmtmDocCliente,@pmtmReferencia1,@pmtmReferencia2,@pmtmReferencia3,@pmtmCdMercancia,@pmtmCdConc,@pmtmCdCCosto,@pmtmCdSubCos,@pmtmNitTercero,@pmtmCdAgencia,@pmtmpVehiculo,@pmtmTipoVehic,@pmtmNitRemite,@pmtmRemitente,@pmtmDirOrigen,@pmtmIdOrigen,@pmtmNitDestntario,@pmtmDestinatario,@pmtmDirDestino,@pmtmIdDestino,@pmtmCantFalt,@pmtmUnidadFalt,@pmtmCdTarRet,@pmtmCdTarIca,@pmtmCdTarDct,@pmtmPedRem,@pmtmCiaPed,@pmtmEtdoFact,@pmtmFecRemesa,@pmtmIntCumplido
,@pmtmVrDeclarado,@pmtmTarifSeguro,@pmtmVrSeguro,@pmtmNitAsegurad,@pmtmCostoCump,@pmtmTipoOrd,@pmtmNumOrden,@pmtmIdCiaOrd,@pmtmCantPago,@pmtmVrAutoIca)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAntRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=NULL
 AS 
 SELECT A.TipDoc AS TipoAnt,TipoDoc,Anticipo,A.IdCia AS CdCia,Compania,A.Fecha AS FecAnt,A.IdConcepto AS CdConcepto,Concepto,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,O.IdVehiculo AS PlacaVeh,nRemolque,O.TipoAfiVehic,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,O.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,AG.Agencia,A.VrAnticipo,A.VrAbonado,A.VrAnticipo-A.VrAbonado AS VrSaldo,TipoPago,A.NumCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,PU.NomCuenta,CedBenef,Beneficiario,FechaVence,TipoAncpo,NumPresAnt,A.TipCom,A.Comprobante,A.IdCiaCom,A.Anulado AS Anuldo,A.NumDev
,A.FecDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,A.OrigenAdd,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaModif,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario
--datos de la orden
,O.Modalidad,O.TipoOrden,O.VrTotal,O.VrCosto,O.Cantidad,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido
,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino,LD.Localidad AS NomDestino,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido
,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.Observacion AS OdsObserv
--Cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraOrdenAnt AS A INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 INNER JOIN Companias AS CN ON A.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON A.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
 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 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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS AG ON O.IdAgencia=AG.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS PU ON A.IdCuenta=PU.IdCuenta
 WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevAosRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT D.TipDev AS IdTipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Anticipo AS NumAnticipo,IdCiaDoc,FecDoc
,VrDevolucion,A.VrAnticipo,A.VrAbonado,A.VrAnticipo-A.VrAbonado AS SaldoActual,TipoPago,NumCheque,FecCheque,CdCta,NumeroCta,CT.IdBanco,Banco,CdCuenta,NomCuenta
,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden,O.IdVehiculo AS PlacaVeh,nRemolque,O.TipoAfiVehic,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor
,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,O.VrTotal,O.VrCosto,O.Cantidad
,CedBenef,Beneficiario,FechaVence,D.ModdDev,D.OrigenAdd,D.TipCom,D.Comprobante AS NumComp,D.IdCiaCom,D.Observacion AS Observ
,D.IdEstado AS CdEstado,Estado,D.TimeSys AS FechaCrea,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,Leyenda
--datos orden
,O.Modalidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino,LD.Localidad AS NomDestino
,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.Observacion AS OdsObserv
--Cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevAos AS D INNER JOIN Trn_TraOrdenAnt AS A ON D.TipDoc=A.TipDoc AND D.Anticipo=A.Anticipo AND D.IdCiaDoc=A.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Localidades AS L ON T.IdLocal=L.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 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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN CtasCorrientes AS CT ON D.CdCta=CT.IdCta LEFT JOIN Bancos AS B ON CT.IdBanco=B.IdBanco
 LEFT JOIN Puc AS PU ON D.CdCuenta=PU.IdCuenta LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO