DIS Worksheet Execution History

Description

Categories: Enginatics
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.

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

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 object_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,
upper(eqs.qs_doc_owner) qs_doc_owner_,
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,
&eul.eul5_documents 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_=xxen_util.dis_user_name(ed.doc_eu_id(+),:eul,'N')
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 Use Key

eqs.qs_object_use_key=:object_use_key

Char

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

Date

Start Date To

eqs.qs_created_date<:start_date_to+1

Date

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,upper(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,