Reports2017-11-18T12:27:27+00:00

DIS Workbooks, Folders and Items

Description
Categories: Application, Enginatics
Discoverer workbooks, their owners, folders and items accessed, derived from dependency table eul5_elem_xrefs.

select distinct
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
from
(
select
ed.doc_name workbook,
ed.doc_description description,
xxen_util.disco_user_name(ed.doc_eu_id,'&eul') owner,
ed.doc_developer_key identifier,
eqs.access_count,
eqs.last_accessed,
xxen_util.disco_user_name(ed.doc_updated_by) last_updated_by,
ed.doc_updated_date last_update_date,
(
select distinct
listagg(eb.ba_name,chr(10)) within group (order by eb.ba_name) over (partition by ebol.bol_obj_id) business_area
from
&eul.eul5_ba_obj_links ebol,
&eul.eul5_bas eb
where
eo.obj_id=ebol.bol_obj_id and
ebol.bol_ba_id=eb.ba_id
) business_area,
eo.obj_name folder,
eo.obj_developer_key folder_identifier,
decode(eo.obj_type,'SOBJ','Standard','COBJ','Complex','CUO','Custom') 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,
eo.obj_id,
eex.ex_to_devkey,
eex.ex_to_type
from
&eul.eul5_documents ed,
(select eex.* from &eul.eul5_elem_xrefs eex where :display_level in ('Folders','Items')) eex,
&eul.eul5_objs 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_from_type(+)='DOC' 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,
(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
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
Display Level
,x.folder
Display Level
,x.folder
Display Level
,coalesce(ee.exp_name,ekc.key_name,ef.fun_name) item,
coalesce(
decode(ee.exp_type,'FIL','Condition','CI','Calculated Item','CO','Item','JP','Join Predicate',ee.exp_type),
decode(x.ex_to_type,'ITE','Item','JOI','Join','FUN','Function','FIL','Condition',x.ex_to_type)
) item_type,
decode(ee.fil_runtime_filter,1,'Optional',0,'Mandatory') condition_type,
coalesce(ee.it_ext_column,xxen_util.disco_formula(ee.exp_formula1),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,
edo.dom_name item_class_lov
Display Level
,x.business_area,
x.folder,
x.folder_type,
x.object_type,
x.object_name
Display Level
,x.business_area,
x.folder,
x.folder_type,
x.object_type,
x.object_name
Display Level
 
LOV
Access Count within x Days
eqs.qs_created_date>sysdate-:days
Number
View Name
eo.sobj_ext_table=:view_name
LOV
Folder
eo.obj_name=:folder
LOV

By continuing to use the site, you agree to the use of cookies. Accept