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