BIApps 7.9.6.x – Configuration for COA (Chart of Accounts)

Here we will discuss howto configure Chart of accounts and Account grouping In BI Apps Financial Analytics 7.9.6.x

What is Chart of Account – The Chart of Account Structure (also known as the ‘Accounting Flexfield’) is an underlying foundation component used by Oracle Apps 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 General Ledger.

Base Setup for Segments for E-Business Financial Analytics: The GL account segment configuration is required to be done by customer, this is 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 required columns in file_glacct_segment_config_ora11i.csv with appropriate values. Below is the 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.set_of_books_id sob_id,
 sob.chart_of_accounts_id coa_id,
 fifst.id_flex_structure_name struct_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,
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.chart_of_accounts_id, ifs.application_column_name;