LOWER() + :xdo_user_name
Creating user-aware BI Publisher reports with :xdo_user_name in Oracle Fusion Cloud means letting the report engine quietly inject the signed-in user into your SQL, then using that to drive row-level filtering.
Why :xdo_user_name matters
In Oracle Fusion Cloud BI Publisher, :xdo_user_name is a built‑in bind variable that resolves to the username of the person running the report.
Because BI Publisher does not expose a numeric user ID directly, this username is often the cleanest way to join back to Fusion security tables or user records and enforce user-specific visibility.
The case-sensitivity trap
From 11g onwards, xdo_user_name reflects the username exactly as entered at login, which can introduce case-sensitivity issues in your predicates.
If the application stores usernames in a different case than the login (for example, all lowercase in PER_USERS), simple equality checks will fail silently and return no data.
Normalising with LOWER()
To avoid this, normalise both sides of the predicate so Oracle compares like with like.
A common and effective pattern in Fusion Cloud data models is:
WHERE LOWER(Users.user_name) = :xdo_user_name
In this approach, the user_name column is forced to lowercase, and the value supplied in :xdo_user_name is expected to already be lowercase, matching Oracle’s documented behaviour for system variables in BI Publisher.
Putting it into a Fusion data model
A typical user-aware data set might look something like this:
SELECT
Users.user_name,
Users.person_id,
Users.start_date,
Users.end_date
FROM per_users Users
WHERE LOWER(Users.user_name) = :xdo_user_name;
Here, the query returns only the row for the logged‑in user, which can then be used to drive joins to secured views, HR data, or downstream filters in the same data model.
The key is that the filtering happens in the data model itself, so every layout (RTF, Excel, PDF) inherits the same row-level security behaviour with no extra work.
When to use this pattern
This technique is particularly useful when:
- You need self-service reports where each user should only see their own transactions, requests, or HR records.
- You are complementing Fusion role-based security with additional “by user” filters driven from the database, without exposing extra report parameters to the end user.
Handled carefully, :xdo_user_name plus LOWER() gives a simple, declarative way to keep BI Publisher reports aligned with how Fusion knows each user, without resorting to complex custom security code.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
