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

   /*Apaga as listas de preco nao utilizadas*/
   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 de 5 dias atras*/
   DELETE FROM NOTIFICACAOTCO
    WHERE DTINCLUSAO < TRUNC(SYSDATE - 5);
    
   COMMIT;
END;
/

DECLARE
  V_JOB NUMBER;
BEGIN
  FOR C_JOBS IN (SELECT JOB
                   FROM ALL_JOBS
                  WHERE WHAT LIKE '%LIMPEZATCO%') LOOP
    DBMS_JOB.REMOVE(C_JOBS.JOB);
  END LOOP;

  DBMS_JOB.SUBMIT(V_JOB, WHAT => 'BEGIN LIMPEZATCO; END;', 
  NEXT_DATE => TO_DATE(TO_CHAR(TRUNC(SYSDATE + 5), 'DD/MM/YYYY') || ' 00:00', 'DD/MM/YYYY HH24:MI'), 
  INTERVAL => 'TO_DATE(TO_CHAR(TRUNC(SYSDATE + 5), ''DD/MM/YYYY'') || '' 00:00'', ''DD/MM/YYYY HH24:MI'')');
  COMMIT;
END;
/