ALTER TABLE Conceptos ADD CodCiaTdo CHAR(2)
GO
INSERT INTO ClaseDiv (IdClase,NomClase,Inactivo)
 VALUES ('0008','INGRESO (DB) RESULTADO DE OPERACION NEGATIVO',0)
GO
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('FCR',31,'INGRESOS (DB) - RESULTADO DE OPERACION NEGATIVO','D')
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('FC1',31,'INGRESOS (DB) - RESULTADO DE OPERACION NEGATIVO','D')
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('FC2',31,'INGRESOS (DB) - RESULTADO DE OPERACION NEGATIVO','D')
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('CUM',31,'INGRESOS (DB)- RESULTADO DE OPERACION NEGATIVO','D')
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('DCU',31,'INGRESOS (CR)- RESULTADO DE OPERACION NEGATIVO','C')
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('DFC',31,'INGRESOS (CR) - RESULTADO DE OPERACION NEGATIVO','C')
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('DF1',31,'INGRESOS (CR) - RESULTADO DE OPERACION NEGATIVO','C')
INSERT INTO Sys_TiposDocCue (IdDoc,IdClase,ClaseCuenta,TipoMov)
 VALUES ('DF2',31,'INGRESOS (CR) - RESULTADO DE OPERACION NEGATIVO','C')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryConceptosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryConceptosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsConceptos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsConceptos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevCumFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevCumFmt]
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].[paQryTraDevOdpRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevOdpRel]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryConceptosLta] @pmIdDoc VARCHAR(3)=Null,@pmIdCom VARCHAR(3)=Null,@pmPdterminado INT=Null
,@pmInactivo  BIT=Null
 AS 
 SELECT IdConcepto,Concepto,C.IdDoc AS CdTipo,TipoDoc,C.IdCom AS CdTcmp,TipoCom,Vcmto,Imptos,Retfte,RetIca,Observacion,Pdterminado
,TrfaSanc,RndTotal,DcmtoEquiv,TipoContab,CdComNiif,CdComFiscal,CodCiaTdo,C.FechaAdd AS Fec_Add,C.FechaUpdate AS Fec_Upd,C.IdUsuario AS IdUsuari,Usuario,C.Inactivo AS Inactiv
 FROM Conceptos AS C INNER JOIN Sys_TiposDoc AS T ON C.IdDoc=T.IdDoc
 INNER JOIN TiposCom AS TC ON C.IdCom=TC.IdCom
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 WHERE C.IdDoc LIKE ISNULL(@pmIdDoc,'%') AND C.IdCom LIKE ISNULL(@pmIdCom,'%')
 AND (Pdterminado>=ISNULL(@pmPdterminado,-1)  AND Pdterminado<=ISNULL(@pmPdterminado,2147483647))
 AND  (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY C.IdDoc,Concepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpConceptos] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(100),@pmIdDoc VARCHAR(3),@pmIdCom VARCHAR(3),@pmVcmto BIT,@pmImptos BIT,@pmRetfte BIT
,@pmRetIca BIT,@pmObservacion VARCHAR(150),@pmPdterminado INT,@pmTrfaSanc DECIMAL(14,4),@pmRndTotal INT,@pmDcmtoEquiv BIT,@pmInactivo BIT
,@pmTipoContab VARCHAR(10),@pmCdComNiif VARCHAR(3),@pmCdComFiscal VARCHAR(3),@pmCodCiaTdo CHAR(2),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Conceptos SET Concepto=@pmConcepto,IdDoc=@pmIdDoc,IdCom=@pmIdCom,Vcmto=@pmVcmto,Imptos=@pmImptos,Retfte=@pmRetfte,RetIca=@pmRetIca,Observacion=@pmObservacion
,Pdterminado=@pmPdterminado,TrfaSanc=@pmTrfaSanc,RndTotal=@pmRndTotal,Inactivo=@pmInactivo,DcmtoEquiv=@pmDcmtoEquiv
,TipoContab=@pmTipoContab,CdComNiif=@pmCdComNiif,CdComFiscal=@pmCdComFiscal,CodCiaTdo=@pmCodCiaTdo,FechaUpdate=@pmFechaUpdate
 WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryConceptos] @pmIdConcepto VARCHAR(4)
 AS 
 SELECT IdConcepto,Concepto,IdDoc,IdCom,Vcmto,Imptos,Retfte,RetIca,Observacion,Pdterminado
