ALTER TABLE ProdCombo ADD ExentoSob BIT DEFAULT(0) NOT NULL
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsProdCombo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsProdCombo]
GO

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsProdCombo @pmIdProducto VARCHAR(16),@pmItem INT,@pmIdProdBas VARCHAR(16),@pmCant DECIMAL(14,4)
,@pmCdBodega VARCHAR(4),@pmCdTanq VARCHAR(4),@pmFacMezcla VARCHAR(10),@pmExentoSob BIT
 AS 
 INSERT INTO ProdCombo (IdProducto,Item,IdProdBas,Cant,CdBodega,CdTanq,FacMezcla,ExentoSob)
 VALUES (@pmIdProducto,@pmItem,@pmIdProdBas,@pmCant,@pmCdBodega,@pmCdTanq,@pmFacMezcla,@pmExentoSob)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryProdCombo @pmIdProducto VARCHAR(16),@pmItem INT
 AS 
 SELECT IdProducto,Item,IdProdBas,Cant,FacMezcla,CdBodega,CdTanq,ExentoSob
 FROM ProdCombo
 WHERE IdProducto=@pmIdProducto
 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 ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryProdComboLta @pmIdProducto VARCHAR(16)=Null,@pmIdProdBas VARCHAR(16)=Null
 AS 
 SELECT PC.IdProducto AS CdProd,P.DescripProd AS Prodcto,Item,IdProdBas,PB.DescripProd AS ProdBase
,Cant,FacMezcla,CdBodega,Bodega,CdTanq,P.Tanques AS ProdTanques,PB.Tanques AS ProBasTanq,ExentoSob
 FROM ProdCombo AS PC INNER JOIN ProdMcias AS P ON PC.IdProducto=P.IdProducto
 INNER JOIN ProdMcias AS PB ON PC.IdProdBas=PB.IdProducto
 LEFT JOIN  Bodegas AS B ON PC.CdBodega=B.IdBodega
 WHERE PC.IdProducto LIKE ISNULL(@pmIdProducto,'%')  AND IdProdBas LIKE ISNULL(@pmIdProdBas,'%')
 ORDER BY P.DescripProd,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryProdComboPro @pmIdProducto VARCHAR(16)
 AS 
 SELECT C.IdProducto AS CdProducto,Item,IdProdBas,Cant,FacMezcla,CdBodega,CdTanq
,DescripProd,TipoRef,IdSubgrupo,IdUnd,IdBodega,IdUbic
,VrCostPmd,Seriales,Lotes,Combo,Tanques,Precio1,Precio2,Precio3,Precio4,Precio5,ExentoSob
 FROM ProdCombo AS C INNER JOIN ProdMcias AS P ON C.IdProdBas=P.IdProducto
 WHERE C.IdProducto=@pmIdProducto
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpProdCombo @pmIdProducto VARCHAR(16),@pmItem INT,@pmIdProdBas VARCHAR(16),@pmCant DECIMAL(14,4)
,@pmCdBodega VARCHAR(4),@pmCdTanq VARCHAR(4),@pmFacMezcla VARCHAR(10),@pmExentoSob BIT
 AS 
 UPDATE ProdCombo SET IdProdBas=@pmIdProdBas,Cant=@pmCant,CdBodega=@pmCdBodega,CdTanq=@pmCdTanq
,FacMezcla=@pmFacMezcla,ExentoSob=@pmExentoSob
 WHERE IdProducto=@pmIdProducto AND Item=@pmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO