DIS Workbooks, Folders, Items and LOVs

Description
Categories: Enginatics, Toolkit - Setup & Support
Repository: Github
Discoverer workbooks, their owners, folders, items and item class LOVs, derived from dependency table eul5_elem_xrefs.
select
x.workbook,
x.description,
x.owner,
x.identifier workbook_identifier,
x.access_count,
x.last_accessed,
x.last_updated_by,
x.last_update_date,
&folder_columns
&item_columns
x.doc_id
from
(
select
ed.doc_name workbook,
ed.doc_description description,
xxen_util.dis_user_name(ed.doc_eu_id,'&eul') owner,
ed.doc_developer_key identifier,
eqs.access_count,
eqs.last_accessed,
xxen_util.dis_user_name(ed.doc_updated_by) last_updated_by,
ed.doc_updated_date last_update_date,
xxen_util.dis_business_area(eo.obj_id,'&eul') business_area,
eo.obj_name folder,
eo.obj_developer_key folder_identifier,
xxen_util.dis_folder_type(eo.obj_type) folder_type,
nvl2(eo.sobj_ext_table,nvl2((select dv.view_name from dba_views dv where eo.sobj_ext_table=dv.view_name and dv.owner='APPS'),'View','Table'),null) object_type,
eo.sobj_ext_table object_name,
xxen_util.dis_folder_sql2(eo.obj_id,'eul_pmi') folder_sql,
case when lower(dbms_lob.substr(eo.text,15,length(eo.text)-14))=' with read only' then substr(eo.text,1,length(eo.text)-15) else eo.text end view_sql,
eo.obj_description folder_description,
eo.obj_id,
eex.ex_to_devkey,
eex.ex_to_type,
ed.doc_id
from
&eul.eul5_documents ed,
(select distinct eex.ex_from_id, eex.ex_to_par_devkey, decode(:display_level,'Items',eex.ex_to_type) ex_to_type, decode(:display_level,'Items',eex.ex_to_devkey) ex_to_devkey from &eul.eul5_elem_xrefs eex where eex.ex_from_type='DOC' and :display_level in ('Folders','Items')) eex,
(
select
(
select
xxen_util.long_to_clob('SYS.VIEW$', 'TEXT', v.rowid) text
from
sys."_CURRENT_EDITION_OBJ" o,
sys.view$ v,
sys.user$ u
where
u.name='APPS' and
eo.sobj_ext_table=o.name and
o.obj#=v.obj# and
o.owner#=u.user#
) text,
eo.*
from
&eul.eul5_objs eo
) eo,
(
select
eqs.qs_doc_name,
count(*) access_count,
max(eqs.qs_created_date) last_accessed
from
&eul.eul5_qpp_stats eqs
where
2=2
group by
eqs.qs_doc_name
) eqs
where
1=1 and
ed.doc_id=eex.ex_from_id(+) and
eex.ex_to_par_devkey=eo.obj_developer_key(+) and
ed.doc_name=eqs.qs_doc_name(+)
) x,
(
select ee.it_obj_id obj_id, ee.* from &eul.eul5_expressions ee where ee.exp_type in ('CI','CO') and ee.fil_obj_id is null and :display_level='Items' union all
select ee.fil_obj_id obj_id, ee.* from &eul.eul5_expressions ee where ee.exp_type='FIL' and ee.it_obj_id is null and :display_level='Items'
) ee,
&eul.eul5_domains edo,
&eul.eul5_expressions ee2,
&eul.eul5_objs eo2,
(select ekc.* from &eul.eul5_key_cons ekc where :display_level='Items') ekc,
(select ef.* from &eul.eul5_functions ef where :display_level='Items') ef
where
x.obj_id=ee.obj_id(+) and
case when x.ex_to_type in ('ITE','FIL') then x.ex_to_devkey end=ee.exp_developer_key(+) and
ee.it_dom_id=edo.dom_id(+) and
edo.dom_it_id_lov=ee2.exp_id(+) and
ee2.it_obj_id=eo2.obj_id(+) and
decode(x.ex_to_type,'JOI',x.ex_to_devkey)=ekc.key_developer_key(+) and
decode(x.ex_to_type,'FUN',x.ex_to_devkey)=ef.fun_developer_key(+)
order by
x.workbook
&order_by_folder
Parameter Name SQL text Validation
Show Active only
eqs.qs_doc_name is not null
LOV
End User Layer
<parameter_value>
LOV
Workbook
ed.doc_name=:workbook
LOV
Folder
eo.obj_name like :folder
LOV
View Name
eo.sobj_ext_table=:view_name
LOV
Access Count within x Days
eqs.qs_created_date>sysdate-:days
Number
Display Level
 
LOV
Display Level
x.business_area,
x.folder,
x.folder_type,
x.object_type,
x.object_name,
x.folder_sql,
x.view_sql,
x.folder_description,
x.obj_id,
Display Level
x.business_area,
x.folder,
x.folder_type,
x.object_type,
x.object_name,
x.folder_sql,
x.view_sql,
x.folder_description,
x.obj_id,
Display Level
coalesce(ee.exp_name,ekc.key_name,ef.fun_name) item_name,
coalesce(
xxen_util.dis_item_type(ee.exp_type),
decode(x.ex_to_type,'ITE','Item','JOI','Join','FUN','Function','FIL','Condition',x.ex_to_type)
) item_type,
lower(nvl2(ef.fun_ext_package,ef.fun_ext_package||'.',null)||ef.fun_ext_name) db_function_name,
decode(ee.fil_runtime_filter,1,'Optional',0,'Mandatory') condition_type,
coalesce(ee.it_ext_column,xxen_util.dis_formula_sql(ee.exp_id,'&eul'),ekc.key_description) column_or_formula,
decode(ee.exp_data_type,1,'Varchar',2,'Number',3,'Long',4,'Date',5,'Raw',6,'Large binary object',8,'Char',10,null,ee.exp_data_type) data_type,
eo2.obj_name lov_folder,
ee2.exp_name lov_item,
xxen_util.dis_lov_query(ee2.it_obj_id,ee2.it_ext_column,'&eul') lov_query,
edo.dom_name lov_item_class,
ee.exp_id,
Display Level
,x.folder
Display Level
,x.folder