IF  EXISTS( SELECT  1 FROM  SYS.OBJECTS 	WHERE  NAME LIKE 'P_ATUALIZA_STS_PROD_EM_MASSA' AND TYPE = 'P' )
BEGIN
	DROP PROCEDURE P_ATUALIZA_STS_PROD_EM_MASSA
END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create Procedure P_ATUALIZA_STS_PROD_EM_MASSA
AS
SET NOCOUNT ON;

if OBJECT_ID( 'tempdb..#tmpProdStatus' ) is not null drop table #tmpProdStatus

CREATE TABLE #tmpProdStatus(
			 CD_EMP int
			,CD_PROD int)
---------------------------------------------------------------------------------------------------
 BEGIN
	INSERT INTO #tmpProdStatus 
	SELECT
		distinct
		P.CD_EMP
		,P.CD_PROD
	FROM EST_PROD P 
		INNER JOIN 
		(SELECT
			CD_EMP
			,CD_PROD 
			,SUM(ISNULL(QT_EST,0) + ISNULL(QT_EST_FLUT,0) + ISNULL(QT_EST_EM_TERC,0) + ISNULL(QT_EST_DE_TERC,0) + ISNULL(QT_EST_DEPOSITOS,0)) AS QTDE_EST			
		FROM EST_PROD_CPL 	
		GROUP BY CD_EMP,CD_PROD) AS ESTOQUE ON
		ESTOQUE.CD_EMP=P.CD_EMP AND
		ESTOQUE.CD_PROD=P.CD_PROD

		INNER JOIN EST_PROD_MOTIVO_STS MTV ON
		P.CD_EMP=MTV.CD_EMP AND
		P.CD_MOTIVO_STS = MTV.CD_MOTIVO_STS

	WHERE P.CD_EMP = 1
		AND P.STS_PROD = 2
		AND MTV.FLAG_INATIVA_PROD_EST_ZERADO = 1
		AND ESTOQUE.QTDE_EST = 0  
		--AND P.CD_PROD IN (6,1530)

	BEGIN
	UPDATE P 
		SET
			STS_PROD = 1
		FROM
			#tmpProdStatus T
			INNER JOIN EST_PROD P ON		 
			T.CD_EMP = P.CD_EMP AND 
			T.CD_PROD = P.CD_PROD
		WHERE P.CD_EMP = 1; 
	END

END
