Get the XML Extract of Payments or Separate Remittance Advise from Oracle Fusion Applications

When an organization is issuing Payments to Suppliers and sending Remittance Advise, 

there would be instances where issues would arise (Incorrect Amounts, Invalid Supplier Details, etc.) and there is a need to review the XML data fusion is sending out.

However, getting the XML Extract of Payments or Remittance Advises in Fusion can be quite difficult as compared to other Fusion Reports.

In comparison, getting XML data for AR Invoices/Credit Memos are particularly easy, as the option to download its XML is already included when running its ESS job ("Print Receivables Transactions").

On the other hand, Payments or Remittance Advises don't have this capability.

Luckily, there are two workarounds Oracle has identified and I have personally tested both.

Listed below are the workarounds, including their advantages and disadvantages:

1. Use BI Publisher's ad-hoc querying capabilities. 
• The advantage of this option is there is no need for the actual process to be re-run, particularly useful if the issue has occurred in Production.
• The downside of this option is sufficient BI Administrator Privileges is needed to create the ad-hoc Data Model that will retrieve the XML.
2. Modify the Report's Output Format to XML and re-run the process.

• Unfortunately, I don't actually see an advantage with regards to this option.
• Because actual process needs to be re-run, this option would be risky if done in the Production Environment, as the application might re-send the data to suppliers or the Bank, creating confusion.

For this post, we will focus on the Option # 1. The steps to do this are as follows:

1. Login to Oracle Fusion Applications using a Username that has the sufficient Custom BI Role (inherited BI Admin or BI Author role)
2. Go to BI Publisher by appending "xmlpserver" on the browser's URL: (i.e. https://xxxx.xx.xxx.oraclecloud.com/xmlpserver)
3. Create a New Data Model 

4. Create a New SQL Data Set:

5. Use the Below Query with the appropriate Payment_File_Number 

select document 
from  iby_trxn_documents 
where  payment_instruction_id = &Payment_File_Number
and  doctype = 100

6. Run this Data Set and Click on "Export".

7. Open this output in either Notepad or Notepad++ and re-save it as an XML file.

The resulting XML file would be the actual XML data used by the Payment File and Remittance Advise.