OracleAppsDNA

SQL Query of Manage Data Access along with Security Context

select *
from
         (
                select
                       gl.name
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts role
                     , fusion.gl_access_sets              gl
                     , fusion.per_users                   pu
                where
                       gl.access_set_id = role.access_set_id
                       and pu.user_guid = role.user_guid
                union
                select
                       bu.bu_name
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_all_business_units_v    bu
                     , fusion.fun_user_role_data_asgnmnts role
                     , fusion.per_users                   pu
                where
                       role.org_id      = bu.bu_id
                       and pu.user_guid = role.user_guid
                union
                select
                       led.name
                     , role.role_name
                     , pu.username
                from
                       fusion.gl_ledgers                  led
                     , fusion.fun_user_role_data_asgnmnts role
                     , fusion.per_users                   pu
                where
                       role.ledger_id   = led.ledger_id
                       and pu.user_guid = role.user_guid
                union
                select
                       book.book_type_name
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts role
                     , fusion.fa_book_controls            book
                     , fusion.per_users                   pu
                where
                       book.book_control_id = role.book_id
                       and pu.user_guid     = role.user_guid
                union
                select
                       interco.interco_org_name
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts role
                     , fusion.fun_interco_organizations   interco
                     , fusion.per_users                   pu
                where
                       interco.interco_org_id= role.interco_org_id
                       and pu.user_guid      = role.user_guid
                union
                select
                       cost.cost_org_name
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts role
                     , fusion.cst_cost_orgs_v             cost
                     , fusion.per_users                   pu
                where
                       cost.cost_org_id = role.cst_organization_id
                       and pu.user_guid = role.user_guid
                union
                select
                       mfg.def_supply_subinv
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts role
                     , fusion.rcs_mfg_parameters          mfg
                     , fusion.per_users                   pu
                where
                       mfg.organization_id= role.mfg_organization_id
                       and pu.user_guid   = role.user_guid
                union
                select
                       budget.name
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts role
                     , fusion.xcc_control_budgets         budget
                     , fusion.per_users                   pu
                where
                       budget.control_budget_id = role.control_budget_id
                       and pu.user_guid         = role.user_guid
                union
                select
                       st.set_name
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts role
                     , fusion.fnd_setid_sets_vl           st
                     , fusion.per_users                   pu
                where
                       st.set_id        = role.set_id
                       and pu.user_guid = role.user_guid
                union
                select
                       inv.organization_code
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts role
                     , fusion.inv_org_parameters          inv
                     , fusion.per_users                   pu
                where
                       inv.organization_id = role.inv_organization_id
                       and pu.user_guid    = role.user_guid
                union
                select
                       hr.classification_code
                     , role.role_name
                     , pu.username
                from
                       fusion.fun_user_role_data_asgnmnts   role
                     , fusion.hr_org_unit_classifications_f hr
                     , fusion.per_users                     pu
                where
                       hr.org_unit_classification_id = role.org_id
                       and pu.user_guid              = role.user_guid
         )
where
         1 = 1
         and username in ('shailender@OracleAppsDNA.com'
                        ,'rahul@OracleAppsDNA.com')
         and role_name like 'ORA_AR_ACCOUNTS_RECEIVABLE%'
         and name      like 'Enterp%'
order by
         username, role_name
Exit mobile version