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. Natural Account, Balancing Segment, and Cost Center segments are mapped by default, but you must manually map additional segments as described in this topic.

To specify the segments, you use the ETL configuration file named 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 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 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

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.

Informational Task Reference - Miscellaneous

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.

Figure B–2 shows how the configuration values above would be specified in the CSV file.

Figure B–2 Example values in file_glacct_segment_config_ora.csv

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

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

Informational Task Reference - Miscellaneous

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.

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 CodeChart of Account NumSegment NameColumn NameValue Set IdParent Column Name

US_ACCOUNTING_FLEX101RegionSEGMENT1 1026447

US_ACCOUNTING_FLEX101ProductSEGMENT2 1026448 SEGMENT1 US_ACCOUNTING_FLEX101Sub-AccountSEGMENT3 1026449 SEGMENT1 EU_ ACCOUNTING_FLEX201RegionSEGMENT1 1031001

EU_ ACCOUNTING_FLEX201DepartmentSEGMENT2 1031002 EU_ ACCOUNTING_FLEX201ProductSEGMENT3 1031003 EU_ ACCOUNTING_FLEX201Sub Account SEGMENT41031004

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.

Informational Task Reference - Miscellaneous

Figure B–3 shows how the configuration values above would be specified in the CSV file.

Figure B–3 Example values in file_glacct_segment_config_ora.csv