Skip to content
Home » How to get Concatenated Segments in Oracle Fusion and Apps

How to get Concatenated Segments in Oracle Fusion and Apps

The accounting flex field uniquely identifies a chart of accounts. The organization may choose 3 segments, 5 segment or 7 segments flex field as per the business requirement e.g

  • Org 1 – Company, Department, and Account – 010.202.300
  • Org 2 – Company, Account, Product, Product Line, and Subaccount – 020.456.8898.8890.8980

GL_CODE_COMBINATIONS table stores all this accounting combination where code_combination_id is a unique key and segment1 to segment30 stores the value of each defined entity separately. As I said, the Concatenated Segments identifies unique account combination.

So how to find the Concatenated Segment Of An Accounting Flexfield Code Combination?

You can simply run the below query on gl_code_combinations table to get concatenated segments.

SELECT Segment1 ||'.'||Segment2 ||'.'||Segment3
   FROM gl_code_combinations
  WHERE code_combination_id = 121

One of the requirements of the above query is that you should know how many segments are there in the accounting flex field and what is segment separator.

There is a better way…!!!

Oracle Apps provide gl_code_combinations_kfv view and fnd_flex_ext.get_segs API to get this Concatenated Segment information.

Get Concatenated Segments in Oracle Apps

Oracle Apps R12 as well as 11i stores concatenated segments information in concatenated_segments column in gl_code_combinations_kfv view.

Run the below query against this view to get the required information.

Query

SELECT kfv.concatenated_segments
FROM gl_code_combinations_kfv kfv
WHERE code_combination_id = 121;

Output

011.202.320

Here, you don’t have to bother if your flex field has 3 segments, 5 or more. This query will give results considering that setup.

You can also use the fnd_flex_ext.get_segs API to get concatenated segments information. This API accepts below parameters,

  • application_short_name
  • key_flex_code
  • structure_number 
  • combination_id

This is the way you can call the API.

SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', 50234, 121) from dual

Get Concatenated Segments in Oracle Fusion Apps

The gl_code_combinations_kfv view is not there is Oracle Cloud Applications. So you have to use fnd_flex_ext.get_segs to get the Concatenated segment in Oracle Fusion Apps as shown above.

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

Summary

I would recommend using fnd_flex_ext.get_segs API to get this segment information.