if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsRecibos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsRecibos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsSalidas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsSalidas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_MovClientes_Dfc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_MovClientes_Dfc]
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].[paInsVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsVehRemolq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcr_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcr_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrAcu]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrAcu]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDevFcrRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecibos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRecibos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecibos_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRecibos_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecibosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRecibosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecibosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRecibosRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySalidas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySalidas_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySalidasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidasRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySalidasRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevOdpFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevOdpFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevOdpLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevOdpLta]
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].[paQryVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehRemolq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehRemolqLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehRemolqLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDevFcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpRecibos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpRecibos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpSalidas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpSalidas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpVehRemolq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpVehRemolq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemCancelaOst]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemCancelaOst]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetalleRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalleRes]
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
,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
,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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevOdpLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT  Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipOdp,D.OrdPago AS NumOrden,IdCiaDoc,FecDoc,TipMuc,Manifiesto,IdCiaMuc,D.IdVehiculo AS PlacaVeh,NumVeh
,D.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,D.CxPagar AS EsCxpagar,FechaVence,LiqRemesas,TipoLiq,VrTotalFletes,VrDescuento
,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,M.VrImpAvTa,VrNeto,TarifaTabla,TarifaFlete
,UnidTarifa,PesoTotal,Unidades,Volumen,VrRemesas,VrSeguroRc,VrRecCaja,CdForma,NumCheque,Referencia,ModdDev,D.TipCom AS Tip_Comp,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaCom,D.Observacion AS Observ
,D.IdEstado AS CdEstado,Estado,D.TimeSys AS FechaCrea,D.FecUpdate AS FechaAct,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraDevOdp AS D INNER JOIN  Trn_TraOrdenPago AS O ON D.TipDoc=O.TipDoc  AND D.OrdPago=O.OrdPago AND D.IdCiaDoc=O.IdCia
 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 D.IdCia=CI.IdCia INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN Terceros AS NP ON D.IdPoseedor=NP.IdTercero 
 INNER JOIN Terceros AS NC ON D.IdConductor=NC.IdTercero
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 WHERE TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY  D.IdCia,D.Devolucion
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.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,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
,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].[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,CdForma,CdCenCosto,CdSubCenCos
,O.IdConcepto AS CdConcepto,Concepto,Modalidad,TipEgr,Egreso,IdCiaEgr,FechaEgr,NumCheque,Referencia,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
 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 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,'%')
 ORDER BY OP.IdCia,OP.OrdPago
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 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,'%')
 ORDER BY OP.IdCia,OP.OrdPago
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevOdpFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipoOdp,D.OrdPago AS NumOdp,IdCiaDoc,FecDoc
,D.IdVehiculo AS PlacaVeh,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,CxPagar,ModdDev
,D.TipCom AS CdTipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp,D.Observacion AS Observ,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,Leyenda
,O.TipMuc AS TipManif,O.Manifiesto AS NumManif,O.IdCiaMuc AS CdCiaManif,VrTotalFletes,O.VrDescuento AS Vr_Dcto,O.VrRetencion AS Vr_RetFte,O.VrReteIca AS Vr_RetIca,VrAnticipos
,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrImpAvTa,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,TarifaAvta,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,VrConcPagos
,VrConcDctos,VrConcFondo,VrConcSeguro,VrConcAporte,VrConcImpuesto,VrRemesas,VrRecCaja,CdForma,EdoLiqCausac
,M.Fecha AS FecManif,FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,nRemolque,TipoAfiVehic
,IdLocFletes,CF.Localidad AS LugarFletes,M.Observacion AS MucObserv
--Datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevOdp AS D INNER JOIN Trn_TraOrdenManif AS O ON D.TipDoc=O.TipDoc AND D.OrdPago=O.OrdPago AND D.IdCiaDoc=O.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_TraManifiesto AS M ON O.TipMuc=M.TipDoc AND O.Manifiesto=M.Manifiesto AND O.IdCiaMuc=M.IdCia
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta    INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero  LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 WHERE D.TipDev=@pmTipDev AND D.Devolucion  BETWEEN @pmDevolucionIni AND @pmDevolucionFin  AND D.IdCia=@pmIdCia

 ORDER BY D.Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehRemolqLta] @pmIdMarca VARCHAR(4)=Null,@pmIdCrceria VARCHAR(4)=Null,@pmModelo VARCHAR(4)=Null
,@pmIdPropietario VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmFecComIni SMALLDATETIME=Null,@pmFecComFin SMALLDATETIME=Null
 AS 
 SELECT IdRemque,R.IdMarca AS CdMarca,Marca,R.IdColor AS CodColor,NomColor,R.IdCrceria AS CodCarr,TipoCar,Modelo,ConfEjes,NEjes,nLlantas,Altura,Ancho,Largo,Vlumen
,PesoKg,Capneta,UndCapc,NumComp,CapcComp,NumSerial,IdPropietario,NP.RazonSocial AS Propietario,NitProv,NPV.RazonSocial AS Proveedor,FecCompra,VidaUtil
,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje,kmRuta,kmOtro,EquipoAdic,Descripcion,CdCenSer,CentroServ,CdLocal,LU.Localidad AS CiuUbicacion
,LU.IdDep AS CodDpto,Departamento,Ubicacion,R.Observacion AS Observ,R.ArcFotoRem,R.IdEstado AS CodEstado,Estado,R.Inactivo AS Inactiv,R.IdUsuario AS IdUsuari,Usuario
,FecAdd,FecUpdate,EV.NColor AS NumColor,OutDemand,TarjetaProp,FecTarjProp,VigTarjProp,Hidrostatica,FecPruebaHid,VigPruebaHid,Kingpin,FecKingpin,VigKingpin,FecVigExtintor
 FROM VehRemolq AS R INNER JOIN Marcas AS M ON R.IdMarca=M.IdMarca  INNER JOIN TiposCol AS CL ON R.IdColor=CL.IdColor
 INNER JOIN TiposCar AS TC ON R.IdCrceria=TC.IdCrceria  INNER JOIN Terceros AS NP ON  R.IdPropietario=NP.IdTercero
 INNER JOIN EstadoVeh AS EV ON R.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 LEFT JOIN Terceros AS NPV ON R.NitProv=NPV.IdTercero
 LEFT JOIN CentrosServ AS CS ON R.CdCenSer =CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON R.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 WHERE R.IdMarca LIKE ISNULL(@pmIdMarca,'%')  AND R.IdCrceria LIKE ISNULL(@pmIdCrceria,'%')
 AND Modelo LIKE ISNULL(@pmModelo,'%')  AND IdPropietario LIKE ISNULL(@pmIdPropietario,'%')
 AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (R.Inactivo=ISNULL(@pmInactivo,0) or R.Inactivo=ISNULL(@pmInactivo,1))
 AND (FecCompra>=ISNULL(@pmFecComIni,CAST('19100101' AS SMALLDATETIME)) AND FecCompra<=ISNULL(@pmFecComFin,CAST('20781230' AS SMALLDATETIME)))
