FND Concurrent Requests (11i)

Description
Categories: Application, DBA, 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
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 fcpt.user_concurrent_program_name end program,
case when fcr.request_type='M' and fcr.frr_display_sequence is not null then fcpt.user_concurrent_program_name||' ('||fcr.frr_display_sequence||')' else fcr.description end description,
flv2.meaning phase,
flv1.meaning status,
xxen_util.user_name(fcr.requested_by) user_name,
haou.name ou,
frt.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,
fcp.concurrent_program_name short_name,
fev.executable_name executable_short_name,
flv3.meaning 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,
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.*,
(
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||