As a part of Oracle BI Apps Financial Analytics 7.9.6.X Projects we have to do configurations around chart of accounts and account grouping. What I found is that the developers/designers really don’t have enough knowledge around the chart of accounts and group accounts etc. No one expect that to but I believe knowledge of key terms on source application is very important and useful for project delivery with value.
This blog is an attempt to share the knowledge around chart of accounts, importance of chart of accounts, components of chart of accounts , structure of chart of accounts etc. I hope with the text below which is kindly shared by an E-Business resource would answer many about segment, balancing segments and spare segments.
Chart of Account Overview
The Chart of Account Structure (also known as the ‘Accounting Flexfield’) is an underlying foundation component used by Oracle Applications to track transactions that have a financial impact. The Accounting Flexfield is used in Oracle sub-modules for recording and reporting of accounting information that ultimately reside in Oracle General Ledger.
Best Practices for Chart of Accounts
The following are the best practices should have considered while setting up the chart of account.. it is a candid assumption the BI Resource need to consider L
· Define an account structure large enough to reflect the important aspects of the organization, but small enough so that it is manageable and meaningful.
· Define an account structure that accommodates and properly classifies information from other financial information sources.
· Create an account structure that provides a logical ordering of values by grouping related accounts in the same range of values.
· Create an account structure that allows for expansion and development of new categories.
Chart of Accounts Components
The chart of account components are as follows
Accounting Flexfield
While defining the chart of account structure in the Accounting Flexfield it is important to create accounts that fit the specific needs of the organization, this includes determining Number of segments, Length of each segment, Name and order of each segment and purpose of the segment.
Segments
Identify segments that company might need in the future. Consider future expansion and possible changes in the organization and reporting needs. It is easier to build flexibility into the account structure through the use of ‘spare’ or unused segments during the initial setup and configuration of Oracle Applications rather than to try and change the account structure in the future.
Determine whether the current length of each segment is sufficient for anticipated growth in the number of values. Consider the structure of values plan to maintain within the segment.
Flexfield Qualifiers
Oracle Applications use Flexfield Qualifiers to identify certain segments used for specific application purposes. The following qualifiers are used when defining the Accounting Flexfield to identify which segment in the chart of account structure is a:-
· Cost Center Segment: Cost centers indicate functional areas of the organization, such as Accounting, Facilities and so on. Oracle Assets and Oracle Projects require to qualify a segment as a cost center segment in account.
· Natural Account Segment
· Balancing Segment: General Ledger uses the balancing segment to ensure that all journals balance for each value of the balancing segment. General Ledger also use the balancing segment to ensure that entries that impact more than one balancing segment use the appropriate intercompany or inter-fund accounting.
· Intercompany Segment
· Secondary Tracking Segment
Cross Validation Rules
Define cross–validation rules to control the valid combinations of values the use to create accounts. For example, one may create a cross validation rule that validates and only allows the creation of valid Cost Centers-Entity combinations.
Security Rules
Define security rules to restrict entry and query access for specific segment values or ranges of segment values by responsibility for accounts. Security rules restrict query access to segment values for Account inquiries, Funds Available inquiries, and Summary Account inquiries. When application restricts access, users cannot query any combination that contains a secure value.
Account Aliases
Define account shorthand aliases to speed entry of account segment values. If application enables shorthand alias flex-field entry when you define your account structure, then one can define aliases, or codes, which stand for complete or partial accounts.
Parent-Child Hierarchy
A parent value is a value that has one or more child values associated with it. A parent value can be assigned to a rollup group.
The parent-child relationships can be created by defining a range of child values that belong to a parent value. These parent-child relationships can be used for reporting and other application purposes.
A child value is a value that lies in a range of values belonging to a parent value. A child value can belong to more than one parent value. You create parent-child relationships by defining a range of child values that belong to a parent value.
The parent-child relationship is used to generate the segment hierarchies in BI Apps.
Rollup Groups
A rollup group is a collection of parent values. Only parent values can be assigned to a rollup group. A rollup group allows you to group related parent values for creating summary templates. Given a summary template, General Ledger creates summary balances using all parent values assigned to that rollup group.
Chart of Account Structure – An Example
To demonstrate purpose a three segment chart of account structure (Accounting Flexfield) is shown below.
The nominated segment separator is a dash ’-‘, in addition it is defined to allow dynamic insertion and cross validate segments. This allows the system to create all valid combinations subject to passing any cross validation rules that are defined by the organization in Oracle General Ledger.
For the above stated example, cross validation rules only exist to validate Cost Centre against the Entity/Fund. In all other cases, where cross validation rules are not defined between other segments (e.g. between Natural Account and Cost Centre), dynamic insertion will automatically allow the creation of combinations without validation against those segments.
Chart of Account Attribute
|
Natural Account
|
Cost Centre
|
Entity / Fund
|
Segment Name
|
Account
|
Cost Centre
|
Entity
|
Value Set Name
|
XX_GL_ACCOUNT
|
XX_GL_COST_CENTRE
|
XX_GL_ENTITY
|
Column
|
SEGMENT1
|
SEGMENT2
|
SEGMENT3
|
Max Length
|
6
|
6
|
4
|
Format Type
|
Char
|
Char
|
Char
|
Enabled
|
Yes
|
Yes
|
Yes
|
Displayed
|
Yes
|
Yes
|
Yes
|
Required / Optional
|
Required
|
Required
|
Required
|
Security Enabled
|
No
|
No
|
No
|
Security Type
|
No Security
|
No Security
|
No Security
|
Numbers Only (0-9)
|
No
|
No
|
No
|
Uppercase Only (A-Z)
|
No
|
No
|
No
|
Right Justify and Zero Fill
|
No
|
No
|
No
|
Default Value
|
N/A
|
N/A
|
N/A
|
Value Validation
|
Independant
|
Independent
|
Independant
|
Flexfield Qualifier
|
Natural Account Segment
|
Cost Centre Segment
|
Balancing Segment
|
As I stated earlier the balancing segment is Entity/Fund, the accounting flex field does not contains Intercompany Segment flex field qualifier, the Balancing Rules are configured which allows the system to automatically balance journals by the Entity/Fund.
The table below summarises the Oracle General Ledger features and how it is being used in the mentioned example.
Cross Validation Rules – A single validation rule is defined to validate the Cost Centre segment against the Entity segment. Each entity has as assigned range of cost centre values
Account Aliases- Account Aliases representing the cost centre value have been defined to automatically link the Cost Centre to an Entity. This is possible because each Entity has an assigned range of Cost Centre values.
Parent-Child Hierarchy - Parent-Child Hierarchies are currently established for the Natural Account segment. They are also defined for Entity and Cost Centre segments for some Area Health Services
Financial Statement Generator (FSG) Report Definitions - Various reports and formats exist for the various Area Health Services. Not all Area Health Services use FSG reports for financial reporting.
Account Code Combinations - Account Code combinations are dynamically inserted by Oracle General Ledger.
Advanced Considerations for Chart of Accounts
Spare Segment
GL General Ledger can define up to 30 segments for organization account structure; but it would not be practical or manageable to maintain so many segments. The general recommendation is that there should be 1-2 additional spare segments be defined in the Accounting Flexfield structure, which in turn allows some flexibility for future requirements.
The account structure using two spare segments is shown below. The configuration is done to chart to automatically pre-fill a default value (such as zeros or other nominated value) into each spare segment.
Intercompany Segment
A separate Intercompany segment will enable organization to identify both sides of the inter company account movements from both the debit and credit perspectives. One can continue to maintain and tack the separate natural account codes to identify inter-entity transaction by the type of transactions.
The benefits of having an inter entity segment include:-
1. The easier identification and reconciliation of intra health cash and non cash movements.
2. Enable the easier production of cash requirement reports by entity. If such reports are needed.
The intercompany segment is optional and is not required in order to do intercompany balancing. If used, the intercompany segment can be any segment other than the natural account or balancing segments. If a separate segment is used, the segment values in the intercompany segment should be a super set of the values in the balancing segment if you want to use the intercompany balancing features provided by GL.
Base Setup for Segments for E-Business Financial Analytics
The GL account segment configuration is required to be done by customer, this can be done by modifying file file_glacct_segment_config_ora11i.csv, the only other file which requires to be configured is file_group_acct_codes_ora.csv.
In order to configure this file, one need to map Key Flex Fields used in the Chart of Accounts Segments to BI Apps Segment fields and also identify which segments one would wish to include in the main GL Balance Aggregate table.
In short update following columns in file_glacct_segment_config_ora11i.csv with appropriate values.
Script to get the information required such as chart of account id, segment name, column name and flex value set name and id.
SELECT sob.name sob_name,
sob.set_of_books_id sob_id,
sob.chart_of_accounts_id coa_id,
fifst.id_flex_structure_name struct_name,
ifs.segment_name,
ifs.application_column_name column_name,
sav1.attribute_value balancing,
sav2.attribute_value cost_center,
sav3.attribute_value natural_account,
sav4.attribute_value intercompany,
sav5.attribute_value secondary_tracking,
sav6.attribute_value GLOBAL,
ffvs.flex_value_set_name,
ffvs.flex_value_set_id
FROM fnd_id_flex_structures fifs,
fnd_id_flex_structures_tl fifst,
fnd_segment_attribute_values sav1,
fnd_segment_attribute_values sav2,
fnd_segment_attribute_values sav3,
fnd_segment_attribute_values sav4,
fnd_segment_attribute_values sav5,
fnd_segment_attribute_values sav6,
fnd_id_flex_segments ifs,
fnd_flex_value_sets ffvs,
gl_sets_of_books sob
WHERE 1 = 1
AND fifs.id_flex_code = 'GL#'
AND fifs.application_id = fifst.application_id
AND fifs.id_flex_code = fifst.id_flex_code
AND fifs.id_flex_num = fifst.id_flex_num
AND fifs.application_id = ifs.application_id
AND fifs.id_flex_code = ifs.id_flex_code
AND fifs.id_flex_num = ifs.id_flex_num
AND sav1.application_id = ifs.application_id
AND sav1.id_flex_code = ifs.id_flex_code
AND sav1.id_flex_num = ifs.id_flex_num
AND sav1.application_column_name = ifs.application_column_name
AND sav2.application_id = ifs.application_id
AND sav2.id_flex_code = ifs.id_flex_code
AND sav2.id_flex_num = ifs.id_flex_num
AND sav2.application_column_name = ifs.application_column_name
AND sav3.application_id = ifs.application_id
AND sav3.id_flex_code = ifs.id_flex_code
AND sav3.id_flex_num = ifs.id_flex_num
AND sav3.application_column_name = ifs.application_column_name
AND sav4.application_id = ifs.application_id
AND sav4.id_flex_code = ifs.id_flex_code
AND sav4.id_flex_num = ifs.id_flex_num
AND sav4.application_column_name = ifs.application_column_name
AND sav5.application_id = ifs.application_id
AND sav5.id_flex_code = ifs.id_flex_code
AND sav5.id_flex_num = ifs.id_flex_num
AND sav5.application_column_name = ifs.application_column_name
AND sav6.application_id = ifs.application_id
AND sav6.id_flex_code = ifs.id_flex_code
AND sav6.id_flex_num = ifs.id_flex_num
AND sav6.application_column_name = ifs.application_column_name
AND sav1.segment_attribute_type = 'GL_BALANCING'
AND sav2.segment_attribute_type = 'FA_COST_CTR'
AND sav3.segment_attribute_type = 'GL_ACCOUNT'
AND sav4.segment_attribute_type = 'GL_INTERCOMPANY'
AND sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
AND sav6.segment_attribute_type = 'GL_GLOBAL'
AND ifs.id_flex_num = sob.chart_of_accounts_id
AND ifs.flex_value_set_id = ffvs.flex_value_set_id -- comment the next expression to show all books
-- currently it show the info for the site level set profile option value
-- and sob.set_of_books_id = nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.set_of_books_id)
ORDER BY sob.name,
sob.chart_of_accounts_id,
ifs.application_column_name;
Inference
I hope this piece of work is useful to understand the basic concept around the segments and how it is relate back to E-Business application. It might be useful tool to crossover the bridges from BI Apps to source system for effective conversation with our source system resources.
No comments:
Post a Comment