DIS Folders, Business Areas, Items and LOVs

➜ Back to Library
Description
Categories: Enginatics, Toolkit - Setup & Support
Repository: Github Columns: Business Area, Folder, Folder Identifier, Folder Type, Object Type, Object Name, Access Count, Last Accessed, Folder Sql, View Sql ...
Discoverer folders, business areas and items.
Columns 'Access Count' and 'Last Accessed' shows how many times a folder was accessed by a worksheet within the past x days.
Parameter 'Show Active Only' restricts to folders which have been accessed by worksheet executions within the past x days. 
select
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,
eqs.access_count,
eqs.last_accessed,
&sql_text_columns
eo.obj_description folder_description,
&item_columns
eo.obj_id,
xxen_util.dis_user_name(eo.obj_created_by) created_by,
xxen_util.client_time(eo.obj_created_date) creation_date,
xxen_util.dis_user_name(eo.obj_updated_by) last_updated_by,
xxen_util.client_time(eo.obj_updated_date) last_update_date
from
(
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 ee.it_obj_id obj_id, ee.* from &eul.eul5_expressions ee where '&show_items'='Y' and ee.exp_type in ('CI','CO') and ee.fil_obj_id is null union all
select ee.fil_obj_id obj_id, ee.* from &eul.eul5_expressions ee where '&show_items'='Y' and ee.exp_type='FIL' and ee.it_obj_id is null
) ee,
&eul.eul5_domains edo,
&eul.eul5_expressions ee2,
&eul.eul5_objs eo2,
(
select
eqs.obj_id,
count(*) access_count,
max(eqs.qs_created_date) last_accessed
from
(
select
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id,
eqs.*
from
&eul.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
where
2=2
) eqs
where
translate(eqs.obj_id,'x0123456789','x') is null
group by
eqs.obj_id
) eqs
where
1=1 and
eo.obj_id=ee.obj_id(+) 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
eo.obj_id=eqs.obj_id(+)
order by
eo.obj_name,
decode(ee.exp_type,'FIL',2,1),
ee.exp_sequence
Parameter Name SQL text Validation
Show Active only 
eqs.obj_id is not null
 LOV
Show Items 
Y
 LOV
Show Object SQL 
xxen_util.dis_folder_sql2(eo.obj_id,'&eul') 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,
 LOV
End User Layer 
<parameter_value>
 LOV
Business Area 
eo.obj_id in (
select
ebol.bol_obj_id
from
eul_us1.eul5_bas eb,
eul_us1.eul5_ba_obj_links ebol
where
eb.ba_name=:business_area and
eb.ba_id=ebol.bol_ba_id
)
 LOV
Used by Workbook 
eo.obj_developer_key in (
select
eex.ex_to_par_devkey
from
eul_us1.eul5_documents ed,
eul_us1.eul5_elem_xrefs eex
where
ed.doc_name=:workbook and
ed.doc_id=eex.ex_from_id and
eex.ex_from_type='DOC'
)
 LOV
Folder 
eo.obj_name like :folder
 LOV
Folder Type 
eo.obj_type=decode(:folder_type,'Standard','SOBJ','Complex view','COBJ','Custom SQL','CUO')
 LOV
Item 
ee.exp_name=:item
 LOV
Item Type 
ee.exp_type=decode(:item_type,'Condition','FIL','Calculated Item','CI','Item','CO','Join Predicate','JP')
 LOV
View Name 
eo.sobj_ext_table=:view_name
 LOV
Folder Identifier 
eo.obj_developer_key=:folder_identifier
 LOV
Object Id 
eo.obj_id=:object_id
 Number
Access Count within x Days 
eqs.qs_created_date>sysdate-:days
 Number
Item 
Y
Item Type 
Y
Show Items 
ee.exp_name item_name,
xxen_util.dis_item_type(ee.exp_type) item_type,
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')) 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,
Download
 
Related Blitz Reports
DIS Workbooks, Folders, Items and LOVs DIS Items, Folders and Formulas DIS Worksheet Execution History DIS Worksheet Execution Summary DIS Access Privileges DIS Business Areas DIS End User Layers DIS Users DIS Worksheet SQLs
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: