Retrieving Oracle Fusion BI Report Role, Duty, and Navigation Path using SQL Query

In Oracle Fusion, understanding the relationship between Job Roles and Duty Roles is essential for effective access governance and audit compliance. The query below helps users retrieve the full navigation path of roles, duties, and their hierarchy in Oracle Fusion BI Reports, enabling security administrators and auditors to visualize how access privileges flow through the organization.

SQL Query

SELECT
    "A"."Job_Role_id",
    (SELECT "ASE_ROLE_VL"."ROLE_NAME"
     FROM "FUSION"."ASE_ROLE_VL" "ASE_ROLE_VL"
     WHERE "ASE_ROLE_VL"."ROLE_ID" = "A"."Job_Role_id") AS "Job_Role_Name",
    "A"."Duty_Role_id",
    "A"."Duty_Role_Name",
    "A"."ROLE_TYPE_NAME",

    “A”.”Navigation_path”

FROM (

    SELECT DISTINCT
        CONNECT_BY_ROOT "ASE_ROLE_ROLE_MBR"."CHILD_ROLE_ID" AS "Job_Role_id",
        NVL("ASE_ROLE_ROLE_MBR"."CHILD_ROLE_ID", "ASE_ROLE_VL"."ROLE_ID") AS "Duty_Role_id",
        NVL((SELECT "ASE_ROLE_VL"."ROLE_NAME"
             FROM "FUSION"."ASE_ROLE_VL" "ASE_ROLE_VL"
             WHERE "ASE_ROLE_VL"."ROLE_ID" = "ASE_ROLE_ROLE_MBR"."CHILD_ROLE_ID"),
            "ASE_ROLE_VL"."ROLE_NAME") AS "Duty_Role_Name",
        (SELECT "ASE_ROLE_TYPE_VL"."ROLE_TYPE_NAME"
         FROM "FUSION"."ASE_ROLE_TYPE_VL" "ASE_ROLE_TYPE_VL"
         WHERE "ASE_ROLE_TYPE_VL"."ROLE_TYPE_CODE" = "ASE_ROLE_VL"."ROLE_TYPE_CODE") AS "ROLE_TYPE_NAME",
        SYS_CONNECT_BY_PATH("ASE_ROLE_VL"."ROLE_NAME", ' -> ') AS "Navigation_path",
        "ASE_ROLE_VL"."EFFECTIVE_END_DATE" AS "EED1",

        “ASE_ROLE_ROLE_MBR”.”EFFECTIVE_END_DATE” AS “EED2”

    FROM “FUSION”.”ASE_ROLE_ROLE_MBR” “ASE_ROLE_ROLE_MBR”,

         “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL”

    WHERE “ASE_ROLE_VL”.”ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID” (+)

    CONNECT BY PRIOR “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID”

       OR PRIOR “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID” = “ASE_ROLE_VL”.”ROLE_ID”

) “A”

WHERE “A”.”ROLE_TYPE_NAME” LIKE ‘%Duty Roles%’

  AND (“A”.”EED1″ >= SYSDATE OR “A”.”EED1″ IS NULL)

  AND (“A”.”EED1″ >= SYSDATE OR “A”.”EED1″ IS NULL);

Query Overview

This SQL query retrieves the hierarchical mapping of Job Roles and Duty Roles in Oracle Fusion, including:

• Job Role ID and Name
• Duty Role ID and Name
• Role Type (e.g., Duty Roles, Job Roles, Abstract Roles)
• Navigation Path, showing how roles are connected
• Effective Dates, ensuring only active roles are retrieved

The query utilizes CONNECT BY logic to traverse the parent-child relationship between roles, providing a hierarchical view of how roles are assigned and inherited.

Key Highlights

Data Source Tables:

• ASE_ROLE_ROLE_MBR – Stores parent and child role relationships.
• ASE_ROLE_VL – Contains details of role names and effective dates.
• ASE_ROLE_TYPE_VL – Defines the type of each role (Job, Duty, Abstract).

Navigation Path Tracking:

• The SYS_CONNECT_BY_PATH function constructs a role hierarchy chain, such as:
• Job Role → Duty Role → Privilege

Filter on Active Roles:

• The query filters records where effective end dates are greater than or equal to the current date or null, ensuring only active roles appear in results.

Business Use Case

This query is particularly beneficial for:

• Access Review & Audit: To trace how job roles inherit duties and privileges.
• Role Design Analysis: To identify redundant or overlapping roles in Fusion Security.
• Segregation of Duties (SoD): To detect role conflicts and compliance risks.
• Security Reporting: To generate BI reports that visualize role hierarchies and governance structure.
  

By leveraging this query, organizations can maintain cleaner, compliant, and well-documented role structures within Oracle Fusion Applications.