Issue:
Concurrent program stuck in OPP
Steps to solve the Issue:
Query to check concurrent program stuck in OPP
SELECT a.request_id, substr(e.user_concurrent_program_name,1,80) Program,d.sid, d.serial# ,d.status,to_char(d.logon_time,’DD-MON-YY HH24:MI:SS’)logon, round(d.last_call_et/60) LCT
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,v$session d,
apps.fnd_concurrent_programs_tl e
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id=e.concurrent_program_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = ‘R’
and d.sql_id=’fnpyvpk41nd5s’
order by 7 desc;
Log/Output file
If the above query returns a concurrent program,we need to check the log file from the concurrent node of the environment.
Cd $APPLCSF/log
Query to terminate the request,
We need to find the Sid,serial# and status of the session of the concurrent request.
Alter system kill session ‘sid,serial#’;
We need to update the phase and status of concurrent program and do a commit
update apps.fnd_concurrent_requests set phase_code=”C” , status_code=”X” where request_id=”’||req_id||”’;
Conclusion:
If the program has ran with the same parameters and completed normal, we can directly terminate the program,
Query to check if the same conc request has ran with the same parameters,
set pages 1000 lines 1000 pagesize 1000;
select a.request_id ,decode(a.phase_code,’R’,’Running’,’Normal’) Phase,
to_char(a.actual_start_date,’DD-MON-RRRR HH24:MI:SS’)
Start_time , substr(b.user_concurrent_program_name,1,40) Program, a.argument_text, c.user_name
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c
where a.concurrent_program_id = b.concurrent_program_id
and a.status_code = ‘R’ and a.requested_by=c.user_id order by Start_time;
