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