if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsComFactura] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsComFactura_Uni]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsComFactura_Uni] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCompGastos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsCompGastos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsConcDiversos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDevEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsDevEnt] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEntradas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsEntradas] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsOpedido] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsSobDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsSobDetalle] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_SelDve]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInstm_Kdex_SelDve] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreMora]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInstm_PreMora] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Sobtasas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInstm_Sobtasas] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsZonas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsZonas] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryActivosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryActivosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFactura] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFactura_Ent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFactura_Ent] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaCmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFacturaCmp] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFacturaDoc] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFacturaDso] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFacturaLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaNit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFacturaNit] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaRef]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFacturaRef] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaSal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryComFacturaSal] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompGastos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryCompGastos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompGastosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryCompGastosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryConcDiversos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryConcDiversosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryConcDiversosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryDevEnt] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevEntLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryDevEntLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevEntRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryDevEntRel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevEnt_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryDevEnt_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_CentrosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEmp_CentrosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEntradas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEntradas] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEntradas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEntradas_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEntradasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEntradasLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEntradasNet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEntradasNet] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEntradasRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEntradasRel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinClieSaldosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryFinClieSaldosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPrestamoRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryFinPrestamoRes] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryOpedido] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedido_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryOpedido_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryOpedidoLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryOpedidoRel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPlantillasConcLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryPlantillasConcLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrySobDetalle] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobretasas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrySobretasas_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobretasas_Crr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrySobretasas_Crr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySobretasasDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrySobretasasDet] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySubCentrosDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrySubCentrosDso] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexDve]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrytm_KdexDve] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrytm_KdexEnt] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryZonas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryZonas] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryZonasDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryZonasDso] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpComFactura] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCompGastos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpCompGastos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpConcDiversos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpConcDiversos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDevEnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpDevEnt] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEntradas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpEntradas] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpOpedido] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpVehiculosRetB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpVehiculosRetB] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpZonas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpZonas] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsComFactura @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmItem INT,@pmIdProveedor VARCHAR(16),@pmIdCuenta VARCHAR(16),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmFecEmision SMALLDATETIME ,@pmFecVence SMALLDATETIME,@pmVrFactura MONEY,@pmVrAbonado MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmItemCom INT,@pmReferencia VARCHAR(50),@pmDetalle VARCHAR(100),@pmpVehiculo VARCHAR(10),@pmVehPropio BIT ,@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmEstadoApr INT AS INSERT INTO Trn_ComFactura (TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr) VALUES (@pmTipFac,@pmFactura,@pmIdCia,@pmItem,@pmIdProveedor,@pmIdCuenta,@pmTipDoc,@pmDocumento,@pmIdCiaDoc,@pmFecEmision,@pmFecVence,@pmVrFactura,@pmVrAbonado,@pmTipCom,@pmComprobante,@pmItemCom ,@pmReferencia,@pmDetalle,@pmpVehiculo,@pmVehPropio,@pmTipRef,@pmDocRef,@pmIdCiaRef,@pmEstadoApr) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsComFactura_Uni @pmIdProveedor VARCHAR(16),@pmNewProveedor VARCHAR(16) AS INSERT INTO Trn_ComFactura (TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr) SELECT TipFac,Factura,IdCia,Item,@pmNewProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura WHERE IdProveedor=@pmIdProveedor GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE paInsCompGastos @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdPlantilla VARCHAR(4),@pmIdTercero VARCHAR(16),@pmTipFact VARCHAR(3) ,@pmFactura VARCHAR(15),@pmFecFactura SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmVrTotal MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4) ,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) AS INSERT INTO Trn_CompGastos (TipCom,Comprobante,IdCia,Fecha,IdPlantilla,IdTercero,TipFact,Factura,FecFactura,FecVence,VrTotal,Anulado,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario) VALUES (@pmTipCom,@pmComprobante,@pmIdCia,@pmFecha,@pmIdPlantilla,@pmIdTercero,@pmTipFact,@pmFactura,@pmFecFactura,@pmFecVence,@pmVrTotal,@pmAnulado,@pmFecDev,@pmObservacion ,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsConcDiversos @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmGrupo VARCHAR(20),@pmEsServicio BIT,@pmDeManfsto BIT,@pmCdIva VARCHAR(4) ,@pmCdRet VARCHAR(4),@pmCdIca VARCHAR(4),@pmIdCueDeb VARCHAR(16),@pmIdCueCre VARCHAR(16),@pmInactivo BIT ,@pmCdAutoret VARCHAR(4),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11) AS INSERT INTO ConcDiversos (IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,CdRet,CdIca,CdAutoret,IdCueDeb,IdCueCre,Inactivo,FechaAdd,IdUsuario) VALUES (@pmIdConcepto,@pmConcepto,@pmGrupo,@pmEsServicio,@pmDeManfsto,@pmCdIva,@pmCdRet,@pmCdIca,@pmCdAutoret,@pmIdCueDeb,@pmIdCueCre,@pmInactivo,@pmFechaAdd,@pmIdUsuario) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsDevEnt @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmEntrada INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdProv VARCHAR(16) ,@pmFactura VARCHAR(15),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrCargos MONEY ,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmNitFletes VARCHAR(16),@pmCxPagar BIT,@pmDocEquiv VARCHAR(3),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmModalidad VARCHAR(10) ,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmVrRetFlete MONEY,@pmVrIcaFlete MONEY,@pmFletesCosto BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) AS INSERT INTO Trn_DevEnt (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Entrada,IdCiaDoc,FecDoc,IdProv,Factura,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,FletesCosto,CxPagar,DocEquiv ,BaseImp,BaseRet,CdCCosto,CdSubCos,Modalidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario) VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmEntrada,@pmIdCiaDoc,@pmFecDoc,@pmIdProv,@pmFactura,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros ,@pmVrSobretasa,@pmVrImpGlobal,@pmVrCargos,@pmVrOtrDcto,@pmVrNeto,@pmCantidad,@pmNitFletes,@pmVrRetFlete,@pmVrIcaFlete,@pmFletesCosto,@pmCxPagar,@pmDocEquiv,@pmBaseImp,@pmBaseRet,@pmCdCCosto,@pmCdSubCos,@pmModalidad,@pmModdDev,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsEntradas @pmTipDoc VARCHAR(3),@pmEntrada INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdProv VARCHAR(16),@pmFactura VARCHAR(15),@pmFechaFac SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY ,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmNitFletes VARCHAR(16),@pmTipOdc VARCHAR(3) ,@pmOCompra INT,@pmIdCiaOdc CHAR(2),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCxPagar BIT,@pmDocEquiv VARCHAR(3),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4) ,@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr 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),@pmVrRetFlete MONEY,@pmVrIcaFlete MONEY,@pmCodTarRetFle VARCHAR(4),@pmCodTarIcaFle VARCHAR(4),@pmFletesCosto BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) AS INSERT INTO Trn_Entradas (TipDoc,Entrada,IdCia,Fecha,IdConcepto,IdProv,Factura,FechaFac,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,CodTarRetFle,CodTarIcaFle,FletesCosto,TipOdc,OCompra,IdCiaOdc,MulPlazos,IdPlazo,CxPagar,DocEquiv,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva ,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CdCCosto,CdSubCos,TipEgr,Egreso,IdCiaEgr,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario) VALUES (@pmTipDoc,@pmEntrada,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdProv,@pmFactura,@pmFechaFac,@pmFechaVence,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros,@pmVrSobretasa,@pmVrImpGlobal,@pmVrCargos,@pmVrOtrDcto,@pmVrNeto,@pmCantidad,@pmNitFletes , @pmVrRetFlete,@pmVrIcaFlete,@pmCodTarRetFle,@pmCodTarIcaFle,@pmFletesCosto,@pmTipOdc,@pmOCompra,@pmIdCiaOdc,@pmMulPlazos,@pmIdPlazo,@pmCxPagar,@pmDocEquiv,@pmBaseImp,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaIva,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmCdCCosto,@pmCdSubCos,@pmTipEgr,@pmEgreso,@pmIdCiaEgr,@pmModalidad,@pmOrigenAdd ,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsOpedido @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY ,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmLugarEnvio VARCHAR(50) ,@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150) ,@pmAsignarVeh BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmListaPrec CHAR(1),@pmRefPedido VARCHAR(50),@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmNumAutoriza INT,@pmNumAutCupo INT,@pmNumAutCheq INT,@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME ,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmFechaRem SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME ,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmTipoTrans INT,@pmTipoOrden VARCHAR(3),@pmTipoModifica VARCHAR(10),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) AS INSERT INTO Trn_Opedido (TipDoc,Pedido,IdCia,Fecha,FechaVence,IdConcepto,IdCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago ,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,AsignarVeh,pVehiculo,CdConductor,CdRuta,ListaPrec,RefPedido,Modalidad,Vigencia,NumAutoriza,NumAutCupo,NumAutCheq,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem,OrigenAdd,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario) VALUES (@pmTipDoc,@pmPedido,@pmIdCia,@pmFecha,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmIdClieFact,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobretasa,@pmVrImpGlobal,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmDirEnvio,@pmIdLocEnv ,@pmLugarEnvio,@pmDiasEntraga,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmNitEmpTrans,@pmEmpTrans,@pmAsignarVeh,@pmpVehiculo,@pmCdConductor,@pmCdRuta,@pmListaPrec,@pmRefPedido,@pmModalidad,@pmVigencia,@pmNumAutoriza,@pmNumAutCupo ,@pmNumAutCheq,@pmNumAprob,@pmIdCiaApr,@pmFecAprob,@pmDetalleAprob,@pmCdUsuAprob,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmTipRem,@pmRemision,@pmIdCiaRem,@pmFechaRem,@pmOrigenAdd,@pmZonaFrontera,@pmTipoTrans,@pmTipoOrden,@pmTipoModifica,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsSobDetalle @pmIdDec VARCHAR(4),@pmNumDeclara INT,@pmItem INT,@pmConcepto VARCHAR(250) ,@pmCantidad DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrBase MONEY,@pmTarifa DECIMAL(14,4),@pmVrLiquida MONEY ,@pmVrPrecio MONEY,@pmClaseProd VARCHAR(20),@pmTarifGalon DECIMAL(14,4),@pmPorcBase DECIMAL(14,4) AS INSERT INTO Trn_SobDetalle (IdDec,NumDeclara,Item,Concepto,Cantidad,VrUnitario,VrBase,Tarifa,VrLiquida,VrPrecio,ClaseProd,TarifGalon,PorcBase) VALUES (@pmIdDec,@pmNumDeclara,@pmItem,@pmConcepto,@pmCantidad,@pmVrUnitario,@pmVrBase,@pmTarifa ,@pmVrLiquida,@pmVrPrecio,@pmClaseProd,@pmTarifGalon,@pmPorcBase) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInstm_Kdex_SelDve @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5) AS INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete ,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend ,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto ,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo ,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad) SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,Salidas,Entradas,K.IdUnd,CASE WHEN NumInicial>0 THEN VrUnitario-NumInicial ELSE VrUnitario END,CASE WHEN NumInicial>0 THEN VrPrecio-NumInicial ELSE VrPrecio END ,VrCostProm,0,TarifaIva,VrIvaEnt+VrIvaSal,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal ,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend ,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto ,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec,TipOrd,NumOrden,IdCiaOrd,VrBase,CdMoneda,VrTasaCamb ,'REM',Remision,IdCiaRem,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE paInstm_PreMora @pmtmTipDoc VARCHAR(3),@pmtmIdPrestamo INT,@pmtmIdCia CHAR(2) ,@pmtmVrCuota MONEY,@pmtmDescCuota VARCHAR(100),@pmtmVrMora MONEY,@pmtmDiasMora INT AS INSERT INTO tm_PreMora (tmTipDoc,tmIdPrestamo,tmIdCia,tmVrCuota,tmDescCuota,tmVrMora,tmDiasMora) VALUES (@pmtmTipDoc,@pmtmIdPrestamo,@pmtmIdCia,@pmtmVrCuota,@pmtmDescCuota,@pmtmVrMora,@pmtmDiasMora) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInstm_Sobtasas @pmtmIdDec VARCHAR(4),@pmtmNumDeclara INT,@pmtmAcpmCant DECIMAL(14,4) ,@pmtmAcpmBase MONEY,@pmtmAcpmValor MONEY,@pmtmAcpmTarif DECIMAL(14,4),@pmtmAcpmPrecio MONEY ,@pmtmAcpiCant DECIMAL(14,4),@pmtmAcpiBase MONEY,@pmtmAcpiValor MONEY,@pmtmAcpiTarif DECIMAL(14,4) ,@pmtmAcpiPrecio MONEY,@pmtmGcorCant DECIMAL(14,4),@pmtmGcorBase MONEY,@pmtmGcorValor MONEY ,@pmtmGcorTarif DECIMAL(14,4),@pmtmGcorPrecio MONEY,@pmtmGextCant DECIMAL(14,4),@pmtmGextBase MONEY ,@pmtmGextValor MONEY,@pmtmGextTarif DECIMAL(14,4),@pmtmGextPrecio MONEY,@pmtmGimpCant DECIMAL(14,4) ,@pmtmGimpBase MONEY,@pmtmGimpValor MONEY,@pmtmGimpTarif DECIMAL(14,4),@pmtmGimpPrecio MONEY ,@pmtmGcoxCant DECIMAL(14,4),@pmtmGcoxBase MONEY,@pmtmGcoxValor MONEY,@pmtmGcoxTarif DECIMAL(14,4) ,@pmtmGcoxPrecio MONEY,@pmtmGeoxCant DECIMAL(14,4),@pmtmGeoxBase MONEY,@pmtmGeoxValor MONEY,@pmtmGeoxTarif DECIMAL(14,4),@pmtmGeoxPrecio MONEY ,@pmtmAczfCant DECIMAL(14,4),@pmtmAczfBase MONEY,@pmtmAczfValor MONEY,@pmtmAczfTarif DECIMAL(14,4) ,@pmtmAczfPrecio MONEY ,@pmtmGnzfCant DECIMAL(14,4),@pmtmGnzfBase MONEY,@pmtmGnzfValor MONEY,@pmtmGnzfTarif DECIMAL(14,4),@pmtmGnzfPrecio MONEY AS INSERT INTO tm_Sobtasas (tmIdDec,tmNumDeclara,tmAcpmCant,tmAcpmBase,tmAcpmValor,tmAcpmTarif,tmAcpmPrecio,tmAcpiCant,tmAcpiBase,tmAcpiValor ,tmAcpiTarif,tmAcpiPrecio,tmGcorCant,tmGcorBase,tmGcorValor,tmGcorTarif,tmGcorPrecio,tmGextCant,tmGextBase,tmGextValor,tmGextTarif,tmGextPrecio ,tmGimpCant,tmGimpBase,tmGimpValor,tmGimpTarif,tmGimpPrecio,tmGcoxCant,tmGcoxBase,tmGcoxValor,tmGcoxTarif,tmGcoxPrecio,tmGeoxCant ,tmGeoxBase,tmGeoxValor,tmGeoxTarif,tmGeoxPrecio,tmAczfCant,tmAczfBase,tmAczfValor,tmAczfTarif,tmAczfPrecio,tmGnzfCant,tmGnzfBase,tmGnzfValor,tmGnzfTarif,tmGnzfPrecio) VALUES (@pmtmIdDec,@pmtmNumDeclara,@pmtmAcpmCant,@pmtmAcpmBase,@pmtmAcpmValor,@pmtmAcpmTarif,@pmtmAcpmPrecio,@pmtmAcpiCant ,@pmtmAcpiBase,@pmtmAcpiValor,@pmtmAcpiTarif,@pmtmAcpiPrecio,@pmtmGcorCant,@pmtmGcorBase,@pmtmGcorValor,@pmtmGcorTarif ,@pmtmGcorPrecio,@pmtmGextCant,@pmtmGextBase,@pmtmGextValor,@pmtmGextTarif,@pmtmGextPrecio,@pmtmGimpCant,@pmtmGimpBase ,@pmtmGimpValor,@pmtmGimpTarif,@pmtmGimpPrecio,@pmtmGcoxCant,@pmtmGcoxBase,@pmtmGcoxValor,@pmtmGcoxTarif,@pmtmGcoxPrecio ,@pmtmGeoxCant,@pmtmGeoxBase,@pmtmGeoxValor,@pmtmGeoxTarif,@pmtmGeoxPrecio,@pmtmAczfCant,@pmtmAczfBase,@pmtmAczfValor,@pmtmAczfTarif ,@pmtmAczfPrecio,@pmtmGnzfCant,@pmtmGnzfBase,@pmtmGnzfValor,@pmtmGnzfTarif,@pmtmGnzfPrecio) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsZonas @pmIdZona VARCHAR(4),@pmZona VARCHAR(50),@pmCdSubCos VARCHAR(16),@pmInactivo BIT AS INSERT INTO Zonas (IdZona,Zona,CdSubCos,Inactivo) VALUES (@pmIdZona,@pmZona,@pmCdSubCos,@pmInactivo) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryActivosLta @pmIdGrupo VARCHAR(4)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdInstala VARCHAR(4)=Null ,@pmIdDep VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null,@pmIdProv VARCHAR(16)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null ,@pmNumEntrada INT=Null,@pmIdEstado VARCHAR(4)=Null AS SELECT A.IdActivo AS CdActivo,NomActivo,NumSerie,CodInvent,CodBarras,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubcentro,SubCosto,A.IdInstala AS CdInstala,Instlacion,A.IdDep AS CdDep,Dependencia ,IdRespons,TR.RazonSocial AS Responsable,A.IdCia AS CdCia,Compania,IdProv,T.RazonSocial AS Proveedor,FecCompra,FecActivacion,NumOrden,IdCiaOrden,NumEntrada,IdCiaEnt,VrCompra,VrComercial,VrBaseDep ,VrMejoras,VrDisminucion,A.VidaUtil AS Vida_util,FecFinDep,MetodoDep,Ajustable,Contable,CueEnGrupo,VrAjuInfAcum,VrDepAcuHis,VrDepAcuAju,VrAjuInfDep,NumMesesDep ,FecUltDep,Marca,Modelo,NumPlaca,NumDoc,Referencia,A.IdEstado AS CdEstado,Estado,FecRetiro,A.IdCausal AS CdCausal,Causal,VrVenta,FecCreacion,A.IdUsuario AS IdUsuari,Usuario --datos de anexo ,IdCuenta,IdCueDep,IdCueGasto,IdCueAjuste,IdCueCorr,IdCueAjuDep,IdCueCorrAD --datos del proveedor ,T.Codigo AS TercCodigo,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigla,T.Direccion AS TercDirecc,T.IdLocal AS CdLocalidad,Localidad,LC.IdDep AS Cd_Depto,Departamento,T.Telefono AS TercTelefono,T.e_mail AS TercEmail FROM Activos AS A INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea INNER JOIN CentroCosto AS CC ON A.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON A.IdInstala=I.IdInstala INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN Terceros AS T ON A.IdProv=T.IdTercero INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario INNER JOIN Localidades AS LC ON T.IdLocal=LC.IdLocal INNER JOIN Departamentos AS D ON LC.IdDep=D.IdDep LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN AfCausales AS CA ON A.IdCausal=CA.IdCausal LEFT JOIN Activos_Anexo AS AX ON A.IdActivo=AX.IdActivo WHERE A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND A.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND A.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND A.IdDep LIKE ISNULL(@pmIdDep,'%') AND IdRespons LIKE ISNULL(@pmIdRespons,'%') AND A.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProv LIKE ISNULL(@pmIdProv,'%') AND (FecActivacion>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND FecActivacion<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))) AND (NumEntrada>=ISNULL(@pmNumEntrada,-1) AND NumEntrada<=ISNULL(@pmNumEntrada,2147483647)) AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%') ORDER BY NomActivo GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFactura @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmIdProveedor VARCHAR(16),@pmItem INT AS SELECT TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom ,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura WHERE TipFac=@pmTipFac AND Factura=@pmFactura AND IdCia=@pmIdCia AND IdProveedor=@pmIdProveedor AND (Item>=ISNULL(@pmItem,0) AND Item<=ISNULL(@pmItem,2147483647)) ORDER BY Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFactura_Ent @pmTipFac VARCHAR(3),@pmDocumentoIni INT,@pmDocumentoFin INT,@pmIdCia CHAR(2) ,@pmIdProveedor VARCHAR(16)=Null,@pmFactura VARCHAR(15)=Null AS SELECT TipFac,Factura,C.IdCia AS CdCia,Compania,Item,IdProveedor,RazonSocial,C.IdCuenta AS CdCuenta,NomCuenta,TipDoc,Documento,IdCiaDoc ,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura AS C INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero INNER JOIN Puc AS P ON C.IdCuenta=P.IdCuenta INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia WHERE TipFac=@pmTipFac AND TipDoc=@pmTipFac AND Documento BETWEEN @pmDocumentoIni AND @pmDocumentoFin AND C.IdCia=@pmIdCia AND IdCiaDoc=@pmIdCia AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND Factura LIKE ISNULL(@pmFactura,'%') ORDER BY Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFacturaCmp @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2) AS SELECT TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante ,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura WHERE TipCom=@pmTipCom AND Comprobante=@pmComprobante AND IdCia=@pmIdCia ORDER BY TipFac,Factura,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFacturaDoc @pmTipFac VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2) ,@pmIdProveedor VARCHAR(16),@pmFactura VARCHAR(15),@pmItemCom INT AS SELECT TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante ,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura WHERE TipFac=@pmTipFac AND TipDoc=@pmTipFac AND Documento=@pmDocumento AND IdCia=@pmIdCia AND IdCiaDoc=@pmIdCia AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND Factura LIKE ISNULL(@pmFactura,'%') AND (ItemCom>=ISNULL(@pmItemCom,-10) AND ItemCom<=ISNULL(@pmItemCom,0)) ORDER BY Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFacturaDso @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmSaldo DECIMAL(14,2),@pmIdCia CHAR(2)=Null,@pmTipFac VARCHAR(3)=Null ,@pmTipDoc VARCHAR(3)=Null,@pmIdProveedor VARCHAR(16)=Null,@pmIdCuenta VARCHAR(16)=Null,@pmpVehiculo VARCHAR(10)=Null,@pmTipCom VARCHAR(3)=Null,@pmComprobante INT=Null AS SELECT TipFac, Factura, IdCia, Item,IdCuenta,IdProveedor,T.RazonSocial AS Proveedor,TipDoc, Documento, IdCiaDoc, FecEmision, FecVence, VrFactura,VrAbonado,VrFactura-VrAbonado AS ValorSaldo ,TipCom, Comprobante, ItemCom, Referencia, Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura AS V INNER JOIN Terceros AS T ON V.IdProveedor=T.IdTercero WHERE FecVence BETWEEN @pmFechaIni AND @pmFechaFin AND (VrFactura-VrAbonado)>=@pmSaldo AND IdCia like ISNULL(@pmIdCia,'%%') AND TipFac like ISNULL(@pmTipFac,'%') AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND pVehiculo LIKE ISNULL(@pmpVehiculo,'%') AND TipCom LIKE ISNULL(@pmTipCom,'%') AND (Comprobante>=ISNULL(@pmComprobante,-1) AND Comprobante<=ISNULL(@pmComprobante,2147483647)) ORDER BY TipFac,Factura,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFacturaLta @pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME ,@pmIdCuenta VARCHAR(16)=Null,@pmIdProveedor VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null ,@pmTipFac VARCHAR(3)=Null,@pmTipDoc VARCHAR(3)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null AS SELECT C.IdCuenta AS CdCuenta,NomCuenta,IdProveedor,RazonSocial,TipFac,TipoDoc,Factura,C.IdCia AS CdCia,Compania ,Item,VrFactura,VrAbonado,VrFactura-VrAbonado AS ValorSaldo,FecEmision,FecVence,DATEDIFF(day,FecVence,@pmFecActual) AS DiasMora ,TipDoc,Documento,IdCiaDoc,TipCom,TipoCom,Comprobante,ItemCom,Detalle,Referencia,pVehiculo,TipRef,DocRef,IdCiaRef,EstadoApr --datos del proveedor ,TipoId,Dv,T.Codigo AS CodigoProv,NomCial,SiglaRaz,T.Direccion AS DirProveedor,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDep,Departamento ,Telefono,Fax,e_mail,SitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte ,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga ,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret FROM Trn_ComFactura AS C INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero INNER JOIN Puc AS P ON C.IdCuenta=P.IdCuenta INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia 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 TiposCom AS TC ON C.TipCom=TC.IdCom LEFT JOIN Sys_TiposDoc AS TD ON C.TipFac=TD.IdDoc LEFT JOIN TercProvee AS TP ON C.IdProveedor=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco WHERE (VrFactura-VrAbonado)>@pmSaldo AND C.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND TipDoc LIKE ISNULL(@pmTipDoc,'%' ) AND TipFac LIKE ISNULL(@pmTipFac,'%') AND (FecEmision>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND FecEmision<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))) ORDER BY C.IdCuenta,RazonSocial,FecVence GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFacturaNit @pmIdProveedor VARCHAR(16),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null ,@pmTipDoc VARCHAR(3)=Null,@pmTipFac VARCHAR(3)=Null,@pmpVehiculo VARCHAR(10)=Null AS SELECT TipFac,Factura,IdCia,Item,VrFactura-VrAbonado AS ValorSaldo,VrFactura,VrAbonado,FecEmision,FecVence,TipDoc,Documento,IdCiaDoc ,IdProveedor,RazonSocial, F.IdCuenta AS IdCuent,NomCuenta,TipCom,Comprobante, ItemCom, Referencia, Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura AS F INNER JOIN Terceros AS T ON F.IdProveedor=T.IdTercero INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta WHERE VrFactura>VrAbonado AND IdProveedor=@pmIdProveedor AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND TipFac LIKE ISNULL(@pmTipFac,'%') --AND TipDoc LIKE ISNULL(@pmTipDoc,'%' ) AND pVehiculo LIKE ISNULL(@pmpVehiculo,'%') ORDER BY FecVence,TipFac,IdCia,Factura GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFacturaRef @pmTipFac VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2) ,@pmIdProveedor VARCHAR(16),@pmFactura VARCHAR(15),@pmReferencia VARCHAR(50) AS SELECT TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante ,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura WHERE TipFac=@pmTipFac AND TipDoc=@pmTipFac AND Documento=@pmDocumento AND IdCia=@pmIdCia AND IdCiaDoc=@pmIdCia AND Referencia=@pmReferencia AND ItemCom=0 AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND Factura LIKE ISNULL(@pmFactura,'%') ORDER BY Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryComFacturaSal @pmFechaAct SMALLDATETIME,@pmSaldo DECIMAL(14,2),@pmIdProveedor VARCHAR(16)=Null, @pmIdCia CHAR(2)=Null ,@pmIdCuenta VARCHAR(16)=Null,@pmTipDoc VARCHAR(3)=Null,@pmTipFac VARCHAR(3)=Null AS SELECT TipFac, Factura, IdCia, Item,VrFactura-VrAbonado AS ValorSaldo,VrFactura,VrAbonado, FecEmision, FecVence,DATEDIFF(day,FecVence,@pmFechaAct) AS DiasMora,TipDoc, Documento, IdCiaDoc ,IdProveedor,RazonSocial, F.IdCuenta AS IdCuent,NomCuenta,TipCom, Comprobante, ItemCom, Referencia, Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr FROM Trn_ComFactura AS F INNER JOIN Terceros AS T ON F.IdProveedor=T.IdTercero INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta WHERE (VrFactura-VrAbonado)>@pmSaldo AND F.IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND TipDoc LIKE ISNULL(@pmTipDoc,'%' ) AND TipFac LIKE ISNULL(@pmTipFac,'%') ORDER BY RazonSocial,F.IdCuenta,FecVence,TipFac,IdCia,Factura GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryCompGastos @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2) AS SELECT TipCom,Comprobante,IdCia,Fecha,IdPlantilla,IdTercero,TipFact,Factura,FecFactura,FecVence,VrTotal ,Anulado,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario FROM Trn_CompGastos WHERE TipCom=@pmTipCom AND Comprobante=@pmComprobante AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryCompGastosLta @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipCom VARCHAR(3)=Null ,@pmIdCia CHAR(2)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdPlantilla VARCHAR(4)=Null AS SELECT TipCom,TipoCom,Comprobante,C.IdCia AS CdCia,Compania,Fecha,C.IdPlantilla AS CdPlantilla,DescPlantilla,C.IdTercero AS NitTercero,RazonSocial,TipFact,Factura,FecFactura,FecVence,VrTotal ,Anulado,FecDev,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario FROM Trn_CompGastos AS C INNER JOIN TiposCom AS TC ON C.TipCom=TC.IdCom INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Plantillas AS PL ON C.IdPlantilla=PL.IdPlantilla WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipCom LIKE ISNULL(@pmTipCom,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND C.IdPlantilla LIKE ISNULL(@pmIdPlantilla,'%') ORDER BY TipCom,C.IdCia,Comprobante GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryConcDiversos @pmIdConcepto VARCHAR(4) AS SELECT IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,CdRet,CdIca,IdCueDeb,IdCueCre,Inactivo ,CdAutoret,FechaAdd,FechaUpdate,IdUsuario FROM ConcDiversos WHERE IdConcepto=@pmIdConcepto GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryConcDiversosLta @pmGrupo VARCHAR(20)=Null,@pmEsServicio BIT=Null AS SELECT IdConcepto,Concepto,Grupo,EsServicio,DeManfsto,CdIva,TI.Tarifa AS TarfIva,CdRet ,TR.Tarifa AS TarfRet,CdIca,TC.Tarifa AS TarfIca,CdAutoret,IdCueDeb,PD.NomCuenta AS NomCueDeb,IdCueCre,PC.NomCuenta AS NomCueCre ,D.IdUsuario AS IdUsuari,Usuario,D.FechaAdd AS Fech_Add,D.FechaUpdate AS Fech_Update FROM ConcDiversos AS D INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario LEFT JOIN Tablapor AS TI ON D.CdIva=TI.IdTarifa LEFT JOIN Tablapor AS TR ON D.CdRet=TR.IdTarifa LEFT JOIN Tablapor AS TC ON D.CdIca=TC.IdTarifa LEFT JOIN Puc AS PD ON D.IdCueDeb=PD.IdCuenta LEFT JOIN Puc AS PC ON D.IdCueCre=PC.IdCuenta WHERE D.Inactivo=0 AND Grupo LIKE ISNULL(@pmGrupo,'%') AND (EsServicio=ISNULL(@pmEsServicio,0) or EsServicio=ISNULL(@pmEsServicio,1)) ORDER BY Concepto GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryDevEnt @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2) AS SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Entrada,IdCiaDoc,FecDoc,IdProv,Factura,VrSubTotal,VrDescuento,VrImpuesto ,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,FletesCosto,CxPagar,DocEquiv ,BaseImp,BaseRet,CdCCosto,CdSubCos,Modalidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario FROM Trn_DevEnt WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryDevEntLta @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmDevolucionIni INT=Null,@pmDevolucionFin INT=Null ,@pmEntradaIni INT=Null,@pmEntradaFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdProv VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null ,@pmIdEstado VARCHAR(4)=Null,@pmModdDev VARCHAR(10)=Null AS SELECT Devolucion,IdCia,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Entrada,IdCiaDoc,FecDoc,IdProv,RazonSocial,Factura,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes ,VrOtros,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,FletesCosto,CxPagar,DocEquiv,BaseImp,BaseRet,CdCCosto,CdSubCos,Modalidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom ,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TipDev,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario FROM Trn_DevEnt AS D INNER JOIN Terceros AS T ON D.IdProv=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 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipDev=@pmTipDev AND Devolucion BETWEEN ISNULL(@pmDevolucionIni,0) AND ISNULL(@pmDevolucionFin,2147483647) AND Entrada BETWEEN ISNULL(@pmEntradaIni,0) AND ISNULL(@pmEntradaFin,2147483647) AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProv LIKE ISNULL(@pmIdProv,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND ModdDev LIKE ISNULL(@pmModdDev,'%') ORDER BY IdCia,Devolucion GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryDevEntRel @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null ,@pmIdConcepto VARCHAR(4)=Null,@pmIdProv VARCHAR(16)=Null,@pmCxPagar BIT=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,Entrada,IdCiaDoc,FecDoc ,D.IdProv AS NitProvee,RazonSocial,Factura,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal ,VrCargos,VrOtrDcto,VrNeto,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,FletesCosto,CxPagar,DocEquiv,BaseImp,BaseRet,CdCCosto,CCosto,CdSubCos,SubCosto,Modalidad,ModdDev,OrigenAdd ,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario --Información del tercero ,TipoId,Dv,T.Codigo AS CodTercero,NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,Telefono,Fax,e_mail ,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte --Información del proveedor ,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga ,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret FROM Trn_DevEnt AS D INNER JOIN Terceros AS T ON D.IdProv=T.IdTercero INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 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 CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos LEFT JOIN TercProvee AS TP ON D.IdProv=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco WHERE TipDev=@pmTipDev AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdProv LIKE ISNULL(@pmIdProv,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND ModdDev LIKE ISNULL(@pmModdDev,'%') AND (CxPagar=ISNULL(@pmCxPagar,0) or CxPagar=ISNULL(@pmCxPagar,1)) ORDER BY D.IdCia,Devolucion GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryDevEnt_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 TipEnt,Entrada,IdCiaDoc,FecDoc ,D.IdProv AS NitProvee,RazonSocial,D.Factura AS EntFactura,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal ,VrCargos,VrOtrDcto,VrNeto,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,FletesCosto,CxPagar,DocEquiv,BaseImp,BaseRet,D.CdCCosto AS CodCenCosto,CC.CCosto AS CentCosto ,D.CdSubCos AS CodSubCent,SC.SubCosto AS SubcCosto,Modalidad,ModdDev,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 ,TipoId,Dv,T.Codigo AS CodTercero,NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,Telefono,Fax,e_mail ,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte --Información del proveedor ,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga ,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret --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,CdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto ,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,pVehiculo,K.Referencia AS KarReferencia,Descripcion,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,Remision,IdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal ,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden --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 FROM Trn_DevEnt 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 Terceros AS T ON D.IdProv=T.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 INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc 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 CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos LEFT JOIN TercProvee AS TP ON D.IdProv=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco LEFT JOIN TiposCom AS TC ON D.TipCom=TC.IdCom WHERE TipDev=@pmTipDev AND Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia ORDER BY Devolucion,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEmp_CentrosLta @pmNContrato INT AS SELECT EC.NContrato AS NumContrato,C.IdEmpleado AS IdEmplado,Apellidos,Nombres,FecIngreso,FecRetiro,Indefinido ,EC.IdCCosto AS CdCCosto,CCosto,Tarifa,C.Inactivo AS ConInactivo FROM Emp_Centros AS EC INNER JOIN Emp_Contrato AS C ON EC.NContrato=C.NContrato INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN CentroCosto AS CO ON EC.IdCCosto=CO.IdCCosto WHERE (EC.NContrato>=ISNULL(@pmNContrato,0) AND EC.NContrato<=ISNULL(@pmNContrato,2147483647)) ORDER BY EC.NContrato,EC.IdCCosto GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEntradas @pmTipDoc VARCHAR(3),@pmEntrada INT,@pmIdCia CHAR(2) AS SELECT TipDoc,Entrada,IdCia,Fecha,IdConcepto,IdProv,Factura,FechaFac,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion ,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,CodTarRetFle,CodTarIcaFle,FletesCosto,TipOdc,OCompra,IdCiaOdc,MulPlazos ,IdPlazo,CxPagar,DocEquiv,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv ,CdCCosto,CdSubCos,TipEgr,Egreso,IdCiaEgr,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion ,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario FROM Trn_Entradas WHERE TipDoc=@pmTipDoc AND Entrada=@pmEntrada AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEntradas_Cr @pmTipDoc VARCHAR(3),@pmEntradaIni INT,@pmEntradaFin INT,@pmIdCia CHAR(2) AS SELECT E.TipDoc AS Tip_Doc,TipoDoc,Entrada,E.IdCia AS CdCia,Compania,E.Fecha AS FechaDoc,E.IdConcepto AS CdConcepto,Concepto,E.IdProv AS NitPrevee,RazonSocial,E.Factura AS NumFactura,FechaFac,FechaVence ,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto ,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,CodTarRetFle,CodTarIcaFle,FletesCosto,TipOdc,OCompra,IdCiaOdc,MulPlazos,E.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,CxPagar,DocEquiv,BaseImp,BaseRet,BaseIca,BaseRiv ,E.TarifaIva AS TarifIva,E.TarifaRet AS TarifRet,E.TarifaIca AS TarifIca,TarifaRiv,E.CdCCosto AS CodCenCosto,CC.CCosto AS CentCosto ,E.CdSubCos AS CodSubCent,SC.SubCosto AS SubcCosto,TipEgr,Egreso,IdCiaEgr,Modalidad,OrigenAdd,TipCom,TipoCom,Comprobante ,IdCiaCom,Anulado,NumDev,FecDev,E.Observacion AS Observ,E.IdEstado AS CdEstado,Estado,E.TimeSys AS Fec_add,E.FecUpdate AS Fec_Update,IdCiaCrea,E.IdUsuario AS CdUsuario,Usuario,Leyenda --Información del tercero ,TipoId,Dv,T.Codigo AS CodTercero,NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,Telefono,Fax,e_mail ,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte --Información del proveedor ,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga ,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret --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,CdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto ,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,pVehiculo,K.Referencia AS KarReferencia,Descripcion,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,Remision,IdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal ,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden --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 FROM Trn_Entradas AS E INNER JOIN Trn_Kardex AS K ON E.TipDoc=K.TipDoc AND E.Entrada=K.Documento AND E.IdCia=K.IdCia INNER JOIN Companias AS CN ON E.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON E.TipDoc=TD.IdDoc INNER JOIN Terceros AS T ON E.IdProv=T.IdTercero INNER JOIN Conceptos AS C ON E.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON E.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON E.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON E.IdPlazo=PZ.IdPlazo 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 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 CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos LEFT JOIN TiposCom AS TC ON E.TipCom=TC.IdCom LEFT JOIN CentroCosto AS CC ON E.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON E.CdSubCos=SC.IdSubCos LEFT JOIN TercProvee AS TP ON E.IdProv=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco WHERE E.TipDoc=@pmTipDoc AND Entrada BETWEEN @pmEntradaIni AND @pmEntradaFin AND E.IdCia=@pmIdCia ORDER BY Entrada,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEntradasLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmEntradaIni INT=Null,@pmEntradaFin INT=Null ,@pmIdCia CHAR(2)=Null,@pmIdProv VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null AS SELECT Entrada,IdCia,Fecha,E.IdConcepto AS CdConcepto,Concepto,IdProv,RazonSocial,Factura,FechaFac,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros ,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,CodTarRetFle,CodTarIcaFle,FletesCosto,TipOdc,OCompra,IdCiaOdc,MulPlazos,IdPlazo,CxPagar,DocEquiv ,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv ,CdCCosto,CdSubCos,TipEgr,Egreso,IdCiaEgr,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,E.Observacion AS Observ ,E.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,E.IdUsuario AS IdUsuari,Usuario,TipDoc FROM Trn_Entradas AS E INNER JOIN Terceros AS T ON E.IdProv=T.IdTercero INNER JOIN Conceptos AS C ON E.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON E.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON E.IdUsuario=U.IdUsuario WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Entrada BETWEEN ISNULL(@pmEntradaIni,0) AND ISNULL(@pmEntradaFin,2147483647) AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProv LIKE ISNULL(@pmIdProv,'%') AND E.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND E.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1)) ORDER BY IdCia,Entrada GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEntradasNet @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null ,@pmIdConcepto VARCHAR(4)=Null,@pmIdProv VARCHAR(16)=Null,@pmCxPagar BIT=Null,@pmModalidad VARCHAR(10)=Null AS SELECT TipDoc,Entrada,E.IdCia AS CdCia,Compania,Fecha,E.IdConcepto AS CdConcepto,Concepto,E.IdProv AS NitPrevee,T.RazonSocial AS NomProveedor,Factura,FechaFac,FechaVence ,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto ,Cantidad,NitFletes,TF.RazonSocial AS NomFletes,VrRetFlete,VrIcaFlete,FletesCosto,TipOdc,OCompra,IdCiaOdc,MulPlazos,E.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,CxPagar,DocEquiv,BaseImp,BaseRet ,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CdCCosto,CCosto,CdSubCos,SubCosto,Modalidad,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev ,E.Observacion AS Observ,E.IdEstado AS CdEstado,Estado,TimeSys,E.FecUpdate AS Fec_Update,IdCiaCrea,E.IdUsuario AS CdUsuario,Usuario --Información del tercero ,T.TipoId AS TercTipoId,T.Dv AS TercDV,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCodLocal,Localidad,L.IdDep AS CdDep,Departamento ,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS TercCodSector,SectorEco,T.IdRegimen AS TercCodRegimen,Regimen,T.TipEnte AS TercTipoEnte --Información del proveedor ,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga,Autoret FROM Trn_Entradas AS E INNER JOIN Companias AS CN ON E.IdCia=CN.IdCia INNER JOIN Terceros AS T ON E.IdProv=T.IdTercero INNER JOIN Conceptos AS C ON E.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON E.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON E.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON E.IdPlazo=PZ.IdPlazo 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 CentroCosto AS CC ON E.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON E.CdSubCos=SC.IdSubCos LEFT JOIN TercProvee AS TP ON E.IdProv=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo LEFT JOIN Terceros AS TF ON E.NitFletes=TF.IdTercero WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND E.IdCia LIKE ISNULL(@pmIdCia,'%%') AND E.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND E.IdProv LIKE ISNULL(@pmIdProv,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND (CxPagar=ISNULL(@pmCxPagar,0) or CxPagar=ISNULL(@pmCxPagar,1)) UNION SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.IdProv AS NitProvee,T.RazonSocial AS NomProveedor ,D.Factura AS NumFactura,FechaFac,FechaVence,D.VrSubTotal AS SubTotal,D.VrDescuento AS Descuento,D.VrImpuesto AS Impuesto,D.VrRetencion AS ReteFte,D.VrReteICA AS ReteICA ,D.VrReteIVA AS ReteIva,D.VrFletes AS Fletes,D.VrOtros AS ValOtros,D.VrSobretasa AS Sobretasas,D.VrImpGlobal AS ImpGlobal,D.VrCargos AS OtrosCargos,D.VrOtrDcto AS OtrosDctos ,D.VrNeto AS TotalNeto,D.Cantidad AS CantDev,D.NitFletes AS Nit_Fletes,TF.RazonSocial AS NomFletes,D.VrRetFlete,D.VrIcaFlete,D.FletesCosto,TipOdc,OCompra,IdCiaOdc,MulPlazos,E.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago ,D.CxPagar AS Cx_Pagar,D.DocEquiv AS DocEquival,D.BaseImp AS VrBaseIva,D.BaseRet AS VrBaseRet,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,D.CdCCosto AS CodCcost,CCosto ,D.CdSubCos AS CodSubCent,SubCosto,D.Modalidad AS ModalidadEnt,D.TipCom AS TipoComp,D.Comprobante AS NumComp,D.IdCiaCom AS CodCiaComp,0,D.Entrada AS NumEntrada,FecDoc ,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS FecAdd,D.FecUpdate AS Fec_Update,D.IdCiaCrea AS CodCiaCrea,D.IdUsuario AS IdUsuari,Usuario --Información del tercero ,T.TipoId AS TercTipoId,T.Dv AS TercDV,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCodLocal,Localidad,L.IdDep AS CdDep,Departamento ,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS TercCodSector,SectorEco,T.IdRegimen AS TercCodRegimen,Regimen,T.TipEnte AS TercTipoEnte --Información del proveedor ,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga,Autoret FROM Trn_DevEnt AS D INNER JOIN Terceros AS T ON D.IdProv=T.IdTercero INNER JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 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 Trn_Entradas AS E ON D.TipDoc=E.TipDoc AND D.Entrada=E.Entrada AND D.IdCiaDoc=E.IdCia INNER JOIN Plazos AS PZ ON E.IdPlazo=PZ.IdPlazo LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos LEFT JOIN TercProvee AS TP ON D.IdProv=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo LEFT JOIN Terceros AS TF ON D.NitFletes=TF.IdTercero WHERE D.TipDoc=@pmTipDoc AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND E.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdProv LIKE ISNULL(@pmIdProv,'%') AND D.Modalidad LIKE ISNULL(@pmModalidad,'%') AND (D.CxPagar=ISNULL(@pmCxPagar,0) or D.CxPagar=ISNULL(@pmCxPagar,1)) ORDER BY E.IdCia,Entrada GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEntradasRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null ,@pmIdConcepto VARCHAR(4)=Null,@pmIdProv VARCHAR(16)=Null,@pmCxPagar BIT=Null,@pmModalidad VARCHAR(10)=Null,@pmAnulado BIT=Null AS SELECT TipDoc,Entrada,E.IdCia AS CdCia,Compania,Fecha,E.IdConcepto AS CdConcepto,Concepto,E.IdProv AS NitPrevee,RazonSocial,Factura,FechaFac,FechaVence ,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrSobretasa,VrImpGlobal,VrCargos,VrOtrDcto,VrNeto ,Cantidad,NitFletes,VrRetFlete,VrIcaFlete,CodTarRetFle,CodTarIcaFle,FletesCosto,TipOdc,OCompra,IdCiaOdc,MulPlazos,E.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,CxPagar,DocEquiv,BaseImp,BaseRet,BaseIca,BaseRiv ,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CdCCosto,CCosto,CdSubCos,SubCosto,TipEgr,Egreso,IdCiaEgr,Modalidad,OrigenAdd,TipCom,Comprobante ,IdCiaCom,Anulado,NumDev,FecDev,E.Observacion AS Observ,E.IdEstado AS CdEstado,Estado,TimeSys,E.FecUpdate AS Fec_Update,IdCiaCrea,E.IdUsuario AS CdUsuario,Usuario --Información del tercero ,TipoId,Dv,T.Codigo AS CodTercero,NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,Telefono,Fax,e_mail ,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte --Información del proveedor ,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga ,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret FROM Trn_Entradas AS E INNER JOIN Companias AS CN ON E.IdCia=CN.IdCia INNER JOIN Terceros AS T ON E.IdProv=T.IdTercero INNER JOIN Conceptos AS C ON E.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON E.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON E.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON E.IdPlazo=PZ.IdPlazo 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 CentroCosto AS CC ON E.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON E.CdSubCos=SC.IdSubCos LEFT JOIN TercProvee AS TP ON E.IdProv=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND E.IdCia LIKE ISNULL(@pmIdCia,'%%') AND E.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND E.IdProv LIKE ISNULL(@pmIdProv,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND (CxPagar=ISNULL(@pmCxPagar,0) or CxPagar=ISNULL(@pmCxPagar,1)) AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1)) ORDER BY E.IdCia,Entrada GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryFinClieSaldosLta @pmIdClie VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null AS SELECT F.IdClie AS NitClie,RazonSocial,CdAgencia,CodAgencia,Agencia,F.IdCia AS CdCia,Compania,F.VrSaldo AS SaldoActual FROM FinClieSaldos AS F INNER JOIN Terceros AS T ON F.IdClie=T.IdTercero INNER JOIN TercCliente AS CLI ON F.IdClie=CLI.IdClie INNER JOIN Companias AS CI ON F.IdCia=CI.IdCia LEFT JOIN Agencias AS A ON F.CdAgencia=A.IdAgencia WHERE F.IdClie LIKE ISNULL(@pmIdClie,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') ORDER BY RazonSocial,F.IdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE paQryFinPrestamoRes @pmSaldo DECIMAL(12,2),@pmTipDoc VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null ,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null AS SELECT T.RazonSocial AS NomCliente,IdCliente,Agencia,P.IdAgencia AS CdAgencia,P.IdPrestamo AS NumPrest,P.IdCia AS CdCia,Compania ,NumCredito,P.Fecha AS FecRadica,FecPrestamo,TipoAprob,NActaJunta,VrPrestamo,NPlazos,Causacion,P.IdTasa AS CdTasa,DescTasa ,tmVrCuota AS VrCuotaDTF,tmDescCuota AS DescripCuota,tmVrMora AS VrEnMora,AcumCapital,AcumIntereses,VrPrestamo-AcumCapital AS SaldoCapital,tmDiasMora AS PromDiasMora ,CausaMora,GestionClie,AcuerdosClie,TipoGarantia,DescGarantia,VrGarantia,NumCuotaIni,AcumCuotas,CuotasMora ,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,P.NContrato AS NumContrato,CdCiaCon,P.Observacion AS Observ ,P.IdConcepto AS CdConcepto,Concepto,P.IdEstado AS CdEstado,Estado ,TI.TipoInteres AS Tipo_Int,TasaEfectiva,TipoPeriodo,CausaInteres,DTF_EA,Spread_TA,OpcCompra --cliente ,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.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte ,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie --agencias ,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_FinPrestamo AS P INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero 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 TercCliente AS CLI ON P.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 LEFT JOIN Agencias AS A ON P.IdAgencia=A.IdAgencia LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep LEFT JOIN Trn_FinObserv AS PD ON P.TipDoc=PD.TipDoc AND P.IdPrestamo=PD.IdPrestamo AND P.IdCia=PD.IdCia LEFT JOIN tm_PreMora AS PM ON P.TipDoc=PM.tmTipDoc AND P.IdPrestamo=PM.tmIdPrestamo AND P.IdCia=PM.tmIdCia WHERE P.TipDoc=@pmTipDoc AND P.Anulado=0 AND (VrPrestamo-AcumCapital)>=@pmSaldo AND P.IdCia LIKE ISNULL(@pmIdCia,'%%') AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%') ORDER BY P.IdCia,P.IdPrestamo GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryOpedido @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2) AS SELECT TipDoc,Pedido,IdCia,Fecha,FechaVence,IdConcepto,IdCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa ,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma ,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,AsignarVeh,pVehiculo,CdConductor,CdRuta,ListaPrec,RefPedido,Modalidad,Vigencia,NumAutoriza,NumAutCupo ,NumAutCheq,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem,OrigenAdd,Anulado ,FecDev,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario FROM Trn_Opedido WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryOpedido_Cr @pmTipDoc VARCHAR(3),@pmPedidoIni INT,@pmPedidoFin INT,@pmIdCia CHAR(2) AS SELECT O.TipDoc AS Tip_Doc,TipoDoc,Pedido,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente ,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad ,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,DiasEntraga,LugarEnvio ,O.NitContac AS NitContacto,O.NomContac AS NomContacto,O.TelContac AS TelContacto,O.emlContac AS EmailContacto,CargoContac,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago ,NitEmpTrans,EmpTrans,O.pVehiculo AS PlacaVeh,AsignarVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta,RefPedido,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact ,TipRem,O.Remision AS NumRemision,O.IdCiaRem AS CdCiaRem,FechaRem,NumAutoriza,NumAutCupo,NumAutCheq,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera ,TipoTrans,TipoOrden,TipoModifica,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS Fech_Add,O.FecUpdate AS Fech_Update,IdCiaCrea,O.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,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 CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato ,DirAgncia,TelAgncia,FaxAgncia,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,K.IdTercero AS KarNitTercero,CdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto ,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,K.Remision AS KarRemision,K.IdCiaRem AS CdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev ,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,K.ListaPrec AS KarLtaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden --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_Opedido AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Pedido=K.Documento AND O.IdCia=K.IdCia INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Conceptos AS C ON O.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma INNER JOIN TercCliente AS CLI ON O.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 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 O.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep LEFT JOIN Terceros AS TC ON O.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos WHERE O.TipDoc=@pmTipDoc AND Pedido BETWEEN @pmPedidoIni AND @pmPedidoFin AND O.IdCia=@pmIdCia AND EsProdBase=0 ORDER BY Pedido,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryOpedidoLta @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmPedidoIni INT=Null,@pmPedidoFin INT=Null ,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null ,@pmVigencia VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null AS SELECT Pedido,IdCia,Fecha,FechaVence,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrOtrDcto ,VrCargos,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac ,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,TipoTrans,NitEmpTrans,EmpTrans,pVehiculo,AsignarVeh,CdConductor,CdRuta,ListaPrec,RefPedido,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem ,NumAutoriza,NumAutCupo,NumAutCheq,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,CdUsuAprob,DetalleAprob,TipoOrden,TipoModifica,OrigenAdd,ZonaFrontera,Anulado,FecDev,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS IdUsuari,Usuario FROM Trn_Opedido AS P INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero INNER JOIN Terceros AS V ON P.IdVend=V.IdTercero INNER JOIN Conceptos AS CN ON P.IdConcepto=CN.IdConcepto INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Pedido BETWEEN ISNULL(@pmPedidoIni,0) AND ISNULL(@pmPedidoFin,2147483647) AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND Vigencia LIKE ISNULL(@pmVigencia,'%') AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1)) ORDER BY IdCia,Pedido GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryOpedidoRel @pmTipDoc 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,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null AS SELECT TipDoc,Pedido,O.IdCia AS CdCia,Compania,Fecha,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente ,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad ,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,LugarEnvio,DiasEntraga ,O.NitContac AS NitContacto,O.NomContac AS NomContacto,O.TelContac AS TelContacto,O.emlContac AS EmailContacto,CargoContac,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago ,NitEmpTrans,EmpTrans,AsignarVeh,O.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta,RefPedido,TipFac,Factura,IdCiaFac,FechaFact ,TipRem,Remision,IdCiaRem,FechaRem,NumAutoriza,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,CdUsuAprob,DetalleAprob,OrigenAdd,Anulado,FecDev,O.Observacion AS Observ ,O.IdEstado AS CdEstado,Estado,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,TimeSys,O.FecUpdate AS Fech_Update,IdCiaCrea,O.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,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 CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato ,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia FROM Trn_Opedido AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Conceptos AS C ON O.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma INNER JOIN TercCliente AS CLI ON O.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 LE ON O.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep LEFT JOIN Terceros AS TC ON O.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND O.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%') AND O.IdVend LIKE ISNULL(@pmIdVend,'%') AND O.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1)) ORDER BY O.IdCia,Pedido GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryPlantillasConcLta @pmIdPlantilla VARCHAR(4)=Null AS SELECT P.IdPlantilla AS CdPlantilla,DescPlantilla,CdTipCom,TipoCom,P.Inactivo AS Inactvo ,Item,Concepto,C.IdCuenta AS CdCuenta,NomCuenta,TipoMov,Tarifa, Requerido FROM Plantillas AS P LEFT JOIN PlantillasConc AS C ON P.IdPlantilla=C.IdPlantilla LEFT JOIN TiposCom AS TC ON P.CdTipCom=TC.IdCom LEFT JOIN Puc AS PC ON C.IdCuenta=PC.IdCuenta WHERE P.IdPlantilla LIKE ISNULL(@pmIdPlantilla,'%') GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrySobDetalle @pmIdDec VARCHAR(4),@pmNumDeclara INT,@pmItem INT AS SELECT IdDec,NumDeclara,Item,Concepto,Cantidad,VrUnitario,VrBase,Tarifa,VrLiquida,VrPrecio,ClaseProd,TarifGalon,PorcBase FROM Trn_SobDetalle WHERE IdDec=@pmIdDec AND NumDeclara=@pmNumDeclara AND (Item>=ISNULL(@pmItem,0) AND Item<=ISNULL(@pmItem,2147483647)) ORDER BY Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrySobretasas_Cr @pmIdDec VARCHAR(4),@pmNumDeclaraIni INT,@pmNumDeclaraFin INT ,@pmnAnnoIni INT=Null,@pmnAnnoFin INT=Null,@pmnMesIni INT=Null,@pmnMesFin INT=Null ,@pmIdEnterr VARCHAR(8)=Null,@pmTipoEntidad VARCHAR(10)=Null,@pmIdBanco VARCHAR(4)=Null,@pmEstado INT=Null AS SELECT S.IdDec AS CdDec,Declaracion,S.NumDeclara AS NumeroDec,Fecha,nAnno,nMes,NitDeclara,DvDeclara,CedDeclara,Declarante ,VrSobretasa,VrSanciones,VrCompensa,VrIntMora,VrTotalPagar,VrFondoSubs,VrRecaudoDep,VrEfectivo,VrOtros,VrTransf,VrPagado --detalles ,Item,Concepto,Cantidad,VrUnitario,VrBase,Tarifa,VrLiquida,VrPrecio,ClaseProd,TarifGalon,PorcBase --entidad ,TipoEntidad,S.IdEnterr AS CdEnterr,NomEntidad,E.TipoId AS EntTipoID,NitEntidad,E.Dv AS EntDv,RazonSocial,DirEntidad,E.Telefono AS EntTelefono,E.Fax AS EntFax ,E.SitioWeb AS EntSitioWeb,EmailEnt,E.CedRepLegal AS CedRepres,NomRepres,CargoRepres,FormaPago ,S.IdClase AS CdClase,ClaseCuenta,S.NumCuenta AS NroCuenta,S.IdBanco AS IdBanc,Banco,CodEntidad ,S.CdLocCue AS IdLocCue,L.Localidad AS LugarCuenta,L.IdDep AS CdDepLugar,DP.Departamento AS DptoLugarCuenta,TitularCuenta,NumCheque,CodBanco,CodCta,NumeroCta ,S.TipoFirma AS TipFirma,S.NitRevisor AS IdRevisor,S.NomRevisor AS NombRevisor,S.TpRevisor AS TarjProf,Estado,Adhesivo,FechaPago ,EsCorr,NumCorr,FecCorr,S.Observacion AS Observ,S.IdUsuario AS IdUsuari,Usuario,TimeSys --información del tipo de declaracion ,TipoDec,TipoIdDec,TD.Direccion AS DirDeclarante,TD.IdLocal AS CdCiuDec,LT.Localidad AS CiuDeclarante ,LT.IdDep AS CdDepDec,DT.Departamento AS DptoDeclarante,TD.Telefono AS TelefDeclara,TD.CedRepLegal AS CedRepDeclara,TD.RepLegal AS RepLegalDeclara FROM Trn_Sobretasas AS S INNER JOIN Trn_SobDetalle AS D ON S.IdDec=D.IdDec AND S.NumDeclara=D.NumDeclara INNER JOIN SobEntidades AS E ON S.IdEnterr=E.IdEnterr INNER JOIN SobTiposDec AS TD ON S.IdDec=TD.IdDec INNER JOIN Bancos AS B ON S.IdBanco=B.IdBanco INNER JOIN ClaseCta AS CT ON S.IdClase=CT.IdClase INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario INNER JOIN Localidades AS LT ON TD.IdLocal=LT.IdLocal INNER JOIN Departamentos AS DT ON LT.IdDep=DT.IdDep LEFT JOIN Localidades AS L ON S.CdLocCue=L.IdLocal LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep LEFT JOIN Terceros AS T ON E.NitEntidad=T.IdTercero LEFT JOIN CtasCorrientes AS CTA ON S.CodCta=CTA.IdCta WHERE S.IdDec LIKE ISNULL(@pmIdDec ,'%') AND S.NumDeclara BETWEEN ISNULL(@pmNumDeclaraIni,0) AND ISNULL(@pmNumDeclaraFin,2147483647) AND nMes BETWEEN ISNULL(@pmnMesIni,0) AND ISNULL(@pmnMesFin,20) AND nAnno BETWEEN ISNULL(@pmnAnnoIni,0) AND ISNULL(@pmnAnnoFin,2147483647) AND S.IdEnterr LIKE ISNULL(@pmIdEnterr,'%') AND TipoEntidad LIKE ISNULL(@pmTipoEntidad ,'%') AND S.IdBanco LIKE ISNULL(@pmIdBanco,'%') AND (Estado>=ISNULL(@pmEstado,-1) AND Estado<=ISNULL(@pmEstado,2147483647)) ORDER BY S.NumDeclara,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrySobretasas_Crr @pmnAnno INT,@pmnMesIni INT=Null,@pmnMesFin INT=Null,@pmIdDec VARCHAR(4)=Null ,@pmNumDeclaraIni INT=Null,@pmNumDeclaraFin INT=Null,@pmIdEnterr VARCHAR(8)=Null,@pmTipoEntidad VARCHAR(10)=Null ,@pmIdBanco VARCHAR(4)=Null AS SELECT IdDec,NumDeclara,nAnno,nMes,TipoEntidad,S.IdEnterr AS CdEnterr,E.TipoId AS EntTipoID ,NitEntidad,E.Dv AS EntDv,NomEntidad,S.IdClase AS CdClase,ClaseCuenta,S.NumCuenta AS NroCuenta,S.IdBanco AS IdBanc,Banco,CodEntidad,TitularCuenta --valores ,tmAcpmCant,tmAcpmBase,tmAcpmValor,tmAcpmTarif,tmAcpmPrecio,tmAcpiCant,tmAcpiBase,tmAcpiValor,tmAcpiTarif,tmAcpiPrecio,tmGcorCant,tmGcorBase ,tmGcorValor,tmGcorTarif,tmGcorPrecio,tmGextCant,tmGextBase,tmGextValor,tmGextTarif,tmGextPrecio,tmGimpCant,tmGimpBase,tmGimpValor,tmGimpTarif ,tmGimpPrecio,tmGcoxCant,tmGcoxBase,tmGcoxValor,tmGcoxTarif,tmGcoxPrecio,tmGeoxCant,tmGeoxBase,tmGeoxValor,tmGeoxTarif,tmGeoxPrecio ,tmAczfCant,tmAczfBase,tmAczfValor,tmAczfTarif,tmAczfPrecio,tmGnzfCant,tmGnzfBase,tmGnzfValor,tmGnzfTarif,tmGnzfPrecio ,VrSobretasa,VrSanciones,VrCompensa,VrIntMora,VrTotalPagar,VrFondoSubs,VrRecaudoDep,VrEfectivo,VrOtros,VrTransf,VrPagado ,FormaPago,S.CdLocCue AS IdLocCue,L.Localidad AS LugarCuenta,L.IdDep AS CdDepLugar,DP.Departamento AS DptoLugarCuenta,NumCheque,CodBanco ,CodCta,NumeroCta,DirEntidad,E.Telefono AS EntTelefono,E.Fax AS EntFax,E.SitioWeb AS EntSitioWeb,EmailEnt,E.CedRepLegal AS CedRepres,NomRepres,CargoRepres ,RazonSocial,LT.Localidad AS NomCiudad,LT.IdDep AS CdDep,DT.Departamento AS NomDpto ,Estado,Fecha,NitDeclara,DvDeclara,CedDeclara,Declarante FROM Trn_Sobretasas AS S INNER JOIN tm_Sobtasas AS T ON S.IdDec=T.tmIdDec AND S.NumDeclara=T.tmNumDeclara INNER JOIN SobEntidades AS E ON S.IdEnterr=E.IdEnterr INNER JOIN Bancos AS B ON S.IdBanco=B.IdBanco INNER JOIN ClaseCta AS CT ON S.IdClase=CT.IdClase LEFT JOIN Localidades AS L ON S.CdLocCue=L.IdLocal LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep LEFT JOIN Localidades AS LT ON S.IdEnterr=LT.IdLocal LEFT JOIN Departamentos AS DT ON LT.IdDep=DT.IdDep LEFT JOIN Terceros AS TE ON E.NitEntidad=TE.IdTercero LEFT JOIN CtasCorrientes AS CTA ON S.CodCta=CTA.IdCta WHERE nAnno=@pmnAnno AND Estado<=1 AND IdDec LIKE ISNULL(@pmIdDec ,'%') AND nMes BETWEEN ISNULL(@pmnMesIni,0) AND ISNULL(@pmnMesFin,20) AND NumDeclara BETWEEN ISNULL(@pmNumDeclaraIni,0) AND ISNULL(@pmNumDeclaraFin,2147483647) AND S.IdEnterr LIKE ISNULL(@pmIdEnterr,'%') AND TipoEntidad LIKE ISNULL(@pmTipoEntidad ,'%') AND S.IdBanco LIKE ISNULL(@pmIdBanco,'%') ORDER BY IdDec,NomEntidad,NumDeclara GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrySobretasasDet @pmnAnno INT,@pmnMesIni INT=Null,@pmnMesFin INT=Null ,@pmIdDec VARCHAR(4)=Null,@pmIdEnterr VARCHAR(8)=Null,@pmTipoEntidad VARCHAR(10)=Null,@pmIdBanco VARCHAR(4)=Null ,@pmNumDeclaraIni INT=Null,@pmNumDeclaraFin INT=Null AS SELECT S.IdDec AS CdDec,S.NumDeclara AS NroDeclara,nAnno,nMes,S.IdEnterr AS CdEnterr,FormaPago,NomEntidad,Localidad,IdDep ,TipoEntidad,VrSobretasa,VrSanciones,VrCompensa,VrIntMora,VrTotalPagar ,VrEfectivo,VrOtros,VrTransf,VrPagado,Estado,CodBanco,CodCta ,Item,Concepto,Cantidad,VrUnitario,VrBase,Tarifa,VrLiquida,VrPrecio,ClaseProd,TarifGalon,PorcBase FROM Trn_Sobretasas AS S INNER JOIN Trn_SobDetalle AS D ON S.IdDec=D.IdDec AND S.NumDeclara=D.NumDeclara INNER JOIN SobEntidades AS E ON S.IdEnterr=E.IdEnterr LEFT JOIN Localidades AS L ON E.IdEnterr=L.IdLocal WHERE nAnno=@pmnAnno AND Estado<=1 --2 Y 3 no se incluyen AND (nMes>=ISNULL(@pmnMesIni,0) AND nMes<=ISNULL(@pmnMesFin,2147483647)) AND S.IdDec LIKE ISNULL(@pmIdDec,'%') AND S.IdEnterr LIKE ISNULL(@pmIdEnterr,'%') AND TipoEntidad LIKE ISNULL(@pmTipoEntidad,'%') AND S.IdBanco LIKE ISNULL(@pmIdBanco,'%') AND (S.NumDeclara>=ISNULL(@pmNumDeclaraIni,0) AND S.NumDeclara<=ISNULL(@pmNumDeclaraFin,2147483647)) ORDER BY S.IdDec,S.NumDeclara,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrySubCentrosDso @pmIdCCosto VARCHAR(16)=Null,@pmInactivo BIT=Null AS IF @pmIdCCosto IS NULL BEGIN SELECT IdSubCos,SubCosto,SubCosto+' '+IdSubCos AS DsSub,IdCCosto FROM SubCentros WHERE (Inactivo=ISNULL(@pmInactivo,0) or Inactivo=ISNULL(@pmInactivo,1)) ORDER BY IdCCosto,SubCosto END ELSE BEGIN SELECT IdSubCos,SubCosto,SubCosto+' '+IdSubCos AS DsSub,IdCCosto FROM SubCentros WHERE (IdSubCos='0' OR IdCCosto='0' OR IdCCosto=@pmIdCCosto) AND (Inactivo=ISNULL(@pmInactivo,0) or Inactivo=ISNULL(@pmInactivo,1)) ORDER BY IdCCosto,SubCosto END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrytm_KdexDve @pmtmNumero VARCHAR(5) AS SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,tmCdTanque,tmSalidas,tmVrPrecio,tmTarifaDct,tmVrDcto,tmVrUnitario ,(tmSalidas*tmVrPrecio)-tmVrDcto AS VrTotal,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmImpGlobal ,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom ,tmUnidades,Bodega,tmReferencia,tmDescripcion,tmCdCCosto,tmCdSubCos,tmNumLote,tmFecVceLote,tmCodTarDct,tmCodTarRet,tmCodTarIca ,tmServcios,Tanques,tmTipDoc,tmDocumento,tmIdCia,tmNumInicial FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega WHERE tmNumero=@pmtmNumero ORDER BY tmItem GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrytm_KdexEnt @pmtmNumero VARCHAR(5) AS SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto,tmVrUnitario ,(tmEntradas*tmVrPrecio)-tmVrDcto AS VrTotal,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmImpGlobal ,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmUnidades,tmReferencia,tmDescripcion,tmCdCCosto,tmCdSubCos,tmNumLote,tmFecVceLote,tmCodTarDct,tmCodTarRet,tmCodTarIca ,tmTipDoc,tmDocumento,tmIdCia,tmServcios,Tanques,tmNumInicial FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega WHERE tmNumero=@pmtmNumero ORDER BY tmItem GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryZonas @pmIdZona VARCHAR(4) AS SELECT IdZona,Zona,CdSubCos,Inactivo FROM Zonas WHERE IdZona=@pmIdZona GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryZonasDso AS SELECT IdZona,Zona FROM Zonas WHERE Inactivo=0 ORDER BY Zona GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpComFactura @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmItem INT,@pmIdProveedor VARCHAR(16),@pmIdCuenta VARCHAR(16),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmFecEmision SMALLDATETIME,@pmFecVence SMALLDATETIME ,@pmVrFactura MONEY,@pmVrAbonado MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmItemCom INT,@pmReferencia VARCHAR(50),@pmDetalle VARCHAR(100),@pmpVehiculo VARCHAR(10),@pmVehPropio BIT ,@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmEstadoApr INT AS UPDATE Trn_ComFactura SET IdCuenta=@pmIdCuenta,TipDoc=@pmTipDoc,Documento=@pmDocumento,IdCiaDoc=@pmIdCiaDoc,FecEmision=@pmFecEmision,FecVence=@pmFecVence,VrFactura=@pmVrFactura,VrAbonado=@pmVrAbonado,TipCom=@pmTipCom,Comprobante=@pmComprobante,ItemCom=@pmItemCom ,Referencia=@pmReferencia,Detalle=@pmDetalle,pVehiculo=@pmpVehiculo,VehPropio=@pmVehPropio,TipRef=@pmTipRef,DocRef=@pmDocRef,IdCiaRef=@pmIdCiaRef,EstadoApr=@pmEstadoApr WHERE TipFac=@pmTipFac AND Factura=@pmFactura AND IdCia=@pmIdCia AND Item=@pmItem AND IdProveedor=@pmIdProveedor GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE paUpCompGastos @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdPlantilla VARCHAR(4),@pmIdTercero VARCHAR(16),@pmTipFact VARCHAR(3) ,@pmFactura VARCHAR(15),@pmFecFactura SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmVrTotal MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4) AS UPDATE Trn_CompGastos SET Fecha=@pmFecha,IdPlantilla=@pmIdPlantilla,IdTercero=@pmIdTercero,TipFact=@pmTipFact,Factura=@pmFactura,FecFactura=@pmFecFactura,FecVence=@pmFecVence,VrTotal=@pmVrTotal ,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado WHERE TipCom=@pmTipCom AND Comprobante=@pmComprobante AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpConcDiversos @pmIdConcepto VARCHAR(4),@pmConcepto VARCHAR(250),@pmGrupo VARCHAR(20),@pmEsServicio BIT ,@pmDeManfsto BIT,@pmCdIva VARCHAR(4),@pmCdRet VARCHAR(4) ,@pmCdIca VARCHAR(4),@pmIdCueDeb VARCHAR(16),@pmIdCueCre VARCHAR(16),@pmInactivo BIT,@pmCdAutoret VARCHAR(4),@pmFechaUpdate SMALLDATETIME AS UPDATE ConcDiversos SET Concepto=@pmConcepto,Grupo=@pmGrupo,EsServicio=@pmEsServicio,DeManfsto=@pmDeManfsto,CdIva=@pmCdIva ,CdRet=@pmCdRet,CdIca=@pmCdIca,Inactivo=@pmInactivo,IdCueDeb=@pmIdCueDeb,IdCueCre=@pmIdCueCre,CdAutoret=@pmCdAutoret,FechaUpdate=@pmFechaUpdate WHERE IdConcepto=@pmIdConcepto GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpDevEnt @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmEntrada INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME ,@pmIdProv VARCHAR(16),@pmFactura VARCHAR(15),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrSobretasa MONEY ,@pmVrImpGlobal MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmNitFletes VARCHAR(16),@pmCxPagar BIT,@pmDocEquiv VARCHAR(3),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmCdCCosto VARCHAR(16) ,@pmCdSubCos VARCHAR(16),@pmModalidad VARCHAR(10),@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4) ,@pmVrRetFlete MONEY,@pmVrIcaFlete MONEY,@pmFletesCosto BIT,@pmFecUpdate SMALLDATETIME AS UPDATE Trn_DevEnt SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,Entrada=@pmEntrada,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,IdProv=@pmIdProv,Factura=@pmFactura,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento ,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto ,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,NitFletes=@pmNitFletes,CxPagar=@pmCxPagar,DocEquiv=@pmDocEquiv,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos,Modalidad=@pmModalidad,ModdDev=@pmModdDev ,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,VrRetFlete=@pmVrRetFlete,VrIcaFlete=@pmVrIcaFlete,FletesCosto=@pmFletesCosto,FecUpdate=@pmFecUpdate WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpEntradas @pmTipDoc VARCHAR(3),@pmEntrada INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdProv VARCHAR(16),@pmFactura VARCHAR(15),@pmFechaFac SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY ,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmNitFletes VARCHAR(16),@pmTipOdc VARCHAR(3),@pmOCompra INT,@pmIdCiaOdc CHAR(2) ,@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCxPagar BIT,@pmDocEquiv VARCHAR(3),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4) ,@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr 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),@pmVrRetFlete MONEY,@pmVrIcaFlete MONEY,@pmCodTarRetFle VARCHAR(4),@pmCodTarIcaFle VARCHAR(4),@pmFletesCosto BIT,@pmFecUpdate SMALLDATETIME AS UPDATE Trn_Entradas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdProv=@pmIdProv,Factura=@pmFactura,FechaFac=@pmFechaFac,FechaVence=@pmFechaVence,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes ,VrOtros=@pmVrOtros,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,NitFletes=@pmNitFletes,TipOdc=@pmTipOdc,OCompra=@pmOCompra,IdCiaOdc=@pmIdCiaOdc,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,CxPagar=@pmCxPagar,DocEquiv=@pmDocEquiv ,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,CodTarIva=@pmCodTarIva,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos,TipEgr=@pmTipEgr ,Egreso=@pmEgreso,IdCiaEgr=@pmIdCiaEgr,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado ,VrRetFlete=@pmVrRetFlete,VrIcaFlete=@pmVrIcaFlete,CodTarRetFle=@pmCodTarRetFle,CodTarIcaFle=@pmCodTarIcaFle,FletesCosto=@pmFletesCosto,FecUpdate=@pmFecUpdate WHERE TipDoc=@pmTipDoc AND Entrada=@pmEntrada AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpOpedido @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY ,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmLugarEnvio VARCHAR(50),@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20) ,@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmAsignarVeh BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmListaPrec CHAR(1),@pmRefPedido VARCHAR(50) ,@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmNumAutoriza INT,@pmNumAutCupo INT,@pmNumAutCheq INT,@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2) ,@pmFechaRem SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmTipoTrans INT,@pmTipoOrden VARCHAR(3),@pmTipoModifica VARCHAR(10),@pmFecUpdate SMALLDATETIME AS UPDATE Trn_Opedido SET Fecha=@pmFecha,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdClieFact=@pmIdClieFact,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrNeto=@pmVrNeto ,Cantidad=@pmCantidad,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirEnvio=@pmDirEnvio,IdLocEnv=@pmIdLocEnv,LugarEnvio=@pmLugarEnvio,DiasEntraga=@pmDiasEntraga,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargoContac=@pmCargoContac,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo ,CdMney=@pmCdMney,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,AsignarVeh=@pmAsignarVeh,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,CdRuta=@pmCdRuta,ListaPrec=@pmListaPrec,RefPedido=@pmRefPedido,Modalidad=@pmModalidad,Vigencia=@pmVigencia,NumAutoriza=@pmNumAutoriza,NumAutCupo=@pmNumAutCupo,NumAutCheq=@pmNumAutCheq,NumAprob=@pmNumAprob,IdCiaApr=@pmIdCiaApr ,FecAprob=@pmFecAprob,DetalleAprob=@pmDetalleAprob,CdUsuAprob=@pmCdUsuAprob,TipFac=@pmTipFac,Factura=@pmFactura,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,TipRem=@pmTipRem,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,FechaRem=@pmFechaRem,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,TipoModifica=@pmTipoModifica,IdEstado=@pmIdEstado ,ZonaFrontera=@pmZonaFrontera,TipoTrans=@pmTipoTrans,TipoOrden=@pmTipoOrden, FecUpdate=@pmFecUpdate WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpVehiculosRetB @pmIdVehiculo VARCHAR(10),@pmNumContrato INT ,@pmFecRetiro SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmObservacion VARCHAR(250) ,@pmFechaUpdate SMALLDATETIME,@pmInactivo BIT AS UPDATE Vehiculos SET NContrato=@pmNumContrato,FecRetiro=@pmFecRetiro ,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,Observacion=@pmObservacion,FechaUpdate=@pmFechaUpdate WHERE IdVehiculo=@pmIdVehiculo GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpZonas @pmIdZona VARCHAR(4),@pmZona VARCHAR(50),@pmCdSubCos VARCHAR(16),@pmInactivo BIT AS UPDATE Zonas SET Zona=@pmZona,CdSubCos=@pmCdSubCos,Inactivo=@pmInactivo WHERE IdZona=@pmIdZona GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasNetDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryFacturasNetDet] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryFacturas_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFactconNetDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryFactconNetDet] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuia_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryGuia_Cr] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryFacturasNetDet @pmTipDoc 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,@pmIdProducto VARCHAR(16)=Null,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null ,@pmTipoRef VARCHAR(10)=Null AS SELECT F.TipDoc AS TipoFact,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente ,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto ,VrNeto,CantPuntos,PuntosAcum,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio ,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta ,TipPed,Pedido,IdCiaPed,TipRem,F.Remision AS NumRemision,F.IdCiaRem AS CdCiaRem,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CdCiaCotiza,FecPedido ,Modalidad,KmtVehic,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,F.TimeSys AS Fecha_Add,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario --detalles ,K.IdProducto AS CdProducto,DescripProd,Item,K.IdBodega AS CdBodega,Bodega,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario ,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva AS TarifIva,VrIvaEnt,VrIvaSal,K.TarifaRet AS TarifRet,VrReteEnt,VrReteSal ,K.TarifaIca AS TarifIca,VrIcaEnt,VrIcaSal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden ,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,Comision,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper ,K.pVehiculo AS KarPlacaVeh,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,K.CdSubCos AS CodSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,K.Remision AS KarNumRemision,K.IdCiaRem AS KarCdCiaRem,K.Cotizacion AS KarNumCotizacion ,K.IdCiaCot AS KarCiaCotiza,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo ,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca ,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5 --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.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte --información del cliente ,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,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_Facturas AS F INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN TercCliente AS CLI ON F.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 Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.IdCia 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 Terceros AS VN ON K.IdVend=VN.IdTercero LEFT JOIN Agencias AS KA ON K.CdAgencia=KA.IdAgencia LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos LEFT JOIN Localidades AS LK ON K.CdLocal=LK.IdLocal LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero LEFT JOIN Localidades AS LE ON F.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsProdBase=0 AND F.TipDoc LIKE ISNULL(@pmTipDoc,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND F.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%') AND F.IdVend LIKE ISNULL(@pmIdVend,'%') AND F.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%') UNION SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.IdCliente AS NitCliente,T.RazonSocial AS NomCliente ,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,D.VrRetencion AS ReteFte,D.VrReteICA AS ReteIca,D.VrReteIVA AS ReteIVA,D.VrFletes AS Fletes ,D.VrOtros AS Otros,D.VrCargos AS OtrosCargos,D.VrOtrDcto AS OtrosDctos,D.VrNeto AS TotalNeto,D.CantPuntos AS CantPuntDev,0,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio ,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor ,CdRuta,Ruta,TipPed,D.Pedido AS NumPedido,D.IdCiaPed AS CdCiaPed,TipRem,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem,TipCot,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCotiza ,D.FecPedido AS FechaPed,D.Modalidad AS ModFactura,D.KmtVehic AS DevKmtVeh,D.TipCom AS TipoComp,D.Comprobante AS NumComp,D.IdCiaCom AS CodCiaCom,0,D.Factura AS NumFactura,FecDoc ,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fech_Sys,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS IdUsuari,Usuario --detalles ,K.IdProducto AS CdProducto,DescripProd,Item,K.IdBodega AS CdBodega,Bodega,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario ,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva AS TarifIva,VrIvaEnt,VrIvaSal,K.TarifaRet AS TarifRet,VrReteEnt,VrReteSal ,K.TarifaIca AS TarifIca,VrIcaEnt,VrIcaSal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden ,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,Comision,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper ,K.pVehiculo AS KarPlacaVeh,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,K.CdSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,K.Remision AS KarNumRemision,K.IdCiaRem AS KarCdCiaRem,K.Cotizacion AS KarNumCotizacion ,K.IdCiaCot AS KarCiaCotiza,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo ,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca ,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5 --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.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte --información del cliente ,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,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_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 Agencias AS A ON D.IdAgencia=A.IdAgencia 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 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 Trn_Facturas AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia 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 Terceros AS VN ON K.IdVend=VN.IdTercero LEFT JOIN Agencias AS KA ON K.CdAgencia=KA.IdAgencia LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos LEFT JOIN Localidades AS LK ON K.CdLocal=LK.IdLocal LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero LEFT JOIN Localidades AS LE ON D.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsProdBase=0 AND D.TipDoc LIKE ISNULL(@pmTipDoc ,'%') AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%') AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%') AND D.Modalidad LIKE ISNULL(@pmModalidad,'%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%') ORDER BY F.IdCia,F.Factura GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryFacturas_Cr @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2) AS SELECT F.TipDoc AS Tip_Doc,TipoDoc,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente ,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes ,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,BaseIca,BaseRiv ,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,F.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,F.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom ,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,DiasEntraga ,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac,F.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans ,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta,TipPed,Pedido,IdCiaPed,TipRem,F.Remision AS NumRemision,F.IdCiaRem AS CdCiaRem,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CiaCotiza,FecPedido ,AutzaMora,AutzaCupo,Modalidad,KmtVehic,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev ,F.Observacion AS Observ,ZonaFrontera,F.IdEstado AS CdEstado,Estado,F.TimeSys AS Fec_Add,F.FecUpdate AS Fec_Update,IdCiaCrea,F.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.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 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 --campos de memo ,DM.Comentarios AS Comentario,Nota1,Nota2,Nota3 FROM Trn_Facturas AS F INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.IdCia INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN Terceros AS VN ON F.IdVend=VN.IdTercero INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN CentroCosto AS CC ON F.IdCCosto=CC.IdCCosto INNER JOIN TercCliente AS CLI ON F.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 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 F.IdLocEnv=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta 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 F.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 F.TipCom=TCM.IdCom LEFT JOIN Trn_DocMemo AS DM ON F.TipDoc=DM.TipDoc AND F.Factura=DM.Documento AND F.IdCia=DM.IdCia WHERE F.TipDoc=@pmTipDoc AND F.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia AND EsProdBase=0 ORDER BY F.Factura,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryFactconNetDet @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null ,@pmIdConcepto VARCHAR(4)=Null,@pmnClieCon VARCHAR(16)=Null,@pmIdCajero VARCHAR(11)=Null,@pmIdVend VARCHAR(16)=Null,@pmNitCliente VARCHAR(16)=Null ,@pmEnEfectivo BIT=Null,@pmModalidad VARCHAR(10)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null ,@pmIdSubgrupo VARCHAR(8)=Null,@pmTipoRef VARCHAR(10)=Null AS SELECT F.TipDoc AS TipoFact,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,Concepto,nClieCon,Nombre,CC.TipoId AS CliTipoId,CC.Dv AS CliDV,CC.Direccion AS CliDireccion,CC.IdLocal AS CliCodCiudad ,LE.Localidad AS CiudadClie,LE.IdDep AS CdDepClie,DE.Departamento AS DptoClie,CC.Telefono AS CliTelefono,CC.email AS ClieEmail,Excento,VrRetencion,VrReteICA,VrReteIVA,VrFletes ,VrCargos,VrOtrDcto,VrNeto,VrEfectivo,VrPagosOtr,CantPuntos,PuntosAcum,IdCajero,UC.Usuario AS UsuCajero,UC.IdEstacion AS UsuEstacion ,NitCliente,T.RazonSocial AS NombreClie,F.CdAgencia AS CodAgencClie,Agencia,CodAgencia,EnEfectivo,CdForma,NumForma,DetallePago,Referncia1,Referncia2,CdBanco,Banco ,TipPed,Pedido,IdCiaPed,TipRem,F.Remision AS NumRemision,F.IdCiaRem AS CdCiaRem,FecPedido,Modalidad,PlacaVehic,KmtVehic,TipCom,Comprobante,IdCiaCom ,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,F.TimeSys AS Fech_Add,IdCiaCrea,F.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario --detalles ,K.IdProducto AS CdProducto,DescripProd,Item,K.IdBodega AS CdBodega,Bodega,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario ,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva AS TarifIva,VrIvaEnt,VrIvaSal,K.TarifaRet AS TarifRet,VrReteEnt,VrReteSal ,K.TarifaIca AS TarifIca,VrIcaEnt,VrIcaSal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden ,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,K.Comision AS TarifCom,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper ,K.pVehiculo AS KarPlacaVeh,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,K.CdSubCos AS CodSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,K.Remision AS KarNumRemision ,K.IdCiaRem AS KarCdCiaRem,K.Cotizacion AS KarNumCotizacion,K.IdCiaCot AS KarCiaCotiza,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo ,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca ,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5 --datos 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 TercCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,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 ,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie FROM Trn_Factcon AS F INNER JOIN ClieContado AS CC ON F.nClieCon=CC.IdCliente INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario INNER JOIN adm_Usuarios AS UC ON F.IdCajero=UC.IdUsuario INNER JOIN Localidades AS LE ON CC.IdLocal=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.IdCia 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 Terceros AS VN ON K.IdVend=VN.IdTercero LEFT JOIN Terceros AS T ON F.NitCliente=T.IdTercero LEFT JOIN Agencias AS A ON F.CdAgencia=A.IdAgencia LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal LEFT JOIN Departamentos AS D ON L.IdDep=D.IdDep LEFT JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen LEFT JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector LEFT JOIN TercCliente AS CLI ON F.NitCliente=CLI.IdClie LEFT JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona LEFT JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Bancos AS BF ON F.CdBanco=BF.IdBanco LEFT JOIN CentroCosto AS CCT ON K.CdCCosto=CCT.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos LEFT JOIN Localidades AS LK ON K.CdLocal=LK.IdLocal LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsProdBase=0 AND F.TipDoc LIKE ISNULL(@pmTipDoc,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND nClieCon LIKE ISNULL(@pmnClieCon,'%') AND IdCajero LIKE ISNULL(@pmIdCajero,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%') AND F.IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1)) AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%') UNION SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.nClieCon AS NitClieCon,Nombre,CC.TipoId AS CliTipoId ,CC.Dv AS CliDV,CC.Direccion AS CliDireccion,CC.IdLocal AS CliCodCiudad,LE.Localidad AS CiudadClie,LE.IdDep AS CdDepClie,DE.Departamento AS DptoClie ,CC.Telefono AS CliTelefono,CC.email AS ClieEmail,Excento,D.VrRetencion AS ReteFte,D.VrReteICA AS ReteICA,D.VrReteIVA AS ReteIVA,D.VrFletes AS Fletes ,D.VrCargos AS OtrosCargos,D.VrOtrDcto AS OtrosDctos,D.VrNeto AS TotalNeto,D.VrEfectivo AS TotalEfectivo,D.VrPagosOtr AS OtrosPagos ,D.CantPuntos AS CantPuntosDev,PuntosAcum,D.IdCajero AS CdCajero,UC.Usuario AS Cajero,UC.IdEstacion AS UsuEstacion ,D.NitCliente AS DevNitCliente,T.RazonSocial AS NomCliente,D.CdAgencia AS IdAgncia,Agencia,CodAgencia,D.EnEfectivo AS DevEnEfectivo,CdForma,NumForma,DetallePago,Referncia1,Referncia2,CdBanco,Banco ,TipPed,D.Pedido AS NumPedido,D.IdCiaPed AS CdCiaPed,TipRem,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem,D.FecPedido AS FechaPed,D.Modalidad AS ModDoc,D.PlacaVehic AS DevPlacaVeh,D.KmtVehic AS DevKmtVehic ,D.TipCom AS TipoComp,D.Comprobante AS NumComp,D.IdCiaCom AS CodCiaCom,0,D.Factura AS NumFactura,FecDoc ,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fec_Add,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario --detalles ,K.IdProducto AS CdProducto,DescripProd,Item,K.IdBodega AS CdBodega,Bodega,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario ,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva AS TarifIva,VrIvaEnt,VrIvaSal,K.TarifaRet AS TarifRet,VrReteEnt,VrReteSal ,K.TarifaIca AS TarifIca,VrIcaEnt,VrIcaSal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden ,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,K.Comision AS TarifCom,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper ,K.pVehiculo AS KarPlacaVeh,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,K.CdSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,K.Remision AS KarNumRemision ,K.IdCiaRem AS KarCdCiaRem,K.Cotizacion AS KarNumCotizacion,K.IdCiaCot AS KarCiaCotiza,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo ,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca ,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5 --datos 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 TercCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,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 ,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie FROM Trn_DevFco AS D INNER JOIN ClieContado AS CC ON D.nClieCon=CC.IdCliente INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 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 adm_Usuarios AS UC ON D.IdCajero=UC.IdUsuario INNER JOIN Localidades AS LE ON CC.IdLocal=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep INNER JOIN Trn_Factcon AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia 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 Terceros AS VN ON K.IdVend=VN.IdTercero LEFT JOIN Terceros AS T ON D.NitCliente=T.IdTercero LEFT JOIN Agencias AS A ON D.CdAgencia=A.IdAgencia LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal LEFT JOIN Departamentos AS DP ON L.IdDep=DP.IdDep LEFT JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen LEFT JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector LEFT JOIN TercCliente AS CLI ON D.NitCliente=CLI.IdClie LEFT JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona LEFT JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Bancos AS BF ON F.CdBanco=BF.IdBanco LEFT JOIN CentroCosto AS CCT ON K.CdCCosto=CCT.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos LEFT JOIN Localidades AS LK ON K.CdLocal=LK.IdLocal LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsProdBase=0 AND D.TipDoc LIKE ISNULL(@pmTipDoc,'%') AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.nClieCon LIKE ISNULL(@pmnClieCon,'%') AND D.IdCajero LIKE ISNULL(@pmIdCajero,'%') AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.NitCliente LIKE ISNULL(@pmNitCliente ,'%') AND (D.EnEfectivo=ISNULL(@pmEnEfectivo,0) or D.EnEfectivo=ISNULL(@pmEnEfectivo,1)) AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%') AND D.Modalidad LIKE ISNULL(@pmModalidad,'%') ORDER BY F.IdCia,F.Factura GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryGuia_Cr @pmTipDoc VARCHAR(3),@pmGuiaIni INT,@pmGuiaFin INT,@pmIdCia CHAR(2) AS SELECT O.TipDoc AS Tip_Doc,TipoDoc,Guia,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaDesp,FechaVence,IdCliente,T.RazonSocial AS NomCliente ,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,Cantidad,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio ,LugarEnv,HorasVig,O.IdVehiculo,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta ,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,CdCiuOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CdDepOrigen,DO.Departamento AS DptoOrigen ,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact,OrigenAdd,ZonaFrontera,Anulado,TipoTemp,O.Temperatura,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado ,O.TimeSys AS Fec_Add,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS IdUsuari,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,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,CLI.NumCuenta AS NumeroCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato ,DirAgncia,TelAgncia,FaxAgncia,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,K.IdTercero AS KarNitTercero,CdAgencia,CdCCosto,K.CdSubCos AS CodSubCos,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia ,K.Descripcion AS KarDescricion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,K.Remision AS KarRemision,K.IdCiaRem AS CdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase ,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden --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,Compania --Inf. vehículo ,CdRemque,VehArtic,NumVeh FROM Trn_Guia AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Guia=K.Documento AND O.IdCia=K.IdCia INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia INNER JOIN Terceros AS TC ON O.IdConductor=TC.IdTercero INNER JOIN TercCliente AS CLI ON O.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 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 O.IdLocal=LE.IdLocal LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep LEFT JOIN Localidades AS LO ON O.CdCiuOrigen=LO.IdLocal LEFT JOIN Departamentos AS DO ON LO.IdDep=DO.IdDep LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor LEFT JOIN Vehiculos AS VH ON O.IdVehiculo=VH.IdVehiculo WHERE O.TipDoc=@pmTipDoc AND Guia BETWEEN @pmGuiaIni AND @pmGuiaFin AND O.IdCia=@pmIdCia AND EsProdBase=0 ORDER BY Guia,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsProdCombo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsProdCombo] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdCombo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryProdCombo] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdComboLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryProdComboLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdComboPro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryProdComboPro] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpProdCombo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpProdCombo] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsFormasRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEdsFormasRel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsFormasRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEdsFormasRelDet] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsProdCombo @pmIdProducto VARCHAR(16),@pmItem INT,@pmIdProdBas VARCHAR(16),@pmCant DECIMAL(14,4) ,@pmCdBodega VARCHAR(4),@pmCdTanq VARCHAR(4),@pmFacMezcla VARCHAR(10),@pmExentoSob BIT AS INSERT INTO ProdCombo (IdProducto,Item,IdProdBas,Cant,CdBodega,CdTanq,FacMezcla,ExentoSob) VALUES (@pmIdProducto,@pmItem,@pmIdProdBas,@pmCant,@pmCdBodega,@pmCdTanq,@pmFacMezcla,@pmExentoSob) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryProdCombo @pmIdProducto VARCHAR(16),@pmItem INT AS SELECT IdProducto,Item,IdProdBas,Cant,FacMezcla,CdBodega,CdTanq,ExentoSob FROM ProdCombo WHERE IdProducto=@pmIdProducto AND (Item>=ISNULL(@pmItem,0) AND Item<=ISNULL(@pmItem,2147483647)) ORDER BY Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryProdComboLta @pmIdProducto VARCHAR(16)=Null,@pmIdProdBas VARCHAR(16)=Null AS SELECT PC.IdProducto AS CdProd,P.DescripProd AS Prodcto,Item,IdProdBas,PB.DescripProd AS ProdBase ,Cant,FacMezcla,CdBodega,Bodega,CdTanq,P.Tanques AS ProdTanques,PB.Tanques AS ProBasTanq,ExentoSob FROM ProdCombo AS PC INNER JOIN ProdMcias AS P ON PC.IdProducto=P.IdProducto INNER JOIN ProdMcias AS PB ON PC.IdProdBas=PB.IdProducto LEFT JOIN Bodegas AS B ON PC.CdBodega=B.IdBodega WHERE PC.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND IdProdBas LIKE ISNULL(@pmIdProdBas,'%') ORDER BY P.DescripProd,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryProdComboPro @pmIdProducto VARCHAR(16) AS SELECT C.IdProducto AS CdProducto,Item,IdProdBas,Cant,FacMezcla,CdBodega,CdTanq ,DescripProd,TipoRef,IdSubgrupo,IdUnd,IdBodega,IdUbic ,VrCostPmd,Seriales,Lotes,Combo,Tanques,Precio1,Precio2,Precio3,Precio4,Precio5,ExentoSob FROM ProdCombo AS C INNER JOIN ProdMcias AS P ON C.IdProdBas=P.IdProducto WHERE C.IdProducto=@pmIdProducto ORDER BY Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpProdCombo @pmIdProducto VARCHAR(16),@pmItem INT,@pmIdProdBas VARCHAR(16),@pmCant DECIMAL(14,4) ,@pmCdBodega VARCHAR(4),@pmCdTanq VARCHAR(4),@pmFacMezcla VARCHAR(10),@pmExentoSob BIT AS UPDATE ProdCombo SET IdProdBas=@pmIdProdBas,Cant=@pmCant,CdBodega=@pmCdBodega,CdTanq=@pmCdTanq ,FacMezcla=@pmFacMezcla,ExentoSob=@pmExentoSob WHERE IdProducto=@pmIdProducto AND Item=@pmItem GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEdsFormasRel @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipoRango VARCHAR(3)=Null ,@pmNumSerie VARCHAR(5)=Null,@pmPlanillaIni INT=Null,@pmPlanillaFin INT=Null,@pmIdCia CHAR(2)=Null ,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdCuenta VARCHAR(16)=Null,@pmDocumentoIni INT=Null,@pmDocumentoFin INT=Null,@pmAnulado BIT=Null ,@pmFechaDocIni SMALLDATETIME=Null,@pmFechaDocFin SMALLDATETIME=Null AS SELECT TipoRango,NumSerie,NumForma,F.Planilla AS NumPlanilla,F.IdCia AS CdCia,Compania,F.Fecha AS FechaVale,VrTotal,IdCliente,T.RazonSocial AS NomCliente ,F.IdAgencia AS Id_Agencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,pVehiculo,nVehiculo,F.IdCajero AS CdCajero,Usuario,F.IdVend AS NitVend,V.RazonSocial AS Vendedor ,CdProducto,DescripProd,F.Cantidad AS Cant,F.IdCuenta AS CodCuenta,NomCuenta,F.IdForma AS CdForma,FormaPago,F.Referencia AS Referncia,F.Observacion AS Observ ,TipDcm,Documento,IdCiaDcm,FechaDcm,AgencDcm,AD.Agencia AS DocAgencia,F.Anulado AS EstaAnulado,F.FecDev AS FechaDev ,IdRango,Item,CP.IdJornada AS CdJornada,Jornada,NumCorte,FecCorte --Información del tercero ,T.TipoId AS TercTipo,T.Dv AS TercDv,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 --información del cliente ,CLI.IdSzona AS CdSubzona,Subzona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie ,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia ,CdCCosto,F.CdSubCos AS CodSubCos FROM Trn_EdsFormas AS F INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta INNER JOIN adm_Usuarios AS U ON F.IdCajero=U.IdUsuario INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN Companias AS C ON F.IdCia=C.IdCia INNER JOIN TercCliente AS CLI ON F.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 Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep LEFT JOIN ProdMcias AS R ON F.CdProducto=R.IdProducto LEFT JOIN Agencias AS AD ON F.AgencDcm=AD.IdAgencia LEFT JOIN Trn_EdsCortes AS CP ON F.Planilla=CP.Planilla AND F.IdCia=CP.IdCia LEFT JOIN Jornadas AS J ON CP.IdJornada=J.IdJornada WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipoRango LIKE ISNULL(@pmTipoRango,'%') AND NumSerie LIKE ISNULL(@pmNumSerie,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND F.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND F.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND F.Planilla BETWEEN ISNULL(@pmPlanillaIni,-1) AND ISNULL(@pmPlanillaFin,2147483647) AND Documento BETWEEN ISNULL(@pmDocumentoIni,-1) AND ISNULL(@pmDocumentoFin,2147483647) AND (F.Anulado=ISNULL(@pmAnulado,0) or F.Anulado=ISNULL(@pmAnulado,1)) AND (ISNULL(FechaDcm,F.Fecha)>=ISNULL(@pmFechaDocIni,CAST('19100101' AS SMALLDATETIME)) AND ISNULL(FechaDcm,F.Fecha)<=ISNULL(@pmFechaDocFin,CAST('20781230' AS SMALLDATETIME))) ORDER BY T.RazonSocial,F.Fecha,TipoRango,NumSerie,NumForma GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEdsFormasRelDet @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipoRango VARCHAR(3)=Null ,@pmNumSerie VARCHAR(5)=Null,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdCuenta VARCHAR(16)=Null ,@pmDocumentoIni INT=Null,@pmDocumentoFin INT=Null,@pmAnulado BIT=Null,@pmFechaDocIni SMALLDATETIME=Null,@pmFechaDocFin SMALLDATETIME=Null ,@pmIdProducto VARCHAR(16)=Null AS SELECT F.TipoRango AS TipRango,F.NumSerie AS Num_Serie,F.NumForma AS NumVale,F.IdCia AS CdCia,Compania,F.Fecha AS FechaVale --detalle del vale ,FD.Item AS NumItem,FD.IdProducto AS CodProducto,DescripProd,FD.IdBodega AS CdBodega,Bodega,FD.Cantidad AS Cant,VrPrecio,FD.Cantidad*VrPrecio AS ValorTotal,TarifaDct,VrDcto,TarifaIva,VrIva ,Servicios,Descripcion,ListaPrec,VrTotal AS ValorVale,IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS Id_Agencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia ,pVehiculo,nVehiculo,F.IdCajero AS CdCajero,Usuario,F.IdVend AS NitVend,V.RazonSocial AS Vendedor ,F.IdCuenta AS CodCuenta,NomCuenta,F.IdForma AS CdForma,FormaPago,F.Referencia AS Referncia,F.Observacion AS Observ ,TipDcm,Documento,IdCiaDcm,FechaDcm,AgencDcm,AD.Agencia AS DocAgencia,F.Anulado AS EstaAnulado,F.FecDev AS FechaDev,F.IdRango AS NumRango,Planilla --Información del tercero ,T.TipoId AS TercTipo,T.Dv AS TercDv,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 --información del cliente ,CLI.IdSzona AS CdSubzona,Subzona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie ,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia ,CdCCosto,F.CdSubCos AS CodSubCos FROM Trn_EdsFormas AS F INNER JOIN Trn_EdsForItems AS FD ON F.TipoRango=FD.TipoRango AND F.IdRango=FD.IdRango AND F.NumSerie=FD.NumSerie AND F.NumForma=FD.NumForma INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta INNER JOIN adm_Usuarios AS U ON F.IdCajero=U.IdUsuario INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN Companias AS C ON F.IdCia=C.IdCia INNER JOIN TercCliente AS CLI ON F.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 Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep INNER JOIN ProdMcias AS R ON FD.IdProducto=R.IdProducto INNER JOIN Bodegas AS B ON FD.IdBodega=B.IdBodega LEFT JOIN Agencias AS AD ON F.AgencDcm=AD.IdAgencia WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND F.TipoRango LIKE ISNULL(@pmTipoRango,'%') AND F.NumSerie LIKE ISNULL(@pmNumSerie,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND F.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND F.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND Documento BETWEEN ISNULL(@pmDocumentoIni,-1) AND ISNULL(@pmDocumentoFin,2147483647) AND (F.Anulado=ISNULL(@pmAnulado,0) or F.Anulado=ISNULL(@pmAnulado,1)) AND (ISNULL(FechaDcm,F.Fecha)>=ISNULL(@pmFechaDocIni,CAST('19100101' AS SMALLDATETIME)) AND ISNULL(FechaDcm,F.Fecha)<=ISNULL(@pmFechaDocFin,CAST('20781230' AS SMALLDATETIME))) AND FD.IdProducto LIKE ISNULL(@pmIdProducto,'%') ORDER BY T.RazonSocial,F.Fecha,F.TipoRango,F.NumSerie,F.NumForma GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO