Retrieving Role, Duty Role, and Privileges Navigation Path in Oracle Fusion BI Report with SQL Query
The following Oracle Fusion SQL query enables administrators and auditors to retrieve the complete hierarchy of Job Roles, Duty Roles, and associated Privileges, along with the navigation path that defines their interrelationships. This query is particularly useful in Oracle Fusion BI Reports to analyze access governance, track inherited security relationships, and ensure compliance with internal […]
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” […]
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 […]
Query for GL Journal Creation, Approval, and Posting by the Same User
In Oracle General Ledger (GL), maintaining financial control and transparency is essential for every organization. One key area of compliance involves ensuring that no single user performs all critical actions, such as creating, approving, and posting the same journal entry. However, in some business cases or during audit investigations, it becomes important to validate whether […]
Query to Confirm Approval of Purchase Orders, Including Blanket POs, by the ‘Owner’
In Oracle ERP systems, maintaining accountability and transparency in the Purchase Order (PO) approval process is a critical compliance measure.While some organizations enforce strict Segregation of Duties (SoD), there are legitimate cases where a PO creator and approver may be the same individual, often referred to as the ‘Owner’. This SQL query is designed to […]