Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Sachin Mistry
    user role security context security context value report
    Topic posted August 31, 2018 by Sachin MistryGold Medal: 3,500+ Points, tagged BI Publisher, Fusion Financial reporting, Fusion Procurement reporting, Fusion Project reporting, Fusion Supply Chain Management reporting, OBIEE Answers, OTBI, Reports 
    628 Views, 1 Comment
    Title:
    user role security context security context value report
    Summary:
    user role security context security context value report
    Content:

    Is there a report where we can retrieve all values as shown in screen shot below?

    Image:

    Comment

     

    • Manjunath Rallabandi

      Hello Sachin,

      I had a similar requirement and had reached out to Oracle. I got the following SQL from Oracle engineer and with minor modifications(like fetching select columns rather than * from FUN_USER_ROLE_DATA_ASGNMNTS), I was able to create a data model and a report.  Having said that, what I understand is that this works only for FSCM and not HCM.  HCM roles, security model and underlying tables are different so you may have to get another sql from Oracle and have 2 reports for user access audit. 

      There are no canned reports available.  

      SELECT /*3-18101151861*/ a.USERNAME, 
      c.ROLE_COMMON_NAME, 
      c.ROLE_DISTINGUISHED_NAME, 
      d.* 
      FROM fusion.PER_USERS a, 
      fusion.PER_USER_ROLES b, 
      fusion.PER_ROLES_DN_VL c, 
      fusion.FUN_USER_ROLE_DATA_ASGNMNTS d 
      WHERE a.USER_ID = b.USER_ID 
      AND b.ROLE_ID = c.ROLE_ID 
      and d.USER_GUID=a.user_guid 
      and d.ROLE_NAME=c.ROLE_COMMON_NAME 

      Hope this helps. 

      Regards,

      Manjunath