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].[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].[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].[paUpTraManifiesto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraManifiesto]
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



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,O.VrRetFopat,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,O.TarifaFopat
,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,O.VrRetFopat,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,O.TarifaFopat,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].[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,O.TarifaFopat,O.VrRetFopat
,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,O.TarifaFopat,O.CodTarifPat,O.VrRetFopat,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 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.TarifaFopat,M.VrRetFopat,M.CodTarifPat,M.VrSiceMoviliza,M.VrSiceHora
,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 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.TarifaFopat,M.VrRetFopat,M.CodTarifPat,M.VrSiceMoviliza,M.VrSiceHora,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 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,VrRetFopat,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,BaseRet,TarifaRet,TarifaIca,TarifaFopat
,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,CodTarifPat,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,VrSiceMoviliza,VrSiceHora,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].[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,VrRetFopat,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,TarifaFopat,Cantidad,PesoTotal,VolumenCarga,BaseRet,TarifaRet,TarifaIca,CodTarRet
,CodTarIca,CodTarifPat,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,VrSiceMoviliza,VrSiceHora
,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 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,@pmTarifaFopat DECIMAL(14,4),@pmVrRetFopat MONEY,@pmCodTarifPat VARCHAR(4),@pmVrSiceMoviliza MONEY,@pmVrSiceHora 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,TarifaFopat,VrRetFopat,CodTarifPat,VrSiceMoviliza,VrSiceHora)
 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
,@pmTarifaFopat,@pmVrRetFopat,@pmCodTarifPat,@pmVrSiceMoviliza,@pmVrSiceHora)
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,TarifaFopat,VrRetFopat,CodTarifPat,VrSiceMoviliza,VrSiceHora,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].[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,@pmTarifaFopat DECIMAL(14,4),@pmVrRetFopat MONEY,@pmCodTarifPat VARCHAR(4),@pmVrSiceMoviliza MONEY,@pmVrSiceHora 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,TarifaFopat=@pmTarifaFopat,VrRetFopat=@pmVrRetFopat,CodTarifPat=@pmCodTarifPat,VrSiceMoviliza=@pmVrSiceMoviliza,VrSiceHora=@pmVrSiceHora
 WHERE TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto 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,@pmTarifaFopat DECIMAL(14,4),@pmCodTarifPat VARCHAR(4),@pmVrRetFopat 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,TarifaFopat,CodTarifPat,VrRetFopat)
 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,@pmTarifaFopat,@pmCodTarifPat,@pmVrRetFopat)
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,TarifaFopat,CodTarifPat,VrRetFopat
 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].[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.CodTarifPat,M.TarifaFopat,M.VrRetFopat,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