if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AuxInv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AuxInv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfDepreciaRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAfDepreciaRes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasNet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasNet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasNetDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFacturasNetDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercClieCuposLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercClieCuposLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AuxInv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AuxInv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AuxInvVen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AuxInvVen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexOdc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexOdc]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturasNet] @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
 AS 
 SELECT TipDoc,Factura,F.IdCia AS CdCia,Compania,Fecha,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia
,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrCostos,VrSobretasa,VrImpGlobal
,VrFaltantes,VrAnticipos,VrNeto,VrAplicado,Cantidad,CantPuntos,PuntosAcum,BaseImp,BaseRet,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,F.IdCCosto AS IdCenCost,CCosto
,F.IdSubCos AS IdSubCent,SubCosto,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio
,DE.Departamento AS DptoEnvio,DiasEntraga,F.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,pVehiculo
,CdConductor,TC.RazonSocial AS Conductor,F.CdRuta,Ruta,TipPed,Pedido,IdCiaPed,TipRem,Remision,IdCiaRem,TipCot,Cotizacion,IdCiaCot,FecPedido,KmtVehic,VrImpCons,VrReteCREE,Modalidad,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,TimeSys,F.FecUpdate AS Fec_Update,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,CLI.IdSzona AS CdSubzona,SZ.Subzona,SZ.IdZona AS CdZona,Z.Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,A.CdFntePago AS CdFuente,FuentePago,TipoZona,A.IdSzona AS AgeCdSubzona,SZA.Subzona AS AgeSubzona,SZA.IdZona AS AgeCdZona,ZA.Zona AS AgeZona
 FROM Trn_Facturas AS F  INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON F.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma
 INNER JOIN CentroCosto AS CC ON F.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON F.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN Localidades AS LE ON F.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN SubCentros AS SC ON F.IdSubCos=SC.IdSubCos LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 LEFT JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona LEFT JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona 
 WHERE TipDoc LIKE ISNULL(@pmTipDoc,'%') AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND F.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND F.IdVend LIKE ISNULL(@pmIdVend,'%') AND F.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 UNION
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.IdCliente AS NitCliente,T.RazonSocial AS NomCliente
,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,D.VrSubTotal AS SubTotal,D.VrDescuento AS Descuentos,D.VrImpuesto AS Impuestos
,D.VrRetencion AS ReteFte,D.VrReteICA AS ReteIca,D.VrReteIVA AS ReteIVA,D.VrFletes AS Fletes,D.VrOtros AS Otros,D.VrCargos AS OtrosCargos,D.VrOtrDcto AS OtrosDctos,D.VrCostos AS TotalCostos
,D.VrSobretasa AS Sobretasas,D.VrImpGlobal AS ImpGlobal,D.VrFaltantes AS Faltantes,D.VrAnticipos AS Anticipos,D.VrNeto AS TotalNeto,0,D.Cantidad AS CantDev,D.CantPuntos AS CantPuntDev,0
,D.BaseImp AS BaseIVA,D.BaseRet AS BaseRetFte,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,D.TarifaRtc,D.IdCCosto AS IdCenCost,CCosto,D.IdSubCos AS IdSubCent,SubCosto,D.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,D.TarifaCom AS TarifCom,DirEnvio,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,DiasEntraga,F.IdForma AS CdForma,FormaPago,DetallePago
,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,pVehiculo,CdConductor,TC.RazonSocial AS Conductor,F.CdRuta,Ruta,TipPed,D.Pedido AS NumPedido,D.IdCiaPed AS CdCiaPed
,TipRem,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem,TipCot,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCotiza,D.FecPedido AS FechaPed,D.KmtVehic AS DevKmtVeh,D.VrImpCons,D.VrReteCREE,D.Modalidad AS ModFactura
,D.TipCom AS TipoComp,D.Comprobante AS NumComp,D.IdCiaCom AS CodCiaCom,0,D.Factura AS NumFactura,FecDoc,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fech_Sys
,D.FecUpdate AS Fech_Update,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,CLI.IdSzona AS CdSubzona,SZ.Subzona,SZ.IdZona AS CdZona,Z.Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,A.CdFntePago AS CdFuente,FuentePago,TipoZona,A.IdSzona,SZA.Subzona,SZA.IdZona,ZA.Zona
 FROM Trn_DevFcr AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS VN ON D.IdVend=VN.IdTercero INNER JOIN Agencias AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Trn_Facturas AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia
 INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN  Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo
 LEFT JOIN  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente 
 LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona LEFT JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona 
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.TipDoc LIKE ISNULL(@pmTipDoc ,'%') 
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND D.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND D.Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY F.IdCia,Factura
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFacturasNetDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdLocEnv VARCHAR(8)=Null
,@pmModalidad VARCHAR(10)=Null,@pmIdProducto VARCHAR(16)=Null,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null
,@pmTipoRef VARCHAR(10)=Null
 AS 
 SELECT F.TipDoc AS TipoFact,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrRetencion,VrReteICA,VrReteIVA,VrFletes,VrOtros,VrCargos,VrOtrDcto
