Blitz Report Assignment Validation

Description

Categories
Enginatics
Repository
GitHub
Lists every Blitz Report with the number of assignments it has at each assignment level, so you can validate that all reports are assigned.

Each level column counts the distinct included assignment targets at that level (Site, Application, Request Group, Responsibility, User, Form, Function, Report). The Excluded column counts exclusion assignments. The zero_to_null function leaves a blank cell where there is no assignment, so reports missing an assignment stand out at a glance - a row that is completely blank across the assignment columns is not assigned to anyone.

Use the Category, Not in Category and Type parameters to limit the scope. Set 'Not Assigned Only' to Yes to list only reports that have no include assignment at any level, or pick a level in 'Not Assigned at Level' to find reports missing an assignment at one specific level.

SQL 49 lines

Parameters 6

ParameterTypeList of values SQL
CategoryLOV
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
)
Not in CategoryLOV
xrv.report_id not in (
select
xrca.report_id
from
xxen_report_category_assigns xrca,
xxen_report_categories_v xrcv
where
xrcv.category=:not_in_category and
xrcv.category_id=xrca.category_id
)
TypeLOV
xrv.type_dsp=:type
DisabledLOV Oracle
xrv.disabled='Y'
Not Assigned OnlyLOV
xrv.report_id not in (
select
xra.report_id
from
xxen_report_assignments xra
where
xra.include_exclude='I'
)
Not Assigned at LevelLOV
xrv.report_id not in (
select
xra.report_id
from
xxen_report_assignments xra
where
xra.include_exclude='I' and
xra.assignment_level=:missing_level
)