Wednesday, May 4, 2011

Oracle R12 NACHA Customizations

Platform: Oracle EBS
Version: R12

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.

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.

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 + 1
That 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 + 1
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.

Monday, April 18, 2011

AP Invoice Status - Where does it live?

Platform: Oracle EBS
Version: Any

Which column does the invoice status live in the AP invoice tables?

The technical and disappointing answer is...nowhere.  It is derived from logic.  See Oracle's note on Oracle Support for note#301806.1 for the news.

Oracle looks at the ap_invoice_distributions_all and specifically the match_status column for part of the status display.  There can be nulls, N, A, and T as values, and all your distribution lines main contain a combination of them.  These varieties drive the final logic for Validated, Needs Validation, or Needs Revalidation displays.

On top of that logic, Oracle also looks to see if there are any invoice holds that have not been released.  If so, it places the invoice in a "Needs Revalidation" status.

It's worth the time to review the Oracle Support note.  To make life easier for you, OnShore has provided a package function here that tries to replicate Oracle's behavior for invoice status.  This works great in discoverer or sql reports to give users summary totals by validation status.

Friday, April 8, 2011

Concurrent Manager Pending Too Long

Platform: Oracle EBS Release: Any

When submitting requests, the process waits in a Pending status for too long before changing to a Running status.

The concurrent managers are controlled by a number of factors, but one helpful setting is the "Sleep Seconds."

First, you must change the Conflict Resolution Manager, since it controls the higher level decisions to allow a new process to begin.

Navigate to System Administrator->Concurrent->Manager->Define.

Change the sleep seconds to something closer to 5 seconds.

Next, locate the concurrent manager that is processing your specific request.  Often this is the Standard manager.  Change the sleep seconds there as well.

Finally, bounce the concurrent manager to ensure the changes take effect.

Thursday, April 7, 2011

AP Invoice Conversion - GL Balance Concerns

Platform: Any

When converting invoice transactions, the total liability and distribution balances are already reflected in your GL historical balance conversion.  If you send these invoices through with their normal accounts, both the liability and expense accounts in the ledger will in effect be doubled.


You have two choices:
  1. Convert the invoice transactions with their original account information, and then reverse these balances back out after the conversion is complete.
  2. Use the same account number on the header (liability account) as the distributions (typically your expenses.)
Option #2 is preferable to the finance department because each invoice the converts will result in netting out the balance on the one account.  For example, account 100.110.3678.2 (assume liability account) would be used in these two fields:
These distributions will wash out the balance that was entered from the header import.  No fuss later from the finance group since their books will look the same before and after the load, assuming you loaded GL balances first.

Wednesday, April 6, 2011

GL Conversion - Intercompany Out of Balance

Platform: Oracle ERP
Version 11i, R12

When converting large amounts of history into Oracle, everyone understands that the total debits must equal the total credits or else the batches will be out of balance.

What can be tricky is when your converted journal entries balance, but are spread across multiple companies.  Maybe the legacy system that sources the data does not have a constraint preventing these entries.  In some cases it is desired behavior - that is, you have a need to balance across companies.  If that is true, then Oracle allows you to setup intercompany activity for those scenarios.  When transferring GL entries from a subledger, it may be necessary to have distributions that balance on a different company than the balance sheet account.

But what if you do not intend to turn on intercompany activity in GL, but are getting errors with Intercompany Activities Out of Balance?  Especially when you are not dealing with subledger activity, but rather a balance load from a retiring accounting system to Oracle.

When importing, Oracle takes each period in the gl_interface and creates a batch.  Within this batch, each company (or your segment used for balancing) is evaluated to see if debits match credits. 

Try running this sql, assuming you are using segment1 for balancing, and adjust the period for the one your posting process is complaining about.
select segment1, sum(entered_dr), sum(entered_cr)
from gl_interface gl
where gl.period_name like 'FY06-Dec%'
group by segment1

As an example, assume you see the following output:
Company      Debits       Credits
------  -----------   -----------
100     36103438.14   36102438.14
200       465581.00     465581.00
300      6336586.97    6337586.97
Here we can see that company 100 has an extra $1,000 in debits, and company 300 has an extra $1,000 in credits.  Assuming in our example that an attempt was made to charge expense accounts in a different company from where the cash was taken, this would explain the data.

At this point you have two choices:
  1. Setup intercompany in GL, or
  2. Apply a correcting journal entry to balance the companies.
If you do not wish to allow intercompany entries to occur (even from feeder subledgers like AR and AP), then choose #2.

The steps to correct the batch are:
  1. Identify the offending journal entries
  2. Create a reversing entry in the gl interface screen.
  3. Re-post the batch
