if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuInventarioKar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAcuInventarioKar]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuInvLotesKar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAcuInvLotesKar]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuInvTanquesKar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAcuInvTanquesKar]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuInvUbicaKar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAcuInvUbicaKar]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCotizacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsCotizacion]
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].[paInstm_AuxInv_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelBod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelBod]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelIns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelIns]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelInsBod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelInsBod]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelTanq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelTanq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelVen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenBod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelVenBod]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenBodCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelVenBodCia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelVenCia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenTanq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelVenTanq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv_SelVenTanqCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_AuxInv_SelVenTanqCia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Saldos_Kar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_Saldos_Kar]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCotizacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCotizacion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCotizacion_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCotizacion_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCotizacionFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCotizacionFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCotizacionLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCotizacionLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCotizacionRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCotizacionRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardex_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryKardex_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexMovTan]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryKardexMovTan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOpedido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedido_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOpedido_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOpedidoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOpedidoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposDocInv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTiposDocInv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraTrasladosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraTrasladosRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCotizacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpCotizacion]
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].[paUpProdSaldosKar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpProdSaldosKar]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTanqSaldosKar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTanqSaldosKar]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryKardex_Cr] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipDoc VARCHAR(3)=Null
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null
,@pmTipoRef VARCHAR(10)=Null,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null
,@pmnAnnoAnt INT=Null,@pmnMesAnt INT=Null
 AS 
 SELECT  K.IdProducto AS CdProducto,DescripProd,TipDoc,Documento,K.IdCia AS CdCia,Compania,Fecha,Item,K.IdBodega AS CdBodega,Bodega
,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal
,TarifaIva,VrIvaEnt,VrIvaSal,TarifaRet,VrReteEnt,VrReteSal,TarifaIca,VrIcaEnt,VrIcaSal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden
,K.IdConcepto AS CdConcepto,Concepto,K.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,CdAgencia,Agencia,CodAgencia,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,Comision
,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper,pVehiculo,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,CdSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,Factura,Remision,IdCiaRem,Cotizacion,IdCiaCot
,TipDocDev,NumDocDev,CdUbic,NumLote,FechLote,Comptmntos,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun
,Soldicom,ImpGlobal,OtroImpto,Rec_Costo,MgenCont,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo
,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,TimeSys,K.IdUsuario AS IdUsuari,Usuario,TipoDoc
--Datos del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono
--datos del producto
,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,Color,PM.UndMed AS CdUndMed,SU.Unidad AS ProdUnidad,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,IvaInc,IdTarIva,Tarifa,Simbolo
,Seriales,Lotes,Combo,Tanques,DescripLong,DescripAbrv,Precio1,Precio2,Precio3,Precio4,Precio5,IdProv,PV.RazonSocial AS NomProveedor
,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5,PM.Inactivo AS ProdInactivo
--Información del saldo anterior
,SaldoAntProd,CostoAntProd,SaldoAntBod,CostoAntBod
 FROM Trn_Kardex AS K INNER JOIN Companias AS CN ON K.IdCia=CN.IdCia 
 INNER JOIN Sys_TiposDoc AS TD ON K.TipDoc=TD.IdDoc
 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   INNER JOIN Terceros AS PV ON PM.IdProv=PV.IdTercero
 INNER JOIN  Conceptos AS C ON K.IdConcepto=C.IdConcepto 
 INNER JOIN Terceros AS T ON K.IdTercero=T.IdTercero
 INNER JOIN adm_Usuarios AS U ON K.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN Terceros AS VN ON K.IdVend=VN.IdTercero
 LEFT JOIN Agencias  AS A ON K.CdAgencia=A.IdAgencia
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 LEFT JOIN  Localidades AS LK ON K.CdLocal=LK.IdLocal 
 LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero
 LEFT JOIN Tablapor AS TI ON PM.IdTarIva=TI.IdTarifa
