BI Report, Business Intelligence Report,, bursting sftp, Oracle Fusion, Oracle Fusion Finance

SFTP Bursting to Split and Deliver the File

SFTP Bursting is a functionality in Oracle Fusion in which a file is split into multiple files and placed on different SFTP servers. To know the basics of Bursting, please click here.

For me the requirement was to send the order information to two different SFTP locations based on the Business Unit they belonged to. So obviously the report had the BU Name in the query output. Let me change the scenario a bit and let’s say our requirement is to send the information of 1 Procurement BU’s PO to sftp server FTP_1 while the details of POs of another BU to sftp server FTP_2.  So my query looks like:

select hou.name BU, po.segment1 PO_NUM
from po_headers_all po,
hr_operating_units hou
where po.prc_bu_id = hou.organization_id
and po.creation_date>= sysdate -1

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
https://www.amazon.in/gp/product/B093CC1CLD?ie=UTF8&tag=oraclebytes-21&camp=3638&linkCode=xm2&creativeASIN=B093CC1CLD

We need to do the same steps as we did in email bursting. When it comes to choosing the value of “Split By” and “Deliver By”, we need to select BU as we just want to split the file for each BU. So this is how my bursting query looks like:

Select ‘BU_1’ KEY — this value must match the BU name in report query output precisely as it will decide which record goes in which file. No match will mean record not going anywhere
, ‘test’ TEMPLATE the template name and not the rtf etc name
, ‘en-EN’ LOCALE — locale of the template
, ‘PDF’ OUTPUT_FORMAT — outout format
, ‘FTP’ DEL_CHANNEL — has to be FTP for SFTP bursting
, ‘FTP_1’ PARAMETER1 –– name of the sftp connection. you would have defined it in administration–>Delivery –>FTP.
, ‘SFTP_USER1’ as PARAMETER2 — user name for the sftp connection
, ‘SFTP_PASSWD1’ PARAMETER3 — password for the sftp connection
, ‘/Home/’ PARAMETER4 — Location where you want to send the file to in sftp server
, ‘PO_Details.pdf’ PARAMETER5 — name of the pdf
,’true’ PARAMETER6 — keep it true for secure FTP
from dual
union all
Select ‘BU_2’ KEY
, ‘test’ TEMPLATE
, ‘en-EN’ LOCALE
, ‘PDF’ OUTPUT_FORMAT
, ‘FTP’ DEL_CHANNEL
, ‘FTP_2’ PARAMETER1
, ‘SFTP_USER2’ as PARAMETER2
, ‘SFTP_PASSWD2’ PARAMETER3
, ‘/Home/’ PARAMETER4
, ‘PO_Details.pdf’ PARAMETER5
,’true’ PARAMETER6
from dual

now run the report and your file will split into 2.

Related Posts:

Bursting
OTBI vs BI Reporting in Oracle Fusion
OTBI Report Creation in Oracle Fusion
BI Report Development In Oracle Fusion
RTF Date Formatting With Example
RTF Number Formatting With Examples
Looping Finite Number Of Times in RTF

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

https://docs.oracle.com/cd/E21764_01/bi.1111/e18862/T527073T555155.htm

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?