GL Account and GL Segments Configuration

For deploying Oracle E-Business Suite Data Intelligence, its essential to configure General Ledger (GL) account hierarchies.

Oracle supports up to 30 segments to store accounting flexfields, allowing for highly flexible and complex data configurations:

  • Data can be stored in any segment.

  • The number of segments per Chart of Accounts (COA) can vary.

  • Multiple segments can represent similar entities within the same COA.

Note: The data warehouse by default supports only ten segments.

By default, the following segments are mapped:

  • Natural Account

  • Balancing Segment

  • Cost Center

In addition to these, any other required segments must be mapped manually.

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 Oracle 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 E-Business Suite Data Intelligence 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.

GL Accounts and segments must be configured using the inline dataset FILE_GL_ACCT_SEG_CFG_ORA.

Key guidelines:

  • Segments of the same type must be grouped into the same column.

    • Example: All Product segments across COAs in one column, all Region segments in another.

  • Each accounting segment is defined using a set of three columns.

Column Details:

  1. Segment Column Name

    • Specify the actual segment column name from Oracle E-Business Suite.

    • Valid values: SEGMENT1 to SEGMENT30 (case-sensitive).

  2. Value Set ID

    • Provide the corresponding Value Set ID used for that segment in the COA.

  3. Dependent Segment (Optional)

    • Required only if the segment is dependent on another segment.

    • Specify the parent segment name.

    • Leave blank if there is no dependency.

Example Scenario:

Suppose you want to analyze GL account hierarchies using only Product, Region, and Location dimensions:

  • Map all Product segments → ACCOUNT_SEG1_CODE in W_GL_ACCOUNT_D

  • Map all Region segments → ACCOUNT_SEG2_CODE

  • Map all Location segments → ACCOUNT_SEG3_CODE

You have three COAs configured in Oracle E-Business Suite:

  • COA 101

    • Product → SEGMENT1

    • Region → SEGMENT2

    • Location → SEGMENT3

  • COA 50194

    • Product → SEGMENT2

    • Region → SEGMENT3

    • Location → SEGMENT1

  • COA 50195

    • Product → SEGMENT3

    • Region → SEGMENT1

    • Location → SEGMENT2

    • Region and Location are dependent on Product

The following screenshot shows hows the configuration values would be specified in the inline dataset:
GL account configuration

Additional Information

The following SQL query (run against Oracle E-Business Suite) retrieves the full GL Chart of Accounts setup. This output provides the necessary details to configure the FILE_GL_ACCT_SEG_CFG_ORA dataset:

EBS Query

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 Oracle E-Business Suite Data Intelligence and you want to map them as follows in Oracle E-Business Suite Data Intelligence:

- Map Region to Seg1

- Map Product to Seg2

- Map Sub-Account to Seg3

- Map Department to Seg4

The figure shows how the configuration values above would be specified in the Inline dataset.


Configuration values above would be specified in the Inline dataset