DIS Business Areas

Description
Categories: Enginatics
Repository: Github Columns: Business Area, Business Area Description, Business Area Identifier, Access Count, Last Accessed, Folder Count, Created By, Creation Date, Last Updated By, Last Update Date ...
Summary report showing Discoverer business areas, with an access account showing how many times a business area's folder was used within the past x number of days.
select
eb.ba_name business_area,
eb.ba_description business_area_description,
eb.ba_developer_key business_area_identifier,
eqs.access_count,
eqs.last_accessed,
(select count(*) from &eul.eul5_ba_obj_links ebol where eb.ba_id=ebol.bol_ba_id) folder_count,
xxen_util.dis_user_name(eb.ba_created_by) created_by,
xxen_util.client_time(eb.ba_created_date) creation_date,
xxen_util.dis_user_name(eb.ba_updated_by) last_updated_by,
xxen_util.client_time(eb.ba_updated_date) last_update_date
from
&eul.eul5_bas eb,
(
select
eqs.bol_ba_id,
count(*) access_count,
max(eqs.qs_created_date) last_accessed
from
(
select distinct
eqs.qs_id,
ebol.bol_ba_id,
eqs.qs_created_date
from
(
select
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id,
eqs.*
from
&eul.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
where
2=2
) eqs,
&eul.eul5_ba_obj_links ebol
where
translate(eqs.obj_id,'x0123456789','x') is null and
eqs.obj_id=ebol.bol_obj_id
) eqs
group by
eqs.bol_ba_id
) eqs
where
1=1 and
eb.ba_id=eqs.bol_ba_id(+)
order by
eb.ba_id
Parameter Name SQL text Validation
Show Active Only
eqs.bol_ba_id is not null
LOV
End User Layer
<parameter_value>
LOV
Business Area
eb.ba_name=:business_area
LOV
Access Count within x Days
eqs.qs_created_date>sysdate-:days
Number