IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_TENTACLE_SYNC_CARGA_CLEANUP]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_TENTACLE_SYNC_CARGA_CLEANUP]
GO
GO
/*
=======================================================================================
'Job.................:  
'Função..............: 
'Objetivo............: Deletar Tabela de Auditoria de Carga
'Tabelas.............: 
'Dt Modificação......: 24/12/2020
'Modificação.........: Tag #A001 Ajuste de estouro de campo 
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/
CREATE PROCEDURE P_TENTACLE_SYNC_CARGA_CLEANUP AS

SET NOCOUNT ON;
-- Tag #A001
DECLARE @CD_FILIAL INT;
DECLARE @LAST_VERSION BIGINT;
DECLARE @Mestre INT;

DECLARE @sTabelas Varchar(100);
declare @Cmd Varchar(8000);

declare c_Tabelas CURSOR FAST_FORWARD FOR
 SELECT TABELA_ORIGEM_MONITORADA,FLAG_TABELA_MESTRE 
	FROM 
		TENTACLE_SYNC_TABELAS
	WHERE
		TP_CONTROLE=1 
	--	and TABELA_ORIGEM_MONITORADA='EST_PROD_PRECO'
		 
OPEN c_Tabelas
FETCH NEXT FROM c_Tabelas INTO @sTabelas, @Mestre
WHILE @@FETCH_STATUS = 0
	BEGIN

		PRINT 'Efetuando limpeza da tabela ' + @sTabelas +'_CARGA'
	
		----------------------------------------------------------------------------------------------------
		if @Mestre =1
		begin 
			Set @Cmd = 'DELETE FROM ' + @sTabelas + '_CARGA WHERE    VERSAO < ' + Convert(varchar(50), @LAST_VERSION); 
			Exec (@Cmd);   
		end
		else
		begin
			DECLARE C1 CURSOR
			FOR SELECT 
						B.CD_FILIAL
					, A.LAST_VERSION
				FROM 
					TENTACLE_SYNC_VERSAO A
					INNER JOIN TENTACLE_SYNC_CLIENT B ON 
						A.CLIENT_ID = B.ID
				WHERE 
					A.TABELA = @sTabelas;
    
			OPEN C1;

			FETCH NEXT FROM C1 INTO  @CD_FILIAL, @LAST_VERSION;
			WHILE @@FETCH_STATUS = 0
				BEGIN
					PRINT 'Efetuando limpeza da tabela ' + @sTabelas + '_CARGA Filial : '+ CONVERT(VARCHAR,@CD_FILIAL);
				
					Set @Cmd = 'DELETE FROM ' + @sTabelas + '_CARGA WHERE CD_FILIAL =' + Convert(varchar, @CD_FILIAL) + ' AND VERSAO <=' + Convert(varchar(50), @LAST_VERSION);
											
					Exec (@Cmd);  

					FETCH NEXT FROM C1 INTO  @CD_FILIAL, @LAST_VERSION;
				END;

			CLOSE C1;
			DEALLOCATE C1;

			----------------------------------------------------------------------------------------------------
			---APGANDO OS REGISTOS DE FILIAIS QUE NÃO ESTÃO CONFIGURADAS NA CARGA

			IF Exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @sTabelas +'_CARGA' AND column_name='CD_FILIAL')
			BEGIN

				DECLARE C2 CURSOR
				FOR 
					SELECT 
						A.CD_FILIAL
					FROM  
						PRC_FILIAL A
						LEFT JOIN (SELECT * FROM  TENTACLE_SYNC_CLIENT WHERE ATIVO=1) B ON
							A.CD_EMP = B.CD_EMP
							AND A.CD_FILIAL = B.CD_FILIAL
					WHERE 
						ISNULL(B.ATIVO,0) = 0

				OPEN C2;
				FETCH NEXT FROM C2 INTO  @CD_FILIAL;

				WHILE @@FETCH_STATUS = 0
					BEGIN
				
					--	PRINT 'Efetuando limpeza da tabela ' + @sTabelas + '_CARGA Filial inativa -> '+ CONVERT(VARCHAR,@CD_FILIAL);

						Set @Cmd = 'WHILE EXISTS( Select * from '+ @sTabelas+ '_CARGA WHERE  CD_FILIAL = ' + convert(varchar,@CD_FILIAL)
										+ ') begin DELETE TOP (100000) FROM  '+ @sTabelas+ '_CARGA WHERE  CD_FILIAL =' + convert(varchar,@CD_FILIAL)+ ' end '	
						Exec (@Cmd )
						FETCH NEXT FROM C2 INTO @CD_FILIAL;
					END;
				CLOSE C2;
				DEALLOCATE C2;

			END
		end
		 
		FETCH NEXT FROM c_Tabelas INTO @sTabelas, @Mestre
	
	END

CLOSE c_Tabelas;
DEALLOCATE c_Tabelas; 
 

   