Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Marisa Escobar
    Report that List All Users and Assigned RolesAnswered
    Topic posted July 1, 2016 by Marisa EscobarRed Ribbon: 250+ Points, last edited January 30, 2019, tagged Reports, Security 
    5158 Views, 34 Comments
    Title:
    Report that List All Users and Assigned Roles
    Summary:
    Looking for a report that provides a list of all users and their assigned roles.
    Content:

    Hi.  Does anyone know of a report that list all users and their assigned roles?  

    Thanks.

    -Marisa

    Best Comment

    Alexey Shtrakhov

    Hi Marisa,

    Sorry for delay. Vacation period.... :)
    But here I made for you fast document on how to create your own report. Please see attachment: Custom BIP Report User Roles.docx. My query is included in the document. If you like Arun's query more, then you can do the same but using Arun's query. Im not sure if Arun had some parameters there.

    If you have any questions, please ask.

     

    Have a Nice Summer Time!

    With respect
    Alexey 

    Comment

     

    • Alexey Shtrakhov

      Hi Marisa,

      Im not aware of any standard report for this, maybe some other person can reply on this.  But I did normally a customized BIP report. It is quite simple and straightforward. You could use user / user name / role as a parameter.

      If you want I could provide you a query and steps for creating this kind of report. (this would be for Fusion ERP)

      With respect
      Alexey

    • Wendy Ware

      Hi Marisa,

      We have a custom BIP report, too, that we run monthly for audit purposes.  I can share the SQL for our report if you would like.  Let me know.

      Thanks, Wendy

    • Marisa Escobar

      Thank you for the replies, Alexey and Wendy!

      Alexey, if you could share the steps for your BIP report, that would be great.  We are using Fusion ERP.

      Thank you.

      -Marisa

    • Alexey Shtrakhov

      Hi Marisa,

      Sorry for delay. Vacation period.... :)
      But here I made for you fast document on how to create your own report. Please see attachment: Custom BIP Report User Roles.docx. My query is included in the document. If you like Arun's query more, then you can do the same but using Arun's query. Im not sure if Arun had some parameters there.

      If you have any questions, please ask.

       

      Have a Nice Summer Time!

      With respect
      Alexey 

    • Jerry Ursetti

      I am very new to Financials Cloud. Where are you creating the custom BIP report? I have Oracle Cloud Application Revision 13.18.05 (11.13.18.05.0) and Navigator > Tools > Reports and Analytics path does not exist.

      Thanks.

      Jerry

      • Jennifer Bruce

        Hi Jerry,

         

        Welcome to the Cloud!  If I recall from our R13 upgrade, the Reports and Analytics path didn't necessarily appear after the upgrade on the menu options - what we had to do was create a sandbox (click on your initials in the top right of the homepage, and there will be a 'manage sandboxes' option there), and set that as active. 

        Once in the sandbox, Navigator > configuration > structure - this brings up all of the options available for displaying on both the navigator and springboard/homepage.  Find 'tools' in the menu and click on the menu 'carrot' to the left of it - this will expand the menu to see the sub-items.  Click on 'Reports and Analytics' - you can choose how and if you want this option to be displayed - we have set 'show on navigator' to 'yes' in our environment to ensure all users can see it.

        Save and close once you have made the changes to the display option and then click on the navigator icon - hopefully you should now see 'reports and analytics' showing under 'Tools'.  Assuming it does, you just then need to publish your sandbox to ensure that the changes are rolled out into the mainline (click on the yellow bar at the top of the screen, then 'more',  and 'publish').

        Good luck with your report, if there's anything else, we're all here to help each other smiley

        Jenny

         

        • Jennifer Bruce

          BTW, you need to make sure you have the BI author role to write a report and possibly IT security manager to make changes to the navigator and springboard - I'm sure my learned colleagues will point out if I'm wrong there!

        • Jennifer Bruce

          BTW, you need to make sure you have the BI author role to write a report and possibly IT security manager to make changes to the navigator and springboard - I'm sure my learned colleagues will point out if I'm wrong there!

        • Jerry Ursetti

          When I click on Manage Sandboxes, a window opens with 'No data to display'.There isn't anything to set active. I've attached a screen shot.

          Thanks.

          Jerry

          • Jennifer Bruce

            Hi Jerry

            Don't panic - one of the joys of Fusion is that 2 or 3 releases ago, the powers that be decided to make all active icons on a page grey, as well as the inactive icons (you will get used to the quirks!) - if you click on the grey plus sign on the 'manage sandboxes' screen, this will allow you to create a sandbox.  Once created, it will display on that screen as a row - you can then click on the row to highlight it, and then click 'set as active'

            Please feel free to come back if you are still struggling

            Jenny

            • Jerry Ursetti

              Jenny,

              OK. I created a sandbox and added Reports and Analytics to Tools . I published the sandbox. I then tried to add the BI Author role to my account. When I go to Tools > Security Console > Roles. I can search for, and find, the BI roles - Bi Administrator & BI Author. But when I edit my account and try to add the BI role, I don't get any BI roles when I search. I think maybe that's because I have the ORA_ASM_APPLICATION_IMPLEMENTATION_CONSULTANT_JOB role, which includes the BI Administrator role, which includes the BI Author role. So now I will try to create the report.

              Now I will try to create the BIP report.

              And I will be spending some time with the security docs...

              Thanks for your help.

              Jerry

            • Wendy Ware

              Yes, making active icons only a slightly darker grey than inactive ones was considered an "enhancement" at the time.  Not sure why.  Dark grey is The New Black?

              • Jennifer Bruce

                Orange would have been more helpful!  I've wasted an inordinate number of hours cursing icons that look inactive, only to realise that its an ever so slightly different shade of grey and is an active one.  Hey ho, we've just had to learn to live with the 'feature'

    • Jerry Ursetti

      I added Reports and Analytics to the Tools menu. I followed the steps in Custom_BIP_Report_User_Roles.docx. I'm not getting any data. Every column says 'No data found /DATA_DS/G_!/[column name].(See attached screen shot.)

      I get the same results in both the prod and test pods.

      Any idea why I'm not getting any data? There aren't many users but there are more than 0.

      Thanks.

      Jerry

      • Jennifer Bruce
        Hiya Jerry Is this whilst you are building the report (so arranging your columns on the screen), or once you have run it? When you created the data model, did you click on the 'data' tab (Sorry, doing this from memory!) to create sample data? Once the sample data is generated, you need to click 'save as sample data' and then save the data model itself. Keep reminding yourself that it's all a good learning experience! Jenny
      • Jennifer Bruce

        I've given in and dug my laptop out for this - doing it on my mobile phone wasn't working!

        In that screenshot, you are still building the report - that message usually means there is no sample data being brough back by the data model to populate the report preview with.  Usually this is nothing more technical than not having saved the DM with any sample data in it, but sometimes it can be because you have tried to report something that Fusion simply can't make head or tail of.  As this is a known report, its probably the first option - I'll do you a couple of screenshots for saving sample data.

        Theoretically, if you now run the report, it should work correctly

        J

      • Jennifer Bruce

        Screenshots of saving sample data attached - not of this report I hasten to add!

        • Jerry Ursetti

          When I created the data model, I followed the steps for sample data but I don't get anything. I've attached a screen shot of what I get after clicking View.

          Jerry

          • Jennifer Bruce

            Does it do anything if you click 'save as sample data'?

            If you are using Alexey's SQL, then one option is to remove the filters code so the DM returns everything.  Its potentially a sledgehammer to crack a nut, but it will rull out anything being amiss with your filters.  To do this, just delete the section:

             

            AND   UPPER(rv.ROLE_COMMON_NAME) like NVL(UPPER(:P_ROLE_NAME), '%')

              AND   UPPER(peru.username) like NVL(UPPER(:P_USERNAME), '%')

              AND   UPPER(pern.FULL_NAME) like NVL(UPPER(:P_FULLNAME), '%')

            Then save the DM and try to view the sample data again.  I can't remember if there are any regular processes that need to run through scheduled processes to populate these tables - I''ll do some digging for you

            Jenny

            • Jerry Ursetti

              I cannot get any data. I removed the 3 rows and still got nothing.

              I changed the query to just list the USERNAME in FUSION.PER_USERS. I got what looks like system users, but no actual person users:

              FAAdmin

              FUSION_APPS_AMX_APPID

              FUSION_APPS_ATK_ADF_APPID

              FUSION_APPS_ATK_UMS_APPID

              FUSION_APPS_BI_APPID

              I checked my provisioning and added the Human Capital Management Application Administrator role. That didn't have any effect. I logged off and back on, just in case that was necessary. It didn't help. 

              I tried some of the reports on the HR Dashboard. The User Details System Extract dumps this unformatted data:

              ALL 1980-01-01T00:00:00.000+00:00 ALL   FUSION_APPS_BI_APPID 100000000257001 Y 2017-05-30T18:52:16.000+00:00 anonymous   FUSION_APPS_CRM_SES_CRAWL_APPID 100000000257003 Y 2017-05-30T19:04:13.000+00:00 anonymous   FUSION_APPS_HCM_SOA_APPID 100000000336006 Y 2017-11-10T02:23:14.000+00:00 anonymous   afra44 300000001580042 Y 2017-11-14T00:00:00.000+00:00 ab72317 FUSION_APPS_SEARCH_APPID 100000000253003 Y 2017-02-06T19:27:11.000+00:00 anonymous   FUSION_APPS_AMX_APPID 100000000339004 Y 2017-11-14T22:30:19.000+00:00 anonymous FUSION_APPS_PROV_PATCH_APPID 100000000253001 Y 2017-02-06T19:13:47.000+00:00 anonymous   FUSION_APPS_HCM_ADF_LDAP_APPID 100000000540051 Y 2019-01-05T08:11:27.000+00:00 anonymous FUSION_APPS_HCM_SES_CRAWL_APPID 100000000257005 Y 2017-05-30T19:05:43.000+00:00 anonymous   FUSION_APPS_FSCM_SES_CRAWL_APPID 100000000258001 Y 2017-05-30T19:08:55.000+00:00 anonymous FUSION_APPS_HCM_ADF_APPID 100000000253005 Y 2017-02-06T20:07:20.000+00:00 anonymous   FUSION_APPS_CRM_ESS_APPID 100000000254001 Y 2017-02-06T23:30:19.000+00:00 anonymous OCLOUD9_osn_APPID 100000000260001 Y 2017-05-31T00:24:17.000+00:00 anonymous   fa_guest 100000000322007 Y 2017-10-20T06:30:24.000+00:00 anonymous   FAAdmin 100000000336004 Y 2017-11-10T02:18:24.000+00:00 anonymous FUSION_APPS_ATK_UMS_APPID 100000000339006 Y 2017-11-14T22:31:11.000+00:00 anonymous   axis11 300000001580016 Y 2017-11-13T00:00:00.000+00:00 Jerry.Ursetti@anthem.com   em_monitoring 100000000259001 Y 2017-05-30T21:47:14.000+00:00 anonymous   FUSION_APPS_PRC_SOA_APPID 100000000336008 Y 2017-11-10T02:23:58.000+00:00 anonymous   FUSION_APPS_PRJ_SOA_APPID 100000000336010 Y 2017-11-10T02:24:00.000+00:00 anonymous FUSION_APPS_ATK_ADF_APPID 100000000519603 Y 2019-01-03T20:35:38.000+00:00 OCLOUD9_osn_APPID   FUSION_APPS_CRM_SOA_APPID 100000000279003 Y 2017-06-17T16:03:05.000+00:00 anonymous ab72317 300000001580029 Y 2017-11-13T00:00:00.000+00:00 axis11   FUSION_APPS_CRM_ODI_ESS_APPID 100000000505024 Y 2018-10-06T11:26:47.000+00:00 anonymous   anonymous 100000000395018 Y 2018-02-27T21:01:02.000+00:00 anonymous   FUSION_APPS_HCM_ESS_APPID 100000000541051 Y 2019-01-05T08:11:27.000+00:00 anonymous   afra07 300000001580055 Y 2017-11-14T00:00:00.000+00:00 ab72317

              I can see the 4 actual users in this, but not all of their information. I only see my name and email, not the other users' names and emails. Why isn't the output formatted?

              The User Information report just gave me this:

              DATA_DS
              

              I also noticed that changes to data models are automatically saved. When I closed the tab without saving, the last change was present when I logged back in.

              This is turning into quite a frustrating exercise. :/

              Thanks a lot for trying to help me with this.

              Jerry

          • Jennifer Bruce

            Do you have any HCM job roles?  I've found the below reference in connection with the User Details System Extract report:

            "The Oracle BI Publisher User Details System Extract Report includes details of selected Oracle Fusion Applications user accounts. To run this report, you must have a data role providing view-all access to person records for the Human Capital Management Application Administrator job role"

            https://docs.oracle.com/en/cloud/saas/applications-common/18b/faser/creating-and-managing-application-users.html#FASER1251654

            If you don't have the HCM access, that might be why you can't see any data

            J

    • Jennifer Bruce
      I Think I'm staying to run out of ideas! Are you about to tell through you scheduled processes screen whether the processes described in: https://docs.oracle.com/en/cloud/saas/sales/r13-update17d/fasoc/synchronizing-with-oracle-identity-management.html have been run? If they haven't run recently, it's possible that the tables aren't up to date J
      • Jerry Ursetti

        I thought about that. No synch jobs have run. There isn't even a security synch job process to schedule. Plus we don't have the LDAP connection configured. I have a task to work with IT to configure SSO. I'm not sure if that will also include LDAP configuration; it probably won't. That wasn't needed with EPM Cloud SSO but the EPM Cloud technology is very different from Fusion.

        Thank you very much for trying to help.

        Jerry

    • Jennifer Bruce
      It's frustrating not to be able to solve! Given that the sql works in other people's environments, and it looks like you've got the right roles, I think it will be a synchronisation thing - hopefully when sso is configured everything will work out. Let us know how you get on J
    • John Allegro

      This report worked great for me except for the fact that I am missing some users.  We have implementation consultants that are not being reported by this report.  These users have a different email suffix than our actual employees which is linked to their usernames (we use SSO).  Vendors cannot use SSO for obvious reasons and must log in manually.  Other than this difference, I am not sure why they are not collected into the report.

      Any idea what I may be missing?

       

      John

      • Alexey Shtrakhov

        I suspect your consultants are not people :)  (not created as a person)
        Let me check the report and let you know.

        Alexey

      • Alexey Shtrakhov

        Hi John,

        tested with contingent user.
        please try to use attached DM.

        Changed this line:
        before: AND     PERPERSONNAMEFV.PERSON_ID  = AssignmentDPEO.PERSON_ID
        now: AND     PERPERSONNAMEFV.PERSON_ID (+)  = AssignmentDPEO.PERSON_ID

        Also changed this to be able to test on my side with the recently created user (you may skip this change):
        before: AND SYSDATE BETWEEN PU.START_DATE AND  NVL(PU.end_date, SYSDATE)
        now: AND SYSDATE BETWEEN PU.START_DATE-1 AND  NVL(PU.end_date, SYSDATE+1)

        Alexey

    • Ashish Mangal

      Hi Alexy,

      Can we customize a report like if user logged in instance and run a particular report his/her name comes in header. is it possible?

      Regards,

      Ashish

      • Alexey Shtrakhov

        Hi Ashish,

        in BIP report you can add at least user name from session variables:
        Something like this. select :xdo_user_name USER_ID from dual

        You can add this on your report as a hidden parameter and then use it on you report to print it on the report header.

        Refer this doc: https://docs.oracle.com/middleware/12212/bip/BIPDM/GUID-2A6E4B9B-ABCD-4CF0-A458-59B89F2443A1.htm#BIPDM221

        With respect
        Alexey

    • Jeannine Giovagnoli

      Hi , I added the DM and Report per Word Document instructions (thanks-very helpful) in Alexey's post.  However it gives me all users, not just 'Active' users.  How can I modify the sql to include on Active users and their assigned roles and the date the role was assigned (and by whom if possible)?  I have modified the sql to exclude Employee and Line Manager roles, since those are auto-provisioned.  We need this for security audit.

      Any help is appreciated.

      Thanks Much!