Search Results additional_where_clause

Overview

FND_ID_FLEX_SEGMENTS_VL is a validation view owned by the APPS schema in Oracle E-Business Suite, residing in the FND - Application Object Library product. Its name follows the standard Oracle multilingual (MLS) convention: the "_VL" suffix denotes a view that joins a base entity with its translated (TL) counterpart and filters the translation rows by the current runtime language via USERENV('LANG'). In EBS 12.1.1 and 12.2.2 the view is documented with a status of VALID and is exposed as a synonym in APPS.

Functionally, FND_ID_FLEX_SEGMENTS_VL presents the definitions of key flexfield segments together with their language-specific display attributes. The base layer supplies the structural and behavioral definition of each segment, while the translation layer contributes the user-facing prompts and description. This makes the view the standard reporting and integration source for key flexfield segment metadata that must be presented in the user's active language, rather than the untranslated base table alone.

Underlying Base Objects

The ETRM metadata identifies two referenced base objects, both consumed through synonyms: FND_ID_FLEX_SEGMENTS and FND_ID_FLEX_SEGMENTS_TL. The view is defined as a join between these two objects, with FND_ID_FLEX_SEGMENTS acting as the driving table (aliased B) and FND_ID_FLEX_SEGMENTS_TL supplying translated columns (aliased T).

The join is composite and matches the full identity of a segment definition across both objects. The predicate equates APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM, and APPLICATION_COLUMN_NAME between the base and translated rows, and additionally restricts the translation to the currently active language with the condition T.LANGUAGE = USERENV('LANG'). The base table's ROWID is projected as ROW_ID, which provides a stable handle for the underlying base row while still surfacing translated prompts.

Key Columns

The columns exposed by the view fall into two groups. The first group comes from FND_ID_FLEX_SEGMENTS and describes the segment's structural and functional definition:

• APPLICATION_ID, ID_FLEX_CODE, ID_FLEX_NUM — identify the owning application and the specific key flexfield structure to which the segment belongs.
• APPLICATION_COLUMN_NAME, SEGMENT_NAME — the segment's application column name and internal segment name.
• SEGMENT_NUM — the segment's ordinal position within the flexfield structure.
• ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG — control whether the segment is enabled, mandatory, and displayed.
• DISPLAY_SIZE — the display width of the segment.
• SECURITY_ENABLED_FLAG — indicates whether value security applies to the segment.
• FLEX_VALUE_SET_ID — the value set that validates and supplies values for the segment.
• RANGE_CODE, DEFAULT_TYPE, DEFAULT_VALUE, RUNTIME_PROPERTY_FUNCTION — defaulting and range behavior for the segment.
• ADDITIONAL_WHERE_CLAUSE — an optional restricting clause for segment values.
• MAXIMUM_DESCRIPTION_LEN and CONCATENATION_DESCRIPTION_LEN — sizing attributes for description handling.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.
• ROW_ID — the ROWID of the base FND_ID_FLEX_SEGMENTS row.

The second group comes from FND_ID_FLEX_SEGMENTS_TL and provides the language-dependent presentation attributes: FORM_LEFT_PROMPT, FORM_ABOVE_PROMPT, and DESCRIPTION.

Common Use Cases and Queries

The view is used whenever segment metadata must be reported with translated prompts, for example in flexfield configuration reports, data migration mapping documents, and integration extracts that feed downstream systems requiring the prompt text shown to end users. A typical query listing all segments for a structure is:

• SELECT id_flex_code, id_flex_num, segment_num, segment_name, application_column_name, form_left_prompt, form_above_prompt, flex_value_set_id, enabled_flag, required_flag FROM fnd_id_flex_segments_vl WHERE id_flex_code = :p_flex_code AND id_flex_num = :p_num ORDER BY segment_num;

Because the view automatically filters on USERENV('LANG'), the returned FORM_LEFT_PROMPT, FORM_ABOVE_PROMPT, and DESCRIPTION values reflect the session language without additional filtering by the caller. Developers comparing the view against the base table should note that segments lacking a translation row in the current language will not appear in the result set, a behavior inherent to the inner join in the view definition.