This Excel-Out query allows you to find all the requests submitted from a request set and/or all children spawned from inside the body of a request, including grandchildren, grand-grandchildren, etc. The query syntax uses hierarchical query logic to find all the branches of the query tree, regardless of how they were submitted.
The Excel-Out p_parameter01 is the ID of the top concurrent request that initiated the run. Use the standard '10/Number' value set to define it. I find it particularly useful for diagnosing issues with my ASCP plan run as I have over 200 requests submitted and the report shows me details of the execution , including sequence, duration, start, end, concurrent manager used, completion status and all the parameters for each request.
Since the Default Value field in concurrent request parameters where we paste the query allows only 2000 characters, you need to split the select statement into 2, and paste it into parameters 991 and 992. They will be concatenated together during fun time. I convenient place to split is after the first decode statement and I marked that in the comments.
select
cque.USER_CONCURRENT_QUEUE_NAME conc_manager,
fcr.request_id "Request ID",
fcr.requested_by "User",
substr(DECODE (FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
decode(trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date)*24, 24 ) ),0,null,trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date)*24, 24 ) )||':')||
lpad( trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date)*24*60, 60 ) ),2,'0')||':'||
lpad(trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date)*24*60*60, 60 ) ),2,'0') "Run time in mi:ss",
fcr.oracle_process_id "Trace File ID" ,
decode(fcr.phase_code,'R', 'Running'
,'C', 'Completed'
,'P', 'Pending'
,fcr.phase_code) phase_code,
decode(fcr.status_code,'T', 'Terminating'
,'X', 'Terminated'
,'C', 'Normal'
,'I', 'Scheduled'
,'R', 'Normal'
,'G', 'Warning'
,'W', 'Paused'
,'E','Error'
,fcr.status_code) status_code,
to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",
/*break the query after here, paste the rest into parameter 992*/
decode(trunc( mod( (fcr.actual_start_date - fcr.request_date)*24, 24 ) ),0,null,trunc( mod( (fcr.actual_start_date - fcr.request_date)*24, 24 ) )||':')||
lpad( trunc( mod( (fcr.actual_start_date - fcr.request_date)*24*60, 60 ) ),2,'0')||':'||
lpad(trunc( mod( (fcr.actual_start_date - fcr.request_date)*24*60*60, 60 ) ),2,'0') "Delay",
to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",
to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
fcr.argument_text "Parameters"
from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
fcr1.request_id
from fnd_concurrent_requests fcr1
where 1=1
start with fcr1.request_id = p_parameter01
connect by prior fcr1.request_id = fcr1.parent_request_id) x,
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl,
fnd_concurrent_queues_vl cque,
fnd_concurrent_processes cproc
where fcr.request_id = x.request_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcptl.application_id
and fcp.concurrent_program_id = fcptl.concurrent_program_id
and fcptl.language = 'US'
and cproc.CONCURRENT_QUEUE_ID = cque.CONCURRENT_QUEUE_ID
and cproc.CONCURRENT_PROCESS_ID = fcr.controlling_manager
order by
decode(fcr.status_code,'E',1,2), decode(fcr.request_type,'S',1,2),
fcr.actual_completion_date
No comments:
Post a Comment