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].[paInsTiposMcia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposMcia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Muc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetalle_Muc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Rem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetalle_Rem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetalle_Sel]
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].[paInsTraManifAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraManifAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraManifiesto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraManifiesto]
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].[paInsTraPedMcias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraPedMcias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraRemMcias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraRemMcias]
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].[paQryTiposMcia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposMcia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetalleLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraDetalleLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetalleOct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraDetalleOct]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetalleRmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_TraDetalleRmt]
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].[paQryTraManifAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiesto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiesto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoRelDet]
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].[paQryTraPedMcias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPedMcias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPedMciasDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPedMciasDoc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemMcias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemMcias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemMciasOrd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemMciasOrd]
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].[paUpTiposMcia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposMcia]
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].[paUpTraManifiesto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraManifiesto]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,UT.Unidad AS UM_PesoTra,M.IdUnd AS CdUndPre,UM.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,M.UmCapac,M.UM_Prod,UP.Unidad AS DescUMprod,M.CodArancel,M.CodSubpartida,M.CodigoUN,UN.Designacion,UN.Clase AS ClaseUN,UN.PeligroSec,UN.GrupoEmb
,UN.IdCoRes,CR.GrupoRP,CR.CodigoCR,CR.CorrResiduo,UN.IdCRdes,DG.CodigoDR,DG.Desagregacion
,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 Sys_Um AS UT ON M.UndMed=UT.UndMed
 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
 LEFT JOIN Sys_Um AS UP ON M.UM_Prod=UP.UndMed
 LEFT JOIN ResiduosPeligrosos AS UN ON M.CodigoUN=UN.CodigoUN LEFT JOIN CorrResiduos AS CR ON UN.IdCoRes=CR.IdCorr
 LEFT JOIN Desagregaciones AS DG ON UN.IdCRdes=DG.IdDrp
 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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,VrReteCREE,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,TarifaRtc
,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaCom,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,VrSeguroRc,VrRecCaja,TarifaAvta,VrImpAvTa,O.VrTrayVacio1,O.VrTrayVacio2
,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
,ISNULL(EGR.Vrabono,0) AS VrEgreso,ISNULL(FechaEgr,EGR.EGRFEC) AS FechaEgr,ISNULL(VC.VrTotalAbo,0) AS CxpAbonado,ISNULL(VC.VrSaldo,0) AS CxpSaldo
--Datos del vehiculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Direccion AS DirPoseedor,T.Telefono AS TelPoseedor,T.TelMovil AS TelMovilPosee,T.e_mail AS emailPoseedor
,TIC.CdLocdad AS IdIcaOdp,LIC.Localidad AS CiudadICaOdp,CDTR.IdLugar AS IdLugarLic,LLC.Localidad AS LugarLicencia
,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 
 LEFT JOIN TercCndtores AS CDTR ON O.IdConductor =CDTR.IdConductor LEFT JOIN Localidades AS LLC ON CDTR.IdLugar=LLC.IdLocal 
 LEFT JOIN Tablapor AS TIC ON O.CodTarIca=TIC.IdTarifa  LEFT JOIN Localidades LIC ON TIC.CdLocdad=LIC.IdLocal 
 LEFT JOIN ( SELECT TipOdp,OrdPago,IdCiaOdp,SUM(VrAbonado) AS Vrabono,MAX(Trn_Comprobantes.Fecha) AS EGRFEC FROM Trn_TraEgrOrden 
 LEFT JOIN Trn_Comprobantes ON Trn_TraEgrOrden.TipCom =Trn_Comprobantes.TipCom AND Trn_TraEgrOrden.NumEgreso =Trn_Comprobantes.Comprobante
 AND Trn_TraEgrOrden.IdCia =Trn_Comprobantes.IdCia  GROUP BY TipOdp,OrdPago,IdCiaOdp) AS EGR
 ON OP.TipDoc =EGR.TipOdp AND OP.OrdPago =EGR.OrdPago AND OP.IdCia =EGR.IdCiaOdp 
 --subconsulta abonos cuentas por pagar
 LEFT JOIN (SELECT TipFac,Factura,IdCia,IdProveedor,Documento,SUM(VrFactura) AS VrTotalFac,SUM(VrAbonado) AS VrTotalAbo,SUM(VrFactura-VrAbonado) AS VrSaldo FROM Trn_ComFactura
 WHERE TipFac='ODP' AND TipDoc='ODP' AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProveedor LIKE ISNULL(@pmIdPoseedor,'%')
 GROUP BY TipFac,Factura,IdCia,IdProveedor,Documento) AS VC ON OP.TipDoc=VC.TipFac AND OP.OrdPago=VC.Documento AND OP.IdCia=VC.IdCia
 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,VrReteCREE,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,TarifaRtc,D.CxPagar,0,'','','',FecDoc,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,CAST(0 AS BIT),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,VrSeguroRc,VrRecCaja,TarifaAvta,VrImpAvTa,O.VrTrayVacio1,O.VrTrayVacio2
,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,ISNULL(EGR.Vrabono,0),ISNULL(FechaEgr,EGR.EGRFEC),O.VrNeto,0
--Datos del vehiculo
,T.TipoId,T.Dv,T.IdLocal,L.Localidad,T.Direccion,T.Telefono,T.TelMovil,T.e_mail,TIC.CdLocdad,LIC.Localidad,CDTR.IdLugar,LLC.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 
 LEFT JOIN TercCndtores AS CDTR ON D.IdConductor=CDTR.IdConductor LEFT JOIN Localidades AS LLC ON CDTR.IdLugar=LLC.IdLocal 
 LEFT JOIN Tablapor AS TIC ON O.CodTarIca=TIC.IdTarifa  LEFT JOIN Localidades LIC ON TIC.CdLocdad=LIC.IdLocal 
 LEFT JOIN ( SELECT TipOdp,OrdPago,IdCiaOdp,SUM(VrAbonado) AS Vrabono,MAX(Trn_Comprobantes.Fecha) AS EGRFEC FROM Trn_TraEgrOrden 
 LEFT JOIN Trn_Comprobantes ON Trn_TraEgrOrden.TipCom =Trn_Comprobantes.TipCom AND Trn_TraEgrOrden.NumEgreso =Trn_Comprobantes.Comprobante
 AND Trn_TraEgrOrden.IdCia=Trn_Comprobantes.IdCia GROUP BY TipOdp,OrdPago,IdCiaOdp) AS EGR
 ON D.TipDoc=EGR.TipOdp AND D.OrdPago=EGR.OrdPago AND D.IdCiaDoc=EGR.IdCiaOdp 
 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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
,@pmOrdPagoIni INT=Null,@pmOrdPagoFin INT=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,OP.Modalidad,LiqRemesas,CxPagar,MulPlazos,OP.IdPlazo AS CdPlazo,Plazo,DiasPago,FechaVence
,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaComp,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,FechaEgr,O.NumCheque,O.Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrSeguroRc,VrRecCaja,VrReteCREE,TarifaRtc,CodTarRtc,VrImpAvTa,CodTarifAvta,TarifaAvta,EdoLiqCausac,O.VrTrayVacio1,O.VrTrayVacio2
,M.Fecha AS FecManif,M.FecDespacho,M.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,M.nRemolque,M.TipoAfiVehic
,M.IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--detalles
,ORM.TipRem,ORM.Remesa AS NumRemesa,ORM.IdCiaRem AS CdCiaRem,ORM.ItemRem,ORM.Cantidad AS Cant,ORM.PesoNeto,ORM.UndMed AS CdUMed,UMP.Unidad AS UmPeso,ORM.Volumen AS Volmen,ORM.UndVol,ORM.Cases,ORM.Cajas,ORM.Palets,ORM.TarifPago,ORM.TarifTabla,ORM.UndTarifa,ORM.Detalle,RD.Referencia1,RD.Referencia2,RD.Referencia3
,RM.IdCliente AS CdCliente,NC.RazonSocial AS NomCliente,RM.IdAgencia AS CdAgencia,A.Agencia
--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,V.IdGrupo AS CdGrupoPro,GrupoProp
,ISNULL(VC.VrTotalAbo,0) AS CxpAbonado,ISNULL(VC.VrSaldo,0) AS CxpSaldo
 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 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Trn_TraRemesa AS RM ON ORM.TipRem=RM.TipDoc AND ORM.Remesa=RM.NumOrden AND ORM.IdCiaRem=RM.IdCia
 LEFT JOIN Trn_TraRemMcias AS RD ON ORM.TipRem=RD.TipDoc AND ORM.Remesa=RD.NumOrden AND ORM.IdCiaRem=RD.IdCia AND ORM.ItemRem=RD.Item
 LEFT JOIN Terceros AS NC ON RM.IdCliente=NC.IdTercero LEFT JOIN Agencias AS A ON RM.IdAgencia=A.IdAgencia
  --subconsulta abonos cuentas por pagar
 LEFT JOIN (SELECT TipFac,Factura,IdCia,IdProveedor,Documento,SUM(VrFactura) AS VrTotalFac,SUM(VrAbonado) AS VrTotalAbo,SUM(VrFactura-VrAbonado) AS VrSaldo FROM Trn_ComFactura
 WHERE TipFac=@pmTipDoc AND TipDoc=@pmTipDoc AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProveedor LIKE ISNULL(@pmIdPoseedor,'%')
 GROUP BY TipFac,Factura,IdCia,IdProveedor,Documento) AS VC ON O.TipDoc=VC.TipFac AND O.OrdPago=VC.Documento AND O.IdCia=VC.IdCia AND O.IdPoseedor=VC.IdProveedor
 WHERE OP.TipDoc=@pmTipDoc AND OP.OrdPago BETWEEN ISNULL(@pmOrdPagoIni,0) AND ISNULL(@pmOrdPagoFin,2147483647)
 AND OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 --Mayo 10/2018 Parametros Obsoletos
 --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,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,VrSeguroRc,VrRecCaja,VrReteCREE,TarifaRtc,CodTarRtc,CodTarifAvta,TarifaAvta,VrImpAvTa,VrDctoPago,M.VrTrayVacio1,M.VrTrayVacio2,CodTarifDcpp,CdForma,CdCenCosto,CdSubCenCos
