ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5C69F6E5589317E8E0630100007FB1C2Y 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 New report: pending EIS report submission requests with schedule, parameters, delivery settings of the reused EIS process, and last run. US EIS Schedules Pending requests of the EIS report submission programs, one row per schedule or future request, for recreating EIS schedules as Blitz Report schedules. A recurring schedule whose request is running is listed with that request, as the concurrent manager submits the next one only when the run completes. Report set schedules are not listed. Parameters and delivery settings are those of the EIS process that a schedule reuses for every run (EIS Process ID). EIS re-evaluates SQL parameter defaults at every run, and Increment Dates shifts the date parameters, so Last Run Parameters shows the values the last run actually used. Email Recipients are the selected recipients plus the current members of the selected EIS user groups, as EIS resolves them at run time. Last Run is the previous request of the schedule's resubmission chain. Blank Weeks of Month or Months on a Specific Days schedule means every week or month. Cloning an instance terminates its pending and running requests: on a clone, set Terminated Schedules from to the clone date to list the production schedules. EIS 1=1 :terminated_schedules_from 1 10 1=1 era.application_name=:application LOV custom Y select era.application_name value, era.description from xxeis.eis_rs_applications era order by era.application_name US Application 2 20 1=1 errc.category_name=:category LOV custom Y select distinct errc.category_name value, null description from xxeis.eis_rs_report_categories errc, xxeis.eis_rs_applications era where errc.application_id=era.application_id and (:$flex$.application is null or xxen_util.contains(:$flex$.application,era.application_name)='Y') order by errc.category_name US Category 3 30 1=1 err.report_name=:report_name LOV custom Y select err.report_name value, err.description from xxeis.eis_rs_reports err order by err.report_name US Report Name 4 40 1=1 fcr.requested_by=xxen_util.user_id(:user_name) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US User User who submitted the run. For scheduled runs, the user who created the schedule. 5 50 1=1 frv.responsibility_name=:responsibility LOV FND Responsibility Name 8E2FF36EDE9E79D2E0530100007F1FF2 Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv order by frv.responsibility_name US Responsibility Responsibility of the EIS session of the run. For scheduled runs, the one the schedule was created in. 6 60 :terminated_schedules_from Date US Terminated Schedules from Also lists requests due on or after this date that were terminated before they started, or while running as the last request of a recurring schedule, such as the requests terminated by a clone.