Introduction
Oracle Fusion Applications provide robust reporting capabilities through Oracle BI Publisher (BIP). Often, organizations require sending report outputs directly to the user who triggered the report execution. By default, Fusion reports use predefined or static email recipients, limiting dynamic user-specific delivery options.
In this blog post, we’ll show how to use BI Publisher Bursting to dynamically deliver reports via email directly to the user who executed them, even when users aren’t defined as employees within Fusion.
What is BIP Bursting?
BIP Bursting allows Oracle BI Publisher to automatically split (or “burst”) report outputs based on defined criteria, sending personalized outputs directly to users via email or other delivery methods.
Common scenarios include:
✅ Dynamic Recipient Emailing: Reports delivered directly to initiating users.
✅ Secure, Personalized Distribution: Ensuring each user receives the correct content.
The Challenge: Dynamically Emailing the Running User
Oracle Fusion’s standard report scheduling and ESS jobs typically send reports to static recipients. However, many businesses have a requirement to:
Automatically email the generated report output to this user’s email.
Dynamically identify the currently logged-in user who initiated the report.
Retrieve their email address, even if they’re not defined as Fusion employees.
Capturing the User Email Dynamically with SQL
To address this requirement, use the following SQL query within your BI Publisher report data model to dynamically retrieve the running user’s email, supporting both employee and non-employee Fusion users:
select coalesce(pea.email_address, ldap.ldap_email) as email_address
from per_users pu,
per_email_addresses pea,
(select max(email) keep (dense_rank last order by ldap_request_id) as ldap_email
from PER_LDAP_USERS lu
where lu.username = fnd_global.user_name) ldap
where pu.username = fnd_global.user_name
and pu.person_id = pea.person_id(+)
and pea.email_type(+) = 'W1'
and trunc(sysdate) between pea.date_from(+) and nvl(pea.date_to(+), to_date('31124712','ddmmyyyy'))Explanation:
Ensures reliable dynamic email retrieval regardless of user type.
Uses fnd_global.user_name to identify the currently executing Fusion user.
Retrieves email addresses from:
Employee records (per_email_addresses).
LDAP records (PER_LDAP_USERS) if the user isn’t defined as an employee.
Creating the Bursting Definition in BI Publisher
Follow Oracle’s documentation to create the bursting query, ensuring that you:
- Use the email address dynamically retrieved by the above query.
- Configure proper email delivery channels in the bursting definition.
- Define the report template, subject, body, and output format according to your organization’s needs.
For complete guidance, refer to Oracle’s official bursting configuration documentation.
Step-by-Step Implementation Summary
- Include the SQL query (above) in your report’s data model to capture the current user’s email.
- Create the Bursting definition according to Oracle’s standard documentation, using the dynamic email address.
- Test and verify that reports are dynamically delivered to the running user’s email upon execution.
Benefits of Dynamic Report Delivery with BIP Bursting
✅ Personalized Delivery: Report outputs are delivered directly to the requesting user without manual intervention.
✅ Improved Security: Reports are sent directly and securely to the intended recipient only.
✅ Enhanced User Experience: Immediate and targeted report access improves productivity.
Conclusion
By using Oracle BI Publisher’s Bursting engine with a dynamic SQL query, you can effectively automate the delivery of Fusion reports directly to users who execute them, even when they’re not defined as Fusion employees. This approach reduces manual effort, enhances security, and significantly improves user satisfaction.
? Need help optimizing your Oracle Fusion reporting solutions?
? Contact CID Solutions today for tailored support and advanced report automation services.