--Saldos anteriores
 LEFT JOIN (SELECT IdProducto,SUM(SaldoAnt+Entradas-Salidas) AS SaldoAntProd,SUM(CostoAnt+CostoEnt-CostoSal) AS CostoAntProd 
 FROM  AcuInventario
 WHERE  nAnno=@pmnAnnoAnt AND nMes=@pmnMesAnt
 AND  IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND IdBodega LIKE ISNULL(@pmIdBodega,'%')
 GROUP BY IdProducto) AS SP ON K.IdProducto=SP.IdProducto
--Saldos por bodega
 LEFT JOIN (SELECT IdProducto,IdBodega,SUM(SaldoAnt+Entradas-Salidas) AS SaldoAntBod,SUM(CostoAnt+CostoEnt-CostoSal) AS CostoAntBod
 FROM  AcuInventario
 WHERE  nAnno=@pmnAnnoAnt AND nMes=@pmnMesAnt
 AND  IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND IdBodega LIKE ISNULL(@pmIdBodega,'%')
 GROUP BY IdProducto,IdBodega) AS SB ON K.IdProducto=SB.IdProducto AND K.IdBodega=SB.IdBodega
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.TipDoc LIKE ISNULL(@pmTipDoc,'%') 
 AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%') AND K.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsAcuInventarioKar] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null
 AS 
 INSERT INTO AcuInventario (nAnno,nMes,IdCia,IdProducto,IdBodega,Entradas,Salidas,CostoEnt,CostoSal,SaldoAnt,CostoAnt)
 SELECT @pmnAnno,@pmnMes,IdCia,K.IdProducto,K.IdBodega,SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal),0,0
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE  YEAR(Fecha)=@pmnAnno AND MONTH(Fecha)=@pmnMes AND TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')
 AND TipoRef<>'SERVICIO' AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 GROUP BY IdCia,K.IdProducto,K.IdBodega

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryCotizacion_Cr] @pmTipDoc VARCHAR(3),@pmCotizacionIni INT,@pmCotizacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,O.Cotizacion AS NumCotizacion,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NombreCliente
,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrOtrDcto,VrNeto,Cantidad
,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirContacto,O.IdLocal AS CdCiudEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,DiasVigencia,O.NitContac AS CotNitContac,O.NomContac AS CotNomContac,O.TelContac AS CotTelContac,O.emlContac AS CotEmailContac,CargoContac,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago
,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact,Modalidad,NumAprob,FecAprob,CdUsuAprob,NumPedido,CdCiaPed,OrigenAdd,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.TimeSys AS Fec_Add,O.FecUpdate AS Fec_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,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_Cotizacion AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Cotizacion=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.IdLocal=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
 WHERE O.TipDoc=@pmTipDoc AND O.Cotizacion BETWEEN @pmCotizacionIni AND @pmCotizacionFin AND O.IdCia=@pmIdCia
 ORDER BY O.Cotizacion,Item

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[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,NumCotizac,CdCiaCotizac,NumAutoriza,NumAutCupo,NumAutCheq,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera
,TipoTrans,TipoOrden,TipoModifica,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS Fech_Add,O.FecUpdate AS Fech_Update,IdCiaCrea,O.IdUsuario AS IdUsuari,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--Detalles
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,K.IdTercero AS KarNitTercero,CdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,K.Remision AS KarRemision,K.IdCiaRem AS CdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev
,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,K.ListaPrec AS KarLtaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5
 FROM Trn_Opedido AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Pedido=K.Documento AND O.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN  Localidades AS LE ON O.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON O.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 WHERE O.TipDoc=@pmTipDoc AND Pedido BETWEEN @pmPedidoIni AND @pmPedidoFin AND O.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Pedido,Item

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[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,NumCotizac,CdCiaCotizac,NumAutoriza,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,CdUsuAprob,DetalleAprob,OrigenAdd,Anulado,FecDev,O.Observacion AS Observ
,O.IdEstado AS CdEstado,Estado,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,TimeSys,O.FecUpdate AS Fech_Update,IdCiaCrea,O.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_Opedido AS O  INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN  Localidades AS LE ON O.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON O.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND O.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND O.IdVend LIKE ISNULL(@pmIdVend,'%') AND O.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,Pedido

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryCotizacionRel] @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
,@pmModalidad VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,Cotizacion,O.IdCia AS CdCia,Compania,Fecha,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NombreCliente
,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrOtrDcto,VrNeto,Cantidad
,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirContacto,O.IdLocal AS CdCiudEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,DiasVigencia,O.NitContac AS CotNitContac,O.NomContac AS CotNomContac,O.TelContac AS CotTelContac,O.emlContac AS CotEmailContac,CargoContac,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago
,TipFac,Factura,IdCiaFac,FechaFact,NumPedido,CdCiaPed,Modalidad,NumAprob,FecAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,TimeSys,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_Cotizacion 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.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 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 Modalidad LIKE ISNULL(@pmModalidad,'%') AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,O.Cotizacion

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE  [dbo].[paQryTraTrasladosRel] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdVehiculo VARCHAR(10)=Null
,@pmIdConductor VARCHAR(16)=Null
AS 
 SELECT T.TipDoc AS CdTipDoc,TipoDoc,T.Traslado AS NumTraslado,T.IdCia AS CdCia,Compania,T.Fecha AS FecTraslado,T.FecDespacho AS FecDespTrasl,TipoTraslado,T.IdBodega AS CdBodega,BG.Bodega AS BodegaOrig,IdBodDtno,BD.Bodega AS BodegaDtno
,T.IdVehiculo AS PlacaVeh,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,T.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,Manifiesto,CdCiaMuc,EstadoRec,FechaRec,CedRecibido,NomRecibido
,T.Observacion AS Observ,T.OrigenAdd,T.TimeSys,T.IdCiaCrea,T.IdUsuario AS CdUsuario,Usuario
,DR.Item AS DetItem,TipRem,Remesa,IdCiaRem,ItemRem,EdoRecibido,Comentarios,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol
,Cases,Cajas,Palets,NitRemite,Remitente,DirOrigen,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,NitDestntario,Destinatario,DirDestino,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,TarifClie,D.VrDeclarado AS VlrDeclarado,D.VrSeguro AS VlrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CdNovedad,Novedad
,R.FecDespacho AS FecDespRemesa,CL.TipoId AS CliTipoId,R.IdCliente AS NitCliente,CL.Dv AS CliDv,CL.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,TipoAfiVehic,Modalidad,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm
,NumManif,IdCiaManif,EstCumplido,EstFactura,SerieGuia,NumGuia,R.CdBodega AS CodBodega,BR.Bodega AS BodActual
 FROM Trn_TraTraslados AS T INNER JOIN Companias AS CN ON T.IdCia=CN.IdCia INNER JOIN Terceros AS CDT ON T.IdConductor=CDT.IdTercero 
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON T.TipDoc=TD.IdDoc
 INNER JOIN AlmBodegas AS BG ON T.IdBodega=BG.IdBodega INNER JOIN AlmBodegas AS BD ON T.IdBodDtno=BD.IdBodega
 INNER JOIN Trn_TraTrasladoRem AS DR ON T.TipDoc=DR.TipDoc AND T.Traslado=DR.Traslado AND T.IdCia=DR.IdCia
 INNER JOIN Trn_TraRemesa AS R ON DR.TipRem=R.TipDoc AND DR.Remesa=R.NumOrden AND DR.IdCiaRem=R.IdCia
 INNER JOIN Terceros AS CL ON R.IdCliente=CL.IdTercero  
 LEFT JOIN Trn_TraRemMcias AS D ON DR.TipRem=D.TipDoc AND DR.Remesa=D.NumOrden AND DR.IdCiaRem=D.IdCia AND DR.ItemRem=D.Item
 LEFT JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal LEFT JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal LEFT JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN NovedadTra AS NV ON DR.CdNovedad=NV.IdNovedad
 LEFT JOIN AlmBodegas AS BR ON R.CdBodega=BR.IdBodega 
 LEFT JOIN Vehiculos AS V ON T.IdVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 WHERE  T.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND T.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND T.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND T.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND R.Anulado=0





GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryCotizacionFac] @pmFactura INT,@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT Cotizacion,IdCia,Fecha,FechaVence,C.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros
,VrOtrDcto,VrNeto,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirContacto,C.IdLocal AS CdCiudad,DiasVigencia,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma
,DetallePago,MulPlazos,IdPlazo,CdMney,Modalidad,NumPedido,CdCiaPed,C.Observacion AS Observ,TimeSys,IdCiaCrea
 FROM Trn_Cotizacion AS C INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON C.IdConcepto=CN.IdConcepto
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Anulado=0 AND Factura<=@pmFactura
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY IdCia,Cotizacion


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsCotizacion] @pmTipDoc VARCHAR(3),@pmCotizacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY
,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirContacto VARCHAR(250),@pmIdLocal VARCHAR(8),@pmDiasVigencia INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150)
,@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(250),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME
,@pmModalidad VARCHAR(10),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmNumAprob INT,@pmFecAprob SMALLDATETIME,@pmCdUsuAprob VARCHAR(11),@pmIdEstado VARCHAR(4),@pmNumPedido INT,@pmCdCiaPed CHAR(2),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Cotizacion (TipDoc,Cotizacion,IdCia,Fecha,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrOtrDcto,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirContacto,IdLocal,DiasVigencia,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney
,TipFac,Factura,IdCiaFac,FechaFact,Modalidad,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,NumAprob,FecAprob,CdUsuAprob,NumPedido,CdCiaPed,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmCotizacion,@pmIdCia,@pmFecha,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrOtrDcto,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmDirContacto,@pmIdLocal,@pmDiasVigencia,@pmNitContac
,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmModalidad,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmNumAprob,@pmFecAprob,@pmCdUsuAprob,@pmNumPedido,@pmCdCiaPed,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpCotizacion] @pmTipDoc VARCHAR(3),@pmCotizacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16)
,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrOtrDcto MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4)
,@pmDirContacto VARCHAR(250),@pmIdLocal VARCHAR(8),@pmDiasVigencia INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4)
,@pmDetallePago VARCHAR(250),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmModalidad VARCHAR(10)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmNumAprob INT,@pmFecAprob SMALLDATETIME,@pmCdUsuAprob VARCHAR(11),@pmIdEstado VARCHAR(4),@pmNumPedido INT,@pmCdCiaPed CHAR(2),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Cotizacion SET Fecha=@pmFecha,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrFletes=@pmVrFletes
,VrOtros=@pmVrOtros,VrOtrDcto=@pmVrOtrDcto,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirContacto=@pmDirContacto,IdLocal=@pmIdLocal,DiasVigencia=@pmDiasVigencia,NitContac=@pmNitContac
,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargoContac=@pmCargoContac,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,CdMney=@pmCdMney,TipFac=@pmTipFac,Factura=@pmFactura
,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,Modalidad=@pmModalidad,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,NumAprob=@pmNumAprob
,FecAprob=@pmFecAprob,CdUsuAprob=@pmCdUsuAprob,NumPedido=@pmNumPedido,CdCiaPed=@pmCdCiaPed,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Cotizacion=@pmCotizacion AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryCotizacion] @pmTipDoc VARCHAR(3),@pmCotizacion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Cotizacion,IdCia,Fecha,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrOtrDcto,VrNeto,Cantidad,IdVend,TarifaCom
,CodTarCom,DirContacto,IdLocal,DiasVigencia,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,TipFac,Factura,IdCiaFac,FechaFact
,Modalidad,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,NumAprob,FecAprob,CdUsuAprob,NumPedido,CdCiaPed,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Cotizacion
 WHERE TipDoc=@pmTipDoc AND Cotizacion=@pmCotizacion AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryCotizacionLta] @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmCotizacionIni INT=Null,@pmCotizacionFin 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 Cotizacion,IdCia,Fecha,FechaVence,C.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros
