ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5C69E66DDF03056DE0630100007F964CY select x.application, x.category, x.report_name, x."EIS Report ID", x.view_name, x.seeded, x.condition_seq, x.condition_name, x.condition_type, x.enabled, x.detail_seq, x.item_type, x.item, x.operator, x.value, x.value2, x.advanced_template, x.condition_text, xxen_util.yes(x.hardcoded) hardcoded from ( select y.*, case when y.condition_text is not null and count(case when regexp_like(y.condition_text,':[[:alpha:]]') then 1 end) over (partition by y.condition_id)=0 then 'Y' end hardcoded from ( 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, errch.display_order condition_seq, errch.condition_name, initcap(replace(errch.condition_type,'_',' ')) condition_type, xxen_util.yes(errch.enabled) enabled, errcd.display_order detail_seq, d.item_type, d.item, d.operator, d.value1 value, d.value2, decode(errch.condition_type,'ADVANCED',trim(errch.advanced_condition)) advanced_template, case when d.free_text is not null then d.free_text when d.item is null then null when d.operator in ('is null','is not null') then d.item||' '||d.operator when d.value1 is null then null when d.operator='between' then d.item||' between '||d.value1||' and '||d.value2 when d.operator in ('in','not in') then d.item||' '||d.operator||' ('||d.value1||')' else d.item||' '||d.operator||' '||d.value1 end condition_text, errch.condition_id, errcd.detail_id from xxeis.eis_rs_reports err, xxeis.eis_rs_applications era, xxeis.eis_rs_report_categories errc, xxeis.eis_rs_report_cond_headers errch, xxeis.eis_rs_report_cond_details errcd, ( select o.detail_id, o.operator, o.free_text, max(decode(o.role,1,o.operand_type)) item_type, max(decode(o.role,1,o.operand)) item, max(decode(o.role,2,o.operand)) value1, max(decode(o.role,3,o.operand)) value2 from ( select c.detail_id, c.role, c.operator, c.free_text, case when c.text is not null then 'Expression' when c.view_column_id is not null and ervc.view_column_id is not null then 'View Column' when c.column_id is not null then 'Report Column' when errp.parameter_id is not null then 'Parameter' end operand_type, case when c.text is not null then c.text when c.view_column_id is null and c.derived_flag='Y' then c.calculation_column when ervc.view_column_id is not null then coalesce( ervc_comp.alias_name, ( select min(ervc_comp2.alias_name) from xxeis.eis_rs_report_views errv, xxeis.eis_rs_view_components ervc_comp2 where c.report_id=errv.report_id and ervc.view_id=errv.view_id and errv.view_component_id=ervc_comp2.view_component_id having count(*)=1 ), erv.view_alias )||'.'||ervc.column_name when c.column_id is not null then c.column_name when errp.parameter_id is not null then ':'||errp.parameter_name end operand from ( select z.*, errc_col.column_id, errc_col.column_name, errc_col.derived_flag, errc_col.calculation_column, nvl(z.view_column_id,errc_col.view_column_id) resolved_view_column_id, nvl(z.view_component_id,errc_col.view_component_id) resolved_view_component_id from ( select errch.report_id, errcd.detail_id, rowgen.column_value role, decode(errcd.operator, 'IN','in', 'NOTIN','not in', 'EQUALS','=', 'NOTEQUALS','<>', 'NOT_EQUAL','<>', 'GREATER_THAN','>', 'GREATER_THAN_EQUALS','>=', 'LESS_THAN','<', 'LESS_THAN_EQUALS','<=', 'LIKE','like', 'BETWEEN','between', 'IS','is', 'IS_NULL','is null', 'IS_NOT_NULL','is not null' ) operator, regexp_replace(errcd.free_text,'^\s*and\s*|\s+$','',1,0,'i') free_text, trim(decode(rowgen.column_value,1,errcd.item,2,errcd.value1,3,errcd.value2)) text, decode(rowgen.column_value,1,errcd.item_view_column_id,2,errcd.value1_view_column_id,3,errcd.value2_view_column_id) view_column_id, decode(rowgen.column_value,1,errcd.item_view_component_id,2,to_number(errcd.value1_view_component_id),3,to_number(errcd.value2_view_component_id)) view_component_id, decode(rowgen.column_value,1,errcd.item_report_column_id,2,errcd.value1_report_column_id,3,errcd.value2_report_column_id) report_column_id, decode(rowgen.column_value,1,errcd.item_report_parameter_id,2,errcd.value1_report_parameter_id,3,errcd.value2_report_parameter_id) parameter_id from xxeis.eis_rs_report_cond_headers errch, xxeis.eis_rs_report_cond_details errcd, table(xxen_util.rowgen(3)) rowgen where errch.condition_id=errcd.condition_id ) z, xxeis.eis_rs_report_columns errc_col where z.report_column_id=errc_col.column_id(+) ) c, xxeis.eis_rs_view_columns ervc, xxeis.eis_rs_views erv, xxeis.eis_rs_view_components ervc_comp, xxeis.eis_rs_report_parameters errp where c.resolved_view_column_id=ervc.view_column_id(+) and ervc.view_id=erv.view_id(+) and c.resolved_view_component_id=ervc_comp.view_component_id(+) and c.parameter_id=errp.parameter_id(+) ) o group by o.detail_id, o.operator, o.free_text ) d where 1=1 and err.application_id=era.application_id and err.category_id=errc.category_id(+) and err.report_id=errch.report_id and errch.condition_id=errcd.condition_id and errcd.detail_id=d.detail_id ) y ) x where 2=2 order by x.application, x.category, x.report_name, x.condition_seq, x.condition_id, x.detail_seq, x.detail_id Initial version: one row per EIS report condition detail with rendered condition text, advanced template and hardcoded flag. US EIS Report Conditions EIS eXpress report conditions: one row per condition detail (eis_rs_report_cond_details). A Simple or Advanced condition has one detail per term, a Free Text condition one detail holding its SQL fragment, shown without the leading and. Condition Text renders the term as EIS adds it to the report SQL: item (view column with its alias, report column expression, expression text or :Parameter Name), operator and value. At run time EIS replaces :Parameter Name by the value as a literal, skips a condition whose parameter is empty and appends % to a like parameter value. Condition Text is blank for an incomplete detail without item or value. Advanced Template combines the details of an Advanced condition: N#$# is the detail with Detail Seq N. In a condition with several details, EIS replaces a detail whose parameter is empty by 1=1. Condition Name is the name EIS stored when the condition was created and can still show the alias of a view or report it was copied from. Hardcoded flags conditions that reference no parameter in any detail, so they apply to every run. 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 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 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 errch.condition_type=:condition_type LOV custom Y select 'SIMPLE' id, 'Simple' value, 'Column, operator and value' description from dual union all select 'ADVANCED', 'Advanced', 'Details combined by a template' from dual union all select 'FREE_TEXT', 'Free Text', 'SQL fragment' from dual US Condition Type 8 70 1=1 errch.enabled=: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 9 80 2=2 x.hardcoded='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 Hardcoded 10 2=2 x.hardcoded is null N US Hardcoded