DIS Worksheet Execution History

Description
Categories: Application, Enginatics
Discoverer worksheet access statistics from table eul5_qpp_stats, including folder objects used.
Parameter 'Show Folder Details' switches between aggregate and list view of used folder objects.
select distinct
eqs.qs_id id,
xxen_util.disco_user_name(eqs.qs_created_by) user_name,
eqs.qs_doc_name workbook,
ed.doc_developer_key workbook_identifier,
eqs.qs_doc_details sheet,
xxen_util.client_time(eqs.qs_created_date) start_date,
xxen_util.time(eqs.seconds) time,
eqs.seconds,
eqs.qs_num_rows row_count,
&object_columns
length(eqs.qs_object_use_key)-length(translate(eqs.qs_object_use_key,'x.','x'))+1 folder_count,
xxen_util.meaning(case when eqs.qs_object_use_key like '%.%' then 'Y' end,'YES_NO',0) multiple_flag,
eqs.qs_object_use_key use_key
from
(
select
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id,
greatest(nvl(eqs.qs_act_cpu_time,0),nvl(eqs.qs_act_elap_time,0)) seconds,
eqs.*
from
&eul.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
where
1=1
) eqs,
&eul.eul5_objs eo,
(
select
ed.doc_name,
fu.user_name,
ed.doc_developer_key
from
&eul.eul5_documents ed,
&eul.eul5_eul_users eeu,
fnd_user fu
where
ed.doc_eu_id=eeu.eu_id and
case when eeu.eu_username like '#%' and eeu.eu_username not like '#%#%' then to_number(substr(eeu.eu_username,2)) end=fu.user_id
) ed
where
2=2 and
translate(eqs.obj_id,'x0123456789','x') is null and
eqs.obj_id=eo.obj_id(+) and
eqs.qs_doc_name=ed.doc_name(+) and
eqs.qs_doc_owner=ed.user_name(+)
order by
eqs.qs_id desc
Parameter Name SQL text Validation
Submitted by User
eqs.qs_created_by in (select '#'||fu.user_id from fnd_user fu where fu.user_name=:user_name)
LOV
End User Layer
<parameter_value>
LOV
Business Area
eqs.qs_id in
(
select
eqs0.qs_id
from
(
select
eqs.qs_id,
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id
from
eul_us.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
) eqs0
where
eqs0.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
Workbook
eqs.qs_doc_name=:workbook
LOV
Folder
eqs.qs_id in
(
select
eqs0.qs_id
from
(
select
eqs.qs_id,
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id
from
eul_us.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
) eqs0
where
eqs0.obj_id in (
select
eo.obj_id
from
eul_us.eul5_objs eo
where
eo.obj_name=:folder
)
)
LOV
View Name
eqs.qs_id in
(
select
eqs0.qs_id
from
(
select
eqs.qs_id,
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id
from
eul_us.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
) eqs0
where
eqs0.obj_id in (
select
eo.obj_id
from
eul_us.eul5_objs eo
where
eo.sobj_ext_table=:view_name
)
)
LOV
Show Folder Details
listagg(eo.obj_name,chr(10)) within group (order by eo.obj_name) over (partition by eqs.qs_id) folders,
listagg(eo.obj_developer_key,chr(10)) within group (order by eo.obj_developer_key) over (partition by eqs.qs_id) folder_identifiers,
listagg(eo.sobj_ext_table,chr(10)) within group (order by eo.sobj_ext_table) over (partition by eqs.qs_id) view_names,
listagg(eo.obj_description,chr(10)) within group (order by eo.obj_description) over (partition by eqs.qs_id) folder_descriptions,
Show Folder Details
eo.obj_name folder,
eo.obj_developer_key folder_identifier,
eo.sobj_ext_table object_name,
eo.obj_description folder_description,
decode(eo.obj_type,'SOBJ','Standard','COBJ','Complex','CUO','Custom') folder_type,
eqs.obj_id folder_id,
LOV Oracle
Start Date To
eqs.qs_created_date<=:start_date_to
DateTime
Start Date From
eqs.qs_created_date>=:start_date_from
DateTime
Started within Days
eqs.qs_created_date>sysdate-:days
Number