Search Results primary_key_sequence

Overview

The FND_PRIMARY_KEY_COLUMNS table is a core metadata repository within the Oracle E-Business Suite Application Object Library (FND). It serves as a centralized registry for the column-level definitions of primary and unique keys that are defined on application tables. Its primary role is to provide a declarative, queryable data dictionary for the EBS framework, enabling key metadata to be programmatically discovered and validated by various application utilities, upgrade scripts, and diagnostic tools. This registration is fundamental for maintaining data integrity and supporting the modular architecture of the suite.

Key Information Stored

The table stores the composition of each registered key by linking a key definition to its constituent columns and defining their order. The critical columns, as defined by its primary and unique keys, are APPLICATION_ID, TABLE_ID, PRIMARY_KEY_ID, and COLUMN_ID. The PRIMARY_KEY_SEQUENCE column specifies the ordinal position of the column within the key definition, which is essential for composite keys. In essence, each row answers the question: "For a given application and table, what column, and in what sequence, is part of a specific primary or unique key?"

Common Use Cases and Queries

A primary use case is generating dynamic SQL or performing metadata-driven validation where the structure of a table's key must be determined at runtime. For instance, a utility may need to construct a WHERE clause based on a table's primary key columns. Common queries involve joining with related metadata tables like FND_TABLES and FND_COLUMNS. A sample query to list all primary key columns for a specific table would be:

• SELECT fc.column_name, fpkc.primary_key_sequence FROM fnd_primary_key_columns fpkc JOIN fnd_columns fc ON fpkc.application_id = fc.application_id AND fpkc.table_id = fc.table_id AND fpkc.column_id = fc.column_id JOIN fnd_tables ft ON fpkc.application_id = ft.application_id AND fpkc.table_id = ft.table_id WHERE ft.table_name = '<TABLE_NAME>' AND fpkc.primary_key_id = 1 ORDER BY fpkc.primary_key_sequence;

This metadata is also crucial for impact analysis during customizations or upgrades to understand dependencies on key structures.

Related Objects

The table maintains strict foreign key relationships with other central Application Object Library metadata tables, as documented in the provided ETRM data. These relationships are:

• FND_PRIMARY_KEYS: The parent table that defines the key itself (e.g., its name, type). The join is on the composite foreign key columns (APPLICATION_ID, TABLE_ID, PRIMARY_KEY_ID).
• FND_COLUMNS: The table that stores registered column metadata. The join is on the composite foreign key columns (APPLICATION_ID, TABLE_ID, COLUMN_ID).

Therefore, a complete view of a key's definition requires joining FND_PRIMARY_KEY_COLUMNS with both FND_PRIMARY_KEYS (for key-level attributes) and FND_COLUMNS (for column names and details).