SQL Query for Retrieving Inactive Users in Oracle Fusion BI Report

The following SQL query is designed to identify inactive or suspended users in Oracle Fusion BI Reports, helping administrators maintain user governance, compliance, and license efficiency.

This query retrieves all users whose accounts are inactive (ACTIVE_FLAG = ‘N’) or suspended (SUSPENDED = ‘Y’) within the Oracle Fusion environment, ensuring better visibility and control over user access.

SELECT *
FROM "FUSION"."ASE_USER_VL" "ASE_USER_VL"
WHERE EXISTS (
    SELECT '1'
    FROM "FUSION"."PER_USERS" "PER_USERS"
    WHERE UPPER("ASE_USER_VL"."USER_DISPLAY_NAME") = UPPER("PER_USERS"."USERNAME")
      AND ("PER_USERS"."ACTIVE_FLAG" = 'N' OR "PER_USERS"."SUSPENDED" = 'Y')
);

Query Highlights

• ASE_USER_VL: Stores user profile information in Oracle Fusion.
• PER_USERS: Contains login details and account statuses of Fusion users.
• The EXISTS clause links both tables to fetch only users whose display name matches their username.
• The condition checks for users who are either inactive or suspended, indicating restricted access.
  

Business Use Case

This query supports organizations in:

• Auditing user access and identifying dormant accounts.
• Improving license utilization by removing inactive users.
• Enhancing compliance with access control policies.
• Preventing unauthorized access and strengthening data security posture.
  

By running this query periodically in Oracle BI Publisher or scheduling it within your Fusion Analytics setup, teams can maintain a clean and compliant user database, ensuring only active users retain access to enterprise systems.