Sunday, July 31, 2011

Oracle R12 NACHA Customizations

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

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.
Example Requirements

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.

Payee :
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".