How to Configure GL Account and GL Segments for Oracle E-Business Suite

B.2.20 How to Configure GL Account and GL Segments for Oracle E-Business Suite

This section explains how to configure General Ledger Account and General Ledger Segments for Oracle E-Business Suite, and contains the following topics:

• Section B.2.20.1, "Overview"
• Section B.2.20.2, "Example of Data Configuration for a Chart of Accounts"
• Section B.2.20.3, "How to Set Up the GL Segment Configuration File"
• Section B.2.20.4, "How to Configure GL Segments and Hierarchies Using Value Set Definitions"

B.2.20.1 Overview

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 account hierarchies as described in this topic.

Thirty segments are supported in which you can store accounting flexfields. Flexfields are flexible enough to support complex data configurations. For example:

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

B.2.20.2 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 the following data configuration:

Table B-7 Example Chart of Accounts

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

B.2.20.3 How to Set 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. To specify the segments, you use the ETL configuration file named file_glacct_segment_config_<source_system>.csv.

Note:

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.

Figure B-2 Example values in file_glacct_segment_config_ora.csv

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 Cost Center segments from all charts of accounts in one column, and all Company segments from all charts of accounts in a separate column.

File file_glacct_segment_config_ora.csv contains a pair of 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.

For example, you might want to do the following:

• Analyze GL account hierarchies using only Company, Cost Center, Natural Account, and LOB.
  You are not interested in using Geography for hierarchy analysis.
• Store all Company segments from all COAs in ACCOUNT_SEG1_CODE column in W_GL_ACCOUNT_D.
• Store all Cost Center segments from all COAs in ACCOUNT_SEG2_CODE column in W_GL_ACCOUNT_D.
• Store all Natural Account segments from all COAs in ACCOUNT_SEG3_CODE column in W_GL_ACCOUNT_D.
• Store all LOB segments from all COAs in ACCOUNT_SEG4_CODE column in W_GL_ACCOUNT_D.
  Note: Although the examples above are mapping Natural Account, Balancing Segment and Cost Center segments to one of the segment columns in the file, it is not required that you map these three segments in the file. This is because we have dedicated dimensions to populate these three segments and they will be populated automatically by default whether or not you map these three segments in this file. It is preferred that these three segments are not mapped in this file so as to avoid redundant segment dimensions giving the same information.

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:

1. Put your Project segment column name in the 'SEG_PROJECT' column in the CSV file.

2. Put your Program segment column name in the 'SEG_PROGRAM' column in the CSV file.

If you do not have any one of these reserved segments in your source system, leave that particular segment empty in the CSV file.

B.2.20.4 How to Configure GL Segments and Hierarchies Using Value Set Definitions

Note:

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.

1. Configure file_glacct_segment_config_ora.csv, as follows:
   1. Edit the file file_glacct_segment_config_ora.csv.
      For example, you might edit the file located in \src_files\EBS11510.
   2. Follow the steps in Section B.2.20.3, "How to Set Up the GL Segment Configuration File" to configure the file.
2. Edit the BI metadata repository (that is, the RPD file) for GL Segments and Hierarchies Using Value Set Definitions.
   The metadata contains multiple logical tables that represent each GL Segment, such as Dim_W_GL_SEGMENT_D_ProgramSegment, Dim_W_GL_SEGMENT_D_ProjectSegment, Dim_W_GL_SEGMENT_D_Segment1 and so on. Because all these logical tables are mapped to the same physical table, W_GL_SEGMENT_D, a filter should be specified in the logical table source of these logical tables in order to restrain the output of the logical table to get values pertaining to that particular segment. You must set the filter on the physical column SEGMENT_LOV_ID to the Value Set IDs that are applicable for that particular segment. The list of the Value Set IDs would be the same as the Value Set IDs you configured in the CSV file mentioned above.
   Specify a filter in the Business Model and Mapping layer of the Oracle BI Repository, as follows.
   1. In Oracle BI EE Administration Tool, edit the BI metadata repository (for example, OracleBIAnalyticsApps.rpd).
      The OracleBIAnalyticsApps.rpd file is located in ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obis<n>\repository.
   2. Expand each logical table, for example, Dim - GL Segment1, and open the logical table source under it. Display the Content tab. In the 'Use this WHERE clause…' box, apply a filter on the corresponding physical table alias of W_GL_SEGMENT_D.
      For example: "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_GL_SEGMENT_D_Segment1"."SEGMENT_LOV_ID" IN (comma separated values IDs).
   3. Enter all Value Set IDs, separated by commas that correspond to this segment.