It can be difficult to identify the problem journal entry.  In the example above, there is a $1,000 difference.  the best place to start is to search for all debit records in company 100 for $1,000.  If there are just a few, you may be able to spot the one that has a credit side balance on company 300. 

From the OnShore team,
Happy posting!

Thursday, March 17, 2011

Oracle ODI Remote Agent

Platform: Any
Version: 11g

For those that employ Oracle's Data Integrator product, there comes a time when you no longer wish to use the local client version of the agent. 

What is the remote agent?
The agent is the background process that executes the Project related package, interface, or procedure.

However, a local agent comes installed on all client desktops and is not optimal when it comes to performance.  When you run a process through your local agent, your client desktop becomes a gateway for query processing.  So rather then having data take a trip from server 1, to your machine, and off to server 2, it is best to install the remote agent.

The remote agent sits (should sit, it's not mandatory) on the same server where your repository is installed.  When the client requests a process to be executed in ODI, you specify the remote agent instead of the local agent.  Since the remote agent is much closer to the data and database then your desktop, the performance increases.  This is especially evident if you are VPN'd into work and do not want all that data taking a trip from the company server, back to your home office, and back to the server.

The latest version 11g (everyone should consider upgrading) has new methods for deploying remote agents.  You now have an option to utilize a web logic server if you have one up and running.  What we find is that for most cases, a standalone agent is easier to maintain and faster to get up and running.
The older 10g ODI was more forgiving in the java versions used on the server, but it's worth moving to 11g just for the updated designer screens.

Here are the steps, some of which aren't documented well in the docs.
  1. Copy from your local machine the 11g ODI foloders that were installed over to the same server that houses the ODI database repository.  I typically place these in the $ORACLE_HOME, new folder called odi.  Don't worry that you are taking files from a window's machine to a linux, if that is the case.   Java will work it all out.
  2. You need a compatible java run time, so download from Oracle, the latest version of JRockit.  Download from Oracle Here.  Place this in the same odi folder you created in step 1.
  3. Configure your (assuming you are on linux, otherwise use the .bat file).  This file runs as part of the agent start script, and it sets your environment variables, like your ODI_JAVA_HOME.  If you point your ODI_JAVA_HOME to a non JRockit JRE on your linux box, we have found that the agent script errors out with a "Java exception in main" type message.
  4. Also, be certain to setup in the odiparams file the connection info for the repository including the name and passwords.  You must run the script to encrypt your password.  The also uses the script, so if you have not set your ODI_JAVA_HOME, it will error out.
  5. It's a bit of a chicken and egg thing - you can't fully setup the odiparams script at first because you can't possibly know the encrypyted passwords to set.  So just set the java home, save the file, then run the encode for each password.  Go back into odiparams and paste in the new passwords and re-save.
  6. Now you are ready to run the remote agent.  At first you may want to try running it where it is persistantly listening at the prompt.  Type "." and see if it does not error due to java issues.
  7. Next, you need to kick it off in the background.  Exit out of the process on the command line - the listener will stop.  Type "nohup -PORT=20910 -NAME=Remote &"
  8. You must now go into the ODI Topology and configure the remote agent in two places
Physical Architecture

Make certain the server and port match where and how your remote agent is listening.

Logical Architecture
Next, right click and add new logical agent.  Set the context and physical agent accordingly.

It is best practice to install and use a remote agent with ODI.  As you get into scheduling, you really have little chocie but ot move off of the local agent.  you wouldn't want to be running daily batch jobs from your PC.  If the PC went down, the jobs would terminate.

From the OnShore team, have a great weekend.

Wednesday, March 16, 2011

AP Invoice Interface Holds

ERP: Oracle EBS
Version: All

Have you ever wanted to import invoices into AP and have specific holds to place on each depending on the scenario?  You may have also noticed that Oracle does not provide a field for a hold code or hold id. 

Consider grouping invoices with similar hold types and filling out the group_id column on the AP_INVOICES_INTERFACE table.

For example:
  • Invoices 123, 156, and 198 get assigned group_id "10"
  • Invoices 122, 144, and 165 get assigned group_id "20"
When importing these records through the standard concurrent program, you will need to do two imports.  Pick your source, group_id (no LOV list for this one), and pick the appropriate hold code and optional reason that corresponds with that group_id.

You could add the holds post-import through the AP_HOLDS_PKG.INSERT_SINGLE_HOLD API, but we find it's simpler to do it up front.  Especially if you are intent on using the open interface method in lieu of the Invoice APIs.

If you choose to utilize the grouping method, you may want to consider creating a concurrent request set that has one import job per hold, with the parameters preset for each group and hold code.  This will save you from the headaches later when someone forgets to run all the hold batches.

Happy importing!