Results for “fk_item”

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

Overview

EDW_UNIQUE_KEY_COLUMNS_MD_V is a read-only metadata view owned by the APPS schema in Oracle E-Business Suite, delivered under the BIS (Business Intelligence System) product family. It exposes the relationship between unique key definitions and the individual columns that compose those keys, drawing on the Oracle Data Warehouse / Business Intelligence metadata layer. Its name follows the EBS data-warehouse naming convention in which EDW_ denotes an Enterprise Data Warehouse object, UNIQUE_KEY_COLUMNS describes the subject area, MD denotes metadata, and the _V suffix confirms it is a view.

The view is intended to answer a recurring metadata question: which columns of which item set constitute the unique key that identifies a record. In reporting and integration contexts, this metadata is used to generate surrogate key logic, build dimension and fact definitions in the warehouse, and validate that Extract, Transform, Load processes are joining on the correct business key. Its status is VALID in both 12.1.1 and 12.2.2, and the definition is declared WITH READ ONLY, so it can be queried but never updated.

Underlying Base Objects

Although the ETRM metadata records no referenced base objects, the view text documents the three objects it selects from:

• CMPWBITEMSETUSAGE_V (aliased FKISU) — an item set usage view that maps an item set to the attribute (item) that uses it.
• CMPITEM_V (aliased FK_ITEM) — the inventory item view supplying the column-level item identity.
• CMPUNIQUEKEY_V (aliased PK) — the unique key definition view supplying the key header.

The join is performed on FKISU.ITEMSET = PK.ELEMENTID and FK_ITEM.ELEMENTID = FKISU.ATTRIBUTE. This is the structural heart of the view: it resolves a unique key (the parent, or "PK" side) to each of the items used as its columns (the "FK_ITEM" side). The aliases are telling — references to FK_ITEM in the SQL and in user searches such as "fk_item" point directly at the CMPITEM_V join that supplies the column identity. Notably, the view is self-joining the two ELEMENTID/NAME column pairs, producing a flat, two-axis result of key versus column.

Key Columns

The view projects four columns, returned as two paired identifier/name couples:

• KEY_ID — the ELEMENTID of the unique key definition (from PK.ELEMENTID). It identifies the parent key.
• KEY_NAME — the NAME of that unique key (from PK.NAME).
• COLUMN_ID — the ELEMENTID of the item acting as a key column (from FK_ITEM.ELEMENTID).
• COLUMN_NAME — the NAME of that item column (from FK_ITEM.NAME).

Each row therefore represents one column belonging to one unique key. A key composed of multiple columns produces multiple rows sharing the same KEY_ID and KEY_NAME but differing in COLUMN_ID and COLUMN_NAME. There is no ordinal position column exposed, so column ordering within a composite key is not available from this view — a limitation worth noting when generating key constructs that depend on column sequence.

Common Use Cases and Queries

Typical uses include auditing key composition, generating dynamic SQL for warehouse loads, and reconciling item sets against their declared keys. The view is lightweight and read-only, making it safe to query directly in ad hoc analysis.

Listing all columns of every unique key:

• SELECT key_name, column_name FROM apps.edw_unique_key_columns_md_v ORDER BY key_name;

Resolving the columns of one specific key by identifier:

• SELECT key_id, key_name, column_id, column_name FROM apps.edw_unique_key_columns_md_v WHERE key_id = :p_key_id;

Identifying composite keys (those spanning more than one column):

• SELECT key_id, key_name, COUNT(*) col_cnt FROM apps.edw_unique_key_columns_md_v GROUP BY key_id, key_name HAVING COUNT(*) > 1;

Because the definition carries WITH READ ONLY, no DML should be attempted. All joins and filters should be applied in the consuming query, and the view is best treated as a stable metadata source for both 12.1.1 and 12.2.2 environments.