DEV Community

Naveen
Naveen

Posted on

Real-Time Reporting: How to Push ATP Data to Oracle Fusion BI Publisher

#oracle #oic #analytics #ace

Most Oracle Fusion implementations follow a standard pattern: transferring data from Fusion Applications into Oracle Autonomous Transaction Processing (ATP) for analytics

But what happens when you need to go the other way ?

In this project, I designed a solution for a common business pain point: fetching processed data from ATP, generating a dynamic BI Publisher report, and distributing it via Oracle Integration Cloud (OIC) notifications.

The Business Challenge

Organizations store massive volumes of processed data in ATP—everything from billing records to custom integration staging tables. While ATP is great for storage, business users need:

  • Professional, formatted report templates.
  • Automated, scheduled delivery.
  • Rich HTML email notifications.

Our solution automates this entire flow, removing manual effort and custom scripting.

Solution Architecture

The flow moves from data storage to user delivery in a seamless loop:

Technologies Used
Oracle Technology Purpose Oracle ATP Source Data Repository OIC Orchestration Layer Fusion BI Publisher Report Design & Execution SOAP Services Remote Report Execution OCI Hosting Platform

Implementation Steps

Step 1: Create Scheduled Integration in OIC

We start with a Scheduled Integration. This removes human dependency and allows for daily, weekly, or custom business intervals.

Step 2: Connect to ATP and Execute SQL

Using the ATP Adapter, we establish a secure connection to fetch the "Active" records.


SELECT 
    * from emp
Enter fullscreen mode Exit fullscreen mode

Step 2.1: ATP Data to Stage File in OIC

Here we used stage file action in OIC to write the ATP data into a file, which further we mapped to SOAP services as content.

Click to see Loaded sample xml file in the stage file 
<?xml version="1.0" encoding="UTF-8" ?><root>
   <row>
      <EMPNO>7839</EMPNO>
      <ENAME>KING</ENAME>
      <JOB>PRESIDENT</JOB>
      <MGR/>
      <HIREDATE>1981-11-17T00:00:00.000+00:00</HIREDATE>
      <SAL>5000</SAL>
      <COMM/>
      <DEPTNO>10</DEPTNO>
   </row>
   <row>
      <EMPNO>7698</EMPNO>
      <ENAME>BLAKE</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>1981-05-01T00:00:00.000+00:00</HIREDATE>
      <SAL>2850</SAL>
      <COMM/>
      <DEPTNO>30</DEPTNO>
   </row>
</root>
Enter fullscreen mode Exit fullscreen mode

Here the mapping reference of stage writes from ATP

mapping reference

Step 3: Build Fusion BI Publisher Data Model

The incoming ATP data is structured into an XML format. This serves as the data source for our report.

Click to see Magic in the Fusion

Create Data model with the XML FILE

Loaded the same sample XML File which we used earlier

Preview Data & save as Sample Data

Don't forgot save it in folders.

Create report based on above Data model

Keep the report path with you

Step 4: Execute Fusion Run Report SOAP Service

This is the core of the integration. OIC invokes the runReport()
operation via the BI Publisher SOAP service.

Below is the mapper reference!!


Key Insight: The SOAP service is highly flexible. It accepts the XML data, applies your RTF/Excel template, and generates the output in your choice of format (PDF, Excel, CSV).

Click to see How reportBytes converts to DecodeBase64

Using assignment, I converted to DecodeBase64

Step 5: Send OIC HTML Notification

Finally, we use the OIC Notification Action to deliver the report.

Click to see Sample Email Template:

Sample Email Template:

<html>
<body>
<p>Hi Nj</p>
<p>Generate excel file using BIP report<br>
DB data - XML Data Model fusion - report xlsx</p>

<p>{bfy}</p>

<p>Best Regards,<br>Naveen Jejji<br>OIC Dev</p>


<div class="logo">
<img src ="https://osidigital.com/wp-content/uploads/2018/08/osi_logo_rgb.png" alt="company logo"
width ="250">
</div>
</body>
</html>
Enter fullscreen mode Exit fullscreen mode

Key Innovation: Flipping the Script

The Paradigm Shift

  • Traditional: Fusion → ATP (Data Archiving)
  • Our Approach: ATP → Fusion BI Publisher → OIC (Dynamic Reporting)

This leverages ATP as the "Source of Truth" while utilizing Fusion’s powerful formatting engine.

Measurable Outcomes

Metric Improvement Manual Effort Reduced by 90% Delivery Time Hours to Minutes Automation 100% Automated

Conclusion

This project demonstrates that the Oracle ecosystem is most powerful when its components work in a "reverse" architecture.

By moving data from ATP back into the Fusion reporting engine, we provide users with the professional outputs they need without manual intervention.

I’d love to hear your thoughts:

Have you implemented a similar "reverse" integration? What challenges did you face with the SOAP service limits? Let's discuss in the comments!

Top comments (0)