,O.IdConcepto AS CdConcepto,Concepto,Modalidad,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,NumDocSop,CiaDocSop,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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
,@pmOrdPagoIni INT=Null,@pmOrdPagoFin INT=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
,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaComp,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,FechaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrSeguroRc,VrRecCaja,VrReteCREE,TarifaRtc,CodTarRtc,VrImpAvTa,CodTarifAvta,TarifaAvta,O.VrTrayVacio1,O.VrTrayVacio2
,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,V.IdGrupo AS CdGrupoPro,GrupoProp
,ISNULL(VC.VrTotalAbo,0) AS CxpAbonado,ISNULL(VC.VrSaldo,0) AS CxpSaldo
 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 GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 --subconsulta abonos cuentas por pagar
 LEFT JOIN (SELECT TipFac,Factura,IdCia,IdProveedor,Documento,SUM(VrFactura) AS VrTotalFac,SUM(VrAbonado) AS VrTotalAbo,SUM(VrFactura-VrAbonado) AS VrSaldo FROM Trn_ComFactura
 WHERE TipFac=@pmTipDoc AND TipDoc=@pmTipDoc AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProveedor LIKE ISNULL(@pmIdPoseedor,'%')
 GROUP BY TipFac,Factura,IdCia,IdProveedor,Documento) AS VC ON O.TipDoc=VC.TipFac AND O.OrdPago=VC.Documento AND O.IdCia=VC.IdCia AND O.IdPoseedor=VC.IdProveedor
 WHERE OP.TipDoc=@pmTipDoc AND OP.OrdPago BETWEEN ISNULL(@pmOrdPagoIni,0) AND ISNULL(@pmOrdPagoFin,2147483647)
 AND OP.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 --Mayo 10/2018 Parametros Obsoletos
 --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,'%')
 --2147483647
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
,OP.TipCom AS TipComp,TipoCom,OP.Comprobante AS NumComp,OP.IdCiaCom AS CdCiaComp,OP.Anulado AS Anuldo,NumDev,OP.FecDev AS FechaDev,OP.NumDocSop,OP.CiaDocSop,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,FechaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,O.VrTrayVacio1,O.VrTrayVacio2,EdoLiqCausac,VrRemesas,VrSeguroRc,VrRecCaja,VrReteCREE,TarifaRtc,CodTarRtc,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,CdCenCosto,CdSubCenCos,CodTarifAvta,TarifaAvta,VrImpAvTa,O.VrDctoPago,O.CodTarifDcpp
,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
,NumLiquida
 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 
 LEFT JOIN (SELECT TipOdp,OrdPago,IdCiaOdp,MAX(Liquidacion) AS NumLiquida,SUM(VrAbonado) AS TotalAbonos FROM Trn_TraLiquidaOdp GROUP BY TipOdp,OrdPago,IdCiaOdp)
 AS LQ ON OP.TipDoc=LQ.TipOdp AND OP.OrdPago=LQ.OrdPago AND OP.IdCia=LQ.IdCiaOdp
 WHERE OP.TipDoc=@pmTipDoc AND OP.OrdPago BETWEEN @pmOrdPagoIni AND @pmOrdPagoFin AND OP.IdCia=@pmIdCia
 ORDER BY OP.OrdPago
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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.Observacion AS Observ
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.IdEstado AS CdEstado,Estado,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.NumDevCum
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario,Leyenda
,M.Fecha AS FecManif,FecDespacho,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,TipoComp AS CdTipComp,NumComp AS Comprobante
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact,MvoRechazo
,CU.MunOrigVacio1,CU.MunDestVacio1,CU.MunOrigVacio2,CU.MunDestVacio2,CU.VrTrayVacio1,CU.VrTrayVacio2
--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,TarifFlete,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,TipoCumRemesa,MotivoSuspRem,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue
,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue
--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,CU.CdPlazoPago,PZ.Plazo,PZ.NVmto,PZ.DiasPago
 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 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 Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta 
 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 Plazos AS PZ ON CU.CdPlazoPago=PZ.IdPlazo
 WHERE CU.TipDoc=@pmTipDoc AND CU.Cumplido BETWEEN @pmCumplidoIni AND @pmCumplidoFin AND CU.IdCia=@pmIdCia
 ORDER BY CU.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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.NumDevCum,TipoComp,NumComp,NumRadicaMT,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario
,M.Fecha AS FecManif,FecDespacho,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
,MA.TipoRuta,MA.kmsTotal,MA.NomRemite,MA.NomDestino,MA.LugarFletes,MA.NumAnticipo AS NumAnticipo,MA.NumCheque AS Num_Cheque,MA.TipoMintrans,MA.WsSeguro,MA.NumRadSeguro,CU.TipoCumpMT,CU.MotivoSusp,CU.ConsecSusp,CU.MvoRechazo
,CU.MunOrigVacio1,CU.MunDestVacio1,CU.MunOrigVacio2,CU.MunDestVacio2,CU.VrTrayVacio1,CU.VrTrayVacio2
--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,TarifFlete,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,D.HoraLlegaCargue,D.HoraEntraCargue,D.HoraSaleCargue,D.HoraLlegaDescargue,D.HoraEntraDescargue,D.HoraSaleDescargue
,D.CodCCosto,CCosto,D.CodSubCos,SubCosto
--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,V.IdGrupo AS CdGrupoPro,GrupoProp
 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 Trn_TraManifAnexo AS MA ON CU.TipMuc=MA.TipDoc AND CU.Manifiesto=MA.Manifiesto AND CU.IdCiaMuc=MA.IdCia
 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 Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta 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
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN CentroCosto AS CC ON D.CodCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CodSubCos=SC.IdSubCos
 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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoFmt] @pmTipDoc VARCHAR(3),@pmManifiestoIni INT,@pmManifiestoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc AS CdTipDoc,TipoDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,CN.Compania AS NomCompania,M.Fecha AS FechaMuc,M.FecDespacho,M.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,RT.Ruta AS DescRuta,IdRutaFle,RF.Ruta AS RutaFletes
,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,M.nRemolque,M.TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,M.VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,BaseRet,TarifaRet,TarifaIca
,M.IdLocFletes,CF.Localidad AS CiuLugarFletes,FechaPago,PagoCargue,PagoDescargue,NumMintrans,EdoMintrans,M.FechaRegMT,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,M.EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.OrigenAdd,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom
,M.TimeSys,M.FecUpdate,M.IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario,Leyenda,kmsTotal,VrFleteTon,MA.TipoRuta AS MucTipoRuta,MA.CdLocTrao,CMI.Localidad AS MunIntermedio,MA.CdLocTrad,KmsTraOri,KmsTraDes,MA.IdMneda,MA.VrTasa,MA.NomRemite AS NomRmtente,MA.NomDestino AS NomDestnatario
,MA.LugarFletes AS Lugar_Fletes,NumAnticipo,NumCheque,MA.TipoMintrans AS TipoMucMintrans,MucMintrans,ContIntegral,VolumenCarga,MA.NumPoliza AS NumPolizaMuc,DescFirma,CodEmpresa,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,MA.MvoAnulacion,MvoSuspension
,PuntosRuta,MA.CantViajes,Transbordo,NumMucAnu,CiaMucAnu,MA.AceptaFirma,MA.TipoValPacto,M.VrTrayVacio1,M.VrTrayVacio2,MA.MunOrigVacio1,MA.MunDestVacio1,MA.MunOrigVacio2,MA.MunDestVacio2
--detalles
,MR.Item AS ItemMuc,TipRem,MR.Remesa AS NumRemesa,MR.IdCiaRem AS CdCiaRem,ItemRem,D.IdMercancia AS CdMercancia,DescripMcias,CodigoMcia,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol
,Cases,Cajas,Palets,NitRemite,Remitente,DirOrigen,D.IdOrigen AS RemCodOrigen,LO.Localidad AS RemCiudadOrigen,LO.IdDep AS RemCdepOrigen,DOR.Departamento AS RemDptoOrigen,NitDestntario,Destinatario,DirDestino
,D.IdDestino AS RemCodDestino,LD.Localidad AS RemCiudadDestino,LD.IdDep AS RemCdepDestino,DDN.Departamento AS RemDptoDestino,TarifClie,MR.TarifTabla AS TarifaTabla,MR.TarifPago AS TarifaPago,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro
,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,DA.Agencia AS DetAgencia
,D.Cumplido AS RemCumplido,D.IdCiaCump AS RemCiaCump,D.FechaCump AS RemFechaCum,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,D.TipOdp AS RemTipoOdp,D.NumeroOdp AS RemNumOdp,D.IdCiaOdp AS RemCiaOdp,TarifOdp,PesoCont,MR.RemMintrans AS RemMucMintrans,EdoRemMin
,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,CodBodDtno,TipTraslado,CodigoUN
,HorasCargue,HorasDesc,FecInicioCargue,FecFinCargue,FecIniciodesc,FecFindesc,TiempoCargue,TiempoDesc,FecLlegCargue,FecSalidaCargue,FecLlegdesc,FecSalidadesc
,IdCliente,CL.RazonSocial AS RemNomCliente,R.IdClieFact AS RemIdPropMcia,PM.RazonSocial AS RemPropMcia
--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,V.Modelo,V.Config
,V.PesoVacio,V.PesoMax,V.NumMotor,V.SerieChasis,V.NumSerie,V.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,RQ.PesoKg AS PesoVacioRq
,CT.CertJudicial,CT.FecJudicial,CT.VigJudicial,CT.Licencia,CT.CatLicencia,CT.IdLugar AS CdLugar,EL.LugarLic,CT.FecLicencia,CT.VigLicencia,CDT.Direccion AS CdtDireccion,CDT.Telefono AS CdtTelefono,CDT.TelMovil AS CdtMovil,CDT.e_mail AS CdtEmail
,LC.Localidad AS CdtCiudad,DC.Departamento AS CdtDpto
,CdCondRelev,CRV.RazonSocial AS SegConductor,CTR.CertJudicial  AS CertJudicialRelev,CTR.FecJudicial AS FecJudicialRelev,CTR.VigJudicial AS VigJudicialRelev,CTR.Licencia AS LicenciaRelev,CTR.CatLicencia AS CatLicenciaRelev
,CTR.IdLugar AS CdLugarRelev,ELR.LugarLic AS LugarLicRelev,CTR.FecLicencia AS FecLicenciaRelev,CTR.VigLicencia AS VigLicenciaRelev,CRV.Direccion AS CdtDireccionRelev,CRV.Telefono AS CdtTelefonoRelev,CRV.e_mail AS CdtEmailRelev
,CRV.TelMovil AS CdtMovilRelev,LCR.Localidad AS CdtCiudadRelev, DCR.Departamento AS CdtDptoRelev
 FROM Trn_TraManifiesto AS M INNER JOIN  Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND M.Manifiesto=MA.Manifiesto AND  M.IdCia=MA.IdCia
 INNER JOIN Trn_TraManifRem AS MR ON M.TipDoc=MR.TipDoc AND  M.Manifiesto=MR.Manifiesto AND  M.IdCia=MR.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON MR.TipRem=D.TipDoc  AND MR.Remesa=D.NumOrden AND MR.IdCiaRem=D.IdCia AND MR.ItemRem=D.Item
 INNER JOIN Trn_TraRemesa AS R ON MR.TipRem=R.TipDoc AND MR.Remesa=R.NumOrden AND MR.IdCiaRem=R.IdCia 
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 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 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 Mercancias AS MCA ON D.IdMercancia=MCA.IdMercancia
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DOR ON LO.IdDep=DOR.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DDN ON LD.IdDep=DDN.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 
 INNER JOIN Vehiculos AS V ON M.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 COL ON V.IdColor=COL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Terceros AS CL ON R.IdCliente=CL.IdTercero LEFT JOIN Terceros AS PM ON R.IdClieFact=PM.IdTercero
 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 M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS DA ON D.CdAgencia=DA.IdAgencia  LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango    LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta
 LEFT JOIN Polizas AS PS ON MA.NumPoliza=PS.NumPoliza
 LEFT JOIN Trn_TraRemAnexo AS RA ON MR.TipRem=RA.TipDoc AND MR.Remesa=RA.NumOrden AND MR.IdCiaRem=RA.IdCia
 LEFT JOIN VehRemolq AS RQ ON M.nRemolque=RQ.IdRemque
 LEFT JOIN Terceros AS CRV ON M.CdCondRelev=CRV.IdTercero
 LEFT JOIN Localidades AS LCR ON CRV.IdLocal=LCR.IdLocal LEFT JOIN Departamentos AS DCR ON LCR.IdDep=DCR.IdDep
 LEFT JOIN TercCndtores AS CTR ON M.CdCondRelev=CTR.IdConductor LEFT JOIN ExpLicencias AS ELR ON CTR.IdLugar=ELR.IdLugar
 LEFT JOIN Localidades AS LC ON CDT.IdLocal=LC.IdLocal LEFT JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 LEFT JOIN Localidades AS CMI ON MA.CdLocTrao=CMI.IdLocal 
 WHERE M.TipDoc=@pmTipDoc AND M.Manifiesto BETWEEN @pmManifiestoIni AND @pmManifiestoFin AND M.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,M.Fecha,M.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,IdRutaFle,RF.Ruta AS RutaFletes
