DIS Folders, Business Areas, Items and LOVs

Description

Categories: Enginatics, Toolkit - Setup & Support
Repository: Github

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.

DIS Folders, Business Areas, Items and LOVs and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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

Business Area

eo.obj_id in (
select
ebol.bol_obj_id
from
eul_us.eul5_bas eb,
eul_us.eul5_ba_obj_links ebol
where
eb.ba_name=:business_area and
eb.ba_id=ebol.bol_ba_id
)

LOV

Folder

eo.obj_name like :folder

LOV

Folder Identifier

eo.obj_developer_key=:folder_identifier

LOV

Folder Type

eo.obj_type=decode(:folder_type,'Standard','SOBJ','Complex view','COBJ','Custom SQL','CUO')

LOV

View Name

eo.sobj_ext_table=:view_name

LOV

Object Id

eo.obj_id=:object_id

Number

Used by Workbook

eo.obj_developer_key in (
select
eex.ex_to_par_devkey
from
eul_us.eul5_documents ed,
eul_us.eul5_elem_xrefs eex
where
ed.doc_name=:workbook and
ed.doc_id=eex.ex_from_id and
eex.ex_from_type='DOC'
)

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

Access Count within x Days

eqs.qs_created_date>sysdate-:days

Number

Show Active only

eqs.obj_id is not null

LOV

Show Items

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

Item

Item Type

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,