Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Wendy Ware
    Revoke Roles in Security Console R12 Still Showing Assigned...Answered
    Topic posted September 27, 2017 by Wendy WareBronze Crown: 15,000+ Points, tagged BI Publisher, Fusion Financial reporting, OTBI 
    66 Views, 3 Comments
    Title:
    Revoke Roles in Security Console R12 Still Showing Assigned in BI Report
    Summary:
    Since R12 upgrade revoking roles in Security Console, the revoked users' roles still appear to be assigned in our existing custom BI report (SQL below)
    Content:

    In R12 we have been revoking users' roles in security console which seems fine but I suspect we need to change the source for our pre-existing custom user roles report.  In the table "fusion.per_user_roles" these roles are still assigned.  SQL below.  Can anyone guide me as to the solution to this?  Thanks, Wendy

    SELECT
    pu.person_id,
    ppn.display_name,
    pu.username,
    pu.suspended,
    prdv.role_name,
    prdv.description,
    prdv.abstract_role,
    prdv.job_role,
    prdv.data_role
    FROM
    fusion.per_users pu,
    fusion.per_user_roles pur,
    fusion.per_roles_dn_vl prdv,
    fusion.per_person_names_f ppn
    WHERE
    pu.user_id = pur.user_id(+)
    AND prdv.role_guid(+) = pur.role_guid
    AND pu.person_id is not null
    and ppn.name_type = 'GLOBAL'
    AND ppn.person_id = pu.person_id
    and (sysdate between ppn.effective_start_date and ppn.effective_end_date)
    and pu.active_flag = 'Y'
    and (pu.suspended is NULL or pu.suspended ='N')
    ORDER BY 3

     

    Best Comment

    Alexey Shtrakhov

    Please check following flag on per_user_roles 
    per_user_roles.ACTIVE_FLAG = 'Y'

    Alexey

    Comment

     

    • Alexey Shtrakhov

      Please check following flag on per_user_roles 
      per_user_roles.ACTIVE_FLAG = 'Y'

      Alexey

    • Alexey Shtrakhov

      This is a very good thing as you can make the report to show history like data for revoked roles. We did it with parameter like this:

      AND PUR.ACTIVE_FLAG = DECODE(NVL(:P_ACTIVE_ONLY, 'Y'),'X',PUR.ACTIVE_FLAG,NVL(:P_ACTIVE_ONLY, 'Y'))

      and assigning a list for a parameter :P_ACTIVE_ONLY

      Y - Only Active
      N - Only Inactive
      X - All roles

      This gives the flexibility for the user to check on revoked roles.

       

      Alexey

       

    • Wendy Ware

      Alexey, perfect answer...thanks!  Wendy