Oracle provides a very useful script for analyzing the Concurrent Request Id and get detailed Information. The script can also be downloaded from Oracle support Doc ID 134035.1.
Download

 USAGE: 
sqlplus apps_user/apps_passwd @analyzereq request_id

 EXAMPLE:    
sqlplus apps/apps @analyzereq 123456


Script:

12.2 EBS

set serveroutput on format wrapped
set feedback off
set verify off
set heading off
set timing off
set linesize 150

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
REM dbdrv: none

prompt

DECLARE

req_id number(15) := &1;

PROCEDURE print_mgrs(p_req_id number) AS

CURSOR c_mgrs(rid number) IS
SELECT user_concurrent_queue_name name, fcwr.running_processes active,
decode(fcwr.control_code, 'A', fl.meaning,
'D', fl.meaning,
'E', fl.meaning,
'N', fl.meaning,
'R', fl.meaning,
'T', fl.meaning,
'U', fl.meaning,
'V', fl.meaning,
'X', fl.meaning,
NULL, 'Running',
'** Unknown Status **') status
FROM fnd_concurrent_queues_vl fcqv, fnd_concurrent_worker_requests fcwr, fnd_lookups fl
WHERE fcwr.request_id = rid
AND fcwr.concurrent_queue_id = fcqv.concurrent_queue_id
AND fcwr.concurrent_queue_id not in (1, 4)
AND fl.lookup_code (+) = fcwr.control_code
AND fl.lookup_type (+) = 'CP_CONTROL_CODE';

BEGIN

for mgr_rec in c_mgrs(p_req_id) loop
DBMS_OUTPUT.PUT_LINE('- ' || mgr_rec.name || ' | Status: ' || mgr_rec.status || ' (' || mgr_rec.active || ' active processes)');
end loop;

END print_mgrs;

PROCEDURE analyze_request(p_req_id number) AS

reqinfo fnd_concurrent_requests%ROWTYPE;
proginfo fnd_concurrent_programs_vl%ROWTYPE;

cnt number;
c_status fnd_lookups.meaning%TYPE;
m_buf fnd_lookups.meaning%TYPE;
conc_prog_name fnd_concurrent_programs.concurrent_program_name%TYPE;
exe_method_code fnd_concurrent_programs_vl.execution_method_code%TYPE;
conc_app_name fnd_application_vl.application_name%TYPE;
tmp_id number(15);
tmp_status fnd_concurrent_requests.status_code%TYPE;
tmp_date date;
conc_app_id fnd_concurrent_requests.program_application_id%TYPE;
conc_id fnd_concurrent_requests.concurrent_program_id%TYPE;
conc_cd_id fnd_concurrent_requests.cd_id%TYPE;
v_enabled_flag fnd_concurrent_programs.enabled_flag%TYPE;
conflict_domain fnd_conflicts_domain.user_cd_name%TYPE;
parent_id number(15);
resp_name varchar2(100);
rclass_name fnd_concurrent_request_class.request_class_name%TYPE;
exe_file_name fnd_executables.execution_file_name%TYPE;
cur_ed_name fnd_concurrent_requests.edition_name%TYPE;

c_user fnd_user.user_name%TYPE;
last_user fnd_user.user_name%TYPE;

fcd_phase varchar2(48);
fcd_status varchar2(48);

traid fnd_concurrent_requests.program_application_id%TYPE;
trcpid fnd_concurrent_requests.concurrent_program_id%TYPE;

icount number;
ireqid fnd_concurrent_requests.request_id%TYPE;
pcode fnd_concurrent_requests.phase_code%TYPE;
scode fnd_concurrent_requests.status_code%TYPE;

live_child boolean;
mgr_defined boolean;
mgr_active boolean;
mgr_workshift boolean;
mgr_running boolean;
run_alone boolean;
reqlimit boolean := false;

mgrname fnd_concurrent_queues_vl.user_concurrent_queue_name%TYPE;
mgrid number;
filename varchar2(255);

pub_fname fnd_conc_req_outputs.file_name%TYPE;
pub_fsize fnd_conc_req_outputs.file_size%TYPE;
pub_ftype fnd_conc_req_outputs.file_type%TYPE;

qcf fnd_concurrent_programs.queue_control_flag%TYPE;

sep varchar2(200) := '------------------------------------------------------';

REQ_NOTFOUND exception;

CURSOR c_wait IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE parent_request_id = p_req_id;

CURSOR c_inc IS
SELECT to_run_application_id, to_run_concurrent_program_id
FROM fnd_concurrent_program_serial
WHERE running_application_id = conc_app_id
AND running_concurrent_program_id = conc_id;

CURSOR c_ireqs IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code = 'R'
AND program_application_id = traid
AND concurrent_program_id = trcpid
AND cd_id = conc_cd_id;

CURSOR c_userreqs(uid number, s date) IS
SELECT request_id, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') start_date,
phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code IN ('R', 'P')
AND requested_by = uid
AND requested_start_date < s
AND hold_flag = 'N';

BEGIN

BEGIN
SELECT *
INTO reqinfo
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise REQ_NOTFOUND;
END;

DBMS_OUTPUT.PUT_LINE('Analyzing request '||req_id||':');
DBMS_OUTPUT.PUT_LINE(sep);

-- Program information
DBMS_OUTPUT.PUT_LINE('Program information:');
DBMS_OUTPUT.NEW_LINE;

SELECT fvl.*
INTO proginfo
FROM fnd_concurrent_programs_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.concurrent_program_id = fvl.concurrent_program_id
AND fcr.program_application_id = fvl.application_id;

DBMS_OUTPUT.PUT_LINE('Program: '|| proginfo.user_concurrent_program_name || ' (' || proginfo.concurrent_program_name || ')');

SELECT nvl(application_name, '-- UNKNOWN APPLICATION --')
INTO conc_app_name
FROM fnd_application_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.program_application_id = fvl.application_id;

DBMS_OUTPUT.PUT_LINE('Application: '||conc_app_name);

