Group Account Number Configuration is an important step in the configuration of Financial Analytics, because it determines the accuracy of the majority of metrics in the General Ledger and Profitability module. Group Accounts in combination with Financial Statement Item Codes are also leveraged in the GL reconciliation process, to ensure that subledger data reconciles with GL journal entries.
You set up General Ledger accounts using the following configuration file:
file_group_acct_codes_ora.csv
— this file maps General Ledger accounts to group account codes.
The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:
Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\
.
Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>
.
Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.
You can categorize your Oracle General Ledger accounts into specific group account numbers. The group account number is used during data extraction as well as front-end reporting. The GROUP_ACCT_NUM field in the GL Account dimension table W_GL_ACCOUNT_D denotes the nature of the General Ledger accounts (for example, cash account, payroll account). For a list of the Group Account Number domain values, see Oracle Business Analytics Warehouse Data Model Reference. The mappings to General Ledger Accounts Numbers are important for both Profitability analysis and General Ledger analysis (for example, Balance Sheets, Profit and Loss, Cash Flow statements).
The logic for assigning the group accounts is located in file_group_acct_codes_ora.csv
. This table shows an example configuration of the file_group_acct_codes_ora.csv
file.
CHART OF ACCOUNTS ID | FROM ACCT | TO ACCT | GROUP_ACCT_NUM |
---|---|---|---|
1 |
101010 |
101099 |
CA |
1 |
131010 |
131939 |
FG INV |
1 |
152121 |
152401 |
RM INV |
1 |
171101 |
171901 |
WIP INV |
1 |
173001 |
173001 |
PPE |
1 |
240100 |
240120 |
ACC DEPCN |
1 |
261000 |
261100 |
INT EXP |
1 |
181011 |
181918 |
CASH |
1 |
251100 |
251120 |
ST BORR |
In the first row, all accounts within the account number range from 101010 to 101099 that have a Chart of Account (COA) ID equal to 1 are assigned to Current Asset (that is, CA). Each row maps all accounts within the specified account number range and within the given chart of account ID.
If you need to create a new group of account numbers, you can create new rows in Oracle BI Applications Configuration Manager. You can then assign GL accounts to the new group of account numbers in the file_group_acct_codes_ora.csv
file.
You must also add a new row in Oracle BI Applications Configuration Manager to map Financial Statement Item codes to the respective Base Table Facts. This table shows the Financial Statement Item codes to which Group Account Numbers must map, and their associated base fact tables.
Financial Statement Item Codes | Base Fact Tables |
---|---|
AP |
AP base fact (W_AP_XACT_F) |
AR |
AR base fact (W_AR_XACT_F) |
COGS |
Cost of Goods Sold base fact (W_GL_COGS_F) |
REVENUE |
Revenue base fact (W_GL_REVN_F) |
TAX |
Tax base fact (W_TAX_XACT_F)
Note: E-Business Suite adapters for Financial Analytics do not support the Tax base fact (W_TAX_XACT_F). |
OTHERS |
GL Journal base fact (W_GL_OTHER_F) |
By mapping your GL accounts against the group account numbers and then associating the group account number to a Financial Statement Item code, you have indirectly associated the GL account numbers to Financial Statement Item codes as well. This association is important to perform GL reconciliation and to ensure the subledger data reconciles with GL journal entries. It is possible that after an invoice has been transferred to GL, a GL user might adjust that invoice in GL. In this scenario, it is important to ensure that the adjustment amount is reflected in the subledger base fact as well as balance tables. To determine such subledger transactions in GL, the reconciliation process uses Financial Statement Item codes.
Financial Statement Item codes are internal codes used by the ETL process to process the GL journal records during the GL reconciliation process against the subledgers. When the ETL process reconciles a GL journal record, it looks at the Financial Statement Item code associated with the GL account that the journal is charging against, and then uses the value of the Financial Statement item code to decide which base fact the GL journal should reconcile against. For example, when processing a GL journal that charges to a GL account which is associate to 'AP' Financial Statement Item code, then the ETL process will try to go against AP base fact table (W_AP_XACT_F), and try to locate the corresponding matching AP accounting entry. If that GL account is associated with the 'REVENUE' Financial Statement Item code, then the ETL program will try to go against the Revenue base fact table (W_GL_REVN_F), and try to locate the corresponding matching Revenue accounting entry.
Tasks
A description of how to map Oracle General Ledger Accounts to Group Account Numbers - see Mapping Oracle GL Account Numbers to Group Account Numbers.
A description of how to add Group Account Number metrics to the Oracle BI Repository - see Adding a New Metric in the Logical Table Fact – Fins – GL Journals Posted.
Note:
It is critical that the GL account numbers are mapped to the group account numbers (or domain values) because the metrics in the GL reporting layer use these values.
You can map Oracle General Ledger Account Numbers to Group Account Numbers.
If you add new Group Account Numbers to the file_group_acct_codes_<source system type>.csv
file, you must also add metrics to the BI metadata repository (that is, the RPD file).
If you add new Group Account Numbers to the file_group_acct_codes_<source system type>.csv
file, use the Oracle BI EE Administration Tool to add metrics to the Oracle BI repository to expose the new Group Account Numbers.
This example assumes that you have a new Group Account Number named Payroll (Domain member code 'PAYROLL'), and you want to add a new metric to the Presentation layer called 'Payroll Expense'.