Wednesday, February 19, 2014

GL Chart of Accounts – Number of Segments -- R12

How many segments in my Chart of Accounts (COA) are needed for my business?
There are many considerations and this article will only focus on the number of segments rather than the segment values which will be handled in a later post.
Some of the considerations are:
  • Oracle System requirements
  • Legal reporting requirements
  • Financial management reporting needs
  • Globalisation needs (some countries have special requirements)
  • Consolidation needs (some segments may have to be used globally)
  • Security needs

Oracle System Requirements

Oracle E-Business suite requires the following segments:
  • Balancing
  • Natural Account
  • Cost Center (for Fixed Assets)
Optionally you can also have:
  • Intercompany
  • Management (for security)
  • Secondary Tracking (for extra year-end tracking)
Usage details can be found in the General Ledger Implementation Guide.
All other requirements vary so below is a few do’s and don’ts:

Do’s

The User Guide details the Oracle requirements for a segment however in addition to that I recommend this:
  • Keep it simple – your users will have to type it in a lot of times
  • Focus on financial reporting requirements and needs
  • Identify segments likely to be consolidated and make global usage rules for these
  • Have same number of segments globally (to ease documentation and customisations)
  • Have same segment names globally (to ease documentation and customisations)

Don’ts

Could be a very long list but I’ll keep it to the typical errors I have seen in implementations:
  • Have too many segments (impacts usability and performance)
  • Use of subledger reporting segments like:
    • Product or Item (sales reporting in the GL)
    • Customer (sales reporting in the GL)
    • Project (project accounting in the GL)
  • Dual purpose segments (like one segment for both product and department)
  • Think you can change the COA after go-live: you cannot
Subledger reporting segments are often driven by the requirement for global reporting on subledger data where the use of the GL and consolidations could provide this.
However implications are huge ranging from simple performance problems to feeble attempts to maintain a customer hierarchy in the GL.
Keep in mind the GL is for financial reporting and not for subledger reporting.
By putting subledger reporting in the GL you will also implicit impose limitations of the GL on your subledger which can cause unintended problems like need for custom segment validation and worst case will limit your business as your subledger reporting needs cannot be accomplished in the GL.
In very rare cases combined purpose segments can be justified but the values must be mutual exclusive as any overlapping will cause problems and is in fact a need for an additional segment.

An Example

Most mid to large scale business could have a structure like this:
  • Company (Balancing)
  • Department (Cost Center)
  • Account (Natural Account)
  • Analysis 1
  • Analysis 2
  • Intercompany (same segment values as Company)
Company, Department and Account segments are self explanatory.
Company and Account is for legal reporting.
Account, Department, Analysis 1 and 2 is used to support management reporting requirements.
Note there is a dual purpose for the Account segment in both legal and management reporting and sometimes this is remedied by adding a separate sub-account segment for the sole purpose of management reporting.
The intercompany segment is used for tracking the source of an intercompany transaction.
Keep the analysis segments to a finite number of values so if any subledger information is to be contained within these use summary levels like product line, customer group and similar as these values are unlikely to change over time.

Technical and performance

Two tables are the focus for performance:
  • GL_CODE_COMBINATIONS – stores unique segment values as a single unique value combination id: CCID
  • GL_BALANCES – stores summarised GL journals for each CCID

GL_CODE_COMBINATIONS

The content of this table expands for every new segment value combination entered per COA:
image
So looking at the example above – if we have the following segments and number of segment values:
  • Company = 10
  • Department = 25
  • Account = P/L 1500 and B/S 500 = 2000
  • Analysis 1 = 20
  • Analysis 2 = 20
  • Intercompany = 10 We can have a worst case number of combinations: 10 x 25 x 2000 x 20 x 20 x 10 = 2,000,000,000
    So 2 billion rows in this table doesn’t sound good?
    The try to imagine if you use subledger segments using all of your world-wide customers, products or projects?
    The above number is not that bad as Department and Analysis segments would normally only be used with P/L accounts and intercompany would apply to very few specific accounts.
    So a more precise estimate is:
    Combinations = P/L combinations + B/S combinations = 10 x 25 x 1500 x 20 x 20 x 1 + 10 x 1 x 500 x 1 x 1 x 1 = 150,000,000 + 5,000 = 150,005,000
    A lot less but still significant so performance is a very important consideration when creating your COA.

    GL_BALANCES

    The content of this table expands per:
    • Set of Books
    • Code Combination (CCID)
    • Currency
    • Period
    • Balance Type (Actual, Budget or Encumbrance)
    For each month opened all rows from the previous month are duplicated – so the more CCID that are used the more will be carried forward.
    So for the above example if we say each of the companies have one set of books and one currency each and they have 12 periods per year. So we will disregard from any budgets and encumbrances as these are normally for a limited number of accounts at a summary level.
    New rows per year = 1 x 150,005,000 x 1 x 12 x 1 = 1,800,060,000
    So for a large business intending to keep financial data on-line for a few years the number of code combinations have a huge impact.
  • No comments:

    Post a Comment