SQL Query for Extracting Role, Duty Role, and Effective Date Navigation Path in Oracle Fusion BI Report

The following SQL query is designed to extract user role assignments, associated duty roles, and their effective date range from Oracle Fusion BI Reports. This helps administrators and auditors gain visibility into the role hierarchy and access timelines of Fusion users, ensuring strong governance and compliance tracking.

SELECT
    "ASE_USER_ROLE_MBR"."USER_ID" AS "USER_ID",
    (SELECT "ASE_USER_VL"."USER_DISPLAY_NAME"
     FROM "FUSION"."ASE_USER_VL" "ASE_USER_VL"
     WHERE "ASE_USER_VL"."USER_ID" = "ASE_USER_ROLE_MBR"."USER_ID") AS "USER_DISPLAY_NAME",
    "ASE_USER_ROLE_MBR"."ROLE_ID" AS "JOB_ROLE_ID",
    (SELECT "ASE_ROLE_VL"."ROLE_NAME"
     FROM "FUSION"."ASE_ROLE_VL" "ASE_ROLE_VL"
     WHERE "ASE_ROLE_VL"."ROLE_ID" = "ASE_USER_ROLE_MBR"."ROLE_ID") AS "JOB_ROLE_NAME",
    "ASE_USER_ROLE_MBR"."EFFECTIVE_START_DATE" AS "EFFECTIVE_START_DATE",

    “ASE_USER_ROLE_MBR”.”EFFECTIVE_END_DATE” AS “EFFECTIVE_END_DATE”

FROM “FUSION”.”ASE_USER_ROLE_MBR” “ASE_USER_ROLE_MBR”

WHERE (“ASE_USER_ROLE_MBR”.”EFFECTIVE_END_DATE” >= SYSDATE 

       OR “ASE_USER_ROLE_MBR”.”EFFECTIVE_END_DATE” IS NULL)

AND EXISTS (

    SELECT '1'
    FROM "FUSION"."ASE_USER_VL" "ASE_USER_VL"
    WHERE ("ASE_USER_VL"."EFFECTIVE_END_DATE" >= SYSDATE
           OR "ASE_USER_VL"."EFFECTIVE_END_DATE" IS NULL)
      AND "ASE_USER_VL"."USER_ID" = "ASE_USER_ROLE_MBR"."USER_ID"
);

Query Overview

This query provides a complete navigation path of how users are mapped to their job roles and duty roles, including when each assignment starts and ends. It uses key Oracle Fusion tables to ensure accuracy and traceability:

• ASE_USER_ROLE_MBR: Stores details of user-to-role assignments.
  
• ASE_USER_VL: Contains user display names and personal identifiers.
  
• ASE_ROLE_VL: Maintains role names and definitions within the Fusion security model.
  

The EFFECTIVE_START_DATE and EFFECTIVE_END_DATE fields define the validity period for each role assignment, helping teams track access changes over time.

Business Use Case

This query is essential for:

• Audit and Compliance Teams: To review which users hold active job or duty roles and verify date validity.
• Security Administrators: To trace overlapping roles and eliminate redundant access.
• License Management: To analyze active users and their associated access scopes for optimization.
• SoD (Segregation of Duties) Reviews: To identify users who may possess conflicting roles within the same timeframe.
  

By deploying this query in Oracle BI Publisher or Fusion OTBI, administrators can streamline access monitoring, maintain regulatory compliance, and enhance enterprise data security.