SELECT nvl(meaning, 'UNKNOWN')
INTO m_buf
FROM fnd_lookups
WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lookup_code = proginfo.execution_method_code;

SELECT nvl(execution_file_name, 'NONE')
INTO exe_file_name
FROM fnd_executables
WHERE application_id = proginfo.executable_application_id
AND executable_id = proginfo.executable_id;

DBMS_OUTPUT.PUT_LINE('Executable type: ' || m_buf || ' (' || proginfo.execution_method_code || ')');
DBMS_OUTPUT.PUT_LINE('Executable file name or procedure: ' || exe_file_name);
DBMS_OUTPUT.PUT_LINE('Run alone flag: ' || proginfo.run_alone_flag);
DBMS_OUTPUT.PUT_LINE('SRS flag: ' || proginfo.srs_flag);
DBMS_OUTPUT.PUT_LINE('NLS compliant: ' || proginfo.nls_compliant);
DBMS_OUTPUT.PUT_LINE('Output file type: ' || proginfo.output_file_type);

if proginfo.concurrent_class_id is not null then
select request_class_name
into rclass_name
from fnd_concurrent_request_class
where application_id = proginfo.class_application_id
and request_class_id = proginfo.concurrent_class_id;

DBMS_OUTPUT.PUT_LINE('Request type: ' || rclass_name);
end if;

if proginfo.execution_options is not null then
DBMS_OUTPUT.PUT_LINE('Execution options: ' || proginfo.execution_options);
end if;

if proginfo.enable_trace = 'Y' then
DBMS_OUTPUT.PUT_LINE('SQL Trace has been enabled for this program.');
end if;

DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE(sep);

-- Submission information
DBMS_OUTPUT.PUT_LINE('Submission information:');
DBMS_OUTPUT.NEW_LINE;

begin
SELECT user_name into c_user from fnd_user
where user_id = reqinfo.requested_by;
exception
when no_data_found then
c_user := '-- UNKNOWN USER --';
end;

begin
SELECT user_name into last_user from fnd_user
WHERE user_id = reqinfo.last_updated_by;
exception
when no_data_found then
last_user := '-- UNKNOWN USER --';
end;

DBMS_OUTPUT.PUT_LINE('It was submitted by user: '||c_user);
SELECT responsibility_name
INTO resp_name
FROM fnd_responsibility_vl
WHERE responsibility_id = reqinfo.responsibility_id
AND application_id = reqinfo.responsibility_application_id;

