DIS Worksheet Execution Summary

Description
Categories: Enginatics
Repository: Github
Discoverer worksheet access statistic summary from table eul5_qpp_stats to show the number of active Discoverer users, number of different workbooks and worksheets executed and the number of different folder (combinations) that these are based on.
Run DIS Worksheet Execution Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
select distinct
&month_column
count(*) over (&partition_by) execution_count,
count(distinct x.qs_created_by) over (&partition_by) user_count,
count(distinct x.workbook_owner||x.workbook) over (&partition_by) workbook_count,
count(distinct x.workbook_owner||x.workbook||x.sheet) over (&partition_by) sheet_count,
count(distinct x.use_key) over (&partition_by) folder_count,
count(distinct decode(x.folder_count,1,x.use_key)) over (&partition_by) "1 Folder Count",
count(distinct decode(x.folder_count,2,x.use_key)) over (&partition_by) "2 Folder Count",
count(distinct decode(x.folder_count,3,x.use_key)) over (&partition_by) "3 Folder Count",
count(distinct decode(x.folder_count,4,x.use_key)) over (&partition_by) "4 Folder Count",
count(distinct decode(x.folder_count,5,x.use_key)) over (&partition_by) "5 Folder Count",
count(distinct case when x.folder_count>5 then x.use_key end) over (&partition_by) ">5 Folder Count"
from
(
select
eqs.qs_id id,
eqs.qs_created_by,
eqs.qs_doc_owner workbook_owner,
eqs.qs_doc_name workbook,
eqs.qs_doc_details sheet,
trunc(eqs.qs_created_date,'month') month,
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
from
&eul.eul5_qpp_stats eqs
where
1=1
) x
&order_by
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
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
Show By Month
x.month,
LOV
End User Layer
<parameter_value>
LOV