Last Run time taken by request, program

SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      --AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
      and pt.user_concurrent_program_name = 'XXX: Program Name'
order by
      f.request_id desc;

For checking last run of a Concurrent Program along with Processed time

SELECT DISTINCT c.user_concurrent_program_name, 
Round(( ( SYSDATE - a.actual_start_date ) * 24 * 60 * 60 / 60 ), 2) AS 
Process_time, 
a.request_id, 
a.parent_request_id, 
(SELECT d.sid 
 FROM   apps.fnd_concurrent_requests f, 
        apps.fnd_concurrent_processes b, 
        v$process c, 
        v$session d 
 WHERE  f.controlling_manager = b.concurrent_process_id 
        AND c.pid = b.oracle_process_id 
        AND b.session_id = d.audsid 
        AND f.request_id = a.request_id 
        AND f.phase_code = 'R')                                     SID#, 
a.request_date, 
a.actual_start_date, 
a.actual_completion_date, 
( a.actual_completion_date - a.request_date ) * 24 * 60 * 60        AS 
                end_to_end, 
( a.actual_start_date - a.request_date ) * 24 * 60 * 60             AS lag_time, 
d.user_name, 
a.phase_code, 
a.status_code, 
a.argument_text, 
a.priority 
FROM   apps.fnd_concurrent_requests a, 
       apps.fnd_concurrent_programs b, 
       apps.fnd_concurrent_programs_tl c, 
       apps.fnd_user d 
WHERE  a.concurrent_program_id = b.concurrent_program_id 
       AND b.concurrent_program_id = c.concurrent_program_id 
       AND a.requested_by = d.user_id 
       AND status_code = 'R' 
ORDER  BY process_time DESC; 

For checking the concurrent programs running currently with Details of Processed time-- and Start Date

SELECT DISTINCT c.user_concurrent_program_name, 
Round(( ( SYSDATE - a.actual_start_date ) * 24 * 60 * 60 / 60 ), 2) AS 
Process_time, 
a.request_id, 
a.parent_request_id, 
a.request_date, 
a.actual_start_date, 
a.actual_completion_date, 
( a.actual_completion_date - a.request_date ) * 24 * 60 * 60        AS 
                end_to_end, 
( a.actual_start_date - a.request_date ) * 24 * 60 * 60             AS lag_time, 
d.user_name, 
a.phase_code, 
a.status_code, 
a.argument_text, 
a.priority 
FROM   apps.fnd_concurrent_requests a, 
       apps.fnd_concurrent_programs b, 
       apps.fnd_concurrent_programs_tl c, 
       apps.fnd_user d 
WHERE  a.concurrent_program_id = b.concurrent_program_id 
       AND b.concurrent_program_id = c.concurrent_program_id 
       AND a.requested_by = d.user_id 
       AND status_code = 'R' 
ORDER  BY process_time DESC; 

For checking the locks in concurrent jobs - Oracle Database (EBS) Apps

SELECT Decode(request, 0, 'Holder: ', 
                       'Waiter: ') 
       || sid sess, 
       inst_id, 
       id1, 
       id2, 
       lmode, 
       request, 
       type 
FROM   gv$lock 
WHERE  ( id1, id2, type ) IN (SELECT id1, 
                                     id2, 
                                     type 
                              FROM   gv$lock 
                              WHERE  request > 0) 
ORDER  BY id1, 
          request; 

Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name

SELECT b.user_concurrent_program_name, 
       b.concurrent_program_name, 
       a.user_executable_name, 
       Decode (a.execution_method_code, 'I', 'PL/SQL Stored Procedure', 
                                        'H', 'Host', 
                                        'S', 'Immediate', 
                                        'J', 'Java Stored Procedure', 
                                        'K', 'Java concurrent program', 
                                        'M', 'Multi Language Function', 
                                        'P', 'Oracle reports', 
                                        'B', 'Request Set Stage Function', 
                                        'A', 'Spawned', 
                                        'L', 'SQL*Loader', 
                                        'Q', 'SQL*Plus', 
                                        'E', 'Pearl concurrent Programm', 
                                        'Unkown Type') TYPE, 
       a.execution_file_name, 
       a.execution_file_path, 
       a.application_name, 
       c.basepath 
FROM   fnd_executables_form_v a, 
       fnd_concurrent_programs_vl b, 
       fnd_application c 
WHERE  a.application_id = c.application_id 
       AND a.executable_id = b.executable_id 
       AND a.application_id = b.application_id 
       AND a.executable_id > 4 
       AND b.user_concurrent_program_name LIKE 'XXX: Program Name';