ORDER BY  IdRemque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRecibos_Cr] @pmTipDoc VARCHAR(3),@pmReciboIni INT,@pmReciboFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS Tip_Doc,TipoDoc,Recibo,R.IdCia AS CodCia,Compania,R.Fecha AS FechaRec,FecPago,R.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,R.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrRecibido,VrSubTotal,VrDescuento,VrRetencion,VrReteICA,VrReteIVA
,VrOtros,VrOtrDcto,VrAhorro,VrPagosMas,VrNeto,VrAplicado,VrAnticipo,VrEfectivo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv
,R.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,R.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,IdCajero,UC.Usuario AS NomCajero
,R.IdVend AS NitVend,V.RazonSocial AS Vendedor,TarifaCom,R.IdLocal AS CdCiudad,LR.Localidad AS CiudadRecibo,EnEfectivo,CdForma,R.NumForma AS RecNumForma,DetallePago
,CdBanco,BR.Banco AS RecBanco,R.CdCta AS RecCodCta,R.Referencia AS RecReferencia,pVehiculo,CdConductor,TC.RazonSocial AS Conductor,TipDcm,R.Documento AS NumDocmto,IdCiaDcm
,Modalidad,VrReteCREE,TarifaRtc,CodTarRtc,VrDevFact,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
,TimeSys,R.FecUpdate AS Fec_Update,IdCiaCrea,R.IdUsuario AS IdUsuari,U.Usuario AS NombreUsuario,Leyenda
--detalle de pagos
,Item,P.IdForma AS CdForma,FormaPago,Detalle,VrPagado,VrCambio,EsCaja,P.IdBanco AS PagCdBanco,BF.Banco AS PagBanco,NitCliente,PC.RazonSocial AS NomCliente
,CdAgencia,PA.Agencia AS PagNomAgencia,PA.CodAgencia AS PagCodAgencia,PA.Referencia AS PagRefeAgencia,P.NumForma AS PagNumForma,FecForma,CtaForma,Beneficiario,Referncia1
,P.Referncia2 AS PagReferencia2,CdLocal,LF.Localidad AS CiudadPlaza,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CliCodBanco,BC.Banco AS CliBanco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_Recibos AS R  INNER JOIN Trn_Pagos AS P ON R.TipDoc=P.TipDoc AND R.Recibo=P.Documento AND R.IdCia=P.IdCia
 INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero  INNER JOIN Terceros AS V ON R.IdVend=V.IdTercero
 INNER JOIN Agencias  AS A ON R.IdAgencia=A.IdAgencia
 INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia  INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN adm_Usuarios AS UC ON R.IdCajero=UC.IdUsuario 
 INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto 
 INNER JOIN Formaspago AS FP ON P.IdForma=FP.IdForma INNER JOIN Bancos AS BF ON P.IdBanco=BF.IdBanco
 INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN Localidades AS LR ON R.IdLocal=LR.IdLocal LEFT JOIN SubCentros AS SC ON R.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposCom AS TCM ON R.TipCom=TCM.IdCom
 LEFT JOIN Bancos AS BR ON R.CdBanco=BR.IdBanco LEFT JOIN Terceros AS TC ON R.CdConductor=TC.IdTercero
 LEFT JOIN Terceros AS PC ON P.NitCliente=PC.IdTercero  LEFT JOIN Agencias  AS PA ON P.CdAgencia=PA.IdAgencia
 LEFT JOIN Localidades AS LF ON P.CdLocal=LF.IdLocal 
 WHERE R.TipDoc=@pmTipDoc AND Recibo BETWEEN @pmReciboIni AND @pmReciboFin AND R.IdCia=@pmIdCia
 ORDER BY Recibo,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcr_Cr] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipFact,D.Factura AS NumFactura
,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion
,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,D.VrImpCarbono,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet
,VrReteCREE,TarifaRtc,CodTarRtc,D.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,D.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,TarifaCom,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,Pedido,IdCiaPed,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem
,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCot,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,ZonaFrontera,VrImpCons,BaseIvaObsq,VrIvaObsequio,DevMasivo,NumRecibo,CdCiaRec
,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fec_Add,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--Detalles
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,KA.NContrato AS KarAgeContrato,KA.Referencia AS KarAgeRefencia,CdLocal,KL.Localidad AS KarCiudad
,CdCCosto,KC.CCosto AS Kar_Ccosto,K.CdSubCos AS CodSubCos,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,K.Cotizacion AS KarNumCotiza,K.IdCiaCot AS KarCiaCotiza,K.Remision AS KarRemision,K.IdCiaRem AS KarCiaRem
,K.Factura AS KarFactura,TipDocDev,NumDocDev,K.IdVend AS KarNitVend,KV.RazonSocial AS KarVendedor,K.Comision AS KarTarifaCom,CdOperario,KO.RazonSocial AS NomOperario,ComisnOper,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase
,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
,CdMngra,NumInicial,NumFinal
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5
 FROM Trn_DevFcr AS D  INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos 
 LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia LEFT JOIN Localidades AS KL ON K.CdLocal=KL.IdLocal
 LEFT JOIN Terceros AS KV ON K.IdVend=KV.IdTercero  LEFT JOIN Terceros AS KO ON K.CdOperario=KO.IdTercero
 LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom
 WHERE TipDev=@pmTipDev  AND Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Devolucion,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdLocEnv VARCHAR(8)=Null
,@pmModalidad VARCHAR(10)=Null,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente
,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos
,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,D.VrImpCarbono,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,D.IdCCosto AS IdCenCost,CCosto,D.IdSubCos AS IdSubCent,SubCosto
,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,Modalidad,ModdDev,ZonaFrontera,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,DevMasivo,NumRecibo,CdCiaRec
,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia,CdFntePago,FuentePago
 FROM Trn_DevFcr AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos 
 LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipDev LIKE ISNULL(@pmTipDev ,'%') 
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND D.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND ModdDev LIKE ISNULL(@pmModdDev,'%')
 ORDER BY D.IdCia,Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRecibosRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdCajero VARCHAR(11)=Null