,M.IdVehiculo AS PlacaVeh,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
,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrDescuento,M.VrAnticipo,M.VrAntAdic,M.VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,M.TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,M.BaseRet,M.TarifaRet,M.TarifaIca
,M.IdLocFletes,CF.Localidad AS LugarFletes,M.FechaPago,M.PagoCargue,M.PagoDescargue,NumMintrans,EdoMintrans,M.Remesa AS NumRemesa,M.IdCiaRem,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,OP.NumCheque AS EgrNumCheque,ISNULL(TotalEgresos,0) AS TotalEgresos,M.VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.VrTrayVacio1,M.VrTrayVacio2,M.Anulado,M.FecDev
,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom,TimeSys,FecUpdate,IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino,LugarFletes,NumAnticipo,MA.NumCheque,TipoMintrans,MucMintrans,ContIntegral,PuntosRuta,MA.CantViajes,VolumenCarga,ISNULL(Num_Radica,0) AS NumRadicac,MA.TipoValPacto
,Transbordo,NumMucAnu,CiaMucAnu
,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
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia,V.IdGrupo AS CdGrupoPro,GrupoProp,CdCondRelev,CRV.RazonSocial AS SegConductor
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND M.IdCia=MA.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 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 M.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 TercCndtores AS CT ON M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Terceros AS CRV ON M.CdCondRelev=CRV.IdTercero
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
 FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
 WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EG ON M.TipOdp=EG.TipOrden AND M.OrdPago=EG.NumOPago AND M.IdCiaOdp=EG.CdCiaOpago
 --radicacion de cumplidos
 LEFT JOIN (SELECT TipMuc,Manifiesto,IdCiaMuc,MAX(NumRadica) AS Num_Radica FROM Trn_TraCumRadica GROUP BY TipMuc,Manifiesto,IdCiaMuc) AS RC ON M.TipDoc=RC.TipMuc AND M.Manifiesto=RC.Manifiesto AND M.IdCia=RC.IdCiaMuc
--Condiciones consulta general
 WHERE M.TipDoc=@pmTipDoc AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,M.Fecha,M.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,IdRutaFle,RF.Ruta AS RutaFletes
,M.IdVehiculo AS PlacaVeh,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
,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrDescuento,M.VrAnticipo,M.VrAntAdic,M.VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,M.TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,M.BaseRet,M.TarifaRet,M.TarifaIca
,M.IdLocFletes,CF.Localidad AS LugarFletes,M.FechaPago,M.PagoCargue,M.PagoDescargue,NumMintrans,EdoMintrans,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,M.FechaOdp,M.EstOrden,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,OP.NumCheque AS EgrNumCheque,ISNULL(TotalEgresos,0) AS TotalEgresos
,M.VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.VrTrayVacio1,M.VrTrayVacio2,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom,M.TimeSys,M.FecUpdate,M.IdCiaCrea
,M.IdUsuario AS CdUsuario,Usuario,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino,LugarFletes,NumAnticipo,MA.NumCheque,TipoMintrans,MucMintrans,ContIntegral,PuntosRuta,VolumenCarga
,CdCondRelev,CRV.RazonSocial AS SegConductor,ISNULL(Num_Radica,0) AS NumRadicac,MA.TipoValPacto,Transbordo,NumMucAnu,CiaMucAnu
--detalles
,MR.Item AS ItemMuc,TipRem,MR.Remesa AS NumRemesa,MR.IdCiaRem AS CdCiaRem,ItemRem,D.IdMercancia AS CdMercancia,DescripMcias,CodigoMcia,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,D.Volumen,UndVol
,D.Cases,D.Cajas,D.Palets,D.NitRemite,Remitente,DirOrigen,D.IdOrigen AS CodCiuOrigen,LO.Localidad AS CiudadOrigen,NitDestntario,Destinatario,DirDestino,D.IdDestino AS CodCiuDestino,LD.Localidad AS CiudadDestino,TarifClie,MR.TarifTabla AS TarifaTabla,MR.TarifPago AS TarifaPago
,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,D.Cumplido AS NumCumplidoRem,D.IdCiaCump AS CdCiaCumpRem
,D.FechaCump AS FecCumRem,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,D.TipOdp AS DetTipOdp,D.NumeroOdp AS NumOdp,D.IdCiaOdp AS CdCiaOdp,TarifOdp,PesoCont,RemMintrans,EdoRemMin
,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 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
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia,V.IdGrupo AS CdGrupoPro,GrupoProp
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND  M.IdCia=MA.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 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 M.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_TraManifRem AS MR ON M.TipDoc=MR.TipDoc AND  M.Manifiesto=MR.Manifiesto AND  M.IdCia=MR.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON MR.TipRem=D.TipDoc  AND MR.Remesa=D.NumOrden AND MR.IdCiaRem=D.IdCia AND MR.ItemRem=D.Item
 INNER JOIN Mercancias AS MCA ON D.IdMercancia=MCA.IdMercancia
 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 Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta  LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango  LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN Terceros AS CRV ON M.CdCondRelev=CRV.IdTercero
 LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
 FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
 WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EG ON M.TipOdp=EG.TipOrden AND M.OrdPago=EG.NumOPago AND M.IdCiaOdp=EG.CdCiaOpago
 --radicacion de cumplidos
 LEFT JOIN (SELECT TipMuc,Manifiesto,IdCiaMuc,MAX(NumRadica) AS Num_Radica FROM Trn_TraCumRadica GROUP BY TipMuc,Manifiesto,IdCiaMuc) AS RC ON M.TipDoc=RC.TipMuc AND M.Manifiesto=RC.Manifiesto AND M.IdCia=RC.IdCiaMuc
--Condiciones consulta general
 WHERE M.TipDoc=@pmTipDoc AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY M.IdCia,M.Manifiesto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifAnexo] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Manifiesto,IdCia,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa
,NomRemite,NomDestino,LugarFletes,NumAnticipo,NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga
,NumPoliza,CdBodDtno,TipoTrslado,MvoAnulacion,MvoSuspension,WsSeguro,NumRadSeguro,PuntosRuta,CantViajes,Transbordo,NumMucAnu,CiaMucAnu,AceptaFirma,TipoValPacto
,MunOrigVacio1,MunDestVacio1,MunOrigVacio2,MunDestVacio2
 FROM Trn_TraManifAnexo
 WHERE TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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.NumDevCum,TipoComp,NumComp,CodConcepto,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,IdLocFletes,LP.Localidad AS LugarPago,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaAct,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT
,MA.TipoRuta,MvoAnulaCump,ObservAnulado,C.MvoRechazo,NumViajesCum,PesoLiqPago,PesoLiqFact,MA.MucMintrans AS TipoViaje,C.CdPlazoPago,C.MunOrigVacio1,C.MunDestVacio1,C.VrTrayVacio1,C.MunOrigVacio2,C.MunDestVacio2,C.VrTrayVacio2
 FROM Trn_TraCumplido AS C 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 Trn_TraManifiesto AS M ON C.TipMuc=M.TipDoc AND C.Manifiesto=M.Manifiesto AND C.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON C.TipMuc=MA.TipDoc AND C.Manifiesto=MA.Manifiesto AND C.IdCiaMuc=MA.IdCia
 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 
 LEFT JOIN Localidades AS CO ON C.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON C.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON C.CdRuta=R.IdRuta LEFT JOIN Localidades AS LP ON M.IdLocFletes=LP.IdLocal 
 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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraManifAnexo] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2),@pmkmsTotal DECIMAL(14,4),@pmVrFleteTon MONEY,@pmTipoRuta VARCHAR(10)
