About Setting Up Group Account Numbers for E-Business Suite

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

Note:

If you are deploying OTBI-Enterprise, then you must download the CSV files from Configuration Manager, as follows: 1. From the Tasks bar, click Define Business Intelligence Applications. 2. Click Actions, then Manage Source Configuration Data Files. 3. Click the Export Source Configuration Data Files option on the tool bar. 4. 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.

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

OTHERS

GL Journal base fact (W_GL_OTHER_F)

Note:

*E-Business Suite adapters for Financial Analytics do not support the Tax base fact (W_TAX_XACT_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

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.

Mapping Oracle GL Account Numbers to Group Account Numbers

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

  1. Edit the file_group_acct_codes_ora.csv file.

    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.

  2. For each Oracle GL account number that you want to map, create a new row in the file containing the following fields:
    Field Name Description

    CHART OF ACCOUNTS ID

    The ID of the GL chart of account.

    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 Oracle General Ledger account, as specified in the warehouse domain Group Account in Configuration Manager. For example, 'AP' for Accounts Payables, 'CASH' for cash account, 'GEN PAYROLL' for payroll account, and so on.

    For example:

    101, 1110, 1110, CASH
    101, 1210, 1210, AR
    101, 1220, 1220, AR
    

    Note:

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

  3. Ensure that the values that you specify in the file_group_acct_codes_ora.csv file are consistent with the values that are specified in Configuration Manager for Group Accounts.
  4. Save and close the CSV file.

Adding a New Metric in the Logical Table Fact – Fins – GL Journals Posted

If you add new Group Account Numbers to the file_group_acct_codes_<source system type>.csv file, use the Oracle BI 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'.

Adding a New Metric in the Logical Table Fact – Fins – GL Other Posted Transaction

You can add a new metric in the logical table Fact – Fins – GL Other Posted Transaction.

  1. Using Oracle BI Administration Tool, edit the BI metadata repository (that is, the RPD file).
    For example, the file OracleBIAnalyticsApps.rpd is located at ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obis<n>\repository
  2. In the Business Model and Mapping layer, expand the Core\Fact - Fins - GL Journals Posted\Sources\ folder and double-click the Fact_W_GL_OTHER_GRPACCT_FSCLPRD_A source to display the Logical Table Source dialog.
    1. Display the Column Mapping tab.
    2. Click the Add New Column icon to display the Logical Column dialog.
    3. Display the Column Source tab.
    4. Select the Derived from existing columns using an expression radio button, then click the Edit Expression icon.
    5. In the Expression Builder, select Logical Tables in the Category list.
    6. Use the Expression Builder to specify the SQL statement:
      FILTER("Core"."Fact - Fins - GL Journals Posted"."Transaction Amount"
      USING "Core"."Dim - GL Account"."Group Account Number" = 'PAYROLL')
      
    7. Click OK to return to the Logical Column dialog.
  3. Click OK to save the details
  4. To expose the new repository objects in end users' dashboards and reports, drag the new objects from the Business Model and Mapping layer to an appropriate folder in the Presentation layer.

Adding a New Metric in the Logical Table Fact – Fins – GL Balance

You add a new metric in the logical table Fact – Fins – GL Balance.

  1. Using Oracle BI Administration Tool, edit the BI metadata repository (that is, the RPD file).
    For example, the file OracleBIAnalyticsApps.rpd is located at ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_ obis<n>\repository
  2. In the Business Model and Mapping layer, create a logical column named 'Payroll Expense' in logical table 'Fact – Fins – GL Balance'.
    For example, right-click the Core\Fact – Fins – GL Balance object and choose New Object, then Logical Column, to display the Logical Column dialog. Specify Payroll Expense in the Name field.
    1. In the Column Source tab, select the Derived from existing columns using an expression radio button.
    2. Click the Expression Builder icon to display Expression Builder.
    3. Use the Expression Builder to specify the following SQL statement:
      FILTER("Core"."Fact - Fins - GL Balance"."Activity Amount" USING "Core"."Dim - GL Account"."Group Account Number" = 'PAYROLL')
      

      The filter condition refers to the new Group Account Number 'PAYROLL'.

  3. Save the details.
  4. To expose the new repository objects in end users' dashboards and reports, drag the new objects from the Business Model and Mapping layer to an appropriate folder in the Presentation layer.