Consolidation Requires a New COA Structure

Problem

An international financial services company had three instances of their Oracle E-Business Suites. One instance was for the European countries and the UK, another instance was for the US, and the third instance was for the Asia-Pacific region. In all, the three instances had approximately 30 modules of E-Business Suites. There were three different charts of accounts – one for each instance. They realized that the consolidation process would be much easier if the new instance had a common chart of accounts. With a common chart of accounts, all of the transactions from the sub ledgers would be able to be merged without writing translation scripts for each instance. They would also be able to write consistent interfaces to third-party systems from their single new instance. Finally, the performance for their new data warehouse would be significantly improved by not having to perform translation every time data was extracted from their E-Business Suite and loaded into the data warehouse.

Solution

The company designed a new global chart of accounts. They used ranges of values within the natural account segment to accommodate local statutory requirements. They finalized on the mappings from each of the three charts during their first test run. Before they went into production with their new chart of accounts, they used eprentise Consolidation software to merge their instances with the three separate charts of accounts. Then, in their target, consolidated instance, they changed each of the charts of accounts to the new global chart of accounts using FlexField software. They only had to create one set of interfaces to their third-party systems and greatly enhanced the performance on their OBIEE data warehouse.

Merge Ledgers or Sets of Books

Problem

A large system integrator put each line of business into a separate primary ledger in their E-Business Suite. Realizing that the same customer may use each of their ERP implementation services, their security services, and their data warehouse services, they decided to merge their ledgers. The chart of accounts, the currency, the calendar, and the accounting conventions were the same for all 9 of their ledgers.

Solution

The firm used eprentise Reorganization software’s MS Sets of Books module to merge their 9 ledgers into a single primary ledger. They created operating units for each of the lines of business to keep the transactions separate and to maintain controls within the organization. They used Multiple Organizations Access Control in R12 to enable cross organizational processing and reporting on operating units in their subledgers.

Move to a Single Global Chart of Accounts

Problem

A pharmaceutical company is upgrading to R12 in their Oracle E-Business Suite because of the capability to have multiple ledgers. Their current set up had a separate set of books for each of the European countries because they implemented before the Euro was the European standard currency. Since they originally implemented with seven separate sets of books for each of the countries where they did business in Europe, they didn’t see a need to standardize on a single chart of accounts. Now, in R 12, they can record all transactions in a single ledger because the currency is the same. However, in order to use a single primary ledger, they need to have a single chart of accounts.

Solution

The company created their new chart of accounts, and mapped each of the seven charts of accounts to the new chart of accounts. Using FlexField software, they completed the conversion process to their new chart of accounts with three test runs in 5 months. In two of the segments, they made the segments large enough to allow each country to have its own range of values. Using security and cross validation rules, they were able to restrict access to those values, giving each country some freedom in the way they controlled operations without limiting the company’s ability to operate globally with consistent data. As a side, they are able to close their books each month in 3 days instead of the 12 days that it took before the chart of accounts change. They estimate that they have reduced operating costs for their accounting team by approximately 35%.

Change Your EBS Calendar

Problem

A pharmaceutical company has been acquired. The old company used a fiscal 4-4-5 calendar (so period 1 ended on January 28) and the new parent company uses a monthly calendar (with period 1 ending on January 31).

Solution

Using eprentise Reorganization software’s Calendar Change module, the acquired company changed the start and end dates of each of their GL periods. eprentise created unposted journal entries to add all the transactions from January 29-31 to period 1 and subtract them from period 2. It did the same for all the periods in the year. After changing the start and end dates for the GL periods, they used eprentise to synchronize the Fixed Assets calendar, the project accounting calendar, and the inventory calendar to the GL calendar. Everything tied out to the penny with the new calendar.

Consolidate Multiple Production Instances of EBS Into a Single Instance

You want to go to a central data center and consolidate your Oracle E-Business Suites into a single instance.

Problem

A manufacturing company had 7 different implementations of Oracle E-Business Suite. Each application instance was configured at the plant level and had different product numbers structure, and different business processes that were implemented in their application. They could not consolidate their inventory into a single warehouse because of the different structures. The customer wanted to standardize all products and consolidate the databases into a single data center. They determined that they would save maintenance costs and license fees by having a single global instance, resolve business process inconsistencies, and save operating expenses when all the data resided in a single data center. The instances and the set-up decisions from their initial implementations were not well documented.

