Segment qualifiers in Oracle Fusion Financials Cloud

A segment qualifier identifies a particular type of value in a single segment of a key flexfield. In the Oracle Applications, only the Accounting Flexfield uses segment qualifiers. You can think of a segment qualifier as an "identification tag" for a value. In the Accounting Flexfield, segment qualifiers can identify the account type for a natural account segment value, and determine whether detail posting or budgeting is allowed for a particular value.

In oracle fusion, segment qualifier values are stored in compiled_value_attributes column of fnd_flex_values. These values are seperated by line feed delimiter and each value will be in new line, as shown in below screenshot. The compiled_value_attributes column store values in below order:

Few values for financial category:

SELECT flex_value_set_id,
       flex_value_id,
       flex_value,
       enabled_flag,
       summary_flag,
       compiled_value_attributes,
       value_category,
       attribute_sort_order
FROM   fnd_flex_values  WHERE  flex_value = '9099';  

COMPILED_VALUE_ATTRIBUTES (Y N N A N N AP)

COMPILED_VALUE_ATTRIBUTES (Y Y N A N N AP)

COMPILED_VALUE_ATTRIBUTES (Y Y Y A N N AP)

COMPILED_VALUE_ATTRIBUTES (Y Y Y A Y N AP)

COMPILED_VALUE_ATTRIBUTES (Y Y Y A Y Y AP)

SELECT meaning,

       lookup_code,
       description,
       enabled_flag
FROM   fnd_lookup_values
WHERE  lookup_type = 'FINANCIAL_CATEGORY'
       AND view_application_id = '101'
       AND LANGUAGE = 'US'
ORDER  BY meaning; 

Now to extract each value in separate column use the below SQL:

SELECT compiled_value_attributes,
       summary_flag                                           SUMMARY_FLAG,
       Regexp_substr(compiled_value_attributes, '[^'
                                                ||Chr(10)
                                                ||']+', 1, 1) AS summary_flag_1,
       Regexp_substr(compiled_value_attributes, '[^'
                                                ||Chr(10)
                                                ||']+', 1, 4) AS account_type,
       Regexp_substr(compiled_value_attributes, '[^'
                                                ||Chr(10)
                                                ||']+', 1, 2) AS allow_posting,
       Regexp_substr(compiled_value_attributes, '[^'
                                                ||Chr(10)
                                                ||']+', 1, 3) AS allow_budgeting
       ,
       Regexp_substr(compiled_value_attributes, '[^'
                                                ||Chr(10)
                                                ||']+', 1, 5) AS
       third_party_control,
       Regexp_substr(compiled_value_attributes, '[^'
                                                ||Chr(10)
                                                ||']+', 1, 6) AS reconcile_flag,
       Regexp_substr(compiled_value_attributes, '[^'
                                                ||Chr(10)
                                                ||']+', 1, 7) AS
       financial_category
FROM   fnd_flex_values
WHERE  flex_value = '9099'