,@pmCdLocTrao VARCHAR(8),@pmCdLocTrad VARCHAR(8),@pmKmsTraOri DECIMAL(14,4),@pmKmsTraDes DECIMAL(14,4),@pmIdMneda VARCHAR(5),@pmVrTasa DECIMAL(14,4)
,@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarFletes VARCHAR(100),@pmNumAnticipo INT,@pmNumCheque VARCHAR(20),@pmTipoMintrans VARCHAR(10)
,@pmMucMintrans INT,@pmContIntegral INT,@pmVolumenCarga DECIMAL(14,4),@pmNumPoliza VARCHAR(30),@pmCdBodDtno VARCHAR(4),@pmTipoTrslado INT,@pmMvoAnulacion VARCHAR(5),@pmMvoSuspension VARCHAR(5)
,@pmWsSeguro BIT,@pmNumRadSeguro DECIMAL(18,2),@pmPuntosRuta DECIMAL(14,4),@pmCantViajes INT,@pmTransbordo INT,@pmNumMucAnu INT,@pmCiaMucAnu CHAR(2),@pmAceptaFirma BIT,@pmTipoValPacto VARCHAR(10)
,@pmMunOrigVacio1 VARCHAR(8),@pmMunDestVacio1 VARCHAR(8),@pmMunOrigVacio2 VARCHAR(8),@pmMunDestVacio2 VARCHAR(8)
 AS 
 INSERT INTO Trn_TraManifAnexo (TipDoc,Manifiesto,IdCia,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino
,LugarFletes,NumAnticipo,NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga,NumPoliza,CdBodDtno,TipoTrslado,MvoAnulacion,MvoSuspension,WsSeguro,NumRadSeguro,PuntosRuta,CantViajes,Transbordo,NumMucAnu,CiaMucAnu,AceptaFirma,TipoValPacto,MunOrigVacio1,MunDestVacio1,MunOrigVacio2,MunDestVacio2)
 VALUES (@pmTipDoc,@pmManifiesto,@pmIdCia,@pmkmsTotal,@pmVrFleteTon,@pmTipoRuta,@pmCdLocTrao,@pmCdLocTrad,@pmKmsTraOri,@pmKmsTraDes,@pmIdMneda,@pmVrTasa
,@pmNomRemite,@pmNomDestino,@pmLugarFletes,@pmNumAnticipo,@pmNumCheque,@pmTipoMintrans,@pmMucMintrans,@pmContIntegral,@pmVolumenCarga,@pmNumPoliza,@pmCdBodDtno,@pmTipoTrslado,@pmMvoAnulacion,@pmMvoSuspension,@pmWsSeguro
,@pmNumRadSeguro,@pmPuntosRuta,@pmCantViajes,@pmTransbordo,@pmNumMucAnu,@pmCiaMucAnu,@pmAceptaFirma,@pmTipoValPacto,@pmMunOrigVacio1,@pmMunDestVacio1,@pmMunOrigVacio2,@pmMunDestVacio2)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,IdOrigen,LO.Localidad AS CiudadOrigen,IdDestino,LD.Localidad AS CiudadDestino,IdRuta,IdRutaFle,M.IdVehiculo AS PlacaVeh,NumVeh
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,Cantidad,PesoTotal,VolumenCarga,BaseRet,TarifaRet,TarifaIca,CodTarRet
,CodTarIca,IdLocFletes,LugarFletes,FechaPago,PagoCargue,PagoDescargue,NomRemite,NomDestino,NumMintrans,EdoMintrans,Remesa,IdCiaRem,Cumplido,IdCiaCump,FechaCump,EstCumplido
,TipOdp,OrdPago,IdCiaOdp,FechaOdp,EstOrden,VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,NumAnticipo,NumCheque,TipoMintrans,TipoRuta,MucMintrans,PuntosRuta,AceptaFirma,A.TipoValPacto,CdCondRelev,A.CantViajes,M.VrTrayVacio1,M.VrTrayVacio2
,OrigenAdd,Anulado,FecDev,Transbordo,NumMucAnu,CiaMucAnu,TipCom,Comprobante,IdCiaCom,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS A ON M.TipDoc=A.TipDoc AND M.Manifiesto=A.Manifiesto AND M.IdCia=A.IdCia
 INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo INNER JOIN Companias AS CI ON M.IdCia=CI.IdCia
 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 
 INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 WHERE M.TipDoc=@pmTipDoc AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdRuta LIKE ISNULL(@pmIdRuta,'%')  AND IdOrigen LIKE ISNULL(@pmIdOrigen,'%') AND IdDestino LIKE ISNULL(@pmIdDestino,'%')
 AND M.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY M.IdCia,M.Manifiesto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraPedMcias] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Pedido,IdCia,Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,CdRango,Cases,Cajas,Palets
