Script de Limpeza Oracle

IMPORTANTE:

•Aconselhável acompanhamento de um especialista em banco de dados para esse procedimento.

DECLARE

VAR_INDEX NUMBER;

BEGIN

    -- Criar tabelas temporárias

    EXECUTE IMMEDIATE 'CREATE TABLE "NDD_NFCE".TmpDocInvalidsProcessIDs(

      ProcessID NUMBER,

      ProcessDataID NUMBER,

    CONSTRAINT PK_PROCESSID PRIMARY KEY (ProcessID))';

    EXECUTE IMMEDIATE 'CREATE TABLE "NDD_NFCE".TmpLogsDocIds(

      LOGDOCID NUMBER CONSTRAINT PK_LOGDOCID PRIMARY KEY)';

    EXECUTE IMMEDIATE 'CREATE TABLE "NDD_NFCE".TmpLotIDs(

      LOTID NUMBER CONSTRAINT PK_LOTID  PRIMARY KEY)';

    EXECUTE IMMEDIATE 'CREATE TABLE "NDD_NFCE".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 "NDD_NFCE".SEQ_LIMPA

      MINVALUE 1

      MAXVALUE 999999

      NOCYCLE';

    -- Criar tabelas de logs

    EXECUTE IMMEDIATE 'CREATE TABLE "NDD_NFCE".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 "NDD_NFCE".TBLOGMSGS(

      IdLogMSg NUMBER NOT NULL,

      IdLOGLIMPDOCS NUMBER NOT NULL,

      HorLog date NOT NULL,

      TxtMsg varchar2(200))';

    EXECUTE IMMEDIATE 'CREATE TABLE "NDD_NFCE".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 "NDD_NFCE".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 "NDD_NFCE".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 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');

 /*

 CONCILIAÇÃO

 */

 -- remove as concilições que estão com data de criação 30 dias mais antigos do que a data de retenção

        DELETE FROM TbConciliation WHERE ProcessID IN (SELECT ProcessID FROM TmpProcessIDs 

                UNION 

                SELECT processid FROM TBPROCESS 

                WHERE PROCESSID in (SELECT RegistryProcessID FROM tmpProcessIDs)) OR PROCESSID IS NULL;

        ESCREVE_LOG(Var_IdLimDocs, 'Deletou da tbConciliation');    

        /*

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

         /*

        LOGDOCUMENTDETAIL

        */

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

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

        /*

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

        /*

        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 WHERE ProcessDataId IN  (SELECT ProcessDataId FROM tmpDocInvalidsProcessIDs);

        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;

/

/****************************************  Job de Limpeza **************************************************/

BEGIN

Dbms_Scheduler.Create_Job (

   job_name             => 'LIMPEZA_NDD_NFCE',

   job_type             => 'PLSQL_BLOCK',

   job_action           => 'BEGIN NDD_NFCE.LIMPA_REGISTROS_EXPIRADOS; END;',

   Start_Date           => To_Timestamp('23-10-2013 00.00.00','DD-MM-YYYY HH24:MI:SS'),

   repeat_interval      => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0', 

   enabled              =>  TRUE,

   comments             => 'Limpeza das tabelas do eForms');

End;

/

INSERT INTO "NDD_NFCE".TBPARLIMDOCS VALUES(180,1000, to_timestamp('01-01-13 00:00:01','DD-MM-YY HH24:MI:SS'), 1, 1);