ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5C69C71956B772EEE0630100007F5EA9Y select era.application_name application, errc.category_name category, err.report_name, err.report_id "EIS Report ID", err.view_name, xxen_util.yes(err.seeded_flag) seeded, errp.display_order, errp.parameter_name, errp.description, errp.data_type, u.operator, case when ervc.column_name is not null then ltrim(ervc_comp.alias_name||'.'||ervc.column_name,'.') end column_name, ervc.data_type view_column_data_type, erl.lov_name "LOV Name", case when erl.lov_query is not null then 'SQL' when erl.lov_values is not null then 'Values' when erl.value_set_id is not null then 'Value Set' end "LOV Type", coalesce(erl.lov_query,to_clob(erl.lov_values),to_clob(ffvs.flex_value_set_name)) "LOV Query", case when errp.default_value is not null or errp.default_type='CURRENT_DATE' then decode(errp.default_type,'CONSTANT','Constant','CURRENT_DATE','Current Date',errp.default_type) end default_type, errp.default_value, xxen_util.yes(errp.required_flag) required, xxen_util.yes(errp.display_flag) displayed, xxen_util.yes(errp.enabled_flag) enabled, xxen_util.yes(errp.multiple_entires_allowed) multiple_values, errp.date_list, xxen_util.yes(errp.expand_gl_parameter) "GL Expand", erdpm.parent_parameter, erdpm.parent_filter, nvl(u.used_in,'None') used_in, xxen_util.yes(case when regexp_like(erl.lov_query,'xxeis\.','i') or regexp_like(errp.default_value,'xxeis\.','i') then 'Y' end) "XXEIS Reference" from xxeis.eis_rs_reports err, xxeis.eis_rs_applications era, xxeis.eis_rs_report_categories errc, xxeis.eis_rs_report_parameters errp, xxeis.eis_rs_view_columns ervc, xxeis.eis_rs_view_components ervc_comp, xxeis.eis_rs_lovs erl, fnd_flex_value_sets ffvs, ( select erdpm.parameter_id, listagg(errp_p.parameter_name,', ') within group (order by errp_p.display_order) parent_parameter, listagg( case when erdpm.operator='FREE_TEXT' then erdpm.free_text else erdpm.column_name||' '||decode(erdpm.operator,'EQUALS','=','GREATERTHAN EQUALS','>=',erdpm.operator)||' :'||errp_p.parameter_name end|| decode(erdpm.optional,'Y',' (optional)'),chr(10) ) within group (order by errp_p.display_order) parent_filter from xxeis.eis_rs_dependant_param_maps erdpm, xxeis.eis_rs_report_parameters errp_p where erdpm.dependant_param_id=errp_p.parameter_id group by erdpm.parameter_id ) erdpm, ( select y.parameter_id, listagg(case when y.used_in_rank=1 then y.used_in end,', ') within group (order by y.sort_order) used_in, listagg( case when y.operator_rank=1 then decode(y.operator,'EQUALS','=','NOTEQUALS','<>','NOT_EQUAL','<>','GREATER_THAN','>','LESS_THAN','<','GREATER_THAN_EQUALS','>=','LESS_THAN_EQUALS','<=','NOTIN','NOT IN',replace(y.operator,'_',' ')) end,', ' ) within group (order by y.operator) operator from ( select x.parameter_id, x.used_in, x.sort_order, x.operator, row_number() over (partition by x.parameter_id, x.used_in order by null) used_in_rank, row_number() over (partition by x.parameter_id, x.operator order by null) operator_rank from ( select w.parameter_id, w.used_in, w.sort_order, w.operator from ( select p.parameter_id, p.used_in, p.sort_order, p.operator, p.name_length, max(p.name_length) over (partition by p.text_id, regexp_instr(p.text,p.pattern,1,rowgen.column_value,0,'i')) max_name_length from ( select /*+ no_merge */ errp.parameter_id, errp.name_length, errp.pattern, t.used_in, t.sort_order, t.operator, t.text_id, t.text from ( select errp.report_id, errp.parameter_id, length(errp.parameter_name) name_length, ':'||regexp_replace(errp.parameter_name,'([][().\$|?*+{}])','[\1]')||'($|[^[:alnum:]_])' pattern from xxeis.eis_rs_report_parameters errp ) errp, ( select t.report_id, t.used_in, t.sort_order, t.operator, t.text_id, regexp_replace(t.text,'--[^'||chr(10)||']*|/\*.*?\*/',null,1,0,'n') text from ( select errch.report_id, decode(rowgen.column_value,4,'Free Text','Condition') used_in, decode(rowgen.column_value,4,2,1) sort_order, case when rowgen.column_value<4 then errcd.operator end operator, 'D'||errcd.detail_id||'.'||rowgen.column_value text_id, to_clob(decode(rowgen.column_value,1,errcd.item,2,errcd.value1,3,errcd.value2,errcd.free_text)) text from xxeis.eis_rs_report_cond_headers errch, xxeis.eis_rs_report_cond_details errcd, table(xxen_util.rowgen(4)) rowgen where errch.enabled='Y' and errch.condition_id=errcd.condition_id and errcd.enabled='Y' and decode(rowgen.column_value,1,errcd.item,2,errcd.value1,3,errcd.value2,errcd.free_text) like '%:%' union all select errco.report_id, 'Derived Column' used_in, 3 sort_order, null operator, 'C'||errco.column_id text_id, to_clob(errco.calculation_column) text from xxeis.eis_rs_report_columns errco where errco.derived_flag='Y' and nvl(errco.enabled_flag,'Y')='Y' and errco.calculation_column like '%:%' union all select erso.report_id, 'Sort' used_in, 4 sort_order, null operator, 'S'||erso.sort_id text_id, to_clob(erso.free_text) text from xxeis.eis_rs_sorts erso where erso.free_text like '%:%' union all select errt.report_id, 'Trigger' used_in, 5 sort_order, null operator, 'T'||errt.trigger_id text_id, errt.trigger_text text from xxeis.eis_rs_report_triggers errt where errt.enable_flag='Y' and dbms_lob.instr(errt.trigger_text,':')>0 ) t ) t where errp.report_id=t.report_id and regexp_like(t.text,errp.pattern,'i') ) p, table(xxen_util.rowgen(regexp_count(p.text,p.pattern,1,'i'))) rowgen ) w where w.name_length=w.max_name_length union all select decode(rowgen.column_value,1,errcd.item_report_parameter_id,2,errcd.value1_report_parameter_id,errcd.value2_report_parameter_id) parameter_id, 'Condition' used_in, 1 sort_order, errcd.operator from xxeis.eis_rs_report_cond_headers errch, xxeis.eis_rs_report_cond_details errcd, table(xxen_util.rowgen(3)) rowgen where errch.enabled='Y' and errch.condition_id=errcd.condition_id and errcd.enabled='Y' union all select erdpm.dependant_param_id parameter_id, 'Dependent Parameter' used_in, 6 sort_order, null operator from xxeis.eis_rs_dependant_param_maps erdpm ) x where x.parameter_id is not null ) y group by y.parameter_id ) u where 1=1 and err.application_id=era.application_id and err.category_id=errc.category_id(+) and err.report_id=errp.report_id and errp.column_id=ervc.view_column_id(+) and errp.view_component_id=ervc_comp.view_component_id(+) and errp.lov_id=erl.lov_id(+) and to_number(erl.value_set_id)=ffvs.flex_value_set_id(+) and errp.parameter_id=erdpm.parameter_id(+) and errp.parameter_id=u.parameter_id(+) order by era.application_name, errc.category_name, err.report_name, errp.display_order, errp.parameter_id Initial version: one row per EIS report parameter with the operator of the conditions using it, filtered view column, LOV, default, flags, parent parameters of dependent LOVs and where EIS uses the parameter value. US EIS Report Parameters EIS eXpress report parameter definitions: one row per parameter of an EIS report (eis_rs_report_parameters), including disabled and hidden parameters. Operator comes from the conditions that use the parameter, which EIS applies, and not from the operator stored on the parameter; the two differ on some parameters. It is blank for a parameter that no enabled condition uses. Column Name is the view column the parameter was created for; an advanced condition can apply the parameter to an expression instead. Used In lists where EIS substitutes the parameter value: Condition (by reference or as :Parameter Name in a condition expression), Free Text, Derived Column (calculation of an enabled column), Sort (free text sort), Trigger and Dependent Parameter (restricts the LOV of another parameter). Disabled conditions and columns are ignored, so None means the parameter has no effect on the report. Displayed blank marks a hidden parameter, which runs with its default. Parent Parameter and Parent Filter show the parameters that restrict this parameter's LOV, with the LOV column and operator. LOV Query holds the LOV's SQL, its fixed value list or the name of its value set. XXEIS Reference flags an LOV or default SQL that uses an object of the XXEIS schema, which remains a dependency on EIS after a migration. Executed within Days keeps the reports run from eXpress or XL Connect in that window. EIS purges its run history; EIS Execution History shows the earliest retained run. EIS 1=1 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 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 US Seeded 5 1=1 err.seeded_flag is null N US Seeded 6 50 1=1 err.report_id in (select /*+ no_unnest */ erp.report_id from xxeis.eis_rs_processes erp where erp.submission_source in ('eXpress','XL Connect') and erp.start_time>=sysdate-:executed_within_days) Number 365 US Executed within Days Reports run within this many days before today. Blank includes reports without runs. 7 60 1=1 nvl(u.used_in,'None') like '%'||:used_in||'%' LOV custom Y select 'Condition' value, 'Simple or advanced condition' description from dual union all select 'Free Text', 'Free text condition' from dual union all select 'Derived Column', 'Calculation of an enabled derived column' from dual union all select 'Sort', 'Free text sort' from dual union all select 'Trigger', 'Before or after report trigger' from dual union all select 'Dependent Parameter', 'Restricts the LOV of another parameter' from dual union all select 'None', 'Not used by the report' from dual US Used In Where EIS substitutes the parameter value. None lists the parameters that have no effect on the report. 8 70 1=1 erl.lov_name=:lov_name LOV custom Y select distinct erl.lov_name value, null description from xxeis.eis_rs_lovs erl where erl.lov_id in (select errp.lov_id from xxeis.eis_rs_report_parameters errp) order by erl.lov_name US LOV Name 9 80 1=1 (regexp_like(erl.lov_query,'xxeis\.','i') or regexp_like(errp.default_value,'xxeis\.','i')) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US XXEIS Reference Parameters whose LOV query or default value uses an object of the XXEIS schema.