In modern enterprise landscapes, automated and secure data extraction from ERP systems is a critical requirement—especially for financial reporting, regulatory compliance, and downstream system integrations.
This article demonstrates a scalable and reusable
integration pattern using Oracle Integration Cloud to extract data from BI
Publisher reports in Oracle Fusion Applications and deliver it as a CSV file to
an SFTP location.
This approach eliminates manual intervention, ensures data
consistency, and aligns with enterprise integration best practices.
🔍 Use Case & Business
Value
Organizations frequently require:
- Automated
extraction of financial or transactional data
- Scheduled
or on-demand file generation
- Secure
file transfer to external systems (banks, auditors, partners)
This solution provides:
- End-to-end
automation
- Standard
Oracle-supported integration (no custom code)
- Secure
and scalable file delivery mechanism
⚙️ Prerequisites
Ensure the following components are available:
- Basic Integration Cloud knowledge
- BI
Publisher Data Model and Report (via Reports & Analytics), (Reprot SQL is pasted at the end of this article)
- Report
Path
- Sample
XML Output (to derive XSD schema)
- Access
to Oracle Fusion instance
- Opaque
schema (opaque.xsd)
- Report
XSD schema
🧩 Solution Architecture
Overview
The integration leverages the standard SOAP service: ExternalReportWSSService
→ runReport
This service enables:
- Execution
of BI Publisher reports
- Retrieval
of output in Base64 format
The integration flow is designed as follows:
REST Trigger → SOAP (BIP Report) → Stage File → SFTP
This modular design ensures flexibility and reusability
across multiple reporting scenarios.
Report Path Example
/Custom/Financials/GNE_AR_E_INVOICES_RPT.xdo
To find the report path:
- Navigate
to Reports and Analytics
- Locate
your report
- Click
More → Schedule
- Copy
the report path
🚀 Implementation Steps
1. REST Trigger (Entry Point)
A REST endpoint is used to
initiate the integration:
- Method:
POST
- Parameters:
Optional (can be extended for dynamic inputs like report path, date
filters, etc.)
This allows the integration to be
invoked:
- On-demand
- Via
external systems
- Through
schedulers
Select the Action
as POST
2. SOAP Adapter – BI Publisher Invocation
Configure SOAP connection using:
- WSDL
URL: https://<servername>/xmlpserver/services/ExternalReportWSSService?WSDL
- Operation:
runReport
Key Mappings
- reportAbsolutePath:
/~muhammad.aamir/GNE_AR_E_INVOICES_RPT.xdo
- sizeOfDataChunkDownload
= -1
(Ensures full report output is retrieved)
The response will return report data in Base64 format.
3. Stage File – Handling Binary Payload
Since the BI Publisher response is binary, we use a Stage
File action with an opaque schema.
Why This Matters
Handling Base64 payloads correctly is critical in OIC to
avoid:
- File
corruption
- Partial
data writes
- Encoding
issues
Why Opaque Schema?
The BI Publisher output is in binary (Base64), so we must
use an opaque schema to handle it.
Opaque Schema (opaque.xsd)
<?xml
version="1.0" encoding="UTF-8"?>
<schema
targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/"
xmlns="http://www.w3.org/2001/XMLSchema">
<element name="opaqueElement"
type="base64Binary"/>
</schema>
Map:
- reportBytes
→ opaqueElement
This converts the SOAP binary response into a file.
4. SFTP Adapter – Secure File Delivery
Configure SFTP connection with:
- Host
and credentials
- Target
directory
- File
naming convention
Key Considerations
- Dynamic
file naming (e.g., timestamp-based)
- Directory
structuring for downstream consumption
- Optional
PGP encryption for enhanced security
Mapping
Map the following:
- File
Reference (from Stage File output)
- File
Name (dynamic or static)
- Directory
Path
Testing the Integration
- Activate
the integration in Debug mode
- Enable
payload tracking
- Run
the integration from the Actions menu (⋮ → Run)
Expected Result
- Integration
completes successfully
- No
errors or warnings
- File
is generated at the SFTP location
Run the
integration
Select the integration
and from 3 dot menu, choose Run
Click run to
execute the instance
Integration
is successfully extorted without any error or warning
And notice Invoke
WriteBIPtoSFTP activity, it has the file name
We will
check now at SFTP Location
File is
created and next is the file content screenshot
CSV file
🧠 Key Design
Considerations
This solution is designed with enterprise-grade practices:
- Loose
coupling via REST trigger
- Reusability
across multiple reports
- Scalability
for large data volumes
- Standard
Oracle services only (no custom code)
🌟 Real-World Extensions
This pattern can be extended for:
- Scheduled
batch integrations
- Multi-report
orchestration
- Integration
with downstream systems (banks, tax authorities)
- Adding
notifications (email alerts, callbacks)
- Enabling
encryption (PGP) for compliance
📌 Conclusion
This integration demonstrates a robust, scalable, and
Oracle-recommended approach for extracting BI Publisher data and delivering
it securely to external systems.
By leveraging standard capabilities of OIC and Oracle
Fusion, organizations can:
- Reduce
manual effort
- Improve
data reliability
- Accelerate
integration delivery
📚 References
Oracle Documentation:
https://docs.oracle.com/en/cloud/paas/application-integration/soap-adapter/call-oracle-fusion-applications-business-intelligence-publisher-report-services.html 3
Report SQL Query:
rct.trx_number AS invoice_number,
rct.trx_date AS invoice_date,
hp.party_name AS customer_name,
hps.party_site_name AS customer_site,
rctl.line_number,
rctl.description AS line_description,
rctl.quantity_invoiced,
rctl.unit_selling_price,
rctl.extended_amount AS line_total,
ztl.tax_regime_code,
ztl.tax,
ztl.tax_rate_code,
ztl.tax_amt AS tax_amount
FROM
ra_customer_trx_all rct
JOIN
ra_customer_trx_lines_all rctl ON rct.customer_trx_id = rctl.customer_trx_id
JOIN
hz_cust_accounts hca ON rct.bill_to_customer_id = hca.cust_account_id
JOIN
hz_parties hp ON hca.party_id = hp.party_id
JOIN
hz_cust_site_uses_all hcsu ON rct.bill_to_site_use_id = hcsu.site_use_id
JOIN
hz_cust_acct_sites_all hcas ON hcsu.cust_acct_site_id = hcas.cust_acct_site_id
JOIN
hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
LEFT JOIN
zx_lines ztl ON rctl.customer_trx_id = ztl.trx_id
AND rctl.customer_trx_line_id = ztl.trx_line_id
WHERE
rct.complete_flag = 'Y' -- Only retrieve finalized invoices
AND trunc(rct.trx_date) BETWEEN TO_DATE('2025-01-01', 'YYYY-MM-DD') AND TO_DATE('2025-03-31', 'YYYY-MM-DD')--Filter by specific invoice if needed
ORDER BY rct.trx_number, rctl.line_number