Query to find child concurrent requests

Query to find child concurrent requests

In this post , We will be discuss about Query to find child concurrent requests. This sql query will help to extract the list of child concurrent program requests in oracle. We will get the parent Concurrent program and the child concurrent programs submitted under the parent one. This is one of the important sql query to find child concurrent request details. Here below is the complete detail about Query to find child concurrent requests.

4 Important Tables used by  Query to find child concurrent requests

1.fnd_concurrent_requests
2.fnd_concurrent_requests
3.fnd_concurrent_programs
4.fnd_concurrent_programs_tl

Detail SQL Query to find child concurrent requests

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",
(fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed"
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;

Query to find child concurrent requests