DIS Worksheet Execution History

Description
Categories: Enginatics, Kcapps, Toolkit - Setup & Support
Repository: Github
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.
Run DIS Worksheet Execution History and other Oracle EBS reports with Blitz Report™ on our demo environment
select distinct
eqs.qs_id id,
xxen_util.dis_user_name(eqs.qs_created_by) user_name,
eqs.qs_doc_name workbook,
ed.doc_developer_key workbook_identifier,
eqs.qs_doc_details sheet,
eqs.qs_doc_owner owner,
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,
eqs.qs_object_use_key use_key,
eqs.qs_doc_name||': '||eqs.qs_doc_details||' ('||eqs.qs_doc_owner||')' workbook_sheet,
eqs.qs_created_by created_by
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
&restrict_to_latest_workbook1
&eul.eul5_qpp_stats eqs
&restrict_to_latest_workbook2
,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
Workbook
eqs.qs_doc_name=:workbook
LOV
Submitted by User
eqs.qs_created_by in (select '#'||fu.user_id from fnd_user fu where fu.user_name=:user_name)
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
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
Object Id
eqs.qs_object_use_key like '%'||:object_id||'%'
Number
Accessed within Days
eqs.qs_created_date>sysdate-:days
Number
Start Date From
eqs.qs_created_date>=:start_date_from
DateTime
Start Date To
eqs.qs_created_date<=:start_date_to
DateTime
Latest Workbook only
(select x.* from (select max(eqs.qs_object_use_key) keep (dense_rank last order by eqs.qs_id) over (partition by eqs.qs_doc_name,eqs.qs_doc_details,eqs.qs_doc_owner) max_object_use_key, eqs.* from
LOV
Show Folder Details
eo.obj_name folder,
eo.obj_developer_key folder_identifier,
eo.sobj_ext_table object_name,
eo.obj_description folder_description,
xxen_util.dis_folder_type(eo.obj_type) folder_type,
eqs.obj_id,
LOV Oracle
End User Layer
<parameter_value>
LOV