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

CREATE PROCEDURE [dbo].[CP_Stk_Depositos] @Id_Fam INT, @Con_Stock TINYINT, @id_Prov INT
                                       , @Descrip CHAR(100), @pId_Producto  CHAR(14) 

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(150),
				LISTA1          DECIMAL(16,2),
				COSTO   	   	DECIMAL(16,2),
				STK_1   	   	INT,
				STK_2   	   	INT,	
				STK_3   	   	INT,
                STK_PED			INT,	
				TOTAL    	   	INT,
				FAMILIA_ID      INT,
				DESC_FAM VARCHAR(50),
				FECHA    DATETIME,
				FECHA_COSTO DATETIME,
				FECHA_L1 DATETIME,
				RAZON_SOCIAL VARCHAR(100),
				MONEDA CHAR(3)

BEGIN 

 

IF RTRIM(LTRIM(isnull(@descrip,''))) = '' 
   BEGIN
    set @descrip = '% %'
   END
ELSE
   BEGIN
    set @descrip = '%'+ltrim(rtrim(@Descrip))+'%' 
   END   
 
 
IF ISNULL(@Id_Fam,0) = 0 AND ISNULL(@Id_Prov,0) = 0
	BEGIN
    INSERT INTO #STOCK
    SELECT P.ID_PRODUCTO AS ID_PRODUCTO ,P.DESCRIPCION_DETALLADA AS DESCRIPCION ,
			L1.PRECIO  AS LISTA1,
			P.COSTO  AS COSTO ,
		    0 AS    STK_1,
			0 AS	STK_2,
			0 AS	STK_3,
			0 AS    STK_PED,
			0 AS	TOTAL,
			P.Id_Familia  AS  ID_FAMILIA,
			F.DESCRIPCION  AS  DESC_FAM,
			GETDATE() AS FECHA,
			P.Fecha_Costo AS  FECHA_COSTO,
			L1.Fecha_Desde As FECHA_L1,
			PR.razon_social AS RAZON_SOCIAL,
			P.Id_Moneda_C as MONEDA
			FROM Productos P,Prod_Listas_Precios_Items L1,
			     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_PRODUCTO = L1.ID_PRODUCTO
	   AND L1.Id_Lista_Precio = 1
	   AND I.Id_Iva = P.Id_Iva
	   AND PR.Id_Proveedor = P.Id_Proveedor
	   AND P.Descripcion_Detallada like rtrim(Ltrim(@descrip))
    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 ,
					P.DESCRIPCION_DETALLADA AS DESCRIPCION ,
					L1.PRECIO AS LISTA1,
					P.COSTO  AS COSTO ,
					0 AS    STK_1,
					0 AS	STK_2,
					0 AS	STK_3,
					0 AS    STK_PED,
					0 AS	TOTAL,
					P.Id_Familia  AS  ID_FAMILIA,
		         	F.DESCRIPCION  AS  DESC_FAM,
			        GETDATE() AS FECHA,
					P.Fecha_Costo AS  FECHA_COSTO,
		        	L1.Fecha_Desde As FECHA_L1,
			        PR.razon_social AS RAZON_SOCIAL, 
					P.Id_Moneda_C as MONEDA
			   FROM Productos P,Prod_Listas_Precios_Items L1,
			       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_PRODUCTO = L1.ID_PRODUCTO
			   AND L1.Id_Lista_Precio = 1
			   AND P.Id_Familia = @Id_Fam
			   AND I.Id_Iva = P.Id_Iva
		  	   AND PR.Id_Proveedor = P.Id_Proveedor
			   AND P.Descripcion_Detallada like  rtrim(Ltrim(@descrip))
	      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 ,
						P.DESCRIPCION_DETALLADA AS DESCRIPCION ,
						L1.PRECIO  AS LISTA1,
						P.COSTO * (1 + (I.Porc1 / 100)) AS COSTO ,
						0 AS    STK_1,
						0 AS	STK_2,
						0 AS	STK_3,
						0 AS    STK_PED,
						0 AS	TOTAL,
						P.Id_Familia  AS  ID_FAMILIA,
						F.DESCRIPCION  AS  DESC_FAM,
			            GETDATE() AS FECHA,
						P.Fecha_Costo AS   FECHA_COSTO,
						L1.Fecha_Desde As FECHA_L1,
						PR.razon_social AS RAZON_SOCIAL,
						P.Id_Moneda_C as MONEDA
						FROM Productos P,Prod_Listas_Precios_Items L1, 
			             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_PRODUCTO = L1.ID_PRODUCTO
				   AND L1.Id_Lista_Precio = 1
				   AND P.Id_Familia = @Id_Fam
				   AND I.Id_Iva = P.Id_Iva
				   AND P.Id_Proveedor = @Id_PROV
				   AND PR.Id_Proveedor = P.Id_Proveedor
				   AND P.Descripcion_Detallada like rtrim(Ltrim(@descrip))
		 END
		ELSE
		 BEGIN
		 INSERT INTO #STOCK
			  SELECT P.ID_PRODUCTO AS ID_PRODUCTO ,
						P.DESCRIPCION_DETALLADA AS DESCRIPCION ,
						L1.PRECIO AS LISTA1,
						P.COSTO  AS COSTO ,
						0 AS    STK_1,
						0 AS	STK_2,
						0 AS	STK_3,
						0 AS    STK_PED,
						0 AS	TOTAL,
						P.Id_Familia  AS  ID_FAMILIA,
						F.DESCRIPCION  AS  DESC_FAM,
			            GETDATE() AS FECHA,
						P.Fecha_Costo AS   FECHA_COSTO,
					    L1.Fecha_Desde As FECHA_L1,
		             	PR.razon_social AS RAZON_SOCIAL, 
						P.Id_Moneda_C as MONEDA
         		   FROM Productos P,Prod_Listas_Precios_Items L1,
			             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_PRODUCTO = L1.ID_PRODUCTO
				   AND L1.Id_Lista_Precio = 1
				   AND I.Id_Iva = P.Id_Iva
				   AND P.Id_Proveedor = @Id_PROV
			   	   AND PR.Id_Proveedor = P.Id_Proveedor
				   AND P.Descripcion_Detallada like  rtrim(Ltrim(@descrip))
		  END
	  END
   END

   DECLARE TagCbtesCursor CURSOR FOR  

       select I.ID_PRODUCTO,C.Id_Dep_Fisico,C.Id_Dep_Logico,sum(i.Cantidad*isnull(t.compromete_Stock,1)) 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 <> 3
		   and I.id_Producto = S.Id_Producto 
		   And t.compromete_Stock in (1,-1)
		   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 

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

	END
	CLOSE TagCbtesCursor
	DEALLOCATE TagCbtesCursor

    DECLARE TagCbtesCursorRES CURSOR FOR  

    select I.ID_PRODUCTO,sum(ISNULL(i.Cantidad_Saldo,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 <> 3
		   and I.id_Producto = S.Id_Producto 
		   And t.Compromete_Pedido in (1,-1)
		   group by I.ID_PRODUCTO 
		   having sum(i.Cantidad_Saldo) > 0

	OPEN TagCbtesCursorRES

	FETCH NEXT FROM TagCbtesCursorRES INTO @ID_PRODUCTO,@Cantidad
	SET @CursorOpen = 0	
	SET @UltError = 0
	WHILE @@FETCH_STATUS=0 AND @UltError  = 0
	BEGIN
	       Update #STOCK SET STK_PED = STK_PED + @Cantidad WHERE ID_PRODUCTO =  @ID_PRODUCTO 

   		   FETCH NEXT FROM TagCbtesCursorRES INTO @ID_PRODUCTO,@Cantidad

	END
	CLOSE TagCbtesCursorRES
	DEALLOCATE TagCbtesCursorRES



--		/*FIN */

   UPDATE #STOCK SET TOTAL = ISNULL(STK_1,0) + ISNULL(STK_2,0) + ISNULL(STK_3,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
			SELECT * FROM #STOCK --WHERE TOTAL > 0  ORDER BY DESCRIPCION
   END 
 
 DROP TABLE #STOCK


 /* z_stock_vic
  select * from z_stock_vic
 exec cP_Stk_Depositos 0,0,0,'','_'

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

 FIN */
 



