Sending Invoice Statement and Dunning Letters to an FTP Location
A common client requirement in Oracle Cloud ERP is to deliver the output of
- Create Customer Statements
- Print Receivable Transactions
- Send Dunning Letters
to an SFTP location when the delivery flag for a customer is set to PRINT. However, clients often encounter an issue that the bursting does not work as expected, where the customer has the delivery method set to PRINT.
Why Bursting Fails for the PRINT Delivery Method?
Oracle Cloud ERP does not natively execute bursting when a customers delivery method is set to Paper/Print. This means that even if you customize the bursting query to send the output to an SFTP location, when the ESS Job runs, bursting will not trigger where the customers delievery method is set to PRINT.
Workaround for Bursting to FTP for PRINT Customers
01.
Create an FTP Site in BI Administration
02.
Set the delivery method to EMAIL for the desired group of customers / all customers in your system.
03.
For customers who should not actually receive invoices, statements or dunning letters via email, update the contact with a dummy email address.
04.
In the bursting query, use the dummy email as an identifier to deliver the document to the SFTP location.
SELECT del.customer_trx_id || '_' || del.payment_schedule_id as "KEY"
, :TEMPLATE_NAME AS TEMPLATE
, 'RTF' AS TEMPLATE_FORMAT
, 'PDF' AS OUTPUT_FORMAT
, del.trx_number AS OUTPUT_NAME
, 'FTP' AS DEL_CHANNEL
, 'XXX.XXX.XXX.XXX' AS PARAMETER1 --Server Name
, NULL AS PARAMETER2 --Username
, NULL AS PARAMETER3 --Password
, '/OUTBOUND/DOCUMENTS/AR/INVOICE' AS PARAMETER4 --Remote Directory
, del.trx_number || '_' || del.terms_sequence_number||'.pdf' AS PARAMETER5 --Remote File Name
, 'true' PARAMETER6 --Secure FTP
, NULL PARAMETER7 --FTP not used
FROM AR_BPA_DELIVERY_DETAILS del
WHERE (
del.billing_request_id=(SELECT instanceparentid FROM ess_request_history WHERE requestid =FND_JOB.REQUEST_ID)
OR del.billing_request_id=(SELECT parentrequestid FROM ess_request_history WHERE requestid =FND_JOB.REQUEST_ID)
)
This approach ensures that all statement documents will be uploaded to the FTP location, while customers with dummy email addresses will not receive any email. This is a practical solution which is also commonly applied in scenarios like Dunning Letters.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
