DROP PROCEDURE paQryKardexBDev
DROP PROCEDURE paQryKardexBFcr
DROP PROCEDURE paQryKardexBon
DROP PROCEDURE paQryKardexBond
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexBDev @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT TipDev,Devolucion,F.IdCia AS CdCia,F.Fecha AS FechaDev,F.TipDoc AS TipoFact,F.Factura AS NumFact
,IdCiaDoc,FecDoc,IdCliente,IdAgencia,F.IdVend AS CdVend,TarifaCom,Modalidad,ModdDev
--datos del kardex
,K.IdProducto AS CdProducto,DescripProd,TipoRef,P.IdSubgrupo AS CdSubgrupo,Subgrupo,P.IdUnd AS CdUnid,Tanques
,SUM(Entradas) AS STOTENT,SUM(VrCostoEnt) AS STOTCOS,SUM(Entradas*VrPrecio) AS SVALTOT
,SUM(VrIvaEnt) AS STOTIVA,SUM(VrDctoEnt) AS STOTDCT
 FROM Trn_DevFcr AS F INNER JOIN Trn_Kardex AS K ON F.TipDev=K.TipDoc AND F.Devolucion=K.Documento AND F.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo
 WHERE TipDev IN ('DFC','DF1','DF2','DF3','DF4','DF5')  AND F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND NoVentas>0
 AND IdCliente=@pmIdCliente AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 GROUP BY  TipDev,Devolucion,F.IdCia,F.Fecha,F.TipDoc,F.Factura,IdCiaDoc,FecDoc,IdCliente,IdAgencia
,F.IdVend,TarifaCom,Modalidad,ModdDev,K.IdProducto,DescripProd,TipoRef,P.IdSubgrupo,Subgrupo,P.IdUnd,Tanques
 ORDER BY TipDev,Devolucion,F.IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexBFcr @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT F.TipDoc AS TipoFact,F.Factura AS NumFact,F.IdCia AS CdCia,F.Fecha AS FecFact,FechaVence,IdCliente,IdAgencia,IdCCosto,IdSubCos
,F.IdVend AS CdVend,TarifaCom,Modalidad,Anulado,NumDev
--datos del kardex
,K.IdProducto AS CdProducto,DescripProd,TipoRef,P.IdSubgrupo AS CdSubgrupo,Subgrupo,P.IdUnd AS CdUnid,Tanques
,SUM(Salidas) AS STOTSAL,SUM(VrCostoSal) AS STOTCOS,SUM(Salidas*VrPrecio) AS SVALTOT
,SUM(VrIvaSal) AS STOTIVA,SUM(VrDctoSal) AS STOTDCT
 FROM Trn_Facturas AS F INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo
 WHERE F.TipDoc IN  ('FCR','FC1','FC2','FC3','FC4','FC5') AND F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND NoVentas>0
 AND IdCliente=@pmIdCliente AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 GROUP BY F.TipDoc,F.Factura,F.IdCia,F.Fecha,FechaVence,IdCliente,IdAgencia,IdCCosto,IdSubCos,F.IdVend,TarifaCom
,Modalidad,Anulado,NumDev,K.IdProducto,DescripProd,TipoRef,P.IdSubgrupo,Subgrupo,P.IdUnd,Tanques
 ORDER BY  F.TipDoc,F.Factura,F.IdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexBon @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT K.IdProducto AS CdProducto,DescripProd,TipoRef,P.IdSubgrupo AS CdSubgrupo,Subgrupo,P.IdUnd AS CdUnid,Tanques
,SUM(Salidas) AS STOTSAL,SUM(VrCostoSal) AS STOTCOS,SUM(Salidas*VrPrecio) AS SVALTOT
,SUM(VrIvaSal) AS STOTIVA,SUM(VrDctoSal) AS STOTDCT
 FROM Trn_Facturas AS F INNER JOIN Trn_Kardex AS K ON F.TipDoc=K.TipDoc AND F.Factura=K.Documento AND F.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo
 WHERE F.TipDoc IN  ('FCR','FC1','FC2','FC3','FC4','FC5') AND F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND NoVentas>0
 AND IdCliente=@pmIdCliente AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 GROUP BY  K.IdProducto,DescripProd,TipoRef,P.IdSubgrupo,Subgrupo,P.IdUnd,Tanques
 ORDER BY P.IdSubgrupo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexBond @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT K.IdProducto AS CdProducto,DescripProd,TipoRef,P.IdSubgrupo AS CdSubgrupo,Subgrupo,P.IdUnd AS CdUnid,Tanques
,SUM(Entradas) AS STOTENT,SUM(VrCostoEnt) AS STOTCOS,SUM(Entradas*VrPrecio) AS SVALTOT
,SUM(VrIvaEnt) AS STOTIVA,SUM(VrDctoEnt) AS STOTDCT
 FROM Trn_DevFcr AS F INNER JOIN Trn_Kardex AS K ON F.TipDev=K.TipDoc AND F.Devolucion=K.Documento AND F.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo
 WHERE TipDev IN ('DFC','DF1','DF2','DF3','DF4','DF5')  AND F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND NoVentas>0
 AND IdCliente=@pmIdCliente AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 GROUP BY  K.IdProducto,DescripProd,TipoRef,P.IdSubgrupo,Subgrupo,P.IdUnd,Tanques
 ORDER BY P.IdSubgrupo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO