First question: Why on earth do we need to secure the report in such a way that Data Access On Specific Entities is limited. Well, As it happened to us, we developed many reports and delivered them to the client. but one fine day client asked us to hide the data of inventory from the admin of other inventories i.e. admin of the inventory org must be able to run the report for only his inventory. Or For few reports data access to the user had to be restricted based on the Business Unit i.e. user must be able to run the report for the business unit on which s/he has access upon.
In short, if you have the access on a particular entity such as Business Unit or Inventory or the legal entity or the subinventory, you must be able to run the report for only that entity. To achieve the data access on specific entities, this is what we did:
1. Identified the role which determined the access on the inventory in our case. (ORA_INV_WAREHOUSE_MANAGER_JOB).
2. Created an LoV for Inventory Org parameter using the query below:
select iop.organization_code
from fun_user_role_data_asgnmnts asgn,
inv_org_parameters iop,
per_users usr
where asgn.role_name = ‘ORA_INV_WAREHOUSE_MANAGER_JOB’
and asgn.user_guid = usr.user_guid
and iop.organization_id = asgn.inv_organization_id
AND UPPER(usr.username) = UPPER(:xdo_user_name)
AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE+1)
3. Made the parameter mandatory.
this ensured that whenever user tried to run the report, only the inventory on which s/he has access upon, is visible in the LoV. Similar query can be created for Business Unit Access too. You can try to change the role based on your requirement. cheers…
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Related Posts
Report Security
OTBI vs BI Reporting in Oracle Fusion
OTBI Report Creation in Oracle Fusion
RTF- Looping Finite Number Of Times
RTF Date Formatting With Example
RTF Number Formatting With Examples
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com