,@pmIdVend VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null,@pmEnEfectivo BIT=Null,@pmModalidad VARCHAR(10)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,Recibo,R.IdCia AS CodCia,Compania,Fecha,FecPago,R.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,R.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrRecibido,VrSubTotal,VrDescuento,VrRetencion,VrReteICA,VrReteIVA
,VrOtros,VrOtrDcto,VrAhorro,VrPagosMas,VrNeto,VrAplicado,VrAnticipo,VrEfectivo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv
,R.IdCCosto AS IdCenCost,CCosto,R.IdSubCos AS IdSubCent,SubCosto,IdCajero,UC.Usuario AS NomCajero
,R.IdVend AS NitVend,V.RazonSocial AS Vendedor,TarifaCom,R.IdLocal AS CdCiudad,LR.Localidad AS CiudadRecibo,EnEfectivo,CdForma,NumForma,DetallePago
,CdBanco,BR.Banco AS RecBanco,CdCta,R.Referencia AS Referncia,pVehiculo,CdConductor,TC.RazonSocial AS Conductor,TipDcm,Documento,IdCiaDcm
,Modalidad,VrReteCREE,TarifaRtc,CodTarRtc,VrDevFact,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
,TimeSys,R.FecUpdate AS Fec_Update,IdCiaCrea,R.IdUsuario AS IdUsuari,U.Usuario AS NombreUsuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CliCodBanco,BC.Banco AS CliBanco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia,CdFntePago,FuentePago
 FROM Trn_Recibos AS R  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero  INNER JOIN Terceros AS V ON R.IdVend=V.IdTercero
 INNER JOIN Agencias  AS A ON R.IdAgencia=A.IdAgencia INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia  INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario  INNER JOIN adm_Usuarios AS UC ON R.IdCajero=UC.IdUsuario 
 INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Localidades AS LR ON R.IdLocal=LR.IdLocal LEFT JOIN SubCentros AS SC ON R.IdSubCos=SC.IdSubCos
 LEFT JOIN Bancos AS BR ON R.CdBanco=BR.IdBanco LEFT JOIN Terceros AS TC ON R.CdConductor=TC.IdTercero
 LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 WHERE  TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND R.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND R.IdVend LIKE ISNULL(@pmIdVend,'%') AND IdCajero LIKE ISNULL(@pmIdCajero,'%')
 AND R.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY R.IdCia,Recibo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_MovClientes_Dfc] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 INSERT INTO tm_MovClientes (tmEst,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmIdVend,tmCargos,tmAbonos,tmTimeSys,tmFecVence,tmCdConcepto,tmModalidad,tmDetalle,tmReferencia
,tmTipRef,tmDocRef,tmIdCiaRef,tmCdLocal,tmNumForma,tmCdBanco,tmBeneficiario,tmCdForma,tmEnEfectivo,tmTotalAbono,tmVrAfavor,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev,tmIdUsuario,tmSanClie,tmSanClieCia,tmSanAge,tmSanAgeCia)
 SELECT @pmtmEst,TipDev,Devolucion,IdCia,0,Fecha,IdCliente,IdAgencia,IdVend,0,VrNeto,TimeSys,FecDoc,IdConcepto,Modalidad,Observacion,ModdDev
,TipDoc,Factura,IdCiaDoc,IdLocEnv,'','0','','0',0,0,0,TipCom,Comprobante,IdCiaCom,0,0,Fecha,IdUsuario,0,0,0,0
 FROM Trn_DevFcr
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND DevMasivo<=0 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySalidas_Cr] @pmTipDoc VARCHAR(3),@pmSalidaIni INT,@pmSalidaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT S.TipDoc AS Tipo_Doc,TipoDoc,Salida,S.IdCia AS CdCia,Compania,S.Fecha AS FechaDoc,S.IdConcepto AS CdConcepto
,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrSubTotal,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,S.TarifaIva AS TarifIva
,S.TipOrd AS TipoOrden,S.NumOrden AS Num_Orden,S.IdCiaOrd AS CdCiaOrden,S.IdCCosto AS CdCentCosto,CC.CCosto AS CentCosto,S.IdSubCos AS CdSubCenCost,SC.SubCosto AS SubcCosto
,S.pVehiculo AS PlacaVeh,CdConductor,CDT.RazonSocial AS NomConductor,S.NContrato AS NumContrato,IdCiaCont
,NitCliente,CLI.RazonSocial AS NomCliente,S.CdAgencia AS IdAgncia,Agencia,CdDep,ModRequis,DirEntrega,IdLocEnt,LE.Localidad AS CiuEntrega,GuiaComb,CdCiaGuia,NumDotac,DiaVigDot,FecVigDot
,Modalidad,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado
,S.TimeSys AS Fec_Add,S.FecUpdate AS Fec_Update,IdCiaCrea,S.IdUsuario AS CdUsuario,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--Detalle
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,K.IdTercero AS KarNitTercero,KT.RazonSocial AS KarNomTercero,K.CdAgencia AS KarIdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,CdLocal,KL.Localidad AS KarCiudad,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,K.TipOrd AS KarTipOrden,K.NumOrden AS KarNumOrden,K.IdCiaOrd AS KarCiaOrden,Cotizacion,IdCiaCot
,Remision,IdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal
,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,SG.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,Precio1,Precio2,Precio3,Precio4,Precio5,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong
 FROM Trn_Salidas AS S INNER JOIN Trn_Kardex AS K ON S.TipDoc=K.TipDoc AND S.Salida=K.Documento AND S.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON S.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON S.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON S.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON S.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON S.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON S.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd INNER JOIN Terceros AS KT ON K.IdTercero=KT.IdTercero
 INNER JOIN SubGrupos AS SG ON PM.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN Localidades AS KL ON K.CdLocal=KL.IdLocal
 LEFT JOIN SubCentros AS SC ON S.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposCom AS TC ON S.TipCom=TC.IdCom
 LEFT JOIN Terceros AS CLI ON S.NitCliente=CLI.IdTercero
 LEFT JOIN Terceros AS CDT ON S.CdConductor=CDT.IdTercero
 LEFT JOIN Agencias AS A ON S.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LE ON S.IdLocEnt=LE.IdLocal
 WHERE S.TipDoc=@pmTipDoc AND Salida BETWEEN @pmSalidaIni AND  @pmSalidaFin AND S.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Salida,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySalidasRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdLocEnt VARCHAR(8)=Null,@pmModalidad VARCHAR(10)=Null