,Remitente,DirOrigen,IdOrigen,Destinatario,DirDestino,IdDestino,TarifClie,TarifPago,TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,Referencia1,Referencia2,Contenedor1,Contenedor2
,NitRemite,NitDestntario,UndTarifa,UndTarifPago,DocCliente,Referencia3,CdTipoVehic,Tipo_Servicio,SedeRem,SedeDest,Cod_UN,Cod_Corr,Cod_Desg,DescripcionRP
 FROM Trn_TraPedMcias
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraPedMciasDoc] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,D.UndMed AS UndPeso,Cases,Cajas,Palets,TarifClie,UndTarifa,TarifTabla,TarifPago,UndTarifPago,VrDeclarado,VrSeguro,TarifSeguro
,D.IdUnd AS CdUnid,Unidad,Referencia1,Referencia2,Referencia3,DocCliente,NitRemite,Remitente,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen
,NitDestntario,Destinatario,DirDestino,IdDestino,LD.Localidad AS CiudadDestino
,dmsLargo,dmsAncho,dmsAlto,Volumen,UndVol,Contenedor1,Contenedor2,CdRango,D.IdEmp AS CdEmpaque,Empaque
,IdNat,IdMnjo,IdTmcia,CdTipoVehic,Tipo_Servicio,SedeRem,SedeDest,Cod_UN,Cod_Corr,Cod_Desg,DescripcionRP
 FROM Trn_TraPedMcias AS D INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal
 INNER JOIN UndMed AS U ON D.IdUnd=U.IdUnd INNER JOIN Empaques AS E ON D.IdEmp=E.IdEmp
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraPedMcias] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdMercancia VARCHAR(16),@pmDescripMcias VARCHAR(250),@pmCantidad DECIMAL(14,4),@pmPesoNeto DECIMAL(14,4),@pmUndMed VARCHAR(10),@pmdmsAlto DECIMAL(14,4),@pmdmsAncho DECIMAL(14,4),@pmdmsLargo DECIMAL(14,4),@pmVolumen DECIMAL(14,4),@pmUndVol VARCHAR(10),@pmIdUnd VARCHAR(4)
,@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmCdRango VARCHAR(4),@pmCases INT,@pmCajas INT,@pmPalets INT,@pmNitRemite VARCHAR(16),@pmRemitente VARCHAR(250),@pmDirOrigen VARCHAR(250),@pmIdOrigen VARCHAR(8),@pmNitDestntario VARCHAR(16),@pmDestinatario VARCHAR(250),@pmDirDestino VARCHAR(250),@pmIdDestino VARCHAR(8),@pmTarifClie MONEY,@pmTarifPago MONEY
,@pmTarifTabla MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmTarifSeguro DECIMAL(14,4),@pmReferencia1 VARCHAR(50),@pmReferencia2 VARCHAR(50),@pmContenedor1 VARCHAR(50),@pmContenedor2 VARCHAR(50),@pmUndTarifa VARCHAR(10),@pmUndTarifPago VARCHAR(10),@pmDocCliente VARCHAR(30),@pmReferencia3 VARCHAR(50),@pmCdTipoVehic VARCHAR(4),@pmTipo_Servicio VARCHAR(10),@pmSedeRem VARCHAR(10),@pmSedeDest VARCHAR(10)
,@pmCod_UN VARCHAR(30),@pmCod_Corr VARCHAR(8),@pmCod_Desg VARCHAR(8),@pmDescripcionRP VARCHAR(500)
 AS 
 INSERT INTO Trn_TraPedMcias (TipDoc,Pedido,IdCia,Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,CdRango,Cases,Cajas,Palets,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino,TarifClie,TarifPago,TarifTabla,VrDeclarado,VrSeguro,TarifSeguro
,Referencia1,Referencia2,Contenedor1,Contenedor2,UndTarifa,UndTarifPago,DocCliente,Referencia3,CdTipoVehic,Tipo_Servicio,SedeRem,SedeDest,Cod_UN,Cod_Corr,Cod_Desg,DescripcionRP)
 VALUES (@pmTipDoc,@pmPedido,@pmIdCia,@pmItem,@pmIdMercancia,@pmDescripMcias,@pmCantidad,@pmPesoNeto,@pmUndMed,@pmdmsAlto,@pmdmsAncho,@pmdmsLargo,@pmVolumen,@pmUndVol,@pmIdUnd,@pmIdEmp,@pmIdNat,@pmIdMnjo,@pmIdTmcia,@pmCdRango,@pmCases,@pmCajas,@pmPalets,@pmNitRemite,@pmRemitente,@pmDirOrigen,@pmIdOrigen,@pmNitDestntario,@pmDestinatario,@pmDirDestino
,@pmIdDestino,@pmTarifClie,@pmTarifPago,@pmTarifTabla,@pmVrDeclarado,@pmVrSeguro,@pmTarifSeguro,@pmReferencia1,@pmReferencia2,@pmContenedor1,@pmContenedor2,@pmUndTarifa,@pmUndTarifPago,@pmDocCliente,@pmReferencia3,@pmCdTipoVehic,@pmTipo_Servicio,@pmSedeRem,@pmSedeDest,@pmCod_UN,@pmCod_Corr,@pmCod_Desg,@pmDescripcionRP)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Sel] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat
,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino
,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc,tmCdTipoVeh
,tmTipoServicio,tmSedeRem,tmSedeDest,tmFecLlegaCargue,tmFecLlegaDesc,tmMinCargue,tmMinDescargue,tmTarifOtros,tmCod_UN,tmCod_Corr,tmCod_Desg,tmDescripRP)
SELECT @pmtmNumero,Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat
,IdMnjo,IdTmcia,CdRango,Cases,Cajas,Palets,0,0,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino
,'0','TERCEROS',TarifClie,TarifPago,TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,UndTarifa
,TipDoc,Pedido,IdCia,Item,0,0,UndTarifPago,'',0,0,0,Null,Null,Null,Null,CdTipoVehic,Tipo_Servicio,SedeRem,SedeDest,Null,Null,0,0,0,Cod_UN,Cod_Corr,Cod_Desg,DescripcionRP
 FROM Trn_TraPedMcias
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,CdRuta,CdOrigen,CdDestino,TipoComp,NumComp,CodConcepto
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue
,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT
,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact,CdPlazoPago,NumDevCum,MvoRechazo,MunOrigVacio1,MunDestVacio1,MunOrigVacio2,MunDestVacio2,VrTrayVacio1,VrTrayVacio2
 FROM Trn_TraCumplido
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,@pmCdRuta VARCHAR(4),@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmTipoComp VARCHAR(3),@pmNumComp INT,@pmCodConcepto VARCHAR(4)
,@pmTipoCumpMT VARCHAR(3),@pmMotivoSusp VARCHAR(3),@pmConsecSusp VARCHAR(3),@pmVrAdicCargue DECIMAL(16,4),@pmVrAdicDescargue DECIMAL(16,4),@pmVrAdicFlete DECIMAL(16,4),@pmMotivoVrAdic VARCHAR(3)
,@pmVrDctoFlete DECIMAL(16,4),@pmMotivoVrDcto VARCHAR(3),@pmVrAdicAnticipo DECIMAL(16,4),@pmFecEntregaDoc SMALLDATETIME,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumViajesCum INT,@pmPesoLiqPago INT,@pmPesoLiqFact INT,@pmCdPlazoPago VARCHAR(4),@pmNumDevCum INT,@pmMvoRechazo INT
,@pmMunOrigVacio1 VARCHAR(8),@pmMunDestVacio1 VARCHAR(8),@pmMunOrigVacio2 VARCHAR(8),@pmMunDestVacio2 VARCHAR(8),@pmVrTrayVacio1 MONEY,@pmVrTrayVacio2 MONEY,@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,CdRuta,CdOrigen,CdDestino
,Anulado,FecDev,TipoComp,NumComp,CodConcepto,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact,CdPlazoPago,NumDevCum,MvoRechazo
,MunOrigVacio1,MunDestVacio1,MunOrigVacio2,MunDestVacio2,VrTrayVacio1,VrTrayVacio2)
 VALUES (@pmTipDoc,@pmCumplido,@pmIdCia,@pmFecha,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmModalidad,@pmDiasPlazo,@pmFecPago,@pmTipoMargen,@pmMargenFalt,@pmUndCalcFalt,@pmTarifFaltPago,@pmTarifFaltCobro,@pmNRadicaDoc,@pmIdCiaRadic,@pmCdCiaOfic,@pmFecRadic
,@pmCdRuta,@pmCdOrigen,@pmCdDestino,@pmAnulado,@pmFecDev,@pmTipoComp,@pmNumComp,@pmCodConcepto,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoCumpMT,@pmMotivoSusp,@pmConsecSusp,@pmVrAdicCargue,@pmVrAdicDescargue,@pmVrAdicFlete
,@pmMotivoVrAdic,@pmVrDctoFlete,@pmMotivoVrDcto,@pmVrAdicAnticipo,@pmFecEntregaDoc,0,@pmMvoAnulaCump,@pmObservAnulado,@pmNumViajesCum,@pmPesoLiqPago,@pmPesoLiqFact,@pmCdPlazoPago,@pmNumDevCum,@pmMvoRechazo,@pmMunOrigVacio1,@pmMunDestVacio1,@pmMunOrigVacio2,@pmMunDestVacio2,@pmVrTrayVacio1,@pmVrTrayVacio2)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,@pmCdRuta VARCHAR(4),@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmTipoComp VARCHAR(3),@pmNumComp INT,@pmCodConcepto VARCHAR(4)
,@pmTipoCumpMT VARCHAR(3),@pmMotivoSusp VARCHAR(3),@pmConsecSusp VARCHAR(3),@pmVrAdicCargue DECIMAL(16,4),@pmVrAdicDescargue DECIMAL(16,4),@pmVrAdicFlete DECIMAL(16,4),@pmMotivoVrAdic VARCHAR(3)
,@pmVrDctoFlete DECIMAL(16,4),@pmMotivoVrDcto VARCHAR(3),@pmVrAdicAnticipo DECIMAL(16,4),@pmFecEntregaDoc SMALLDATETIME,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumViajesCum INT,@pmPesoLiqPago INT,@pmPesoLiqFact INT,@pmCdPlazoPago VARCHAR(4)
,@pmNumDevCum INT,@pmMvoRechazo INT,@pmMunOrigVacio1 VARCHAR(8),@pmMunDestVacio1 VARCHAR(8),@pmMunOrigVacio2 VARCHAR(8),@pmMunDestVacio2 VARCHAR(8),@pmVrTrayVacio1 MONEY,@pmVrTrayVacio2 MONEY,@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,CdRuta=@pmCdRuta,CdOrigen=@pmCdOrigen,CdDestino=@pmCdDestino,TipoComp=@pmTipoComp,NumComp=@pmNumComp,CodConcepto=@pmCodConcepto,FecUpdate=@pmFecUpdate
,TipoCumpMT=@pmTipoCumpMT,MotivoSusp=@pmMotivoSusp,ConsecSusp=@pmConsecSusp,VrAdicCargue=@pmVrAdicCargue,VrAdicDescargue=@pmVrAdicDescargue,VrAdicFlete=@pmVrAdicFlete,MotivoVrAdic=@pmMotivoVrAdic,VrDctoFlete=@pmVrDctoFlete
,MotivoVrDcto=@pmMotivoVrDcto,VrAdicAnticipo=@pmVrAdicAnticipo,FecEntregaDoc=@pmFecEntregaDoc,MvoAnulaCump=@pmMvoAnulaCump,ObservAnulado=@pmObservAnulado,NumViajesCum=@pmNumViajesCum,PesoLiqPago=@pmPesoLiqPago,PesoLiqFact=@pmPesoLiqFact,CdPlazoPago=@pmCdPlazoPago
,NumDevCum=@pmNumDevCum,MvoRechazo=@pmMvoRechazo,MunOrigVacio1=@pmMunOrigVacio1,MunDestVacio1=@pmMunDestVacio1,MunOrigVacio2=@pmMunOrigVacio2,MunDestVacio2=@pmMunDestVacio2,VrTrayVacio1=@pmVrTrayVacio1,VrTrayVacio2=@pmVrTrayVacio2
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraRemMciasOrd] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,D.UndMed AS UndPeso,Cases,Cajas,Palets,D.IdUnd AS CdUnid,Unidad
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,NitRemite,Remitente,SedeRem,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen
,NitDestntario,Destinatario,SedeDest,DirDestino,IdDestino,LD.Localidad AS CiudadDestino
,dmsLargo,dmsAncho,dmsAlto,Volumen,UndVol,Contenedor1,Contenedor2,CdRango,D.IdEmp AS CdEmpaque,Empaque
,IdNat,IdMnjo,IdTmcia,PesoCont,TipDocInv,NumDocInv,IdCiaDocInv,ItemDocInv,Cod_UN,Cod_Corr,Cod_Desg,DescripcionRP
 FROM Trn_TraRemMcias AS D INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal
 INNER JOIN UndMed AS U ON D.IdUnd=U.IdUnd INNER JOIN Empaques AS E ON D.IdEmp=E.IdEmp
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_TraDetalleRmt] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmIdEmp,Empaque,tmIdNat,Natlzaprod
,tmIdOrigen,LO.Localidad AS LugarOrigen,tmIdDestino,LD.Localidad AS LugarDestino,tmTarifClie,tmUndTarifa,tmTarifTabla,tmTarifPago,tmUndTarPago
,tmTarifOtros,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmRemision,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3
,tmDirOrigen,tmDirDestino,tmNitRemite,tmRemitente,tmSedeRem,tmNitDestntario,tmDestinatario,tmSedeDest
,tmdmsLargo,tmdmsAncho,tmdmsAlto,tmVolumen,tmUndVol,tmCases,tmCajas,tmPalets,tmIdUnd,U.Unidad AS UndPresenta
,tmIdMnjo,ManejoMcia,tmIdTmcia,TipoMcia,tmCdRango,DescripRango,tmContenedor1,tmContenedor2,tmPesoCont,tmCod_UN,tmDescripRP,tmCod_Corr,tmCod_Desg
 FROM tm_TraDetalle AS T INNER JOIN Localidades AS LO ON T.tmIdOrigen=LO.IdLocal
 INNER JOIN Localidades AS LD ON T.tmIdDestino=LD.IdLocal
 INNER JOIN UndMed AS U ON T.tmIdUnd=U.IdUnd  LEFT JOIN Empaques AS E ON T.tmIdEmp=E.IdEmp
 LEFT JOIN TiposNat AS TN ON T.tmIdNat=TN.IdNat LEFT JOIN TiposMnjo AS MJ ON T.tmIdMnjo=MJ.IdMnjo
 LEFT JOIN TiposMcia AS TM ON T.tmIdTmcia=TM.IdTmcia 
 LEFT JOIN RangosPeso AS RP ON T.tmCdRango=RP.IdRango
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_TraDetalleOct] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmIdEmp,Empaque,tmIdNat,Natlzaprod
,tmIdOrigen,LO.Localidad AS LugarOrigen,tmIdDestino,LD.Localidad AS LugarDestino,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3
,tmDirOrigen,tmDirDestino,tmNitRemite,tmRemitente,tmSedeRem,tmNitDestntario,tmDestinatario,tmSedeDest,tmCases,tmCajas,tmPalets
,tmdmsLargo,tmdmsAncho,tmdmsAlto,tmVolumen,tmUndVol,tmIdUnd,U.Unidad AS UndPresenta,tmIdMnjo,ManejoMcia,tmIdTmcia,TipoMcia,tmCdRango
,DescripRango,tmContenedor1,tmContenedor2,tmPesoCont,tmCod_UN,tmDescripRP,tmCod_Corr,tmCod_Desg
--,tmTarifClie,tmTarifTabla,tmTarifPago,tmUndTarifa,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmUndTarPago
 FROM tm_TraDetalle AS T INNER JOIN Localidades AS LO ON T.tmIdOrigen=LO.IdLocal
 INNER JOIN Localidades AS LD ON T.tmIdDestino=LD.IdLocal
 INNER JOIN UndMed AS U ON T.tmIdUnd=U.IdUnd  LEFT JOIN Empaques AS E ON T.tmIdEmp=E.IdEmp
 LEFT JOIN TiposNat AS TN ON T.tmIdNat=TN.IdNat LEFT JOIN TiposMnjo AS MJ ON T.tmIdMnjo=MJ.IdMnjo
 LEFT JOIN TiposMcia AS TM ON T.tmIdTmcia=TM.IdTmcia 
 LEFT JOIN RangosPeso AS RP ON T.tmCdRango=RP.IdRango
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraDetalleLta] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmCases,tmCajas,tmPalets,tmPesoNeto,tmUndMed,tmTarifClie,tmUndTarifa,tmTarifTabla,tmTarifPago,tmUndTarPago
,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmIdOrigen,LO.Localidad AS LugarOrigen,tmIdDestino,LD.Localidad AS LugarDestino,tmDirOrigen,tmDirDestino,tmNitRemite,tmRemitente,tmSedeRem
,tmNitDestntario,tmDestinatario,tmSedeDest,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmdmsLargo,tmdmsAncho,tmdmsAlto,tmVolumen,tmUndVol,tmIdUnd,U.Unidad AS UndPresenta,tmIdEmp,Empaque 
,tmIdNat,Natlzaprod,tmIdMnjo,ManejoMcia,tmIdTmcia,TipoMcia,tmCdRango,DescripRango,tmContenedor1,tmContenedor2,tmPesoCont,tmCodBodega,tmTipoTraslado,tmCdTipoVeh,tmTipoServicio,tmTarifOtros
,tmCod_UN,tmCod_Corr,tmCod_Desg,tmDescripRP
 FROM tm_TraDetalle AS T INNER JOIN Localidades AS LO ON T.tmIdOrigen=LO.IdLocal
 INNER JOIN Localidades AS LD ON T.tmIdDestino=LD.IdLocal
 INNER JOIN UndMed AS U ON T.tmIdUnd=U.IdUnd  LEFT JOIN Empaques AS E ON T.tmIdEmp=E.IdEmp
 LEFT JOIN TiposNat AS TN ON T.tmIdNat=TN.IdNat LEFT JOIN TiposMnjo AS MJ ON T.tmIdMnjo=MJ.IdMnjo
 LEFT JOIN TiposMcia AS TM ON T.tmIdTmcia=TM.IdTmcia 
 LEFT JOIN RangosPeso AS RP ON T.tmCdRango=RP.IdRango
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifiesto] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Manifiesto,IdCia,Fecha,FecDespacho,FecEntrega,IdOrigen,IdDestino,IdRuta,IdRutaFle,IdVehiculo,IdConductor,nRemolque,TipoAfiVehic,IdPropietario,IdPoseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,Cantidad,PesoTotal,BaseRet,TarifaRet,TarifaIca,CodTarRet
,CodTarIca,IdLocFletes,FechaPago,PagoCargue,PagoDescargue,NumMintrans,EdoMintrans,Remesa,IdCiaRem,Cumplido,IdCiaCump,FechaCump,EstCumplido
,TipOdp,OrdPago,IdCiaOdp,FechaOdp,EstOrden,VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,VrTrayVacio1,VrTrayVacio2,OrigenAdd,Anulado,FecDev,CdCondRelev
,Observacion,IdEstado,TipCom,Comprobante,IdCiaCom,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraManifiesto
 WHERE TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraManifiesto] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecEntrega SMALLDATETIME,@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmIdRuta VARCHAR(4),@pmIdRutaFle VARCHAR(4),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmVrFletes MONEY,@pmVrRetencion MONEY