,VrNeto,CantPuntos,PuntosAcum,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,F.CdRuta,Ruta
,TipPed,Pedido,IdCiaPed,TipRem,F.Remision AS NumRemision,F.IdCiaRem AS CdCiaRem,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CdCiaCotiza,FecPedido
,Modalidad,KmtVehic,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdEstado AS CdEstado,Estado,F.TimeSys AS Fecha_Add,IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario
--detalles
,K.IdProducto AS CdProducto,DescripProd,Item,K.IdBodega AS CdBodega,Bodega,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario
,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva AS TarifIva,VrIvaEnt,VrIvaSal,K.TarifaRet AS TarifRet,VrReteEnt,VrReteSal
,K.TarifaIca AS TarifIca,VrIcaEnt,VrIcaSal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden
,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,Comision,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper
,K.pVehiculo AS KarPlacaVeh,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,K.CdSubCos AS CodSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,K.Remision AS KarNumRemision,K.IdCiaRem AS KarCdCiaRem,K.Cotizacion AS KarNumCotizacion
,K.IdCiaCot AS KarCiaCotiza,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,CLI.IdSzona AS CdSubzona,SZ.Subzona,SZ.IdZona AS CdZona,Z.Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,A.CdFntePago AS CdFuente,FuentePago,A.TipoZona AS TipoZona,A.IdSzona AS AgeCdSubzona,SZA.Subzona AS AgeSubzona,SZA.IdZona AS AgeCdZona,ZA.Zona AS AgeZona
 FROM Trn_Facturas AS F  INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON F.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON F.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo  INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia 
 INNER JOIN TercCliente AS CLI ON F.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.IdCia
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Terceros AS VN ON K.IdVend=VN.IdTercero LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 LEFT JOIN  Localidades AS LK ON K.CdLocal=LK.IdLocal 
 LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero
 LEFT JOIN  Localidades AS LE ON F.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 LEFT JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona LEFT JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona 
 WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsProdBase=0 AND F.TipDoc LIKE ISNULL(@pmTipDoc,'%')
 AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND F.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND F.IdVend LIKE ISNULL(@pmIdVend,'%') AND F.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND  K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
 UNION
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.IdCliente AS NitCliente,T.RazonSocial AS NomCliente
,D.IdAgencia AS IdAgncia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,D.VrRetencion AS ReteFte,D.VrReteICA AS ReteIca,D.VrReteIVA AS ReteIVA,D.VrFletes AS Fletes
,D.VrOtros AS Otros,D.VrCargos AS OtrosCargos,D.VrOtrDcto AS OtrosDctos,D.VrNeto AS TotalNeto,D.CantPuntos AS CantPuntDev,0,D.IdLocEnv AS CodCiuEnvio,LE.Localidad AS CiudadEnvio
,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor
,F.CdRuta,Ruta,TipPed,D.Pedido AS NumPedido,D.IdCiaPed AS CdCiaPed,TipRem,D.Remision AS NumRemision,D.IdCiaRem AS CdCiaRem,TipCot,D.Cotizacion AS NumCotizacion,D.IdCiaCot AS CdCiaCotiza
,D.FecPedido AS FechaPed,D.Modalidad AS ModFactura,D.KmtVehic AS DevKmtVeh,D.TipCom AS TipoComp,D.Comprobante AS NumComp,D.IdCiaCom AS CodCiaCom,0,D.Factura AS NumFactura,FecDoc
,D.Observacion AS Observ,D.IdEstado AS CdEstado,Estado,D.TimeSys AS Fech_Sys,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS IdUsuari,Usuario
--detalles
,K.IdProducto AS CdProducto,DescripProd,Item,K.IdBodega AS CdBodega,Bodega,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,VrUnitario
,VrCostoEnt,VrCostoSal,VrCostProm,VrPrecio,VrPrecio*(Entradas+Salidas) AS VrTotal,TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva AS TarifIva,VrIvaEnt,VrIvaSal,K.TarifaRet AS TarifRet,VrReteEnt,VrReteSal
,K.TarifaIca AS TarifIca,VrIcaEnt,VrIcaSal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,Unidades,K.Descripcion AS KarDescripcion,K.Referencia AS Referncia,Referencia2,FecOrden
,CdAgencia,KA.Agencia AS KarAgencia,KA.CodAgencia AS KarCodAgencia,K.IdVend AS NitVend,VN.RazonSocial AS Vendedor,Comision,CdOperario,OP.RazonSocial AS NomOperario,ComisnOper
,K.pVehiculo AS KarPlacaVeh,CdLocal,LK.Localidad AS KarCiudad,CdCCosto,CCosto,K.CdSubCos,SubCosto,TipOrd,NumOrden,IdCiaOrd,K.Remision AS KarNumRemision,K.IdCiaRem AS KarCdCiaRem,K.Cotizacion AS KarNumCotizacion
,K.IdCiaCot AS KarCiaCotiza,ListaPrec,VrBruto,VrBase,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,CLI.IdSzona AS CdSubzona,SZ.Subzona,SZ.IdZona AS CdZona,Z.Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,A.CdFntePago AS  CdFuente,FuentePago,A.TipoZona,A.IdSzona,SZA.Subzona,SZA.IdZona,ZA.Zona
 FROM Trn_DevFcr AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON D.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Agencias  AS A ON D.IdAgencia=A.IdAgencia 
 INNER JOIN TercCliente AS CLI ON D.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Trn_Facturas AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia
 INNER JOIN  Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo
 INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Terceros AS VN ON K.IdVend=VN.IdTercero LEFT JOIN Agencias  AS KA ON K.CdAgencia=KA.IdAgencia
 LEFT JOIN CentroCosto AS CC ON K.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON K.CdSubCos=SC.IdSubCos
 LEFT JOIN  Localidades AS LK ON K.CdLocal=LK.IdLocal  LEFT JOIN Terceros AS OP ON K.CdOperario=OP.IdTercero
 LEFT JOIN  Localidades AS LE ON D.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta
 LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 LEFT JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona LEFT JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona 
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsProdBase=0 AND D.TipDoc LIKE ISNULL(@pmTipDoc ,'%') 
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND D.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND D.IdVend LIKE ISNULL(@pmIdVend,'%') AND D.IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%')
 AND D.Modalidad LIKE ISNULL(@pmModalidad,'%') AND  K.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 AND TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND PM.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo ,'%')
 ORDER BY F.IdCia,F.Factura
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAfDepreciaRes]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdLinea VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdCia CHAR(2)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null
,@pmIdActivo VARCHAR(30)=Null
 AS 
 SELECT DP.IdActivo AS CdActivo,NomActivo,Fecha,D.Numero AS NumDeprecia,D.IdUsuario AS CdUsuario,Usuario,FechaCrea,D.IdCia AS DepCdCia
