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