
/****** Object:  StoredProcedure [dbo].[P_GERA_EST_ARV_GRP_RESSUPRIMENTO_NAO_EXECUTAR_FILIAL_CRONOGRAMA]    Script Date: 06/16/2012 13:50:42 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_GERA_EST_ARV_GRP_RESSUPRIMENTO_NAO_EXECUTAR_FILIAL_CRONOGRAMA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_GERA_EST_ARV_GRP_RESSUPRIMENTO_NAO_EXECUTAR_FILIAL_CRONOGRAMA]
GO

CREATE PROCEDURE [dbo].[P_GERA_EST_ARV_GRP_RESSUPRIMENTO_NAO_EXECUTAR_FILIAL_CRONOGRAMA] 
AS  
	BEGIN  
		DECLARE @CONT INT    
		    
		CREATE TABLE #BALAN			(CD_EMP INT, CD_FILIAL INT,DT_BALAN DATETIME,CD_USU INT, DT_CAD DATETIME)    
		CREATE TABLE #TMP_RESSUP    (CD_EMP INT, CD_FILIAL INT,DT_N_RESSUP DATETIME,CD_USU INT, DT_CAD DATETIME)         


		INSERT INTO #BALAN    
		SELECT  
			 CD_EMP,   
			 CD_FILIAL,     
			 DT_BALAN,    
			 CD_USU,    
			 DT_CAD    
		FROM     
			EST_PROD_BALAN_CRONOGRAMA      
		WHERE     
			DT_BALAN >= DBO.F_HOJE()     
		     
		SET @CONT = (SELECT TOP 1 CONVERT(INT, VALOR )    
					 FROM     
						PRC_EMP_CONFIG     
					 WHERE     
						CD_CHAVE = 'QTDE_DIAS_RESSUPRIMENTO_CRONOGRAMA_BALANCO'    
						AND CD_EMP=1)    
		    
		    
		WHILE @CONT >=0    
			BEGIN    
				 INSERT INTO #TMP_RESSUP    
				 SELECT  
					  CD_EMP,	   
					  CD_FILIAL,     
					  DATEADD(DAY, @CONT*-1, DT_BALAN) AS DT_N_RESSUP ,    
					  CD_USU,    
					  DT_CAD     
				 FROM     
					  #BALAN B   
				 WHERE 
					  NOT EXISTS (SELECT C.CD_EMP 
								  FROM #TMP_RESSUP C
								  WHERE C.CD_EMP	   = B.CD_EMP
									   AND C.CD_FILIAL = B.CD_FILIAL
									   AND C.DT_N_RESSUP  = DATEADD(DAY, @CONT*-1, B.DT_BALAN))  
				    
				 SET @CONT =@CONT -1    
			END    


		INSERT INTO EST_ARV_GRP_RESSUPRIMENTO_NAO_EXECUTAR_FILIAL     
		SELECT      
			 A.CD_EMP,    
			 A.CD_FILIAL,    
			 A.DT_N_RESSUP,
			 A.CD_USU,    
			 A.DT_CAD     
		FROM     
			 #TMP_RESSUP A    
			 LEFT JOIN EST_ARV_GRP_RESSUPRIMENTO_NAO_EXECUTAR_FILIAL F ON     
				 A.CD_EMP = F.CD_EMP AND
				 A.CD_FILIAL  = F.CD_FILIAL AND    
				 A.DT_N_RESSUP  = F.DT_NAO_RESSUP  
		WHERE    
			 F.CD_EMP IS NULL    
		ORDER BY CD_FILIAL,DT_N_RESSUP 

	END

		 
		 
		  