FND Concurrent Requests

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