,TrfaSanc,RndTotal,Inactivo,DcmtoEquiv,TipoContab,CdComNiif,CdComFiscal,CodCiaTdo,FechaAdd,FechaUpdate,IdUsuario
 FROM Conceptos
 WHERE IdConcepto=@pmIdConcepto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsConceptos] @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(100),@pmIdDoc VARCHAR(3),@pmIdCom VARCHAR(3),@pmVcmto BIT,@pmImptos BIT
,@pmRetfte BIT,@pmRetIca BIT,@pmObservacion VARCHAR(150),@pmPdterminado INT,@pmTrfaSanc DECIMAL(14,4),@pmRndTotal INT,@pmDcmtoEquiv BIT
,@pmInactivo BIT,@pmTipoContab VARCHAR(10),@pmCdComNiif VARCHAR(3),@pmCdComFiscal VARCHAR(3),@pmCodCiaTdo CHAR(2),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Conceptos (IdConcepto,Concepto,IdDoc,IdCom,Vcmto,Imptos,Retfte,RetIca,Observacion,Pdterminado,TrfaSanc,RndTotal,DcmtoEquiv
,Inactivo,FechaAdd,IdUsuario,TipoContab,CdComNiif,CdComFiscal,CodCiaTdo)
 VALUES (@pmIdConcepto,@pmConcepto,@pmIdDoc,@pmIdCom,@pmVcmto,@pmImptos,@pmRetfte,@pmRetIca,@pmObservacion,@pmPdterminado,@pmTrfaSanc
,@pmRndTotal,@pmDcmtoEquiv,@pmInactivo,@pmFechaAdd,@pmIdUsuario,@pmTipoContab,@pmCdComNiif,@pmCdComFiscal,@pmCodCiaTdo)
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
 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,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.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].[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
 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
,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
,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
,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 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.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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevOdpRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipoOdp,D.OrdPago AS NumOdp,IdCiaDoc,FecDoc
,D.IdVehiculo AS PlacaVeh,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,CxPagar,ModdDev
,D.TipCom AS CdTipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp,D.Observacion AS Observ,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos
,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,O.VrNeto AS Vr_Neto
,TarifaTabla,O.TarifaFlete AS Tarifa_Flete,UnidTarifa,O.PesoTotal AS Peso_Total,Unidades,O.Volumen AS VolmTotal,PesoOrigen,UnidOrigen,VolOrigen,PesoDestino,UnidDestino,VolDestino
,O.BaseRet,O.BaseIca,BaseSeg,BaseImp,O.TarifaRet,O.TarifaIca,TarifaSeg,TarifaImp,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos
,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,CdForma,EdoLiqCausac
,M.Fecha AS FecManif,FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--Datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevOdp  AS D INNER JOIN Trn_TraOrdenManif AS O ON D.TipDoc=O.TipDoc AND D.OrdPago=O.OrdPago AND D.IdCiaDoc=O.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero  LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
-- AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
-- AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
-- AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevCumFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.TipDev,D.Devolucion,D.IdCia AS CdCia,Compania,D.Fecha,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Cumplido,D.IdCiaDoc,D.FecDoc
,C.TipMuc,C.Manifiesto,C.IdCiaMuc,C.IdVehiculo AS PlacaVeh,C.Modalidad,C.DiasPlazo,C.FecPago,C.Observacion AS CumObserv
,C.TipoCumpMT,C.MotivoSusp,C.ConsecSusp,C.VrAdicCargue,C.VrAdicDescargue,C.VrAdicFlete,C.MotivoVrAdic
,C.VrDctoFlete,C.MotivoVrDcto,C.VrAdicAnticipo,C.FecEntregaDoc,C.NumRadicaMT,C.MvoAnulaCump,C.ObservAnulado,C.NumViajesCum,C.PesoLiqPago,C.PesoLiqFact
,C.CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,C.CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,C.CdRuta,R.Ruta AS DescRuta
,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,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.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,M.FechaOdp,M.EstOrden
,D.ModdDev,D.OrigenAdd,D.TipCom,D.Comprobante,D.IdCiaCom,D.Observacion AS Observ,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraDevCum AS D INNER JOIN Trn_TraCumplido AS C ON D.TipDoc=C.TipDoc AND D.Cumplido=C.Cumplido AND D.IdCiaDoc=C.IdCia
 INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON D.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 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 Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo
 INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 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  
 WHERE D.TipDev=@pmTipDev AND D.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
GO