Search Results uk_column_sequence

Overview

OE_PC_UKEY_COLS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, registered under the ONT (Order Management) product family. It exposes metadata describing the unique key definitions that Oracle's Applications Object Library (AK) maintains for database objects—principally views—used within the Order Management configuration framework. The view's name reflects its purpose: "OE" denotes Order Management, "PC" references the product/catalog metadata layer, and "UKEY_COLS" identifies it as a listing of unique key columns.

Functionally, the view flattens the relationship between a database object, its declared unique keys, and the individual attributes that compose each key, while simultaneously flagging which unique key corresponds to the object's primary key. This makes it valuable for reporting and integration scenarios in both EBS 12.1.1 and 12.2.2, where the underlying AK entity-attribute metadata model remains consistent. Because the view resolves the AK hierarchy into a simple, denormalized column list, it allows developers and analysts to enumerate key structures without writing multi-table joins against the AK tables directly.

Underlying Base Objects

The view is defined over four documented base objects, all referenced through public synonyms that resolve to the corresponding AK tables:

• AK_UNIQUE_KEYS — the master registry of unique keys defined per application and database object.
• AK_UNIQUE_KEY_COLUMNS — the child table mapping each unique key to the attributes (columns) that constitute it, including sequence ordering.
• AK_OBJECTS — the object registry, supplying the primary key name associated with each database object.
• AK_OBJECT_ATTRIBUTES — the attribute registry, providing the physical column name for each attribute code.

Joins are established on APPLICATION_ID, DATABASE_OBJECT_NAME, UNIQUE_KEY_NAME, ATTRIBUTE_CODE, and ATTRIBUTE_APPLICATION_ID. Notably, the DB_OBJECT_TYPE column is hard-coded to the literal 'VIEW' in the view's SELECT text, indicating OE_PC_UKEY_COLS_V is intended to surface unique key metadata only for view-based objects rather than tables.

Key Columns

• APPLICATION_ID — the application owning the database object and its key definitions.
• DB_OBJECT_NAME — the name of the underlying database object (aliased from AK_UNIQUE_KEYS.DATABASE_OBJECT_NAME).
• DB_OBJECT_TYPE — a constant value of 'VIEW'.
• UNIQUE_KEY_NAME — the internal name of the unique key.
• PRIMARY_KEY_FLAG — a DECODE expression returning 'Y' when the unique key name matches the object's primary key name, otherwise 'N'.
• UK_COLUMN_SEQUENCE — the position of the column within the unique key, sourced from AK_UNIQUE_KEY_COLUMNS.UNIQUE_KEY_SEQUENCE. This is the column most directly associated with the search term "uk_column_sequence."
• UK_COLUMN_NAME — the physical database column name of the key attribute.

Common Use Cases and Queries

This view is typically used to inspect or document the primary and alternate key structures of Order Management views, particularly when integrating with external systems or building validation logic. A representative query enumerating all unique key columns with their ordinal positions is:

SELECT application_id, db_object_name, unique_key_name, primarykey_flag, uk_column_sequence, uk_column_name FROM apps.oe_pc_ukey_cols_v ORDER BY db_object_name, unique_key_name, uk_column_sequence;

To isolate only primary keys ordered by sequence, a WHERE clause on primarykey_flag = 'Y' may be applied. Because the join conditions are restricted to matching application and object identifiers, the view does not itself enforce a particular product context; consumers should filter by db_object_name or application_id as required for their reporting scope.