FND Concurrent Requests Summary

➜ Back to Library

Excel example

XML import file

Description

Categories: Enginatics
Repository: GitHub

Concurrent programs sorted by the sum of their historic execution times

SQL 93 lines

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

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

Parameters 9

Parameter

Type

List of values SQL

Started within Days

Number

fcr.actual_start_date>sysdate-:days

Running from Date

Date

fcr.actual_completion_date>=:from_date

Running to Date

Date

fcr.actual_start_date<:to_date+1

Program Name

LOV

upper(fcpv.user_concurrent_program_name) like upper(:program_name)

System Program Name

LOV

fcpv.concurrent_program_name like :system_program_name

Executable Short Name

LOV

(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
)

Execution Method

LOV

(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
)

Show Argument Text

LOV

regexp_replace(fcr.argument_text,'\d+', '%') argument_text,

Show Reports only

LOV

(
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 %' and
fcpv.concurrent_program_name not in ('APXIIMPT')

Description

SQL 93 lines

Parameters 9

Find out more

Features

Download

FAQ