SQL to get first name, last name, email of user in Oracle Fusion

Details on the table used:

PER_USERS  - Table for storing one record per fusion system user. Maps to a single LDAP user. Primary Key is USER_ID

PER_LDAP_USERS - Table for storing one record for each user to be processed in OIM when creating new users or maintaining user details. Primary Key is LDAP_USER_ID

PER_ALL_PEOPLE_F - This table will store core personal data. Data is stored with date effectivity and it can having multiple records for given person_id record. Values that are static over time will be duplicated to each new date effective instance of a row with the same person_id. Primary Key is PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE

PER_PERSON_NAMES_F - This table is to hold core (common) and legislative-specific name components for a person, and names represented in different character sets. The table is date effective to record changes over time. Primary Key is PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE

PER_EMAIL_ADDRESSES - This table stores email addresses of different types for a person. Primary Key is EMAIL_ADDRESS_ID

 Run the below query to get first name, last name, email of user in Oracle Fusion

SELECT 
	 pu.user_id
	,pu.user_guid
	,papf.person_id
	,papf.effective_start_date
	,papf.effective_end_date
	,papf.person_number
	,pu.username
	,NVL(
		ppnf.first_name,(
			SELECT 	ldu.first_name FROM PER_LDAP_USERS ldu 
			WHERE 	ldu.username 	= pu.username 
			AND 	ldu.user_guid 	= pu.user_guid 
			AND 	ldu.creation_date = (
				SELECT 	MAX(ldu1.creation_date) 
				FROM 	PER_LDAP_USERS ldu1 
				WHERE 	ldu1.username 	= ldu.username 
				AND 	ldu1.user_guid 	= ldu.user_guid
			)
		)
	) AS FIRST_NAME
	,NVL(
		ppnf.last_name,(
			SELECT 	ldu.last_name FROM PER_LDAP_USERS ldu 
			WHERE 	ldu.username 	= pu.username 
			AND 	ldu.user_guid 	= pu.user_guid 
			AND 	ldu.creation_date = (
				SELECT 	MAX(ldu1.creation_date) 
				FROM 	PER_LDAP_USERS ldu1 
				WHERE 	ldu1.username 	= ldu.username 
				AND 	ldu1.user_guid 	= ldu.user_guid
			)
		)
	) AS LAST_NAME
	,NVL(
		pea.email_address,(
			SELECT 	ldu.email FROM PER_LDAP_USERS ldu 
			WHERE 	ldu.username 	= pu.username 
			AND 	ldu.user_guid 	= pu.user_guid 
			AND 	ldu.creation_date = (
				SELECT 	MAX(ldu1.creation_date) 
				FROM 	PER_LDAP_USERS ldu1 
				WHERE 	ldu1.username 	= ldu.username 
				AND 	ldu1.user_guid 	= ldu.user_guid
			)
		)
	) AS EMAIL
	,pu.active_flag
FROM
	 per_users pu
	,per_all_people_f papf
	,per_person_names_f ppnf
	,per_email_addresses pea
WHERE	1=1
AND	pu.PERSON_ID = papf.person_id (+) 
AND pu.person_id = ppnf.person_id (+) 
AND pu.person_id = pea.person_id  (+) 
AND pu.username in( 'username')
AND NVL(ppnf.name_type,'GLOBAL') 	= 'GLOBAL'
AND NVL(pea.email_type,'W1') 		= 'W1'
AND papf.effective_start_date = (
		select max(papf1.effective_start_date) from per_all_people_f papf1 
		where papf1.person_id =papf.person_id 
	);
;

SELECT usr.username,
       NAME.full_name,
       email.email_address,
       role_name.role_common_name,
       role_name.role_name,
       role_name.description,
       usr.active_flag,
       usr.creation_date,
       usr.last_update_date,
       usr.last_updated_by
FROM   per_users usr,
       per_person_names_f_v NAME,
       per_email_addresses_v email,
       per_user_roles role,
       per_roles_dn_vl role_name
WHERE  1 = 1
       AND NAME.person_id = usr.person_id
       AND email.person_id = usr.person_id
       AND NAME.effective_end_date > sysdate + 1
       AND role.user_id = usr.user_id
       AND role_name.role_id = role.role_id
       AND Upper(role_name.role_common_name) LIKE Nvl(Upper(:p_role_name), '%')
       AND Upper(usr.username) LIKE Nvl(Upper(:p_username), '%')
       AND Upper(NAME.full_name) LIKE Nvl(Upper(:p_fullname), '%')
ORDER  BY NAME.full_name ASC ;

Source : https://docs.oracle.com/