ALTER TABLE LogSobTasas ADD PorcAchol DECIMAL(14,4) DEFAULT(0) NOT NULL
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsLogSobTasas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsLogSobTasas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryLogSobTasas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryLogSobTasas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryLogSobTasasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryLogSobTasasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryLogSobTasasTot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryLogSobTasasTot]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsLogSobTasas] @pmIdDec VARCHAR(4),@pmIdLog INT,@pmItem INT,@pmnAnno INT,@pmnMes INT,@pmTipoEntidad VARCHAR(10),@pmIdEnterr VARCHAR(8),@pmNomEntidad VARCHAR(50)
,@pmCdProducto VARCHAR(16),@pmDescripProd VARCHAR(150),@pmClaseProd VARCHAR(20),@pmCantVentas DECIMAL(14,4),@pmCantDev DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrTotalVentas MONEY
,@pmVrTotalDev MONEY,@pmTarifa DECIMAL(14,4),@pmVrSobVentas MONEY,@pmVrSobDev MONEY,@pmVrPrecio MONEY,@pmFormaPago VARCHAR(10),@pmCdClase VARCHAR(4)
,@pmNomClase VARCHAR(50),@pmNumCuenta VARCHAR(30),@pmCdBanco VARCHAR(4),@pmNomBanco VARCHAR(50),@pmVrPagado MONEY,@pmObservacion VARCHAR(250),@pmTimeSys SMALLDATETIME
,@pmIdUsuario VARCHAR(11),@pmNombre VARCHAR(50),@pmEstado INT,@pmEsCorrecto BIT,@pmTarifGalon DECIMAL(14,4),@pmPorcBase DECIMAL(14,4),@pmPorcAchol DECIMAL(14,4)
 AS 
 INSERT INTO LogSobTasas (IdDec,IdLog,Item,nAnno,nMes,TipoEntidad,IdEnterr,NomEntidad,CdProducto,DescripProd,ClaseProd,CantVentas,CantDev,VrUnitario,VrTotalVentas,VrTotalDev,Tarifa,VrSobVentas,VrSobDev,VrPrecio
,FormaPago,CdClase,NomClase,NumCuenta,CdBanco,NomBanco,VrPagado,Observacion,TimeSys,IdUsuario,Nombre,Estado,EsCorrecto,TarifGalon,PorcBase,PorcAchol)
 VALUES (@pmIdDec,@pmIdLog,@pmItem,@pmnAnno,@pmnMes,@pmTipoEntidad,@pmIdEnterr,@pmNomEntidad,@pmCdProducto,@pmDescripProd,@pmClaseProd,@pmCantVentas,@pmCantDev,@pmVrUnitario
,@pmVrTotalVentas,@pmVrTotalDev,@pmTarifa,@pmVrSobVentas,@pmVrSobDev,@pmVrPrecio,@pmFormaPago,@pmCdClase,@pmNomClase,@pmNumCuenta,@pmCdBanco,@pmNomBanco
,@pmVrPagado,@pmObservacion,@pmTimeSys,@pmIdUsuario,@pmNombre,@pmEstado,@pmEsCorrecto,@pmTarifGalon,@pmPorcBase,@pmPorcAchol)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryLogSobTasas] @pmIdDec VARCHAR(4),@pmIdLog INT
 AS 
 SELECT IdDec,IdLog,Item,nAnno,nMes,TipoEntidad,IdEnterr,NomEntidad
,CdProducto,DescripProd,ClaseProd,CantVentas,CantDev,VrUnitario,VrTotalVentas
,VrTotalDev,Tarifa,VrSobVentas,VrSobDev,VrPrecio,FormaPago,CdClase,NomClase
,NumCuenta,CdBanco,NomBanco,VrPagado,Observacion,TarifGalon,PorcBase,PorcAchol
 FROM LogSobTasas
 WHERE IdDec=@pmIdDec AND IdLog=@pmIdLog
 AND Estado<=0 AND EsCorrecto<>0
 ORDER BY TipoEntidad,IdEnterr,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryLogSobTasasLta] @pmIdDec VARCHAR(4),@pmIdLog INT
 AS 
 SELECT Item,IdEnterr,NomEntidad,ClaseProd,CdProducto,DescripProd,CantVentas,CantDev,CantVentas-CantDev AS CantNeto,Tarifa,PorcBase,PorcAchol
 ,CASE WHEN PorcBase>0 AND PorcBase<100 THEN ((CantVentas-CantDev)*PorcBase)/100  ELSE CantVentas-CantDev END AS BaseSobTasa
 ,CASE WHEN PorcBase>0 AND PorcBase<100 THEN (((CantVentas-CantDev)*PorcBase)/100)*Tarifa ELSE (CantVentas-CantDev)*Tarifa END AS TotalSobretasa
 ,VrPrecio,VrTotalVentas,VrTotalDev,VrTotalVentas-VrTotalDev AS VentasNeto,TarifGalon,VrSobVentas,VrSobDev,VrSobVentas-VrSobDev AS SobtasaVentas
,VrUnitario,TipoEntidad,nAnno,nMes,FormaPago,CdClase,NomClase,NumCuenta,CdBanco,NomBanco,VrPagado,Estado,EsCorrecto,Observacion,TimeSys,IdUsuario,Nombre,IdDec,IdLog
 FROM LogSobTasas
 WHERE IdDec=@pmIdDec AND IdLog=@pmIdLog
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryLogSobTasasTot] @pmIdDec VARCHAR(4),@pmIdLog INT
 AS 
 SELECT COUNT(Item) SCANT,SUM(CantVentas) AS SCANVEN,SUM(CantDev) AS SCANDEV
,SUM(VrTotalVentas) AS STOTVEN,SUM(VrTotalDev) AS STOTDEV
,SUM(VrSobVentas) AS STOTSOB,SUM(VrSobDev) AS STOTSDEV
,SUM(CASE WHEN PorcBase>0 AND PorcBase<100 THEN ((CantVentas-CantDev)*PorcBase)/100  ELSE CantVentas-CantDev END) AS BaseSobTasa
,SUM(CASE WHEN PorcBase>0 AND PorcBase<100 THEN (((CantVentas-CantDev)*PorcBase)/100)*Tarifa ELSE (CantVentas-CantDev)*Tarifa END) AS TotalSobretasa
 FROM LogSobTasas
 WHERE IdDec=@pmIdDec AND IdLog=@pmIdLog
GO