Results for “structure_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ICX_CATEGORY_SET_LOV is a database view owned by the APPS schema in Oracle E-Business Suite. It is documented under the ICX product family, which maps to Oracle iProcurement, and its description in the ETRM metadata is "Category Set LOV View." As the name implies, the view exists to populate List of Values (LOV) components in iProcurement and related self-service purchasing pages, allowing end users and application code to select a purchasing category set by name without directly querying the underlying transactional table.

The view is a thin, denormalized projection rather than a complex join or aggregation. It presents three columns drawn straight from a single base object, so it adds no filtering, transformation, or business logic of its own. Its value lies in decoupling the LOV presentation layer from the physical MTL_CATEGORY_SETS table and in providing a stable, minimally privileged access point for read-only lookups. Because it is defined with a very small column list, it is efficient for the high-frequency, narrow lookups typical of an LOV control.

Underlying Base Objects

The documented base object referenced by ICX_CATEGORY_SET_LOV is MTL_CATEGORY_SETS, exposed to the APPS schema through a synonym. MTL_CATEGORY_SETS is the inventory/product master table that stores category set definitions, including the category set identifier, the descriptive name, and the associated category structure. The view is defined as a direct SELECT over this synonym, with no joins to other tables in the documented view text.

Because the definition is a straight projection, referential integrity and column semantics are inherited entirely from MTL_CATEGORY_SETS. The view carries the same row cardinality as its base table, meaning every category set row in MTL_CATEGORY_SETS is visible through ICX_CATEGORY_SET_LOV. The view's status is recorded as VALID in the metadata, indicating that its defining query parsed successfully against the underlying synonym at the time of documentation.

Key Columns

• CATEGORY_SET_ID — The unique identifier (primary key) for the category set. This is the value typically stored as a foreign key in downstream tables and the value passed as the internal return value when a user makes an LOV selection.
• CATEGORY_SET_NAME — The user-facing, translatable name of the category set. This is the value rendered in the LOV display field and the value a user types when searching.
• STRUCTURE_ID — The identifier of the category structure (hierarchy) to which the category set belongs. Because the user interface search term was "structure_id," this column is of particular interest: it links each category set to its underlying structure definition and is the key field for partitioning category sets by structure. In practice, STRUCTURE_ID is frequently used as a bind or filter value to constrain an LOV to only those category sets belonging to a specific structure.

Common Use Cases and Queries

The primary use case is driving LOV controls. Application pages query the view to populate a category set selector, commonly restricting results by structure and ordering by name for readability. A representative query filtering on the user's search term is:

• SELECT category_set_id, category_set_name, structure_id FROM apps.icx_category_set_lov WHERE structure_id = :p_structure_id ORDER BY category_set_name;
• SELECT category_set_id, category_set_name FROM apps.icx_category_set_lov WHERE category_set_name LIKE :p_name || '%'; — name-based type-ahead searching.
• SELECT structure_id, COUNT(*) FROM apps.icx_category_set_lov GROUP BY structure_id; — reporting category set counts per structure.

Because the view exposes STRUCTURE_ID directly, integration and reporting logic that must associate a selected category set with its structure can read it without joining MTL_CATEGORY_SETS. This makes the view convenient for lightweight validations, cross-reference extracts, and BI publisher queries where only these three attributes are required. For any additional category set attributes, a join back to MTL_CATEGORY_SETS is necessary, since the view intentionally limits its projection to the three documented columns.