How to Extract User Role Details with SQL in Oracle Fusion

-

When working with Oracle HCM / Fusion Applications, administrators and developers often need to fetch details about users, their assigned roles, and their effective dates. This helps in audits, access reviews, and troubleshooting authorization issues.

In this post, we’ll walk through a SQL query that retrieves user-role information from Oracle HCM tables.

SQL Query:

                    SELECT

    pus.user_id             "User ID",

    pus.username            "Username",

    ppnaf.full_name         "Full Name",

    prdn.role_common_name   "Role Common Name",

    prdtl.role_name         "Role Name",

    to_char(purs.start_date, 'DD-MON-YYYY') "Role Start Date",

    to_char(purs.end_date, 'DD-MON-YYYY')   "Role End Date",

    prdn.job_role           "Job Role",

    prdn.active_flag        "Active Flag"

FROM

    per_user_roles       purs,

    per_users            pus,

    per_roles_dn_tl      prdtl,

    per_roles_dn         prdn,

    per_person_names_f   ppnaf

WHERE

    1 = 1

    AND pus.user_id = purs.user_id

    AND prdn.role_id = purs.role_id

    AND prdtl.language = userenv('lang')

    AND prdtl.role_id = prdn.role_id

    AND nvl(pus.suspended, 'N') = 'N'

    AND pus.username = :p_username         --Parameter

    AND ppnaf.person_id = pus.person_id

    AND ppnaf.name_type = 'GLOBAL'

    AND pus.active_flag = 'Y'

    AND nvl(pus.start_date, sysdate) <= sysdate

    AND nvl(pus.end_date, sysdate) >= sysdate

    AND ppnaf.effective_end_date >= sysdate

ORDER BY

    pus.user_id;

Explanation of Key Tables

  • PER_USERS (PUS): Stores application user details such as user_id, username, and account status.

  • PER_USER_ROLES (PURS): Maintains the mapping between users and roles, including effective start and end dates.

  • PER_ROLES_DN (PRDN): Holds role metadata such as role_id, job_role, and active status.

  • PER_ROLES_DN_TL (PRDTL): Translation layer for role names, ensuring role names display based on session language.

  • PER_PERSON_NAMES_F (PPNAF): Stores person-related name information, such as full name, with effective dating.

Comments

Post a Comment

Popular posts from this blog

Query to find Responsibility and Request Group from Concurrent Program

-
 In this blog, we’ll break down a query that helps you trace a specific Concurrent Program through its Application , Request Group , and associated Responsibilities in Oracle EBS R12 Objective The goal of this query is to find: The User-Friendly Name of a Concurrent Program The Technical Concurrent Program Name The Application to which it belongs The Request Group under which it is registered The Responsibility that grants access to it This is especially useful for ensuring correct program registration or for debugging access issues. SQL Query:      SELECT DISTINCT      fcpl.user_concurrent_program_name,     fcp.concurrent_program_name,     fapp.application_name,     frg.request_group_name,     fnrtl.responsibility_name FROM      apps.fnd_request_groups         frg,     apps.fnd_application_tl         fapp,   ...
Read more »»»»

3 Way PO Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation

-
3 Way Matching SQL Query in Oracle Fusion: Purchase Order, Receipt, and Invoice Reconciliation In any procurement system, ensuring the accuracy of supplier payments is critical. One of the most robust methods for validating supplier invoices is the 3-way matching process. This blog breaks down the 3-way matching concept in Oracle E-Business Suite (EBS) or Oracle Fusion, explains its benefits, and presents a detailed SQL query that helps extract and reconcile PO, Receipt, and Invoice data. What is 3-Way Matching ? 3-Way Matching is a process that compares the following three documents before payment is released: Purchase Order (PO) – What you ordered Receipt – What you received Invoice – What the supplier billed This comparison ensures that you only pay for goods that were ordered and received , avoiding overpayments, duplicates, and fraud. Matching Criteria   Element              PO      Receipt  ...
Read more »»»»