FND Concurrent Requests

Description
Categories: Enginatics
Repository: Github
Running, scheduled and historic concurrent requests including phase, status, parameters, schedule, timing, delivery and output option information.
For performance analysis of running requests, the report contains sid, currently executed sql_id and sql_text and distribution of wait time on different wait classes.

Use parameter 'Scheduled or Running' to get a list of all currently schedule ... 
Running, scheduled and historic concurrent requests including phase, status, parameters, schedule, timing, delivery and output option information.
For performance analysis of running requests, the report contains sid, currently executed sql_id and sql_text and distribution of wait time on different wait classes.

Use parameter 'Scheduled or Running' to get a list of all currently scheduled or running requests.
   more
Run FND Concurrent Requests and other Oracle EBS reports with Blitz Report™ on our demo environment
select
fcr.request_id id,
decode(fcr.priority_request_id,fcr.request_id,null,fcr.priority_request_id) priority_id,
decode(fcr.parent_request_id,-1,null,fcr.parent_request_id) parent_id,
case when fcr.program_application_id=160 and fcr.concurrent_program_id=20392 /*alecdc*/ or fcr.request_type='M' then fcr.description else fcpv.user_concurrent_program_name end program,
case when fcr.request_type='M' and fcr.frr_display_sequence is not null then fcpv.user_concurrent_program_name||' ('||fcr.frr_display_sequence||')' else fcr.description end description,
flvv2.meaning phase,
trim(flvv1.meaning) status,
xxen_util.user_name(fcr.requested_by) user_name,
haouv.name operating_unit,
frv.responsibility_name responsibility,
xxen_util.client_time(fcr.actual_start_date) start_date,
xxen_util.client_time(fcr.actual_completion_date) completion_date,
xxen_util.time(fcr.seconds) time,
fcr.seconds,
fcr.argument_text_ parameter_text,
&argument_display_values
xxen_util.client_time(fcr.requested_start_date) requested_start_date,
case
when nvl(fcrc.class_type,'P')='P' then
case
when fcr.resubmit_interval_unit_code in ('DAYS','MONTHS') then to_char(xxen_util.client_time(fcr.requested_start_date),'HH24:MI:SS ')||fcr.resubmit_interval||' '||fcr.resubmit_interval_unit_code||' '||fcr.resubmit_interval_type_code else
fcr.resubmit_interval||' '||fcr.resubmit_interval_unit_code||' '||fcr.resubmit_interval_type_code
end
else
to_char(xxen_util.client_time(fcr.requested_start_date),'HH24:MI:SS ')
||decode(substr(fcrc.class_info,1,1),'1',' 1 of month ')
||decode(substr(fcrc.class_info,2,1),'1',' 2 of month ')
||decode(substr(fcrc.class_info,3,1),'1',' 3 of month ')
||decode(substr(fcrc.class_info,4,1),'1',' 4 of month ')
||decode(substr(fcrc.class_info,5,1),'1',' 5 of month ')
||decode(substr(fcrc.class_info,6,1),'1',' 6 of month ')
||decode(substr(fcrc.class_info,7,1),'1',' 7 of month ')
||decode(substr(fcrc.class_info,8,1),'1',' 8 of month ')
||decode(substr(fcrc.class_info,9,1),'1',' 9 of month ')
||decode(substr(fcrc.class_info,10,1),'1','10 of month ')
||decode(substr(fcrc.class_info,11,1),'1','11 of month ')
||decode(substr(fcrc.class_info,12,1),'1','12 of month ')
||decode(substr(fcrc.class_info,13,1),'1','13 of month ')
||decode(substr(fcrc.class_info,14,1),'1','14 of month ')
||decode(substr(fcrc.class_info,15,1),'1','15 of month ')
||decode(substr(fcrc.class_info,16,1),'1','16 of month ')
||decode(substr(fcrc.class_info,17,1),'1','17 of month ')
||decode(substr(fcrc.class_info,18,1),'1','18 of month ')
||decode(substr(fcrc.class_info,19,1),'1','19 of month ')
||decode(substr(fcrc.class_info,20,1),'1','20 of month ')
||decode(substr(fcrc.class_info,21,1),'1','21 of month ')
||decode(substr(fcrc.class_info,22,1),'1','22 of month ')
||decode(substr(fcrc.class_info,23,1),'1','23 of month ')
||decode(substr(fcrc.class_info,24,1),'1','24 of month ')
||decode(substr(fcrc.class_info,25,1),'1','25 of month ')
||decode(substr(fcrc.class_info,26,1),'1','26 of month ')
||decode(substr(fcrc.class_info,27,1),'1','27 of month ')
||decode(substr(fcrc.class_info,28,1),'1','28 of month ')
||decode(substr(fcrc.class_info,29,1),'1','29 of month ')
||decode(substr(fcrc.class_info,30,1),'1','30 of month ')
||decode(substr(fcrc.class_info,31,1),'1','31 of month ')
||decode(substr(fcrc.class_info,32,1),'1','Last day of month ')
||decode(substr(fcrc.class_info,33,1),'1','Sun ')
||decode(substr(fcrc.class_info,34,1),'1','Mon ')
||decode(substr(fcrc.class_info,35,1),'1','Tue ')
||decode(substr(fcrc.class_info,36,1),'1','Wed ')
||decode(substr(fcrc.class_info,37,1),'1','Thu ')
||decode(substr(fcrc.class_info,38,1),'1','Fri ')
||decode(substr(fcrc.class_info,39,1),'1','Sat ')
end||decode(fcr.increment_dates,'Y',' - increment dates') schedule,
xxen_util.time(fcr.wait_seconds) wait_time,
fcr.wait_seconds,
xxen_util.time(fcr.non_conflict_wait_seconds) non_conflict_wait_time,
fcr.non_conflict_wait_seconds,
xxen_util.time(fcr.conflict_wait_seconds) conflict_wait_time,
fcr.conflict_wait_seconds,
case when fcr.program_application_id=160 and fcr.concurrent_program_id=20392 then 'Alert' else decode(fcr.request_type,'P','Req Set Child Program','S','Req Set Stage','M','Req Set','B','Multi Language Set','C','Multi Language Child Program',decode(fcr.priority_request_id,fcr.request_id,'Standalone','Other Subprogram')) end type,
fcpv.concurrent_program_name short_name,
fev.executable_name executable_short_name,
xxen_util.meaning(fev.execution_method_code,'CP_EXECUTION_METHOD_CODE',0) method,
fev.execution_file_name,
case when fev.description is null and fev.user_executable_name<>fev.executable_name then fev.user_executable_name else fev.description end executable_description,
fcd.cd_name,
fcd.user_cd_name,
gs.sql_id,
&sql_text_column
gs.event,
gse.user_io_time,
gse.network_time,
gse.system_io_time,
gse.configuration_time,
gse.application_time,
gse.commit_time,
gse.concurrency_time,
gse.other_time,
&delivery_cols
fcr.output_file_type,
fcr.outfile_name,
fcr.ofile_size outfile_size,
fcr.logfile_name,
fcr.lfile_size logfile_size,
fcr.temp_out,
fcr.temp_log,
fcpoi.node_name1 target_node1,
fcpoi.node_name2 target_node2,
fcr.conc_manager,
fcr.node,
fcr.os_process_manager,
fcr.os_process_id os_process_apps,
fcr.oracle_process_id os_process_db,
fcr.instance_number,
fcr.audsid,
gs.sid,
gs.serial#,
case when fcr.program_application_id=160 and fcr.concurrent_program_id=20392 or fcr.request_type='M' then to_number(fcr.argument1) else fcr.program_application_id end app_id,
case when fcr.program_application_id=160 and fcr.concurrent_program_id=20392 or fcr.request_type='M' then to_number(fcr.argument2) else fcr.concurrent_program_id end prog_id
from
(
select
fcr.*,
nvl(fcr.org_id,
(
select
fpov.profile_option_value
from
fnd_profile_option_values fpov
where
fcr.responsibility_application_id=fpov.level_value_application_id and
fcr.responsibility_id=fpov.level_value and
fpov.level_value2 is null and
fpov.application_id=0 and
fpov.profile_option_id=1991 and
fpov.level_id=10003
)) org_id_,
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,
fcpr.plsql_dir||nvl2(fcpr.plsql_out,'/','')||fcpr.plsql_out temp_out,
fcpr.plsql_dir||nvl2(fcpr.plsql_log,'/','')||fcpr.plsql_log temp_log,
fcqv.user_concurrent_queue_name conc_manager,
fcqv.target_node node,
fcpr.os_process_id os_process_manager,
case when fcr.phase_code='R' and fcr.status_code='R' then fcpr.instance_number end instance_number,
case when fcr.phase_code='R' and fcr.status_code='R' then fcr.oracle_session_id end audsid,
frr.display_sequence frr_display_sequence,
nvl(frr.application_id,fcr.program_application_id) program_application_id_,
nvl(frr.concurrent_program_id,fcr.concurrent_program_id) concurrent_program_id_,
case when fcr.request_type='M' and frr.parent_request_id is not null then
rtrim(
replace(
frr.argument1||','||
frr.argument2||','||
frr.argument3||','||
frr.argument4||','||
frr.argument5||','||
frr.argument6||','||
frr.argument7||','||
frr.argument8||','||
frr.argument9||','||
frr.argument10||','||
frr.argument11||','||
frr.argument12||','||
frr.argument13||','||
frr.argument14||','||
frr.argument15||','||
frr.argument16||','||
frr.argument17||','||
frr.argument18||','||
frr.argument19||','||
frr.argument20||','||
frr.argument21||','||
frr.argument22||','||
frr.argument23||','||
frr.argument24||','||
frr.argument25||','||
frr.argument26||','||
frr.argument27||','||
frr.argument28||','||
frr.argument29||','||
frr.argument30||','||
frr.argument31||','||
frr.argument32||','||
frr.argument33||','||
frr.argument34||','||
frr.argument35||','||
frr.argument36||','||
frr.argument37||','||
frr.argument38||','||
frr.argument39||','||
frr.argument40||','||
frr.argument41||','||
frr.argument42||','||
frr.argument43||','||
frr.argument44||','||
frr.argument45||','||
frr.argument46||','||
frr.argument47||','||
frr.argument48||','||
frr.argument49||','||
frr.argument50||','||
frr.argument51||','||
frr.argument52||','||
frr.argument53||','||
frr.argument54||','||
frr.argument55||','||
frr.argument56||','||
frr.argument57||','||
frr.argument58||','||
frr.argument59||','||
frr.argument60||','||
frr.argument61||','||
frr.argument62||','||
frr.argument63||','||
frr.argument64||','||
frr.argument65||','||
frr.argument66||','||
frr.argument67||','||
frr.argument68||','||
frr.argument69||','||
frr.argument70||','||
frr.argument71||','||
frr.argument72||','||
frr.argument73||','||
frr.argument74||','||
frr.argument75||','||
frr.argument76||','||
frr.argument77||','||
frr.argument78||','||
frr.argument79||','||
frr.argument80||','||
frr.argument81||','||
frr.argument82||','||
frr.argument83||','||
frr.argument84||','||
frr.argument85||','||
frr.argument86||','||
frr.argument87||','||
frr.argument88||','||
frr.argument89||','||
frr.argument90||','||
frr.argument91||','||
frr.argument92||','||
frr.argument93||','||
frr.argument94||','||
frr.argument95||','||
frr.argument96||','||
frr.argument97||','||
frr.argument98||','||
frr.argument99||','||
frr.argument100,
chr(0)),
',') else
fcr.argument_text end argument_text_,
fcr.rowid row_id
from
fnd_concurrent_requests fcr,
fnd_concurrent_processes fcpr,
fnd_concurrent_queues_vl fcqv,
(
select
frss.display_sequence,
frr.*
from
fnd_run_requests frr,
fnd_request_set_programs frsp,
fnd_request_set_stages frss
where
frr.set_application_id=frsp.set_application_id and
frr.request_set_id=frsp.request_set_id and
frr.request_set_program_id=frsp.request_set_program_id and
frsp.set_application_id=frss.set_application_id and
frsp.request_set_id=frss.request_set_id and
frsp.request_set_stage_id=frss.request_set_stage_id and
not exists (select null from fnd_concurrent_requests fcr2 where frr.parent_request_id=fcr2.parent_request_id and fcr2.request_type='S' and fcr2.program_application_id=0 and fcr2.concurrent_program_id=36034)
) frr
where
1=1 and
fcr.controlling_manager=fcpr.concurrent_process_id(+) and
fcpr.queue_application_id=fcqv.application_id(+) and
fcpr.concurrent_queue_id=fcqv.concurrent_queue_id(+) and
case when fcr.request_type in ('B','M') then fcr.request_id end=frr.parent_request_id(+)
) fcr,
fnd_concurrent_programs_vl fcpv,
fnd_conc_prog_onsite_info fcpoi,
fnd_executables_vl fev,
fnd_responsibility_vl frv,
fnd_conc_release_classes fcrc,
hr_all_organization_units_vl haouv,
fnd_conflicts_domain fcd,
(select x.* from (select min(fcpa.sequence) over (partition by fcpa.concurrent_request_id,fcpa.action_type) min_sequence, fcpa.* from fnd_conc_pp_actions fcpa where '&show_delivery'='Y' and fcpa.action_type=1 and fcpa.number_of_copies>0) x where x.sequence=x.min_sequence) fcpa1,
(select x.* from (select