ALTER TABLE LogRecaudo ADD EntidadRec CHAR(2) DEFAULT('AB') NOT NULL
,Convenio VARCHAR(20)
GO
CREATE TABLE LogCarnets ( 
IdReg INT DEFAULT (0) NOT NULL,
IdEmpleado VARCHAR(16) NOT NULL,
nContrato INT DEFAULT (0) NOT NULL,
FotoEmp IMAGE NOT NULL
 CONSTRAINT PK_LogCarnets PRIMARY KEY CLUSTERED (IdReg))
GO
--VERSION 4 de Mayo/2010
ALTER TABLE LogPreciosDet ADD  Sobretasa DECIMAL(14,4) DEFAULT(0) NOT NULL,
ImpGlobal DECIMAL(14,4) DEFAULT(0) NOT NULL, Soldicom DECIMAL(14,4) DEFAULT(0) NOT NULL
,IvaComb DECIMAL(14,4) DEFAULT(0) NOT NULL, PrecioRef DECIMAL(14,4) DEFAULT(0) NOT NULL
,CostoRec DECIMAL(14,4) DEFAULT(0) NOT NULL
GO

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsLogRecaudo @pmNumero INT,@pmNitEmpresa VARCHAR(16),@pmFechaRec SMALLDATETIME,@pmFechaCrea SMALLDATETIME,@pmHoraCrea VARCHAR(10)
,@pmSecuencia VARCHAR(3),@pmTipoCuenta INT,@pmNumCuenta VARCHAR(20),@pmTotalReg INT,@pmValorTotal MONEY,@pmEstadoProc INT,@pmObservacion VARCHAR(250)
,@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmNombre VARCHAR(50),@pmNomArchivo VARCHAR(100),@pmEntidadRec CHAR(2),@pmConvenio VARCHAR(20)
 AS 
 INSERT INTO LogRecaudo (Numero,NitEmpresa,FechaRec,FechaCrea,HoraCrea,Secuencia,TipoCuenta,NumCuenta,TotalReg,ValorTotal,EstadoProc,Observacion,TimeSys,IdUsuario,Nombre,NomArchivo,EntidadRec,Convenio)
 VALUES (@pmNumero,@pmNitEmpresa,@pmFechaRec,@pmFechaCrea,@pmHoraCrea,@pmSecuencia,@pmTipoCuenta,@pmNumCuenta,@pmTotalReg,@pmValorTotal,@pmEstadoProc
,@pmObservacion,@pmTimeSys,@pmIdUsuario,@pmNombre,@pmNomArchivo,@pmEntidadRec,@pmConvenio)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryLogRecaudo @pmNumero INT
 AS 
 SELECT Numero,NitEmpresa,FechaRec,FechaCrea,HoraCrea,Secuencia,TipoCuenta,NumCuenta,TotalReg
,ValorTotal,EstadoProc,Observacion,TimeSys,IdUsuario,Nombre,NomArchivo,EntidadRec,Convenio
 FROM LogRecaudo
 WHERE Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryLogRecaudoFmt @pmNumeroIni INT,@pmNumeroFin INT
 AS 
 SELECT R.Numero AS NumeroReg,NitEmpresa,R.FechaRec AS FecRecaudo,FechaCrea,HoraCrea,Secuencia,TipoCuenta,NumCuenta,TotalReg
