Setting Up Group Account Numbers for PeopleSoft

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 PeopleSoft General Ledger accounts into specific group account numbers. The GROUP_ACCT_NUM field denotes the nature of the General Ledger accounts.

You set up General Ledger accounts using the following configuration file:

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

Examples include Cash account, Payroll account, and so on. The group account number configuration is used during data extraction as well as front-end reporting. For example, the group account number configuration is used heavily in both Profitability Analysis (Income Statement) and General Ledger analysis. The logic for assigning the accounts is located in the file_group_acct_codes_psft.csv file.

BUSINESS_UNIT FROM_ACCT TO_ACCT GROUP_ACCT_NUM

AUS01

101010

101099

AP

AUS01

131010

131939

AR

AUS01

152121

152401

COGS

AUS01

171101

173001

OTHER

AUS01

240100

240120

REVENUE

AUS01

251100

251120

TAX*

Note:

*Oracle's PeopleSoft adapters for Financial Analytics do not support the Tax base fact (W_TAX_XACT_F).

In the first row, all accounts within the account number range from 101010 to 101099 containing a Business Unit equal to AUS01 are assigned to AP. Each row maps all accounts within the specified account number range and with the given Business Unit. If you need to assign a new group of account numbers, you can then assign GL accounts to the new group of account numbers in the file_group_acct_codes_psft.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:

*Oracle's PeopleSoft 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 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.

To Add Group Account Numbers to Domain Members and Map Financial Statement Items

  1. In Configuration Manager, click Manage Warehouse Domains on the Tasks pane to display the Manage Warehouse Domains dialog.
  2. In the Search area, select Oracle Financial Analytics in the Offering drop-down list, select Name in the Domain drop-down list, and enter Group Account in the adjacent text box.
  3. Click Search to locate Group Account, and select Group Account in the Warehouse Domains area.
  4. Scroll down to the Warehouse Members area.
  5. Click Add to display the Add Target Domain Member dialog, and specify Group Account Number in the Code and Name fields.
  6. Click Manage Domain Mappings and Hierarchies on the Tasks pane to display the Manage Domain Mappings and Hierarchies dialog.
  7. Select Source Instance and Dim Group 'GL Account Dimension' and click Search.
  8. Click the Warehouse Domain Hierarchies tab, then select W_GL_GROUP_ACCOUNT under W_FIN_STMT.
  9. Navigate to the 'Domain Member Mappings' region and select the Group Account Number domain member that you added in Step 5.
  10. Click Edit and select Financial Statement Item in the Target Domain Member column to assign this value to Group Account Number.
  11. Save the changes.

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

Example 1-1 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. 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 PeopleSoft

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.

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

    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.

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

    BUSINESS_UNIT

    The ID of the BUSINESS UNIT.

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

    For example:

    AUS01, 1110, 1110, CASH
AUS01, 1210, 1210, AR
AUS01, 1220, 1220, AR

    Note:

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

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