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.
SELECT kfv.concatenated_segments FROM gl_code_combinations_kfv kfv WHERE code_combination_id = 121;
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,
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.
I would recommend using fnd_flex_ext.get_segs API to get this segment information.