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 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.