Blitz Report Security

Description
Categories: Enginatics
Repository: Github
Shows all Enginatics reports and their security, for example parameter or SQL restrictions.
Run Blitz Report Security and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
select
xrv.report_name,
xrv.category,
nvl2(xrpv1.report_id,'Ledger',null) ledger_parameter,
nvl2(xrpv2.report_id,'Operating Unit',null) operating_unit_parameter,
nvl2(xrpv3.report_id,'Organization Code',null) inv_org_parameter,
xxen_util.meaning(nvl(xrpv1.required,case when lower(xrv.required_parameters) like '%:ledger is not null%' then 'Y' end),'YES_NO',0) gl_required,
xxen_util.meaning(nvl(xrpv2.required,case when lower(xrv.required_parameters) like '%:operating_unit is not null%' then 'Y' end),'YES_NO',0) ou_required,
xxen_util.meaning(nvl(xrpv3.required,case when lower(xrv.required_parameters) like '%:organization_code is not null%' then 'Y' end),'YES_NO',0) inv_required,
case when xrv.sql_text_full like '%gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v%' or xrv.sql_text_full like '%gl_access_set_assignments%' then xxen_util.meaning('Y','YES_NO',0) end gl_restricted,
case when xrv.sql_text_full like '%from mo_glob_org_access_tmp mgoat%' then xxen_util.meaning('Y','YES_NO',0) end ou_restricted,
case when xrv.sql_text_full like '%from org_access_view oav%' then xxen_util.meaning('Y','YES_NO',0) end inv_restricted,
xrv.required_parameters advanced_required_parameters
from
xxen_reports_v xrv,
(select xrpv.* from xxen_report_parameters_v xrpv where xrpv.lov_name in ('GL Ledger','GL Ledger (Asset Book Secured)','GL Ledger (restricted by AR System Parameters)')) xrpv1,
(select xrpv.* from xxen_report_parameters_v xrpv where xrpv.lov_name in ('HR Operating Unit')) xrpv2,
(select xrpv.* from xxen_report_parameters_v xrpv where xrpv.lov_name in ('INV Organization Code','INV Organization Code (including master)')) xrpv3
where
1=1 and
xrv.type is null and
xrv.report_name not like 'AD %' and
xrv.report_name not like 'Blitz Report%' and
xrv.report_name not like 'DBA %' and
xrv.report_name not like 'DIS %' and
xrv.report_name not like 'ECC %' and
xrv.report_name not like 'FND %' and
xrv.report_name not like 'JTF Grid %' and
xrv.report_name not like 'WF %' and
xrv.report_name not like 'XDO %' and
xrv.report_name not like 'XLA %' and
xrv.report_name not like 'XLE %' and
xrv.report_name not like 'ZX %' and
xrv.report_id=xrpv1.report_id(+) and
xrv.report_id=xrpv2.report_id(+) and
xrv.report_id=xrpv3.report_id(+)
) x
where
2=2
order by
x.report_name
Parameter Name SQL text Validation
Category
xrv.report_id in (
select
xrca.report_id
from
xxen_report_category_assigns xrca,
xxen_report_categories_v xrcv
where
xrcv.category=:category and
xrcv.category_id=xrca.category_id
)
LOV
Report Name starts with
lower(xrv.report_name) like lower(:report_name)||'%'
Char
Missing security
x.ledger_parameter is not null and x.gl_required is null and x.gl_restricted is null or
x.operating_unit_parameter is not null and x.ou_required is null and x.ou_restricted is null or
x.inv_org_parameter is not null and x.inv_required is null and x.inv_restricted is null
LOV