DIS Workbooks, Folders, Items and LOVs

➜ Back to Library
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.
Run DIS Workbooks, Folders, Items and LOVs and other Oracle EBS reports with Blitz Report™ on our demo environment
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,
nvl(xxen_util.dis_user_name(ed.doc_eu_id,:eul),xxen_util.dis_user(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_sql3(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,
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
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
Show Active only 
eqs.qs_doc_name is not null
 LOV
Display Level 
 
 LOV
End User Layer 
<parameter_value>
 LOV
Download
Related Blitz Reports
DIS Folders, Business Areas, Items and LOVs DIS Items, Folders and Formulas DIS Worksheet Execution History DIS Access Privileges DIS Business Areas CST Item Cost Reports DIS Users DIS Worksheet Execution Summary DIS Worksheet SQLs
Blitz Report™

Blitz Report™ provides multiple benefits:

Blitz Report™