ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 58D5B719827E6A37E053A100140A1EB0Y select err.report_id "EIS Report ID", era.application_name application, errc.category_name category, err.report_name, err.description, xxen_util.user_name(err.report_owner) owner, xxen_util.yes(err.seeded_flag) seeded, xxen_util.yes(err.enable_report) enabled, (select err_c.report_name from xxeis.eis_rs_reports err_c where err.copied_from_report_id=err_c.report_id) copied_from, err.view_name, y.view_owner, y.source_type, y.view_status, (select count(*) from xxeis.eis_rs_report_columns errco where err.report_id=errco.report_id and nvl(errco.enabled_flag,'Y')='Y') columns, (select count(*) from xxeis.eis_rs_report_columns errco where err.report_id=errco.report_id and nvl(errco.enabled_flag,'Y')='Y' and errco.derived_flag='Y') derived_columns, (select count(*) from xxeis.eis_rs_report_views errv where err.report_id=errv.report_id and errv.view_component_id is not null) components, (select count(*) from xxeis.eis_rs_report_parameters errp where err.report_id=errp.report_id and errp.enabled_flag='Y') parameters, (select count(*) from xxeis.eis_rs_report_parameters errp where err.report_id=errp.report_id and errp.enabled_flag='Y' and errp.required_flag='Y') required_parameters, (select count(*) from xxeis.eis_rs_report_cond_headers errch where err.report_id=errch.report_id and errch.enabled='Y') conditions, (select count(*) from xxeis.eis_rs_report_cond_headers errch where err.report_id=errch.report_id and errch.enabled='Y' and errch.condition_type='FREE_TEXT') free_text_conditions, (select count(*) from xxeis.eis_rs_sorts erso where err.report_id=erso.report_id) sorts, (select count(*) from xxeis.eis_rs_report_pivots errpi where err.report_id=errpi.report_id) pivots, (select count(*) from xxeis.eis_rs_report_triggers errt where err.report_id=errt.report_id) triggers, xxen_util.yes(err.distinct_flag) "Distinct", xxen_util.yes(err.group_by_flag) "Group By", x.runs, x.scheduled_runs, x.runs-x.scheduled_runs ad_hoc_runs, x.xl_connect_runs "XL Connect Runs", x.users, xxen_util.client_time(x.last_run) last_run, xxen_util.user_name(x.last_user_id) last_user, x.errors, x.average_rows, x.average_seconds, x.deliveries, x.email_deliveries, x.ftp_deliveries "FTP Deliveries", fcr.active_schedules, errs.responsibility_grants, errs.request_group_grants, errs.user_grants, (select count(*) from xxeis.eis_rs_report_favorites errf where err.report_id=errf.report_id) favorites, coalesce(xrt_id.report_name,xrt_name.report_name) blitz_report, &migration_columns &source_sql xxen_util.user_name(err.created_by) created_by, xxen_util.client_time(err.creation_date) creation_date, xxen_util.user_name(err.last_updated_by) last_updated_by, xxen_util.client_time(err.last_update_date) last_update_date from xxeis.eis_rs_reports err, xxeis.eis_rs_applications era, xxeis.eis_rs_report_categories errc, ( select x.report_id, case when x.paste_view_sql is not null then 'Paste SQL' when x.view_type='SQL' then 'SQL View' when x.object_type='VIEW' then 'Database View' when x.object_type='SYNONYM' then 'Synonym' when x.object_type='TABLE' then 'Table' else 'Missing' end source_type, x.view_owner, x.view_status, x.view_sql from ( select err.report_id, err.paste_view_sql, erv.object_type view_type, erv.view_sql, (select max(ao.owner) keep (dense_rank first order by decode(ao.owner,erv.view_owner,1,'APPS',2,3)) from all_objects ao where erv.view_name=ao.object_name and ao.owner in ('APPS','XXEIS') and ao.object_type in ('VIEW','SYNONYM','TABLE')) view_owner, (select max(ao.object_type) keep (dense_rank first order by decode(ao.owner,erv.view_owner,1,'APPS',2,3)) from all_objects ao where erv.view_name=ao.object_name and ao.owner in ('APPS','XXEIS') and ao.object_type in ('VIEW','SYNONYM','TABLE')) object_type, (select max(ao.status) keep (dense_rank first order by decode(ao.owner,erv.view_owner,1,'APPS',2,3)) from all_objects ao where erv.view_name=ao.object_name and ao.owner in ('APPS','XXEIS') and ao.object_type in ('VIEW','SYNONYM','TABLE')) view_status from xxeis.eis_rs_reports err, xxeis.eis_rs_views erv where err.view_id=erv.view_id(+) ) x ) y, ( select x.report_id, count(x.run_flag) runs, count(x.scheduled_flag) scheduled_runs, count(case when x.submission_source='XL Connect' then 1 end) xl_connect_runs, count(distinct case when x.run_flag='Y' then x.created_by end) users, max(case when x.run_flag='Y' then x.start_time end) last_run, max(case when x.run_flag='Y' then x.created_by end) keep (dense_rank last order by case when x.run_flag='Y' then x.start_time end nulls first) last_user_id, count(case when x.run_flag='Y' and x.status in ('E','T') then 1 end) errors, round(avg(case when x.run_flag='Y' then x.rows_retrieved end)) average_rows, round(avg(case when x.run_flag='Y' then x.seconds end)) average_seconds, count(case when x.run_flag is null then 1 end) deliveries, count(x.email_flag) email_deliveries, count(x.ftp_flag) ftp_deliveries from ( select case when erp.submission_source like 'Email Distribution~%' then to_number(substr(erp.submission_source,20)) else erp.report_id end report_id, case when erp.submission_source in ('eXpress','XL Connect') then 'Y' end run_flag, case when erp.submission_source in ('eXpress','XL Connect') and erp.start_time-ers.logon_time>1 then 'Y' end scheduled_flag, erp.submission_source, erp.status, erp.start_time, erp.created_by, erp.rows_retrieved, (erp.end_time-erp.start_time)*86400 seconds, erprd.email_flag, erprd.ftp_flag from xxeis.eis_rs_processes erp, xxeis.eis_rs_sessions ers, ( select erprd.process_id, max(case when erprd.select_flag='Y' and (erprd.email_address is not null or erprd.user_group_id is not null) then 'Y' end) email_flag, max(case when erprd.ftp_site_id is not null then 'Y' end) ftp_flag from xxeis.eis_rs_process_rpt_distribute erprd group by erprd.process_id ) erprd where 2=2 and (erp.submission_source in ('eXpress','XL Connect') or erp.submission_source like 'Email Distribution~%') and erp.session_id=ers.session_id(+) and erp.process_id=erprd.process_id(+) ) x group by x.report_id ) x, ( select fcr.argument1, count(*) active_schedules from fnd_concurrent_requests fcr where (fcr.phase_code='P' or fcr.phase_code='R' and fcr.release_class_id is not null) and (fcr.program_application_id,fcr.concurrent_program_id) in ( select fcp.application_id, fcp.concurrent_program_id from fnd_application fa, fnd_executables fe, fnd_concurrent_programs fcp where 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=fcp.executable_application_id and fe.executable_id=fcp.executable_id ) group by fcr.argument1 ) fcr, ( select coalesce(errs.report_id,errsu.report_id) report_id, count(distinct errs.responsibility_id) responsibility_grants, count(distinct errs.request_group_id) request_group_grants, count(distinct errs.user_id) user_grants from xxeis.eis_rs_report_security errs, xxeis.eis_rs_report_set_units errsu where errs.report_set_id=errsu.report_set_id(+) and sysdate between errs.start_date and nvl(errs.end_date,sysdate) group by coalesce(errs.report_id,errsu.report_id) ) errs, ( select err.report_id, min(xrt.report_id) keep (dense_rank first order by decode(xrt.report_name,err.report_name,1)) blitz_report_id from xxen_reports_tl xrt, xxeis.eis_rs_reports err where xrt.language=xxen_util.base_language and xrt.description like '%EIS Report ID: %' and to_number(regexp_substr(xrt.description,'^EIS Report ID: ([0-9]+)',1,1,'m',1))=err.report_id group by err.report_id ) xrt_eis, xxen_reports_tl xrt_id, xxen_reports_tl xrt_name where 1=1 and err.application_id=era.application_id(+) and err.category_id=errc.category_id(+) and err.report_id=y.report_id and err.report_id=x.report_id(+) and to_char(err.report_id)=fcr.argument1(+) and err.report_id=errs.report_id(+) and err.report_id=xrt_eis.report_id(+) and xrt_eis.blitz_report_id=xrt_id.report_id(+) and xrt_id.language(+)=userenv('lang') and err.report_name=xrt_name.report_name(+) and xrt_name.language(+)=userenv('lang') order by x.runs desc nulls last, era.application_name, errc.category_name, err.report_name The view owner, type and status lookup reads all_objects by view name. US EIS Reports One row per EIS eXpress report with its definition, usage, schedules and access grants, and the Blitz Report it was migrated to. Source Type follows the EIS runtime: the SQL pasted into the report, else the SQL of an EIS view of type SQL, else the database object named by the report's view, looked up in the registered owner, then APPS, then XXEIS. Missing means that none exists, so the report cannot run in EIS either. View Owner and View Status describe that database object. Runs count eXpress and XL Connect executions that started within Executed within Days. A run counts as scheduled when it starts more than a day after the logon of its EIS session, as scheduled runs keep the session in which the schedule was created. Deliveries count EIS distribution jobs of any method, Email and FTP Deliveries the runs with an email recipient or an FTP target. EIS purges its run history, so a report without runs may still have been used before the earliest retained run. Distribution jobs are kept longer and show older usage when Executed within Days reaches back further. Active Schedules counts the requests EIS Schedules lists for the report: pending requests of the EIS report submission programs and the running request of a recurring schedule. Grants count active grants, with report set grants expanded to their reports. Blitz Report is the report whose description carries the EIS Report ID line written by the EIS import, whatever its name, or else a Blitz Report of the same name. EIS &migration_columns &source_sql 1=1 2=2 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 F Voir les contenus SQL 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 F Spectacle plus ancien à la séance uniquement 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 err.report_owner=xxen_util.user_id(:owner) 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 F Spectacle à la carte uniquement US Owner 5 50 1=1 err.seeded_flag='Y' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y F Nombre de jours d'exécution US Seeded 6 1=1 err.seeded_flag is null N US Seeded 7 60 1=1 err.enable_report=:enabled LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description US Enabled 8 70 1=1 y.source_type=:source_type LOV custom Y select 'Paste SQL' value, 'SQL pasted into the EIS report' description from dual union all select 'SQL View', 'SQL stored with an EIS view of type SQL' from dual union all select 'Database View', 'Database view referenced by name' from dual union all select 'Synonym', 'Synonym referenced by name' from dual union all select 'Table', 'Table referenced by name' from dual union all select 'Missing', 'No source found, the report cannot run in EIS' from dual US Source Type 9 80 2=2 erp.start_time>=sysdate-:executed_within_days Number 365 US Executed within Days Usage window in days before today, by run start. Blank counts the whole retained history. 10 90 1=1 x.runs>0 LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Executed Yes lists the reports with a run within the usage window, No the others. 11 1=1 nvl(x.runs,0)=0 N US Executed 12 100 &migration_columns xxen_eis.sql_shape(err.report_id) "SQL Shape", xxen_eis.migration_tier(err.report_id) migration_tier, xxen_eis.xxeis_objects(err.report_id) "XXEIS Objects", LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual F Drapeau semé US Show Migration Analysis Adds the SQL shape, the migration tier and the XXEIS objects that the Blitz Report generated by the EIS import (package XXEN_EIS) still references. Generates the SQL of every listed report, so restrict the selection. 13 110 &source_sql case y.source_type when 'Paste SQL' then err.paste_view_sql when 'Database View' then ( select xxen_util.long_to_clob('SYS.VIEW$','TEXT',v.rowid) from all_users au, sys."_CURRENT_EDITION_OBJ" ceo, sys.view$ v where y.view_owner=au.username and au.user_id=ceo.owner# and err.view_name=ceo.name and ceo.obj#=v.obj# ) else y.view_sql end "Source SQL", LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show SQL Adds the SQL that EIS runs the report from: the pasted SQL, the SQL of an EIS SQL view or the database view text. Excel cuts a cell after 32,767 characters.