Tech Trainings

Oracle Fusion User Details Based on Role Query

July 5, 2024 by admin

SELECT 
pu.username , ROLE.role_name , ppnf.first_name, ppnf.last_name
FROM 
  fusion.fun_user_role_data_asgnmnts ROLE, 
  fusion.per_users pu ,
  per_all_people_f    papf,
  per_person_names_f  ppnf
WHERE 
  1=1
  AND pu.user_guid = ROLE.user_guid 
  and ROLE.role_name IN (
    'ORA_AP_ACCOUNTS_PAYABLE_MANAGER_JOB', 
    'ORA_AP_ACCOUNTS_PAYABLE_SUPERVISOR_JOB'
  ) 
  and role.active_flag = 'Y'	
  AND SYSDATE BETWEEN NVL(papf.effective_start_date, SYSDATE) AND NVL(papf.effective_end_date, SYSDATE)
      and    ppnf.person_id(+) = papf.person_id
    and    ppnf.name_type(+) = 'GLOBAL'
    and    SYSDATE BETWEEN NVL(ppnf.effective_start_date(+), SYSDATE) AND NVL(ppnf.effective_end_date(+), SYSDATE)
  and pu.username ='FAS16.STUDENT'

Comments |0|