Can you run an ESS job to run a BI Bursting query to burst to a SFTP server

jasmine baker

Summary

Can you run an ESS job to run a BI Bursting query to burst to a SFTP server

Content

I have a very specific issue/question;

The customer wants to use the out of the box ESS job 'Print Receivables Transactions' to run the BI report 'Invoice Print Transactions Template'.

Included in this BI report is a custom bursting query that bursts individual PDF documents (split by customer_trx_id) to the Oracle SaaS server.

I have scheduled this report manually for a couple of transactions through BI and it successfully bursts the transactions to the SFTP server, when I go to 'Reports Job History' I can see the correct number of output files there and a successful message (green tick) indicating the report ran successfully and the output was burst to the SFTP server (I can also see the correct output on the SFTP when I log on).

When I run the ESS job through the external application, the bursting query does not get kicked off. If I go to 'Reports Job History' I can see the successful message (green tick) saying the report has run successfully but there is no output being generated and there is no output being sent to the SFTP server.

I have also tried altering the bursting query to burst to my email as a test, and I have the same issue.

It is important to have the bursting query working through the ESS job in the scenario for two reasons:

1. The customer needs the output to be burst onto the SFTP server as one of their core requirements

2. Running the BI report through the ESS jobs runs the Oracle Receivables API: AR_INVOICE_SQL_FUNC_PUB defined in ARTPSQBS.pls (package body) and ARTPSQS.pls (package spec).

The UPDATE_CUSTOMER_TRX function takes care of updating the following fields:

PRINTING_COUNT, PRINTING_LAST_PRINTED, PRINTING_ORIGINAL_DATE, and LAST_PRINTED_SEQUENCE_NUM.

Without the above fields being set by the package there is no way in knowing which transactions have been printed. I can not invoke this function myself as we do not have access to it, being in the cloud.

My main question now is; is it possible to burst output using a BIP bursting query, by running an ESS job?

If the answer is no, how do we ensure we can mark transactions as printed AND get those transaction burst to an SFTP server?

Thanks in Advance,