3. Oracle Financial Analytics supports up to 30 segments in the GL Account dimension, and by default delivers ten GL Segment dimensions in the RPD. If you need more than ten GL Segments, perform the following steps to add new segments:
   In the Physical Layer:
   1. Create two new physical alias of W_GL_SEGMENT_D as "Dim_W_GL_SEGMENT_D_SegmentXX" and Dim_W_GL_SEGMENT_D_SegmentXX_GLAccount.
      To do this, right-click the physical table W_GL_SEGMENT_D and select New Object and then Alias.Name the new alias as "Dim_W_GL_SEGMENT_D_SegmentXX" and "Dim_W_GL_SEGMENT_D_SegmentXX_GLAccount".
   2. Create 4 new alias of W_GL_SEGMENT_DH as:
      - "Dim_W_GL_SEGMENT_DH_SegmentXX"
      - "Dim_W_GL_SEGMENT_DH_Security_SegmentXX"
      - "Dim_W_GL_SEGMENT_DH_SegmentXX_GLAccount"
      - "Dim_W_GL_SEGMENT_DH_Security_SegmentXX_GLAccount"
   3. Create a Foreign Key from "Dim_W_GL_SEGMENT_D_SegmentXX" to "Dim_W_GL_SEGMENT_DH_SegmentXX" and "Dim_W_GL_SEGMENT_DH_Security_SegmentXX".
      The foreign key is similar to the one from "Dim_W_GL_SEGMENT_D_Segment1" to "Dim_W_GL_SEGMENT_DH_Segment1" and "Dim_W_GL_SEGMENT_DH_Security_Segment1".
      The direction of the foreign key should be from W_GL_SEGMENT_DH to W_GL_SEGMENT_D; for example, on a '0/1': N cardinality join, W_GL_SEGMENT_DH will be on the '0/1' side and W_GL_SEGMENT_D will be on the 'N' side. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information about how to create physical foreign key joins.
   4. Create a similar physical foreign key from "Dim_W_GL_SEGMENT_D_SegmentXX_GLAccount" to "Dim_W_GL_SEGMENT_DH_SegmentXX_GLAccount' and "Dim_W_GL_SEGMENT_DH_Security_SegmentXX_GLAccount".
   5. Similarly, create physical foreign key join between Dim_W_GL_SEGMENT_D_SegmentXX and Dim_W_GL_ACCOUNT_D, with W_GL_SEGMENT_D on the '1' side and W_GL_ACCOUNT_D on the 'N' side.
   6. Save your changes.
