How to utilize session variables inside BI publisher reports in Oracle Fusion Cloud

So, you are trying to create a custom report in Oracle Fusion based on it's older EBS version but the EBS query contains references to session variables like USER_ID, RESP_ID.

But in Fusion, we cannot derive such session/user specific information using profile options like we used to do in EBS.

Let's see how to derive this information in Fusion BI reports.

The BI Publisher stores information about the current user that can be accessed by the report data model.

The user information is stored in system variables as described below.

Note:
BI Publisher populates all the system variables in an online report.

In case of a scheduled job, publisher doesn’t populate below system variables:

XDO_USER_REPORT_LOCALE
XDO_USER_UI_LOCALE
XDO_USER_UI_ORACLE_LANG

If we want to add the user information to a custom data model, we can define the variables as parameters and then define the parameter value as an element in the data model.

We can also add the variables as parameters then reference the parameter values in the report.

For example, if we use below query in Data Model:

select
:xdo_user_name as USER_NAME,
:xdo_user_report_oracle_lang as REPORT_LANGUAGE,
:xdo_user_report_locale as REPORT_LOCALE,
:xdo_user_ui_oracle_lang as UI_LANGUAGE,
:xdo_user_ui_locale as UI_LOCALE
from dual

It will return below output:

XML Output:

<?xml version="1.0" encoding="UTF-8"?>
<! - Generated by Publisher - >
<DATA_DS>
<G_1>
<REPORT_LANGUAGE>US</REPORT_LANGUAGE>
<REPORT_LOCALE>en_US</REPORT_LOCALE>
<UI_LANGUAGE>US</UI_LANGUAGE>
<UI_LOCALE>en_US</UI_LOCALE>
<USER_NAME>john.doe</USER_ID>
</G_1>
</DATA_DS>

Now, let's see a practical example where we can use such derivations.

Suppose below is the business requirement:

- Identify the current user name

- Find out all roles associated with this user

- Find out all the business units associated with above role(s)

Then we would write below query to achieve this:

SELECT   DISTINCT bu.bu_name

 FROM     fusion.FUN_ALL_BUSINESS_UNITS_V     bu,

                 fusion.FUN_USER_ROLE_DATA_ASGNMNTS  role,

                 fusion.per_users pu

 WHERE role.org_id = bu.bu_id

AND        pu.USER_GUID = role.USER_GUID

AND        pu.username = :xdo_user_name

AND        role.active_flag = 'Y'

The above query can be extrapolated to further utilize this business unit information to link in a bigger derivation/query based on the requirements.

This is how we can make use of session/system variables in BI publisher report in Oracle Fusion.