,VrOtrDcto,VrNeto,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirContacto,C.IdLocal AS CdCiudad,DiasVigencia,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma
,DetallePago,MulPlazos,IdPlazo,CdMney,TipFac,Factura,IdCiaFac,FechaFact,Modalidad,NumAprob,FecAprob,CdUsuAprob,NumPedido,CdCiaPed,OrigenAdd,Anulado,FecDev,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado
,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Cotizacion AS C INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON C.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Cotizacion BETWEEN ISNULL(@pmCotizacionIni,0) AND ISNULL(@pmCotizacionFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Cotizacion

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsAcuInvLotesKar] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null
 AS 
 INSERT INTO AcuInvLotes (nAnno,nMes,IdCia,IdProducto,IdBodega,NumLote,Entradas,Salidas,CostoEnt,CostoSal,SaldoAnt,CostoAnt)
 SELECT @pmnAnno,@pmnMes,IdCia,L.IdProducto,L.IdBodega,NumLote,SUM(Entradas),SUM(Salidas),0,0,0,0
 FROM Trn_LotMov AS L INNER JOIN ProdMcias AS P ON L.IdProducto=P.IdProducto
 WHERE  YEAR(Fecha)=@pmnAnno AND MONTH(Fecha)=@pmnMes AND TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')
 AND TipoRef<>'SERVICIO' AND Lotes=1  AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND L.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 GROUP BY IdCia,L.IdProducto,L.IdBodega,NumLote

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpTanqSaldosKar] @pmDelete BIT,@pmIdProducto VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 IF @pmDelete<>0  
  BEGIN
   DECLARE @TrnNombre VARCHAR(20)
   SELECT @TrnNombre = 'TrnProdSaldos'
   BEGIN TRANSACTION @TrnNombre
    DELETE TanqSaldos
    FROM TanqSaldos AS TS INNER JOIN Tanques AS T ON TS.IdTanque=T.IdTanque
    WHERE IdCia LIKE ISNULL(@pmIdCia,'%%')  AND IdProducto LIKE ISNULL(@pmIdProducto,'%')
    INSERT INTO  TanqSaldos  (IdTanque,IdCia,SaldoActual)
    SELECT ISNULL(CdTanque,'0'),IdCia,SUM(Entradas-Salidas)
    FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
    WHERE TipDoc NOT IN ('PED','REM','COT','ODC','GUI','OCC','ODS','VCC') AND TipoRef<>'SERVICIO' AND Tanques<>0
    AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%')
    GROUP BY CdTanque,IdCia
   COMMIT TRANSACTION  @TrnNombre
   END
 ELSE
  INSERT INTO  TanqSaldos  (IdTanque,IdCia,SaldoActual)
  SELECT ISNULL(CdTanque,'0'),IdCia,SUM(Entradas-Salidas)
  FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
  WHERE TipDoc NOT IN ('PED','REM','COT','ODC','GUI','OCC','ODS','VCC') AND TipoRef<>'SERVICIO' AND Tanques<>0
  AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%')
  GROUP BY CdTanque,IdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsAcuInvUbicaKar] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null
 AS 
 INSERT INTO AcuInvUbica (nAnno,nMes,IdCia,IdProducto,IdUbic,Entradas,Salidas,CostoEnt,CostoSal,SaldoAnt,CostoAnt)
 SELECT @pmnAnno,@pmnMes,IdCia,U.IdProducto,U.IdUbic,SUM(Entradas),SUM(Salidas),0,0,0,0
 FROM  Trn_KarUbic AS U INNER JOIN ProdMcias AS P ON U.IdProducto=P.IdProducto
 WHERE  YEAR(Fecha)=@pmnAnno AND MONTH(Fecha)=@pmnMes AND TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')
 AND TipoRef<>'SERVICIO' AND Tanques=0 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND U.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 GROUP BY IdCia,U.IdProducto,U.IdUbic

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsAcuInvTanquesKar] @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null
 AS 
 INSERT INTO AcuInvTanques (nAnno,nMes,IdCia,IdProducto,IdTanque,Entradas,Salidas,CostoEnt,CostoSal,SaldoAnt,CostoAnt)
 SELECT @pmnAnno,@pmnMes,IdCia,K.IdProducto,CASE WHEN CdTanque IS NULL THEN '0' WHEN LEN(CdTanque)<1 THEN '0' ELSE CdTanque END
,SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal),0,0
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE  YEAR(Fecha)=@pmnAnno AND MONTH(Fecha)=@pmnMes AND TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')
 AND TipoRef<>'SERVICIO' AND Tanques=1 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 GROUP BY IdCia,K.IdProducto,CdTanque

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTiposDocInv]
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc  NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','FIS','VCC') AND Inactivo=0
 ORDER BY IdDoc

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenTanqCia] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,IdCia,CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS CdBodga
,'0',CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS NumTanque
,SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*Salidas ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*Entradas ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND Tanques<>0 AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia,CdTanque

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenBodCia] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,IdCia,K.IdBodega,'0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*Salidas ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*Entradas ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia,K.IdBodega


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenTanq] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,'00',CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS CdBodga,'0'
,CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS NumTanque
,SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*Salidas ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*Entradas ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND Tanques<>0 AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,CdTanque

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVen] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,'00','0','0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*Salidas ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*Entradas ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenBod] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,'00',K.IdBodega,'0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*Salidas ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*Entradas ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,K.IdBodega

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelTanq] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmDetCia BIT
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 IF @pmDetCia <>0
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,IdCia,CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS CdBodga,'0'
 ,CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS NumTanque
 ,SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND Tanques<>0 AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia,CdTanque
 ELSE
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,'00',CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS CdBodga
  ,'0',CASE CdTanque WHEN Null THEN '0' WHEN '' THEN '0' ELSE CdTanque  END AS NumTanque
  ,SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND Tanques<>0 AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,CdTanque

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelVenCia] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal
 ,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev
 ,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev) 
 SELECT @pmtmEst,K.IdProducto,IdCia,'0','0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
