Group Account Number Configuration is an important step in the configuration of Financial Analytics, as 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 can categorize your General Ledger accounts into specific group account numbers. The GROUP_ACCT_NUM field denotes the nature of the General Ledger accounts.
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>.
Note:
If you are deploying OTBI-Enterprise, then you must download the CSV files from Configuration Manager, as follows:From the Tasks bar, click Define Business Intelligence Applications.
Click Actions, then Manage Source Configuration Data Files.
Click the Export Source Configuration Data Files option on the tool bar.
Move the required files to the right hand list, then click Export.
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.
file_group_acct_codes_jde.csv
— this file maps General Ledger accounts to group account codes.
The associations in this file are used in conjunction with the values defined for the following Domains:
W_GL_GROUP_ACCOUNT
W_GL_ACCT_CATEGORY
W_FIN_STMT
These Domain values and the mappings between them classify accounts into sub-groups, like Revenue and Cost of Goods Sold, as well as dividing accounts between Balance Sheet and Profit and Loss. Before you load your data, you must ensure that the account values are mapped consistently across these three collections. In particular, the GROUP_ACCOUNT_NUM domain that is specified in Configuration Manager must contain valid members of the W_GL_GROUP_ACCOUNT Domain. Those values, in turn, are mapped to members of the W_GL_ACCT_CATEGORY and W_FIN_STMT Domains.
You can categorize the General Ledger accounts in Oracle's JD Edwards EnterpriseOne 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, AR account, Long Term Debt account Payroll account).
The mappings to General Ledger Accounts Numbers are important for both Profitability analysis and General Ledger analysis (for example, Balance Sheets).
Using the file_group_account_codes_jde.csv, you can specify which group account (among the available group accounts) the object account is associated with. The Company column in this CSV file is the actual company the object account belongs to.
In addition to the From Account and To Account range, the system uses the incoming company as a parameter for the association. If the incoming company has not been configured in the group account flat file, the system inserts 00000 as the default value for Company for lookups. You can choose to not configure group accounts for any company other than 00000 if you are using a single global chart of accounts. However, if you configure group accounts for additional companies, you must configure all possible From Account and To Account ranges for these companies. In addition, you must always configure the entire range of accounts for company 00000.
The table shows example values specified in file_group_account_codes_jde.csv
.
COMPANY | FROM ACCT | TO ACCT | GROUP_ACCT_NUM |
---|---|---|---|
00000 |
4100 |
4190 |
AP |
00000 |
1200 |
1299 |
AR |
00000 |
2120 |
2195 |
ACC DEPCN |
00000 |
4200 |
4211 |
ACC LIAB |
00000 |
1100 |
1121 |
CASH |
00000 |
4900 |
4910 |
CMMN STOCK |
00000 |
1401 |
1469 |
FG INV |
00000 |
3990 |
3990 |
GOODWILL |
00000 |
4690 |
4690 |
LT DEBT |
00000 |
3900 |
3940 |
OTHER ASSET |
00000 |
1310 |
1400 |
OTHER CA |
00000 |
4212 |
4550 |
OTHER CL |
00000 |
4950 |
4950 |
OTHER EQUITY |
00000 |
4610 |
4685 |
OTHER LIAB |
The Domain mapping from W_GL_GROUP_ACCOUNT to W_FIN_STMT specifies the relationship between a group account number and a Financial Statement Item code.
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) |
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.
Tasks
To map General Ledger Accounts to Group Account Numbers, see Mapping GL Account Numbers to Group Account Number.
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. The Oracle Business Analytics Warehouse Data Model Reference provides a list of domain values for GL account numbers.
You can map 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, then you must also add metrics to the BI metadata repository (that is, the RPD file). See Adding a New Metric in the Logical Table Fact – Fins – GL Journals Posted.