Search Results okc_ancestrys




Overview
Explore in AI Chat

Overview

OKC_ANCESTRYS is a transactional table within the OKC (Contracts Core) schema of Oracle E-Business Suite, holding the ancestor relationship between Contract Lines. Each row records that one Contract Line (identified by CLE_ID) has a higher-level ancestor Contract Line (identified by CLE_ID_ASCENDANT). This structure supports hierarchical contract line modeling, enabling features such as line-level groupings, parent-child rollups, entitlement propagation, and pricing or term inheritance across nested contract structures.

The table is registered as VALID and is owned by the OKC schema in both EBS 12.1.1 and 12.2.2. Based on the foreign key topology — two references to OKC_K_LINES_B plus a security reference to FND_SECURITY_GROUPS — the heuristically mined Data Vault classification for this object is a link. In Data Vault modeling terms, OKC_ANCESTRYS would be modeled as a link table that connects two instances of the same hub (Contract Line), representing a recursive (self-referencing) hierarchy rather than an ordinary many-to-many relationship between distinct entities. This classification is offered as a modeling suggestion; the physical table functions as a hierarchy edge list within the EBS transactional model.

Key Information Stored

The documented physical schema consists of 10 columns. The most significant columns are described below.

  • CLE_ID — Identifies the descendant (child) Contract Line. This column participates in the composite primary key and is a foreign key to OKC_K_LINES_B.
  • CLE_ID_ASCENDANT — Identifies the ancestor (parent or higher-level) Contract Line. This column also participates in the composite primary key and references OKC_K_LINES_B.
  • LEVEL_SEQUENCE — Indicates the relative depth or ordering of the ancestor level within the lineage. This supports traversal and ordering of hierarchical relationships without requiring recursive queries for simple level reporting.
  • OBJECT_VERSION_NUMBER — Implements optimistic locking, incrementing on each row update to prevent conflicting concurrent modifications.
  • SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enabling multi-tenant / multi-org data isolation by security group.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns capturing who created and last modified the row and when.

The surrogate primary key is defined by OKC_ANCESTRYS_PK on the composite of (CLE_ID, CLE_ID_ASCENDANT). A unique index, OKC_ANCESTRYS_U1, exists on the same column pair (CLE_ID, CLE_ID_ASCENDANT), making that pair the business-key candidate that enforces uniqueness of a single ancestor relationship per child line. Notably, the ancestor edge is not identified by an artificial single-column surrogate; the relationship pair itself is the key.

Common Use Cases and Queries

Typical scenarios include reconstructing the full ancestry of a Contract Line, listing all descendants of a parent line, and reporting contract line hierarchies for pricing, billing, or entitlement rollups. Because OKC_ANCESTRYS stores explicit ancestor-descendant pairs (and not merely immediate parents), queries can often resolve multi-level lineage with a straight join rather than a recursive CONNECT BY.

Representative SQL patterns:

  • Find all ancestors for a specific contract line: 
    SELECT CLE_ID_ASCENDANT, LEVEL_SEQUENCE
FROM   OKC.OKC_ANCESTRYS
WHERE  CLE_ID = :p_cle_id
ORDER BY LEVEL_SEQUENCE;
  • Find all descendants of a given ancestor line: 
    SELECT CLE_ID, LEVEL_SEQUENCE
FROM   OKC.OKC_ANCESTRYS
WHERE  CLE_ID_ASCENDANT = :p_ancestor_id
ORDER BY LEVEL_SEQUENCE;
  • Join to the line definition to resolve line details: 
    SELECT kl.line_number, kl.name, a.LEVEL_SEQUENCE
FROM   OKC.OKC_ANCESTRYS a,
       OKC.OKC_K_LINES_B  kl
WHERE  a.CLE_ID_ASCENDANT = kl.id
AND    a.CLE_ID = :p_cle_id;
  • Reporting by security group when multi-tenant isolation applies: 
    SELECT CLE_ID, CLE_ID_ASCENDANT
FROM   OKC.OKC_ANCESTRYS
WHERE  SECURITY_GROUP_ID = :p_security_group_id;

Related Objects

The following objects are most closely related to OKC_ANCESTRYS, based on the documented foreign key and primary key relationships:

  • OKC_K_LINES_B — The base Contract Lines table. OKC_ANCESTRYS.CLE_ID and OKC_ANCESTRYS.CLE_ID_ASCENDANT both reference OKC_K_LINES_B, establishing the descendant and ancestor endpoints of each relationship.
  • OKC_K_LINES_TL — The translation table for Contract Lines, useful for retrieving language-specific line names and descriptions when presenting hierarchy results.
  • FND_SECURITY_GROUPS — Referenced by OKC_ANCESTRYS.SECURITY_GROUP_ID, controlling data visibility across operating units and security profiles.
  • OKC_ANCESTRYS_PK / OKC_ANCESTRYS_U1 — The primary key constraint and unique index that enforce one unique ancestor row per child line.

In practice, application logic referencing OKC_ANCESTRYS is usually mediated through the Contracts Core line-relationship APIs and the OKC_K_LINES_B entity, rather than direct DML. This reflects the table's role as an internal structural component of the contract line hierarchy rather than an end-user maintained entity.