About Mapping GL Accounts to Group Account Numbers for JD Edwards EnterpriseOne

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>.

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 Oracle BI Applications 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). 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).

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.

FROM ACCT	TO ACCT	GROUP_ACCT_NUM
4100	4190	AP
1200	1299	AR
2120	2195	ACC DEPCN
4200	4211	ACC LIAB
1100	1121	CASH
4900	4910	CMMN STOCK
1401	1469	FG INV
3990	3990	GOODWILL
4690	4690	LT DEBT
3900	3940	OTHER ASSET
1310	1400	OTHER CA
4212	4550	OTHER CL
4950	4950	OTHER EQUITY
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

A description of how to map General Ledger Accounts to Group Account Numbers - see Mapping GL Account Numbers to Group Account Number.
An example that explains 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. For a list of domain values for GL account numbers, see Oracle Business Analytics Warehouse Data Model Reference.

Mapping GL Account Numbers to Group Account Numbers for JD Edwards EnterpriseOne

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, 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 for more information.

Edit the file_group_acct_codes_jde.csv.
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.

For each GL account number that you want to map, create a new row in the file containing the following fields:

Field Name	Description
COMPANY	The ID of the COMPANY.
FROM ACCT	The lower limit of the natural account range. This is based on the natural account segment of your GL accounts.
TO ACCT	The higher limit of the natural account range. This is based on the natural account segment of your GL accounts.
GROUP_ACCT_NUM	This field denotes the group account number of the General Ledger account, as specified in a domain in Oracle BI Applications Configuration Manager. For example, 'AP' for Accounts Payables, 'CASH' for cash account, 'GEN PAYROLL' for payroll account, and so on.

For example:

1000, 1110, 1110, CASH
1000, 1210, 1210, AR
1000, 1220, 1220, AR

Note:

You can optionally remove the unused rows in the CSV file.

Ensure that the values that you specify in the file_group_acct_codes_jde.csv file are consistent with the domain members of Group Account (W_GL_GROUP_ACCOUNT).

In particular, the GROUP_ACCOUNT_NUM field in file_group_acct_names.csv 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.
Save and close the CSV file.