,Item,VrCostoIni,DP.VrMejoras AS ValMejoras,DP.VrDisminucion AS ValDisminuc,VrAcuAjustes,VrDepreciaHis,VrDepreciaMes,VrAjustesInf,VrAjustesDep,VrDepreciaAcum
,VrAjuDepAcum,TarifaAju,DP.IdCia AS DetCodCia,CN.Compania AS DetCompania,CdCCosto,CCD.CCosto AS DetCentroCosto,CdSubCos,SCD.SubCosto AS DetSubCentro,CdInstala,INS.Instlacion AS DetInstacion
,CdDep,DE.Dependencia AS DetDependencia,NitRespons,TRD.RazonSocial AS DetNomResponsable,FecIniDep,MesesVida,MetodoDeprec
--datos del activo
,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CC.CCosto AS ActCentroCosto,A.IdSubCos AS CdSubcentro,SC.SubCosto AS ActSubCentro,A.IdInstala AS CdInstala,I.Instlacion AS ActInstalacion
,A.IdDep AS CdDep,DEP.Dependencia AS NomDependencia,IdRespons,TR.RazonSocial AS Responsable,A.IdCia AS CdCia,CI.Compania AS NomCia,IdProv,T.RazonSocial AS Proveedor
,FecCompra,FecActivacion,NumOrden,IdCiaOrden,NumEntrada,IdCiaEnt,VrCompra,VrComercial,VrBaseDep,A.VrMejoras AS TotMejoras,A.VrDisminucion AS TotDisminucion,A.VidaUtil AS Vida_util,FecFinDep,MetodoDep
,Ajustable,Contable,CueEnGrupo,VrAjuInfAcum,VrDepAcuHis,VrDepAcuAju,VrAjuInfDep,NumMesesDep,FecUltDep,Marca,Modelo,NumPlaca,NumDoc,Referencia,NumSerie,CodInvent,CodBarras
,A.IdEstado AS CdEstado,ED.Estado AS ActEstado,FecRetiro,A.IdCausal AS CdCausal,Causal,VrVenta,FecCreacion,VrSalvamnto,Unidades,Cantidad,Dep_Partes,A.TipoContable
 FROM  Trn_AfDetalle AS DP  INNER JOIN Trn_AfDeprecia  AS D ON  DP.Numero=D.Numero INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Activos AS A ON DP.IdActivo=A.IdActivo  INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN CentroCosto AS CC ON A.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON A.IdInstala=I.IdInstala
 INNER JOIN Dependencias AS DEP ON A.IdDep=DEP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN Terceros AS T ON A.IdProv=T.IdTercero
 INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado 
 INNER JOIN Companias AS CN ON DP.IdCia=CN.IdCia
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN AfCausales AS CA ON A.IdCausal=CA.IdCausal
 LEFT JOIN CentroCosto AS CCD ON DP.CdCCosto=CCD.IdCCosto LEFT JOIN Instalaciones AS INS ON DP.CdInstala=INS.IdInstala
 LEFT JOIN Dependencias AS DE ON DP.CdDep=DE.IdDep LEFT JOIN Terceros AS TRD ON DP.NitRespons=TRD.IdTercero
 LEFT JOIN SubCentros AS SCD ON DP.CdSubCos=SCD.IdSubCos
 WHERE  D.Fecha BETWEEN  @pmFechaIni  AND @pmFechaFin AND D.Estado<>0  AND DP.IdActivo LIKE ISNULL(@pmIdActivo,'%')
 AND DP.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND  G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')  
 AND DP.CdInstala LIKE ISNULL(@pmIdInstala,'%') AND DP.CdDep LIKE ISNULL(@pmIdDep,'%')
 AND DP.NitRespons LIKE ISNULL(@pmIdRespons,'%')  AND DP.CdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 ORDER BY NomActivo,Fecha
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_AuxInv] @pmtmEst CHAR(2)
 AS 
 SELECT tmIdProducto,DescripProd,tmIdCia,Compania,tmIdBodega,Bodega,tmNumLote,tmSaldoAnt,tmCostoAnt
