About Configuring GL Account and GL Segments for E-Business Suite
If you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, or Oracle Supply Chain and Order Management Analytics, then you must configure GL the account hierarchies.
Thirty segments are supported in which you can store accounting flexfields. Flexfields support complex data configurations:
-
You can store data in any segment.
-
You can use more or fewer segments per chart of accounts, as required.
-
You can specify multiple segments for the same chart of accounts.
Example of Data Configuration for a Chart of Accounts
A single company might have a US chart of accounts and an APAC chart of accounts, with this data configuration:
Segment Type | US Chart of Account (4256) value | APAC Chart of Account (4257) value |
---|---|---|
Company |
Stores in segment 3 |
Stores in segment 1 |
Natural Account |
Stores in segment 4 |
Stores in segment 3 |
Cost Center |
Stores in segment 5 |
Stores in segment 2 |
Geography |
Stores in segment 2 |
Stores in segment 5 |
Line of Business (LOB) |
Stores in segment 1 |
Stores in segment 4 |
This example shows that in US Chart of Account, 'Company' is stored in the segment 3 column in the E-Business Suite table GL_CODE_COMBINATIONS. In APAC Chart of Account, 'Company' is stored in the segment 1 column in GL_CODE_COMBINATIONS table. The objective of this configuration file is to ensure that when segment information is extracted into the Oracle Business Analytics Warehouse table W_GL_ACCOUNT_D, segments with the same nature from different chart of accounts are stored in the same column in W_GL_ACCOUNT_D.
For example, we can store 'Company' segments from US COA and APAC COA in the segment 1 column in W_GL_ACCOUNT_D; and Cost Center segments from US COA and APAC COA in the segment 2 column in W_GL_ACCOUNT_D, and so on.
Setting Up the GL Segment Configuration File
Before you run the ETL process for GL accounts, you must specify the segments that you want to analyze. Natural Account, Balancing Segment, and Cost Center segments are mapped by default, but you must manually map additional segments.
To specify the segments, you use the ETL configuration file named file_glacct_segment_config_ora.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.
In file_glacct_segment_config_ora.csv
, you must specify the segments of the same type in the same column. For example, you might store all Product segments from all charts of accounts in one column, and all Region segments from all charts of accounts in a separate column.
File file_glacct_segment_config_ora.csv
contains a set of 3 columns for each accounting segment to be configured in the warehouse. In the 1st column, give the actual segment column name in Oracle E-Business Suite where this particular entity is stored. This column takes values such as SEGMENT1, SEGMENT2....SEGMENT30 (this is case sensitive). In the second column give the corresponding VALUESETID used for this COA and segment in Oracle E-Business Suite. You will need to configure the third column ONLY if you have configured a dependent segment in the first column. If the segment in the first column is a dependent segment, then give the segment name on which it is dependent on, in Oracle E-Business Suite. If you don't have any dependent segments, then leave this column blank in the CSV file.
For example, you might want to do the following:
-
Analyze GL account hierarchies using only Product, Region and Location.
-
Store all Product segments from all COAs in ACCOUNT_SEG1_CODE column in W_GL_ACCOUNT_D.
-
Store all Region segments from all COAs in ACCOUNT_SEG2_CODE column in W_GL_ACCOUNT_D.
-
Store all Location segments from all COAs in ACCOUNT_SEG3_CODE column in W_GL_ACCOUNT_D.
You have defined three different COAs (101, 50194 and 50195) in EBS, as follows:
-
For COA 101, Product is SEGMENT1, Region is SEGMENT2 and Location is SEGMENT3.
-
For COA 50194, Product is SEGMENT2, Region is SEGMENT3 and Location is SEGMENT1.
-
For COA 50195, Product is SEGMENT3, Region is SEGMENT1 and Location is SEGMENT2.
-
For COA 50195, both the Region and Location segments are dependent on Product segment.
The figure shows hows how the configuration values would be specified in the CSV file.
If you are upgrading from Oracle BI Applications 7.9.6.x, Cost Center, Balancing Segment and Natural account segments are mapped by default. You do not need to map the Cost Center, Balancing Segment and Natural account segments in the file_glacct_segment_config_ora.csv file. The above example is only included to explain the mapping mechanism for additional segments.
Additional Information
The example SQL Statement below shows against an Oracle E-Business Suite database, and outputs the entire GL chart of accounts setup. This output contains the necessary information required to setup the file_glacct_segment_config_ora.csv
file.
SELECT ST.ID_FLEX_STRUCTURE_CODE "Chart of Account Code" ,SG.ID_FLEX_NUM "Chart of Account Num" ,SG.SEGMENT_NAME "Segment Name" ,SG.APPLICATION_COLUMN_NAME "Column Name" ,SG.FLEX_VALUE_SET_ID "Value Set Id" ,SG1.APPLICATION_COLUMN_NAME "Parent Column Name" FROM FND_ID_FLEX_STRUCTURES ST INNER JOIN FND_ID_FLEX_SEGMENTS SG ON ST.APPLICATION_ID = SG.APPLICATION_ID AND ST.ID_FLEX_CODE = SG.ID_FLEX_CODE AND ST.ID_FLEX_NUM = SG.ID_FLEX_NUM INNER JOIN FND_FLEX_VALUE_SETS VS ON SG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID LEFT OUTER JOIN FND_ID_FLEX_SEGMENTS SG1 ON VS.PARENT_FLEX_VALUE_SET_ID = SG1.FLEX_VALUE_SET_ID AND SG.ID_FLEX_NUM = SG1.ID_FLEX_NUM AND SG.APPLICATION_ID = SG1.APPLICATION_ID AND SG.ID_FLEX_CODE = SG1.ID_FLEX_CODE WHERE ST.APPLICATION_ID = 101 AND ST.ID_FLEX_CODE = 'GL#' AND ST.ENABLED_FLAG = 'Y' ORDER BY 1,2,3;
For example, you have 2 chart of accounts and the setup of the 2 chart of accounts as displayed by the SQL statement above as follows:
Chart of Account Code Chart of Account Num Segment Name Column Name Value Set Id Parent Column Name US_ACCOUNTING_FLEX 101 Region SEGMENT1 1026447 US_ACCOUNTING_FLEX 101 Product SEGMENT2 1026448 SEGMENT1 US_ACCOUNTING_FLEX 101 Sub-Account SEGMENT3 1026449 SEGMENT1 EU_ ACCOUNTING_FLEX 201 Region SEGMENT1 1031001 EU_ ACCOUNTING_FLEX 201 Department SEGMENT2 1031002 EU_ ACCOUNTING_FLEX 201 Product SEGMENT3 1031003 EU_ ACCOUNTING_FLEX 201 Sub Account SEGMENT4 1031004
You want all these segments in BI and you want to map them as follows in BI:
- Map Region to Seg1
- Map Product to Seg2
- Map Sub-Account to Seg3
- Map Department to Seg4
Note:
-
Department is only applicable to COA 201.
-
COA 101 has Product and Sub-Account segments as dependent segments.
The figure shows how the configuration values above would be specified in the CSV file.
GL Segment Configuration for Budgetary Control
For Budgetary Control, the first two segments are reserved for Project and Program segments respectively. Therefore, to use one or both of these, configure file_glacct_segment_config_ora.csv
in this particular order:
Configuring GL Segments and Hierarchies Using Value Set Definitions
You can configure GL segments and hierarchies using value set definitions.
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.