4. In the Business Model and Mapping Layer, do the following:
   1. Create a new logical table "Dim - GL SegmentXX" similar to "Dim – GL Segment1".
      This logical table should have a logical table source that is mapped to the physical tables created above (for example, it will have both Dim_W_GL_SEGMENT_DH_SegmentXX and Dim_W_GL_SEGMENT_DH_SegmentXX_GLAccount).
      This logical table should also have all attributes similar to "Dim – GL Segment1" properly mapped to the respective physical tables, Dim_W_GL_SEGMENT_DH_SegmentXX and Dim_W_GL_SEGMENT_DH_SegmentXX_GLAccount.
   2. In the Business Model Diagram, create a logical join from "Dim – GL SegmentXX" to all the relevant logical fact tables similar to "Dim – GL Segment1", with the GL Segment Dimension Logical table on the '0/1' side and the logical fact table on the 'N' side.
      To see all the relevant logical fact tables, first include Dim – GL Segment1 on the Business Model Diagram, and then right-click that table and select Add Direct Joins.
   3. Add the content filter in the logical table source of "Dim – GL SegmentXX" as described in the previous step.
   4. Create a dimension by right-clicking "Dim – GL SegmentXX", and select Create Dimension. Rename this to "GL SegmentXX". Make sure the drill-down structure is similar to "GL Segment1".
      If you are not sure how to do this, follow these steps: By default, the dimension will have two levels: the Grand Total Level and the Detail Level. Rename these levels to "All" and "Detail – GL Segment" respectively.
      Right-click the "All" level and select "New Object" and then "Child Level". Name this level as Tree Code And Version. Create a level under Tree Code And Version and name it as Level31. Similarly create a level under Level31 as Level30. Repeat this process until you have Level1 under Level2.
   5. Drag the "Detail – GL Segment" level under "Level1" so that it is the penultimate level of the hierarchy. Create another child level under "Detail – GL Segment" and name it as "Detail – GL Account".
   6. From the new logical table Dim - GL SegmentXX, drag the Segment Code, Segment Name, Segment Description, Segment Code Id and Segment Value Set Code attributes to the "Detail – GL Segment" level of the hierarchy. Similarly pull in the columns mentioned below for the remaining levels.
      Detail – GL Account – Segment Code – GL Account
      Levelxx – Levelxx Code, Levelxx Name, Levelxx Description and Levelxx Code Id
      Tree Code And Version – Tree Filter, Tree Version ID, Tree Version Name and Tree Code
   7. Navigate to the properties of each Level and from the Keys tab, create the appropriate keys for each level as mentioned below. Select the primary key and "Use for Display option" for each level as mentioned in the matrix below.
      Table B-8 Configuration values for GL Segments and Hierarchies Using Value Set Definitions

      Level	Key Name	Columns	Primary Key of that Level	Use for Display?
      Tree Code And Version	Tree Filter	Tree Filter	Y	Y
      Levelxx	Levelxx Code	Levelxx Code	Y	Y
      Levelxx	Levelxx ID	Levelxx Code Id	<empty>	<empty>
      Detail - GL Segment	Segment ID	Segment Code Id	Y	<empty>
      Detail - GL Segment	Segment Code	Segment Value Set Code and Segment Code	<empty>	Y
      Detail - GL Account	Segment Code - GL Account	Segment Code - GL Account	Y	Y

   8. Once you have created these new levels, you will have to set the aggregation content for all the Logical Table Sources of the newly created logical table created Dim - GL SegmentXX. Set the Aggregation Content in the Content tab for each LTS as mentioned below:
      Dim_W_GL_SEGMENT_DH_SegmentXX – Set the content level to "Detail – GL Segment".
      Dim _W_GL_SEGMENT_DH_SegmentXX_GLAccount – Set it to "Detail – GL Account".
   9. Set the aggregation content to all relevant fact logical table sources. Open all Logical Table Sources of all the logical fact tables that are relevant to the new logical table one at a time. Display the Content tab. If the LTS is applicable for that newly created segment, then set the aggregation content to "Detail – GL Account". If not, skip that logical table source and go to the next one.
   10. Drag your new "Dim - GL Segment XX" dimensions into the appropriate subject areas in the Presentation layer. Typically, you can expose these GL Segment dimensions in all subject areas where the GL Account dimension is exposed. You can also find all appropriate subject areas by right-clicking Dim – GL Segment1 and select Query Related Objects, then selecting Presentation, and then selecting Subject Area.
   11. Save your changes and check global consistency.
5. Each GL Segment denotes a certain meaningful ValueSet(s) in your OLTP. To clearly identify each segment in the report, you can rename the presentation table "GL SegmentX", logical dimension "GL SegmentX", and logical table "Dim - GL SegmentX" according to its own meaning.
   For example, if you populate Product segment into Segment1, you can rename logical table "Dim - GL Segment1" as "Dim – GL Segment Product" or any other appropriate name and then rename the tables in the Presentation layer accordingly.