,tmEntradas,tmCostoEnt,tmSalidas,tmCostoSal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmSaldoAnt+tmEntradas-tmSalidas ELSE tmSalidas-tmEntradas  END AS NuevoSaldo
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoAnt+tmCostoEnt-tmCostoSal ELSE 0  END AS CostoTotal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm ELSE 0  END AS CostoPromActual
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm*(tmSaldoAnt+tmEntradas-tmSalidas) ELSE 0  END AS  CostoTotalPromedio,tmProdCia
,TipoRef,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,tmCdTanque,PM.IdUnd AS CdUnid,UM.Unidad AS UnidadPres,PM.UndMed AS CdUndMed,SU.Unidad AS UnidadMed
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,Tamano,Color,Referencia,VrCosto AS ProdUltCosto,VrCostPmd AS ProdCostoProm,IdTarIva,Tarifa,Simbolo,IvaInc
,Precio1,Precio2,Precio3,Precio4,Precio5,IdProv,PV.RazonSocial AS NomProveedor,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct
,Factor1,Factor2,Factor3,Factor4,Factor5,PM.Inactivo AS ProdInactivo 
 FROM tm_AuxInv  AS A INNER JOIN ProdMcias  AS PM ON A.tmIdProducto=PM.IdProducto 
 INNER JOIN UndMed AS UM ON PM.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
 LEFT JOIN Companias AS CN ON A.tmIdCia=CN.IdCia  LEFT JOIN Bodegas AS B ON A.tmIdBodega=B.IdBodega
 LEFT JOIN Tablapor AS TI ON PM.IdTarIva=TI.IdTarifa
 WHERE tmEst=@pmtmEst
 ORDER BY DescripProd,tmIdCia,tmIdBodega
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexOdc] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,tmCdTanque,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto,tmVrUnitario
,(tmEntradas*tmVrPrecio)-tmVrDcto AS VrTotal,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmUnidades,Bodega,tmReferencia,tmDescripcion,tmImpGlobal,tmSobretasa
,tmCdCCosto,tmCdSubCos,tmCodTarDct,tmCodTarRet,tmCodTarIca,tmServcios,Tanques,tmTipDoc,tmDocumento,tmIdCia,tmItemCbo AS ItemReq,tmVrImvCosto
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 WHERE  tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_AuxInvVen] @pmtmEst CHAR(2)
 AS 
 SELECT tmIdProducto,DescripProd,tmIdCia,Compania,tmIdBodega,Bodega,tmNumLote,tmSaldoAnt,tmCostoAnt
