Oracle Cloud – Flexfields

A flexfield is a set of placeholder fields associated with business objects and placed on the application pages to contain additional data. You can use flexfields to modify the business objects and meet enterprise data management requirements without changing the data model or performing any database programming. Flexfields help you to capture different data on the same database table and provide a means to modify the applications features.

For example, an airline manufacturer may require specific attributes for its orders that aren’t predefined. Using a flexfield for the order business object, you can create and configure the required attribute.

Types of Flexfields

Flexfields that you see on the application pages are predefined. However, you can configure the flexfields or modify their properties. Users see these flexfields as field or information attributes on the UI pages. To manage flexfields, use any of the following tasks in the Setup and Maintenance work area:

• Manage Descriptive Flexfields: Expand the forms on the application page to accommodate additional information that is important and unique to your business. You can use a descriptive flexfield to collect invoice details on a page displaying invoices.
• Manage Extensible Flexfields: Establish one-to-many data relationships and make application data context-sensitive. The flexfields appear only when the contextual data conditions are fulfilled. Thus, extensible flexfields provide more flexibility than the descriptive flexfields.
• Manage Key Flexfields: Store information combining several values, such as a number combination. The key flexfields represent objects such as accounting codes and asset categories.
• Manage Value Sets: Use a group of values to validate the data entered in the flexfields.

Query to fetch DFF Configured on a particular entity

SELECT
APPLICATION_TABLE_NAME,
CONTEXT_COLUMN_NAME ,
TITLE,
DESCRIPTION
FROM fnd_descriptive_flexs_vl 
where 1=1 
and application_table_name='PO_HEADERS_ALL'

Query to get the DFF Attributes, Valueset Details etc on a particular entity/table

SELECT ffv.descriptive_flexfield_name "DFF Name",
ffv.application_table_name "Table Name",
ffv.title "Title",
ap.application_name "Application",
ffc.descriptive_flex_context_code "Context Code",
ffc.descriptive_flex_context_name "Context Name",
ffc.description "Context Desc",
ffc.enabled_flag "Context Enable Flag",
att.column_seq_num "Segment Number",
att.form_left_prompt "Segment Name",
att.application_column_name "Column",
fvs.flex_value_set_name "Value Set",
att.display_flag "Displayed",
att.enabled_flag "Enabled",
att.required_flag "Required"

FROM 
fnd_descriptive_flexs_vl ffv,
fnd_descr_flex_contexts_vl ffc,
fnd_descr_flex_col_usage_vl att,
fnd_flex_value_sets fvs,
fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id=ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id=att.flex_value_set_id
AND ffv.application_table_name='PO_HEADERS_ALL'

Key Flex Fields Query/Table Details

--Entities 
select fif.id_flex_code, fif.id_flex_name 
from FND_ID_FLEXS fif
where 1=1
and fif.ID_FLEX_CODE='GL#'

--structure 
select fifst.ID_FLEX_NUM, fifst.ID_FLEX_STRUCTURE_CODE, fifst.ID_FLEX_CODE
 from FND_ID_FLEX_STRUCTURES  fifst
where 1=1
and fifst.APPLICATION_ID = 101  --for gl 
and fifst.id_flex_structure_code='Corp_US_CoA'
    
--segments
select fifs.ID_FLEX_NUM, fifs.id_flex_code, fifs.APPLICATION_COLUMN_NAME, fifs.SEGMENT_NAME, 
fifs.SEGMENT_NUM, fifs.FLEX_VALUE_SET_ID 
 from FND_ID_FLEX_SEGMENTS fifs
where 1=1
and fifs.id_flex_code='GL#'

--Query to get COA Segments in a strucutre and value set name

select fifst.ID_FLEX_NUM, fifst.ID_FLEX_STRUCTURE_CODE, fifst.ID_FLEX_CODE ,
fifs.APPLICATION_COLUMN_NAME, fifs.SEGMENT_NAME, 
fifs.SEGMENT_NUM, fifs.FLEX_VALUE_SET_ID ,
ffvs.flex_value_set_name, ffvs.description, ffvs.validation_type
 from FND_ID_FLEX_STRUCTURES  fifst,  FND_ID_FLEX_SEGMENTS fifs , FND_FLEX_VALUE_SETS FFVS
where 1=1
and fifst.APPLICATION_ID = 101  --for gl 
and fifst.id_flex_structure_code='Corp_US_CoA'
and fifs.id_flex_num = fifst.id_flex_num
and FFVS.flex_value_set_id = fifs.flex_value_set_id
ORDER BY fifs.SEGMENT_NUM