Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

I want to prepare an OTBI report to capture roles and data access information

Received Response
140
Views
3
Comments
Mayur Rathod
Mayur Rathod Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

Dear Members,

I want to prepare an OTBI report containing the below details:

  • Username
  • Email
  • Job Title
  • Company
  • Manager Name
  • Role Name
  • Role Code
  • Security Context
  • Security Context Value

Please suggest the relevant subject area(s) to achieve the same.

Answers

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited Jul 21, 2023 7:52AM

    Have you tried the subject area for roles and privileges?

    https://docs.oracle.com/en/cloud/saas/human-resources/22d/faohb/Security--Roles-and-Privileges-Real-Time-SA-87.html#Security__Roles_and_Privileges_Real_Time_SA_87_d1e13297

    for example, to get a list of users and their roles

    #/analytics/saw.dll?issuerawsql
select all 0 s_0
, "User"."User Name" as user_name
, "User"."Person Number" as person_number
, "User"."Person ID" as person_id
, "User"."Employee Display Name" as person_display_name
, "User"."Employee Full Name" as person_full_name
, "Role"."Role Display Name" as role_name
, "Role"."Role Name" as role_code
, "User"."User Suspended" as user_suspended
, "Role Facts"."Number of Roles" 
, "Role Facts"."Number of Users" 
from "Security - Roles and Privileges Real Time"
fetch first 7 rows only


  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited Jul 21, 2023 8:03AM

    BTW A user may or may not be linked to a worker. If the user is linked to a worker then "Job Title", "Company", "Manager Name" [and possibly "Email" (if this is one of the many home or work etc contact emails of the worker rather than the one user email of the user)] are from the employment information person assignment(s) of the worker linked to a user. A person may have multiple assignments at the same point in time. So you will need to join to a HCM subject area workforce management assignment to get this then if the person has multiple assignment ids at the same point in time then add a where clause to pick which of the multple assignments you wish to use like get only the current latest sequence instance effective date today between effective start and end date which is primay and active etc.

  • Mayur Rathod
    Mayur Rathod Rank 1 - Community Starter

    Thank you for the information. Shall check the solution and get back.