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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFinCausacion @pmTipoProc VARCHAR(3),@pmNumProc INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTotalCapital MONEY
,@pmTotalInteres MONEY,@pmTotalIntMora MONEY,@pmTipoCred VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmAnulado BIT,@pmObservacion VARCHAR(250)
,@pmEdoCausac INT,@pmTotalOtros MONEY,@pmTipoLiquida VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinCausacion (TipoProc,NumProc,IdCia,Fecha,TotalCapital,TotalInteres,TotalIntMora,TotalOtros,TipoLiquida,TipoCred,TipCom,Comprobante,Anulado,Observacion,EdoCausac,TimeSys,IdUsuario)
 VALUES (@pmTipoProc,@pmNumProc,@pmIdCia,@pmFecha,@pmTotalCapital,@pmTotalInteres,@pmTotalIntMora,@pmTotalOtros,@pmTipoLiquida,@pmTipoCred,@pmTipCom,@pmComprobante,@pmAnulado
,@pmObservacion,@pmEdoCausac,@pmTimeSys,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsMercancias @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(250),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10)
,@pmIdUnd VARCHAR(4),@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmEstadoMcia VARCHAR(20),@pmContenedor BIT
,@pmIdProducto VARCHAR(16),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmUmCapac VARCHAR(10),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Mercancias (IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto,UmCapac,IdEstado,Inactivo,FechaAdd,IdUsuario)
 VALUES (@pmIdMercancia,@pmDescripMcia,@pmCodigoMcia,@pmIdGrupo,@pmUndMed,@pmIdUnd,@pmIdEmp,@pmIdNat,@pmIdMnjo,@pmIdTmcia,@pmEstadoMcia,@pmContenedor,@pmIdProducto
,@pmUmCapac,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTraTurn @pmNumero INT,@pmIdCia CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIndTurno INT,@pmFecha SMALLDATETIME
,@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmEstado INT,@pmTipOrd VARCHAR(3)
,@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmFecOrden SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraTurn (Numero,IdCia,IdVehiculo,IndTurno,Fecha,IdConductor,nRemolque,Estado,TipOrd,NumOrden,IdCiaOrd,FecOrden,IdUsuario)
 VALUES (@pmNumero,@pmIdCia,@pmIdVehiculo,@pmIndTurno,@pmFecha,@pmIdConductor,@pmnRemolque,@pmEstado
,@pmTipOrd,@pmNumOrden,@pmIdCiaOrd,@pmFecOrden,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCausacionFmt @pmTipoProc VARCHAR(3),@pmNumProcIni INT,@pmNumProcFin INT,@pmIdCia CHAR(2) 
 AS 
 SELECT  NumProc,FC.IdCia AS CdCia,Compania,FC.Fecha AS FecCausac,FC.TipCom AS TipComp,FC.Comprobante AS NumComp,FC.Anulado AS Anuldo
 ,FC.Observacion AS CauObserv,TipoLiquida,C.TipDoc AS Tip_Doc,C.IdPrestamo AS NumPstmo,Item,NumCuota,FechaVence
,VrTotal,VrAbonado,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,C.IdCliente AS NitCliente,RazonSocial,C.IdAgencia AS  CdAgencia,NomAgencia AS Agencia
,TipoCuota,Concepto,Detalle,FecUltPago,FecLiqMora,C.FechaCrea AS CuoFecCrea,EdoCausac,FC.TimeSys AS CauFechaCrea,FC.IdUsuario AS CdUsuario,Usuario
--datos del prestamo
,FecPrestamo,FecCorte,NPlazos,Causacion,VrPrestamo,NumCredito,P.Observacion AS Observ,TipoCred
 FROM  Trn_FinCausacion AS FC INNER JOIN Trn_FinCuotas AS C ON FC.TipoProc=C.TipoCausac AND FC.NumProc=C.NumCausacion AND FC.IdCia=C.IdCiaCau
 INNER JOIN Companias AS CI ON FC.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON FC.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero   
 INNER JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc AND C.IdPrestamo=P.IdPrestamo AND C.IdCia=P.IdCia
 LEFT JOIN TercCliePrestamo AS CLI ON  C.IdCliente=CLI.IdClie AND C.IdAgencia=CLI.IdAgencia
 WHERE TipoProc=@pmTipoProc AND  NumProc BETWEEN @pmNumProcIni AND  @pmNumProcFin AND FC.IdCia=@pmIdCia
 AND C.TipDoc='PR1'
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryMercancias @pmIdMercancia VARCHAR(16)
 AS 
 SELECT IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto
,IdEstado,Inactivo,UmCapac,FechaAdd,FechaUpdate,IdUsuario
 FROM Mercancias
 WHERE IdMercancia=@pmIdMercancia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMercanciasLta @pmIdGrupo VARCHAR(10)=Null,@pmIdNat VARCHAR(4)=Null,@pmIdTmcia VARCHAR(4)=Null
,@pmInactivo BIT=Null
 AS 
 SELECT IdMercancia,DescripMcia,M.IdGrupo AS CdGrupo,GrupoMcia,M.UndMed AS Und_Med,M.IdUnd AS CdUndPre,Unidad,UM.IdEmp AS CdEmp
,M.IdNat AS CdNat,Natlzaprod,M.IdMnjo AS CdMnjo,ManejoMcia,M.IdTmcia AS CdTmcia,TipoMcia,Contenedor,M.IdProducto AS CdProducto,DescripProd,CodigoMcia
,M.IdEmp AS CdEmp,Empaque,EstadoMcia,UmCapac,M.IdEstado AS CdEstado,Estado,M.Inactivo AS Inactvo,M.FechaAdd AS FechaCrea,M.FechaUpdate AS FechaAct,M.IdUsuario AS CdUsuario,Usuario
 FROM Mercancias AS M INNER JOIN GruposMcia AS G ON M.IdGrupo=G.IdGrupo
 INNER JOIN UndMed AS UM ON M.IdUnd=UM.IdUnd    INNER JOIN TiposNat AS N ON M.IdNat=N.IdNat
 INNER JOIN TiposMnjo AS MM ON M.IdMnjo=MM.IdMnjo INNER JOIN TiposMcia AS TM ON M.IdTmcia=TM.IdTmcia
 INNER JOIN EstadoPro AS EP ON M.IdEstado=EP.IdEstado  INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario 
 LEFT JOIN ProdMcias AS P ON M.IdProducto=P.IdProducto LEFT JOIN Empaques AS E ON M.IdEmp=E.IdEmp
 WHERE M.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND M.IdNat LIKE ISNULL(@pmIdNat,'%') AND M.IdTmcia LIKE ISNULL(@pmIdTmcia,'%')
  AND (M.Inactivo=ISNULL(@pmInactivo,0) or  M.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY DescripMcia
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
,D.TipCom AS CdTipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp,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 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
,D.TipCom AS CdTipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp,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 paUpMercancias @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(250),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10),@pmIdUnd VARCHAR(4)
,@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmEstadoMcia VARCHAR(20),@pmContenedor BIT,@pmIdProducto VARCHAR(16)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmUmCapac VARCHAR(10),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Mercancias SET DescripMcia=@pmDescripMcia,CodigoMcia=@pmCodigoMcia,IdGrupo=@pmIdGrupo,UndMed=@pmUndMed,IdUnd=@pmIdUnd
,IdNat=@pmIdNat,IdMnjo=@pmIdMnjo,IdTmcia=@pmIdTmcia,Contenedor=@pmContenedor,IdProducto=@pmIdProducto,IdEstado=@pmIdEstado,Inactivo=@pmInactivo
,IdEmp=@pmIdEmp,EstadoMcia=@pmEstadoMcia,UmCapac=@pmUmCapac,FechaUpdate=@pmFechaUpdate
 WHERE IdMercancia=@pmIdMercancia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpTraTurn @pmNumero INT,@pmIdCia CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIndTurno INT,@pmFecha SMALLDATETIME,@pmIdConductor VARCHAR(16)
,@pmnRemolque VARCHAR(10),@pmEstado INT,@pmTipOrd VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmFecOrden SMALLDATETIME
 AS 
 UPDATE Trn_TraTurn SET IdVehiculo=@pmIdVehiculo,IndTurno=@pmIndTurno,Fecha=@pmFecha,IdConductor=@pmIdConductor,nRemolque=@pmnRemolque,Estado=@pmEstado,TipOrd=@pmTipOrd,NumOrden=@pmNumOrden
,IdCiaOrd=@pmIdCiaOrd,FecOrden=@pmFecOrden
 WHERE Numero=@pmNumero 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 paUpVehiculosMuc @pmIdVehiculo VARCHAR(10),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100)
,@pmFecMuc SMALLDATETIME,@pmCdRutaHab VARCHAR(4)
 AS 
 UPDATE Vehiculos SET CdLocal=@pmCdLocal,Ubicacion=@pmUbicacion,FecUltServ=@pmFecMuc
,FecPriServ=ISNULL(FecPriServ,@pmFecMuc),CdRutaHab=@pmCdRutaHab
 WHERE IdVehiculo=@pmIdVehiculo

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

