This paper is for decision makers who find the Chart of Accounts (COA) structure in Oracle E-Business Suite is holding back their business, understand there are inherent risks in changing the COA, and will nevertheless make the change if the risks can be eliminated.  The COA defines how accounting transactions, assets, and liabilities are processed and classified.  The COA is the framework for financial reporting and operational decision making.

The question is not whether the decision maker’s team can change the COA.  They can.  The prevalent available options include a commercial product and scripts written by consultants.  The decision is really a question of the risks and the results.

Without going into the technical details of the COA within the E-Business Suite, this paper asserts that at least one commercial product from an Oracle Partner can be used to change the COA with low risk, and that competing approaches based on custom in-house developers or consulting services have unacceptable risk, even if the costs appear lower.

COA and Accounting Flexfield in Oracle EBS

In the context of the Oracle E-Business Suite, it is customary to talk about the Accounting Flexfield, with multiple segments, as the Chart of Accounts.  In this paper, the COA refers to an individual Accounting Flexfield, including all the segments.  Each segment is constrained by an allowable set of values.  A code combination refers to the specific combination of segment values across the entire accounting flexfield.

Four Ways to Change COA

There are four primary approaches to changing the Chart of Accounts in Oracle E-Business Suite (EBS): 

• Start all over again by reimplementing Oracle EBS and migrating all the old EBS data.  This is what Oracle implicitly suggests.
• Create a new Set Of Books (11i) or Ledger (R12) with a new COA and use the financial consolidation function in Oracle to map one COA into another one for reporting.  This is for converting General Ledger (GL) balances only, and does not allow for the customary drill down from GL into the subledgers of Fixed Assets, Payables, Receivables, or Inventories.   It is a work around, in that you can create financial and operational reports at the GL level using the new COA, but the subledgers use the same fundamental COA within EBS.
• Create custom software programs, SQL code, or “loader scripts” to make a specific change for a specific chart of accounts to a new chart of accounts.  This is what Oracle means when they write: “Oracle STRONGLY RECOMMENDS that you never use SQL*Plus, Oracle Data Browser, database triggers, or any other tool to modify Oracle Applications data unless otherwise instructed” and warns that “Changing the existing flexfield structure after flexfield data has been created can cause serious data inconsistencies.”  In most cases the organization creates their own custom scripts or engages a consulting firm to do so.
• Use a commercial software product from an Oracle Partner that complements Oracle E-Business Suite.  

The methods entail radically different risks.  This paper only discusses the third and fourth approaches.  Note there is a continuum where custom programs, SQL code, or scripts may be passed off as commercial-grade software.

Changing The COA by Custom Programs or Scripts

This is usually a one-time project, for a single EBS customer, specific to a single EBS environment.   There may be a well-defined specific mapping scenario with a project deadline.  There may be a single source COA or set of COAs, and the objective is to map to a single target COA or set of COAs.

This approach relies on a developer/programmer writing SQL code that substitutes one new COA segment value or one new code combination for an existing one.  That programmer must also write code that updates all records in the database where the code combinations are used.

Sometimes a consultant or programmer will characterize bespoke programs or scripts, possibly written for a previous engagement, as a software product.

The first obvious symptom that custom code is being proposed is if the consultant can’t deliver the software (scripts or code) without knowing in advance the current and new COA values, structures, segments, code combinations, and the mappings from the current to new you would like to employ.  A second common symptom is an uncertain or variable price.  A third is the absence of a clear statement of specifications, what the software will and won’t do.  The specifications usually have gaps, inaccuracies, or misunderstandings about how the COA works in EBS and how the accounting and finance organization uses the COA.  There may be a services contract calling for the vendor to retain any intellectual property that results from the COA change code or scripts.  Finally, if the finance team decides to change the COA structure or mapping after the first test run, there may be delays so the code or scripts can be tailored to meet the new requirements.

Changing Accounting Flexfields with a Software Product

The software is designed to fill a well defined gap between Oracle’s EBS offering and many customers’ actual requirements, over an extended period and multiple EBS versions and releases.   There is an investment in a general purpose solution that allows for mapping any source COA to any target COA, or sets.

A commercially available, “off the shelf” (COTS) software product performs according to specifications “out of the box,” with “no assembly required.”  A software product uses the same code for all customers regardless of the mapping, the version of EBS, or the modules installed.  Although customers are sometimes unconcerned how a product is made and maintained, many know that successful software product companies use a formal development process with controlled releases, release notes, and formal testing in several different environments.

A general purpose product for changing the COA in Oracle offers a user interface oriented to accounting and finance professionals, rather than SQL coders.  The interface guides the users through the process of changing the COA, and allows for variations.  The product knows where and how to look within EBS to discover the current structure, segments, values, and code combinations on its own.  It provides many different mapping options (such as by segments, values, or code combinations, one-to-one, many-to-one, splitting a segment, or combining segments, and changing the length of segments).  It allows the user to define any mapping from any source to any target accounting flexfield, and then it executes the transformation.  The user can define the mapping, execute the transformation, and inspect and test the results multiple times.  There are built-in exception and error handling, audit trails, and full drill-down capabilities from anywhere in EBS modules and reports.  The most obvious indicators of commercial software products are a published price list, user manuals, a software product license, and numerous customers who have successfully used the same product in their production environment.

Changing an EBS Accounting Flexfield Involves More Than “Loading a Mapping Table”

There are reasons that Oracle does not provide a way for customers to change the accounting flexfield, and why they warn customers not to do it.  It is an extremely difficult process, and there is a risk of violating the data integrity of your E-Business Suite and corrupting the database.  As a result, using custom scripts and software calls for a tremendous amount of faith in the knowledge of the in-house developer or in the external consultants.  Using commercial software also calls for faith in the vendor.  You hope they know enough to complete the task.

Table 1:  Examination of Changing Chart of Accounts in EBS, Comparing Custom and Commercial Approaches

The table delineates some but not all of the challenges of using custom scripts and software to change an EBS accounting flexfield.  When we consider all of the requirements, using custom scripts and code is really no different than a development effort to create a software package to operate in a company’s production environment.  Potential problems include missed deadlines, budget overruns, overall quality and defect control issues, and project failure.  The technical change of the COA within EBS is part but not all of an enterprise project to change the COA for accounting classifications and for financial and operational reporting.  The price differential between different software products and custom scripts and software is likely a secondary decision factor.  Commercial software, by creating economies of scale, puts the costs that went into its research and development into a proven repeatable technology at a reasonable price.  Packaged software minimizes and controls risk; custom code and scripts invite risk.  Changing your chart of accounts is a critical undertaking. The risks of getting it wrong with custom code are substantial, and the cost of recovering from errors is greater still.

Conclusion

Our conclusion is based on the observations in the table above, knowledge of consulting projects that have reported changing the COA, and our experience with Oracle E-Business Suite customers.   Most decision makers will find that using commercial third party software to change the COA is a low risk approach, but that one-off projects by in-house staff or external consultants is high risk.  The risk of custom solutions is not consistent with most Oracle E‑Business Suite customers’ profiles given they prefer commercial over custom software and eliminate customizations as fast as possible when Oracle provides needed functionality “out of the box.”   Using commercial software to fix COA problems enables decision makers to fix a problem, lay a foundation for better reporting and decisions, eliminate uncertainty, and continue to deploy Oracle to support their business.

eprentise’s FlexField product is the first, and still the only commercially-available software for changing accounting flexfields in the Oracle EBS. With extensive research, experience with many different customers and requirements, and twelve years experience developing this technology, eprentise provides a risk-free, mature solution to change the Chart of Accounts in Oracle’s E-Business Suite.