,@pmVrReteIca MONEY,@pmVrDescuento MONEY,@pmVrAnticipo MONEY,@pmVrAntAdic MONEY,@pmVrNeto MONEY,@pmVrPagos MONEY,@pmVrCargos MONEY,@pmVrDctos MONEY,@pmTarifaFlete MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmBaseRet MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmIdLocFletes VARCHAR(8),@pmFechaPago SMALLDATETIME,@pmPagoCargue VARCHAR(50),@pmPagoDescargue VARCHAR(50)
,@pmNumMintrans DECIMAL(14,2),@pmEdoMintrans VARCHAR(5),@pmRemesa INT,@pmIdCiaRem CHAR(2),@pmCumplido INT,@pmIdCiaCump CHAR(2),@pmFechaCump SMALLDATETIME,@pmEstCumplido INT,@pmTipOdp VARCHAR(3),@pmOrdPago INT,@pmIdCiaOdp CHAR(2),@pmFechaOdp SMALLDATETIME,@pmEstOrden INT, @pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmVrFleteNeto MONEY,@pmTipInfVia VARCHAR(3),@pmNumViaje INT,@pmCiaInfViaje CHAR(2),@pmCdCatPeaje VARCHAR(4),@pmCdCondRelev VARCHAR(16),@pmVrTrayVacio1 MONEY,@pmVrTrayVacio2 MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraManifiesto (TipDoc,Manifiesto,IdCia,Fecha,FecDespacho,FecEntrega,IdOrigen,IdDestino,IdRuta,IdRutaFle,IdVehiculo,IdConductor,nRemolque,TipoAfiVehic,IdPropietario,IdPoseedor,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,Cantidad,PesoTotal,BaseRet,TarifaRet,TarifaIca,CodTarRet,CodTarIca,IdLocFletes,FechaPago,PagoCargue,PagoDescargue,NumMintrans,EdoMintrans,Remesa
,IdCiaRem,Cumplido,IdCiaCump,FechaCump,EstCumplido,TipOdp,OrdPago,IdCiaOdp,FechaOdp,EstOrden,VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,CdCondRelev,VrTrayVacio1,VrTrayVacio2)
 VALUES (@pmTipDoc,@pmManifiesto,@pmIdCia,@pmFecha,@pmFecDespacho,@pmFecEntrega,@pmIdOrigen,@pmIdDestino,@pmIdRuta,@pmIdRutaFle,@pmIdVehiculo,@pmIdConductor,@pmnRemolque,@pmTipoAfiVehic,@pmIdPropietario,@pmIdPoseedor,@pmVrFletes,@pmVrRetencion,@pmVrReteIca,@pmVrDescuento,@pmVrAnticipo,@pmVrAntAdic,@pmVrNeto,@pmVrPagos,@pmVrCargos,@pmVrDctos,@pmTarifaFlete,@pmCantidad,@pmPesoTotal,@pmBaseRet,@pmTarifaRet,@pmTarifaIca
,@pmCodTarRet,@pmCodTarIca,@pmIdLocFletes,@pmFechaPago,@pmPagoCargue,@pmPagoDescargue,@pmNumMintrans,@pmEdoMintrans,@pmRemesa,@pmIdCiaRem,@pmCumplido,@pmIdCiaCump,@pmFechaCump,@pmEstCumplido,@pmTipOdp,@pmOrdPago,@pmIdCiaOdp,@pmFechaOdp,@pmEstOrden,@pmVrFleteNeto,@pmTipInfVia,@pmNumViaje,@pmCiaInfViaje,@pmCdCatPeaje,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmCdCondRelev,@pmVrTrayVacio1,@pmVrTrayVacio2)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraManifiesto] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecEntrega SMALLDATETIME,@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmIdRuta VARCHAR(4),@pmIdRutaFle VARCHAR(4),@pmIdVehiculo VARCHAR(10)
,@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmVrFletes MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY,@pmVrDescuento MONEY,@pmVrAnticipo MONEY,@pmVrAntAdic MONEY,@pmVrNeto MONEY,@pmVrPagos MONEY
,@pmVrCargos MONEY,@pmVrDctos MONEY,@pmTarifaFlete MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmBaseRet MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmIdLocFletes VARCHAR(8),@pmFechaPago SMALLDATETIME
,@pmPagoCargue VARCHAR(50),@pmPagoDescargue VARCHAR(50),@pmNumMintrans DECIMAL(14,2),@pmEdoMintrans VARCHAR(5),@pmRemesa INT,@pmIdCiaRem CHAR(2),@pmCumplido INT,@pmIdCiaCump CHAR(2),@pmFechaCump SMALLDATETIME,@pmEstCumplido INT,@pmTipOdp VARCHAR(3),@pmOrdPago INT,@pmIdCiaOdp CHAR(2)
,@pmFechaOdp SMALLDATETIME,@pmEstOrden INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmVrFleteNeto MONEY,@pmTipInfVia VARCHAR(3),@pmNumViaje INT,@pmCiaInfViaje CHAR(2)
,@pmCdCatPeaje VARCHAR(4),@pmCdCondRelev VARCHAR(16),@pmVrTrayVacio1 MONEY,@pmVrTrayVacio2 MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraManifiesto SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,FecEntrega=@pmFecEntrega,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,IdRuta=@pmIdRuta,IdRutaFle=@pmIdRutaFle,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,nRemolque=@pmnRemolque,TipoAfiVehic=@pmTipoAfiVehic,IdPropietario=@pmIdPropietario,IdPoseedor=@pmIdPoseedor
,VrFletes=@pmVrFletes,VrRetencion=@pmVrRetencion,VrReteIca=@pmVrReteIca,VrDescuento=@pmVrDescuento,VrAnticipo=@pmVrAnticipo,VrAntAdic=@pmVrAntAdic,VrNeto=@pmVrNeto,VrPagos=@pmVrPagos,VrCargos=@pmVrCargos,VrDctos=@pmVrDctos,TarifaFlete=@pmTarifaFlete,Cantidad=@pmCantidad,PesoTotal=@pmPesoTotal,BaseRet=@pmBaseRet,TarifaRet=@pmTarifaRet
,TarifaIca=@pmTarifaIca,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,IdLocFletes=@pmIdLocFletes,FechaPago=@pmFechaPago,PagoCargue=@pmPagoCargue,PagoDescargue=@pmPagoDescargue,NumMintrans=@pmNumMintrans,EdoMintrans=@pmEdoMintrans,Remesa=@pmRemesa,IdCiaRem=@pmIdCiaRem,Cumplido=@pmCumplido,IdCiaCump=@pmIdCiaCump
,FechaCump=@pmFechaCump,EstCumplido=@pmEstCumplido,TipOdp=@pmTipOdp,OrdPago=@pmOrdPago,IdCiaOdp=@pmIdCiaOdp,FechaOdp=@pmFechaOdp,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,EstOrden=@pmEstOrden,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom
,VrFleteNeto=@pmVrFleteNeto,TipInfVia=@pmTipInfVia,NumViaje=@pmNumViaje,CiaInfViaje=@pmCiaInfViaje,CdCatPeaje=@pmCdCatPeaje,FecUpdate=@pmFecUpdate,CdCondRelev=@pmCdCondRelev,VrTrayVacio1=@pmVrTrayVacio1,VrTrayVacio2=@pmVrTrayVacio2
 WHERE TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Muc] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat
