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.

Using Simplified IF-ELSE condition in BI Publisher

In Oracle BI Publisher allows the use of Conditional Statements in RTF Templates. It uses similar logical conditions in PL/SQL such as IF-THEN, CHOOSE-WHEN, etc. More details and examples can be found from Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

However, the documentation provided only shows a scenario wherein you have just one condition. Example is below:

<?if:VENDOR_NAME='COMPANY A'?>This is the Vendor A<?end if?>

However, the code becomes a bit too troublesome and long if you need to have an ELSE condition:

<?if:VENDOR_NAME='COMPANY A'?>This is Vendor A<?end if?>
<?if:VENDOR_NAME!='COMPANY A'?>This is NOT Vendor A<?end if?>

To simplify this, you may use the XSLT function IFELSE that addresses this requirement:

<?xdoxslt:ifelse(VENDOR_NAME='COMPANY A',This is Vendor A,This is NOT Vendor A)?>

It works similar to the DECODE function in PL/SQL.
If the given condition is Satisfied, it will return the first parameter, if not, then it will return the second parameter.

Seems simple enough, eh?
Let me know in the comments if there's any issues!

Deactivate the seeded Separate Remittance Advise Layout in Oracle Fusion Applications

Even if the seeded Separate Remittance Advise (SRA) Layout has already been deactivated from BI Publisher, , it may still appear on the LOV of the SRA Screen.

Take the sample below. the Custom SRA Layout is already selected as the default and the Seeded layout is already Inactive:

However, the seeded layout can still be selected when we go into Payments > Task List > Send Separate Remittance Advise:

To suppress this, go into Setup and Maintenance > Task List > Search and key in "Manage Formats" and search for "Disbursement Separate Remittance Advise Formats":

Open the seeded layout and end-date it:

This will effectively suppress the layout from being selected from the SRA screen: