Search Results oe_pc_fkey_cols_v




Overview
Explore in AI Chat

Overview

OE_PC_FKEY_COLS_V is a read-only view owned by the APPS schema in Oracle E-Business Suite, shipped with the Order Management (ONT) product family. It is part of the Oracle Application Object Library (AK) metadata layer and exposes foreign key definitions together with their corresponding unique key definitions for database objects registered in the EBS metadata repository. The view is primarily used by internal EBS components that need to reason about relational metadata at runtime — for example, the Order Management ordering and schema introspection utilities that rely on the AK foreign key catalog.

The view is documented as VALID and is present in both EBS 12.1.1 and 12.2.2. It is not a transactional view; it is a metadata dictionary view. Its role in reporting and integration is to provide a normalized, readable projection of the foreign key and unique key relationships defined in the AK tables, which would otherwise require multi-table joins across four or more underlying tables. For integrations that build dynamic SQL, generate DDL, or validate referential relationships, this view supplies the column-level mapping between the foreign key columns and the unique key columns they reference.

Underlying Base Objects

The view is defined over five synonymous references in APPS, which resolve to the underlying AK tables:

The join is driven by foreign key name, attribute application ID/attribute code, database object name, and the positional sequence value, ensuring each foreign key column is paired with the unique key column it references.

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 — the object type; the view text hard-codes this as 'VIEW'.
  • FOREIGN_KEY_NAME — the foreign key constraint identifier.
  • FK_COLUMN_SEQUENCE — ordinal position of the column within the foreign key.
  • FK_COLUMN_NAME — the physical column name on the foreign key side.
  • UNIQUE_KEY_NAME — the referenced unique key identifier.
  • UK_APPLICATION_ID, UK_DB_OBJECT_NAME — the owning application and object of the referenced unique key.
  • UK_COLUMN_NAME — the referenced column name on the unique key side.

Common Use Cases and Queries

Typical scenarios include generating dynamic join predicates, validating that the correct unique-key columns back a foreign key, and documenting the relational structure of Order Management tables. A basic query to list all foreign key to unique key column mappings for a given object:

  • SELECT foreign_key_name, fk_column_name, unique_key_name, uk_column_name FROM oe_pc_fkey_cols_v WHERE db_object_name = :object_name ORDER BY foreign_key_name, fk_column_sequence;
  • SELECT db_object_name, foreign_key_name, uk_db_object_name FROM oe_pc_fkey_cols_v WHERE application_id = 660 GROUP BY db_object_name, foreign_key_name, uk_db_object_name;
  • SELECT DISTINCT foreign_key_name, uk_column_name FROM oe_pc_fkey_cols_v WHERE fk_column_name = uk_column_name;

Because the view is a metadata construct, results reflect the AK registration for the current release; query results may differ between 12.1.1 and 12.2.2 as new objects are registered.