Solution

The company used eprentise Metadata Analysis to identify differences among the systems. After defining the target instance, the company used FlexField software to implement a single chart of accounts. Finally, the client used eprentise Consolidation software to identify and resolve configuration differences, other flexfield differences, and resolve duplicates across instances before merging all seven instances into the new target environment. They were able to quickly determine common customers, suppliers, and product lines so they were able to streamline their operations, understand their customers better, and leverage common business practices across the enterprise.

What can I do with eprentise software?

With eprentise software you can reorganize, split, divide, or consolidate OEBS instances, and clean up the data within an instance or across multiple instances.

• Reorganize the business structure within an instance. You can:
  • Move transactions from one set of books, operating unit, or legal entity to another.
  • Move, legal entities between SOBs.
  • Change accounting calendars.
  • Change currency
• Divide an Oracle application instance into two or more consistent and correct instances.
• Consolidate two or more application instances to take advantage of a single shared source of business data.
• Consolidate inventories within an instance. You can also change the valuation of inventories.
• Propagate data quality standards throughout one or more instances, including elimination of duplicate master data records.
• Change key flexfield (KFF) segments and values, such as Sales Territories or Asset Categories, and reflect the change in the existing transactional data.

In what kind of situations would I use eprentise?

How much does an eprentise project cost? How long do the projects take?

We suggest that our customers plan from one to six months for most projects, depending on the complexity. Consolidations are usually complex and will typically take between six months and a year. We welcome the chance to talk with you about the type of project you are contemplating. After a few discussions we will create a project profile which will include the estimates of the schedule, costs and required resources. It usually takes a few weeks of discussions and discovery for us to create and present a proposal.

Who needs to be involved with installing and running eprentise?

A DBA installs eprentise software and supervises the technical operations. The primary eprentise user is a senior business system analyst who runs the software.

A DBA with system privileges makes a clone of the database and installs eprentise software. No special skills or new techniques are needed. The install can be completed in about a half day. The DBA may also create eprentise users and projects so that the correct privileges are assigned. A DBA may also be required to monitor performance, and adjust tablespaces or rollback segments as the data is manipulated. After running Metadata Analysis and analyzing the results, it usually takes a few days from start to finish for each transformation run.

How will I know if the eprentise transformation works?

Your project management and users will develop a test plan to look at the “As is” state, the changes made, and the E-Business Suite instance after using eprentise. Our customers go through the usual change management testing processes, usually an abbreviated version. The testing will focus on account balances, individual transaction records, representative business flows, workflows, reports, interfaces, and customizations.

How do eprentise and FlexField software affect change management processes and systems for regulatory compliance (Sarbanes-Oxley)?

When your change management people become familiar with how eprentise works and what the business users will “ask” it to do, then they should be able to determine how to set up change requests and approvals, and monitor or verify completed changes. All changes made by eprentise are thoroughly documented. If eprentise creates a journal entry, the new journal entry includes the original journal and a description of why the journal was created. The metadata analysis and configuration analysis provide an accurate “before” picture of the environment. They create reports all the way through the transformation process that show which data was selected for change and what changes were made.

Show Me the Money - Reduce the Costs of Running ERP

Download PDF Download Excel
Reducing costs is the major strategic focus for most companies. An often-overlooked cost is the general operation of financial operations. This paper details a methodology for calculating the costs of running financial modules in ERP systems.

The costs are compared against both internal and external benchmarks. After calculating the costs, the paper shows how to reduce costs in two ways: first, by eliminating work that is duplicated across different business units or divisions, and second by determining which operations that are currently distributed across the organization can be consolidated into a shared services center. Together these changes, both to the organization and the ERP system, can generate significant cost savings.

The paper discusses how a $4 billion company using Oracle E-Business Suite (EBS) consolidated its distributed accounting departments, one for each European country, into a shared services organization supporting all European operations.  The cost savings realized and the streamlining of operations prepared for the organization for a major ERP software upgrade.

eprentise E-Business Suite Cost Savings Calculator

Download File
Reducing costs is the major strategic focus for most companies. An often-overlooked cost is the general operation of financial operations. Also see the white paper “Show Me the Money: Reduce the Costs of Running Oracle EBS Before Upgrading to R12”.

The Master Row Set: Managing Financial Statements

Building a master row set for your Financial Statement Generator (FSG) reports will save time, provide agility and consistency, and allow the business users the ability to quickly create new reports “on-the-fly”. In this article, we focus on how you can get the most out of reporting by employing a master row set in FSG. You’ll learn the secrets to keeping FSGs to a minimum and generating a variety of reports faster by focusing on the built-in reporting features of EBS. A master row set eliminates the requirement of rewriting each report in order to get the particular information you need at a certain time, allowing you to reduce the time spent creating custom reports from days to under an hour.

Download White Paper

FSG: Rows, row sets, columns, and column sets

Within the FSG, each financial statement is built around the fundamental concepts of a row and a column, which you will define and use to build your own financial reports. The major steps of the process are:

• Decide which rows and columns you need to display in your report.
• Define the rows, columns, and the attributes those rows and columns have.
• Build a report using those rows and columns.

Generally, accounts are assigned to row definitions and amount types (or period ranges) are assigned to column definitions. It is important that your chart of accounts has values that are organized in logical ranges for you to get the most out of FSG.

Here are some examples of row and column definitions for a typical income statement in FSG (the column definitions are independent of the row definitions):

A powerful feature of FSG is the ability to collect multiple row or column definitions into sets. For example, suppose that you’ve just defined a report that uses two columns with the attributes Year Ended 12/31/2010 and Year Ended 12/31/2011. If you want to use these columns for multiple reports besides the current report, you can define and save a column set that consists of these two column definitions. Then, whenever you need a new report based on these two columns, you tell FSG to build a report using these column set. You can do the same thing for row sets – groups of row definitions that you are likely to re-use in the future. Row sets are similar, but are collections of accounts types or calculations as opposed to amount types, as in column sets.

Row sets and column sets are the two primary building blocks of FSG reports. For our purposes, we’ll focus on row sets and extend the concept to create a master row set that will fulfill almost all of your potential row definition needs for creating reports, making it virtually unnecessary to create additional row definitions for reports once the master row set has been defined.

The master row set

A master row set is a super set of all the rows in all of your reports. Using a master row set enables you to use a top-down approach as opposed to a bottom-up approach when reporting with the FSG. Rather than defining new, customized row sets with the parameters specified for a particular report (bottom-up approach), you can start by creating an inventory of all of the requirements across all of your reports. That means that you will define a row set that contains the entire range of account values (as well as the entire range of segment values for each other segment in your chart of accounts) on the account assignment. For each row, determine all of the possible characteristics of that row. Let’s look at the following example:

In the first report, Balance Sheet – Corporate, Row 2 Subtitle may not be indented, but in the second report, Balance Sheet – North America, Row 2 Subtitle may be indented 3 spaces. In the first report, the detail under CURRENT ASSETS may have a subtotal, while in the second report, only the ASSETS are totaled. The master row set would have all of the different formatting, all of the permutations of totals or other calculations, and display characteristics from these two reports, and also from all of the rest of the reports in a single report. In other words, the attributes or characteristics of every row and every row set are included in a single super report. It represents a complete inventory of all of the row sets in all of your reports.

For all other reports, you will just need to copy this master row set and then delete all rows that are not wanted for each new report (top-down approach).

In How to Generate FSG Reports, Part I, Helene Abrams provides detailed instruction and examples of how to use your new master row to quickly create any report you might need:

