CREATE OR REPLACE PROCEDURE APP.APP_TMP_FVENDAS_REJEITADOS ( evento IN NUMBER) IS
/*<TOAD_FILE_CHUNK>*/
BEGIN     
      IF evento = 1 THEN           
           EXECUTE IMMEDIATE 'TRUNCATE TABLE APP_TMP_FVENDAS'; 
             EXEC reset_sequence ('FVENDAS_SEQ');
   
            --SELECT FVENDAS_SEQ.NEXTVAL from dual   
            INSERT INTO APP_TMP_FVENDAS(ID,NIF, TC1, TC2, NIC) 
            SELECT FVENDAS_SEQ.NEXTVAL, NIF, TC1, TC2, NIC 
            FROM APP_TMP_FVENDAS_WORK WHERE TRANSACTION_DT >= sysdate-5;
                        
      END IF;   
     
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERRO NO PROCESSO APP.APP_TMP_FVENDAS_REJEITADOS: ' || TO_CHAR(SQLCODE) || '-' || SQLERRM);
        app_log('[ERROR](APP.APP_TMP_FVENDAS_REJEITADOS): ' || TO_CHAR(SQLCODE) || '-' || SQLERRM); 
END;
/

CREATE or REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) as    
    curr_val INTEGER;
BEGIN
    EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';
    EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;
    EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by -'||curr_val;
    EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;
    EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 1'; 
END reset_sequence;
/

create table test_051 (col_1 varchar2(20));

create table test_052 (col_1 varchar2(20));

insert into test_051 select 'T_51 Row '||level from dual connect by level <= 10;

insert into test_052 select 'T_52 Row '||level from dual connect by level <= 10;

commit;

begin

  execute immediate 'TRUNCATE TABLE test_051';
  
  insert into test_051 (col_1) select col_1 from test_052;
  commit;
end;
/

select * from test_051;

CREATE OR REPLACE PROCEDURE APP.APP_TMP_FVENDAS_REJEITADOS ( evento IN NUMBER) IS

/*<TOAD_FILE_CHUNK>*/
BEGIN     
      IF evento = 1 THEN
           SET TRANSACTION USE ROLLBACK SEGMENT RBS17;
           EXECUTE IMMEDIATE 'TRUNCATE TABLE APP_TMP_FVENDAS'; 
           reset_sequence ('FVENDAS_SEQ');
           INSERT INTO APP_TMP_FVENDAS(ID,NIF, TC1, TC2, NIC) 
           SELECT FVENDAS_SEQ.NEXTVAL(), NIF, TC1, TC2, NIC 
           FROM APP_TMP_FVENDAS_WORK; 
           COMMIT;     
      END IF;   
      
      IF evento = 2 THEN
         DELETE FROM APP_TMP_FVENDAS_WORK P WHERE EXISTS
               (    
                 SELECT NIF FROM APP_TMP_FVENDAS T where PROCESSED_FLG = 'Y' AND P.NIF = T.NIF);       
       COMMIT;
      END IF;
      ROLLBACK;
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERRO NO PROCESSO APP.APP_TMP_FVENDAS_REJEITADOS: ' || TO_CHAR(SQLCODE) || '-' || SQLERRM);
        app_log('[ERROR](APP.APP_TMP_FVENDAS_REJEITADOS): ' || TO_CHAR(SQLCODE) || '-' || SQLERRM); 
END APP_TMP_FVENDAS_REJEITADOS;
/