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

create PROCEDURE [dbo].[CP_Recalcular_Stock_Disponible] 
AS
BEGIN
 
        select id_producto,
                          Stk_Fisico,
                          Stk_Disponible,
						  Stk_Pedidos,
						  Stk_Ordenes,
						  Stk_Reserva,
						  Stk_Cuarentena,
						  Stk_Lineas,
						  Stk_Rechazado,
						  STk_Externo
						  into #Stock from productos where id_producto = ''

         delete #Stock
		   
  Declare @id_Producto char(14)
  Declare @Stock Decimal(17,4)
  Declare @existe SmallInt

--Calculo Stk_Fisico
  Declare Cursor_RecStock Cursor for

		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_Stock) as Stock  FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T  
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND isnull(i.id_producto,'') <> ''  
		AND (I.Fecha_QA < getdate()+1  OR  I.Fecha_QA IS NULL ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO  having SUM(I.Cantidad*T.Compromete_Stock) <> 0
	
	OPEN Cursor_RecStock

	FETCH NEXT FROM Cursor_RecStock
	INTO @Id_Producto,@Stock

	WHILE @@FETCH_STATUS = 0
	BEGIN
	    set @existe = (Select count(*) from #Stock where id_producto = @Id_Producto)

    	if @existe <> 0
	        	begin
				  update #Stock set stk_Fisico = @Stock where  Id_producto = @Id_Producto 
				end
			  Else
				Begin
				  insert into #stock (Id_Producto,stk_Fisico) values (@id_Producto,@Stock)
                End

	 	FETCH NEXT FROM Cursor_RecStock
		INTO @Id_Producto, @Stock
	END

	CLOSE Cursor_RecStock
	DEALLOCATE Cursor_RecStock



--Calculo Stk_Pendiente
  Declare Cursor_RecStock Cursor for

		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_Stock_Res)  as Stock FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T  
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock_Res in (1,-1) 
	    AND isnull(i.id_producto,'') <> ''  
		AND (I.Fecha_QA < getdate()+1  OR  I.Fecha_QA IS NULL ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO having SUM(I.Cantidad*T.Compromete_Stock) > 0
	
	OPEN Cursor_RecStock

	FETCH NEXT FROM Cursor_RecStock
	INTO @Id_Producto,@Stock

	WHILE @@FETCH_STATUS = 0
	BEGIN
	    set @existe = (Select count(*) from #Stock where id_producto = @Id_Producto)

    	if @existe <> 0
	        	begin
				  update #Stock set Stk_Pedidos = @Stock where  Id_producto = @Id_Producto 
				end
			  Else
				Begin
				  insert into #stock (Id_Producto,Stk_Pedidos) values (@id_Producto,@Stock)
                End

	 	FETCH NEXT FROM Cursor_RecStock
		INTO @Id_Producto, @Stock
	END

	CLOSE Cursor_RecStock
	DEALLOCATE Cursor_RecStock




 update productos set Stk_fisico = 0,Stk_Disponible = 0,Stk_Pedidos=0 

 update productos set Stk_fisico = S.Stk_fisico,Stk_Pedidos = S.Stk_Pedidos, Stk_Disponible = S.Stk_fisico - S.Stk_Pedidos 
        from productos p, #stock S where p.id_producto = s.Id_Producto

  drop table #Stock

  END


--  [CP_Recalcular_Stock_Disponible]