1. Give each report component a unique name.
2. Assign descriptive names to the row set being copied such as “Balance Rows” or “Expense Rows”.
3. Note that the “Title” field is what shows up on the printed report. There is a single line for the title, and it will print in the center of the page underneath the “Set of Books Name”. The third title line of the report is the “Period” for which the report is run. The date and time of the report and a page number will print on the upper left corner of each page. Additional title lines can be typed into the “Column Set Heading” field.
4. Draw each report on an Excel spreadsheet. Note similar flexfield assignments, calculations, and totals for each report. Determine standard characteristics for the rows. For example, always group cash accounts together and report them as one total line. But list expense accounts individually on all reports.
5. Assign sequence numbers for each row to reflect the accounts in that row. Use the same number of digits as the value for the accounts in that row. The account number order is generally the order in which reporting is usually completed. Examples include:
   • Sequence numbers for all asset rows might start with a 1xxxx.
   • Rows with cash accounts might have a sequence number of 11xxx.
   • Rows with liability accounts might have a sequence number of 3xxxx.
6. Make sure the sequence number reflects the type of row.
   • Rows with sequence numbers ending in 0 or 00 should be header rows or subtitles.
   • Sequence number 11000 should be a header row with no account assignments or calculations and should be titled “Cash Assets”.
   • Rows ending in 99999 or 9999 should be totals or subtotals.
   • Sequence 11999 would be a subtotal of all cash asset rows.
   • Sequence 19999 would be a net of all asset rows.Parallel the names and numbers in the values for the account segment of the flexfield, and use the 9 as a parent or summary account. Have the value description read “Total Current Assets” or “Net Accounts Receivable”. Enter the value set descriptions in a form that does not require substantial retyping.
7. Autocopy completed row sets, and then make modifications for each of the other reports. Autocopy all report components (column sets, content sets, row orders, reports, and report sets).

1. Use Autocopy to make a copy of your master row set:
   
2. Delete rows that are not necessary in the report you are creating.
3. Create or use pre-defined column sets to define the amount types.

That’s it. You have a top-down procedure for creating any type of report in a matter of minutes, reducing the time-to-information and allowing management to get the enterprise information they need on a real-time basis.

Usability of the master row set

In order to streamline the effort required for developing your master row set, it is essential that your chart of accounts values be organized into logical ranges for each segment. Then, you can put in a range of values for each row, and when you want to display detailed rows, you display at the detail or summary level. If each segment is not in a logical range, the values that are “out of range” increase the number of rows that are necessary in order for a master row set to capture all account values, opposing the fundamental motivation of the concept of a master row set. It is common for growing companies to end up with values that are all over the place due to overcrowding of the range of values and there are no values left in a particular range that a new account (or other segment value) should logically fall in. A workaround is to create the new value anyway, anywhere that it will fit in the chart regardless of whether or not it should be there. Not only does this practice hinder the opportunity for using a master row set for FSG, it cramps previously designed cross-validation and security rules, requiring data stewardship in the form of frequent, time-consuming rules analysis and reformulation in order to prevent a total implosion of data governance. More work, not less.

For companies that are operating E-Business Suite with a chart of accounts that has fallen out of range, FlexField software from eprentise is the industry-standard solution to getting the chart back on track, transforming a master row set from a helpful tool into a golden key for obtaining real-time enterprise information. FlexField facilitates a chart of accounts change through re-mapping the disparate segment values in your current chart of accounts to a new chart of accounts with lengthened segments and new, logically ranged values. Learn more about how FlexField software enables you to capitalize on a master row set, to minimize the number and maintenance of cross-validation and security rules, and to provide your business with the opportunity to experience growth, and reduce the time and expenses required for your users to create on-demand reports. Click here to read more about the advantages of using FlexField software.

FlexField Datasheet

Download PDF
FlexField’s built-in knowledge base of Oracle E-Business Suite provides a low-risk, low-cost way to make rapid changes to the accounting flexfield. Businesses can change their Chart of Accounts and bring in all transaction history over a weekend while maintaining relational integrity. Users also can model potential business initiatives in order to gauge the financial impact ahead of time.

eprentise Product Datasheet

Download PDF
eprentise software copies, filters, changes, and merges data from one or more Oracle E-Business Suite (EBS) sources into a defined EBS target. An underlying eprentise engine (Metadata Analysis) analyzes the data structures, relationships, and other database objects. Once the Metadata Analysis is complete, rule templates are used to generate the code required to change, filter, merge, or copy source data to the desired target data. In the process, the source and target may be compared, standards may be applied, duplicates identified and resolved, and differences in business processes reconciled.