,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino
,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc,tmCdTipoVeh,tmTipoServicio
,tmSedeRem,tmSedeDest,tmFecLlegaCargue,tmFecLlegaDesc,tmMinCargue,tmMinDescargue,tmTarifOtros,tmCod_UN,tmCod_Corr,tmCod_Desg,tmDescripRP)
SELECT @pmtmNumero,RM.Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat
,IdMnjo,IdTmcia,CdRango,Cases,Cajas,Palets,Remision,Factura,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino
,CdAgencia,'TERCEROS',TarifClie,RM.TarifPago,RM.TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,UndTarifa
,TipRem,Remesa,IdCiaRem,ItemRem,RemMintrans,PesoCont,UndTarifPago,CodBodDtno,TipTraslado,HorasCargue,HorasDesc,FecInicioCargue,FecFinCargue,FecIniciodesc,FecFindesc,'','',SedeRem,SedeDest,Null,Null,0,0,D.TarifOtros
,D.Cod_UN,D.Cod_Corr,D.Cod_Desg,D.DescripcionRP
 FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraRemMcias AS D ON  RM.TipRem=D.TipDoc AND RM.Remesa=D.NumOrden AND RM.IdCiaRem=D.IdCia AND RM.ItemRem=D.Item
 WHERE RM.TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto AND RM.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Rem] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat
,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino
,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc,tmCdTipoVeh
,tmTipoServicio,tmSedeRem,tmSedeDest,tmFecLlegaCargue,tmFecLlegaDesc,tmMinCargue,tmMinDescargue,tmTarifOtros,tmCod_UN,tmCod_Corr,tmCod_Desg,tmDescripRP)
SELECT @pmtmNumero,Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat
,IdMnjo,IdTmcia,CdRango,Cases,Cajas,Palets,Remision,Factura,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino
,CdAgencia,'TERCEROS',TarifClie,TarifPago,TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,UndTarifa
,CASE WHEN TipDocInv='SAL' AND NumDocInv>0 THEN TipDocInv ELSE TipFac END,CASE WHEN TipDocInv='SAL' AND NumDocInv>0 THEN NumDocInv ELSE Factura END
,CASE WHEN TipDocInv='SAL' AND NumDocInv>0 THEN IdCiaDocInv ELSE IdCiaFac END,CASE WHEN TipDocInv='SAL' AND NumDocInv>0 THEN ItemDocInv ELSE 0 END
,0,PesoCont,UndTarifPago,'',0,0,0,Null,Null,Null,Null,'','',SedeRem,SedeDest,Null,Null,0,0,TarifOtros,Cod_UN,Cod_Corr,Cod_Desg,DescripcionRP
 FROM Trn_TraRemMcias
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemMcias] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,NumOrden,IdCia,Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat,IdTmcia,IdMnjo,CdRango,Cases,Cajas,Palets
,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino,TarifClie,TarifPago,TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3
,Contenedor1,Contenedor2,CdAgencia,Cumplido,IdCiaCump,FechaCump,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp,PesoCont,SedeRem,SedeDest,TipDocInv,NumDocInv,IdCiaDocInv,ItemDocInv,TarifOtros,Cod_UN,Cod_Corr,Cod_Desg,DescripcionRP
 FROM Trn_TraRemMcias
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
 AND Item BETWEEN ISNULL(@pmItem,-1) AND  ISNULL(@pmItem,2147483647) 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraRemMcias] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdMercancia VARCHAR(16),@pmDescripMcias VARCHAR(250),@pmCantidad DECIMAL(14,4),@pmPesoNeto DECIMAL(14,4),@pmUndMed VARCHAR(10),@pmdmsAlto DECIMAL(14,4),@pmdmsAncho DECIMAL(14,4),@pmdmsLargo DECIMAL(14,4),@pmVolumen DECIMAL(14,4),@pmUndVol VARCHAR(10),@pmIdUnd VARCHAR(4)
,@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmCdRango VARCHAR(4),@pmCases INT,@pmCajas INT,@pmPalets INT,@pmNitRemite VARCHAR(16),@pmRemitente VARCHAR(250),@pmDirOrigen VARCHAR(250),@pmIdOrigen VARCHAR(8),@pmNitDestntario VARCHAR(16),@pmDestinatario VARCHAR(250),@pmDirDestino VARCHAR(250),@pmIdDestino VARCHAR(8),@pmTarifClie MONEY
,@pmTarifPago MONEY,@pmTarifTabla MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmTarifSeguro DECIMAL(14,4),@pmUndTarifa VARCHAR(10),@pmRemision DECIMAL(18,2),@pmDocCliente VARCHAR(30),@pmReferencia1 VARCHAR(50),@pmReferencia2 VARCHAR(50),@pmReferencia3 VARCHAR(50),@pmContenedor1 VARCHAR(50),@pmContenedor2 VARCHAR(50),@pmCdAgencia VARCHAR(16),@pmCumplido INT,@pmIdCiaCump CHAR(2),@pmFechaCump SMALLDATETIME
,@pmDetalleCump VARCHAR(250),@pmCantidadCump DECIMAL(14,4),@pmPesoCump DECIMAL(14,4),@pmVolCump DECIMAL(14,4),@pmCasesCump INT,@pmCajasCump INT,@pmPaletsCump INT,@pmTarifCump MONEY,@pmPagoCump MONEY,@pmUndTarCump VARCHAR(10),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFac SMALLDATETIME,@pmTarifClieFac MONEY,@pmTipOdp VARCHAR(3),@pmNumeroOdp INT
,@pmIdCiaOdp CHAR(2),@pmTarifOdp MONEY,@pmPesoCont DECIMAL(14,4),@pmUndTarifPago VARCHAR(10),@pmUndTarPagoCump VARCHAR(10),@pmSedeRem VARCHAR(10),@pmSedeDest VARCHAR(10),@pmTipDocInv VARCHAR(3),@pmNumDocInv INT,@pmIdCiaDocInv CHAR(2),@pmItemDocInv INT,@pmTarifOtros MONEY,@pmCod_UN VARCHAR(30),@pmCod_Corr VARCHAR(8),@pmCod_Desg VARCHAR(8),@pmDescripcionRP VARCHAR(500)

 AS 
 INSERT INTO Trn_TraRemMcias (TipDoc,NumOrden,IdCia,Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat,IdTmcia,IdMnjo,CdRango,Cases,Cajas,Palets,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino,TarifClie,TarifPago,TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,CdAgencia
,Cumplido,IdCiaCump,FechaCump,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp,PesoCont,SedeRem,SedeDest,TipDocInv,NumDocInv,IdCiaDocInv,ItemDocInv,TarifOtros,Cod_UN,Cod_Corr,Cod_Desg,DescripcionRP)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmItem,@pmIdMercancia,@pmDescripMcias,@pmCantidad,@pmPesoNeto,@pmUndMed,@pmdmsAlto,@pmdmsAncho,@pmdmsLargo,@pmVolumen,@pmUndVol,@pmIdUnd,@pmIdEmp,@pmIdNat,@pmIdTmcia,@pmIdMnjo,@pmCdRango,@pmCases,@pmCajas,@pmPalets,@pmNitRemite,@pmRemitente,@pmDirOrigen,@pmIdOrigen,@pmNitDestntario,@pmDestinatario,@pmDirDestino,@pmIdDestino,@pmTarifClie
,@pmTarifPago,@pmTarifTabla,@pmVrDeclarado,@pmVrSeguro,@pmTarifSeguro,@pmUndTarifa,@pmUndTarifPago,@pmRemision,@pmDocCliente,@pmReferencia1,@pmReferencia2,@pmReferencia3,@pmContenedor1,@pmContenedor2,@pmCdAgencia,@pmCumplido,@pmIdCiaCump,@pmFechaCump,@pmDetalleCump,@pmCantidadCump,@pmPesoCump,@pmVolCump,@pmCasesCump,@pmCajasCump,@pmPaletsCump,@pmTarifCump,@pmPagoCump,@pmUndTarCump,@pmUndTarPagoCump,@pmTipFac,@pmFactura
,@pmIdCiaFac,@pmFechaFac,@pmTarifClieFac,@pmTipOdp,@pmNumeroOdp,@pmIdCiaOdp,@pmTarifOdp,@pmPesoCont,@pmSedeRem,@pmSedeDest,@pmTipDocInv,@pmNumDocInv,@pmIdCiaDocInv,@pmItemDocInv,@pmTarifOtros,@pmCod_UN,@pmCod_Corr,@pmCod_Desg,@pmDescripcionRP)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTiposMcia] @pmIdTmcia VARCHAR(4),@pmTipoMcia VARCHAR(50),@pmCodPeligrosidad VARCHAR(10),@pmInactivo BIT
 AS 
 INSERT INTO TiposMcia (IdTmcia,TipoMcia,CodPeligrosidad,Inactivo)
 VALUES (@pmIdTmcia,@pmTipoMcia,@pmCodPeligrosidad,@pmInactivo)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTiposMcia] @pmIdTmcia VARCHAR(4),@pmTipoMcia VARCHAR(50),@pmCodPeligrosidad VARCHAR(10),@pmInactivo BIT
 AS 
 UPDATE TiposMcia SET TipoMcia=@pmTipoMcia,CodPeligrosidad=@pmCodPeligrosidad,Inactivo=@pmInactivo
 WHERE IdTmcia=@pmIdTmcia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposMcia] @pmIdTmcia VARCHAR(4)
 AS 
 IF @pmIdTmcia IS NULL
  SELECT IdTmcia,TipoMcia,CodPeligrosidad
  FROM TiposMcia
  WHERE  Inactivo=0
  ORDER BY IdTmcia
 ELSE
  SELECT IdTmcia,TipoMcia,CodPeligrosidad,Inactivo 
  FROM TiposMcia
  WHERE IdTmcia=@pmIdTmcia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,FechaEgr,VrSeguroRc,VrReteCREE,TarifaRtc,CodTarRtc,CdCenCosto,CdSubCenCos
,CodTarifAvta,TarifaAvta,VrImpAvTa,VrDctoPago,CodTarifDcpp,VrTrayVacio1,VrTrayVacio2
 FROM Trn_TraOrdenManif
 WHERE TipDoc=@pmTipDoc AND OrdPago=@pmOrdPago AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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),@pmFechaEgr SMALLDATETIME,@pmVrSeguroRc MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmCdCenCosto VARCHAR(16),@pmCdSubCenCos VARCHAR(16),@pmCodTarifAvta VARCHAR(4),@pmTarifaAvta DECIMAL(14,4),@pmVrImpAvTa MONEY,@pmVrDctoPago MONEY,@pmCodTarifDcpp VARCHAR(4)
