Query to find the Parent and Child Concurrent Request with Run Details and Timings

Query:

SELECT
fcr.request_id "Child Request ID",
PARENT_REQUEST_ID "Parent Request ID",
fcptl.user_concurrent_program_name"Parent Program Name",
fcr.phase_code,
fcr.status_code,
to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",
round((fcr.actual_completion_date - fcr.actual_start_date)*1440,2) "Elapsed Mins"
FROM (SELECT
fcr1.request_id
FROM apps.fnd_concurrent_requests fcr1
WHERE 1=1
START WITH fcr1.request_id = &Parent_request_id
CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcptl
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'
ORDER BY 1;



If you like please follow and comment