Search Results fk_column_sequence

Overview

APPS.OE_PC_FKEY_COLS_V is a reporting view in the Oracle E-Business Suite Applications (APPS) schema that exposes foreign key and unique key column metadata from the Oracle Application Object Library (AK) data dictionary tables. Despite its OE_ prefix, the view is a generic metadata repository view: it does not store Order Entry transactional data. Instead, it provides a flattened, query-friendly representation of how foreign key columns map to the columns of the unique (primary or candidate) key they reference.

The view is particularly relevant when browsing or reporting on the fk_column_sequence attribute. In the Oracle EBS data dictionary, a foreign key is composed of one or more columns whose order is significant; FK_COLUMN_SEQUENCE (sourced from AK_FOREIGN_KEY_COLUMNS.FOREIGN_KEY_SEQUENCE) indicates the positional order of each foreign key column. By aligning FK_COLUMN_SEQUENCE with the corresponding unique key sequence, the view allows consumers to see precisely which foreign key column maps to which referenced unique key column, and in what order. This is essential for generating accurate join predicates, reverse-engineering data models, and documenting interfaces.

Underlying Base Objects

The view is defined over five AK synonym objects registered in the APPS schema:

• AK_FOREIGN_KEYS — header information for each foreign key (application, database object name, foreign key name, referenced unique key name).
• AK_FOREIGN_KEY_COLUMNS — the individual columns that make up each foreign key, including the FOREIGN_KEY_SEQUENCE.
• AK_UNIQUE_KEYS — header information for each unique key referenced by a foreign key.
• AK_UNIQUE_KEY_COLUMNS — the constituent columns of each unique key, including UNIQUE_KEY_SEQUENCE.
• AK_OBJECT_ATTRIBUTES — attribute definitions used twice (aliased FAKOA and UAKOA) to resolve the actual column names for the foreign key columns and the unique key columns respectively.

The joins link foreign keys to their columns, columns to attribute definitions, foreign keys to unique keys, and unique key columns back through attribute definitions. The core join condition FKC.FOREIGN_KEY_SEQUENCE = UKC.UNIQUE_KEY_SEQUENCE is what associates each foreign key column with its positional counterpart in the referenced unique key. The view labels its rows with a constant DB_OBJECT_TYPE of 'VIEW', reflecting that this is a flattened dictionary view rather than a base table.

Key Columns

• APPLICATION_ID — the application owning the foreign key definition.
• DB_OBJECT_NAME — the database object (table or view) on which the foreign key is defined.
• DB_OBJECT_TYPE — constant 'VIEW' as constructed by the view text.
• FOREIGN_KEY_NAME — the identifier of the foreign key constraint definition.
• FK_COLUMN_SEQUENCE — the ordinal position of the foreign key column; the column most commonly searched for as fk_column_sequence.
• FK_COLUMN_NAME — the actual database column participating in the foreign key.
• UNIQUE_KEY_NAME — the referenced unique key.
• UK_APPLICATION_ID, UK_DB_OBJECT_NAME — identifying attributes of the referenced unique key.
• UK_COLUMN_NAME — the referenced unique key column corresponding to the same sequence value.

Common Use Cases and Queries

Typical uses include data-model documentation, impact analysis before schema changes, and dynamic generation of join conditions. A common query retrieves ordering details for a specific foreign key:

SELECT db_object_name,
       foreign_key_name,
       fk_column_sequence,
       fk_column_name,
       unique_key_name,
       uk_column_name
FROM   apps.oe_pc_fkey_cols_v
WHERE  foreign_key_name = :p_fk_name
ORDER  BY fk_column_sequence;

To enumerate all foreign keys and their column ordering on a given object:

SELECT foreign_key_name,
       fk_column_sequence,
       fk_column_name,
       uk_column_name
FROM   apps.oe_pc_fkey_cols_v
WHERE  db_object_name = :p_table_name
ORDER  BY foreign_key_name, fk_column_sequence;

These queries are useful for verifying that key column pairs align correctly, confirming positional integrity, and producing documentation of referential relationships within the EBS data dictionary.