,ValorTotal,EstadoProc,Observacion,TimeSys,IdUsuario,Nombre,NomArchivo,EntidadRec,Convenio
--detalles
,Item,IdCliente,CdAgencia,TipoDoc,NumDoc,IdCia,ValorRec,Referencia,Estado,NumRecibo,IdCiaRec,D.FechaRec AS FecRecibo
,FechaFact,ProcPago,MedioPago,SecRegistro,CodEntidad,CodSucursal,NumOper,NumAutoriza,CodServicio,NumLote,DescRegistro
,Comentario,IdVend,CodTarCom,TarifaCom,CdCiudad,VrFactura,VrAbonado,NomCliente
 FROM LogRecaudo AS R LEFT JOIN LogRecDetalle AS D ON R.Numero=D.Numero
 WHERE R.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin
 ORDER BY R.Numero,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--mayo 4/2010


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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsLogPreciosDet @pmIdLog INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdProducto VARCHAR(16),@pmDescripProd VARCHAR(150),@pmVrCosto MONEY,@pmVrCostPmd MONEY,@pmPrecioAnt1 MONEY,@pmPrecioAnt2 MONEY
,@pmPrecioAnt3 MONEY,@pmPrecioAnt4 MONEY,@pmPrecioAnt5 MONEY,@pmPrecio1 MONEY,@pmPrecio2 MONEY,@pmPrecio3 MONEY,@pmPrecio4 MONEY,@pmPrecio5 MONEY,@pmIvaInc1 BIT,@pmIvaInc2 BIT,@pmIvaInc3 BIT,@pmIvaInc4 BIT
,@pmIvaInc5 BIT,@pmTarifIva DECIMAL(14,4),@pmBaseMgn1 CHAR(1),@pmBaseMgn2 CHAR(1),@pmBaseMgn3 CHAR(1),@pmBaseMgn4 CHAR(1),@pmBaseMgn5 CHAR(1),@pmCdMgn1 VARCHAR(4),@pmCdMgn2 VARCHAR(4),@pmCdMgn3 VARCHAR(4)
,@pmCdMgn4 VARCHAR(4),@pmCdMgn5 VARCHAR(4),@pmTarifMgn1 DECIMAL(14,4),@pmTarifMgn2 DECIMAL(14,4),@pmTarifMgn3 DECIMAL(14,4),@pmTarifMgn4 DECIMAL(14,4),@pmTarifMgn5 DECIMAL(14,4),@pmCdDct1 VARCHAR(4),@pmCdDct2 VARCHAR(4)
,@pmCdDct3 VARCHAR(4),@pmCdDct4 VARCHAR(4),@pmCdDct5 VARCHAR(4),@pmTarifDct1 DECIMAL(14,4),@pmTarifDct2 DECIMAL(14,4),@pmTarifDct3 DECIMAL(14,4),@pmTarifDct4 DECIMAL(14,4),@pmTarifDct5 DECIMAL(14,4)
,@pmCdMon1 VARCHAR(5),@pmCdMon2 VARCHAR(5),@pmCdMon3 VARCHAR(5),@pmCdMon4 VARCHAR(5),@pmCdMon5 VARCHAR(5),@pmIdCiaLta CHAR(2),@pmSobretasa  DECIMAL(14,4), @pmImpGlobal  DECIMAL(14,4), @pmSoldicom  DECIMAL(14,4)
, @pmIvaComb  DECIMAL(14,4), @pmPrecioRef  DECIMAL(14,4), @pmCostoRec  DECIMAL(14,4)
 AS 
 INSERT INTO LogPreciosDet (IdLog,IdCia,Item,IdProducto,DescripProd,VrCosto,VrCostPmd,PrecioAnt1,PrecioAnt2,PrecioAnt3,PrecioAnt4,PrecioAnt5,Precio1,Precio2,Precio3,Precio4,Precio5,IvaInc1,IvaInc2,IvaInc3,IvaInc4,IvaInc5,TarifIva,BaseMgn1,BaseMgn2,BaseMgn3
,BaseMgn4,BaseMgn5,CdMgn1,CdMgn2,CdMgn3,CdMgn4,CdMgn5,TarifMgn1,TarifMgn2,TarifMgn3,TarifMgn4,TarifMgn5,CdDct1,CdDct2,CdDct3,CdDct4,CdDct5,TarifDct1,TarifDct2,TarifDct3,TarifDct4,TarifDct5
,CdMon1,CdMon2,CdMon3,CdMon4,CdMon5,IdCiaLta,Sobretasa, ImpGlobal, Soldicom, IvaComb, PrecioRef, CostoRec)
 VALUES (@pmIdLog,@pmIdCia,@pmItem,@pmIdProducto,@pmDescripProd,@pmVrCosto,@pmVrCostPmd,@pmPrecioAnt1,@pmPrecioAnt2,@pmPrecioAnt3,@pmPrecioAnt4,@pmPrecioAnt5,@pmPrecio1,@pmPrecio2,@pmPrecio3,@pmPrecio4,@pmPrecio5
,@pmIvaInc1,@pmIvaInc2,@pmIvaInc3,@pmIvaInc4,@pmIvaInc5,@pmTarifIva,@pmBaseMgn1,@pmBaseMgn2,@pmBaseMgn3,@pmBaseMgn4,@pmBaseMgn5,@pmCdMgn1,@pmCdMgn2,@pmCdMgn3,@pmCdMgn4,@pmCdMgn5,@pmTarifMgn1,@pmTarifMgn2
,@pmTarifMgn3,@pmTarifMgn4,@pmTarifMgn5,@pmCdDct1,@pmCdDct2,@pmCdDct3,@pmCdDct4,@pmCdDct5,@pmTarifDct1,@pmTarifDct2,@pmTarifDct3,@pmTarifDct4,@pmTarifDct5,@pmCdMon1,@pmCdMon2,@pmCdMon3,@pmCdMon4,@pmCdMon5,@pmIdCiaLta
,@pmSobretasa, @pmImpGlobal, @pmSoldicom, @pmIvaComb, @pmPrecioRef,@pmCostoRec)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryLogPreciosDet @pmIdLog INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT IdLog,IdCia,Item,IdProducto,DescripProd,VrCosto,VrCostPmd,PrecioAnt1,PrecioAnt2,PrecioAnt3
,PrecioAnt4,PrecioAnt5,Precio1,Precio2,Precio3,Precio4,Precio5,IvaInc1,IvaInc2,IvaInc3,IvaInc4,IvaInc5
,TarifIva,BaseMgn1,BaseMgn2,BaseMgn3,BaseMgn4,BaseMgn5,CdMgn1,CdMgn2,CdMgn3,CdMgn4,CdMgn5
,TarifMgn1,TarifMgn2,TarifMgn3,TarifMgn4,TarifMgn5,CdDct1,CdDct2,CdDct3,CdDct4,CdDct5,TarifDct1,TarifDct2,TarifDct3,TarifDct4,TarifDct5
,CdMon1,CdMon2,CdMon3,CdMon4,CdMon5,IdCiaLta,Sobretasa, ImpGlobal, Soldicom, IvaComb, PrecioRef, CostoRec
 FROM LogPreciosDet
 WHERE IdLog=@pmIdLog AND IdCia=@pmIdCia 
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryLogPreciosDetLta @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmTipo VARCHAR(10)=Null,@pmEstado INT=Null,@pmIdLogIni INT=Null,@pmIdLogFin INT=Null
 AS 
 SELECT L.IdLog AS Id_log,L.IdCia AS CdCia,Fecha,Tipo,Listas,SimbTarif,VrTarifa,BaseTarfa,FecProg,IdCiaCie,Estado,IdUsuario,Nombre,Observacion
