IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'SP_Stk_Consulta_Costos') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [SP_Stk_Consulta_Costos]
END
GO


CREATE PROCEDURE [dbo].[SP_Stk_Consulta_Costos] @Id_Fam INT, @Con_Stock TINYINT, @id_Prov INT
                                       , @Descrip CHAR(100), @pId_Producto  CHAR(14)
									   , @Con_Stock_Nega TINYINT
									   , @genero_Producteca TINYINT = null

AS
SET NOCOUNT ON

-------OJO!!!! Siempre que se agrega o elimina una variable aca de entrada, hay que modificar la estructura de la tabla "Producteca_Stock" que usa el Stored: SPJ_Producteca_Generar_Arch_Stock								 						

/*Declaracion Variables Locales*/
DECLARE @Mensaje			VARCHAR(100)
		,@UltError			INT
		,@Id_Trans			INT
		,@ID_PRODUCTO		VARCHAR(14)
		,@Importe			DECIMAL(16,2)
		,@Email_Cob			VARCHAR(256)
		,@Id_Trans_Rec		INT
		,@CursorOpen        INT
		,@Flag_Cbte_Tipo	SMALLINT
		,@Periodo           DATE 
		,@Id_sucursal	    SMALLINT
		,@Existe      	    SMALLINT
		,@DepFisico         CHAR(3)
		,@DepLogico         CHAR(3)
		,@Cantidad			DECIMAL(16,0)
		,@STK_10            INT
		,@Fecha             DATETIME


  SELECT ID_PRODUCTO INTO #STOCK FROM PRODUCTOS WHERE 1=0
		
  ALTER Table #STOCK  ADD
				DESCRIPCION	   	VARCHAR(100),
				COSTO   	   	DECIMAL(16,2),
				COSTO_REAL      DECIMAL(16,2),
				STK_OC	   	    INT,	
				STK_1   	   	INT,
				STK_2   	   	INT,	
				STK_3   	   	INT,	
				STK_4   	   	INT,
		    	STK_5   	   	INT,
			   	STK_6   	   	INT,
			   	STK_7   	   	INT,
			   	STK_8   	   	INT,
			   	STK_9   	   	INT,
				STK_10          INT,
				STK_FULLFI   	INT,
				STK_VIRTUAL   	INT,
			 	STK_SRV   	   	INT,
			 	STK_SRV_EXT	   	INT,
				TOTAL    	   	INT,
				FAMILIA_ID      INT,
				DESC_FAM VARCHAR(50),
				FECHA    DATETIME,
				FECHA_COSTO DATETIME,
				RAZON_SOCIAL VARCHAR(100)

BEGIN 

 

IF RTRIM(LTRIM(isnull(@descrip,''))) = '' 
   BEGIN
    set @descrip = '% %'
   END
ELSE
   BEGIN
    set @descrip = '%'+ltrim(rtrim(@Descrip))+'%' 
   END   
 
 --PRINT(@descrip)