,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,Salida,S.IdCia AS CdCia,Compania,Fecha,S.IdConcepto AS CdConcepto,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrSubTotal
,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,TarifaIva,TipOrd,NumOrden,IdCiaOrd,GuiaComb,CdCiaGuia,S.IdCCosto AS CdCentCosto,CCosto,S.IdSubCos AS CdSubCenCost,SubCosto
,pVehiculo,CdConductor,CDT.RazonSocial AS NomConductor,S.NContrato AS NumContrato,IdCiaCont,NitCliente,CLI.RazonSocial AS NomCliente
,S.CdAgencia AS IdAgncia,Agencia,CdDep,ModRequis,DirEntrega,IdLocEnt,LE.Localidad AS CiuEntrega,NumDotac,DiaVigDot,FecVigDot
,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado
,TimeSys,S.FecUpdate AS Fec_Update,IdCiaCrea,S.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
 FROM Trn_Salidas AS S INNER JOIN Companias AS CN ON S.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON S.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON S.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON S.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON S.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN SubCentros AS SC ON S.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS CLI ON S.NitCliente=CLI.IdTercero
 LEFT JOIN Terceros AS CDT ON S.CdConductor=CDT.IdTercero
 LEFT JOIN Agencias AS A ON S.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LE ON S.IdLocEnt=LE.IdLocal
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND S.IdCia LIKE ISNULL(@pmIdCia,'%%') AND S.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND S.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND S.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')  AND IdLocEnt LIKE ISNULL(@pmIdLocEnt,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY S.IdCia,Salida
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsSalidas] @pmTipDoc VARCHAR(3),@pmSalida INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16)
,@pmVrSubTotal MONEY,@pmVrImpuesto MONEY,@pmVrCostos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmBaseImp MONEY,@pmTarifaIva DECIMAL(14,4),@pmCodTarIva VARCHAR(4)
,@pmTipOrd VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmModalidad VARCHAR(10)
,@pmCdConductor VARCHAR(16),@pmCdDep VARCHAR(4),@pmNContrato INT,@pmIdCiaCont CHAR(2),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmModRequis VARCHAR(10)
,@pmDirEntrega VARCHAR(250),@pmIdLocEnt VARCHAR(8),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmGuiaComb INT,@pmCdCiaGuia CHAR(2),@pmNumDotac INT,@pmDiaVigDot INT,@pmFecVigDot SMALLDATETIME
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Salidas (TipDoc,Salida,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,TarifaIva,CodTarIva,TipOrd,NumOrden,IdCiaOrd,IdCCosto,IdSubCos
,pVehiculo,CdConductor,CdDep,NContrato,IdCiaCont ,NitCliente,CdAgencia,ModRequis,DirEntrega,IdLocEnt,GuiaComb,CdCiaGuia
,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,NumDotac,DiaVigDot,FecVigDot)
 VALUES (@pmTipDoc,@pmSalida,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdTercero,@pmVrSubTotal,@pmVrImpuesto,@pmVrCostos,@pmVrNeto,@pmCantidad,@pmBaseImp,@pmTarifaIva
,@pmCodTarIva,@pmTipOrd,@pmNumOrden,@pmIdCiaOrd,@pmIdCCosto,@pmIdSubCos,@pmpVehiculo
,@pmCdConductor,@pmCdDep,@pmNContrato,@pmIdCiaCont,@pmNitCliente,@pmCdAgencia,@pmModRequis,@pmDirEntrega,@pmIdLocEnt,@pmGuiaComb,@pmCdCiaGuia
,@pmModalidad,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado
,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNumDotac,@pmDiaVigDot,@pmFecVigDot)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySalidasLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmSalidaIni INT=Null,@pmSalidaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT Salida,IdCia,Fecha,S.IdConcepto AS CdConcepto,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrSubTotal,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp,TarifaIva,CodTarIva
,TipOrd,NumOrden,IdCiaOrd,GuiaComb,CdCiaGuia,S.IdCCosto AS CdCcosto,CCosto,IdSubCos,pVehiculo,CdConductor,CdDep,NContrato,IdCiaCont ,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,ModRequis,DirEntrega,IdLocEnt
,Modalidad,NumDotac,DiaVigDot,FecVigDot,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate
,IdCiaCrea,S.IdUsuario AS IdUsuari,Usuario,TipDoc
 FROM Trn_Salidas AS S INNER JOIN Terceros AS T ON S.IdTercero=T.IdTercero
 INNER JOIN  Conceptos AS C ON S.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON S.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON  S.IdCCosto=CC.IdCCosto
 LEFT JOIN Terceros AS CL ON S.NitCliente=CL.IdTercero
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND Salida BETWEEN ISNULL(@pmSalidaIni,0) AND ISNULL(@pmSalidaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND S.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND S.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND S.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Salida
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpSalidas] @pmTipDoc VARCHAR(3),@pmSalida INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16),@pmVrSubTotal MONEY
,@pmVrImpuesto MONEY,@pmVrCostos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmBaseImp MONEY,@pmTarifaIva DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmTipOrd VARCHAR(3)
,@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmModalidad VARCHAR(10)
,@pmCdConductor VARCHAR(16),@pmCdDep VARCHAR(4),@pmNContrato INT,@pmIdCiaCont CHAR(2),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmModRequis VARCHAR(10)
,@pmDirEntrega VARCHAR(250),@pmIdLocEnt VARCHAR(8),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmGuiaComb INT,@pmCdCiaGuia CHAR(2),@pmNumDotac INT,@pmDiaVigDot INT,@pmFecVigDot SMALLDATETIME,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Salidas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdTercero=@pmIdTercero,VrSubTotal=@pmVrSubTotal,VrImpuesto=@pmVrImpuesto,VrCostos=@pmVrCostos,VrNeto=@pmVrNeto,Cantidad=@pmCantidad
,BaseImp=@pmBaseImp,TarifaIva=@pmTarifaIva,CodTarIva=@pmCodTarIva,TipOrd=@pmTipOrd,NumOrden=@pmNumOrden,IdCiaOrd=@pmIdCiaOrd,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,pVehiculo=@pmpVehiculo
,CdConductor=@pmCdConductor,CdDep=@pmCdDep,NContrato=@pmNContrato,IdCiaCont=@pmIdCiaCont,NitCliente=@pmNitCliente,CdAgencia=@pmCdAgencia,ModRequis=@pmModRequis
,DirEntrega=@pmDirEntrega,IdLocEnt=@pmIdLocEnt,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev
,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,GuiaComb=@pmGuiaComb,CdCiaGuia=@pmCdCiaGuia,NumDotac=@pmNumDotac,DiaVigDot=@pmDiaVigDot,FecVigDot=@pmFecVigDot,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Salida=@pmSalida AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySalidas] @pmTipDoc VARCHAR(3),@pmSalida INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Salida,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,VrImpuesto,VrCostos,VrNeto,Cantidad,BaseImp
,TarifaIva,CodTarIva,TipOrd,NumOrden,IdCiaOrd,IdCCosto,IdSubCos,pVehiculo,Modalidad
,CdConductor,CdDep,NContrato,IdCiaCont ,NitCliente,CdAgencia,ModRequis,DirEntrega,IdLocEnt,GuiaComb,CdCiaGuia,OrigenAdd
,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,NumDotac,DiaVigDot,FecVigDot,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Salidas
 WHERE TipDoc=@pmTipDoc AND Salida=@pmSalida AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDevFcrLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmDevolucionIni INT=Null,@pmDevolucionFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT Devolucion,IdCia,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion
