R12: SQL Script to collect concurrent request IDs, timings, logs for MRP and Data Collection

I use the below SQL when I have the parent request id for MRP or Data collection program. The sql can be used with any program that triggers child requests.

MRP is usually executed in Oracle E-Business Suite OLTP instance where as Data Collection are kicked off from PLAN instance.

Note: You must know the parent request ID.


set verify off
set heading on
spool requests.out
Accept input_request_id Prompt 'Enter the parent request id : '
column conc_prg_name format A37
column req_id format 99999999
column start_time format A12
column end_time format A12
column trace_id format a8

select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
fcr.request_id req_id,
--fcptl.user_concurrent_program_name cprg_name,
decode (fcr.description,null, fcptl.user_concurrent_program_name,
fcr.description || ' (' || fcptl.user_concurrent_program_name || ')') conc_prg_name,
flp.meaning "Phase",--fcr.phase_code,
fls.meaning  "Status", -- fcr.status_code
to_char(fcr.actual_start_date,'dd-mon HH24:MI') start_time,
to_char(fcr.actual_completion_date, 'dd-mon HH24:MI') end_time,
round((fcr.actual_completion_date-fcr.actual_start_date)*24*60,0) "Time Taken(In Min)",
fcr.oracle_process_id trace_id
from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
fcr1.request_id
from fnd_concurrent_requests fcr1
where 1=1
start with fcr1.request_id = &input_request_id
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_lookups flp,
fnd_lookups fls
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 fcr.phase_code = flp.lookup_code
and fcr.status_code = fls.lookup_code
and flp.lookup_type = 'CP_PHASE_CODE'
and fls.lookup_type = 'CP_STATUS_CODE'
order by 1;


prompt
prompt END OF FILE
spool off
set verify on


Reference:
Script for Getting Correct Request IDs and their Corresponding Logs for Plan and Collection Run (Doc ID 262276.1)