A day in the life of an Oracle Applications Consultant

General Ledger

Friday, January 21, 2005

FSG Transfer Program

The other day I came across this feature in Financial Statement Generator which I never knew existed and found to be quite useful.

FSG Transfer is a programme that you can run in General Ledger which gives you the ability to copy Financial Statement Generator (FSG) components from one environment to another. For example, you might want to transfer your FSG’s from a TEST environment to your PRODUCTION environment.

According to the Oracle General Ledger User Guide, before this report can be run, the following prerequisites should have been implemented: -

  • You or your System Administrator must define database links.
  • The Chart of Accounts in your source database must be identical to the Chart of Accounts in your target database.
  • Any currencies and sets of books referred to by the row sets and column sets being copied must exist in the target database.
  • Report details, such as budgets and encumbrance types, referred to by copied reports must exist in the target database.
  • You must be logged in to General Ledger and connected to the target database.

Programme Parameters

To run the report, from the file menu in General Ledger Responsibility select View > Requests and choose to run a single request entitled Program - FSG Transfer. Examples of how the parameters for this programme can be filled are as follows: -

An example of how you might fill out the FSG programme transfer parameters

Component Type

The following elements of an FSG can be copied between environments: -

  • Column Sets
  • Content Sets
  • Display Groups
  • Display Sets
  • Reports
  • Report Sets
  • Row Orders
  • Row Sets
  • All of the above

Component Name

The name of the component selected above should be entered if you are copying a single component mentioned above. If you are copying all components you do not need to specify a name.

Source DB Chart of Accounts

The exact name of the Chart of Accounts from which you want to copy report objects.

Target DB Chart of Accounts

The exact name of the Chart of Accounts to which you want to copy report objects.


The name of the source database from which you are copying. This field will not have a List of Values (LOV) unless you have defined database links. To define database links within the General Ledger Responsibility go to Setup > System > Database Links and create a New Database Link (if you have any uncertainty about what parameters to enter here contact your DBA, typically you should be able to extract the connect string from the TNSNAMES.ORA file).

Thursday, July 15, 2004

Business need for Chart of Accounts - An Overview

The Chart of Accounts (COA) is the account structure the organization uses to record transactions and maintain financial account balances. Oracle General Ledger defines the COA structure in the Accounting Flexfield. The structure enables the organization to categorize accounting information during the recording process. The structure is comprised of multiple uniquely defined segments. Each segment contains a list of values, such as the list of Cost Centers or Natural Accounts. The various combinations of the segment values represent the unique account combination to which accounting transactions are posted and account balances are maintained.

When defining the Accounting Flexfield (COA) segments, Oracle requires one segment be designated as the balancing segment and one segment be designated the account segment. The balancing segment identifies an entity requiring a self-balancing trial balance, such as Company. The Account segment identifies the segment used to produce the Financial Statements such as Cash, Accounts Payable, or Revenue. Additionally, Oracle allows the designation of a cost center segment. The cost center segment identifies functional areas of the business such as Finance and Marketing. The cost center segment value is primarily used for reporting in Oracle Assets or Projects.

There are several constraints that should be adhered to when defining the organization’s COA in Oracle

  • COA structure must contain at least 2 segments (Balancing and Natural Account) and no more than 30 segments
  • Total length of segment combinations cannot exceed 240 characters
  • Each Natural Account value must have only one Account Type (e.g. Expense, Asset, etc…)

Benefits of Common Chart of Account Structure

Some of the benefits of using a Common Chart of Accounts are the following: -

  • Drives consistency of reported information across business units and ensures compatibility
  • Reduces the effort to consolidate information to satisfy management requests
  • Reduces reconciliation procedures
  • Provides easier benchmarking between different business units/territories
  • Allows ability to leverage staff between different business units
  • Reduces learning curve due to commonality
  • Provides a framework to introduce financial shared services

Chart of Accounts Impact on Reporting

The primary purpose of the General Ledger is financial reporting and financial analysis. The Chart of Account structure defines the nature, ranges, and groupings of information available for reporting and inquiry. Reporting is generated by ranges and groupings of values for one or more segments. Management must define the dimensions by which financial data will be analyzed and reported and ensure those dimensions are reflected in the segments contained within the COA structure.

