if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelProdImpFron]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paDelProdImpFron]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAgencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsCompanias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsDevFcr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsFacturas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsGuia]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsOComb]
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].[paInsProdImpFron]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsProdImpFron]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAgencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasCli]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAgenciasCli]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasCod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAgenciasCod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAgenciasLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompanias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompanias_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompanias_Cr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCompaniasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCompaniasLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryDevFcr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryDevFcrLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcr_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryDevFcr_Cr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDevFcrRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryDevFcrRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFacturas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFacturasCom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFacturasLta]
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].[paQryFacturasRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFacturasRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuia]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuiaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuiaLta]
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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuiaRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuiaRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuiaRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuiaRelDet]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexSub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryKardexSub]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOComb]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOCombLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOCombLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOCombRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOCombRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOComb_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOComb_Cr]
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].[paQryOpedidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOpedidoLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoPen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOpedidoPen]
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].[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].[paQryPagosCns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryPagosCns]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdImpFron]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryProdImpFron]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdImpFronLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryProdImpFronLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdImpFronUlt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryProdImpFronUlt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposDocAut]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTiposDocAut]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposDocFor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTiposDocFor]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposDocPag]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTiposDocPag]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_KdexSub]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpAgencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCompanias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpCompanias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDevFcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpDevFcr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFacturas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpFacturas]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpGuia]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpOComb]
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].[paUpProdImpFron]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpProdImpFron]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelProdImpFron @pmIdProducto VARCHAR(16),@pmIdClase VARCHAR(4),@pmItem INT
 AS 
 DELETE FROM ProdImpFron
 WHERE IdProducto=@pmIdProducto AND IdClase LIKE ISNULL(@pmIdClase,'%')
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAgencias @pmIdAgencia VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmIdClie VARCHAR(16),@pmAgencia VARCHAR(150),@pmDirAgncia VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelAgncia VARCHAR(20),@pmFaxAgncia VARCHAR(20),@pmNitCont VARCHAR(16)
,@pmNomCont VARCHAR(150),@pmemlCont VARCHAR(100),@pmCargoCont VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmCdPlazoComb VARCHAR(4),@pmIdForma VARCHAR(4),@pmCdBandera VARCHAR(4),@pmIdVend VARCHAR(16),@pmCdCms VARCHAR(4),@pmCdDct VARCHAR(4),@pmIntMora DECIMAL(14,4)
,@pmDiasGracia INT,@pmFactFletes BIT,@pmFactSold BIT,@pmVrCupoCre MONEY,@pmVrSaldoAct MONEY,@pmFecUpCupo SMALLDATETIME,@pmNContrato INT,@pmCiaCont CHAR(2),@pmFecIngreso SMALLDATETIME,@pmReferencia VARCHAR(50),@pmComentarios VARCHAR(250),@pmCupoGalones DECIMAL(14,4)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoZona VARCHAR(10),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Agencias (IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont
,FecIngreso,Referencia,Comentarios,CupoGalones,TipoZona,IdEstado,Inactivo,FechaAdd,IdUsuario)
 VALUES (@pmIdAgencia,@pmCodAgencia,@pmIdClie,@pmAgencia,@pmDirAgncia,@pmIdLocal,@pmTelAgncia,@pmFaxAgncia,@pmNitCont,@pmNomCont,@pmemlCont,@pmCargoCont,@pmIdSzona,@pmIdPlazo,@pmCdPlazoComb,@pmIdForma,@pmCdBandera,@pmIdVend
,@pmCdCms,@pmCdDct,@pmIntMora,@pmDiasGracia,@pmFactFletes,@pmFactSold,@pmVrCupoCre,@pmVrSaldoAct,@pmFecUpCupo,@pmNContrato,@pmCiaCont,@pmFecIngreso,@pmReferencia,@pmComentarios,@pmCupoGalones,@pmTipoZona,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsCompanias @pmIdCia CHAR(2),@pmCompania VARCHAR(50),@pmDirInterface VARCHAR(255),@pmFechaActual SMALLDATETIME
,@pmPeriodoActual SMALLDATETIME,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdTercero VARCHAR(16)
,@pmCodSucsal VARCHAR(10),@pmDireccSuc VARCHAR(250),@pmTelefSuc VARCHAR(20),@pmFaxSuc VARCHAR(20),@pmIdLocal VARCHAR(8),@pmTipoEDS VARCHAR(10)
,@pmFechaAdd SMALLDATETIME,@pmInactivo BIT
 AS 
 INSERT INTO Companias (IdCia,Compania,DirInterface,FechaActual,PeriodoActual,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,FechaAdd,Inactivo)
 VALUES (@pmIdCia,@pmCompania,@pmDirInterface,@pmFechaActual,@pmPeriodoActual,@pmIdCCosto,@pmIdSubCos,@pmIdTercero
,@pmCodSucsal,@pmDireccSuc,@pmTelefSuc,@pmFaxSuc,@pmIdLocal,@pmTipoEDS,@pmFechaAdd,@pmInactivo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsDevFcr @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY
,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY
,@pmBaseRet MONEY,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocEnv VARCHAR(8),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10)
,@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DevFcr (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,IdLocEnv
,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,ZonaFrontera,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmFactura,@pmIdCiaDoc,@pmFecDoc,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrCostos,@pmVrSobretasa,@pmVrImpGlobal,@pmVrFaltantes
,@pmVrAnticipos,@pmVrNeto,@pmCantidad,@pmCantPuntos,@pmBaseImp,@pmBaseRet,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmIdLocEnv,@pmPedido,@pmIdCiaPed,@pmRemision,@pmIdCiaRem,@pmCotizacion,@pmIdCiaCot,@pmFecPedido,@pmModalidad,@pmModdDev,@pmPlacaVehic,@pmKmtVehic,@pmZonaFrontera,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom
,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFacturas @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY
,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmPuntosAcum DECIMAL(14,4),@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),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8)
,@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),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmTipCot VARCHAR(3)
,@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmModalidad VARCHAR(10),@pmKmtVehic INT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmZonaFrontera BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Facturas (TipDoc,Factura,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,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,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,CdRuta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem
,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,KmtVehic,ZonaFrontera,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmFechaVence,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrCostos,@pmVrSobretasa,@pmVrImpGlobal,@pmVrFaltantes,@pmVrAnticipos,@pmVrNeto,@pmVrAplicado,@pmCantidad,@pmCantPuntos,@pmPuntosAcum,@pmBaseImp,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaIva,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmDirEnvio
,@pmIdLocEnv,@pmDiasEntraga,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmNitEmpTrans,@pmEmpTrans,@pmpVehiculo,@pmCdConductor,@pmCdRuta,@pmTipPed,@pmPedido,@pmIdCiaPed,@pmTipRem,@pmRemision,@pmIdCiaRem,@pmTipCot,@pmCotizacion,@pmIdCiaCot,@pmFecPedido,@pmAutzaMora,@pmAutzaCupo,@pmModalidad,@pmKmtVehic,@pmZonaFrontera,@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 paInsGuia @pmTipDoc VARCHAR(3),@pmGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmCantidad DECIMAL(14,4),@pmDirEnvio VARCHAR(250),@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmHorasVig DECIMAL(14,4),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16)
,@pmEmpTrans VARCHAR(150),@pmCdRuta VARCHAR(4),@pmCdCiuOrigen VARCHAR(8),@pmReferncia VARCHAR(50),@pmTipOcc VARCHAR(3),@pmOCargue INT,@pmIdCiaOcc CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Guia (TipDoc,Guia,IdCia,Fecha,FechaDesp,FechaVence,IdCliente,IdAgencia,Cantidad,DirEnvio,IdLocal,LugarEnv,HorasVig,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans,CdRuta,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,Factura,IdCiaFac,FechaFact,OrigenAdd,ZonaFrontera,Anulado,FecDev,Observacion,IdEstado,CdCiuOrigen,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmGuia,@pmIdCia,@pmFecha,@pmFechaDesp,@pmFechaVence,@pmIdCliente,@pmIdAgencia,@pmCantidad,@pmDirEnvio,@pmIdLocal,@pmLugarEnv,@pmHorasVig,@pmIdVehiculo,@pmIdConductor,@pmNitEmpTrans,@pmEmpTrans,@pmCdRuta,@pmReferncia,@pmTipOcc,@pmOCargue,@pmIdCiaOcc,@pmTipFac,@pmFactura,@pmIdCiaFac
,@pmFechaFact,@pmOrigenAdd,@pmZonaFrontera,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmCdCiuOrigen,@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 paInsOComb @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia 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)
,@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmDiasEntraga INT,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmCdRuta VARCHAR(4),@pmReferncia VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(150)
,@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipGui VARCHAR(3),@pmGuia INT,@pmIdCiaGui CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmAprobacion INT,@pmFechaAprob SMALLDATETIME
,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OComb (TipDoc,OCargue,IdCia,Fecha,FechaDesp,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocal,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans,CdRuta,Referncia
,IdForma,DetallePago,MulPlazos,IdPlazo,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmOCargue,@pmIdCia,@pmFecha,@pmFechaDesp,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobretasa,@pmVrImpGlobal,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom
,@pmDirEnvio,@pmIdLocal,@pmLugarEnv,@pmDiasEntraga,@pmIdVehiculo,@pmIdConductor,@pmNitEmpTrans,@pmEmpTrans,@pmCdRuta,@pmReferncia,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmTipPed,@pmPedido,@pmIdCiaPed,@pmTipGui,@pmGuia,@pmIdCiaGui,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmAutzaMora,@pmAutzaCupo,@pmAprobacion
,@pmFechaAprob,@pmDetalleAprob,@pmCdUsuAprob,@pmOrigenAdd,@pmZonaFrontera,@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 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, @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,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,@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 ON 
GO

CREATE PROCEDURE paInsProdImpFron @pmIdProducto VARCHAR(16),@pmIdClase VARCHAR(4),@pmItem INT,@pmIdLocal VARCHAR(8),@pmTarifa DECIMAL(14,4),@pmSimbTfa CHAR(1),@pmCdTarifa VARCHAR(4)
,@pmRngFecha BIT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmLtaPre CHAR(1),@pmInactivo BIT,@pmFecUpdate SMALLDATETIME
 AS 
 INSERT INTO ProdImpFron (IdProducto,IdClase,Item,IdLocal,Tarifa,SimbTfa,CdTarifa,RngFecha,FecInicial,FecFinal,LtaPre,Inactivo,FecUpdate)
 VALUES (@pmIdProducto,@pmIdClase,@pmItem,@pmIdLocal,@pmTarifa,@pmSimbTfa,@pmCdTarifa,@pmRngFecha,@pmFecInicial
,@pmFecFinal,@pmLtaPre,@pmInactivo,@pmFecUpdate)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAgencias @pmIdAgencia VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont
,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,FecIngreso,Referencia,Comentarios,CupoGalones
,IdEstado,Inactivo,TipoZona,FechaAdd,FechaUpdate,IdUsuario
 FROM Agencias
 WHERE IdAgencia=@pmIdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAgenciasCli @pmIdClie VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,RazonSocial,Codigo,Dv,Agencia,DirAgncia,A.IdLocal AS CdCiudad
,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,VrCupoCre,VrSaldoAct,Referencia
,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,FecUpCupo,NContrato,CiaCont,FecIngreso,CupoGalones,A.Inactivo AS Inactvo,A.IdEstado AS Id_Estado,Comentarios,TipoZona
,A.FechaAdd AS FecAdd,A.FechaUpdate AS FecUpd,A.IdUsuario AS IdUsuari
 FROM Agencias AS A INNER JOIN Terceros AS T ON A.IdClie=T.IdTercero
 WHERE A.Inactivo=0 AND IdClie=@pmIdClie
 ORDER BY IdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAgenciasCod @pmIdClie VARCHAR(16),@pmCodAgencia VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont
,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,FecIngreso,Referencia,Comentarios,CupoGalones
,TipoZona,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM Agencias
 WHERE IdClie=@pmIdClie AND CodAgencia LIKE ISNULL(@pmCodAgencia,'%')
 ORDER BY IdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAgenciasLta @pmIdClie VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdZona VARCHAR(4)=Null,@pmIdSzona VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
,@pmIdSector VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 SELECT A.IdClie AS NitCliente,RazonSocial,IdAgencia,CodAgencia,Agencia,DirAgncia,A.IdLocal AS CdCiuAgncia,L.Localidad AS CiudadAgncia
,L.IdDep AS CdDptoAgncia,D.Departamento AS DptoAgncia,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,VrCupoCre,VrSaldoAct,A.FecUpCupo AS FechaUpCupo,Referencia
,A.NContrato AS NroCont,CiaCont,A.FecIngreso AS Fec_Ingreso,NomCial,SiglaRaz,SZA.IdZona AS Age_CdZona,ZA.Zona AS Age_Zona,A.IdSzona AS Age_IdSubzona,SZA.Subzona AS Age_Subzona
,A.IdPlazo AS CdPlazo,Plazo,NVmto,A.CdPlazoComb AS CdPlazoComb,A.IdForma AS CdForma,FormaPago,A.IdVend AS NitVend,A.CdCms AS CodCms,A.CdDct AS CodDct,IntMora,A.DiasGracia AS Dias_gracia,FactFletes,A.FactSold AS FacturaSold
,A.CupoGalones AS CupoGals
,TipoId,Dv,Direccion,T.IdLocal AS CdCiuClie,LT.Localidad AS CiuClie,LT.IdDep AS CdDptoClie,DT.Departamento AS DptoClie
,NitRepLeg,NomRepLeg,NitContac,NomContac,C.IdGrupo AS CdGrupo,GrupoClie,SZ.IdZona AS CdZona,Z.Zona AS CliZona,C.IdSzona AS CdSubzona,SZ.Subzona AS CliSubZona
,A.CdBandera AS CodBandera,TipoBandera,C.IdEstrato AS CdEstrato,Estrato,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRgmen,Regimen,TipEnte,VrCupo,VrSaldo,Telefono,Fax
,TelMovil,e_mail,C.IdEstado AS CdEstado,Estado,C.Inactivo AS Clie_inactivo,A.IdEstado AS Age_Estado,A.Inactivo AS Age_inactivo,IniStgNom,A.FechaAdd AS FecAdd
,TipoZona,Comentarios,A.FechaUpdate AS FecUpd,A.IdUsuario AS IdUsuari,Usuario
 FROM Agencias AS A INNER JOIN Terceros AS T ON A.IdClie=T.IdTercero
 INNER JOIN TercCliente AS C ON A.IdClie=C.IdClie INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON A.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona INNER JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona
 INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal  INNER JOIN Departamentos AS DT ON LT.IdDep=DT.IdDep
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN Estratos AS EC ON C.IdEstrato=EC.IdEstrato
 INNER JOIN SectoresEco AS S ON T.IdSector=S.IdSector INNER JOIN RegimenDian AS R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS E ON C.IdEstado=E.IdEstado INNER JOIN Plazos AS PZ ON A.IdPlazo=PZ.IdPlazo
 INNER JOIN Formaspago AS FP ON A.IdForma=FP.IdForma
 LEFT JOIN TiposBan AS TB ON A.CdBandera=TB.IdBandera
 WHERE A.Inactivo=0 AND A.IdClie LIKE ISNULL(@pmIdClie,'%') AND A.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND C.IdGrupo LIKE ISNULL(@pmIdGrupo ,'%') AND SZA.IdZona LIKE ISNULL(@pmIdZona,'%') AND A.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%') AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND T.IdSector LIKE ISNULL(@pmIdSector,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY RazonSocial,Agencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryCompanias @pmIdCia CHAR(2)
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,FechaAdd,FechaUpdate,Inactivo
 FROM Companias
 WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompanias_Cr @pmInactivo BIT=Null
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,C.IdCCosto AS CodCcosto,CCosto,C.IdSubCos AS CodSubCosto,SubCosto
,IdTercero,CodSucsal,DireccSuc,TelefSuc,FaxSuc,C.IdLocal AS CodCiu,Localidad,TipoEDS,C.FechaAdd AS Fecha_Add,C.FechaUpdate AS FecUpd,C.Inactivo AS Inactvo
 FROM Companias AS C INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto 
 INNER JOIN  SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal
 WHERE (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompaniasLta @pmIdCia CHAR(2)=Null,@pmInactivo BIT=Null
 AS 
 SELECT IdCia,Compania,DirInterface,FechaActual,PeriodoActual,Inactivo,IdCCosto,IdSubCos,IdTercero
,CodSucsal,DireccSuc,TelefSuc,FaxSuc,IdLocal,TipoEDS,FechaAdd,FechaUpdate
 FROM Companias
 WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND (Inactivo=ISNULL(@pmInactivo,0) or Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryDevFcr @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA
,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos,IdVend,TarifaCom
,CodTarCom,IdLocEnv,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,Modalidad,ModdDev,OrigenAdd,TipCom,Comprobante,IdCiaCom
,Observacion,IdEstado,ZonaFrontera,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_DevFcr
 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 paQryDevFcrLta @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmDevolucionIni INT=Null,@pmDevolucionFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT Devolucion,IdCia,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion
,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,IdCCosto,IdSubCos
,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,IdLocEnv,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,Modalidad,ModdDev,ZonaFrontera,OrigenAdd
,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario,TipDev
 FROM Trn_DevFcr AS D  INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON D.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario 
 WHERE TipDev=@pmTipDev  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND Devolucion BETWEEN ISNULL(@pmDevolucionIni,0) AND ISNULL(@pmDevolucionFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND ModdDev LIKE ISNULL(@pmModdDev,'%') 
 ORDER BY IdCia,Devolucion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryDevFcr_Cr @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc AS TipFact,D.Factura AS NumFactura
,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion
,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet
,D.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,D.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,TarifaCom,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,Pedido,IdCiaPed,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem
,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCot,FecPedido,Modalidad,ModdDev,PlacaVehic,KmtVehic,ZonaFrontera,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado
,D.TimeSys AS Fec_Add,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--Detalles
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,KA.NContrato AS KarAgeContrato,KA.Referencia AS KarAgeRefencia,CdLocal,KL.Localidad AS KarCiudad
,CdCCosto,KC.CCosto AS Kar_Ccosto,CdSubCos,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,K.Cotizacion AS KarNumCotiza,K.IdCiaCot AS KarCiaCotiza,K.Remision AS KarRemision,K.IdCiaRem AS KarCiaRem
,K.Factura AS KarFactura,TipDocDev,NumDocDev,K.IdVend AS KarNitVend,KV.RazonSocial AS KarVendedor,K.Comision AS KarTarifaCom,CdOperario,KO.RazonSocial AS NomOperario,ComisnOper,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase
,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
,CdMngra,NumInicial,NumFinal
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5
 FROM Trn_DevFcr AS D  INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos 
 LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia LEFT JOIN Localidades AS KL ON K.CdLocal=KL.IdLocal
 LEFT JOIN Terceros AS KV ON K.IdVend=KV.IdTercero  LEFT JOIN Terceros AS KO ON K.CdOperario=KO.IdTercero
 LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom
 WHERE TipDev=@pmTipDev  AND Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Devolucion,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryDevFcrRel @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdLocEnv VARCHAR(8)=Null
,@pmModalidad VARCHAR(10)=Null,@pmModdDev VARCHAR(10)=Null
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,Fecha,D.IdConcepto AS CdConcepto,Concepto,TipDoc,Factura,IdCiaDoc,FecDoc,IdCliente,T.RazonSocial AS NomCliente
,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos
,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal,VrFaltantes,VrAnticipos,VrNeto,Cantidad,CantPuntos,BaseImp,BaseRet,D.IdCCosto AS IdCenCost,CCosto,D.IdSubCos AS IdSubCent,SubCosto
,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,Pedido,IdCiaPed,Remision,IdCiaRem,Cotizacion,IdCiaCot,FecPedido,PlacaVehic,KmtVehic,Modalidad,ModdDev,ZonaFrontera,OrigenAdd,TipCom,Comprobante,IdCiaCom,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado
,TimeSys,IdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,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
 FROM Trn_DevFcr AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipDev LIKE ISNULL(@pmTipDev ,'%') 
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND D.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND ModdDev LIKE ISNULL(@pmModdDev,'%')
 ORDER BY D.IdCia,Devolucion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFacturas @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Factura,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,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
,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,DiasEntraga
,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,CdRuta
,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,KmtVehic,OrigenAdd,TipCom,Comprobante,IdCiaCom
,ZonaFrontera,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Facturas
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura 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 paQryFacturasCom @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Factura,F.IdCia AS CdCia,Compania,Fecha,IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia
,A.CodAgencia AS Cod_Agencia,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac
,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta,TipPed,Pedido,IdCiaPed
,TipRem,F.Remision AS NumGuia,F.IdCiaRem AS CdCiaGuia,Modalidad,ZonaFrontera,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdUsuario AS CdUsuario,Usuario
--datos del contenedor
,tmNumComp,tmIdProducto,tmCantidad,tmDescProd,tmComp2,tmIdProd2,tmDescProd2,tmCantidad2
,tmComp3,tmIdProd3,tmDescProd3,tmCantidad3,tmComp4,tmIdProd4,tmDescProd4,tmCantidad4
,tmComp5,tmIdProd5,tmDescProd5,tmCantidad5
 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 adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia 
 LEFT JOIN tm_OccComp AS CMP ON F.TipDoc =CMP.tmTipDoc  AND F.Factura=CMP.tmOCargue AND F.IdCia=CMP.tmIdCia
 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  TipDoc=@pmTipDoc AND Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.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 paQryFacturasLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmFacturaIni INT=Null,@pmFacturaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT Factura,IdCia,Fecha,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,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,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio
,IdLocEnv,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans
,EmpTrans,pVehiculo,CdConductor,KmtVehic,CdRuta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad
,OrigenAdd,TipCom,Comprobante,IdCiaCom,ZonaFrontera,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,F.IdUsuario AS IdUsuari,Usuario,TipDoc
 FROM Trn_Facturas F INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON F.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario 
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Factura BETWEEN ISNULL(@pmFacturaIni,0) AND ISNULL(@pmFacturaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND F.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY 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 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,CdSubCos,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 paQryFacturasRel @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,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,Factura,F.IdCia AS CdCia,Compania,Fecha,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,TarifaIva,TarifaRet ,TarifaIca,TarifaRiv,F.IdCCosto AS IdCenCost,CCosto
,F.IdSubCos AS IdSubCent,SubCosto,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,pVehiculo,KmtVehic,CdConductor,TC.RazonSocial AS Conductor
,CdRuta,Ruta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,AutzaMora,AutzaCupo,Modalidad,OrigenAdd,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,F.Observacion AS Observ,ZonaFrontera,F.IdEstado AS CdEstado,Estado,TimeSys,F.FecUpdate AS Fec_Update,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.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
 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  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
 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 SubCentros AS SC ON F.IdSubCos=SC.IdSubCos 
 WHERE TipDoc LIKE ISNULL(@pmTipDoc,'%') AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 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 (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY F.IdCia,Factura
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryGuia @pmTipDoc VARCHAR(3),@pmGuia INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Guia,IdCia,Fecha,FechaDesp,FechaVence,IdCliente,IdAgencia,Cantidad,DirEnvio,IdLocal,LugarEnv,HorasVig
,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans,CdRuta,CdCiuOrigen,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,Factura,IdCiaFac,FechaFact
,OrigenAdd,ZonaFrontera,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Guia
 WHERE TipDoc=@pmTipDoc AND Guia=@pmGuia 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 paQryGuiaLta  @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmGuiaIni INT=Null,@pmGuiaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT Guia,IdCia,Fecha,FechaDesp,FechaVence,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,Cantidad,DirEnvio,G.IdLocal AS CdCiudad,LugarEnv,HorasVig
,IdVehiculo,IdConductor,CD.RazonSocial AS Conductor,NitEmpTrans,EmpTrans,CdRuta,CdCiuOrigen,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,Factura,IdCiaFac,FechaFact
,OrigenAdd,ZonaFrontera,Anulado,FecDev,G.Observacion AS Observ,G.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,G.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Guia AS G  INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero
 INNER JOIN Terceros AS CD ON G.IdConductor=CD.IdTercero
 INNER JOIN EstadoDoc AS ED ON G.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Guia BETWEEN ISNULL(@pmGuiaIni,0) AND ISNULL(@pmGuiaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND G.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Guia
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,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,CdSubCos,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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryGuiaRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmCdCiuOrigen VARCHAR(8)=Null,@pmIdLocal VARCHAR(8)=Null
,@pmIdConductor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,Guia,O.IdCia AS CdCia,Compania,Fecha,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,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,Factura,IdCiaFac,FechaFact,OrigenAdd,ZonaFrontera,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,TimeSys,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS IdUsuari
--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
 FROM  Trn_Guia AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia
 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
 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
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND O.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%') AND CdCiuOrigen LIKE ISNULL(@pmCdCiuOrigen,'%')
 AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,Guia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryGuiaRelDet @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmCdCiuOrigen VARCHAR(8)=Null,@pmIdLocal VARCHAR(8)=Null
,@pmIdConductor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT O.TipDoc AS CdTipDoc,Guia,O.IdCia AS CdCia,Compania,O.Fecha AS FechaGuia,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,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,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.TimeSys,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS IdUsuari
--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
--datos de kardex
,K.IdProducto AS CdProducto,Salidas,K.Referencia,DescripProd,Descripcion
 FROM  Trn_Guia AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia
 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 Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Guia=K.Documento  AND  O.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 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
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND O.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%') AND CdCiuOrigen LIKE ISNULL(@pmCdCiuOrigen,'%')
 AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,Guia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexSub @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,Combo,EsProdBase AS tmEsProdBase
,IdProv,COUNT(Item) AS SCANT,SUM(Entradas) AS SENT,SUM(Salidas) AS SSAL
,SUM(VrCostoEnt) AS SCOSENT,SUM(VrCostoSal) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(Entradas*ImpGlobal) AS SGLOENT,SUM(Salidas*ImpGlobal) AS SGLOSAL
,SUM(Sobretasa*Entradas) AS SSOBENT,SUM(Sobretasa*Salidas) AS SSOBSAL
,SUM(TasaNac*Entradas) AS SNACENT,SUM(TasaNac*Salidas) AS SNACSAL
,SUM(TasaDep*Entradas) AS SDEPENT,SUM(TasaDep*Salidas) AS SDEPSAL
,SUM(TasaMun*Entradas) AS SMUNENT,SUM(TasaMun*Salidas) AS SMUNSAL
,SUM(Soldicom*Entradas) AS SSOLENT,SUM(Soldicom*Salidas) AS SSOLSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
,SUM(OtroImpto*Entradas) AS SOTRENT,SUM(OtroImpto*Salidas) AS SOTRSAL
--Para los ajustes con cantidad en cero (0)
,SUM(VrUnitario) AS SCOSAJU,SUM(OtroImpto) AS SDVEAJU
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,Combo,EsProdBase,IdProv
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryOComb @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,OCargue,IdCia,Fecha,FechaDesp,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto
,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocal,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans
,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob
,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_OComb
 WHERE TipDoc=@pmTipDoc AND OCargue=@pmOCargue 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 paQryOCombLta  @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmOCargueIni INT=Null,@pmOCargueFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
,@pmIdLocal VARCHAR(8)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT OCargue,IdCia,Fecha,FechaDesp,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,O.IdLocal AS CdCiudad,Localidad,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,CD.RazonSocial AS Conductor,NitEmpTrans,EmpTrans,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo
,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev
,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,ZonaFrontera,O.IdUsuario AS IdUsuari,Usuario,TimeSys,IdCiaCrea,FecUpdate,TipDoc
 FROM Trn_OComb AS O  INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON O.IdVend=V.IdTercero
 INNER JOIN Terceros AS CD ON O.IdConductor=CD.IdTercero
 INNER JOIN  Conceptos AS CN ON O.IdConcepto=CN.IdConcepto
 INNER JOIN Localidades AS L ON O.IdLocal=L.IdLocal
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND OCargue BETWEEN ISNULL(@pmOCargueIni,0) AND ISNULL(@pmOCargueFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,OCargue
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryOCombRel @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,@pmIdLocal VARCHAR(8)=Null
,@pmIdConductor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,OCargue,O.IdCia AS CdCia,Compania,Fecha,FechaDesp,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,DiasEntraga,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,Referncia,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos
,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo
,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,UA.Usuario AS UsuarioAprob,OrigenAdd,ZonaFrontera,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario
,TimeSys,O.FecUpdate AS Fech_Update,IdCiaCrea
--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
--vehiculo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarcaVeh,MV.Marca AS MarcaVeh,Modelo,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,CarrCapac,V.UndCapc AS UnidCapacidad,Comptmtos,CapComp
 FROM  Trn_OComb 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 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
 LEFT JOIN  Localidades AS LE ON O.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN adm_Usuarios AS UA ON O.CdUsuAprob=UA.IdUsuario
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
  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.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,OCargue
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryOComb_Cr @pmTipDoc VARCHAR(3),@pmOCargueIni INT,@pmOCargueFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,OCargue,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaDesp,FechaVence
,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS IdAgenc,Agencia,CodAgencia
,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,DiasEntraga,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,Referncia,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos
,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo
,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,UA.Usuario AS UsuarioAprob,OrigenAdd,ZonaFrontera,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,Leyenda
,O.TimeSys AS Fec_Add,O.FecUpdate AS Fech_Update,IdCiaCrea
--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
--vehiculo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarcaVeh,MV.Marca AS MarcaVeh,Modelo,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,CarrCapac,V.UndCapc AS UnidCapacidad,Comptmtos,CapComp
--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,K.Descripcion AS KarDescripcion,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,M.Marca AS MarcaProd,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_OComb AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.OCargue=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 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 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
 LEFT JOIN adm_Usuarios AS UA ON O.CdUsuAprob=UA.IdUsuario
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
  WHERE O.TipDoc=@pmTipDoc AND OCargue BETWEEN @pmOCargueIni AND @pmOCargueFin AND O.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY OCargue,Item
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,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 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,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,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 paQryOpedidoPen @pmFactura INT,@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT Pedido,P.IdCia AS CodCia,Compania,Fecha,DiasEntraga,FechaVence,P.IdConcepto AS CodConcepto,Concepto,IdCliente,T.RazonSocial AS Cliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto
,VrFletes,VrOtros,VrOtrDcto,VrNeto,Cantidad,IdVend,V.RazonSocial AS Vendedor,DirEnvio,IdLocEnv,Localidad,NitContac,NomContac,TelContac,emlContac,CargoContac
,P.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,CdRuta,ListaPrec,TipFac,Factura,IdCiaFac,FechaFact
,TipRem,Remision,IdCiaRem,FechaRem,NumAutoriza,Modalidad,Vigencia,OrigenAdd,ZonaFrontera,Anulado,FecDev,P.Observacion AS Observ,P.IdEstado AS CodEstado,Estado,P.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Opedido AS P  INNER JOIN Terceros AS T ON  P.IdCliente=T.IdTercero
 INNER JOIN  Conceptos AS C ON P.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario 
 INNER JOIN Terceros AS V ON P.IdVend=V.IdTercero
 INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia
 INNER JOIN Formaspago AS F ON P.IdForma=F.IdForma
 INNER JOIN Localidades AS L ON P.IdLocEnv=L.IdLocal
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  Anulado=0
 AND (Modalidad='PERMANENTE' or Factura<=@pmFactura)
 AND P.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')  AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY P.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,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,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 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
,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 ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryPagosCns @pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
,@pmTipDoc VARCHAR(3)=Null,@pmIdForma VARCHAR(4)=Null,@pmNitCliente VARCHAR(16)=Null,@pmCdAgencia VARCHAR(16)=Null
,@pmDocIni INT=Null,@pmDocFin INT=Null
 AS 
 SELECT TipDoc,Documento,IdCia,Item,P.IdForma AS CdForma,FormaPago,Detalle,VrPagado,VrCambio,P.IdBanco AS CdBanco,Banco,NumForma,Fecha,FecForma,CtaForma
,Beneficiario,Referncia1,Referncia2,NumAutoriza,NitCliente,RazonSocial,CdAgencia,CdLocal,Localidad,CdCta
,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef,ItemDoc,EsCaja,P.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Pagos AS P INNER JOIN Formaspago AS F ON P.IdForma=F.IdForma
 INNER JOIN  Bancos AS B ON P.IdBanco=B.IdBanco  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Localidades AS L ON P.CdLocal=L.IdLocal
 LEFT JOIN Terceros AS T ON P.NitCliente=T.IdTercero
 WHERE TipDoc IN ('REC','FCO','FO1','FO2','FO3','FO4','FO5','PLA','RCO','RC1','RC2' )
 AND ((TipRef='CNS' AND DocRef<=0) OR (TipRef='EGO' AND DocRef<=0) OR (TipRef<>'CNS' AND TipRef<>'EGO')  )
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND P.IdForma LIKE ISNULL(@pmIdForma,'%')
 AND NitCliente LIKE ISNULL(@pmNitCliente,'%') AND CdAgencia LIKE ISNULL(@pmCdAgencia,'%')
 AND Documento BETWEEN ISNULL(@pmDocIni,0) AND ISNULL(@pmDocFin,2147483647)
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY IdCia,TipDoc,Documento
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryProdImpFron @pmIdProducto VARCHAR(16),@pmIdClase VARCHAR(4),@pmItem INT
 AS 
 SELECT IdProducto,IdClase,Item,IdLocal,Tarifa,SimbTfa,CdTarifa,RngFecha,FecInicial,FecFinal,LtaPre,Inactivo,FecUpdate
 FROM ProdImpFron
 WHERE IdProducto=@pmIdProducto AND IdClase=@pmIdClase 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 paQryProdImpFronLta @pmIdProducto VARCHAR(16)=Null,@pmIdClase VARCHAR(4)=Null
 AS 
 SELECT PIM.IdProducto AS CdProducto,DescripProd,PIM.IdClase AS CdClase,NomClase,Item,PIM.IdLocal AS CdLocal,Localidad,Tarifa,SimbTfa,CdTarifa
,RngFecha,FecInicial,FecFinal,LtaPre,PIM.Inactivo AS Inactvo,FecUpdate
 FROM ProdImpFron AS PIM  INNER JOIN ProdMcias AS P ON PIM.IdProducto=P.IdProducto
 INNER JOIN ClaseTar AS CT ON PIM.IdClase=CT.IdClase
 INNER JOIN Localidades AS L ON PIM.IdLocal=L.IdLocal
 WHERE PIM.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND PIM.IdClase LIKE ISNULL(@pmIdClase,'%')
 ORDER BY  DescripProd,PIM.IdClase,PIM.IdLocal,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryProdImpFronUlt @pmIdProducto VARCHAR(16),@pmIdClase VARCHAR(4)
,@pmIdLocal VARCHAR(8)
 AS 
 SELECT IdProducto,IdClase,Item,IdLocal,Tarifa,SimbTfa,CdTarifa,RngFecha,FecInicial,FecFinal,LtaPre
 FROM ProdImpFron
 WHERE IdProducto=@pmIdProducto AND IdClase=@pmIdClase  AND IdLocal=@pmIdLocal
 AND Inactivo=0
 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 paQryTiposDocAut
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('FCR','DFO','ODB','OD2') --todas las fac.credito con este codigo
 ORDER BY IdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTiposDocFor
--tipos de documento de formas de pago
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('REC','FCO','FO1','FO2','FO3','FO4','FO5','PLA','RCO' ,'RC1','RC2')
 ORDER BY IdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTiposDocPag
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('REC','FCO','FO1','FO2','FO3','FO4','FO5','PLA','RCO'
,'PAS','EGO','CNS','DNP','DVP','DVC','ODB','DEC','DRC','DFO','DO1','DO2','DO3','DO4','DO5','OD1','RC1','RC2','OD2')
 ORDER BY IdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_KdexSub @pmtmNumero VARCHAR(5)
 AS 
 SELECT IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv,COUNT(tmItem) AS SCANT,SUM(tmEntradas) AS SENT,SUM(tmSalidas) AS SSAL
,SUM(tmEntradas*tmVrUnitario) AS SCOSENT,SUM(tmSalidas*tmVrUnitario) AS SCOSSAL
,SUM(tmVrPrecio*tmEntradas) AS SVALENT,SUM(tmVrPrecio*tmSalidas) AS SVALSAL
,SUM(tmVrIva) AS SIVA,SUM(tmVrDcto) AS SDCT,SUM(tmVrRete) AS SRET,SUM(tmVrIca) AS SICA
,SUM(tmVrBruto*tmEntradas) AS SBRUENT,SUM(tmVrBruto*tmSalidas) AS SBRUSAL
,SUM(tmEntradas*tmImpGlobal) AS SGLOENT,SUM(tmSalidas*tmImpGlobal) AS SGLOSAL
,SUM(tmSobretasa*tmEntradas) AS SSOBENT,SUM(tmSobretasa*tmSalidas) AS SSOBSAL
,SUM(tmTasaNac*tmEntradas) AS SNACENT,SUM(tmTasaNac*tmSalidas) AS SNACSAL
,SUM(tmTasaDep*tmEntradas) AS SDEPENT,SUM(tmTasaDep*tmSalidas) AS SDEPSAL
,SUM(tmTasaMun*tmEntradas) AS SMUNENT,SUM(tmTasaMun*tmSalidas) AS SMUNSAL
,SUM(tmSoldicom*tmEntradas) AS SSOLENT,SUM(tmSoldicom*tmSalidas) AS SSOLSAL
,SUM(tmOtroImpto*tmEntradas) AS SOTRENT,SUM(tmOtroImpto*tmSalidas) AS SOTRSAL
--Para los ajustes con cantidad en cero (0)
,SUM(tmVrUnitario) AS SCOSAJU,SUM(tmOtroImpto) AS SDVEAJU
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto
 WHERE tmNumero=@pmtmNumero
 GROUP BY IdSubgrupo,tmIdProducto,DescripProd,TipoRef,Combo,tmEsProdBase,IdProv
 ORDER BY IdSubgrupo,tmIdProducto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpAgencias @pmIdAgencia VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmIdClie VARCHAR(16),@pmAgencia VARCHAR(150),@pmDirAgncia VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelAgncia VARCHAR(20),@pmFaxAgncia VARCHAR(20),@pmNitCont VARCHAR(16),@pmNomCont VARCHAR(150)
,@pmemlCont VARCHAR(100),@pmCargoCont VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmCdPlazoComb VARCHAR(4),@pmIdForma VARCHAR(4),@pmCdBandera VARCHAR(4),@pmIdVend VARCHAR(16),@pmCdCms VARCHAR(4),@pmCdDct VARCHAR(4),@pmIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmFactFletes BIT,@pmFactSold BIT
,@pmVrCupoCre MONEY,@pmVrSaldoAct MONEY,@pmFecUpCupo SMALLDATETIME,@pmNContrato INT,@pmCiaCont CHAR(2),@pmFecIngreso SMALLDATETIME,@pmReferencia VARCHAR(50),@pmComentarios VARCHAR(250),@pmCupoGalones DECIMAL(14,4),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoZona VARCHAR(10),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Agencias SET CodAgencia=@pmCodAgencia,IdClie=@pmIdClie,Agencia=@pmAgencia,DirAgncia=@pmDirAgncia,IdLocal=@pmIdLocal,TelAgncia=@pmTelAgncia,FaxAgncia=@pmFaxAgncia,NitCont=@pmNitCont,NomCont=@pmNomCont,emlCont=@pmemlCont,CargoCont=@pmCargoCont,IdSzona=@pmIdSzona
,IdPlazo=@pmIdPlazo,CdPlazoComb=@pmCdPlazoComb,IdForma=@pmIdForma,CdBandera=@pmCdBandera,IdVend=@pmIdVend,CdCms=@pmCdCms,CdDct=@pmCdDct,IntMora=@pmIntMora,DiasGracia=@pmDiasGracia,FactFletes=@pmFactFletes,FactSold=@pmFactSold,VrCupoCre=@pmVrCupoCre,VrSaldoAct=@pmVrSaldoAct
,FecUpCupo=@pmFecUpCupo,FecIngreso=@pmFecIngreso,Referencia=@pmReferencia,Comentarios=@pmComentarios,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,NContrato=@pmNContrato,CiaCont=@pmCiaCont,CupoGalones=@pmCupoGalones,FechaUpdate=@pmFechaUpdate,TipoZona=@pmTipoZona
 WHERE IdAgencia=@pmIdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpCompanias @pmIdCia CHAR(2),@pmCompania VARCHAR(50),@pmDirInterface VARCHAR(255),@pmFechaActual SMALLDATETIME,@pmPeriodoActual SMALLDATETIME,@pmIdCCosto VARCHAR(16)
,@pmIdSubCos VARCHAR(16),@pmIdTercero VARCHAR(16),@pmCodSucsal VARCHAR(10),@pmDireccSuc VARCHAR(250),@pmTelefSuc VARCHAR(20),@pmFaxSuc VARCHAR(20),@pmIdLocal VARCHAR(8)
,@pmTipoEDS VARCHAR(10),@pmFechaUpdate SMALLDATETIME,@pmInactivo BIT
 AS 
 UPDATE Companias SET Compania=@pmCompania,DirInterface=@pmDirInterface,FechaActual=@pmFechaActual,PeriodoActual=@pmPeriodoActual,IdCCosto=@pmIdCCosto
,IdSubCos=@pmIdSubCos,IdTercero=@pmIdTercero,CodSucsal=@pmCodSucsal,DireccSuc=@pmDireccSuc,TelefSuc=@pmTelefSuc,FaxSuc=@pmFaxSuc, IdLocal=@pmIdLocal,TipoEDS=@pmTipoEDS
,FechaUpdate=@pmFechaUpdate,Inactivo=@pmInactivo
 WHERE 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 paUpDevFcr @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4)
,@pmCantPuntos DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmIdLocEnv VARCHAR(8),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmCotizacion INT
,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmModalidad VARCHAR(10),@pmPlacaVehic VARCHAR(10),@pmKmtVehic INT,@pmModdDev VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_DevFcr SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,Factura=@pmFactura,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA
,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrCostos=@pmVrCostos,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrFaltantes=@pmVrFaltantes,VrAnticipos=@pmVrAnticipos,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,CantPuntos=@pmCantPuntos,BaseImp=@pmBaseImp
,BaseRet=@pmBaseRet,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,IdLocEnv=@pmIdLocEnv,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,Cotizacion=@pmCotizacion,IdCiaCot=@pmIdCiaCot
,FecPedido=@pmFecPedido,Modalidad=@pmModalidad,ModdDev=@pmModdDev,PlacaVehic=@pmPlacaVehic,KmtVehic=@pmKmtVehic,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,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 paUpFacturas @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrFletes MONEY
,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrCostos MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrFaltantes MONEY,@pmVrAnticipos MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmCantidad DECIMAL(14,4),@pmCantPuntos DECIMAL(14,4),@pmPuntosAcum DECIMAL(14,4),@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),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8)
,@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),@pmpVehiculo VARCHAR(10)
,@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmTipCot VARCHAR(3),@pmCotizacion INT,@pmIdCiaCot CHAR(2),@pmFecPedido SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmModalidad VARCHAR(10),@pmKmtVehic INT
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Facturas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,FechaVence=@pmFechaVence,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrCostos=@pmVrCostos
,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrFaltantes=@pmVrFaltantes,VrAnticipos=@pmVrAnticipos,VrNeto=@pmVrNeto,VrAplicado=@pmVrAplicado,Cantidad=@pmCantidad,CantPuntos=@pmCantPuntos,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,CodTarIva=@pmCodTarIva
,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirEnvio=@pmDirEnvio,IdLocEnv=@pmIdLocEnv,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,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,CdRuta=@pmCdRuta,TipPed=@pmTipPed,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,TipRem=@pmTipRem,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,TipCot=@pmTipCot,Cotizacion=@pmCotizacion,IdCiaCot=@pmIdCiaCot
,FecPedido=@pmFecPedido,AutzaMora=@pmAutzaMora,AutzaCupo=@pmAutzaCupo,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,PuntosAcum=@pmPuntosAcum,KmtVehic=@pmKmtVehic,ZonaFrontera=@pmZonaFrontera,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura 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 paUpGuia @pmTipDoc VARCHAR(3),@pmGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmCantidad DECIMAL(14,4)
,@pmDirEnvio VARCHAR(250),@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmHorasVig DECIMAL(14,4),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmCdRuta VARCHAR(4),@pmCdCiuOrigen VARCHAR(8)
,@pmReferncia VARCHAR(50),@pmTipOcc VARCHAR(3),@pmOCargue INT,@pmIdCiaOcc CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Guia SET Fecha=@pmFecha,FechaDesp=@pmFechaDesp,FechaVence=@pmFechaVence,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,Cantidad=@pmCantidad,DirEnvio=@pmDirEnvio,IdLocal=@pmIdLocal,LugarEnv=@pmLugarEnv,HorasVig=@pmHorasVig,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor
,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,CdRuta=@pmCdRuta,Referncia=@pmReferncia,TipOcc=@pmTipOcc,OCargue=@pmOCargue,IdCiaOcc=@pmIdCiaOcc,TipFac=@pmTipFac,Factura=@pmFactura,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,Anulado=@pmAnulado,FecDev=@pmFecDev
,Observacion=@pmObservacion,IdEstado=@pmIdEstado,CdCiuOrigen=@pmCdCiuOrigen,ZonaFrontera=@pmZonaFrontera,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Guia=@pmGuia 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 paUpOComb @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia 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),@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmDiasEntraga INT,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150)
,@pmCdRuta VARCHAR(4),@pmReferncia VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(150),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipGui VARCHAR(3),@pmGuia INT,@pmIdCiaGui CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2)
,@pmFechaFact SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmAprobacion INT,@pmFechaAprob SMALLDATETIME,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_OComb SET Fecha=@pmFecha,FechaDesp=@pmFechaDesp,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,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,IdLocal=@pmIdLocal,LugarEnv=@pmLugarEnv,DiasEntraga=@pmDiasEntraga,IdVehiculo=@pmIdVehiculo
,IdConductor=@pmIdConductor,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,CdRuta=@pmCdRuta,Referncia=@pmReferncia,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,TipGui=@pmTipGui,Guia=@pmGuia,IdCiaGui=@pmIdCiaGui,TipFac=@pmTipFac,Factura=@pmFactura
,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,TipPed=@pmTipPed,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,AutzaMora=@pmAutzaMora,AutzaCupo=@pmAutzaCupo,Aprobacion=@pmAprobacion,FechaAprob=@pmFechaAprob,DetalleAprob=@pmDetalleAprob,CdUsuAprob=@pmCdUsuAprob,Anulado=@pmAnulado
,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND OCargue=@pmOCargue 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,@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,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,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 ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpProdImpFron @pmIdProducto VARCHAR(16),@pmIdClase VARCHAR(4),@pmItem INT,@pmIdLocal VARCHAR(8),@pmTarifa DECIMAL(14,4),@pmSimbTfa CHAR(1),@pmCdTarifa VARCHAR(4)
,@pmRngFecha BIT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmLtaPre CHAR(1),@pmInactivo BIT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE ProdImpFron SET IdLocal=@pmIdLocal,Tarifa=@pmTarifa,SimbTfa=@pmSimbTfa,CdTarifa=@pmCdTarifa,RngFecha=@pmRngFecha,FecInicial=@pmFecInicial,FecFinal=@pmFecFinal
,LtaPre=@pmLtaPre,Inactivo=@pmInactivo,FecUpdate=@pmFecUpdate
 WHERE IdProducto=@pmIdProducto AND IdClase=@pmIdClase AND Item=@pmItem

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO