/**
 * Rotina de limpeza das tabelas temporarias do sistema
 * revisada
 */
CREATE OR REPLACE PROCEDURE LIMPEZATCO
AS
BEGIN
   /*Limpa LISTA, lista de valores generica do sistema, com ou mais de 5 dias*/
   DELETE FROM LISTA WHERE DTULTATULIST < TRUNC(SYSDATE - 5);
   /*Limpa LISTAERROS, lista de erros de procedure, com ou mais de 5 dias*/
   DELETE FROM LISTAERROS WHERE DTINCLUSAO < TRUNC(SYSDATE - 5);
   COMMIT;      
   /*Apaga os cardapios de trabalho com ou mais de 5 dias*/
   FOR C_TRAB IN
   (
      SELECT C.NRCARDAPIOTRAB
        FROM CARDAPIOTRAB C
       WHERE C.DTINCLUSAO < TRUNC(SYSDATE - 5)
         AND NOT EXISTS (SELECT 1 FROM DETALHESERVPROPCOM D WHERE D.NRCARDAPIOTRAB = C.NRCARDAPIOTRAB)
   ) LOOP
      BEGIN
        DELETA_CARDTRAB(C_TRAB.NRCARDAPIOTRAB);
      EXCEPTION
        WHEN OTHERS THEN
          CONTINUE;
      END;
   END LOOP;

   /*Apaga as listas de preco nao utilizadas com ou mais de 5 dias*/
   FOR C_LISTAP IN
   (SELECT NRLISTAPRECOTCO
      FROM LISTAPRECOTCO
     WHERE DTREFERENCIA < TRUNC(SYSDATE - 5)) LOOP
      BEGIN
        DELETE FROM LISTAPRECOTCO
         WHERE NRLISTAPRECOTCO = C_LISTAP.NRLISTAPRECOTCO;
      EXCEPTION
        WHEN OTHERS THEN
          CONTINUE;
      END;
   END LOOP;

   /*Apaga as notificacoes com ou mais de 5 dias atras*/
   DELETE FROM NOTIFICACAOTCO
    WHERE DTINCLUSAO < TRUNC(SYSDATE - 5);
    
   COMMIT;
END;
/

DECLARE
  V_JOB_EXISTS NUMBER;
BEGIN
   -- Mantido para apagar possiveis jobs antigos
   FOR C_JOBS IN (SELECT JOB
                    FROM ALL_JOBS
                   WHERE WHAT LIKE '%LIMPEZATCO%') LOOP
      DBMS_JOB.REMOVE(C_JOBS.JOB);
   END LOOP;

   SELECT COUNT(*) INTO V_JOB_EXISTS
     FROM USER_SCHEDULER_JOBS
    WHERE JOB_NAME = 'JOB_LIMPEZATCO';

   IF V_JOB_EXISTS > 0 THEN
      DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_LIMPEZATCO');
   END IF;
   
   DBMS_SCHEDULER.CREATE_JOB (
      job_name => 'JOB_LIMPEZATCO',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN LIMPEZATCO(); END;',
      repeat_interval => 'FREQ=DAILY;INTERVAL=5;BYHOUR=3;BYMINUTE=15',
      end_date => NULL,
      enabled => TRUE,
      comments => 'Job criado para limpeza de tabelas temporarias do tecfood comercial'
   );
   
   COMMIT;
END;
/