Item_Reprocessing.sql

Source: DMF/PuC/AIF/Item_Reprocessing.sql

SELECT * FROM all_scheduler_running_jobs order by job_name;
 
BEGIN
  FOR rec IN (SELECT JOB_NAME FROM dba_scheduler_jobs WHERE OWNER = 'DMF' AND JOB_NAME LIKE 'SEND_TO_MFCS_ITEM_BUNDLE_%')
    LOOP
 
  DBMS_SCHEDULER.stop_JOB(
    job_name => rec.job_name,
    force    => TRUE
  );
 
  end loop;
    
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Erro ao parar job:'||SQLERRM);
END;
/
 
-------------------------
 
--delete FROM rcib_integration_ctrl where entity_id = 'ITEM';
update rcib_mfqueue_item i set event_status = 'U', error_message = null where event_status = 'W';
COMMIT;
 
 
MERGE INTO rcib_mfqueue_item trg
USING (SELECT seq_no,
              NTILE(83) OVER (ORDER BY seq_no) AS new_thread_no
         FROM rcib_mfqueue_item
        WHERE event_status IN ('U')) src
   ON (trg.seq_no = src.seq_no)
 WHEN MATCHED THEN
      UPDATE SET trg.thread_no   = src.new_thread_no,
                 trg.event_status = 'U';
 
DECLARE
  L_date           VARCHAR2(14);
  L_job_name       VARCHAR2(250);
  L_status_code    VARCHAR2(4);
  L_error_message  VARCHAR2(4000) := RPAD('+',4000,'+');
  L_activity_id    NUMBER;
BEGIN
  L_date     := TO_CHAR(SYSTIMESTAMP, 'DDMMYYYYHH24MISS');
  L_job_name := 'PCK_RCIB_INTEGRATION_ITEM' || l_date;
  --
  DBMS_SCHEDULER.CREATE_JOB(job_name            => L_job_name,
                            job_type            => 'STORED_PROCEDURE',
                            number_of_arguments => 4, 
                            job_action          => 'PCK_RCIB_INTEGRATION_ITEM.PROCESS_DATA',
                            start_date          => SYSTIMESTAMP, 
                            auto_drop           => TRUE,
                            job_class           => 'MEDIUM',--'HIGH',
                            enabled             => FALSE);
  --
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name          => L_job_name,
                                        argument_position => 1,
                                        argument_value    => 'FULL');
  --
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name          => L_job_name,
                                        argument_position => 2,
                                        argument_value    => L_status_code);
  --
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name          => L_job_name,
                                        argument_position => 3,
                                        argument_value    => L_error_message);
  --
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name          => L_job_name,
                                        argument_position => 4,
                                        argument_value    => L_activity_id);
  --
  DBMS_SCHEDULER.ENABLE(NAME => L_job_name);
END;
/