Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    absalvi
    MOAC in BIP ReportsAnswered
    Topic posted July 11, 2019 by absalvi Red Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, Fusion Supply Chain Management reporting, OTBI, Reports, Sample Reports 
    141 Views, 8 Comments
    Title:
    MOAC in BIP Reports
    Summary:
    Implementing MOAC in BIP Reports
    Content:

    Hello,Trying to implement security based on the role assigned to a user. Following below note, however, have not been able to implement the same.

    Fusion Applications BI Publisher : Implementing FINS Security in SQL-based BI Publisher reports using Multi-Organization Access Control (MOAC) (Doc ID 1984271.1)

    We have used the synonym ap_invoices instead of ap_invoices_all, however, the data is not restricted. Still all rows are returned.

    Are there any pre-requisites to this? or the way in which a user is setup?

     

    -- Abhishek

    Best Comment

    Milind Dalvi

    unfortunately, we cannot share our setups.

    Steps: (assuming you are reporting AR)

    1. make sure your user is having suitable AR role (I would suggest to create new user which has only this role)

    2. Go to manage data access for user task and assign proper business unit which you want to report on 

    3. Follow the document which you have attached in original topic.

    Key part here is data access for user and making sure we are using views (e.g. RA_CUSTOMER_TRX)

     

    Comment

     

    • Manish Pesswani
      Facing the same issue. Better you write sql checking role assignment & secure data based on that.
    • Madhu Babu Vitta

      Hi Abhishek,

      Hi ,
      See if below query is useful for you .. If you want to build it based on 'Manage Data Access for Users' (or Business Unit)..
       
      SELECT Users.username ,asg.role_name,
      (SELECT Bu.BU_NAME 
      FROM fusion.FUN_ALL_BUSINESS_UNITS_V Bu 
      WHERE Bu.bu_id = Asg.org_id 
      AND SecMap.OBJECT_NAME = 'FUN_ALL_BUSINESS_UNITS_V' 
      AND ROWNUM =1 
      ) AS BU_NAME, 
      (SELECT PK1_COLUMN_NAME 
      FROM fusion.FND_OBJECTS Obj 
      WHERE obj.obj_name = secmap.object_name 
      ) AS SECURITY_CONTEXT 
      FROM FUN_USER_ROLE_DATA_ASGNMNTS asg,
      fusion.PER_USERS Users,
      fusion.FUN_ROLE_DATA_SECURITY_MAPPING SecMap
      where Users.USER_GUID = Asg.USER_GUID 
      AND Asg.ROLE_NAME = SecMap.ROLE_NAME 
      AND UPPER(Users.username) = :p_user
      order by asg.creation_date desc,asg.last_update_date desc
       
      Thanks,
      Madhu
    • Madhu Babu Vitta

      2) If you want to access the report's output and show only User's Cost center. Then try below

      SELECT
      NVL((SELECT DISTINCT gcc.segment3 
      FROM   per_all_assignments_m paam,
         per_periods_of_service ppos,
         gl_code_combinations gcc,
         per_users usrnm
      WHERE  usrnm.person_id = paam.person_id
      AND    ppos.person_id = paam.person_id
      AND    ppos.period_of_service_id = paam.period_of_service_id 
      AND    gcc.code_combination_id = paam.default_code_comb_id 
      AND    paam.primary_assignment_flag = 'Y'
      AND    paam.assignment_type IN ( 'E','C') 
      AND    paam.assignment_status_type ! = 'INACTIVE' 
      AND    TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
      AND    upper(usrnm.username) = upper(:xdo_user_name)
              --Exception - Roles/Users
              AND  NOT EXISTS (
                      SELECT 1  
                      FROM   per_user_roles pur, 
                                   per_roles_dn_vl prdv 
                      WHERE  pur.role_id = prdv.role_id 
                      AND    pur.user_id = usrnm.user_id 
                      AND    (role_name LIKE 'XX AP Inquiry%' OR
                  role_name LIKE 'XX PO Inquiry%' OR
          role_name LIKE 'XX PO Buyer%' 
         )
      ),'ALL') User_CC
      FROM DUAL
      Then pass this result to main query and use gl_code_combinations.segment3 
       
      Thanks,
      Madhu
    • Milind Dalvi

      This document works for us with data access and RBAC. Make sure you are not having some additional data access to global set.

      • absalvi

        Hello Milind,

         

        Could you please explain "Make sure you are not having some additional data access to global set." Also is it possible for you to share some screenshots of your setup?

        • Milind Dalvi

          unfortunately, we cannot share our setups.

          Steps: (assuming you are reporting AR)

          1. make sure your user is having suitable AR role (I would suggest to create new user which has only this role)

          2. Go to manage data access for user task and assign proper business unit which you want to report on 

          3. Follow the document which you have attached in original topic.

          Key part here is data access for user and making sure we are using views (e.g. RA_CUSTOMER_TRX)

           

          • absalvi

            Hi Milind,

            We could get this working. created a new user and then tested, probably there was some setup issue with the previous user .

            Thanks a lot!