The GL Account dimension in the Oracle Business Analytics Warehouse is at a granularity of a combination of chart fields. PeopleSoft Financials provides several chart fields for GL accounts, such as account, alternate account, operating unit, department, and so on. The ETL program extracts all possible combinations of these chart fields that you have used and stores each of these chart fields individually in the GL Account dimension.
It extracts the combinations of chart fields used from the following PeopleSoft account entry tables:
PS_VCHR_ACCTG_LINES (Accounts Payable)
PS_ITEM_DST (Accounts Receivable)
PS_BI_ACCT_ENTRY (Billings)
PS_CM_ACCTG_LINE (Costing)
PS_JRNL_LN (General Ledger)
The GL Account dimension (W_GL_ACCOUNT_D) in the Oracle Business Analytics Warehouse provides a flexible and generic data model to accommodate up to 30 chartfields. These are stored in the generic columns named ACCOUNT_SEG1_CODE, ACCOUNT_SEG2_CODE and so on up to ACCOUNT_SEG30_CODE, henceforth referred to as segments. These columns store the actual chartfield value that is used in your PeopleSoft application.
Mapping PeopleSoft Chartfields
A CSV file named file_glacct_segment_config_psft.csv is provided to map the PeopleSoft chartfields to the generic segments.
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.
The first row in the file is a header row; do not modify this line. The second row in the file is where you specify how to do the mapping. The value for the column ROW_ID is hard coded to '1'; there is no need to change this.
Note that the file contains 30 columns – SEG1, SEG2, up to SEG30. You will have to specify which chart field to populate in each of these columns by specifying one of the supported values for the chart fields. The following list shows the chart fields currently supported for the PeopleSoft application.
Note:
Values are case-sensitive. You must specify the values exactly as listed:
Activity ID
Affiliate
Alternate Account
Analysis Type
Book Code
Budget Reference
Budget Scenario
Business Unit PC
ChartField 1
ChartField 2
ChartField 3
Class Field
Fund Affiliate
GL Adjust Type
Operating Unit
Operating Unit Affiliate
Product
Program Code
Project
Resource Category
Resource Sub Category
Resource Type
Statistics Code
Note:
You only need to include the chartfields in the CSV file that you want to map.