Tabelas de Configuração

TBPARLIMDOCS – Nesta tabela irão constar os parâmetros de configuração da rotina de limpeza.

DiaVidDoc – Estabelece o tempo de vida em dias do documento dentro do ambiente de processamento. Assim sendo, apenas os documentos que estiverem na base há mais tempo do que do que o estipulado nesta coluna serão excluídos.

Se a coluna DiaVidDoc estiver preenchida com o valor 1, todos os documentos que possuam mais do que um dia de vida na base serão excluídos.

QtdRegLimLoo – Para evitar o travamento da tabela, a exclusão dos documentos é executada aos poucos dentro de um looping. O campo QtdRegLimLoo determina quantos documentos serão excluídos por execução.

TemAtrLoo – Este parâmetro define o tempo de espera entre as iterações do looping.

Se o QtdRegLimLoo for definido com valor 1000 e o TemAtrLoo foi definido com valor ‘00:00:03.0000000’, a cada três segundos serão apagados 1000 documentos.

TotDiaExc – Este parâmetro define a quantidade de dias que serão apagados em cada execução do script. Isto significa que a cada execução do script serão apagados todos os documentos que estiverem dentro deste intervalo a partir do primeiro documento.

Se o TotDiaExc for definido com valor 15, os documentos dos 15 dias seguintes ao primeiro documento serão excluídos.

Sugere-se o modelo de valores padrões para inserção na tabela de configuração:

DECLARE

VAR_INDEX NUMBER;

BEGIN

-- Criando estatística para melhorar o desempenho da limpeza

EXECUTE IMMEDIATE 'ANALYZE TABLE TBPROCESS COMPUTE STATISTICS';

-- Criando índice na TBPROCESSDATA

SELECT COUNT(*) INTO VAR_INDEX

FROM USER_INDEXES IDX

INNER JOIN user_ind_columns COL

ON COL.index_name = IDX.index_name

WHERE IDX.table_name = 'TBPROCESSDATA'

AND COL.column_name = 'PROCESSDATAID';

IF VAR_INDEX = 0 THEN

EXECUTE IMMEDIATE 'CREATE INDEX PK_TBPROCESSDATA_2 ON TBPROCESSDATA(PROCESSDATAID)';

END IF;

-- Criar tabelas temporárias

EXECUTE IMMEDIATE 'CREATE TABLE TmpDocInvalidsProcessIDs(

ProcessID NUMBER,

ProcessDataID NUMBER,

CONSTRAINT PK_PROCESSID PRIMARY KEY (ProcessID))';

EXECUTE IMMEDIATE 'CREATE TABLE TmpLogsDocIds(

LOGDOCID NUMBER CONSTRAINT PK_LOGDOCID PRIMARY KEY)';

EXECUTE IMMEDIATE 'CREATE TABLE TmpLotIDs(

LOTID NUMBER CONSTRAINT PK_LOTID PRIMARY KEY)';

EXECUTE IMMEDIATE 'CREATE TABLE TmpProcessIds(

ProcessId NUMBER,

RegistryProcessID NUMBER,

CONSTRAINT PK_PROCESSIDTMP PRIMARY KEY (ProcessId))';

--Criar uma sequence para inserir registro sequenciais nas tabelas, pois o oracle não tem auto increment

EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_LIMPA

MINVALUE 1

MAXVALUE 999999

NOCYCLE';

-- Criar tabelas de logs

EXECUTE IMMEDIATE 'CREATE TABLE TBLOGLIMPDOCS(

IdLimpDocs NUMBER NOT NULL,

HorIniLim DATE NOT NULL,

DiaVidDoc NUMBER,

TotDiaExc NUMBER,

TipLimTLog varchar2(4),

DirBkpTLog varchar2(100),

NomJobTLog varchar2(50),

TotExcTbLogFilterDocument NUMBER,

TotExcTbProcess NUMBER,

TotExcTbProcessData NUMBER,

TotLoopDocInvalidos NUMBER,