--variables de ventas
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Salidas ELSE 0 END ) AS VtaCant
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Entradas ELSE 0 END ) AS VtaCantDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrPrecio*Salidas ELSE 0 END ) AS VtaTotal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrPrecio*Entradas ELSE 0 END ) AS VtaTotalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrCostoSal ELSE 0 END ) AS VtaCosto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrCostoEnt ELSE 0 END ) AS VtaCostoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIvaSal ELSE 0 END ) AS VtaIva
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIvaEnt ELSE 0 END ) AS VtaIvaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrDctoSal ELSE 0 END ) AS VtaDcto
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrDctoEnt ELSE 0 END ) AS VtaDctoDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrReteSal ELSE 0 END ) AS VtaRet
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrReteEnt ELSE 0 END ) AS VtaRetDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN VrIcaSal ELSE 0 END ) AS VtaIca
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN VrIcaEnt ELSE 0 END ) AS VtaIcaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Sobretasa*Salidas ELSE 0 END ) AS VtaTasa
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Sobretasa*Entradas ELSE 0 END ) AS VtaTasaDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN ImpGlobal*Salidas ELSE 0 END ) AS VtaGlobal
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN ImpGlobal*Entradas ELSE 0 END ) AS VtaGlobalDev
,SUM(CASE WHEN NoVentas IN (1,3,5) THEN Soldicom*Salidas ELSE 0 END ) AS VtaSol
,SUM(CASE WHEN NoVentas IN (2,4,6) THEN Soldicom*Entradas ELSE 0 END ) AS VtaSolDev
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelIns] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmDetCia BIT
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 IF @pmDetCia <>0
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,IdCia,'0','0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND TipoRef='INSUMO'
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia
 ELSE
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,'00','0','0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipoRef='INSUMO' 
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelBod] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmDetCia BIT
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 IF @pmDetCia <>0
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,IdCia,K.IdBodega,'0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia,K.IdBodega
 ELSE
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,'00',K.IdBodega,'0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,K.IdBodega

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_Sel] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmDetCia BIT
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null,@pmTipoRef VARCHAR(10)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 IF @pmDetCia <>0
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,IdCia,'0','0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia
 ELSE
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,'00','0','0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
  AND TipoRef<>'INSUMO'  AND  TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto
 


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_AuxInv_SelInsBod] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmDetCia BIT
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdBodega VARCHAR(4)=Null
,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null,@pmTanques BIT=Null
,@pmInactivo BIT=Null
 AS 
 IF @pmDetCia <>0
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,IdCia,K.IdBodega,'0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND TipoRef='INSUMO'
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,IdCia,K.IdBodega
 ELSE
  INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdCia,tmIdBodega,tmNumLote,tmCdTanque,tmEntradas,tmSalidas,tmCostoEnt,tmCostoSal)
  SELECT @pmtmEst,K.IdProducto,'00',K.IdBodega,'0','0',SUM(Entradas),SUM(Salidas),SUM(VrCostoEnt),SUM(VrCostoSal)
  FROM Trn_Kardex  AS K INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto  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
  WHERE TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipoRef='INSUMO' 
  AND  K.IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND K.IdBodega LIKE ISNULL(@pmIdBodega,'%')
  AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
  AND (Tanques=ISNULL(@pmTanques,0) or Tanques=ISNULL(@pmTanques,1))
  AND (PM.Inactivo=ISNULL(@pmInactivo,0) or PM.Inactivo=ISNULL(@pmInactivo,1))
 GROUP BY K.IdProducto,K.IdBodega

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_Saldos_Kar] @pmtmEst CHAR(2),@pmDetCia BIT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmTipoRef VARCHAR(10)=Null,@pmIdSubgrupo VARCHAR(8)=Null
 AS 
 IF @pmDetCia=1 
 BEGIN 
  INSERT INTO tm_Saldos (tmEst,tmIdCia,tmIdCuenta,tmIdTercero,tmCdSubgrupo,tmSanCue,tmDebitos,tmCreditos,tmCostoAnt,tmCostoEnt,tmCostoSal,tmSaldoAnt,tmEntradas,tmSalidas)
  SELECT @pmtmEst,IdCia,IdCuenta,'0',P.IdSubgrupo,0,0,0,0,SUM(VrCostoEnt),SUM(VrCostoSal),0,SUM(Entradas),SUM(Salidas)
  FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
  LEFT JOIN (SELECT IdSubgrupo,IdCuenta  FROM SubgruposCue WHERE IdClase='0001') AS SC ON P.IdSubgrupo=SC.IdSubgrupo
  WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')
  AND TipoRef<>'SERVICIO'  AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND P.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo,'%')
  GROUP BY IdCia,IdCuenta,P.IdSubgrupo 
 END