--Detalle de actualización
,Item,IdProducto,DescripProd,VrCosto,VrCostPmd,PrecioAnt1,PrecioAnt2,PrecioAnt3
,PrecioAnt4,PrecioAnt5,Precio1,Precio2,Precio3,Precio4,Precio5,IvaInc1,IvaInc2,IvaInc3,IvaInc4,IvaInc5
,TarifIva,BaseMgn1,BaseMgn2,BaseMgn3,BaseMgn4,BaseMgn5,CdMgn1,CdMgn2,CdMgn3,CdMgn4,CdMgn5
,TarifMgn1,TarifMgn2,TarifMgn3,TarifMgn4,TarifMgn5,CdDct1,CdDct2,CdDct3,CdDct4,CdDct5,TarifDct1,TarifDct2,TarifDct3,TarifDct4,TarifDct5
,IdCiaLta,TimeSys,FecUpdate,Sobretasa, ImpGlobal, Soldicom, IvaComb, PrecioRef,CostoRec --CdMon1,CdMon2,CdMon3,CdMon4,CdMon5
 FROM LogPrecios AS L INNER JOIN LogPreciosDet AS D ON L.IdLog=D.IdLog AND L.IdCia=D.IdCia
 WHERE Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND Tipo LIKE ISNULL(@pmTipo,'%')  
 AND (Estado>=ISNULL(@pmEstado,-1)  AND Estado<=ISNULL(@pmEstado,2147483647))
 AND L.IdLog BETWEEN  ISNULL(@pmIdLogIni,0)  AND ISNULL(@pmIdLogFin,2147483647)
 ORDER BY L.IdCia,L.IdLog,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryLogPreciosDetDso @pmIdLog INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,IdProducto,DescripProd,IdCiaLta,TarifIva
,PrecioAnt1,Precio1,CdMgn1,TarifMgn1,BaseMgn1,CdDct1,TarifDct1,CdMon1,IvaInc1
,PrecioAnt2,Precio2,CdMgn2,TarifMgn2,BaseMgn2,CdDct2,TarifDct2,CdMon2,IvaInc2
,PrecioAnt3,Precio3,CdMgn3,TarifMgn3,BaseMgn3,CdDct3,TarifDct3,CdMon3,IvaInc3
,PrecioAnt4,Precio4,CdMgn4,TarifMgn4,BaseMgn4,CdDct4,TarifDct4,CdMon4,IvaInc4
,PrecioAnt5,Precio5,CdMgn5,TarifMgn5,BaseMgn5,CdDct5,TarifDct5,CdMon5,IvaInc5
,VrCosto,VrCostPmd,Sobretasa,ImpGlobal,Soldicom,IvaComb,PrecioRef,CostoRec
 FROM LogPreciosDet
 WHERE IdLog=@pmIdLog AND IdCia=@pmIdCia 
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO