Query to find User and manager name -Oracle Fusion
SELECT pu.username "LOGIN_USERNAME",
pu.SUSPENDED,
papf.person_number,
to_char(pu.creation_date, 'DD-MON-YYYY') "LOGIN_CREATION_DATE",
hp.person_first_name "FIRST_NAME", hp.person_last_name "LAST_NAME",
hp.email_address,
gcc.segment2 employee_cost_center,
pu1.username "MGR_USERNAME",
hp1.person_first_name "MGR_FIRST_NAME", hp1.person_last_name "MGR_LAST_NAME",
papf1.person_number manager_person_number
FROM per_users pu,
hz_parties hp,
per_all_assignments_m paa,
PER_ASSIGNMENT_SUPERVISORS_F pas,
per_users pu1,
hz_parties hp1,
per_all_assignments_m paa1,
gl_code_combinations GCC,
per_all_people_f papf,
per_all_people_f papf1
WHERE pu.username not like 'FUSION_APPS_%'
and pu.username not in ('weblogic_idm', 'oamAdminUser', 'IDROUser')
and pu.user_guid = hp.user_guid
and pu.person_id = paa.person_id (+)
and paa.assignment_id = pas.assignment_id (+)
and pas.manager_assignment_id = paa1.assignment_id (+)
and paa1.person_id = pu1.person_id (+)
and pu1.user_guid = hp1.user_guid (+)
and (paa.effective_end_date > sysdate OR paa.effective_end_date IS NULL)
and ( paa1.effective_end_date > sysdate OR paa1.effective_end_date IS NULL)
and paa.DEFAULT_CODE_COMB_ID = gcc.code_combination_id (+)
and (paa.PRIMARY_ASSIGNMENT_FLAG = 'Y' or paa.PRIMARY_ASSIGNMENT_FLAG IS NULL)
and pu.person_id = papf.person_id (+)
and pu1.person_id = papf1.person_id (+)
ORDER BY pu.username