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
AS
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 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 );
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 );
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 );
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;
|
