DECLARE

VAR_INDEX NUMBER;

BEGIN

   EXECUTE IMMEDIATE 'CREATE TABLE TmpOids(

     OID NUMBER CONSTRAINT PK_TmpOid PRIMARY KEY)';

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

   EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_LIMPACOLD

     MINVALUE 1

     MAXVALUE 999999

     NOCYCLE';

   EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_PARLIMCOLD

     MINVALUE 1

     MAXVALUE 999999

     NOCYCLE';

   -- Criar tabelas de logs

   EXECUTE IMMEDIATE 'CREATE TABLE TBLOGLIMPDOCSCOLD(

     IdLimpDocs NUMBER NOT NULL,

     HorIniLim DATE NOT NULL,

     DiaVidDoc NUMBER,

     TotExcTbCold NUMBER,

     TotExcTbColdEvt NUMBER,

     TotLoopDocumentos NUMBER,

     HorFimLim date,

     StatusAtual varchar2(100))';

   EXECUTE IMMEDIATE 'CREATE TABLE TBLOGMSGSCOLD(

     IdLogMSg NUMBER NOT NULL,

     IdLOGLIMPDOCS NUMBER NOT NULL,

     HorLog date NOT NULL,

     TxtMsg varchar2(200))';

   EXECUTE IMMEDIATE 'CREATE TABLE TBPARLIMCOLD(

     Id NUMBER CONSTRAINT PK_TbParlimColdId PRIMARY KEY,

     DiaVidDoc NUMBER NOT NULL,

     QtdRegLimLoo NUMBER NOT NULL,

     TemAtrLoo TIMESTAMP NOT NULL,

     TotDiaExc NUMBER NOT NULL,

     MsgLog NUMBER DEFAULT 0,

     ColdTbName varchar2(50) NOT NULL,

     ColdEvtTbName varchar2(50) NOT NULL,

     TpDoc varchar2(4) NOT NULL)';

END;

/

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

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

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

create or replace

PROCEDURE ESCREVE_LOG(IdLimDocs NUMBER, TxtLog varchar2)AS

BEGIN

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

 COMMIT;

 EXCEPTION

         WHEN NO_DATA_FOUND THEN

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

END;

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

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

--=====================================PROCEDURE DE LIMPEZA DAS TABELAS==============================================

create or replace

PROCEDURE LIMPA_REGISTROS_COLD (configId IN NUMBER )

AS

 Var_DBName varchar2(50);

 Var_IdLimDocs number;

 Var_diasdevida        number;

 Var_totalregistro number;

 Var_qtdloop        number;

 Var_CountLoop number;

 Var_totalLimpezaLoop number;

 Var_tempodeatraso varchar2(50);

 Var_LogStr varchar(200);

 Var_InicioExec timestamp;

 Var_SQL VARCHAR2(2000);

 Var_RowCount number;

 Var_ColdTbName VARCHAR2(50);

 Var_ColdEvtTbName VARCHAR2(50);

 Var_TpDoc VARCHAR2(4);

 Var_DateEmiColumn VARCHAR2(50);

 Var_SqlCountOids VARCHAR(1000);

 Var_SqlDelete VARCHAR(1000);

 Var_MsgLog number;