,tmEntradas,tmCostoEnt,tmSalidas,tmCostoSal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmSaldoAnt+tmEntradas-tmSalidas ELSE tmSalidas-tmEntradas  END AS NuevoSaldo
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoAnt+tmCostoEnt-tmCostoSal ELSE 0  END AS CostoTotal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm ELSE 0  END AS CostoPromActual
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm*(tmSaldoAnt+tmEntradas-tmSalidas) ELSE 0  END AS  CostoTotalPromedio,tmProdCia
--información de ventas
,tmVtaCant,tmVtaCantDev,tmVtaCant-tmVtaCantDev AS VtaCantNeto,tmVtaTotal,tmVtaTotalDev,(tmVtaTotal-tmVtaTotalDev)+(tmVtaGlobal-tmVtaGlobalDev) AS VtaNeto
,tmVtaCosto,tmVtaCostoDev,tmVtaCosto-tmVtaCostoDev AS VtaCostoNeto,tmVtaDcto,tmVtaDctoDev,tmVtaDcto-tmVtaDctoDev AS VtaDctoNeto
,((tmVtaTotal-tmVtaTotalDev)+(tmVtaGlobal-tmVtaGlobalDev))-(tmVtaCosto-tmVtaCostoDev)  AS VtaUtilidad,tmVtaIva,tmVtaIvaDev,tmVtaRet,tmVtaRetDev
,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev
,TipoRef,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,tmCdTanque,PM.IdUnd AS CdUnid,UM.Unidad AS UnidadPres,PM.UndMed AS CdUndMed,SU.Unidad AS UnidadMed
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,Tamano,Color,Referencia,VrCosto AS ProdUltCosto,VrCostPmd AS ProdCostoProm,IdTarIva,Tarifa,Simbolo,IvaInc
,Precio1,Precio2,Precio3,Precio4,Precio5,IdProv,PV.RazonSocial AS NomProveedor,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct
,Factor1,Factor2,Factor3,Factor4,Factor5,PM.Inactivo AS ProdInactivo
 FROM tm_AuxInv  AS A INNER JOIN ProdMcias  AS PM ON A.tmIdProducto=PM.IdProducto 
 INNER JOIN UndMed AS UM ON PM.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
 LEFT JOIN Companias AS CN ON A.tmIdCia=CN.IdCia  LEFT JOIN Bodegas AS B ON A.tmIdBodega=B.IdBodega
 LEFT JOIN Tablapor AS TI ON PM.IdTarIva=TI.IdTarifa
 WHERE tmEst=@pmtmEst 
 ORDER BY DescripProd,tmIdCia,tmIdBodega
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercClieCuposLta] 
 AS 
 SELECT C.IdClie,RazonSocial,C.IdAgencia AS CdAgencia,Agencia,C.nAnno,C.nMes,C.CupoGals,C.CupoZFE,C.CupoAdic
 FROM TercClieCupos AS C INNER JOIN Terceros AS T ON C.IdClie=T.IdTercero 
 LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia
 WHERE T.Inactivo=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AuxInv] @pmtmEst CHAR(2),@pmtmIdProducto VARCHAR(16),@pmtmIdBodega VARCHAR(4),@pmtmIdCia CHAR(2),@pmtmNumLote VARCHAR(30),@pmtmSaldoAnt DECIMAL(14,4)
