Concurrent Requests – Hierarchy

Often if you look in the fnd_concurrent_requests table you’ll see that for some requests, the parent_request_id column is populated.

This can be the case if a concurrent request is part of a Request Set, where a series of jobs run, one after the other.

At other times, one request can launch / or spawn child requests. A common example in Release 12 of such a request is the Create Accounting job.

You can use the Connect By Prior condition of a Hierarchical Query to extract the hierarchy of a set of requests.

For example – this is sample output for the Create Accounting job:

Here are the 2 versions of the SQL, one using Oracle syntax, the other using ANI syntax.

Oracle Syntax SQL

-- ##############################################################################
--      HIERARCHY
-- ############################################################################*/

SELECT   LPAD(' ', (LEVEL - 1) * 10, ' ') || fcr.request_id id
       , LEVEL
       , NVL(fcr.description, fcpt.user_concurrent_program_name) job_name
       , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
       , DECODE (fcr.status_code , 'A', 'Waiting' , 'B', 'Resuming' , 'C', 'Normal' , 'D', 'Cancelled' , 'E', 'Error' , 'F', 'Scheduled' , 'G', 'Warning' , 'H', 'On Hold' , 'I', 'Normal' , 'M', 'No Manager' , 'Q', 'Standby' , 'R', 'Normal' , 'S', 'Suspended' , 'T', 'Terminating' , 'U', 'Disabled' , 'W', 'Paused' , 'X', 'Terminated' , 'Z', 'Waiting') status
       , fcr.actual_start_date start_ 
       , fcr.actual_completion_date end_
       , fcr.argument_text
       , fu.user_name
       , fcr.completion_text
    FROM applsys.fnd_concurrent_requests fcr
       , applsys.fnd_user fu
       , applsys.fnd_concurrent_programs_tl fcpt
   WHERE fcr.requested_by = fu.user_id
     AND fcr.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fcpt.application_id
     AND fcr.phase_code != 'P'   
START WITH fcr.request_id = 20491922
CONNECT BY PRIOR fcr.request_id = fcr.parent_request_id;

ANSI Syntax SQL

-- ##############################################################################
--       HIERARCHY
-- ############################################################################*/

SELECT   LPAD(' ', (LEVEL - 1) * 10, ' ') || fcr.request_id id
       , LEVEL
       , NVL(fcr.description, fcpt.user_concurrent_program_name) job_name
       , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
       , DECODE (fcr.status_code , 'A', 'Waiting' , 'B', 'Resuming' , 'C', 'Normal' , 'D', 'Cancelled' , 'E', 'Error' , 'F', 'Scheduled' , 'G', 'Warning' , 'H', 'On Hold' , 'I', 'Normal' , 'M', 'No Manager' , 'Q', 'Standby' , 'R', 'Normal' , 'S', 'Suspended' , 'T', 'Terminating' , 'U', 'Disabled' , 'W', 'Paused' , 'X', 'Terminated' , 'Z', 'Waiting') status
       , fcr.actual_start_date start_ 
       , fcr.actual_completion_date end_
       , fcr.argument_text
       , fcr.request_type
       , fu.user_name
       , (replace(replace(fcr.completion_text,chr(10),''),chr(13),' ')) completion_text
    FROM applsys.fnd_concurrent_requests fcr
    JOIN applsys.fnd_user fu ON fcr.requested_by = fu.user_id
    JOIN applsys.fnd_concurrent_programs_tl fcpt ON fcr.concurrent_program_id = fcpt.concurrent_program_id
                                                AND fcr.program_application_id = fcpt.application_id
   WHERE fcr.phase_code != 'P'
START WITH fcr.request_id = 20491922
CONNECT BY PRIOR fcr.request_id = fcr.parent_request_id;

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*