TotExcTbEvent NUMBER,

TotExcTbProcessLots NUMBER,

TotExcTbLots NUMBER,

TotExcTbLogDocumentPrinted NUMBER,

TotExcTbImpression NUMBER,

TotExcTbRelatedDocument NUMBER,

TotExcTbLogDocMessage NUMBER,

TotExcTbLogDocumentAction NUMBER,

TotExcTBENTRYDOCUMENT NUMBER,

TotExcTbLogDocument NUMBER,

TotExcTbLogDocumentStatus NUMBER,

TotLoopDocumentos NUMBER,

HorFimLim date,

StatusAtual varchar2(100))';

EXECUTE IMMEDIATE 'CREATE TABLE TBLOGMSGS(

IdLogMSg NUMBER NOT NULL,

IdLOGLIMPDOCS NUMBER NOT NULL,

HorLog date NOT NULL,

TxtMsg varchar2(200))';

EXECUTE IMMEDIATE 'CREATE TABLE TBPARLIMDOCS(

DiaVidDoc NUMBER NOT NULL,

QtdRegLimLoo NUMBER NOT NULL,

TemAtrLoo TIMESTAMP NOT NULL,

TotDiaExc NUMBER NOT NULL,

MsgLog NUMBER DEFAULT 0)';

END;

--===================================================================================================================

--=========================================PROCEDURE PARA GRAVAR LOG=================================================

create or replace

PROCEDURE ESCREVE_LOG(IdLimDocs NUMBER, TxtLog varchar2)AS

Var_MsgLog NUMBER;

BEGIN

SELECT MsgLog INTO Var_MsgLog

FROM TBPARLIMDOCS;

IF Var_MsgLog = 1 THEN

INSERT INTO TBLOGMSGS (IDLOGMSG,IdLOGLIMPDOCS, HorLog, TxtMsg) values (SEQ_LIMPA.NEXTVAL,IdLimDocs, sysdate, TxtLog);

END IF;

COMMIT;

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('DADOS NAO ENCONTRADOS NA TABELA DE PARAMETROS');

END;

--===================================================================================================================

--=====================================PROCEDURE PARA LIMPAR AS TABELAS==============================================

create or replace

PROCEDURE LIMPA_REGISTROS_EXPIRADOS

Var_DBName varchar2(50);

Var_IdLimDocs number;

Var_diasdevida number;

Var_totalregistro number;

Var_totaldedias number := 0;

Var_qtdloop number;

Var_BaseComEventos char;

Var_CountLoop number;

Var_totalLimpezaLoop number;

Var_Debug number;

Var_tempodeatraso varchar2(50);

Var_tiplimlog varchar2(4);

Var_minDocDate timestamp;

Var_minLotDate timestamp;

Var_minDocDate_loop timestamp;

Var_minLotDate_loop timestamp;

Var_MsgLog varchar(200);

Var_InicioExec timestamp;

Var_minProcessDate varchar2(50);

Var_countWsRegs number;

Var_SQL VARCHAR2(2000);

Var_RowCount number;

Var_contador number;

Var_conta_web number;

Var_Data_Formatada timestamp;

BEGIN

SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') into Var_DBName FROM DUAL;

BEGIN

-- carrega as variáreis de acordo com a tabela TBPARLIMDOCS

SELECT DiaVidDoc,

QtdRegLimLoo,

TotDiaExc,

TemAtrLoo

INTO

Var_diasdevida,

Var_totalregistro,

Var_totaldedias,

Var_tempodeatraso

FROM TBPARLIMDOCS

WHERE ROWNUM < 2

ORDER BY 1;

-- insere a linha de log para a execução

