SQL Query to Extract Employees who don't have the user access in Oracle Fusion

Hi Friends, we are going to discuss about sql Query to Extract Employees who don't have the user access in Oracle Fusion. We will share the detail sql query in oracle fusion which helps to extract the complete employees detail who don't have the user access or Inactive user access. This is very useful sql query to find out the user employees details and trying to find out the employees who don't have the user access in oracle fusion. We will also try to share the important tables too in this query. We can use this sql query to develop the custom BIP report for employees and user access in oracle fusion. Please find below the complete detail about SQL Query to Extract Employees who don't have the user access in Oracle Fusion.

SQL Query to Extract Employees who don't have the user access in Oracle Fusion

4 Important tables in sql query to Extract Employees who don't have the user access in Oracle Fusion

1.per_all_people_f 

2.per_all_assignments_m

3.per_periods_of_service

4.per_users

Detail SQL Query to Extract Employees who don't have the user access in Oracle Fusion

Here below is the sql query to extract the Employees against which there is no application user access created in the system.

select person_number,effective_end_date,

effective_start_date,

(select username from per_users

where person_id=pap.person_id) username,

(select a1.NAME from HR_ORGANIZATION_UNITS_F_TL a1,per_all_assignments_m a2

where a1.organization_id = a2.business_unit_id

and a2.effective_end_date > sysdate

and a2.person_id=pap.person_id

and rownum=1) ou_name

from per_all_people_f pap

where (select count(*) from per_users

where person_id=pap.person_id

and NVL(SUSPENDED,'N')='N')=0

and pap.person_id in (select PERSON_ID from HR_ORGANIZATION_UNITS_F_TL a1,per_all_assignments_m a2

where a1.organization_id = a2.business_unit_id

and a2.assignment_type IN ('E')

and pap.effective_end_date > sysdate

and pap.person_id in ((SELECT PERSON_ID FROM per_periods_of_service

WHERE 1=1

AND ACTUAL_TERMINATION_DATE IS NULL))

SQL Query to Extract Employees who don't have the user access in Oracle Fusion