DIS Workbooks, Folders, Items and LOVs
Description
Categories: Enginatics, Toolkit - Setup & Support
Repository: Github
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 |
|
LOV | |
Folder |
|
LOV | |
View Name |
|
LOV | |
Access Count within x Days |
|
Number | |
Show Active only |
|
LOV | |
Display Level |
|
LOV | |
End User Layer |
|
LOV |
Blitz Report™