Results for “okc_ancestrys_u1”

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

Overview

OKC.OKC_ANCESTRYS is a denormalized hierarchy bridge table in the Oracle E-Business Suite Contracts (OKC) schema. Its documented purpose is to hold the complete list of all ancestor Contract Lines for a given Contract Line stored in OKC_K_LINES_B. By pre-materializing every ancestor-descendant relationship, the table enables a simple, non-recursive query to return all parent Contract Lines without the need for a CONNECT BY hierarchical walk. This is particularly valuable in scenarios where recursive SQL cannot be used because the query requires joins to other tables, or where a flat result set is more efficient for reporting.

The object resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10 and holds 10 documented columns. From a Data Vault modeling perspective, the metadata suggests a link classification: the table records relationships between Contract Line identifiers rather than descriptive attributes of a single business entity. Both CLE_ID and CLE_ID_ASCENDANT resolve to OKC_K_LINES_B, reinforcing the link interpretation.

Key Information Stored

The primary key of the table is OKC_ANCESTRYS_PK, composed of CLE_ID and CLE_ID_ASCENDANT. A unique index, OKC_ANCESTRYS_U1, is defined on the same two columns (CLE_ID, CLE_ID_ASCENDANT) in the APPS_TS_TX_IDX tablespace, making these columns the documented business-key candidates. A secondary nonunique index, OKC_ANCESTRYS_N1, supports reverse traversal on CLE_ID_ASCENDANT alone.

• CLE_ID — identifier of the Contract Line (the descendant node).
• CLE_ID_ASCENDANT — identifier of the ancestor Contract Line. Both columns are foreign keys to OKC_K_LINES_B.
• LEVEL_SEQUENCE — numeric indicator of the depth or position of the ancestor relative to the descendant line.
• OBJECT_VERSION_NUMBER — optimistic locking counter set to 1 on insert and incremented on update, used by APIs to detect stale records.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Who columns capturing audit and accountability metadata.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, used in hosted (multi-tenant) environments.

Common Use Cases and Queries

The most common pattern is roll-up reporting: returning every ancestor of a given contract line in a single pass.

• Ancestor listing for a line: SELECT CLE_ID_ASCENDANT FROM OKC.OKC_ANCESTRYS WHERE CLE_ID = :line_id ORDER BY LEVEL_SEQUENCE;
• Descendant listing (reverse traversal leveraging OKC_ANCESTRYS_N1): SELECT CLE_ID FROM OKC.OKC_ANCESTRYS WHERE CLE_ID_ASCENDANT = :line_id;
• Hierarchy depth analysis: aggregate MAX(LEVEL_SEQUENCE) GROUP BY CLE_ID to detect deeply nested structure.
• Joined reporting: because this table flattens the tree, it can be joined directly to OKC_K_LINES_B, OKC_K_HEADERS, or pricing tables without recursive SQL.
• Data validation: reconciliation queries comparing OKC_ANCESTRYS against a CONNECT BY walk over OKC_K_LINES_B to detect orphaned or missing ancestor rows after data migration or patching.

Related Objects

• OKC.OKC_K_LINES_B — parent entity for both CLE_ID and CLE_ID_ASCENDANT; the base Contract Line table.
• OKC.OKC_K_LINES_TL — translated line descriptions for display in hierarchy reports.
• OKC.OKC_K_HEADERS_B — contract header, joined for contract-level roll-ups.
• OKC.OKC_ANCESTRYS_U1 — unique index enforcing (CLE_ID, CLE_ID_ASCENDANT).
• OKC.OKC_ANCESTRYS_N1 — nonunique index on CLE_ID_ASCENDANT for reverse queries.
• FND.FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID.
• APPS.OKC_ANCESTRYS — the APPS synonym layer used by application code and reports.

The table does not reference any other database object beyond the documented foreign keys, and it is referenced by application views and PL/SQL packages in the OKC schema that manage contract line hierarchy maintenance.