Create user in Oracle Fusion (Backend table details included). Know how to view First Name, Last Name, Email of fusion user.

To create user in Oracle Fusion follow the below steps:

• Login to fusion instance with credentials of the user having role "IT Security Manager" assigned to it
• Go to Navigator -> Security Console -> Users. Then click on "Add User Account" 

            

        

• Enter the required fields. By Default, Email field is defaulted to User Name field. You can change it if you want.

        

• You can now Click on Save and Close 
• You can now run the below query to check the user details

select * from PER_USERS  where username='john.wick';

PER_USERS  table stores one record per fusion system user and maps to a single LDAP user. 

select * from PER_LDAP_USERS where user_guid='F47207B750F25150E0530E7A660A3AD0';

PER_LDAP_USERS Table stores one record for each user to be processed in OIM when creating new users or maintaining user details.

• Now open the user record again by searching with User Name. Click on the Display Name link to open the record

• Click on Edit button to edit the record

• Change the Last Name, First Name and Email as below and then click on Save and Close button

• Now run the below query:

select 
    LDAP_USER_ID, LDAP_REQUEST_ID, REQUEST_TYPE, REQUEST_STATUS, USER_GUID,USERNAME,
    LAST_NAME,	FIRST_NAME,	EMAIL, LAST_UPDATE_DATE
from PER_LDAP_USERS where user_guid='F47207B750F25150E0530E7A660A3AD0'
order by last_update_date;

Here we can see that REQUEST_TYPE = UPDATE is added when we modified the user record. So, to know first name, last name, email of any user we need to look at latest record in PER_LDAP_USERS with REQUEST_STATUS =  COMPLETE

• Edit the user record again and click on Add Role and add the below roles then click on Add Role Membership:

• Click on Save and Close button to save the changes. Run the below query to check roles assigned to the user:

select  a.username,
        c.role_name 
from    per_users a, 
        per_user_roles b, 
        per_roles_dn_vl c 
where   a.user_id=b.user_id 
and     b.role_id=c.role_id 
and     username='john.wick';

You can also run the below query to get user details:

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 
	);
;

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