,@pmtmEntradas DECIMAL(14,4),@pmtmSalidas DECIMAL(14,4),@pmtmCostoAnt MONEY,@pmtmCostoEnt MONEY,@pmtmCostoSal MONEY,@pmtmCostoProm MONEY,@pmtmCdTanque VARCHAR(4),@pmtmVtaCant DECIMAL(14,4)
,@pmtmVtaCantDev DECIMAL(14,4),@pmtmVtaTotal MONEY,@pmtmVtaTotalDev MONEY,@pmtmVtaCosto MONEY,@pmtmVtaCostoDev MONEY,@pmtmVtaIva MONEY,@pmtmVtaIvaDev MONEY,@pmtmVtaDcto MONEY
,@pmtmVtaDctoDev MONEY,@pmtmVtaRet MONEY,@pmtmVtaRetDev MONEY,@pmtmVtaIca MONEY,@pmtmVtaIcaDev MONEY,@pmtmVtaTasa MONEY,@pmtmVtaTasaDev MONEY,@pmtmVtaGlobal MONEY
,@pmtmVtaGlobalDev MONEY,@pmtmVtaSol MONEY,@pmtmVtaSolDev MONEY,@pmtmProdCia BIT
 AS 
 INSERT INTO tm_AuxInv (tmEst,tmIdProducto,tmIdBodega,tmIdCia,tmNumLote,tmSaldoAnt,tmEntradas,tmSalidas,tmCostoAnt,tmCostoEnt,tmCostoSal,tmCostoProm,tmCdTanque,tmVtaCant,tmVtaCantDev,tmVtaTotal,tmVtaTotalDev
,tmVtaCosto,tmVtaCostoDev,tmVtaIva,tmVtaIvaDev,tmVtaDcto,tmVtaDctoDev,tmVtaRet,tmVtaRetDev,tmVtaIca,tmVtaIcaDev,tmVtaTasa,tmVtaTasaDev,tmVtaGlobal,tmVtaGlobalDev,tmVtaSol,tmVtaSolDev,tmProdCia)
 VALUES (@pmtmEst,@pmtmIdProducto,@pmtmIdBodega,@pmtmIdCia,@pmtmNumLote,@pmtmSaldoAnt,@pmtmEntradas,@pmtmSalidas,@pmtmCostoAnt,@pmtmCostoEnt,@pmtmCostoSal,@pmtmCostoProm,@pmtmCdTanque
,@pmtmVtaCant,@pmtmVtaCantDev,@pmtmVtaTotal,@pmtmVtaTotalDev,@pmtmVtaCosto,@pmtmVtaCostoDev,@pmtmVtaIva,@pmtmVtaIvaDev,@pmtmVtaDcto,@pmtmVtaDctoDev,@pmtmVtaRet,@pmtmVtaRetDev
,@pmtmVtaIca,@pmtmVtaIcaDev,@pmtmVtaTasa,@pmtmVtaTasaDev,@pmtmVtaGlobal,@pmtmVtaGlobalDev,@pmtmVtaSol,@pmtmVtaSolDev,@pmtmProdCia)
GO