Chart of Accounts Best Practices and Development Guidelines


  • Determine the scope that the chart of accounts must support. The scope should begin with GAAP reporting requirements followed by management reporting requirements. Common examples of Management reporting requirements are Geographic Regions, Product Line reporting, Activities, and Cost Centers.
  • Team members in the chart of accounts design process should be functionally aligned as opposed to geographically aligned. This facilitates the aim of developing a standard COA across global boundaries.
  • Design a flexible chart of accounts that will reflect current business processes and accommodate organizational changes in the future. Consider future segments if involved in a high-growth, dynamic industry or environment.
  • Each measured dimension of the business should be a separate segment. Segments used for more than one dimension limit the use of standard default values and complicates reporting by making data difficult to isolate. In addition, it precludes the user from using more than one dimension in an individual transaction. This also complicates the processing of consolidations and allocations, validation/security rules, and reporting.
  • The resulting COA structure should be more horizontal in design with reporting across segments instead of using individual values for multiple dimensions.


  • Limit detail in values and report on information in the appropriate source (subledger) system unless the data is scattered among multiple systems. (e.g. create the minimum number of accounts for GAAP reporting of PP&E.  Create Asset Categories in Oracle Assets that “roll-up” to the respective natural accounts and report detail out of the Assets subledger)
  • Product segments should be carefully considered for inclusion in the Accounting Flexfield Structure if there are extensive product lines. Try to identify major product lines for meaningful reporting in the General Ledger and look to the relevant subledger for detail reporting.
  • Project segments may be considered if not using Project Accounting. Project Segments are not recommended if Project Accounting is to be used. All project reporting should be generated from the Projects subsystem.
  • Carefully consider usage of summary accounts to capture information. Balances are stored at both the detail and summary levels and can negatively impact some concurrent processes. Summary Accounts may significantly improve FSG reporting.
  • Avoid intelligent numbering (Assigning a meaning to every digit of a segment value). This complicates allocations and reporting.
  • Avoid using dependent segments.  Allocations work off independent segments and may not function properly with dependent segments.
  • Define segments as Character Type
  • Avoid alpha characters in segments, except for parent values. Alpha Characters complicate sorting data, using ranges, assigning codes, and creation of validation/security rules.
  • Use all capital letters for data entry.  This promotes consistency and simplifies query retrievals.
  • Use parent/child relationships to facilitate reporting versus creating additional segments values.
  • Assign segments in the most logical data entry order. Default segments should be at the very beginning or end of the accounting flexfield structure.
  • Verify that you can perform complex allocation schemes.
  • Document mappings to legacy chart of accounts. This will assist in populating conversion tables and in reconciling discrepancies.

Saturday, July 10, 2004

Automating the creation of account code combinations (CCID’s) in Oracle General Ledger

This post outlines two methods one can use to automate the creation of account code combinations in General Ledger

In certain instances it may be necessary to manually create account code combinations (CCID’s) in Oracle General Ledger. To some extent this is determined by whether your organization has decided to use dynamic insert or not. Dynamic insert is an option you can turn on or off when creating your accounting flexfield. If the option is turned on it will allow account code combinations to be automatically generated. If dynamic insert is turned off then it will certainly be necessary for a General Ledger Super User to go and create an approved set of combinations for use within your organization. Even if dynamic insert is turned on you may find that at the beginning of an implementation that there are not enough active account combinations. This means you will more than likely have to manually add account code combinations as presently Oracle has no automated facility to perform this. To manually add account code combinations one should login under General Ledger Super User and on the navigation menu go to Setup > Account > Combinations. Having navigated to the form displayed in Fig. 1 you can then insert the needed lines.

Figure 1: The Account Code Combinations Form Maintain General Ledger Account Code Combinations (CCID's) using this Oracle Form As manually adding account code combinations can be a rather tiresome job I investigated potential ways to automate the process and have come up with the following means of doing so: -

1. Journal Load via ADI

Using this option entails creating an excel spreadsheet journal via ADI with the account code combinations that you would like insert. The account code combinations should contain zero values. After uploading the spreadsheet journal from ADI an unposted journal will be created in General Ledger. The unposted journal can be deleted and the account code combinations that you created will remain behind. The reason for using the spreadsheet journal as opposed to using a journal in General Ledger is that the spreadsheet makes it easier to create the account code combinations you want to load as you can use the copy and paste features.

2. Use of DataLoad

A dataload template can be created and the account code combinations can be loaded directly into General Ledger.

Another tip

Personally I prefer to use the first method mentioned as it is much faster than using Dataload. Another word of advice when creating these combinations. One should ensure that you have correctly filled in the flexfield qualifiers (displayed in Fig. 2) when capturing your segment values as the account code combinations will use this information to determine how a CCID is created.

Figure 2: The Flexfield Segment Qualifiers

The Flexfield Segment Qualifiers should be correctly chosen to ensure proper creation of a CCID

For example, if for any of the accounts in the combination of segment values, you have allow budgeting and allow posting set to “no”, then the account code combinations as displayed in Figure 1 will be created with allow budgeting and allow posting set to “no”. If you create an account code combination with the incorrect value for allow budgeting and allow posting, you can go and manually change the value. However, if you have a large number of lines that have the incorrect values you cannot merely go and upload another blank journal after having corrected the mistake in the flexfield segment qualifiers. You will still have to manually make the amendments as once an account code combination has been created you cannot “overwrite” it.

Lastly, if anyone has come up with any other better method or an automated procedure for performing this activity then please add your comments.

Saturday, June 26, 2004

Implementing a major re-organization

This post contains information about guidelines for implementing a major re-organization.

I found this presentation on the New England Oracle Applications User Group site. The presentation was prepared by Kiran Mundy for OAUG spring 2004 conference.The abstract of the presentation reads “A Company Reorganization changed our company structure from a simple geographical hierarchy to a matrix organization where service organizations were organized by technical specialty and the client sector organizations were organized by Customer Category. Projects would be owned by the Client Sector Orgs and employees by the service orgs. Management needed to be able to view division P&L’s by either the service or Client Sector organization. This paper describes our solution and how we implemented the re-org in 3 months at year-end” I had a look at the presentation and thought that it might be useful for anyone implementing a major change in the organization structure.

Page 2 of 2 pages  < 1 2