ELSE
 BEGIN 
  INSERT INTO tm_Saldos (tmEst,tmIdCia,tmIdCuenta,tmIdTercero,tmCdSubgrupo,tmSanCue,tmDebitos,tmCreditos,tmCostoAnt,tmCostoEnt,tmCostoSal,tmSaldoAnt,tmEntradas,tmSalidas)
  SELECT @pmtmEst,'00',IdCuenta,'0',P.IdSubgrupo,0,0,0,0,SUM(VrCostoEnt),SUM(VrCostoSal),0,SUM(Entradas),SUM(Salidas)
  FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
  LEFT JOIN (SELECT IdSubgrupo,IdCuenta  FROM SubgruposCue WHERE IdClase='0001') AS SC ON P.IdSubgrupo=SC.IdSubgrupo
  WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')
  AND TipoRef<>'SERVICIO'  AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND TipoRef LIKE ISNULL(@pmTipoRef,'%')
  AND P.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo,'%')
  GROUP BY  IdCuenta,P.IdSubgrupo 
 END
 


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpOpedido] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY
,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmLugarEnvio VARCHAR(50),@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20)
,@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmAsignarVeh BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmListaPrec CHAR(1),@pmRefPedido VARCHAR(50)
,@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmNumAutoriza INT,@pmNumAutCupo INT,@pmNumAutCheq INT,@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2)
,@pmFechaRem SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmTipoTrans INT,@pmTipoOrden VARCHAR(3),@pmTipoModifica VARCHAR(10),@pmNumCotizac INT,@pmCdCiaCotizac CHAR(2),@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,NumCotizac=@pmNumCotizac,CdCiaCotizac=@pmCdCiaCotizac,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,TipoModifica=@pmTipoModifica,IdEstado=@pmIdEstado
,ZonaFrontera=@pmZonaFrontera,TipoTrans=@pmTipoTrans,TipoOrden=@pmTipoOrden, FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsOpedido] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY
,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmLugarEnvio VARCHAR(50)
,@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150)
,@pmAsignarVeh BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmListaPrec CHAR(1),@pmRefPedido VARCHAR(50),@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmNumAutoriza INT,@pmNumAutCupo INT,@pmNumAutCheq INT,@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME
,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmFechaRem SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmTipoTrans INT,@pmTipoOrden VARCHAR(3),@pmTipoModifica VARCHAR(10),@pmNumCotizac INT,@pmCdCiaCotizac CHAR(2),@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,NumCotizac,CdCiaCotizac,OrigenAdd,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmPedido,@pmIdCia,@pmFecha,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmIdClieFact,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobretasa,@pmVrImpGlobal,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmDirEnvio,@pmIdLocEnv
,@pmLugarEnvio,@pmDiasEntraga,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmNitEmpTrans,@pmEmpTrans,@pmAsignarVeh,@pmpVehiculo,@pmCdConductor,@pmCdRuta,@pmListaPrec,@pmRefPedido,@pmModalidad,@pmVigencia,@pmNumAutoriza,@pmNumAutCupo
,@pmNumAutCheq,@pmNumAprob,@pmIdCiaApr,@pmFecAprob,@pmDetalleAprob,@pmCdUsuAprob,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmTipRem,@pmRemision,@pmIdCiaRem,@pmFechaRem,@pmNumCotizac,@pmCdCiaCotizac,@pmOrigenAdd,@pmZonaFrontera,@pmTipoTrans,@pmTipoOrden,@pmTipoModifica,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[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,NumCotizac,CdCiaCotizac,OrigenAdd,Anulado
,FecDev,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Opedido
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryOpedidoLta] @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmPedidoIni INT=Null,@pmPedidoFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmVigencia VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS
 SELECT Pedido,IdCia,Fecha,FechaVence,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrOtrDcto
,VrCargos,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac
,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,TipoTrans,NitEmpTrans,EmpTrans,pVehiculo,AsignarVeh,CdConductor,CdRuta,ListaPrec,RefPedido,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem
,NumAutoriza,NumAutCupo,NumAutCheq,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,CdUsuAprob,DetalleAprob,TipoOrden,TipoModifica,NumCotizac,CdCiaCotizac,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 ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpProdSaldosKar] @pmDelete BIT,@pmIdProducto VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 IF @pmDelete<>0  
  BEGIN
  DECLARE @TrnNombre VARCHAR(20)
  SELECT @TrnNombre = 'TrnProdSaldos'
  BEGIN TRANSACTION @TrnNombre
   DELETE  FROM ProdSaldos WHERE IdProducto LIKE ISNULL(@pmIdProducto,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%')
   INSERT INTO ProdSaldos  (IdProducto,IdBodega,IdCia,SaldoActual)
   SELECT K.IdProducto,K.IdBodega,IdCia,SUM(Entradas-Salidas)
   FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
   WHERE TipDoc NOT IN ('PED','REM','COT','ODC','GUI','OCC','ODS','VCC') AND TipoRef<>'SERVICIO'
   AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%')
   GROUP BY K.IdProducto,K.IdBodega,IdCia
  COMMIT TRANSACTION  @TrnNombre
  END
 ELSE
  INSERT INTO ProdSaldos  (IdProducto,IdBodega,IdCia,SaldoActual)
  SELECT K.IdProducto,K.IdBodega,IdCia,SUM(Entradas-Salidas)
  FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
  WHERE TipDoc NOT IN ('PED','REM','COT','ODC','GUI','OCC','ODS','VCC') AND TipoRef<>'SERVICIO'
  AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%')
  GROUP BY K.IdProducto,K.IdBodega,IdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryKardexMovTan] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdProducto VARCHAR(16)=Null
 AS 
 SELECT K.IdProducto AS CdProducto,CdTanque,SUM(Entradas) AS TotalEnt,SUM(Salidas) AS TotalSal
,SUM(VrCostoEnt) AS TotalCosEnt,SUM(VrCostoSal) AS TotalCosSal
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE  Fecha  BETWEEN @pmFechaIni AND @pmFechaFin AND TipDoc NOT IN ('ODC','PED','REM','COT','GUI','ODS','OCC','VCC')
 AND TipoRef<>'SERVICIO' AND Tanques=1 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 GROUP BY K.IdProducto,CdTanque