BEGIN  

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

 BEGIN

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

 SELECT DiaVidDoc,QtdRegLimLoo,TemAtrLoo,ColdTbName,ColdEvtTbName,TpDoc, MsgLog

   INTO Var_diasdevida,Var_totalregistro,Var_tempodeatraso,Var_ColdTbName,Var_ColdEvtTbName,Var_TpDoc, Var_MsgLog

 FROM TBPARLIMCOLD

 WHERE Id = configId

 ORDER BY 1;  

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

   INSERT INTO TBLOGLIMPDOCSCOLD (IdLimpDocs,HorIniLim,DiaVidDoc,TotExcTbCold,TotExcTbColdEvt,TotLoopDocumentos)

   values (SEQ_LIMPACOLD.NEXTVAL,sysdate,Var_diasdevida,0,0,0);  

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

   SELECT SEQ_LIMPACOLD.CURRVAL INTO Var_IdLimDocs FROM DUAL;

   EXCEPTION

     WHEN NO_DATA_FOUND THEN

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

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

 IF Var_MsgLog = 1 then

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

 END IF;

 IF UPPER(Var_TpDoc) = 'NFE'

         then Var_DateEmiColumn := 'IDE_DEMI';

 ELSE

         Var_DateEmiColumn := 'IDE_DHEMI';

 END IF;

 Var_qtdloop := 0;

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

 IF Var_MsgLog = 1 then

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

 END IF;

 -- INICIO DA EXCLUSAO DE DOCUMENTOS EXPIRADOS

 Var_qtdloop := 0;

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

 Var_totalLimpezaLoop := Var_totalregistro;

 Var_SqlCountOids := 'SELECT COUNT(oid) oidCount FROM '||Var_ColdTbName||' WHERE '||Var_DateEmiColumn||' < to_timestamp('''||to_char(Var_InicioExec,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'') - '||to_char(Var_diasdevida)||' order by 1';

 EXECUTE IMMEDIATE Var_SqlCountOids INTO Var_CountLoop;

 IF Var_MsgLog = 1 then

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

         ESCREVE_LOG(Var_IdLimDocs, Var_LogStr);

 END IF;

 WHILE Var_CountLoop > 0 LOOP

       -- limpa tabelas para seleção dos registros

         IF Var_MsgLog = 1 then

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

         END IF;

       EXECUTE IMMEDIATE 'TRUNCATE TABLE TmpOids';

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

       Var_qtdloop := Var_qtdloop + 1;

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

       Var_SQL := 'INSERT INTO TmpOids SELECT * FROM ( SELECT Oid FROM '||Var_ColdTbName||' WHERE '||Var_DateEmiColumn||' < to_timestamp('''||to_char(Var_InicioExec,'DD-MM-YY HH24:MI:SS')||''',''DD-MM-YY HH24:MI:SS'') - '||to_char(Var_diasdevida)||' order by 1) WHERE ROWNUM <='||to_char(Var_totalregistro);

       EXECUTE IMMEDIATE Var_SQL;

       SELECT count(*) into Var_totalLimpezaLoop from tmpoids;

         IF Var_MsgLog = 1 then

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

                 ESCREVE_LOG(Var_IdLimDocs, Var_LogStr);

       END IF;

       IF Var_totalLimpezaLoop = 0 then

                 IF Var_MsgLog = 1 then

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

                 END IF;

                 EXIT;

       END IF;

       /*

       INICIO DAS EXCLUSÕES

       */

       -- 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_SqlDelete := 'DELETE FROM '||Var_ColdEvtTbName||' WHERE OID IN (SELECT OID FROM TMPOIDS)' ;

       execute immediate Var_SqlDelete;

       Var_RowCount := SQL%ROWCOUNT;

       UPDATE TBLOGLIMPDOCSCOLD SET TotExcTbColdEvt = TotExcTbColdEvt + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

       IF Var_MsgLog = 1 then

                 ESCREVE_LOG(Var_IdLimDocs, 'Deletou da tablea de eventos');

         END IF;

       Var_SqlDelete := 'DELETE FROM '||Var_ColdTbName||' WHERE OID IN (SELECT OID FROM TMPOIDS)';

       execute immediate Var_SqlDelete;

       Var_RowCount := SQL%ROWCOUNT;

       UPDATE TBLOGLIMPDOCSCOLD SET TotExcTbCold = TotExcTbCold + Var_RowCount WHERE IdLimpDocs = Var_IdLimDocs;

         IF Var_MsgLog = 1 then

                 ESCREVE_LOG(Var_IdLimDocs, 'Deletou da tabela cold');

         END IF;

       EXECUTE IMMEDIATE Var_SqlCountOids INTO Var_CountLoop;

       --WAITFOR DELAY @tempodeatraso

 END LOOP;

 EXECUTE IMMEDIATE 'TRUNCATE TABLE TmpOids';

 UPDATE TBLOGLIMPDOCSCOLD SET TotLoopDocumentos = Var_qtdloop where IdLimpDocs = Var_IdLimDocs;

 --FINAL DA EXCLUSAO DE DOCUMENTOS EXPIRADOS

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

 IF Var_MsgLog = 1 then

         ESCREVE_LOG(Var_IdLimDocs, 'Finalizou Limpeza');

 END IF;

END LIMPA_REGISTROS_COLD;