How to Clean 11i Apps Concurrent Manager Tables

Print this post

This can be used as a method to clear the errors upon bringing the internal manager back up.

Use this method for 11.5.7+ instances provided the managers are down and no FNDLIBR processes are
still running.

FIRST

Update process status codes to TERMINATED
Updating invalid process status codes in FND_CONCURRENT_PROCESSES

SELECT  concurrent_queue_name manager,
      concurrent_process_id pid,
      process_status_code pscode
FROM    fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE   process_status_code not in ('K', 'S')
AND     fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND     fcq.application_id = fcp.queue_application_id;

UPDATE  fnd_concurrent_processes
SET     process_status_code = 'K'
WHERE   process_status_code not in ('K', 'S');

commit;

SECOND

Set all managers to 0 processes
Updating running processes in FND_CONCURRENT_QUEUES
Setting running_processes = 0 and max_processes = 0 for all managers

UPDATE  fnd_concurrent_queues
SET     running_processes = 0, max_processes = 0;

commit;

THIRD

Reset control codes
Updating invalid control_codes in FND_CONCURRENT_QUEUES

SELECT  concurrent_queue_name manager,
      control_code ccode
FROM    fnd_concurrent_queues
WHERE   control_code not in ('E', 'R', 'X')
AND     control_code IS NOT NULL;

UPDATE  fnd_concurrent_queues
SET     control_code = NULL
WHERE   control_code not in ('E', 'R', 'X')
AND     control_code IS NOT NULL;

commit;

FOURTH

Also null out target_node for all managers

UPDATE  fnd_concurrent_queues
SET     target_node = null;

commit;

FIFTH

Set all 'Terminating' requests to Completed/Error
Also set Running requests to completed, since the managers are down
Updating any Running or Terminating requests to Completed/Error

SELECT  request_id request,
      phase_code pcode,
      status_code scode
FROM    fnd_concurrent_requests
WHERE   status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;
    
UPDATE  fnd_concurrent_requests
SET     phase_code = 'C', status_code = 'E'
WHERE   status_code ='T' OR phase_code = 'R';

commit;

SIXTH

Set all Runalone flags to 'N'
Updating any Runalone flags to 'N'

set serveroutput on
set feedback off
declare
      c         pls_integer := dbms_sql.open_cursor;
      upd_rows  pls_integer;
      vers      varchar2(50);
      tbl       varchar2(50);
      col       varchar2(50);
      statement varchar2(255);
begin

      select substr(release_name, 1, 2)
      into   vers
      from fnd_product_groups;

      if vers >= 11 then
         tbl := 'fnd_conflicts_domain';
         col := 'runalone_flag';
      else
         tbl := 'fnd_concurrent_conflict_sets';
         col := 'run_alone_flag';
      end if;


      statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
      dbms_sql.parse(c, statement, dbms_sql.native);
      upd_rows := dbms_sql.execute(c);
      dbms_sql.close_cursor(c);
      dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
end;
/

commit;

Now you can start with your Concurrent Managers...!!!!!!!!