,@pmVrTrayVacio1 MONEY,@pmVrTrayVacio2 MONEY
 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,FechaEgr,VrSeguroRc,VrReteCREE,TarifaRtc,CodTarRtc,CdCenCosto,CdSubCenCos,CodTarifAvta,TarifaAvta,VrImpAvTa,VrDctoPago,CodTarifDcpp,VrTrayVacio1,VrTrayVacio2)
 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,@pmFechaEgr,@pmVrSeguroRc,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc,@pmCdCenCosto,@pmCdSubCenCos,@pmCodTarifAvta,@pmTarifaAvta,@pmVrImpAvTa,@pmVrDctoPago,@pmCodTarifDcpp,@pmVrTrayVacio1,@pmVrTrayVacio2)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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),@pmCodigoUN VARCHAR(30),@pmUM_Prod VARCHAR(10),@pmCodArancel VARCHAR(5),@pmCodSubpartida VARCHAR(5),@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,CodigoUN,UM_Prod,CodArancel,CodSubpartida)
 VALUES (@pmIdMercancia,@pmDescripMcia,@pmCodigoMcia,@pmIdGrupo,@pmUndMed,@pmIdUnd,@pmIdEmp,@pmIdNat,@pmIdMnjo,@pmIdTmcia,@pmEstadoMcia,@pmContenedor,@pmIdProducto
,@pmUmCapac,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario,@pmCodigoUN,@pmUM_Prod,@pmCodArancel,@pmCodSubpartida)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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),@pmCodigoUN VARCHAR(30),@pmUM_Prod VARCHAR(10),@pmCodArancel VARCHAR(5),@pmCodSubpartida VARCHAR(5),@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,CodigoUN=@pmCodigoUN,UM_Prod=@pmUM_Prod,CodArancel=@pmCodArancel,CodSubpartida=@pmCodSubpartida,FechaUpdate=@pmFechaUpdate
 WHERE IdMercancia=@pmIdMercancia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMercancias] @pmIdMercancia VARCHAR(16)
 AS 
 SELECT IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto
,IdEstado,Inactivo,UmCapac,CodigoUN,UM_Prod,CodArancel,CodSubpartida,FechaAdd,FechaUpdate,IdUsuario
 FROM Mercancias
 WHERE IdMercancia=@pmIdMercancia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetalle] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdMercancia VARCHAR(16),@pmtmDescripMcias VARCHAR(250),@pmtmCantidad DECIMAL(14,4),@pmtmPesoNeto DECIMAL(14,4),@pmtmUndMed VARCHAR(10),@pmtmdmsAlto DECIMAL(14,4),@pmtmdmsAncho DECIMAL(14,4),@pmtmdmsLargo DECIMAL(14,4),@pmtmVolumen DECIMAL(14,4),@pmtmUndVol VARCHAR(10),@pmtmIdUnd VARCHAR(4),@pmtmIdEmp VARCHAR(4),@pmtmIdNat VARCHAR(4),@pmtmIdMnjo VARCHAR(4),@pmtmIdTmcia VARCHAR(4)
,@pmtmCdRango VARCHAR(4),@pmtmCases INT,@pmtmCajas INT,@pmtmPalets INT,@pmtmRemision DECIMAL(18,2),@pmtmNumOrden INT,@pmtmNitRemite VARCHAR(16),@pmtmRemitente VARCHAR(250),@pmtmDirOrigen VARCHAR(250),@pmtmIdOrigen VARCHAR(8),@pmtmNitDestntario VARCHAR(16),@pmtmDestinatario VARCHAR(250),@pmtmDirDestino VARCHAR(250),@pmtmIdDestino VARCHAR(8),@pmtmCdAgencia VARCHAR(16),@pmtmTipoVehic VARCHAR(10),@pmtmTarifClie MONEY,@pmtmTarifPago MONEY,@pmtmTarifTabla MONEY,@pmtmVrDeclarado MONEY,@pmtmVrSeguro MONEY
,@pmtmTarifSeguro DECIMAL(14,4),@pmtmDocCliente VARCHAR(30),@pmtmReferencia1 VARCHAR(50),@pmtmReferencia2 VARCHAR(50),@pmtmReferencia3 VARCHAR(50),@pmtmContenedor1 VARCHAR(50),@pmtmContenedor2 VARCHAR(50),@pmtmUndTarifa VARCHAR(10),@pmtmTipRem VARCHAR(3),@pmtmRemesa INT,@pmtmIdCiaRem CHAR(2),@pmtmItemRem INT,@pmtmNumMintrans DECIMAL(14,2),@pmtmPesoCont DECIMAL(14,4),@pmtmUndTarPago VARCHAR(10),@pmtmCodBodega VARCHAR(4),@pmtmTipoTraslado INT
,@pmtmTieCargue DECIMAL(14,4),@pmtmTieDesc DECIMAL(14,4),@pmtmFecIniCargue SMALLDATETIME,@pmtmFecFinCargue SMALLDATETIME,@pmtmFecInidesc SMALLDATETIME,@pmtmFecFindesc SMALLDATETIME,@pmtmCdTipoVeh VARCHAR(4),@pmtmTipoServicio VARCHAR(10),@pmtmSedeRem VARCHAR(10),@pmtmSedeDest VARCHAR(10),@pmtmFecLlegaCargue SMALLDATETIME,@pmtmFecLlegaDesc SMALLDATETIME,@pmtmMinCargue INT,@pmtmMinDescargue INT,@pmtmTarifOtros MONEY,@pmtmCod_UN VARCHAR(30),@pmtmCod_Corr VARCHAR(8),@pmtmCod_Desg VARCHAR(8),@pmtmDescripRP VARCHAR(500)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3
,tmContenedor1,tmContenedor2,tmUndTarifa,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc,tmCdTipoVeh,tmTipoServicio,tmSedeRem,tmSedeDest,tmFecLlegaCargue,tmFecLlegaDesc,tmMinCargue,tmMinDescargue,tmTarifOtros,tmCod_UN,tmCod_Corr,tmCod_Desg,tmDescripRP)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdMercancia,@pmtmDescripMcias,@pmtmCantidad,@pmtmPesoNeto,@pmtmUndMed,@pmtmdmsAlto,@pmtmdmsAncho,@pmtmdmsLargo,@pmtmVolumen,@pmtmUndVol,@pmtmIdUnd,@pmtmIdEmp,@pmtmIdNat,@pmtmIdMnjo,@pmtmIdTmcia,@pmtmCdRango,@pmtmCases,@pmtmCajas,@pmtmPalets,@pmtmRemision,@pmtmNumOrden,@pmtmNitRemite,@pmtmRemitente,@pmtmDirOrigen,@pmtmIdOrigen,@pmtmNitDestntario,@pmtmDestinatario,@pmtmDirDestino,@pmtmIdDestino,@pmtmCdAgencia,@pmtmTipoVehic
,@pmtmTarifClie,@pmtmTarifPago,@pmtmTarifTabla,@pmtmVrDeclarado,@pmtmVrSeguro,@pmtmTarifSeguro,@pmtmDocCliente,@pmtmReferencia1,@pmtmReferencia2,@pmtmReferencia3,@pmtmContenedor1,@pmtmContenedor2,@pmtmUndTarifa,@pmtmTipRem,@pmtmRemesa,@pmtmIdCiaRem,@pmtmItemRem,@pmtmNumMintrans,@pmtmPesoCont,@pmtmUndTarPago,@pmtmCodBodega,@pmtmTipoTraslado,@pmtmTieCargue,@pmtmTieDesc,@pmtmFecIniCargue,@pmtmFecFinCargue,@pmtmFecInidesc,@pmtmFecFindesc,@pmtmCdTipoVeh,@pmtmTipoServicio,@pmtmSedeRem,@pmtmSedeDest
,@pmtmFecLlegaCargue,@pmtmFecLlegaDesc,@pmtmMinCargue,@pmtmMinDescargue,@pmtmTarifOtros,@pmtmCod_UN,@pmtmCod_Corr,@pmtmCod_Desg,@pmtmDescripRP)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_TraDetalle] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol
,tmIdUnd,tmIdEmp,tmIdNat,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden
,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCdAgencia,tmTipoVehic
,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc
,tmFecFindesc,tmCdTipoVeh,tmTipoServicio,tmSedeRem,tmSedeDest,tmFecLlegaCargue,tmFecLlegaDesc,tmMinCargue,tmMinDescargue,tmTarifOtros,tmCod_UN,tmCod_Corr,tmCod_Desg,tmDescripRP
 FROM tm_TraDetalle
 WHERE tmNumero=@pmtmNumero 
  AND (tmItem>=ISNULL(@pmtmItem,-1)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY tmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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.NumDevCum,TipoComp,NumComp,NumRadicaMT,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario
,M.Fecha AS FecManif,FecDespacho,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
,MA.TipoRuta,MA.kmsTotal,MA.NomRemite,MA.NomDestino,MA.LugarFletes,MA.NumAnticipo AS NumAnticipo,MA.NumCheque AS Num_Cheque,MA.TipoMintrans,MA.WsSeguro,MA.NumRadSeguro,CU.TipoCumpMT,CU.MotivoSusp,CU.ConsecSusp,CU.MvoRechazo
,CU.MunOrigVacio1,CU.MunDestVacio1,CU.MunOrigVacio2,CU.MunDestVacio2,CU.VrTrayVacio1,CU.VrTrayVacio2,dbo.FuncTraCumplidoCobro(CU.TipDoc,CU.Cumplido,CU.IdCia) AS VrTotalClie,dbo.FuncTraCumplidoPago(CU.TipDoc,CU.Cumplido,CU.IdCia) AS VrTotalPago
--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,V.IdGrupo AS CdGrupoPro,GrupoProp
,M.Remesa,M.IdCiaRem,RMT.Fecha AS Fecremesa,RMT.Comprobante AS Cmpremesa,RMT.TipCom AS TipComRmt
 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 Trn_TraManifAnexo AS MA ON CU.TipMuc=MA.TipDoc AND CU.Manifiesto=MA.Manifiesto AND CU.IdCiaMuc=MA.IdCia
 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 Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN (SELECT NumOrden,IdCia,Fecha,TipCom,Comprobante,IdCiaCom FROM Trn_TraRemesa WHERE TipDoc='RMT') AS RMT ON M.Remesa=RMT.NumOrden AND M.IdCiaRem=RMT.IdCia 
 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