select
null action_,
null status_,
null message_,
cast(null as varchar2(240)) gather_status,
null modified_columns_,
cast(null as varchar2(10)) convert_program,
x.application,
x.program,
x.short_name,
x.program_type,
x.executable,
x.method,
x.execution_file_name,
x.output_format,
x.run_count,
x.distinct_users,
x.last_run_date,
x.first_run_date,
x.created_by,
x.creation_date,
x.concurrent_program_id,
x.application_id,
upper(:customer_abbreviation) customer_abbreviation,
upper(:custom_prefix) custom_prefix,
cast(null as number) request_id_,
rownum upload_row
from
(
select
fav.application_name application,
fcpv.user_concurrent_program_name program,
fcpv.concurrent_program_name short_name,
case
when xddb.data_source_code is not null then 'BI Publisher'
when fev.execution_method_code='P' then 'RDF'
else 'Data Extract'
end program_type,
fev.executable_name executable,
xxen_util.meaning(fev.execution_method_code,'CP_EXECUTION_METHOD_CODE',0) method,
fev.execution_file_name,
xxen_util.meaning(fcpv.output_file_type,'CP_OUTPUT_FILE_TYPE',0) output_format,
nvl(stats.run_count,0) run_count,
nvl(stats.distinct_users,0) distinct_users,
xxen_util.client_time(stats.last_run_date) last_run_date,
xxen_util.client_time(stats.first_run_date) first_run_date,
xxen_util.user_name(fcpv.created_by) created_by,
xxen_util.client_time(fcpv.creation_date) creation_date,
fcpv.concurrent_program_id,
fcpv.application_id
from
fnd_application_vl fav,
fnd_concurrent_programs_vl fcpv,
fnd_executables_vl fev,
xdo_ds_definitions_b xddb,
(
select
fcr.concurrent_program_id,
fcr.program_application_id,
count(*) run_count,
count(distinct fcr.requested_by) distinct_users,
max(fcr.actual_start_date) last_run_date,
min(fcr.actual_start_date) first_run_date
from
fnd_concurrent_requests fcr
group by
fcr.concurrent_program_id,
fcr.program_application_id
) stats
where
fav.application_id=fcpv.application_id and
fcpv.executable_application_id=fev.application_id and
fcpv.executable_id=fev.executable_id and
fcpv.concurrent_program_name=xddb.data_source_code(+) and
fav.application_short_name=xddb.application_short_name(+) and
fcpv.concurrent_program_id=stats.concurrent_program_id(+) and
fcpv.application_id=stats.program_application_id(+) and
nvl(fcpv.enabled_flag,'Y')='Y'
) x
where
1=1 |