INSERT INTO TBLOGLIMPDOCS (IdLimpDocs,HorIniLim,DiaVidDoc,TotDiaExc,TotExcTbLogFilterDocument,TotExcTbProcess,TotExcTbProcessData,TotLoopDocInvalidos,TotExcTbEvent,TotExcTbProcessLots,TotExcTbLots,TotExcTbLogDocumentPrinted,

TotExcTbImpression,TotExcTbRelatedDocument,TotExcTbLogDocMessage,TotExcTBENTRYDOCUMENT,TotExcTbLogDocument,TotExcTbLogDocumentStatus,TotLoopDocumentos)

values (SEQ_LIMPA.NEXTVAL,sysdate,Var_diasdevida,Var_totaldedias,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);

-- captura o id da execução na tabela TBPARLIMDOCS

SELECT SEQ_LIMPA.CURRVAL INTO Var_IdLimDocs FROM DUAL;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.put_line('DADOS NAO ENCONTRADOS NA TABELA TBPARLIMDOCS');

END;

Var_InicioExec := to_timestamp(to_char(sysdate,'DD-MM-YY HH24:MI:SS'),'DD-MM-YY HH24:MI:SS');

--Elimina logs da última execução

EXECUTE IMMEDIATE 'TRUNCATE TABLE TBLOGMSGS';

ESCREVE_LOG(Var_IdLimDocs, 'Início da Limpeza');

Var_qtdloop := 0;

-- início da captura de limites de informções

SELECT MIN(DocumentDate) INTO Var_minDocDate

FROM TBLOGDOCUMENT

WHERE (AllowDelete = 1 or Hidden = 1)

AND LogDocId not in (SELECT DISTINCT(LogDocId) FROM TbEvent WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136)))

OR LogDocId in (SELECT DISTINCT(LogDocId) FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= Var_InicioExec - Var_diasdevida));

ESCREVE_LOG(Var_IdLimDocs, 'Verificado total de registros para limpezas');

SELECT min(LotDate) INTO Var_minLotDate FROM TBLOT;

ESCREVE_LOG(Var_IdLimDocs, 'Verificado total de lotes para limpezas');

-- INICIO DA EXCLUSAO DE DOCUMENTOS EXPIRADOS

Var_qtdloop := 0;

UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Inicio da limpeza de documentos expirados' where IdLimpDocs = Var_IdLimDocs;

Var_totalLimpezaLoop := Var_totalregistro;

SELECT count(LogDocId) INTO Var_CountLoop

FROM TbLogDocument WHERE

(AllowDelete = 1 or Hidden = 1) and

DocumentDate < Var_InicioExec - Var_diasdevida and

DocumentDate between Var_minDocDate and (Var_minDocDate + Var_totaldedias) and

LogDocId not in (SELECT DISTINCT(LogDocId) FROM TbEvent WHERE Colded = 0 AND (CSTAT IS NULL OR CSTAT IN (135, 136))) or

LogDocId in (SELECT DISTINCT(LogDocId) FROM TBPROCESS WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument WHERE FilterDate <= (Var_InicioExec - Var_diasdevida)));

Var_MsgLog := 'Total de documentos expirados encontrados: ' || to_char(Var_CountLoop);

ESCREVE_LOG(Var_IdLimDocs, Var_MsgLog);

WHILE Var_CountLoop > 0 LOOP

-- limpa tabelas para seleção dos registros

ESCREVE_LOG(Var_IdLimDocs, 'Iniciado laço de limpeza da TbLogDocument');

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpLogsDocIds';

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpProcessIDs';

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpLotIDs';

INSERT INTO TBLOGMSGS (IDLOGMSG,IdLOGLIMPDOCS, HorLog, TxtMsg) values (SEQ_LIMPA.NEXTVAL,Var_IdLimDocs, SYSDATE, 'Capturados valores mínimos');

Var_qtdloop := Var_qtdloop + 1;

UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Carga dos documentos expirados' where IdLimpDocs = VAR_IdLimDocs;

Var_SQL := 'INSERT INTO tmpLogsDocIds

SELECT LogDocId FROM TbLogDocument WHERE (AllowDelete = 1 or Hidden = 1)

AND DocumentDate < to_timestamp('''||to_char(sysdate - Var_diasdevida,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'')

AND ROWNUM < '||to_char(Var_totalregistro + 1)||'

AND DocumentDate between to_timestamp('''||to_char(Var_minDocDate,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'') and to_timestamp('''||to_char(Var_minDocDate + Var_totaldedias,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'')

AND LogDocId not in (SELECT DISTINCT(LogDocId) FROM TbEvent WHERE Colded = 0

AND (CSTAT IS NULL OR CSTAT IN (135, 136)))

OR LogDocId in (SELECT DISTINCT(LogDocId)

FROM TBPROCESS

WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument

WHERE FilterDate <= to_timestamp('''||to_char(Var_InicioExec - Var_diasdevida,'DD-MM-YY HH24:MI:SS') ||''',''DD-MM-YY HH24:MI:SS'')))

union

SELECT distinct(LogDocId) FROM TbLogDocumentstatus

WHERE Kind = 7 and DateStatus <= to_timestamp('''||to_char(Var_InicioExec - Var_diasdevida,'DD-MM_YYYY HH24:MI:SS') || ''',''DD-MM_YYYY HH24:MI:SS'')

and DateStatus between to_timestamp('''||to_char(Var_minDocDate,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'') and to_timestamp('''||to_char(Var_minDocDate + Var_totaldedias,'DD-MM-YY HH24:MI:SS')|| ''',''DD-MM-YY HH24:MI:SS'') order by 1';

EXECUTE IMMEDIATE Var_SQL;

SELECT count(*) into Var_totalLimpezaLoop from tmpLogsDocIds;

Var_MsgLog := 'Inseriu registros na tmpLogsDocIds -> ' || to_char(Var_totalLimpezaLoop);

ESCREVE_LOG(Var_IdLimDocs, Var_MsgLog);

IF Var_totalLimpezaLoop = 0 then

ESCREVE_LOG(Var_IdLimDocs,'Encerrada a limpeza de documentos normais. Não existem documentos expirados no período de tempo especificado');

EXIT;

--Aqui deverá encerrar o loop ?

END IF;

-- busca todos os ProcessIDs que estão relacionados com os LogsDocIds que podem ser removidos

INSERT into TmpProcessIDs

SELECT ProcessID, RegistryProcessID

FROM TbProcess

WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

ESCREVE_LOG(Var_IdLimDocs, 'Inseriu registros na ProcessIDs');

EVENTOS

-- remove os registros de eventos que estão vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TbEvent WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbEvent = TotExcTbEvent + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbEvent');

INSERT INTO tmpLotIDs

SELECT DISTINCT LotId FROM TbProcessLots

WHERE ProcessID IN (SELECT PROCESSID FROM TmpProcessIDs);

ESCREVE_LOG(Var_IdLimDocs, 'Inseriu Registros na LotsIDs');

-- remove os processos em lotes de eventos que estão vinculados aos ProcessIDs que serão removidos

DELETE FROM TbProcessLots WHERE LotId IN (SELECT LotId FROM TmpLotIDs);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbProcessLots = TotExcTbProcessLots + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs,'Deletou da tbProcessLots');

-- remove os lotes de eventos que estão vinculados aos ProcessIDs que serão removidos

DELETE FROM TbLots WHERE LotId IN (SELECT LotId FROM TmpLotIDs);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLots = TotExcTbLots + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da tbLots');

PROCESSOS

-- remove os registros de processos que estão vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TbProcess

WHERE ProcessID IN (SELECT ProcessID FROM TmpProcessIDs

UNION

SELECT processid FROM TBPROCESS

WHERE PROCESSID in (SELECT RegistryProcessID FROM tmpProcessIDs));

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbProcess = TotExcTbProcess + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbProcess');

-- remove os registros da tabela de automação de processos que não possuem referência na tabela de processos

DELETE FROM TbAutomate

WHERE ProcessDataId NOT IN (SELECT DISTINCT ProcessDataId FROM TBProcess);

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbAutomate');

-- remove os registros de documento original/convertidos que não possuem referência na tabela de processos

DELETE FROM TbProcessData T1 WHERE NOT EXISTS ( SELECT NULL FROM TBProcess T2 WHERE t2.ProcessDataId = t1.ProcessDataId UNION SELECT NULL FROM TBEvent EV WHERE EV.ProcessDataId = t1.ProcessDataId);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbProcessData = TotExcTbProcessData + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbProcessData');

REIMPRESSÃO PELO E-MONITOR

-- remove os registros de reimpressão vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TbLogDocumentPrinted WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocumentPrinted = TotExcTbLogDocumentPrinted + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbLogDocumentPrinted');

DOCUMENTOS RELACIONADOS

-- remove os registros de documentos relacionados através da automação vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TbRelatedDocument where LogDocID IN (SELECT LogDocId FROM tmpLogsDocIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbRelatedDocument = TotExcTbRelatedDocument + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbRelatedDocument');

-- remove os registros de documentos relacionados que podem ter ficado órfãos, caso apenas o primeiro documento tenha sido excluído no comando anterior

DELETE FROM TbRelatedDocument WHERE ParentId NOT IN (SELECT Id FROM TbRelatedDocument);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbRelatedDocument = TotExcTbRelatedDocument + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou orfãos da TbRelatedDocument');

MENSAGENS

-- remove os registros de mensagens vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TbLogDocMessage WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocMessage = TotExcTbLogDocMessage + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbLogDocMessage');

DOCUMENTOS DE ENTRADA

-- remove os registros de sumário de documentos de entrada vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TBENTRYDOCUMENT WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTBENTRYDOCUMENT = TotExcTBENTRYDOCUMENT + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TBENTRYDOCUMENT');

STATUS

-- remove os registros de status vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TbLogDocumentStatus WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocumentStatus = TotExcTbLogDocumentStatus + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbLogDocumentStatus');

LOGS DE AÇÕES DO MONITOR

-- remove os registros de log de ações do e-monitor vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TbLogDocumentAction WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocumentAction = TotExcTbLogDocumentAction + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbLogDocumentAction');

SENDER CONSULT

--remove os registros do sender consult vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TBSENDERCONSULT WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbSenderConsult');

DOWNLOAD

--remove os registros de download vinculados aos tmpLogsDocIds que serão removidos

DELETE FROM TBDOWNLOADITEMLOT WHERE DOWNLOADITEMID IN (SELECT DOWNLOADITEMID FROM TBDOWNLOADITEM WHERE LOGDOCID IN (SELECT LogDocId FROM tmpLogsDocIds));

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbDownloadItemLot');

DELETE FROM TBDOWNLOADITEM WHERE LOGDOCID IN (SELECT LogDocId FROM tmpLogsDocIds);

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbDownloadItem');

DELETE FROM TBDOWNLOADLOT WHERE DOWNLOADLOTID NOT IN (SELECT DOWNLOADLOTID FROM TBDOWNLOADITEMLOT);

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TBDOWNLOADLOT');

LOGS

-- remove os registros de log de documento que estão com a exclusão permitida

DELETE FROM TbLogDocument WHERE LogDocId IN (SELECT LogDocId FROM tmpLogsDocIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLogDocument = TotExcTbLogDocument + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbLogDocument');

LOTES

-- remove os lotes que estão com data de criação 30 dias mais antigos do que a data de retenção

DELETE FROM TbLot WHERE LotDate < (sysdate - Var_diasdevida)

and LotDate between Var_minLotDate and (Var_minLotDate + Var_totaldedias);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLots = TotExcTbLots + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbLot');

--WAITFOR DELAY @tempodeatraso

Var_CountLoop := 0;

SELECT count(LogDocId) into Var_CountLoop

FROM TbLogDocument

WHERE (AllowDelete = 1 or Hidden = 1) and DocumentDate < Var_InicioExec - Var_diasdevida

and DocumentDate between Var_minDocDate and Var_minDocDate+Var_totaldedias

and LogDocId not in (SELECT DISTINCT(LogDocId) FROM TbEvent

WHERE Colded = 0

AND (CSTAT IS NULL OR CSTAT IN (135, 136)))

OR LogDocId in (SELECT DISTINCT(LogDocId)

FROM TBPROCESS

WHERE PROCESSID in (SELECT PROCESSID

FROM TbLogFilterDocument

WHERE FilterDate <= Var_InicioExec - Var_diasdevida));

END LOOP;

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpLogsDocIds';

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpProcessIDs';

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpLotIDs';

UPDATE TBLOGLIMPDOCS SET TotLoopDocumentos = Var_qtdloop where IdLimpDocs = Var_IdLimDocs;

--FINAL DA EXCLUSAO DE DOCUMENTOS EXPIRADOS

-- INICIO DA EXCLUSAO DE DOCUMENTOS INVALIDOS

ESCREVE_LOG(Var_IdLimDocs, 'Iniciando limpeza de documentos inválidos');

UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Limpando documentos inválidos' where IdLimpDocs = Var_IdLimDocs;

Var_contador := 1;

WHILE Var_contador > 0 LOOP

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpDocInvalidsProcessIDs';

Var_qtdloop := Var_qtdloop + 1;

UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Removendo documentos inválidos' where IdLimpDocs = Var_IdLimDocs;

Var_Data_Formatada := Var_InicioExec - Var_diasdevida;

Var_SQL := 'INSERT INTO tmpDocInvalidsProcessIDs SELECT ProcessID, ProcessDataID FROM TbProcess

WHERE PROCESSID in (SELECT PROCESSID FROM TbLogFilterDocument

WHERE FilterDate <= to_timestamp('''||to_char(Var_Data_Formatada,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'')

AND ROWNUM < '||to_char(Var_totalregistro + 1)||')';

EXECUTE IMMEDIATE Var_SQL;

Var_MsgLog := 'Carregou ' || to_char(SQL%ROWCOUNT) || ' tmpDocInvalidsProcessIDs para remoção';

ESCREVE_LOG(Var_IdLimDocs, Var_MsgLog);

SELECT count(*) into Var_totalLimpezaLoop from tmpDocInvalidsProcessIDs;

IF Var_totalLimpezaLoop = 0 THEN

ESCREVE_LOG(Var_IdLimDocs, 'Terminou a limpeza dos documentos inválidos');

--ENCERRA O LOOP

EXIT;

END IF;

-- remove registros de documentos inválidos

DELETE FROM TbLogFilterDocument WHERE ProcessID IN (SELECT ProcessId FROM tmpDocInvalidsProcessIDs);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLogFilterDocument = TotExcTbLogFilterDocument + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbLogFilterDocument');

-- remove registros de processos de documentos inválidos

DELETE FROM TbProcess WHERE ProcessID IN (SELECT ProcessId FROM tmpDocInvalidsProcessIDs);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbProcess = TotExcTbProcess + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbProcess');

-- remove registros de documentos orignais inválidos

DELETE FROM TbProcessData T1 WHERE NOT EXISTS ( SELECT NULL FROM TBProcess T2 WHERE t2.ProcessDataId = t1.ProcessDataId UNION SELECT NULL FROM TBEvent EV WHERE EV.ProcessDataId = t1.ProcessDataId);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbProcessData = TotExcTbProcessData + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbProcessData');

SELECT count(PROCESSID) into Var_contador

FROM TbLogFilterDocument

WHERE FilterDate <= to_timestamp(Var_InicioExec - Var_diasdevida,'DD-MM-YY HH24:MI:SS');

END LOOP;

EXECUTE IMMEDIATE 'TRUNCATE TABLE tmpDocInvalidsProcessIDs';

UPDATE TBLOGLIMPDOCS SET TotLoopDocInvalidos = Var_qtdloop where IdLimpDocs = Var_IdLimDocs;

-- FINAL DA EXCLUSAO DE DOCUMENTOS INVALIDOS

-- INICIO DA EXCLUSÃO DE PROCESSOS ORFÃOS

-- remove registros da tbprocess que não estão vinculados a logdocid

ESCREVE_LOG(Var_IdLimDocs, 'Iniciando limpeza de processos orfãos');

UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Limpando processos Orfãos' where IdLimpDocs = Var_IdLimDocs;

SELECT to_char(min(StartDate),'DD-MM-YY HH24:MI:SS') INTO Var_minProcessDate

FROM TBPROCESS

WHERE LogDocID IS NULL

AND PROCESSSTATUS IN (1,2,3)

AND STARTDATE < (Var_InicioExec - Var_diasdevida);

Var_contador := 1;

WHILE Var_contador > 0 LOOP

Var_sql := 'INSERT INTO tmpProcessIDs SELECT ProcessID, RegistryProcessID FROM TbProcess

WHERE LogDocID IS NULL

AND STARTDATE < to_timestamp('''||to_char(Var_minProcessDate)||''',''DD-MM-YY HH24:MI:SS'') + '||to_char(Var_totaldedias)|| '

and PROCESSSTATUS IN (1,2,3) and STARTDATE < to_timestamp('''||to_char(Var_InicioExec - Var_diasdevida)||''',''DD-MM-YY HH24:MI:SS'')

and ROWNUM < '||(Var_totalregistro + 1);

EXECUTE IMMEDIATE Var_sql;

SELECT count(*) INTO Var_totalLimpezaLoop from tmpProcessIds;

IF Var_totalLimpezaLoop = 0 THEN

ESCREVE_LOG(Var_IdLimDocs, 'Terminou limpeza de processos orfãos');

EXIT;

END IF;

Var_MsgLog := 'Excluindo processos orfaos -> ' || to_char(Var_totalLimpezaLoop);

ESCREVE_LOG(Var_IdLimDocs, Var_MsgLog);

DELETE FROM TbLogFilterDocument WHERE ProcessID IN (SELECT PROCESSID FROM TmpProcessIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbLogFilterDocument = TotExcTbLogFilterDocument + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbLogFilterDocument');

DELETE FROM TBPROCESS WHERE ProcessId IN (SELECT PROCESSID FROM TmpProcessIds);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbProcess = TotExcTbProcess + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbProcess');

-- remove registros da tbprocessdata que foram esxcluidos da tbprocess

DELETE FROM TbProcessData T1 WHERE NOT EXISTS ( SELECT NULL FROM TBProcess T2 WHERE t2.ProcessDataId = t1.ProcessDataId UNION SELECT NULL FROM TBEvent EV WHERE EV.ProcessDataId = t1.ProcessDataId);

Var_RowCount := SQL%ROWCOUNT;

UPDATE TBLOGLIMPDOCS SET TotExcTbProcessData = TotExcTbProcessData + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Deletou da TbProcessData');

EXECUTE IMMEDIATE 'TRUNCATE TABLE TmpProcessIds';

SELECT count(*) into Var_contador

FROM TbProcess

WHERE LogDocID IS NULL

AND STARTDATE <= to_timestamp(Var_minProcessDate,'DD-MM-YY HH24:MI:SS') + Var_totaldedias

and PROCESSSTATUS IN (1,2,3)

and STARTDATE < Var_InicioExec - Var_diasdevida;

END LOOP;

-- FINAL DA EXCLUSÃO DE PROCESSOS ORFÃOS

-- LIMPEZA DAS TABELAS DO PRODUTO WEB SERVICES

UPDATE TBLOGLIMPDOCS SET StatusAtual = 'Limpando tabelas do produto Web Services' where IdLimpDocs = Var_IdLimDocs;

Var_countWsRegs := 1;

WHILE Var_countWsRegs > 0 LOOP

-- remove os registros das tabelas do produto Web Services

Var_countWsRegs := 0;

Var_contador := 0;

SELECT COUNT(*) into Var_contador

from user_tables

where table_name = 'TBWEBSERVICEDOCUMENTCONTROL';

IF Var_contador > 0 THEN

EXECUTE IMMEDIATE 'DELETE FROM TBWEBSERVICEDOCUMENTCONTROL

WHERE INSERTDATE < '||(SYSDATE - Var_diasdevida)||'

and INSERTDATE < (SELECT MIN(INSERTDATE) + '||Var_totaldedias||' FROM TBWEBSERVICEDOCUMENTCONTROL)

AND ROWNUM < '||(Var_totalregistro + 1);

Var_conta_web := 0;

EXECUTE IMMEDIATE 'SELECT COUNT(docNumber)

FROM TBWEBSERVICEDOCUMENTCONTROL

WHERE INSERTDATE < (sysdate - Var_diasdevida)

and INSERTDATE < (SELECT min(INSERTDATE) + Var_totaldedias FROM TBWEBSERVICEDOCUMENTCONTROL)' INTO Var_conta_web;

Var_countWsRegs := Var_countWsRegs + Var_conta_web;

END IF;

SELECT COUNT(*) into Var_contador

from user_tables

where table_name ='TBWEBSERVICEDOCUMENT';

IF Var_contador > 0 THEN

EXECUTE IMMEDIATE 'DELETE FROM TBWEBSERVICEDOCUMENT

WHERE protocolId IN (SELECT protocolId FROM TBWEBSERVICEDOCUMENT

WHERE INSERTDATE < SYSDATE - '||Var_diasdevida||'

and INSERTDATE < (SELECT min(INSERTDATE) + '||Var_totaldedias||' FROM TBWEBSERVICEDOCUMENT)

and ROWNUM < '||Var_totalregistro + 1||')';

Var_conta_web := 0;

EXECUTE IMMEDIATE 'SELECT COUNT(protocolId)

FROM TBWEBSERVICEDOCUMENT

WHERE INSERTDATE < SYSDATE - Var_diasdevida

and INSERTDATE < (SELECT min(INSERTDATE) + '||Var_totaldedias||' FROM TBWEBSERVICEDOCUMENT)' INTO Var_conta_web;

Var_countWsRegs := Var_countWsRegs + Var_conta_web ;

END IF;

SELECT COUNT(*) into Var_contador

from user_tables

where table_name ='TBWEBSERVICEINPUT';

IF Var_contador > 0 THEN

EXECUTE IMMEDIATE 'DELETE FROM TBWEBSERVICEINPUT

WHERE serviceInputId IN (SELECT serviceInputId FROM TBWEBSERVICEINPUT

WHERE INSERTDATE < sysdate - Var_diasdevida

and INSERTDATE < (SELECT min(INSERTDATE) + '||Var_totaldedias||' FROM TBWEBSERVICEINPUT)

and ROWNUM < '||Var_totalregistro + 1||')';

Var_conta_web := 0;

EXECUTE IMMEDIATE 'SELECT COUNT(serviceInputId)

FROM TBWEBSERVICEINPUT

WHERE INSERTDATE < '||sysdate - Var_diasdevida||'

and INSERTDATE < (SELECT min(INSERTDATE) + '||Var_totaldedias||' FROM TBWEBSERVICEINPUT)' INTO Var_conta_web;

Var_countWsRegs := Var_countWsRegs + Var_conta_web;

END IF;

END LOOP;

-- FINAL DA LIMPEZA DAS TABELAS DO PRODUTO WEB SERVICES

UPDATE TBLOGLIMPDOCS SET HorFimLim = sysdate, StatusAtual = 'Limpeza Finalizada' where IdLimpDocs = Var_IdLimDocs;

ESCREVE_LOG(Var_IdLimDocs, 'Finalizou Limpeza');

END LIMPA_REGISTROS_EXPIRADOS;