IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_CPL_DEMANDA_PROC_EXPURGO]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [dbo].[P_EST_PROD_CPL_DEMANDA_PROC_EXPURGO]
GO 
 
/*
=======================================================================================
'Job.................:  
'Função..............: Linx - Media de Vendas
'Objetivo............: Expurgar registros com mais de 4 meses. 
'Tabelas.............: 
'Dt Modificação......: 18/09/2023
'Modificação.........: 
'Alterado por........: Gerson Reis
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/

CREATE PROCEDURE [dbo].[P_EST_PROD_CPL_DEMANDA_PROC_EXPURGO]  
AS
BEGIN TRY
	DECLARE @DT_DEL						DATE
	DECLARE @CD_CTR_DEL					BIGINT
	DECLARE @CD_CTR						BIGINT
	DECLARE @CD_CTR_DEMANDA_PROC_CPL	BIGINT 
	DECLARE @CD_CTR_CPL					BIGINT 

	SET @DT_DEL = DATEADD(MONTH, -4,GETDATE())
	SET @CD_CTR_DEL = (SELECT MAX(A.CD_CTR) FROM EST_PROD_CPL_DEMANDA_PROC A WHERE A.DT_PROC < @DT_DEL )
	PRINT 'EXPURGO DE DADOS ANTERIOR AO DIA .: ' + CONVERT(VARCHAR(50),@DT_DEL) + ' E CD_CTR MENOR IGUAL .: '  + CONVERT(VARCHAR(50),@CD_CTR_DEL)  

	SELECT DISTINCT top 20000000 PRO.CD_CTR,
			 CPL.CD_CTR_DEMANDA_PROC					AS CD_CTR_DEMANDA_PROC_CPL,
			 CPL.CD_CTR									AS CD_CTR_CPL
		INTO #Expurgar
	   FROM EST_PROD_CPL_DEMANDA_PROC (NOLOCK) PRO   
	        INNER JOIN EST_PROD_CPL_DEMANDA_PROC_CPL CPL (NOLOCK) ON PRO.CD_CTR = CPL.CD_CTR_DEMANDA_PROC
	  WHERE PRO.CD_CTR  <= @CD_CTR_DEL
	    AND PRO.DT_PROC < @DT_DEL
		AND CPL.CD_CTR > IDENT_CURRENT('EST_PROD_CPL_DEMANDA_PROC_CPL')
	 ORDER BY CPL.CD_CTR ASC

DECLARE C1 CURSOR
	FOR 
	 SELECT  CD_CTR
			 ,CD_CTR_DEMANDA_PROC_CPL
			 ,CD_CTR_CPL
	   FROM #Expurgar
	   ORDER BY CD_CTR ASC --Priorizar Ir folgando de baixo pra cima, para liberar espaço para as proximas entradas;

	OPEN C1;
	FETCH NEXT FROM C1 INTO @CD_CTR, @CD_CTR_DEMANDA_PROC_CPL, @CD_CTR_CPL;		
	WHILE @@FETCH_STATUS = 0
	    BEGIN

		IF ( Datepart(hh, Getdate()) >= 22 ) --Só deixar rodar até as 22h, todos os dias;
        BEGIN
            BREAK;
        END
      ELSE
        BEGIN

				DELETE FROM EST_PROD_CPL_DEMANDA_PROC_CPL_NAO_PROCESSAR  WHERE CD_CTR_DEMANDA_PROC = @CD_CTR 
			
				DELETE FROM EST_PROD_CPL_DEMANDA_PROC_CPL_BLOQUEIO WHERE CD_CTR_PROC_CPL = @CD_CTR_CPL
			
				DELETE FROM EST_PROD_CPL_DEMANDA_PROC_CPL_LIB WHERE CD_CTR_PROC_CPL = @CD_CTR_CPL
			
				DELETE FROM EST_PROD_CPL_DEMANDA_PROC_CPL_LIB_PENDENTE WHERE CD_CTR_PROC_CPL = @CD_CTR_CPL
			  
				DELETE FROM EST_PROD_CPL_DEMANDA_PROC_CPL_PENDENTE_APLICAR_MEDIAF WHERE CD_CTR_PROC_CPL = @CD_CTR_CPL
			
				DELETE FROM EST_PROD_CPL_DEMANDA_PROC_CPL WHERE CD_CTR = @CD_CTR_CPL 
			
				DELETE P 
				FROM EST_PROD_CPL_DEMANDA_PROC P
				WHERE CD_CTR = @CD_CTR 
				AND NOT EXISTS (SELECT 1 
								FROM EST_PROD_CPL_DEMANDA_PROC_CPL CP 
								WHERE CP.CD_CTR_DEMANDA_PROC = P.CD_CTR)
			END 

	FETCH NEXT FROM C1 INTO @CD_CTR, @CD_CTR_DEMANDA_PROC_CPL, @CD_CTR_CPL;	
	      END
	CLOSE C1;
	DEALLOCATE C1;

	PRINT ' FIM! P_EST_PROD_CPL_DEMANDA_PROC_EXPURGO'
END TRY	
BEGIN CATCH
		DECLARE @ERRORMESSAGE VARCHAR(4000)=ERROR_MESSAGE();  
		DECLARE @ERRORSEVERITY INT=ERROR_SEVERITY();  
		DECLARE @ERRORSTATE INT=ERROR_STATE();  
	   INSERT INTO ADM_ERRO (NUMERO, SEVERIDADE, ESTADO, ROTINA, LINHA, MENSAGEM, DTERRO )
        SELECT ERROR_NUMBER() 		AS NUMERO,
                @ERRORSEVERITY 		AS SEVERIDADE,
                @ERRORSTATE 		AS ESTADO,
                ERROR_PROCEDURE() 	AS ROTINA,
                ERROR_LINE() 		AS LINHA,
                @ERRORMESSAGE 		AS MENSAGEM,
                GETDATE() 			AS DTERRO;
	   RAISERROR (@ERRORMESSAGE, 
			    @ERRORSEVERITY,
			    @ERRORSTATE );
END CATCH

GO


