A Step-by-Step Guide to Conditional SFTP Bursting in Oracle Cloud BI Publisher

Introduction:

Oracle Fusion BI Publisher (BIP) is the primary workhorse for enterprise data extraction and outbound document delivery. While the standard bursting engine handles basic distribution well such as emailing a payslip to a specific worker real world integration requirements are often much more demanding.

A frequent challenge arises when an organization needs a single payroll or financial report to execute, split its data rows dynamically by business unit or country, and securely deliver those separate files into completely different third-party SFTP servers or specific, nested directory paths. Hardcoding static endpoints within delivery channels fails to scale.

This technical guide provides an end-to-end walkthrough on how to construct an advanced SQL data model and a dynamic Bursting Control XML script to route high-volume system files across separate secure servers and target sub-folders automatically.

Business Requirement:

Consider a multinational enterprise utilizing Oracle HCM Cloud across different entities(for eg: US, UK, and India). At the end of a processing cycle, a single third-party benefits interface file must be generated.

However, regulatory compliance and infrastructure policies dictate that:

• The US data file must be delivered to an external server named US_BENEFITS_SFTP in the folder /us/production/.
• The UK data file must be delivered to a separate server named UK_BENEFITS_SFTP in the folder /uk/compliance/.
• The system must perform these calculations and routing actions seamlessly during a single scheduled enterprise process run, without spawning multiple reports or requiring manual file splitting by IT administrators.

Solution:

1. Dynamic Bursting SQL Query:
The secret to dynamic routing lies within the DELIVERY_KEY and the specific parameter mappings inside your data model's bursting query. Instead of typing fixed server names, we use conditional SQL CASE statements or DECODE logic to establish distinct connection strings on a row-by-row basis.

Navigate to your Data Model, click on the Bursting tab, and implement an advanced query structured like shown below:

select 
    legal_entity_id as key,
    'BenefitsTemplate' as template,
    'en-US' as template_locale,
    'TEXT' as output_format,
    'SFTP' as delivery_channel,
  case when le_country = 'US' then 'US_BENEFITS_SFTP' when le_country = 'UK' then 'UK_BENEFITS_SFTP' else 'GLOBAL_BENEFITS_SFTP' end as parameter1,
  case when le_country = 'US' then '/us/production/' when le_country = 'UK' then '/uk/compliance/' else '/global/dropzone/' end as parameter2,
  'BEN_INTERFACE_' || le_country || '_' || to_char(sysdate, 'YYYYMMDD_HH24MISS') || '.txt' as parameter3,
  'sftp_oracle_user' as parameter4
from
(
    select 
        haou.legal_entity_id,
        ft.country as le_country
  from 
        per_all_assignments_m paam
    inner join hr_all_organization_units haou on paam.legal_entity_id = haou.organization_id
    inner join fnd_territories ft on haou.location_id = ft.territory_code
  where 
        trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
  group by 
        haou.legal_entity_id, ft.country
)

2. Bursting Control XML:
Once the data model query exposes these dynamic parameters, you must map them explicitly to the BI Publisher engine's delivery definitions. Under the Bursting Control XML block, implement the following clean mapping logic.

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://oracle.com" type="bursting">
   <xapi:request select="/DATA_DS/G_1">
      <xapi:delivery>
         <xapi:ftp server="${PARAMETER1}" remote-directory="${PARAMETER2}" remote-file="${PARAMETER3}" username="${PARAMETER4}">
            <!-- Password token -->
         </xapi:ftp>
      </xapi:delivery>
      <xapi:document output-type="${OUTPUT_FORMAT}" template-type="rtf" repository="generic">
         <xapi:template select="${TEMPLATE}" locale="${TEMPLATE_LOCALE}"/>
      </xapi:document>
   </xapi:request>
</xapi:requestset>

Best Practices & Environment Verifications:

To avoid processing exceptions during delivery, ensure your environment adheres to these three critical deployment rules.

Admin Console Pre-Configuration: The server values returned by PARAMETER1 (e.g., US_BENEFITS_SFTP) must exactly match the name of the SFTP server registered under the Administration -> Delivery -> FTP page in BI Publisher. If a typo exists, the engine throws a generic Delivery failed exception.

Directory Permissions Validation: Ensure that the user account declared in PARAMETER4 has absolute read, write, and execute permissions on the paths generated by PARAMETER2. If the directory path is missing on the remote host, BIP will not auto-create it unless specified by server parameters; it will fail the handshake instead.

Handling Memory Overheads: When processing massive employee structures, avoid passing data variables directly inside the bursting filename query string (PARAMETER3) if they can cause duplicates. Duplicate keys force file locking sequences on the target SFTP, lowering processing performance.

Conclusion:

By separating delivery settings from fixed properties and adding decision-making logic into the SQL data layer, technical consultants can greatly reduce the effort needed to maintain reports. This approach changes a fixed file extraction script into a smart, flexible distribution process that can handle complex global delivery requirements in a single scheduled run.