Starting in release 12, Oracle has changed the way payment formats are applied against the data set. Out with Reports, and in with BI Publisher/XML.
Recently, we were faced with customizing the NACHA US file format to meet the receiving bank's specs. In this post, we will walk you through the steps to quickly make this change.
Before we begin, it is important to verify if your payment version has been patched up to deal with a known issue around the IBY IDENTITY file. This file works in conjunction with your Nacha BI Publisher template, and when it comes to the seeded NACHA template, this file is saved properly in the base XDO tables.
Follow this link to Patching IBY IDENTITY FOR R12 to see if you need to mannualy apply a small script to your instance. It is a linux command that makes the file available for your custom template upload. When you upload a custom template, Oracle saves a record in the XDO_LOBS table along with one extra row for this IBY IDENTITY file, and without it you will receive the error "Error: an error occurred during formatting. Please verify the template is valid."
Now, on with the NACHA change. For this example we will assume you will use tags already supplied by the datasource "IBY_FD_INSTRUCTION_1_0." If you need to add custom tags, please refer to the public package "IBY_FD_EXTRACT_EXT_PUB" where Oracle allows you to add on to the xml tree.
Step 1: Download the NACHA format RFT file that best suits your need. In the example below, we chose the CCDP format. You do this in the XML Administrator responsibility. Just drill down to where you can see the "download" link. Pick either the one with a territory or without, it doesn't matter.
Step 2: Rename this on your hard drive using your custom prefix ("XX") on the front.
Step 3: Open the RTF and modify according to your requirements. This uses a different format than what you may be accustomed to with BI Publisher. This is the eText Outbound style and almost looks like a document about Nacha, rather than true code. But it is a nice method Oracle introduced to allow for readable code to a functional person, as well as an easy way to change a fixed format file.
Step 4: Upload the new file under a new template that you setup using the same datasource as the standard Nacha.
Step 5: Create a new Payment Format that uses this template
Step 6: Create a new Payment Profile that uses the new Format.
Step 7: Create a payment run using the new format.
Below is an example of how to make specific changes in the template.
1. Change the name being paid from using the supplier name to the tax reporting name first if it exists, otherwise stick with the vendor name.
2. Remove the debit records
3. Change the Addenda count in record "8" to reflect that we removed one of the two record "6" because of the debit.
To change the payee it is important to note that Oracle supplies in the XML file two valuable names that could go on the payment record. Within the tree, under the parent tag of "Payee", you will see a "Name" tag as well as "AlternateName." Oracle seems to determine the AlternateName from the tax reporting name field on the supplier record. This can come in handy when you vendor name has other information in it that does not reflect their bank account name. However, most companies keep the tax reporting name in sync with their 1099 company or personal name as it was probably used when opening a bank account. In position 55, see the syntax for adding the AlternateName tag as the first in line.
Remove Debit Records
Some banks do not like this extra record. You need to be careful about what you delete in this file, being cautious not to remove specific table heading rows that are needed.
See the image below for what it looks like after removing part of the "6" record which seems to be embedded in the "7".
Notice how the "7" record must still be within the loop of OutboundPayment. The debit headers were removed.
Change Addenda Count in Record "8"
In position 5, the Addenda Count field needs the total number of transactions ("6" records) plus one. you can see in the original formula,
InstructionTotals/PaymentCount*2 + 1That is multiplies the payment count times two. This is because of the debit and credits. To keep it documented in the code, keep the multiplier and change to:
InstructionTotals/PaymentCount*1 + 1Summary
Once you upload, this takes effect immediately, so try to pay another invoice and see the output.
If you need assistance in modifying your templates, OnShore would be happy to assist.