DBMS_OUTPUT.PUT_LINE('Using responsibility: ' || resp_name);
DBMS_OUTPUT.PUT_LINE('It was submitted on: ' || to_char(reqinfo.request_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('It was requested to start on: '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Parent request id: ' || reqinfo.parent_request_id);
DBMS_OUTPUT.PUT_LINE('Language: ' || reqinfo.nls_language);
DBMS_OUTPUT.PUT_LINE('Territory: ' || reqinfo.nls_territory);
DBMS_OUTPUT.PUT_LINE('NLS_NUMERIC_CHARACTERS: ' || reqinfo.nls_numeric_characters);
DBMS_OUTPUT.PUT_LINE('NLS_SORT: ' || reqinfo.nls_sort);
DBMS_OUTPUT.PUT_LINE('Priority: ' || to_char(reqinfo.priority));
DBMS_OUTPUT.PUT_LINE('Recalculate parameters: ' || nvl(reqinfo.recalc_parameters, 'N'));

if reqinfo.node_name1 is not null then
DBMS_OUTPUT.PUT_LINE('Node affinity: ' || reqinfo.node_name1);
end if;
if reqinfo.connstr1 is not null then
DBMS_OUTPUT.PUT_LINE('Instance affinity: ' || reqinfo.connstr1);
end if;

DBMS_OUTPUT.PUT_LINE('Arguments (' || reqinfo.number_of_arguments || '): ' || reqinfo.argument_text);

c_status := trim(nvl(fnd_conc.get_status(reqinfo.status_code), 'UNKNOWN'));

DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE(sep);

-- Analysis
DBMS_OUTPUT.PUT_LINE('Analysis:');
DBMS_OUTPUT.NEW_LINE;

-- Completed Requests
-------------------------------------------------------------------------------------------------------------
IF reqinfo.phase_code = 'C' THEN

DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has completed with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO START DATE --'));
DBMS_OUTPUT.PUT_LINE('It completed on: '||
nvl(to_char(reqinfo.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO COMPLETION DATE --'));

BEGIN
SELECT user_concurrent_queue_name
INTO mgrname
FROM fnd_concurrent_queues_vl fcq, fnd_concurrent_processes fcp
WHERE fcp.concurrent_process_id = reqinfo.controlling_manager
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id;
DBMS_OUTPUT.PUT_LINE('It was run by manager: ' || mgrname || ' (cpid: ' || reqinfo.controlling_manager || ')');
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT queue_control_flag
INTO qcf
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id
AND application_id = reqinfo.program_application_id;

IF qcf = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is a queue control request, it was run by the ICM');
ELSE
DBMS_OUTPUT.PUT_LINE('It was run by an unknown manager.');
END IF;

END;

DBMS_OUTPUT.PUT_LINE('It ran on node: ' || reqinfo.logfile_node_name);
SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
SELECT nvl(reqinfo.outfile_name, '-- No output file --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);
DBMS_OUTPUT.PUT_LINE('Output file size: ' || nvl(reqinfo.ofile_size, 0));

select count(*)
into cnt
from fnd_conc_req_outputs
where p_req_id = concurrent_request_id;
if cnt > 0 then
select file_name, file_type, file_size
into pub_fname, pub_ftype, pub_fsize
from fnd_conc_req_outputs
where p_req_id = concurrent_request_id
and rownum = 1;
DBMS_OUTPUT.PUT_LINE('It produced published output:');
DBMS_OUTPUT.PUT_LINE(' File: ' || pub_fname);
DBMS_OUTPUT.PUT_LINE(' Size: ' || pub_fsize);
DBMS_OUTPUT.PUT_LINE(' Type: ' || pub_ftype);
end if;

BEGIN
SELECT nvl(logfile_name, '-- No manager log --')
INTO filename
FROM fnd_concurrent_processes
where concurrent_process_id = reqinfo.controlling_manager;
EXCEPTION
WHEN NO_DATA_FOUND THEN
filename := 'Not found';
END;
DBMS_OUTPUT.PUT_LINE('Manager log: ' || filename);

DBMS_OUTPUT.PUT_LINE('It produced completion message: ');
DBMS_OUTPUT.PUT_LINE(nvl(reqinfo.completion_text, '-- NO COMPLETION MESSAGE --'));

-- Running Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'R' THEN

DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is currently running with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO START DATE --'));
BEGIN
SELECT user_concurrent_queue_name
INTO mgrname
FROM fnd_concurrent_queues_vl
WHERE concurrent_queue_id = reqinfo.controlling_manager;
DBMS_OUTPUT.PUT_LINE('It is being run by manager: ' || mgrname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;

SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
SELECT nvl(reqinfo.outfile_name, '-- No output file --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);

IF reqinfo.os_process_id is not null THEN
DBMS_OUTPUT.PUT_LINE('OS process id: ' || reqinfo.os_process_id);
END IF;

IF reqinfo.status_code = 'Z' THEN

-- Waiting request, See what it is waiting on
FOR child in c_wait LOOP

DBMS_OUTPUT.PUT_LINE('It is waiting on request '||
child.request_id||' phase = '|| nvl(fnd_conc.get_phase(child.phase_code), 'UNKNOWN')||
' status = '|| trim(nvl(fnd_conc.get_status(child.status_code), 'UNKNOWN')));
END LOOP;

ELSIF reqinfo.status_code = 'W' THEN

-- Paused, check and see if it is a request set, and if its children are running
SELECT nvl(concurrent_program_name, 'UNKNOWN')
INTO conc_prog_name
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id;

DBMS_OUTPUT.PUT_LINE('A Running/Paused request is waiting on one or more child requests to complete.');
IF conc_prog_name = 'FNDRSSTG' THEN
DBMS_OUTPUT.PUT_LINE('This program appears to be a Request Set Stage.');
END IF;

IF instr(conc_prog_name, 'RSSUB') > 0 THEN
DBMS_OUTPUT.PUT_LINE('This program appears to be a Request Set parent program.');
END IF;

live_child := FALSE;
FOR child in c_wait LOOP

DBMS_OUTPUT.PUT_LINE('It has a child request: '||
child.request_id||' (phase = '|| nvl(fnd_conc.get_phase(child.phase_code), 'UNKNOWN')||
' - status = '|| trim(nvl(fnd_conc.get_status(child.status_code), 'UNKNOWN'))||')');
IF child.phase_code <> 'C' THEN
live_child := TRUE;
END IF;

END LOOP;

IF live_child = FALSE THEN
DBMS_OUTPUT.PUT_LINE('This request has no child requests that are still running. You may need to wake this request up manually.');
END IF;
END IF;

-- Pending Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'P' THEN

DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is in phase "Pending" with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE(' (phase_code = P) (status_code = '||reqinfo.status_code||')');

-- could be a queue control request
SELECT queue_control_flag
INTO qcf
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id
AND application_id = reqinfo.program_application_id;

IF qcf = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is a queue control request');
DBMS_OUTPUT.PUT_LINE('It will be run by the ICM on its next sleep cycle');
GOTO diagnose;
END IF;

-- why is it pending?

-- could be System/Deferred
IF reqinfo.status_code = 'O' and reqinfo.hold_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently on System Deferred status. It will not run until Platform Maintenance has completed.');
DBMS_OUTPUT.PUT_LINE('If there is no current Platform Maintenance cycle then this is an invalid status and the request will not be run without manual intervention.');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Deferred');
GOTO diagnose;
END IF;

-- could be on hold
IF reqinfo.hold_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently on hold. It will not run until the hold is released.');
DBMS_OUTPUT.PUT_LINE('It was placed on hold by: '||last_user||' on '||to_char(reqinfo.last_update_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/On Hold');
GOTO diagnose;
END IF;

-- could be disabled
IF proginfo.enabled_flag = 'N' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently disabled.');
DBMS_OUTPUT.PUT_LINE('The concurrent_program '|| proginfo.user_concurrent_program_name ||' needs to be enabled for this request to run.');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Disabled');
GOTO diagnose;
END IF;

-- could be from a patch edition
SELECT sys_context('userenv', 'current_edition_name')
INTO cur_ed_name
FROM dual;
IF reqinfo.edition_name > cur_ed_name THEN
DBMS_OUTPUT.PUT_LINE('This request was submitted from a higher edition than the current one.');
DBMS_OUTPUT.PUT_LINE('Current edition: '|| cur_ed_name);
DBMS_OUTPUT.PUT_LINE('Request edition: '|| reqinfo.edition_name);
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
GOTO diagnose;
END IF;

-- check to see if it has a bad node affinity
IF reqinfo.node_name1 is not null then
select count(*) into cnt from fnd_concurrent_queues where target_node = reqinfo.node_name1;
IF cnt = 0 then
DBMS_OUTPUT.PUT_LINE('This request seems to have an invalid node affinity. (' || reqinfo.node_name1 || ') and will likely not be run.' );
DBMS_OUTPUT.PUT_LINE('You may need to manually set fnd_concurrent_requests.node_name1 to null for this request to run.');
DBMS_OUTPUT.PUT_LINE('You will need to apply patch 35053717.');
GOTO diagnose;
END IF;

END IF;

-- could be scheduled
IF reqinfo.requested_start_date > sysdate or reqinfo.status_code = 'P' THEN
DBMS_OUTPUT.PUT_LINE('This is a scheduled request.');
DBMS_OUTPUT.PUT_LINE('It is currently scheduled to start running on '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Pending/Scheduled');
GOTO diagnose;
END IF;

-- check queue_method_code
-- unconstrained requests
IF reqinfo.queue_method_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('This request is an unconstrained request. (queue_method_code = I)');
IF reqinfo.status_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Normal" status, ready to be run by the next available manager.');
ELSIF reqinfo.status_code = 'Q' THEN
DBMS_OUTPUT.PUT_LINE('It has a status of "Standby" even though it is unconstrained. It will not be run by any manager.');
ELSIF reqinfo.status_code IN ('A', 'Z') THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.');
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = p_req_id;
IF parent_id = -1 THEN
DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
ELSE
DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code || '. I was not really expecting to see this status.');
END IF;

-- constrained requests
ELSIF reqinfo.queue_method_code = 'B' THEN
DBMS_OUTPUT.PUT_LINE('This request is a constrained request. (queue_method_code = B)');
IF reqinfo.status_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('The Conflict Resolution manager has released this request, and it is in a "Pending/Normal" status.');
DBMS_OUTPUT.PUT_LINE('It is ready to be run by the next available manager.');
ELSIF reqinfo.status_code = 'Q' THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Standby" status. The Conflict Resolution manager will need to release it before it can be run.');
ELSIF reqinfo.status_code IN ('A', 'Z') THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.');
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = p_req_id;
IF parent_id = -1 THEN
DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
ELSE
DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code || '. I was not really expecting to see this status.');
END IF;

-- incompatible programs
SELECT program_application_id, concurrent_program_id, cd_id
INTO conc_app_id, conc_id, conc_cd_id
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;

icount := 0;
FOR progs in c_inc LOOP

traid := progs.to_run_application_id;
trcpid := progs.to_run_concurrent_program_id;

OPEN c_ireqs;
LOOP

FETCH c_ireqs INTO ireqid, pcode, scode;
EXIT WHEN c_ireqs%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Request '|| p_req_id ||' is waiting, or will have to wait, on an incompatible request: '|| ireqid );
DBMS_OUTPUT.PUT_LINE('which has phase = '|| pcode ||' and status = '|| scode);
icount := icount + 1;

END LOOP;
CLOSE c_ireqs;

END LOOP;

IF icount = 0 THEN
DBMS_OUTPUT.PUT_LINE('No running incompatible requests were found for request '||p_req_id);
END IF;

-- could be a runalone itself
IF proginfo.run_alone_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because it is a runalone request.');
END IF;

-- single threaded
IF reqinfo.single_thread_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because the profile option Concurrent: Sequential Requests is set.');
reqlimit := true;
END IF;

-- request limit
IF reqinfo.request_limit = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because the profile option Concurrent: Active Request Limit is set.');
reqlimit := true;
END IF;

IF reqlimit = true THEN
DBMS_OUTPUT.PUT_LINE('This request may have to wait on these requests:');
FOR progs in c_userreqs(reqinfo.requested_by, reqinfo.requested_start_date) LOOP
DBMS_OUTPUT.PUT_LINE('Request id: ' || progs.request_id || ' Requested start date: ' || progs.start_date);
DBMS_OUTPUT.PUT_LINE(' Phase: ' || nvl(fnd_conc.get_phase(progs.phase_code), 'UNKNOWN') || ' Status: ' || trim(nvl(fnd_conc.get_status(progs.status_code), 'UNKNOWN')));
END LOOP;
END IF;

-- error, invalid queue_method_code
ELSE
DBMS_OUTPUT.PUT_LINE('** This request has an invalid queue_method_code of '||reqinfo.queue_method_code);
DBMS_OUTPUT.PUT_LINE('** This request will not be run. You may need to apply patch 739644.');
GOTO diagnose;
END IF;

DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.PUT_LINE('Checking managers available to run this request...');

-- check the managers
fnd_conc.manager_check(p_req_id, reqinfo.cd_id, mgr_defined, mgr_active, mgr_workshift, mgr_running, run_alone);

-- could be a runalone ahead of it
IF run_alone = TRUE THEN
DBMS_OUTPUT.PUT_LINE('There is a runalone request running ahead of this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');

select user_cd_name into conflict_domain from fnd_conflicts_domain
where cd_id = reqinfo.cd_id;

DBMS_OUTPUT.PUT_LINE('Conflict domain = '||conflict_domain);

-- see what is running
begin
select request_id, status_code, actual_start_date
into tmp_id, tmp_status, tmp_date
from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
where fcp.run_alone_flag = 'Y'
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.phase_code = 'R'
and fcr.cd_id = reqinfo.cd_id;

DBMS_OUTPUT.PUT_LINE('This request is waiting for request '||tmp_id||
', which is running with status '|| trim(nvl(fnd_conc.get_status(tmp_status), 'UNKNOWN')));
DBMS_OUTPUT.PUT_LINE('It has been running since: '||
nvl(to_char(tmp_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'));
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('** The runalone flag is set for conflict domain '||conflict_domain||
', but there is no runalone request running');
end;

ELSIF mgr_defined = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There is no manager defined that can run this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
DBMS_OUTPUT.PUT_LINE('Check the specialization rules for each manager to make sure they are defined correctly.');
ELSIF mgr_active = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There are one or more managers defined that can run this request, but none of them are currently active');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- print out which managers can run it and their status
DBMS_OUTPUT.PUT_LINE('These managers are defined to run this request:');
print_mgrs(p_req_id);
ELSIF mgr_workshift = FALSE THEN
DBMS_OUTPUT.PUT_LINE('Right now, there is no manager running in an active workshift that can run this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- display details about the workshifts
ELSIF mgr_running = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There is one or more managers available to run this request, but none of them are running');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- print out which managers can run it and their status
print_mgrs(p_req_id);
ELSE
-- print out the managers available to run it
DBMS_OUTPUT.PUT_LINE('These managers are available to run this request:');
print_mgrs(p_req_id);

END IF;

-- invalid phase code
ELSE
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has an invalid phase_code of "'||reqinfo.phase_code||'"');

END IF;

<<diagnose>>

DBMS_OUTPUT.PUT_LINE(sep);

EXCEPTION
WHEN REQ_NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error number ' || sqlcode || ' has occurred.');
DBMS_OUTPUT.PUT_LINE('Cause: ' || sqlerrm);

END analyze_request;

BEGIN

analyze_request(req_id);

END;
/

prompt

exit
/

12.1 EBS:


set serveroutput on format wrapped
set feedback off
set verify off
set heading off
set timing off
set linesize 150

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
REM dbdrv: none

prompt

DECLARE

req_id number(15) := &1;

PROCEDURE print_mgrs(p_req_id number) AS

CURSOR c_mgrs(rid number) IS
SELECT user_concurrent_queue_name name, fcwr.running_processes active,
decode(fcwr.control_code, 'A', fl.meaning,
'D', fl.meaning,
'E', fl.meaning,
'N', fl.meaning,
'R', fl.meaning,
'T', fl.meaning,
'U', fl.meaning,
'V', fl.meaning,
'X', fl.meaning,
NULL, 'Running',
'** Unknown Status **') status
FROM fnd_concurrent_queues_vl fcqv, fnd_concurrent_worker_requests fcwr, fnd_lookups fl
WHERE fcwr.request_id = rid
AND fcwr.concurrent_queue_id = fcqv.concurrent_queue_id
AND fcwr.concurrent_queue_id not in (1, 4)
AND fl.lookup_code (+) = fcwr.control_code
AND fl.lookup_type (+) = 'CP_CONTROL_CODE';

BEGIN

for mgr_rec in c_mgrs(p_req_id) loop
DBMS_OUTPUT.PUT_LINE('- ' || mgr_rec.name || ' | Status: ' || mgr_rec.status || ' (' || mgr_rec.active || ' active processes)');
end loop;

END print_mgrs;

PROCEDURE analyze_request(p_req_id number) AS

reqinfo fnd_concurrent_requests%ROWTYPE;
proginfo fnd_concurrent_programs_vl%ROWTYPE;

cnt number;
c_status fnd_lookups.meaning%TYPE;
m_buf fnd_lookups.meaning%TYPE;
conc_prog_name fnd_concurrent_programs.concurrent_program_name%TYPE;
exe_method_code fnd_concurrent_programs_vl.execution_method_code%TYPE;
conc_app_name fnd_application_vl.application_name%TYPE;
tmp_id number(15);
tmp_status fnd_concurrent_requests.status_code%TYPE;
tmp_date date;
conc_app_id fnd_concurrent_requests.program_application_id%TYPE;
conc_id fnd_concurrent_requests.concurrent_program_id%TYPE;
conc_cd_id fnd_concurrent_requests.cd_id%TYPE;
v_enabled_flag fnd_concurrent_programs.enabled_flag%TYPE;
conflict_domain fnd_conflicts_domain.user_cd_name%TYPE;
parent_id number(15);
resp_name varchar2(100);
rclass_name fnd_concurrent_request_class.request_class_name%TYPE;
exe_file_name fnd_executables.execution_file_name%TYPE;

c_user fnd_user.user_name%TYPE;
last_user fnd_user.user_name%TYPE;

fcd_phase varchar2(48);
fcd_status varchar2(48);

traid fnd_concurrent_requests.program_application_id%TYPE;
trcpid fnd_concurrent_requests.concurrent_program_id%TYPE;

icount number;
ireqid fnd_concurrent_requests.request_id%TYPE;
pcode fnd_concurrent_requests.phase_code%TYPE;
scode fnd_concurrent_requests.status_code%TYPE;

live_child boolean;
mgr_defined boolean;
mgr_active boolean;
mgr_workshift boolean;
mgr_running boolean;
run_alone boolean;
reqlimit boolean := false;

mgrname fnd_concurrent_queues_vl.user_concurrent_queue_name%TYPE;
mgrid number;
filename varchar2(255);

pub_fname fnd_conc_req_outputs.file_name%TYPE;
pub_fsize fnd_conc_req_outputs.file_size%TYPE;
pub_ftype fnd_conc_req_outputs.file_type%TYPE;

qcf fnd_concurrent_programs.queue_control_flag%TYPE;

sep varchar2(200) := '------------------------------------------------------';

REQ_NOTFOUND exception;

CURSOR c_wait IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE parent_request_id = p_req_id;

CURSOR c_inc IS
SELECT to_run_application_id, to_run_concurrent_program_id
FROM fnd_concurrent_program_serial
WHERE running_application_id = conc_app_id
AND running_concurrent_program_id = conc_id;

CURSOR c_ireqs IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code = 'R'
AND program_application_id = traid
AND concurrent_program_id = trcpid
AND cd_id = conc_cd_id;

CURSOR c_userreqs(uid number, s date) IS
SELECT request_id, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') start_date,
phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code IN ('R', 'P')
AND requested_by = uid
AND requested_start_date < s
AND hold_flag = 'N';

BEGIN

BEGIN
SELECT *
INTO reqinfo
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise REQ_NOTFOUND;
END;

DBMS_OUTPUT.PUT_LINE('Analyzing request '||req_id||':');
DBMS_OUTPUT.PUT_LINE(sep);

-- Program information
DBMS_OUTPUT.PUT_LINE('Program information:');
DBMS_OUTPUT.NEW_LINE;

SELECT fvl.*
INTO proginfo
FROM fnd_concurrent_programs_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.concurrent_program_id = fvl.concurrent_program_id
AND fcr.program_application_id = fvl.application_id;

DBMS_OUTPUT.PUT_LINE('Program: '|| proginfo.user_concurrent_program_name || ' (' || proginfo.concurrent_program_name || ')');

SELECT nvl(application_name, '-- UNKNOWN APPLICATION --')
INTO conc_app_name
FROM fnd_application_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.program_application_id = fvl.application_id;

DBMS_OUTPUT.PUT_LINE('Application: '||conc_app_name);

SELECT nvl(meaning, 'UNKNOWN')
INTO m_buf
FROM fnd_lookups
WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lookup_code = proginfo.execution_method_code;

SELECT nvl(execution_file_name, 'NONE')
INTO exe_file_name
FROM fnd_executables
WHERE application_id = proginfo.executable_application_id
AND executable_id = proginfo.executable_id;

DBMS_OUTPUT.PUT_LINE('Executable type: ' || m_buf || ' (' || proginfo.execution_method_code || ')');
DBMS_OUTPUT.PUT_LINE('Executable file name or procedure: ' || exe_file_name);
DBMS_OUTPUT.PUT_LINE('Run alone flag: ' || proginfo.run_alone_flag);
DBMS_OUTPUT.PUT_LINE('SRS flag: ' || proginfo.srs_flag);
DBMS_OUTPUT.PUT_LINE('NLS compliant: ' || proginfo.nls_compliant);
DBMS_OUTPUT.PUT_LINE('Output file type: ' || proginfo.output_file_type);

if proginfo.concurrent_class_id is not null then
select request_class_name
into rclass_name
from fnd_concurrent_request_class
where application_id = proginfo.class_application_id
and request_class_id = proginfo.concurrent_class_id;

DBMS_OUTPUT.PUT_LINE('Request type: ' || rclass_name);
end if;

if proginfo.execution_options is not null then
DBMS_OUTPUT.PUT_LINE('Execution options: ' || proginfo.execution_options);
end if;

if proginfo.enable_trace = 'Y' then
DBMS_OUTPUT.PUT_LINE('SQL Trace has been enabled for this program.');
end if;

DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE(sep);

-- Submission information
DBMS_OUTPUT.PUT_LINE('Submission information:');
DBMS_OUTPUT.NEW_LINE;

begin
SELECT user_name into c_user from fnd_user
where user_id = reqinfo.requested_by;
exception
when no_data_found then
c_user := '-- UNKNOWN USER --';
end;

begin
SELECT user_name into last_user from fnd_user
WHERE user_id = reqinfo.last_updated_by;
exception
when no_data_found then
last_user := '-- UNKNOWN USER --';
end;

DBMS_OUTPUT.PUT_LINE('It was submitted by user: '||c_user);
SELECT responsibility_name
INTO resp_name
FROM fnd_responsibility_vl
WHERE responsibility_id = reqinfo.responsibility_id
AND application_id = reqinfo.responsibility_application_id;

DBMS_OUTPUT.PUT_LINE('Using responsibility: ' || resp_name);
DBMS_OUTPUT.PUT_LINE('It was submitted on: ' || to_char(reqinfo.request_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('It was requested to start on: '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Parent request id: ' || reqinfo.parent_request_id);
DBMS_OUTPUT.PUT_LINE('Language: ' || reqinfo.nls_language);
DBMS_OUTPUT.PUT_LINE('Territory: ' || reqinfo.nls_territory);
DBMS_OUTPUT.PUT_LINE('NLS_NUMERIC_CHARACTERS: ' || reqinfo.nls_numeric_characters);
DBMS_OUTPUT.PUT_LINE('NLS_SORT: ' || reqinfo.nls_sort);
DBMS_OUTPUT.PUT_LINE('Priority: ' || to_char(reqinfo.priority));
DBMS_OUTPUT.PUT_LINE('Recalculate parameters: ' || nvl(reqinfo.recalc_parameters, 'N'));

if reqinfo.node_name1 is not null then
DBMS_OUTPUT.PUT_LINE('Node affinity: ' || reqinfo.node_name1);
end if;
if reqinfo.connstr1 is not null then
DBMS_OUTPUT.PUT_LINE('Instance affinity: ' || reqinfo.connstr1);
end if;

DBMS_OUTPUT.PUT_LINE('Arguments (' || reqinfo.number_of_arguments || '): ' || reqinfo.argument_text);

c_status := trim(nvl(fnd_conc.get_status(reqinfo.status_code), 'UNKNOWN'));

DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE(sep);

-- Analysis
DBMS_OUTPUT.PUT_LINE('Analysis:');
DBMS_OUTPUT.NEW_LINE;

-- Completed Requests
-------------------------------------------------------------------------------------------------------------
IF reqinfo.phase_code = 'C' THEN

DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has completed with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO START DATE --'));
DBMS_OUTPUT.PUT_LINE('It completed on: '||
nvl(to_char(reqinfo.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO COMPLETION DATE --'));

BEGIN
SELECT user_concurrent_queue_name
INTO mgrname
FROM fnd_concurrent_queues_vl fcq, fnd_concurrent_processes fcp
WHERE fcp.concurrent_process_id = reqinfo.controlling_manager
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id;
DBMS_OUTPUT.PUT_LINE('It was run by manager: ' || mgrname || ' (cpid: ' || reqinfo.controlling_manager || ')');
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT queue_control_flag
INTO qcf
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id
AND application_id = reqinfo.program_application_id;

IF qcf = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is a queue control request, it was run by the ICM');
ELSE
DBMS_OUTPUT.PUT_LINE('It was run by an unknown manager.');
END IF;

END;

DBMS_OUTPUT.PUT_LINE('It ran on node: ' || reqinfo.logfile_node_name);
SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
SELECT nvl(reqinfo.outfile_name, '-- No output file --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);
DBMS_OUTPUT.PUT_LINE('Output file size: ' || nvl(reqinfo.ofile_size, 0));

select count(*)
into cnt
from fnd_conc_req_outputs
where p_req_id = concurrent_request_id;
if cnt > 0 then
select file_name, file_type, file_size
into pub_fname, pub_ftype, pub_fsize
from fnd_conc_req_outputs
where p_req_id = concurrent_request_id
and rownum = 1;
DBMS_OUTPUT.PUT_LINE('It produced published output:');
DBMS_OUTPUT.PUT_LINE(' File: ' || pub_fname);
DBMS_OUTPUT.PUT_LINE(' Size: ' || pub_fsize);
DBMS_OUTPUT.PUT_LINE(' Type: ' || pub_ftype);
end if;

BEGIN
SELECT nvl(logfile_name, '-- No manager log --')
INTO filename
FROM fnd_concurrent_processes
where concurrent_process_id = reqinfo.controlling_manager;
EXCEPTION
WHEN NO_DATA_FOUND THEN
filename := 'Not found';
END;
DBMS_OUTPUT.PUT_LINE('Manager log: ' || filename);

DBMS_OUTPUT.PUT_LINE('It produced completion message: ');
DBMS_OUTPUT.PUT_LINE(nvl(reqinfo.completion_text, '-- NO COMPLETION MESSAGE --'));

-- Running Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'R' THEN

DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is currently running with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO START DATE --'));
BEGIN
SELECT user_concurrent_queue_name
INTO mgrname
FROM fnd_concurrent_queues_vl
WHERE concurrent_queue_id = reqinfo.controlling_manager;
DBMS_OUTPUT.PUT_LINE('It is being run by manager: ' || mgrname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;

SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
SELECT nvl(reqinfo.outfile_name, '-- No output file --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);

IF reqinfo.os_process_id is not null THEN
DBMS_OUTPUT.PUT_LINE('OS process id: ' || reqinfo.os_process_id);
END IF;

IF reqinfo.status_code = 'Z' THEN

-- Waiting request, See what it is waiting on
FOR child in c_wait LOOP

DBMS_OUTPUT.PUT_LINE('It is waiting on request '||
child.request_id||' phase = '|| nvl(fnd_conc.get_phase(child.phase_code), 'UNKNOWN')||
' status = '|| trim(nvl(fnd_conc.get_status(child.status_code), 'UNKNOWN')));
END LOOP;

ELSIF reqinfo.status_code = 'W' THEN

-- Paused, check and see if it is a request set, and if its children are running
SELECT nvl(concurrent_program_name, 'UNKNOWN')
INTO conc_prog_name
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id;

DBMS_OUTPUT.PUT_LINE('A Running/Paused request is waiting on one or more child requests to complete.');
IF conc_prog_name = 'FNDRSSTG' THEN
DBMS_OUTPUT.PUT_LINE('This program appears to be a Request Set Stage.');
END IF;

IF instr(conc_prog_name, 'RSSUB') > 0 THEN
DBMS_OUTPUT.PUT_LINE('This program appears to be a Request Set parent program.');
END IF;

live_child := FALSE;
FOR child in c_wait LOOP

DBMS_OUTPUT.PUT_LINE('It has a child request: '||
child.request_id||' (phase = '|| nvl(fnd_conc.get_phase(child.phase_code), 'UNKNOWN')||
' - status = '|| trim(nvl(fnd_conc.get_status(child.status_code), 'UNKNOWN'))||')');
IF child.phase_code <> 'C' THEN
live_child := TRUE;
END IF;

END LOOP;

IF live_child = FALSE THEN
DBMS_OUTPUT.PUT_LINE('This request has no child requests that are still running. You may need to wake this request up manually.');
END IF;
END IF;

-- Pending Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'P' THEN

DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is in phase "Pending" with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE(' (phase_code = P) (status_code = '||reqinfo.status_code||')');

-- could be a queue control request
SELECT queue_control_flag
INTO qcf
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id
AND application_id = reqinfo.program_application_id;

IF qcf = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is a queue control request');
DBMS_OUTPUT.PUT_LINE('It will be run by the ICM on its next sleep cycle');
GOTO diagnose;
END IF;

-- why is it pending?

-- could be System/Deferred
IF reqinfo.status_code = 'O' and reqinfo.hold_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently on System Deferred status. It will not run until Platform Maintenance has completed.');
DBMS_OUTPUT.PUT_LINE('If there is no current Platform Maintenance cycle then this is an invalid status and the request will not be run without manual intervention.');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Deferred');
GOTO diagnose;
END IF;

-- could be on hold
IF reqinfo.hold_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently on hold. It will not run until the hold is released.');
DBMS_OUTPUT.PUT_LINE('It was placed on hold by: '||last_user||' on '||to_char(reqinfo.last_update_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/On Hold');
GOTO diagnose;
END IF;

-- could be disabled
IF proginfo.enabled_flag = 'N' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently disabled.');
DBMS_OUTPUT.PUT_LINE('The concurrent_program '|| proginfo.user_concurrent_program_name ||' needs to be enabled for this request to run.');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Disabled');
GOTO diagnose;
END IF;

-- check to see if it has a bad node affinity
IF reqinfo.node_name1 is not null then
select count(*) into cnt from fnd_concurrent_queues where target_node = reqinfo.node_name1;
IF cnt = 0 then
DBMS_OUTPUT.PUT_LINE('This request seems to have an invalid node affinity. (' || reqinfo.node_name1 || ') and will likely not be run.' );
DBMS_OUTPUT.PUT_LINE('You may need to manually set fnd_concurrent_requests.node_name1 to null for this request to run.');
DBMS_OUTPUT.PUT_LINE('You will need to apply patch 35053717.');
GOTO diagnose;
END IF;

END IF;

-- could be scheduled
IF reqinfo.requested_start_date > sysdate or reqinfo.status_code = 'P' THEN
DBMS_OUTPUT.PUT_LINE('This is a scheduled request.');
DBMS_OUTPUT.PUT_LINE('It is currently scheduled to start running on '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Pending/Scheduled');
GOTO diagnose;
END IF;

-- check queue_method_code
-- unconstrained requests
IF reqinfo.queue_method_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('This request is an unconstrained request. (queue_method_code = I)');
IF reqinfo.status_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Normal" status, ready to be run by the next available manager.');
ELSIF reqinfo.status_code = 'Q' THEN
DBMS_OUTPUT.PUT_LINE('It has a status of "Standby" even though it is unconstrained. It will not be run by any manager.');
ELSIF reqinfo.status_code IN ('A', 'Z') THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.');
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = p_req_id;
IF parent_id = -1 THEN
DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
ELSE
DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code || '. I was not really expecting to see this status.');
END IF;

-- constrained requests
ELSIF reqinfo.queue_method_code = 'B' THEN
DBMS_OUTPUT.PUT_LINE('This request is a constrained request. (queue_method_code = B)');
IF reqinfo.status_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('The Conflict Resolution manager has released this request, and it is in a "Pending/Normal" status.');
DBMS_OUTPUT.PUT_LINE('It is ready to be run by the next available manager.');
ELSIF reqinfo.status_code = 'Q' THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Standby" status. The Conflict Resolution manager will need to release it before it can be run.');
ELSIF reqinfo.status_code IN ('A', 'Z') THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually indicates a child request waiting for the parent to release it.');
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = p_req_id;
IF parent_id = -1 THEN
DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
ELSE
DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code || '. I was not really expecting to see this status.');
END IF;

-- incompatible programs
SELECT program_application_id, concurrent_program_id, cd_id
INTO conc_app_id, conc_id, conc_cd_id
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;

icount := 0;
FOR progs in c_inc LOOP

traid := progs.to_run_application_id;
trcpid := progs.to_run_concurrent_program_id;

OPEN c_ireqs;
LOOP

FETCH c_ireqs INTO ireqid, pcode, scode;
EXIT WHEN c_ireqs%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Request '|| p_req_id ||' is waiting, or will have to wait, on an incompatible request: '|| ireqid );
DBMS_OUTPUT.PUT_LINE('which has phase = '|| pcode ||' and status = '|| scode);
icount := icount + 1;

END LOOP;
CLOSE c_ireqs;

END LOOP;

IF icount = 0 THEN
DBMS_OUTPUT.PUT_LINE('No running incompatible requests were found for request '||p_req_id);
END IF;

-- could be a runalone itself
IF proginfo.run_alone_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because it is a runalone request.');
END IF;

-- single threaded
IF reqinfo.single_thread_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because the profile option Concurrent: Sequential Requests is set.');
reqlimit := true;
END IF;

-- request limit
IF reqinfo.request_limit = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because the profile option Concurrent: Active Request Limit is set.');
reqlimit := true;
END IF;

IF reqlimit = true THEN
DBMS_OUTPUT.PUT_LINE('This request may have to wait on these requests:');
FOR progs in c_userreqs(reqinfo.requested_by, reqinfo.requested_start_date) LOOP
DBMS_OUTPUT.PUT_LINE('Request id: ' || progs.request_id || ' Requested start date: ' || progs.start_date);
DBMS_OUTPUT.PUT_LINE(' Phase: ' || nvl(fnd_conc.get_phase(progs.phase_code), 'UNKNOWN') || ' Status: ' || trim(nvl(fnd_conc.get_status(progs.status_code), 'UNKNOWN')));
END LOOP;
END IF;

-- error, invalid queue_method_code
ELSE
DBMS_OUTPUT.PUT_LINE('** This request has an invalid queue_method_code of '||reqinfo.queue_method_code);
DBMS_OUTPUT.PUT_LINE('** This request will not be run. You may need to apply patch 739644.');
GOTO diagnose;
END IF;

DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.PUT_LINE('Checking managers available to run this request...');

-- check the managers
fnd_conc.manager_check(p_req_id, reqinfo.cd_id, mgr_defined, mgr_active, mgr_workshift, mgr_running, run_alone);

-- could be a runalone ahead of it
IF run_alone = TRUE THEN
DBMS_OUTPUT.PUT_LINE('There is a runalone request running ahead of this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');

select user_cd_name into conflict_domain from fnd_conflicts_domain
where cd_id = reqinfo.cd_id;

DBMS_OUTPUT.PUT_LINE('Conflict domain = '||conflict_domain);

-- see what is running
begin
select request_id, status_code, actual_start_date
into tmp_id, tmp_status, tmp_date
from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
where fcp.run_alone_flag = 'Y'
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.phase_code = 'R'
and fcr.cd_id = reqinfo.cd_id;

DBMS_OUTPUT.PUT_LINE('This request is waiting for request '||tmp_id||
', which is running with status '|| trim(nvl(fnd_conc.get_status(tmp_status), 'UNKNOWN')));
DBMS_OUTPUT.PUT_LINE('It has been running since: '||
nvl(to_char(tmp_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'));
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('** The runalone flag is set for conflict domain '||conflict_domain||
', but there is no runalone request running');
end;

ELSIF mgr_defined = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There is no manager defined that can run this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
DBMS_OUTPUT.PUT_LINE('Check the specialization rules for each manager to make sure they are defined correctly.');
ELSIF mgr_active = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There are one or more managers defined that can run this request, but none of them are currently active');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- print out which managers can run it and their status
DBMS_OUTPUT.PUT_LINE('These managers are defined to run this request:');
print_mgrs(p_req_id);
ELSIF mgr_workshift = FALSE THEN
DBMS_OUTPUT.PUT_LINE('Right now, there is no manager running in an active workshift that can run this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- display details about the workshifts
ELSIF mgr_running = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There is one or more managers available to run this request, but none of them are running');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- print out which managers can run it and their status
print_mgrs(p_req_id);
ELSE
-- print out the managers available to run it
DBMS_OUTPUT.PUT_LINE('These managers are available to run this request:');
print_mgrs(p_req_id);

END IF;

-- invalid phase code
ELSE
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has an invalid phase_code of "'||reqinfo.phase_code||'"');

END IF;

<<diagnose>>

DBMS_OUTPUT.PUT_LINE(sep);

EXCEPTION
WHEN REQ_NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error number ' || sqlcode || ' has occurred.');
DBMS_OUTPUT.PUT_LINE('Cause: ' || sqlerrm);

END analyze_request;

BEGIN

analyze_request(req_id);

END;
/

prompt

exit
/


Reference: