select
x.request_id,
x.report_name,
x.eis_report_id "EIS Report ID",
x.application,
x.category,
x.program,
x.user_name,
x.responsibility,
x.status,
x.schedule_type,
x.interval,
x.interval_unit,
x.interval_type,
x.days_of_month "Days of Month",
x.weeks_of_month "Weeks of Month",
x.days_of_week "Days of Week",
x.months,
xxen_util.client_time(x.next_run) next_run,
xxen_util.client_time(x.end_date) end_date,
x.increment_dates,
x.parameters,
ltrim(nvl2(x.email_recipients,', Email',null)||nvl2(erprd.ftp_site_id,', FTP',null)||nvl2(erprd.file_system,', File System',null),', ') delivery,
x.email_recipients,
x.user_groups,
(select rtrim(erpr.distribution_methods,',') from xxeis.eis_rs_process_rpts erpr where x.email_recipients is not null and x.process_id=erpr.process_id) email_format,
erfs.ftp_site_name "FTP Site",
erfs.protocol "FTP Protocol",
erfs.host "FTP Host",
nvl(erprd.remote_site_directory,erfs.remote_site_directory) "FTP Directory",
nvl2(erfs.ftp_site_id,erprd.ftp_file_naming,null) "FTP File Name",
rtrim(erprd.ftp_distribute_methods,',') "FTP Format",
erprd.file_system file_system_directory,
nvl2(erprd.file_system,erprd.file_sys_naming,null) file_system_file_name,
rtrim(erprd.file_sys_dist_methods,',') file_system_format,
xxen_util.client_time(x.last_run) last_run,
x.last_run_status,
x.last_run_rows,
x.last_run_parameters,
x.process_id "EIS Process ID",
x.argument_text arguments
from
(
select
fcr.request_id,
nvl(err.report_name,fcr.argument2) report_name,
to_number(fcr.argument1) eis_report_id,
era.application_name application,
errc.category_name category,
fcpv.user_concurrent_program_name program,
xxen_util.user_name(fcr.requested_by) user_name,
frv.responsibility_name responsibility,
decode(fcr.phase_code,
'R',xxen_util.meaning(fcr.phase_code,'CP_PHASE_CODE',0),
'P',trim(xxen_util.meaning(decode(fcr.hold_flag,'Y','H',case when fcr.requested_start_date>sysdate then 'P' else fcr.status_code end),'CP_STATUS_CODE',0)),
trim(xxen_util.meaning(fcr.status_code,'CP_STATUS_CODE',0))
) status,
xxen_util.meaning(nvl(fcrc.class_type,case when fcr.requested_start_date>fcr.request_date then 'O' else 'A' end),'FNDCPSCHED',0) schedule_type,
fcr.resubmit_interval interval,
xxen_util.meaning(fcr.resubmit_interval_unit_code,'CP_RESUBMIT_INTERVAL_UNIT',0) interval_unit,
xxen_util.meaning(fcr.resubmit_interval_type_code,'CP_RESUBMIT_INTERVAL_TYPE',0) interval_type,
(
select
listagg(decode(rowgen.column_value,32,xxen_util.meaning('5','FND_SCH_WEEKDAY_TYPE',0),rowgen.column_value),',') within group (order by rowgen.column_value)
from
table(xxen_util.rowgen(32)) rowgen
where
fcrc.class_type='S' and
substr(fcrc.class_info,rowgen.column_value,1)='1'
) days_of_month,
(
select
listagg(xxen_util.meaning(to_char(rowgen.column_value),'FND_SCH_WEEKDAY_TYPE',0),',') within group (order by rowgen.column_value)
from
table(xxen_util.rowgen(5)) rowgen
where
fcrc.class_type='S' and
substr(fcrc.class_info,33,7)<>'0000000' and
substr(fcrc.class_info,40,5)<>'11111' and
substr(fcrc.class_info,39+rowgen.column_value,1)='1'
) weeks_of_month,
(
select
listagg(xxen_util.meaning(to_char(rowgen.column_value),'FND_SCH_WEEK_DAYS',0),',') within group (order by rowgen.column_value)
from
table(xxen_util.rowgen(7)) rowgen
where
fcrc.class_type='S' and
nvl(substr(fcrc.class_info,40,5),'1')<>'00000' and
substr(fcrc.class_info,32+rowgen.column_value,1)='1'
) days_of_week,
(
select
listagg(xxen_util.meaning(to_char(rowgen.column_value),'FND_SCH_MONTHS',0),',') within group (order by rowgen.column_value)
from
table(xxen_util.rowgen(12)) rowgen
where
fcrc.class_type='S' and
substr(fcrc.class_info,45,12)<>'111111111111' and
substr(fcrc.class_info,44+rowgen.column_value,1)='1'
) months,
fcr.requested_start_date next_run,
fcrc.date2 end_date,
xxen_util.yes(fcr.increment_dates) increment_dates,
erp.parameters,
(
select
listagg(y.email_address,', ') within group (order by y.email_address)
from
(
select distinct
erprd.process_id,
nvl(erugd.email_address,erprd.email_address) email_address
from
xxeis.eis_rs_process_rpt_distribute erprd,
xxeis.eis_rs_user_group_details erugd
where
erprd.select_flag='Y' and
erprd.user_group_id=erugd.user_group_id(+)
) y
where
to_number(fcr.argument3)=y.process_id
) email_recipients,
(
select
listagg(erug.user_group_name,', ') within group (order by erug.user_group_name)
from
xxeis.eis_rs_process_rpt_distribute erprd,
xxeis.eis_rs_user_groups erug
where
to_number(fcr.argument3)=erprd.process_id and
erprd.select_flag='Y' and
erprd.user_group_id=erug.user_group_id
) user_groups,
(select max(erprd.process_distribution_id) from xxeis.eis_rs_process_rpt_distribute erprd where to_number(fcr.argument3)=erprd.process_id and (erprd.ftp_site_id is not null or erprd.file_system is not null)) file_distribution_id,
fcr_p.actual_start_date last_run,
trim(xxen_util.meaning(fcr_p.status_code,'CP_STATUS_CODE',0)) last_run_status,
erp_l.rows_retrieved last_run_rows,
erp_l.parameters last_run_parameters,
to_number(fcr.argument3) process_id,
fcr.argument_text
from
fnd_application fa,
fnd_executables fe,
fnd_concurrent_programs_vl fcpv,
fnd_concurrent_requests fcr,
fnd_conc_release_classes fcrc,
fnd_responsibility_vl frv,
fnd_concurrent_requests fcr_p,
xxeis.eis_rs_processes erp_l,
xxeis.eis_rs_processes erp,
xxeis.eis_rs_reports err,
xxeis.eis_rs_applications era,
xxeis.eis_rs_report_categories errc
where
1=1 and
fa.application_short_name='XXEIS' and
fa.application_id=fe.application_id and
upper(fe.execution_file_name)='XXEIS.EIS_RSC_PROCESS_REPORTS.SUBMIT_CONC_REPORT' and
fe.application_id=fcpv.executable_application_id and
fe.executable_id=fcpv.executable_id and
fcpv.application_id=fcr.program_application_id and
fcpv.concurrent_program_id=fcr.concurrent_program_id and
(
fcr.phase_code='P' or
fcr.phase_code='R' and fcr.release_class_id is not null or
fcr.requested_start_date>=:terminated_schedules_from and
(
fcr.status_code='X' and fcr.actual_start_date is null or
fcr.status_code in ('D','X') and fcr.actual_start_date is not null and fcr.release_class_id is not null and
not exists (select /*+ no_unnest push_subq */ null from fnd_concurrent_requests fcr_c where fcr.request_id=fcr_c.parent_request_id and fcr.concurrent_program_id=fcr_c.concurrent_program_id)
)
) and
fcr.release_class_app_id=fcrc.application_id(+) and
fcr.release_class_id=fcrc.release_class_id(+) and
fcr.responsibility_application_id=frv.application_id(+) and
fcr.responsibility_id=frv.responsibility_id(+) and
fcr.parent_request_id=fcr_p.request_id(+) and
fcr_p.request_id=erp_l.request_id(+) and
to_number(fcr.argument3)=erp.process_id(+) and
to_number(fcr.argument1)=err.report_id(+) and
err.application_id=era.application_id(+) and
err.category_id=errc.category_id(+)
) x,
xxeis.eis_rs_process_rpt_distribute erprd,
xxeis.eis_rs_ftp_sites erfs
where
x.file_distribution_id=erprd.process_distribution_id(+) and
erprd.ftp_site_id=erfs.ftp_site_id(+)
order by
x.report_name,
x.next_run |