,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrImpCarbono,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos
,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,IdLocEnv,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio
,DevMasivo,NumRecibo,CdCiaRec,Modalidad,ModdDev,ZonaFrontera,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario,TipDev
 FROM Trn_DevFcr AS D  INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON D.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 
 WHERE TipDev=@pmTipDev  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND Devolucion BETWEEN ISNULL(@pmDevolucionIni,0) AND ISNULL(@pmDevolucionFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND ModdDev LIKE ISNULL(@pmModdDev,'%') 
 ORDER BY IdCia,Devolucion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA
,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos,IdVend,TarifaCom
,CodTarCom,IdLocEnv,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,Modalidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom
,Observacion,IdEstado,ZonaFrontera,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,VrImpCarbono,DevMasivo,NumRecibo,CdCiaRec,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_DevFcr
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryDevFcrAcu] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
AS
 SELECT IdCliente,IdAgencia,IdCia,SUM(VrNeto) AS STOTNET
 FROM Trn_DevFcr
 WHERE YEAR(Fecha)=@pmnAnno AND MONTH(Fecha)=@pmnMes AND DevMasivo<=0 AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 GROUP BY  IdCliente,IdAgencia,IdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4)
,@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocEnv VARCHAR(8),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmCotizacion INT
,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10),@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmVrImpCons MONEY,@pmVrReteCREE MONEY
,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY,@pmDevMasivo INT,@pmNumRecibo INT,@pmCdCiaRec CHAR(2),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_DevFcr SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,Factura=@pmFactura,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA
,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrCostos=@pmVrCostos,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrFaltantes=@pmVrFaltantes,VrAnticipos=@pmVrAnticipos,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,CantPuntos=@pmCantPuntos,BaseImp=@pmBaseImp
,BaseRet=@pmBaseRet,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,IdLocEnv=@pmIdLocEnv,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,Cotizacion=@pmCotizacion,IdCiaCot=@pmIdCiaCot
,FecPedido=@pmFecPedido,Modalidad=@pmModalidad,ModdDev=@pmModdDev,PlacaVehic=@pmPlacaVehic,KmtVehic=@pmKmtVehic,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,VrImpCons=@pmVrImpCons,FecUpdate=@pmFecUpdate
,VrReteCREE=@pmVrReteCREE,TarifaRtc=@pmTarifaRtc,CodTarRtc=@pmCodTarRtc,BaseIvaObsq=@pmBaseIvaObsq,VrIvaObsequio=@pmVrIvaObsequio,VrImpCarbono=@pmVrImpCarbono,DevMasivo=@pmDevMasivo,NumRecibo=@pmNumRecibo,CdCiaRec=@pmCdCiaRec
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDevFcr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY
,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY
,@pmBaseRet MONEY,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocEnv VARCHAR(8),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10)
,@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmVrImpCons MONEY,@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmBaseIvaObsq MONEY,@pmVrIvaObsequio MONEY,@pmVrImpCarbono MONEY
,@pmDevMasivo INT,@pmNumRecibo INT,@pmCdCiaRec CHAR(2),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DevFcr (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,IdLocEnv
,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,ZonaFrontera,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrImpCons,VrReteCREE,TarifaRtc,CodTarRtc,BaseIvaObsq,VrIvaObsequio,VrImpCarbono,DevMasivo,NumRecibo,CdCiaRec)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmFactura,@pmIdCiaDoc,@pmFecDoc,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrCostos,@pmVrSobretasa,@pmVrImpGlobal,@pmVrFaltantes
,@pmVrAnticipos,@pmVrNeto,@pmCantidad,@pmCantPuntos,@pmBaseImp,@pmBaseRet,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmIdLocEnv,@pmPedido,@pmIdCiaPed,@pmRemision,@pmIdCiaRem,@pmCotizacion,@pmIdCiaCot,@pmFecPedido,@pmModalidad,@pmModdDev,@pmPlacaVehic,@pmKmtVehic,@pmZonaFrontera,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom
,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrImpCons,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc,@pmBaseIvaObsq,@pmVrIvaObsequio,@pmVrImpCarbono,@pmDevMasivo,@pmNumRecibo,@pmCdCiaRec)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsVehRemolq] @pmIdRemque VARCHAR(10),@pmIdMarca VARCHAR(4),@pmIdColor VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmNEjes INT,@pmnLlantas INT,@pmAltura DECIMAL(14,4)
,@pmAncho DECIMAL(14,4),@pmLargo DECIMAL(14,4),@pmVlumen DECIMAL(14,4),@pmPesoKg DECIMAL(14,4),@pmCapneta DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmNumComp INT,@pmCapcComp VARCHAR(50),@pmNumSerial VARCHAR(30)
,@pmIdPropietario VARCHAR(16),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME,@pmVidaUtil INT,@pmVrComercial MONEY,@pmVrAvaludo MONEY,@pmVrAsegurado MONEY,@pmKilmetraje INT,@pmEquipoAdic VARCHAR(50)
,@pmDescripcion VARCHAR(100),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTarjetaProp VARCHAR(30),@pmFecTarjProp  SMALLDATETIME
,@pmVigTarjProp SMALLDATETIME,@pmConfEjes VARCHAR(5),@pmHidrostatica VARCHAR(30),@pmFecPruebaHid SMALLDATETIME,@pmVigPruebaHid SMALLDATETIME,@pmkmRuta INT,@pmkmOtro INT,@pmKingpin VARCHAR(50),@pmFecKingpin SMALLDATETIME
,@pmVigKingpin SMALLDATETIME,@pmArcFotoRem VARCHAR(50),@pmFecVigExtintor SMALLDATETIME,@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO VehRemolq (IdRemque,IdMarca,IdColor,IdCrceria,Modelo,NEjes,nLlantas,ConfEjes,Altura,Ancho,Largo,Vlumen,PesoKg,Capneta,UndCapc,NumComp,CapcComp,NumSerial,IdPropietario,NitProv,FecCompra,VidaUtil,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje
,EquipoAdic,Descripcion,CdCenSer,CdLocal,Ubicacion,Observacion,IdEstado,Inactivo,FecAdd,IdUsuario,TarjetaProp,FecTarjProp,VigTarjProp,Hidrostatica,FecPruebaHid,VigPruebaHid,kmRuta,kmOtro,Kingpin,FecKingpin,VigKingpin,ArcFotoRem,FecVigExtintor)
 VALUES (@pmIdRemque,@pmIdMarca,@pmIdColor,@pmIdCrceria,@pmModelo,@pmNEjes,@pmnLlantas,@pmConfEjes,@pmAltura,@pmAncho,@pmLargo,@pmVlumen,@pmPesoKg,@pmCapneta,@pmUndCapc,@pmNumComp,@pmCapcComp,@pmNumSerial,@pmIdPropietario
,@pmNitProv,@pmFecCompra,@pmVidaUtil,@pmVrComercial,@pmVrAvaludo,@pmVrAsegurado,@pmKilmetraje,@pmEquipoAdic,@pmDescripcion,@pmCdCenSer,@pmCdLocal,@pmUbicacion,@pmObservacion,@pmIdEstado,@pmInactivo,@pmFecAdd,@pmIdUsuario
,@pmTarjetaProp,@pmFecTarjProp,@pmVigTarjProp,@pmHidrostatica,@pmFecPruebaHid,@pmVigPruebaHid,@pmkmRuta,@pmkmOtro,@pmKingpin,@pmFecKingpin,@pmVigKingpin,@pmArcFotoRem,@pmFecVigExtintor)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpVehRemolq] @pmIdRemque VARCHAR(10),@pmIdMarca VARCHAR(4),@pmIdColor VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmNEjes INT,@pmnLlantas INT,@pmAltura DECIMAL(14,4),@pmAncho DECIMAL(14,4),@pmLargo DECIMAL(14,4)
,@pmVlumen DECIMAL(14,4),@pmPesoKg DECIMAL(14,4),@pmCapneta DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmNumComp INT,@pmCapcComp VARCHAR(50),@pmNumSerial VARCHAR(30),@pmIdPropietario VARCHAR(16),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME
,@pmVidaUtil INT,@pmVrComercial MONEY,@pmVrAvaludo MONEY,@pmVrAsegurado MONEY,@pmKilmetraje INT,@pmEquipoAdic VARCHAR(50),@pmDescripcion VARCHAR(100),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmObservacion VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTarjetaProp VARCHAR(30),@pmFecTarjProp  SMALLDATETIME,@pmVigTarjProp SMALLDATETIME,@pmConfEjes VARCHAR(5),@pmHidrostatica VARCHAR(30),@pmFecPruebaHid SMALLDATETIME,@pmVigPruebaHid SMALLDATETIME,@pmkmRuta INT,@pmkmOtro INT
,@pmKingpin VARCHAR(50),@pmFecKingpin SMALLDATETIME,@pmVigKingpin SMALLDATETIME,@pmArcFotoRem VARCHAR(50),@pmFecVigExtintor SMALLDATETIME,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE VehRemolq SET IdMarca=@pmIdMarca,IdColor=@pmIdColor,IdCrceria=@pmIdCrceria,Modelo=@pmModelo,NEjes=@pmNEjes,nLlantas=@pmnLlantas,ConfEjes=@pmConfEjes,Altura=@pmAltura,Ancho=@pmAncho,Largo=@pmLargo,Vlumen=@pmVlumen,PesoKg=@pmPesoKg,Capneta=@pmCapneta,UndCapc=@pmUndCapc
,NumComp=@pmNumComp,CapcComp=@pmCapcComp,NumSerial=@pmNumSerial,IdPropietario=@pmIdPropietario,NitProv=@pmNitProv,FecCompra=@pmFecCompra,VidaUtil=@pmVidaUtil,VrComercial=@pmVrComercial,VrAvaludo=@pmVrAvaludo,VrAsegurado=@pmVrAsegurado,Kilmetraje=@pmKilmetraje
,EquipoAdic=@pmEquipoAdic,Descripcion=@pmDescripcion,CdCenSer=@pmCdCenSer,CdLocal=@pmCdLocal,Ubicacion=@pmUbicacion,Observacion=@pmObservacion,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FecUpdate=@pmFecUpdate,TarjetaProp=@pmTarjetaProp,FecTarjProp=@pmFecTarjProp,VigTarjProp=@pmVigTarjProp
,Hidrostatica=@pmHidrostatica,FecPruebaHid=@pmFecPruebaHid,VigPruebaHid=@pmVigPruebaHid,kmRuta=@pmkmRuta,kmOtro=@pmkmOtro,Kingpin=@pmKingpin,FecKingpin=@pmFecKingpin,VigKingpin=@pmVigKingpin,ArcFotoRem=@pmArcFotoRem,FecVigExtintor=@pmFecVigExtintor
 WHERE IdRemque=@pmIdRemque
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehRemolq] @pmIdRemque VARCHAR(10)
 AS 
 SELECT IdRemque,IdMarca,IdColor,IdCrceria,Modelo,NEjes,nLlantas,Altura,Ancho,Largo,Vlumen,PesoKg,Capneta,UndCapc,NumComp,CapcComp
,NumSerial,IdPropietario,NitProv,FecCompra,VidaUtil,VrComercial,VrAvaludo,VrAsegurado,Kilmetraje,EquipoAdic,Descripcion,CdCenSer,CdLocal,Ubicacion
,Observacion,IdEstado,Inactivo,FecAdd,FecUpdate,IdUsuario,TarjetaProp,FecTarjProp,VigTarjProp,ConfEjes,Hidrostatica,FecPruebaHid,VigPruebaHid
,kmRuta,kmOtro,Kingpin,FecKingpin,VigKingpin,ArcFotoRem,FecVigExtintor
 FROM VehRemolq
 WHERE IdRemque=@pmIdRemque
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
 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)
 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)
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
 FROM Trn_TraOrdenManif
 WHERE TipDoc=@pmTipDoc AND OrdPago=@pmOrdPago AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryRecibosLta] @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmReciboIni INT=Null,@pmReciboFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmIdLocal VARCHAR(8)=Null,@pmEnEfectivo BIT=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT Recibo,IdCia,Fecha,R.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,FecPago,VrRecibido,VrSubTotal,VrDescuento,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto,VrAhorro,VrPagosMas,VrNeto
,VrAplicado,VrAnticipo,VrEfectivo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdCajero,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,R.IdLocal AS CdCiudad
,EnEfectivo,CdForma,NumForma,DetallePago,CdBanco,CdCta,Referencia,pVehiculo,CdConductor,TipDcm,Documento,IdCiaDcm,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,VrReteCREE,TarifaRtc,CodTarRtc,VrDevFact,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Recibos AS R INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON R.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Recibo BETWEEN ISNULL(@pmReciboIni,0) AND ISNULL(@pmReciboFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%')  AND R.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 ORDER BY IdCia,Recibo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryRecibos] @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Recibo,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FecPago,VrRecibido,VrSubTotal,VrDescuento,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto
,VrAhorro,VrPagosMas,VrNeto,VrAplicado,VrAnticipo,VrEfectivo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,IdCCosto
,IdSubCos,IdCajero,IdVend,TarifaCom,CodTarCom,IdLocal,EnEfectivo,CdForma,NumForma,DetallePago,CdBanco,CdCta,Referencia,pVehiculo,CdConductor
,TipDcm,Documento,IdCiaDcm,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,VrReteCREE,TarifaRtc,CodTarRtc,VrDevFact
,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Recibos
 WHERE TipDoc=@pmTipDoc AND Recibo=@pmRecibo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsRecibos] @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFecPago SMALLDATETIME,@pmVrRecibido MONEY,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY
,@pmVrReteIVA MONEY,@pmVrOtros MONEY,@pmVrOtrDcto MONEY,@pmVrAhorro MONEY,@pmVrPagosMas MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmVrAnticipo MONEY,@pmVrEfectivo MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarRet VARCHAR(4)
,@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdCajero VARCHAR(11),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocal VARCHAR(8),@pmEnEfectivo BIT,@pmCdForma VARCHAR(4),@pmNumForma VARCHAR(20),@pmDetallePago VARCHAR(250),@pmCdBanco VARCHAR(4),@pmCdCta VARCHAR(4)
,@pmReferencia VARCHAR(50),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDcm CHAR(2),@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmVrDevFact MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Recibos (TipDoc,Recibo,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FecPago,VrRecibido,VrSubTotal,VrDescuento,VrRetencion,VrReteICA,VrReteIVA,VrOtros,VrOtrDcto,VrAhorro,VrPagosMas,VrNeto,VrAplicado,VrAnticipo,VrEfectivo,BaseRet,BaseIca,BaseRiv,TarifaRet,TarifaIca,TarifaRiv,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdCajero,IdVend,TarifaCom,CodTarCom,IdLocal,EnEfectivo
,CdForma,NumForma,DetallePago,CdBanco,CdCta,Referencia,pVehiculo,CdConductor,TipDcm,Documento,IdCiaDcm,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,VrReteCREE,TarifaRtc,CodTarRtc,VrDevFact)
 VALUES (@pmTipDoc,@pmRecibo,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmFecPago,@pmVrRecibido,@pmVrSubTotal,@pmVrDescuento,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrOtros,@pmVrOtrDcto,@pmVrAhorro,@pmVrPagosMas,@pmVrNeto,@pmVrAplicado,@pmVrAnticipo,@pmVrEfectivo,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaRet
,@pmTarifaIca,@pmTarifaRiv,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmIdCCosto,@pmIdSubCos,@pmIdCajero,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmIdLocal,@pmEnEfectivo,@pmCdForma,@pmNumForma,@pmDetallePago,@pmCdBanco,@pmCdCta,@pmReferencia,@pmpVehiculo,@pmCdConductor,@pmTipDcm,@pmDocumento,@pmIdCiaDcm
,@pmModalidad,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmVrReteCREE,@pmTarifaRtc,@pmCodTarRtc,@pmVrDevFact)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpRecibos] @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFecPago SMALLDATETIME,@pmVrRecibido MONEY,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrOtros MONEY,@pmVrOtrDcto MONEY
,@pmVrAhorro MONEY,@pmVrPagosMas MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmVrAnticipo MONEY,@pmVrEfectivo MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdCajero VARCHAR(11),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocal VARCHAR(8),@pmEnEfectivo BIT,@pmCdForma VARCHAR(4),@pmNumForma VARCHAR(20),@pmDetallePago VARCHAR(250),@pmCdBanco VARCHAR(4),@pmCdCta VARCHAR(4),@pmReferencia VARCHAR(50),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDcm CHAR(2)
,@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrReteCREE MONEY,@pmTarifaRtc DECIMAL(14,4),@pmCodTarRtc VARCHAR(4),@pmVrDevFact MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Recibos SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,FecPago=@pmFecPago,VrRecibido=@pmVrRecibido,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrOtros=@pmVrOtros,VrOtrDcto=@pmVrOtrDcto,VrAhorro=@pmVrAhorro,VrPagosMas=@pmVrPagosMas,VrNeto=@pmVrNeto
,VrAplicado=@pmVrAplicado,VrAnticipo=@pmVrAnticipo,VrEfectivo=@pmVrEfectivo,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom
,IdLocal=@pmIdLocal,EnEfectivo=@pmEnEfectivo,CdForma=@pmCdForma,NumForma=@pmNumForma,DetallePago=@pmDetallePago,CdBanco=@pmCdBanco,CdCta=@pmCdCta,IdCajero=@pmIdCajero,Referencia=@pmReferencia,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,TipDcm=@pmTipDcm,Documento=@pmDocumento,IdCiaDcm=@pmIdCiaDcm,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante
,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,VrReteCREE=@pmVrReteCREE,TarifaRtc=@pmTarifaRtc,CodTarRtc=@pmCodTarRtc,VrDevFact=@pmVrDevFact,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Recibo=@pmRecibo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetalleRes] @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdPeriodo VARCHAR(8)=Null
,@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto  VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmClaseLiq VARCHAR(10)=Null,@pmClaseCon VARCHAR(10)=Null,@pmNPrestamo INT=Null
 AS 
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumCntrato,N.KeyRegistro AS KeyReg,N.IdPeriodo AS NumPeriodo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdNom AS CodNmna,TipoNomina,N.IdDep AS CodDep,Dependencia,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcos,SubCosto,N.IdInstala AS CodIstala,Instlacion
,N.VrSalario AS SalBasico,N.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS IdEstdo,ED.Estado AS Nom_Estado,EsRetiro,NLiqRet,TipoLiq,TimeSys
,N.FecUpdate AS Fec_Update,N.IdUsuario AS IdUsuari,Usuario
--datos del detalle
,Item,D.IdConcepto AS CodConcept,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,ClaseCon,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS Tarfa,DiasCalc,DiasNov,CodFondo,FCO.Fondo AS NomFondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso
--Datos empleado
,Codigo,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,FC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,C.VrSalario AS VlrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes,SegFinMes,DchDotacion,DchCafeteria
,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj,C.IdGrupo AS CodGrup
 FROM Trn_Nomina AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala  INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco 
 INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo 
 INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo INNER JOIN Fondos AS FC ON E.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar  INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado 
 INNER JOIN  Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND  N.KeyRegistro=D.KeyRegistro
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto
 LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom  LEFT JOIN Fondos AS FCO ON D.CodFondo=FCO.IdFondo
 WHERE FecInicial>=@pmFecIni  AND FecFinal<=@pmFecFin AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND  N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND N.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND ClaseLiq LIKE ISNULL(@pmClaseLiq,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (N.NContrato>=ISNULL(@pmNContrato,0)  AND N.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-2)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (NPrestamo>=ISNULL(@pmNPrestamo,0)  AND NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
--Descuentos en vacaciones
UNION ALL
 SELECT V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.IdEmpleado,D.IdPeriodo,V.FecInicial,V.FecFinal,V.Cantidad,0,V.VrLiquida,V.VrTotDed
,C.IdNom,TipoNomina,C.IdDep,Dependencia,C.IdCCosto,CCosto,C.IdSubCos,SubCosto,C.IdInstala,Instlacion,V.VrSalario,V.pVehiculo,V.TipCom,TipoCom,V.Comprobante,V.IdCiaCom
,V.Observacion,V.IdEstado,ED.Estado,0,V.Numero,C.TipoLiquida,V.TimeSys,V.FecUpdate,V.IdUsuario,Usuario
,D.Item,D.IdConcepto,Concepto,D.Detalle,0,CASE D.IdConcepto WHEN 'DCP' THEN 0 WHEN 'DCS' THEN 0 WHEN 'FSP' THEN 0 WHEN 'FSU' THEN 0 ELSE D.Cantidad END AS CantDed,0,D.VrDevgado,D.VrDeducido,ClaseCon,'%','NOMINA',D.VrBaseLiq,D.Tarifa
,CASE D.IdConcepto WHEN 'DCP' THEN 0 WHEN 'DCS' THEN 0 WHEN 'FSP' THEN 0 WHEN 'FSU' THEN 0 ELSE D.Cantidad END AS DiasCalc,0,'0','',D.NPrestamo,D.NCuota,'VAC',D.VrBaseLiq,0
,E.Codigo,E.IdLugarCed,Localidad,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf,Profesion,E.IdClase,ClaseCuenta,NumCuenta,E.IdBanco,Banco,IdFonPen,FP.Fondo
,IdFonEps,FS.Fondo,IdFonArp,FR.Fondo,IdCajaCom,FC.Fondo,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo
,C.IdTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo,Cargo,C.IdArea,Area,C.IdClase,ClaseRiesgo,CR.Tarifa,C.VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina
,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado,ECO.Estado,C.Inactivo,C.IdJornada,Jornada,TipoTrabj,C.IdGrupo
 FROM Trn_NomVac AS V INNER JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero
 INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN Dependencias AS DPD ON C.IdDep=DPD.IdDep
 INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco 
 INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo 
 INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo INNER JOIN Fondos AS FC ON E.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado INNER JOIN EstadoDoc AS ED ON V.IdEstado=ED.IdEstado
 LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos LEFT JOIN TiposCom AS TCM ON V.TipCom=TCM.IdCom
 WHERE V.Anulado=0 
 AND (SUBSTRING(D.IdPeriodo,1,6)=SUBSTRING(CONVERT(VARCHAR(30),@pmFecIni,112),1,6) OR SUBSTRING(D.IdPeriodo,1,6)=SUBSTRING(CONVERT(VARCHAR(30),@pmFecFin,112),1,6))
 AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND V.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND V.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (V.NContrato>=ISNULL(@pmNContrato,0)  AND V.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (D.NPrestamo>=ISNULL(@pmNPrestamo,0)  AND D.NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemCancelaOst] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT CR.Fecha,CR.TipDoc AS TipFac,CR.Factura,CR.IdCia AS CdCiaFac,CF.Compania AS CiaFactura,CR.TipRem AS TipOds,CR.Remesa AS NumOrden,CR.IdCiaRem AS CdCiaOrden,CN.Compania AS CiaOrden
 ,O.FecDespacho,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor
,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,O.TipoAfiVehic,O.Modalidad,O.VrTotal,O.VrCosto,O.Cantidad AS Cant_Total,VrAnticipo
,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino,TipoOrden
,TipoResiduo,O.RemCliente,O.NomRecibido,O.NumRemesa,O.CdCiaRem,O.EstCumplido,O.NumCump,O.CdCiaCum,O.EstFactura,O.EstOrden,O.TipLiq,O.NumLiquida,O.CdCiaLiq
,O.Observacion AS OrdenObserv,CR.Observacion,CR.IdUsuario AS CdUsuario,Usuario,CR.FechaCrea,CR.Id
FROM Trn_TraRemCancela AS CR INNER JOIN Companias AS CN ON CR.IdCiaRem=CN.IdCia INNER JOIN adm_Usuarios AS U ON CR.IdUsuario=U.IdUsuario
INNER JOIN Trn_TraOrdenServ AS O ON CR.TipRem=O.TipDoc AND CR.Remesa=O.NumOrden AND CR.IdCiaRem=O.IdCia
INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero
INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo
LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
LEFT JOIN Companias AS CF ON CR.IdCia=CF.IdCia LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia
WHERE CR.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND CR.IdCiaRem LIKE ISNULL(@pmIdCia,'%%')
GO