The accounting flex field in Oracle E-Business Suite (EBS) and Oracle Fusion uniquely identifies a chart of accounts, which is a crucial component of financial management. The number of segments in the accounting flex field can vary based on the organization’s business requirements. For example, an organization might choose a 3-segment, 5-segment, or 7-segment flex field to represent their financial structure.

Let’s take a look at two examples:

1. Organization 1:

• Flex Field Segments: Company, Department, and Account
• Concatenated Segment: 010.202.300

2. Organization 2:

• Flex Field Segments: Company, Account, Product, Product Line, and Subaccount
• Concatenated Segment: 020.456.8898.8890.8980

To store these accounting combinations, Oracle uses the GL_CODE_COMBINATIONS table, where the code_combination_id serves as a unique key. The values for each defined entity, such as Company, Department, Account, etc., are stored in segment1 to segment30 columns.

To find the Concatenated Segment of an Accounting Flexfield Code Combination, you can run a query on the gl_code_combinations table. For example:

SELECT Segment1 || ‘.’ || Segment2 || ‘.’ || Segment3

FROM gl_code_combinations

WHERE code_combination_id = 121;

However, a better approach is to utilize Oracle Apps’ gl_code_combinations_kfv view and the fnd_flex_ext.get_segs API. This view stores the concatenated segments information in the concatenated_segments column. The fnd_flex_ext.get_segs API provides a simpler way to retrieve the concatenated segments, regardless of the number of segments in the accounting flex field. It accepts parameters such as application_short_name, key_flex_code, structure_number, and combination_id.

For Oracle E-Business Suite, you can call the API like this:

SELECT fnd_flex_ext.get_segs(‘SQLGL’, ‘GL#’, 50234, 121) from dual;

One point to remember here, application short name is different in fusion. It is SQLGL in Oracle Apps and GL in Oracle Cloud Application.

In Oracle Fusion Apps, the gl_code_combinations_kfv view is not available, so you should use the fnd_flex_ext.get_segs API as shown above. It’s important to note that the application short name is different in Fusion, where it is GL for Oracle Cloud Applications, as opposed to SQLGL in Oracle E-Business Suite.

In summary, utilizing the fnd_flex_ext.get_segs API is recommended to retrieve the concatenated segment information, providing a more versatile and standardized approach across different implementations of Oracle Apps.