Find Concurrent request long time

-- tim nhung request chay lau hon 45 phut

SELECT

fcr.request_id request_id,

TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,

fcr.actual_start_date start_date,

fcp.concurrent_program_name conc_prog,

fcpt.user_concurrent_program_name user_conc_prog

FROM

apps.fnd_concurrent_programs fcp,

apps.fnd_concurrent_programs_tl fcpt,

apps.fnd_concurrent_requests fcr

WHERE

TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)

and

fcr.concurrent_program_id = fcp.concurrent_program_id

and

fcr.program_application_id = fcp.application_id

and

fcr.concurrent_program_id = fcpt.concurrent_program_id

and

fcr.program_application_id = fcpt.application_id

and

fcpt.language = USERENV('Lang')

and fcr.actual_start_date > to_date('01/JAN/2010')

ORDER BY

TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;

-------- co group

select a.user_conc_prog, max(a.exec_time) maxtime from

(

SELECT

fcr.request_id request_id,

TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,

fcr.actual_start_date start_date,

fcp.concurrent_program_name conc_prog,

fcpt.user_concurrent_program_name user_conc_prog

FROM

apps.fnd_concurrent_programs fcp,

apps.fnd_concurrent_programs_tl fcpt,

apps.fnd_concurrent_requests fcr

WHERE

TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)

and

fcr.concurrent_program_id = fcp.concurrent_program_id

and

fcr.program_application_id = fcp.application_id

and

fcr.concurrent_program_id = fcpt.concurrent_program_id

and

fcr.program_application_id = fcpt.application_id

and

fcpt.language = USERENV('Lang')

and fcr.actual_start_date > to_date('01/JAN/2010')

ORDER BY

TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc

) a

group by a.user_conc_prog

order by maxtime DESC;