FND Concurrent Requests Summary

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Concurrent programs sorted by the sum of their historic execution times

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
x.user_concurrent_program_name program,
x.description,
x.concurrent_program_name short_name,
x.executable_name,
xxen_util.meaning(x.execution_method_code,'CP_EXECUTION_METHOD_CODE',0) method,
x.execution_file_name,
&col_argument2
x.executions,
x.users,
xxen_util.time(x.min_seconds) min_time,
xxen_util.time(x.max_seconds) max_time,
xxen_util.time(x.avg_seconds) avg_time,
xxen_util.time(x.sum_seconds) sum_time,
xxen_util.time(x.avg_wait_seconds) avg_wait_time,
xxen_util.time(x.sum_wait_seconds) sum_wait_time,
xxen_util.time(x.avg_non_conflict_wait_seconds) avg_non_conflict_wait_time,
xxen_util.time(x.sum_non_conflict_wait_seconds) sum_non_conflict_wait_time,
xxen_util.time(x.avg_conflict_wait_seconds) avg_conflict_wait_time,
xxen_util.time(x.sum_conflict_wait_seconds) sum_conflict_wait_time,
x.min_seconds,
x.max_seconds,
x.avg_seconds,
x.sum_seconds,
x.avg_wait_seconds,
x.sum_wait_seconds,
x.avg_non_conflict_wait_seconds,
x.sum_non_conflict_wait_seconds,
x.avg_conflict_wait_seconds,
x.sum_conflict_wait_seconds,
xxen_util.client_time(x.first_run_date) first_run_date,
xxen_util.client_time(x.last_run_date) last_run_date,
xxen_util.user_name(x.last_user) last_user,
x.last_responsibility,
x.execution_method_code,
x.total_users
&time_percentage
from
(
select distinct
fcpv.user_concurrent_program_name,
fcr.description_ description,
fcpv.concurrent_program_name,
fe.executable_name,
fe.execution_method_code,
fe.execution_file_name,
&col_argument
count(*) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name) executions,
count(distinct fcr.requested_by) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) users,
min(fcr.seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) min_seconds,
max(fcr.seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) max_seconds,
avg(fcr.seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) avg_seconds,
sum(fcr.seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) sum_seconds,
avg(fcr.wait_seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) avg_wait_seconds,
sum(fcr.wait_seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) sum_wait_seconds,
avg(fcr.non_conflict_wait_seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) avg_non_conflict_wait_seconds,
sum(fcr.non_conflict_wait_seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) sum_non_conflict_wait_seconds,
avg(fcr.conflict_wait_seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) avg_conflict_wait_seconds,
sum(fcr.conflict_wait_seconds) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) sum_conflict_wait_seconds,
min(fcr.actual_start_date) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) first_run_date,
max(fcr.actual_start_date) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) last_run_date,
max(fcr.requested_by) keep (dense_rank last order by fcr.actual_start_date, fcr.request_id) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) last_user,
max(frv.responsibility_name) keep (dense_rank last order by fcr.actual_start_date, fcr.request_id) over (partition by fcpv.user_concurrent_program_name,fcr.description_,fcpv.concurrent_program_name,fe.executable_name,fe.execution_file_name &partition_argument) last_responsibility,
count(distinct fcr.requested_by) over () total_users
from
(
select
86400*greatest(0,nvl(fcr.actual_completion_date,sysdate)-fcr.actual_start_date) seconds,
86400*greatest(0,nvl(fcr.actual_start_date,case when fcr.requested_start_date<sysdate and fcr.phase_code='P' and fcr.hold_flag='N' then sysdate end)-greatest(fcr.request_date,fcr.requested_start_date)) wait_seconds,
86400*greatest(0,nvl(fcr.actual_start_date,case when fcr.requested_start_date<sysdate and fcr.phase_code='P' and fcr.hold_flag='N' then sysdate end)-nvl(nvl(fcr.crm_release_date,case when fcr.requested_start_date<sysdate and fcr.phase_code='P' and fcr.hold_flag='N' and fcr.status_code='Q' and fcr.crm_tstmp is not null then sysdate end),greatest(fcr.request_date,fcr.requested_start_date))) non_conflict_wait_seconds,
86400*greatest(0,nvl(fcr.crm_release_date,case when fcr.requested_start_date<sysdate and fcr.phase_code='P' and fcr.hold_flag='N' and fcr.status_code='Q' and fcr.crm_tstmp is not null then sysdate end)-greatest(fcr.request_date,fcr.requested_start_date)) conflict_wait_seconds,
regexp_replace(fcr.description,'\d+', '%') description_,
fcr.*
from
fnd_concurrent_requests fcr
) fcr,
fnd_concurrent_programs_vl fcpv,
fnd_user fu,
fnd_executables fe,
fnd_responsibility_vl frv
where
1=1 and
fcr.actual_completion_date is not null and
fcr.requested_by=fu.user_id and
fcr.concurrent_program_id=fcpv.concurrent_program_id and
fcpv.executable_application_id=fe.application_id and
fcpv.executable_id=fe.executable_id and
fcr.responsibility_application_id=frv.application_id(+) and
fcr.responsibility_id=frv.responsibility_id(+)
) x
order by
x.executions desc,
x.sum_seconds desc
Parameter Name SQL text Validation
Started within Days 
fcr.actual_start_date>sysdate-:days
 Number
Running from Date 
fcr.actual_completion_date>=:from_date
 Date
Running to Date 
fcr.actual_start_date<:to_date+1
 Date
Program Name 
upper(fcpv.user_concurrent_program_name) like upper(:program_name)
 LOV
System Program Name 
fcpv.concurrent_program_name like :system_program_name
 LOV
Executable Short Name 
(fcr.concurrent_program_id,fcr.program_application_id) in (
select
fcp0.concurrent_program_id,
fcp0.application_id
from
fnd_executables fe,
fnd_concurrent_programs fcp0
where
fe.executable_name=:executable_name and
fe.application_id=fcp0.executable_application_id and
fe.executable_id=fcp0.executable_id
)
 LOV
Execution Method 
(fcr.concurrent_program_id,fcr.program_application_id) in
(
select
fcp.concurrent_program_id,fcp.application_id
from
fnd_executables fe,
fnd_concurrent_programs fcp
where
fe.execution_method_code=xxen_util.lookup_code(:execution_method,'CP_EXECUTION_METHOD_CODE',0) and
fe.application_id=fcp.executable_application_id and
fe.executable_id=fcp.executable_id
)
 LOV
Show Argument Text 
regexp_replace(fcr.argument_text,'\d+', '%') argument_text,
 LOV
Show Reports only 
(
fcpv.concurrent_program_name in ('RGFSGXML','RGRARG','XXEN_REPORT','') or
lower(fcpv.user_concurrent_program_name) like '% report' or
fe.execution_method_code='P' or
fe.executable_name='XDODTEXE'
) and
fcpv.user_concurrent_program_name not like 'Transfer %' and
fcpv.user_concurrent_program_name not like 'Create %'
 LOV
Download
   
Blitz Report™

Blitz Report™ provides multiple benefits: