Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Rasu P
    Fusion User Roles&Responsibilites Add/RemoveAnswered
    Topic posted June 6, 2019 by Rasu PRed Ribbon: 250+ Points 
    118 Views, 7 Comments
    Title:
    Fusion User Roles&Responsibilites Add/Remove
    Summary:
    Custom BI Report to capture the user roles&reponsibilites
    Content:

    Guys,

    Can someone help with below requirement,

    We have to capture what are all the roles added to the user and what are all the roles removed for particular user. Do you have any idea in which table these information stored.

    I can able to get the roles added information from PER_ROLES_DN_VL. Couldn't able to find information related to the roles removed for particular user.

    Thanks,

    Rasu

    Best Comment

    Vishnu Singireddy

    Hi Rasu,

    You wont find the end date in any table. you just can enable the auditing and run the standard report.

    Navigator --> Setup and Maintenance --> Scheduled Processes --> Search for "User and Role Access Audit Report" --> Select Options "Report Type" (All roles, Multiple users, Role name, User name)

    Just keep in mind that this will fetch all audit reports for the changes only after the Audit Policies are enabled. So if you followed the KM Doc ( Doc ID 2175861.1 ) now then it will fetch the Audit Reports only after enabling the Audit policies.

    Thanks,

    Vishnu.

    Comment

     

    • Karthikeyan Sukumar

      Hi Rasu- Yes you can get the Roles from that table.But there is system will not store any roles removed 

      System will store the history of the Roles used for the users

       

      Thanks!

      Karthik

    • Sudhakara Rao Kovuru

      Hi Rasu,

      Please try below query and let us know.

      SELECT       pu.username username
      ,ppn.first_name
      ,ppn.last_name  
      ,prdv.role_name
      ,prdv.role_common_name code
      ,pur.start_date  role_start_date
      ,pur.end_date   role_end_date        
      FROM per_users pu
      ,per_user_roles pur
      ,per_person_names_f ppn
      ,per_roles_dn_vl prdv
      WHERE pu.user_id = pur.user_id
      AND pu.person_id = ppn.person_id
      AND pur.role_id = prdv.role_id
      AND (pu.end_date IS NULL OR pu.end_date > SYSDATE )
      AND pu.suspended = 'N'
      AND pur.active_flag = 'Y'
      AND ppn.name_type = 'GLOBAL'
          AND TRUNC(SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date 
      AND (:P_Username IS NULL OR (upper(pu.username) like  '%'|| upper(:P_Username)||'%'))
       
      Thanks,
      -Sudha.
      • Rasu P

        Thanks Sudha,

        But i don't see end_date gets populated in per_user_roles table after removing particular role.

        Thanks,

        Rasu

    • Karthikeyan Sukumar

      As said before you can't get those detailed history.You can raise the SR and get the confirmation

       

      Thanks!

      Karthik

    • Sudhakara Rao Kovuru

      Thanks Karthik for the clarification.

      Hi Rasu,

      As Karthik already commented so please proceed to create SR with Oracle support and get the confirmation.

      If you feel like this issue has been resolved so please resolve it.

      Thanks,

      -Sudha

    • Vishnu Singireddy

      Hi Rasu,

      You wont find the end date in any table. you just can enable the auditing and run the standard report.

      Navigator --> Setup and Maintenance --> Scheduled Processes --> Search for "User and Role Access Audit Report" --> Select Options "Report Type" (All roles, Multiple users, Role name, User name)

      Just keep in mind that this will fetch all audit reports for the changes only after the Audit Policies are enabled. So if you followed the KM Doc ( Doc ID 2175861.1 ) now then it will fetch the Audit Reports only after enabling the Audit policies.

      Thanks,

      Vishnu.