IF ISNULL(@Id_Fam,0) = 0 AND ISNULL(@Id_Prov,0) = 0
	BEGIN
    INSERT INTO #STOCK
    SELECT P.ID_PRODUCTO AS ID_PRODUCTO ,SUBSTRING(P.DESCRIPCION_DETALLADA,1,100) AS DESCRIPCION ,
			P.COSTO * (1 + (I.Porc1 / 100)) AS COSTO ,
			P.Costo_Rentabilidad * (1 + (I.Porc1 / 100)) AS COSTO_REAL,
 		    0 AS    STK_OC,
		    0 AS    STK_1,
			0 AS	STK_2,
			0 AS	STK_3,
			0 AS	STK_4,
		    0 AS 	STK_5,
			0 AS   	STK_6 ,
			0 AS   	STK_7 ,
			0 AS   	STK_8 ,
			0 AS   	STK_9 ,
			0 AS   	STK_10 ,
			0 AS	STK_FULLFI,
		    ISNULL(P.COMPRA_MAX,0) AS	STK_VIRTUAL,
			0 AS 	STK_SRV ,
			0 AS 	STK_SRV_EXT,
			0 AS	TOTAL,
			P.Id_Familia  AS  ID_FAMILIA,
			F.DESCRIPCION  AS  DESC_FAM,
			GETDATE() AS FECHA,
			P.Fecha_Costo AS  FECHA_COSTO,
			PR.razon_social AS RAZON_SOCIAL 
			FROM Productos P, Prod_Familias F,Productos_TasaIva I,Proveedores PR 
	   WHERE P.Activo = 1
	   AND P.Vendible = 1
	   AND P.Maneja_Stock = 1
	   AND P.ID_FAMILIA = F.ID_FAMILIA
       AND I.Id_Iva = P.Id_Iva
	   AND ISNULL(PR.Id_Proveedor,0) = ISNULL(P.Id_Proveedor,0)
	   AND P.Descripcion_Detallada like rtrim(ltrim(@descrip))
	   AND P.Id_Familia NOT IN (1000,1020,93)
    END
  ELSE
    BEGIN
	IF ISNULL(@Id_Prov,0) = 0
	   BEGIN 
	      IF ISNULL(@Id_Fam,0) <> 0
	      BEGIN
		    INSERT INTO #STOCK
            SELECT P.ID_PRODUCTO AS ID_PRODUCTO ,SUBSTRING(P.DESCRIPCION_DETALLADA,1,100) AS DESCRIPCION ,
			P.COSTO * (1 + (I.Porc1 / 100)) AS COSTO ,
			P.Costo_Rentabilidad * (1 + (I.Porc1 / 100)) AS COSTO_REAL,
 		    0 AS    STK_OC,
		    0 AS    STK_1,
			0 AS	STK_2,
			0 AS	STK_3,
			0 AS	STK_4,
		    0 AS 	STK_5,
			0 AS   	STK_6 ,
			0 AS   	STK_7 ,
			0 AS   	STK_8 ,
			0 AS   	STK_9 ,
			0 AS   	STK_10 ,
			0 AS	STK_FULLFI,
		    ISNULL(P.COMPRA_MAX,0) AS	STK_VIRTUAL,
			0 AS 	STK_SRV ,
			0 AS 	STK_SRV_EXT,
			0 AS	TOTAL,
			P.Id_Familia  AS  ID_FAMILIA,
			F.DESCRIPCION  AS  DESC_FAM,
			GETDATE() AS FECHA,
			P.Fecha_Costo AS  FECHA_COSTO,
			PR.razon_social AS RAZON_SOCIAL 
			FROM Productos P, Prod_Familias F,Productos_TasaIva I,Proveedores PR 
				   WHERE P.Activo = 1
				   AND P.Vendible = 1
				   AND P.Maneja_Stock = 1
				   AND P.ID_FAMILIA = F.ID_FAMILIA
			   AND P.Id_Familia = @Id_Fam
			   AND I.Id_Iva = P.Id_Iva
		  	   AND ISNULL(PR.Id_Proveedor,0) = ISNULL(P.Id_Proveedor,0)
			   AND P.Descripcion_Detallada like  rtrim(ltrim(@descrip))
			   AND P.Id_Familia NOT IN (1000,1020,93)
	      END
	   END
	 ELSE
	   BEGIN
       IF ISNULL(@Id_Prov,0) <> 0 and iSNULL(@Id_Fam,0) <> 0
		  BEGIN
		  INSERT INTO #STOCK
			SELECT P.ID_PRODUCTO AS ID_PRODUCTO ,SUBSTRING(P.DESCRIPCION_DETALLADA,1,100) AS DESCRIPCION ,
		        	P.COSTO * (1 + (I.Porc1 / 100)) AS COSTO ,
		        	P.Costo_Rentabilidad * (1 + (I.Porc1 / 100)) AS COSTO_REAL,
 					0 AS    STK_OC,
					0 AS    STK_1,
					0 AS	STK_2,
					0 AS	STK_3,
					0 AS	STK_4,
					0 AS 	STK_5,
					0 AS   	STK_6 ,
					0 AS   	STK_7 ,
					0 AS   	STK_8 ,
					0 AS   	STK_9 ,
					0 AS   	STK_10 ,
					0 AS	STK_FULLFI,
					ISNULL(P.COMPRA_MAX,0) AS	STK_VIRTUAL,
					0 AS 	STK_SRV ,
					0 AS 	STK_SRV_EXT,
					0 AS	TOTAL,
					P.Id_Familia  AS  ID_FAMILIA,
					F.DESCRIPCION  AS  DESC_FAM,
					GETDATE() AS FECHA,
					P.Fecha_Costo AS  FECHA_COSTO,
					PR.razon_social AS RAZON_SOCIAL 
					FROM Productos P,Prod_Familias F,Productos_TasaIva I,Proveedores PR 
				   WHERE P.Activo = 1
				   AND P.Vendible = 1
				   AND P.Maneja_Stock = 1
				   AND P.ID_FAMILIA = F.ID_FAMILIA
				   AND P.Id_Familia = @Id_Fam
				   AND I.Id_Iva = P.Id_Iva
				   AND P.Id_Proveedor = @Id_PROV
				   AND ISNULL(PR.Id_Proveedor,0) = ISNULL(P.Id_Proveedor,0)
				   AND P.Descripcion_Detallada like rtrim(ltrim(@descrip))
				   AND P.Id_Familia NOT IN (1000,1020,93)
		 END
		ELSE
		 BEGIN
		 INSERT INTO #STOCK
		SELECT P.ID_PRODUCTO AS ID_PRODUCTO ,SUBSTRING(P.DESCRIPCION_DETALLADA,1,100) AS DESCRIPCION ,
		        P.COSTO * (1 + (I.Porc1 / 100)) AS COSTO ,
		        P.Costo_Rentabilidad * (1 + (I.Porc1 / 100)) AS COSTO_REAL,
 				0 AS    STK_OC,
				0 AS    STK_1,
				0 AS	STK_2,
				0 AS	STK_3,
				0 AS	STK_4,
				0 AS 	STK_5,
				0 AS   	STK_6 ,
				0 AS   	STK_7 ,
				0 AS   	STK_8 ,
				0 AS   	STK_9 ,
				0 AS   	STK_10 ,
				0 AS	STK_FULLFI,
				ISNULL(P.COMPRA_MAX,0) AS	STK_VIRTUAL,
				0 AS 	STK_SRV ,
				0 AS 	STK_SRV_EXT,
				0 AS	TOTAL,
				P.Id_Familia  AS  ID_FAMILIA,
				F.DESCRIPCION  AS  DESC_FAM,
				GETDATE() AS FECHA,
				P.Fecha_Costo AS  FECHA_COSTO,
				PR.razon_social AS RAZON_SOCIAL 
				FROM Productos P, Prod_Familias F,Productos_TasaIva I,Proveedores PR 
				   WHERE P.Activo = 1
				   AND P.Vendible = 1
				   AND P.Maneja_Stock = 1
				   AND P.ID_FAMILIA = F.ID_FAMILIA
				   AND I.Id_Iva = P.Id_Iva
				   AND P.Id_Proveedor = @Id_PROV
			       AND ISNULL(PR.Id_Proveedor,0) = ISNULL(P.Id_Proveedor,0)
				   AND P.Descripcion_Detallada like  rtrim(ltrim(@descrip))
				   AND P.Id_Familia NOT IN (1000,1020,93)
		  END
	  END
   END

   DECLARE TagCbtesCursor CURSOR FOR  

       select I.ID_PRODUCTO,C.Id_Dep_Fisico,C.Id_Dep_Logico,ISNULL(sum(ISNULL(i.Cantidad,0)*isnull(t.compromete_Stock,1)),0) AS CANTIDAD
		   from cbtes c, cbtes_tipos t, cbtes_items i, #STOCK S
		   where c.id_tipo_cbte  = t.id_tipo_cbte
		   and I.Id_Trans = c.Id_Trans
		   And c.id_Estado_Cbte not in (0,3)
		   and I.id_Producto = S.Id_Producto 
		   And t.compromete_Stock in (1,-1)
		   AND C.Id_Dep_Fisico = '35'
		   AND C.Id_Dep_Logico = '35'
		   AND I.FLAG_QA = 'A'
		   group by I.ID_PRODUCTO,C.Id_Dep_Fisico,C.Id_Dep_Logico

	OPEN TagCbtesCursor

	FETCH NEXT FROM TagCbtesCursor INTO @ID_PRODUCTO,@DepFisico,@DepLogico,@Cantidad
	SET @CursorOpen = 0	
	SET @UltError = 0
	WHILE @@FETCH_STATUS=0 AND @UltError  = 0
	BEGIN
	       IF @DepFisico = '1' AND @DepLogico = '1'   Update #STOCK SET STK_1 = STK_1 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '2' AND @DepLogico = '2'   Update #STOCK SET STK_2 = STK_2 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '3' AND @DepLogico = '3'   Update #STOCK SET STK_3 = STK_3 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '4' AND @DepLogico = '4'   Update #STOCK SET STK_4 = STK_4 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '5' AND @DepLogico = '5'   Update #STOCK SET STK_5 = STK_5 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '6' AND @DepLogico = '6'   Update #STOCK SET STK_6 = STK_6 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO
	       IF @DepFisico = '7' AND @DepLogico = '7'   Update #STOCK SET STK_7 = STK_7 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '8' AND @DepLogico = '8'   Update #STOCK SET STK_8 = STK_8 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '9' AND @DepLogico = '9'   Update #STOCK SET STK_9 = STK_9 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '15' AND @DepLogico = '15' Update #STOCK SET STK_9 = STK_9 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '19' AND @DepLogico = '19' Update #STOCK SET STK_5 = STK_5 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '22' AND @DepLogico = '22' Update #STOCK SET STK_3 = STK_3 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '26' AND @DepLogico = '26' Update #STOCK SET STK_6 = STK_6 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '35' AND @DepLogico = '35' Update #STOCK SET STK_10 = STK_10 + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '10' AND @DepLogico = '10' Update #STOCK SET STK_FULLFI = STK_FULLFI + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO    
 
   	 	FETCH NEXT FROM TagCbtesCursor INTO @ID_PRODUCTO,@DepFisico,@DepLogico,@Cantidad

	END
	CLOSE TagCbtesCursor
	DEALLOCATE TagCbtesCursor

    DECLARE TagCbtesCursorRES CURSOR FOR  



       select I.ID_PRODUCTO,C.Id_Dep_Fisico,C.Id_Dep_Logico,ISNULL(sum(ISNULL(i.Cantidad,0)*isnull(t.Compromete_Stock_Res,1)),0) AS CANTIDAD
		   from cbtes c, cbtes_tipos t, cbtes_items i, #STOCK S
		   where c.id_tipo_cbte  = t.id_tipo_cbte
		   and I.Id_Trans = c.Id_Trans
		   And c.id_Estado_Cbte not in (0,3)
		   and I.id_Producto = S.Id_Producto 
		   And t.Compromete_Stock_Res in (1,-1)
		   AND I.FLAG_QA = 'A'
		   AND C.Id_Dep_Fisico = '35'
		   AND C.Id_Dep_Logico = '35'
		   group by I.ID_PRODUCTO,C.Id_Dep_Fisico,C.Id_Dep_Logico 
		   having sum(i.Cantidad*isnull(t.Compromete_Stock_Res,1)) > 0


	OPEN TagCbtesCursorRES

	FETCH NEXT FROM TagCbtesCursorRES INTO @ID_PRODUCTO,@DepFisico,@DepLogico,@Cantidad
	SET @CursorOpen = 0	
	SET @UltError = 0
	WHILE @@FETCH_STATUS=0 AND @UltError  = 0
	BEGIN
	       IF @DepFisico = '1' AND @DepLogico = '1'   Update #STOCK SET STK_1 = STK_1 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '2' AND @DepLogico = '2'   Update #STOCK SET STK_2 = STK_2 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '3' AND @DepLogico = '3'   Update #STOCK SET STK_3 = STK_3 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '4' AND @DepLogico = '4'   Update #STOCK SET STK_4 = STK_4 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '5' AND @DepLogico = '5'   Update #STOCK SET STK_5 = STK_5 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '6' AND @DepLogico = '6'   Update #STOCK SET STK_6 = STK_6 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO
	       IF @DepFisico = '7' AND @DepLogico = '7'   Update #STOCK SET STK_7 = STK_7 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '8' AND @DepLogico = '8'   Update #STOCK SET STK_8 = STK_8 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '9' AND @DepLogico = '9'   Update #STOCK SET STK_9 = STK_9 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '15' AND @DepLogico = '15' Update #STOCK SET STK_9 = STK_9 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '19' AND @DepLogico = '19' Update #STOCK SET STK_5 = STK_5 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '22' AND @DepLogico = '22' Update #STOCK SET STK_3 = STK_3 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '26' AND @DepLogico = '26' Update #STOCK SET STK_6 = STK_6 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '35' AND @DepLogico = '35' Update #STOCK SET STK_10 = STK_10 - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 
	       IF @DepFisico = '10' AND @DepLogico = '10' Update #STOCK SET STK_FULLFI = STK_FULLFI - @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO   

   		FETCH NEXT FROM TagCbtesCursorRES INTO @ID_PRODUCTO,@DepFisico,@DepLogico,@Cantidad

	END
	CLOSE TagCbtesCursorRES
	DEALLOCATE TagCbtesCursorRES

	/*FIN */

   UPDATE #STOCK SET TOTAL = ISNULL(STK_1,0) + 
								ISNULL(STK_3,0) +
								ISNULL(STK_5,0) +
								ISNULL(STK_6,0) +
								ISNULL(STK_9,0) +
								ISNULL(STK_10,0)


END 

IF @pId_Producto <> '_'
   BEGIN
		SELECT * FROM #STOCK WHERE ID_PRODUCTO = @pId_producto  
   END
 ELSE
   BEGIN
		IF ISNULL(@Con_Stock,0) = 0   
			SELECT * FROM #STOCK  ORDER BY DESCRIPCION
		ELSE
		 BEGIN
		    IF ISNULL(@Con_Stock_Nega,0) = 0   
			    SELECT * FROM #STOCK WHERE TOTAL <> 0  ORDER BY DESCRIPCION
			  ELSE
			    SELECT * FROM #STOCK WHERE TOTAL < 0  ORDER BY DESCRIPCION
	     END
   END 

   
  DROP TABLE #STOCK

 


 /* 

 exec [SP_Stk_Consulta_Costos] 0,1,0,'','_',0

@Id_Fam INT, @Con_Stock TINYINT, @id_Prov INT
                                       , @Descrip CHAR(100), @pId_Producto  CHAR(14)
									   , @Con_Stock_Nega TINYINT
                                       

 FIN */



