About Configuring GL Account and GL Segments for E-Business Suite

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.

Description of the illustration GUID-C08D19FA-71CD-4220-BE43-86C05AC34EAA-default.gif

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.

Description of the illustration GUID-D74CD209-9262-4182-90AD-E3E98765BF0A-default.gif

1. Edit the file_glacct_segment_config_ora.csv file.
2. Specify your Project segment column name in the 'SEG_PROJECT' column.
3. Specify your Program segment column name in the 'SEG_PROGRAM' column.
4. If in case your Project and Program segments are dependent on any other segments, then specify those segment column names in 'PROJECT_DEP' and 'PROGRAM_DEP' columns respectively.

   If you do not have reserved segments in your source system, leave that particular segment empty.

5. Save the file.

You can configure GL segments and hierarchies using value set definitions.

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 Setting 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 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 BI metadata repository (that is, the RPD file). 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 Working with Physical Foreign Keys and Joins in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition .

   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:
   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 the Use for Display option for each level as mentioned in the matrix below.

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