Oracle Apps: Helpful Queries on Concurrent Programs / Concurrent Requests

The intention if this post is to post some of the queries which will be helpful when querying on Concurrent Programs / Request.

#1. Query to find the Run Timing for the Concurrent Request.

select fcr.request_id  
      ,fcp.user_concurrent_program_name  
      ,fcp.concurrent_program_name  
      ,fcr.request_date  
      ,fcr.actual_start_date  
      ,fcr.actual_completion_date  
      ,(  
        (floor(((fcr.actual_completion_date - fcr.actual_start_date)*24))      || 'hr ') ||  
        (floor(((fcr.actual_completion_date - fcr.actual_start_date)*24*60))   || 'min ')||  
        (round(((fcr.actual_completion_date - fcr.actual_start_date)*24*60*60))|| 'sec')  
       ) duration  
      ,fcr.status_code  
      ,flvs.meaning status  
      ,fcr.phase_code  
      ,flvp.meaning phase  
      ,fcr.completion_text  
      ,fcr.argument_text  
      ,fcr.logfile_name  
      ,fcr.outfile_name  
from apps.fnd_concurrent_programs_vl fcp  
    ,apps.fnd_concurrent_requests    fcr  
    ,apps.fnd_lookup_values          flvs  
    ,apps.fnd_lookup_values          flvp  
where fcr.concurrent_program_id = fcp.concurrent_program_id(+)  
and   flvs.lookup_code          = fcr.status_code  
and   flvs.lookup_type          = 'CP_STATUS_CODE'  
and   flvs.language             = 'US'  
and   flvs.view_application_id  = 0  
and   flvp.lookup_code          = fcr.phase_code  
and   flvp.lookup_type          = 'CP_PHASE_CODE'  
and   flvp.language             = 'US'  
and   flvp.view_application_id  = 0;  

#2. Query to find the details of the Scheduled Concurrent Requests & Request Sets including the programs under the Request Set.

select request_id  
      ,conc_prog_name  
      ,params  
      ,prog_schedule_type  
      ,prog_schedule  
      ,user_name  
      ,requested_start_date   
 from (  
    select fcr.request_id  
          ,1 seq  
          ,decode(fcpt.user_concurrent_program_name,  
                  'Report Set','Report Set:' || fcr.description,  
                  fcpt.user_concurrent_program_name) conc_prog_name  
          ,(fcr.argument1||','||fcr.argument2||','||fcr.argument3||','||fcr.argument4||','||fcr.argument5||','||  
            fcr.argument6||','||fcr.argument7||','||fcr.argument8||','||fcr.argument9||','||fcr.argument10) params -- Add more parameters if needed or use column 'argument_text'  
          ,nvl2(fcr.resubmit_interval,'Periodically',nvl2(fcr.release_class_id, 'On Specific Days', 'Once')) prog_schedule_type  
          ,(case nvl2(fcr.resubmit_interval,'PERIODICALLY',nvl2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE'))  
                 when 'PERIODICALLY'  
                     then 'EVERY ' || fcr.resubmit_interval || ' ' || fcr.resubmit_interval_unit_code || ' FROM ' ||fcr.resubmit_interval_type_code || ' OF PREV RUN'  
                 when 'ONCE'  
                     then 'AT :' ||to_char(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI')  
                 else  
                     'EVERY: ' || fcrc.class_info  
            end) prog_schedule  
          ,fu.user_name user_name  
          ,to_char(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI') requested_start_date  
     from apps.fnd_concurrent_programs_tl fcpt  
         ,apps.fnd_concurrent_requests    fcr  
         ,apps.fnd_user                   fu  
         ,apps.fnd_conc_release_classes   fcrc  
     where fcpt.application_id        = fcr.program_application_id  
     and   fcpt.concurrent_program_id = fcr.concurrent_program_id  
     and   fcr.requested_by           = fu.user_id  
     and   fcr.phase_code             = 'P'  
     and   fcr.requested_start_date   > sysdate  
     and   fcpt.language              = 'US'  
     and   fcrc.release_class_id(+)   = fcr.release_class_id  
     and   fcrc.application_id(+)     = fcr.release_class_app_id  
     union  
     select fcr.request_id  
           ,2 seq  
           ,'-->' || fcp.user_concurrent_program_name conc_prog_name  
           ,(frr.argument1||','||frr.argument2||','||frr.argument3||','||frr.argument4||','||frr.argument5||','||  
             frr.argument6||','||frr.argument7||','||frr.argument8||','||frr.argument9||','||frr.argument10) params -- Add more parameters if needed  
           ,null prog_schedule_type  
           ,null prog_schedule  
           ,null user_name  
           ,null requested_start_date  
     from apps.fnd_concurrent_programs_tl fcpt
         ,apps.fnd_concurrent_requests    fcr
         ,apps.fnd_user                   fu
         ,apps.fnd_conc_release_classes   fcrc  
         ,apps.fnd_run_requests           frr
         ,apps.fnd_concurrent_programs_tl fcp  
     where fcpt.application_id               = fcr.program_application_id  
     and   fcpt.concurrent_program_id        = fcr.concurrent_program_id  
     and   fcr.requested_by                  = fu.user_id  
     and   fcr.phase_code                    = 'P'  
     and   fcr.requested_start_date          > sysdate  
     and   fcpt.language                     = 'US'  
     and   fcrc.release_class_id(+)          = fcr.release_class_id  
     and   fcrc.application_id(+)            = fcr.release_class_app_id  
     and   fcpt.user_concurrent_program_name = 'Report Set'  
     and   frr.parent_request_id             = fcr.request_id  
     and   frr.concurrent_program_id         = fcp.concurrent_program_id  
 ) qrslt  
 order by request_id,seq;  

Feel free to point out if anything is missing/wrong in this blog.