Results for “csi_inst_ext_attr_all_v”

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

Overview

CSI_INST_EXT_ATTR_ALL_V is an APPS-owned database view in the CSI (Install Base) product family. Its purpose, as stated in the ETRM metadata, is to "display and retrieve the applicable extended attributes and their values for a given instance." Extended attributes in Oracle Install Base are the user-defined, flexfield-style descriptive fields that allow customers to capture additional item or instance information beyond the seeded columns of the install base tables. This view consolidates those attribute definitions together with their corresponding stored values into a single, query-friendly projection.

The view plays a central role in reporting and integration. Because extended attributes are physically distributed across several definition and value tables (differentiated by the level at which the attribute applies — global, item, or category), consumers would otherwise need to join multiple objects and understand the application's fallback logic. CSI_INST_EXT_ATTR_ALL_V abstracts this complexity by presenting every applicable attribute for an instance alongside its value, using an outer-join pattern so that attributes without a stored value are still returned.

Underlying Base Objects

The documented view metadata lists the following referenced base objects:

• CSI_INST_GLOBAL_EXT_ATTR_V (VIEW) — global-level extended attribute definitions
• CSI_INST_ITEM_EXT_ATTR_V (VIEW) — item-level extended attribute definitions
• CSI_INST_CATG_EXT_ATTR_V (VIEW) — category-level extended attribute definitions
• CSI_IEA_VALUES (SYNONYM) — the table holding instance attribute values, including ATTRIBUTE_VALUE_ID
• CSI_ITEM_INSTANCES (SYNONYM) — the core install base instance table
• CSI_I_EXTENDED_ATTRIBS (SYNONYM) — the extended attribute definition table

The view text is a UNION ALL of two nearly identical branches. The first branch joins attribute definitions from the global source (CEA alias) to values in CSI_IEA_VALUES (CIV alias) using the join condition CEA.SRC_INSTANCE_ID = CIV.INSTANCE_ID (+) and CEA.ATTRIBUTE_ID = CIV.ATTRIBUTE_ID (+). The second branch applies the same structure to the remaining attribute levels. The (+) outer-join syntax ensures every defined attribute is returned even when no instance value exists.

Key Columns

The view exposes definition columns from the attribute source and value columns from CSI_IEA_VALUES:

• ATTRIBUTE_ID — the identifier linking the definition to its stored value
• ATTRIBUTE_LEVEL — indicates whether the attribute applies globally, at item level, or at category level
• ATTRIBUTE_CODE, ATTRIBUTE_NAME, ATTRIBUTE_CATEGORY, DESCRIPTION — descriptive metadata for the attribute
• MASTER_ORGANIZATION_ID, INVENTORY_ITEM_ID, ITEM_CATEGORY_ID, INSTANCE_ID — the applicable scope of the attribute
• CONTEXT, ATTRIBUTE1 through ATTRIBUTE15 — the flexfield context and value segments
• ATTRIBUTE_VALUE_ID — the primary identifier of the stored value row; the searched term is answered directly from this column
• ATTRIBUTE_VALUE — the actual value stored for the instance
• SRC_ACTIVE_START_DATE, SRC_ACTIVE_END_DATE, SRC_OBJECT_VERSION_NUMBER — value-row audit columns, renamed to avoid collision with the definition's own ACTIVE_START_DATE, ACTIVE_END_DATE, and OBJECT_VERSION_NUMBER
• SRC_INSTANCE_ID — the instance identifier used to correlate definitions to values

Common Use Cases and Queries

Typical scenarios include install base reporting, data migration validation, and integration extracts where attribute values must be retrieved per instance. The following query lists attributes and values for a specific instance:

• SELECT attribute_id, attribute_code, attribute_value_id, attribute_value FROM csi_inst_ext_attr_all_v WHERE instance_id = :p_instance_id;
• SELECT instance_id, attribute_code, attribute_value FROM csi_inst_ext_attr_all_v WHERE attribute_value_id IS NOT NULL ORDER BY instance_id, attribute_code;
• SELECT attribute_level, COUNT(*) FROM csi_inst_ext_attr_all_v GROUP BY attribute_level;

The presence of the outer join means queries filtering on ATTRIBUTE_VALUE_ID IS NULL isolate defined-but-unvalued attributes, a common check during data quality reviews. Filtering on ATTRIBUTE_VALUE_ID IS NOT NULL restricts output to attributes that actually carry a value.