Query to find the time difference of a concurrent request

SELECT request_id


, x.status_code sts_cod

, (SELECT user_concurrent_program_name

FROM fnd_concurrent_requests a, fnd_concurrent_programs_vl b

WHERE a.request_id = x.request_id

AND a.concurrent_program_id = b.concurrent_program_id)

concurrent_program

, (SELECT meaning

FROM fnd_lookups

WHERE lookup_code = x.status_code

AND lookup_type = 'CP_STATUS_CODE')

status

, fu.user_name

, fu.description

, argument_text parameters

, actual_start_date actual_start_with_time

, TRUNC(actual_start_date) actual_start

, actual_completion_date actual_completion

, DECODE (

actual_completion_date

, NULL, TO_CHAR (

TO_DATE ('00:00:00', 'HH24:MI:SS')

+ (SYSDATE - actual_start_date)

, 'HH24:MI:SS'

)

, TO_CHAR (

TO_DATE ('00:00:00', 'HH24:MI:SS')

+ (actual_completion_date - actual_start_date)

, 'HH24:MI:SS'

)

)

hr_min_sec

, TO_CHAR (SYSDATE, 'ddmm:hh:mi:ss') systime

, completion_text

--,(ROUND(to_number(actual_completion_date -actual_start_date) *60 *60 *24)) dt_dif_in_sec

FROM fnd_concurrent_requests x, fnd_user fu, fnd_responsibility_vl frv

WHERE frv.responsibility_key LIKE '%\_UK\_%' ESCAPE '\'--all UK responsibilities

AND frv.responsibility_id = x.responsibility_id

AND x.requested_by = fu.user_id

AND x.concurrent_program_id IN

( --(170990),

(SELECT concurrent_program_id

FROM fnd_concurrent_programs_vl

WHERE UPPER (user_concurrent_program_name) LIKE 'XX%'))

--and request_id = 27061498

and actual_start_date <= sysdate