Search Results okc_rep_contract_rels_u1

Overview

The OKC.OKC_REP_CONTRACT_RELS table is a core repository table within the Oracle E-Business Suite Contracts (OKC) module. It stores the relationship between two contracts, capturing the directional association from a source contract to a related contract under a specific relationship role. This structure supports contract hierarchies, such as master-to-subordinate agreements, amendments, renewals, and cross-references between independent contractual instruments. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10 and is owned by the OKC schema, designated as VALID in Oracle EBS 12.1.1 and 12.2.2.

In Data Vault modeling terms, the heuristic classification mined from the foreign key structure marks OKC_REP_CONTRACT_RELS as a link. This is consistent with its role as an associative entity connecting contracts to related contracts through a role definition. The link classification is a modeling suggestion rather than a strict EBS construct; in the native schema it functions as a transactional relationship table consumed by ETRM and Contracts APIs.

Key Information Stored

The table contains nine documented columns, of which the most operationally significant are:

• CONTRACT_ID — The source contract from which the relationship is being created. This is a business-key candidate participating in the unique index.
• RELATED_CONTRACT_ID — The target contract to which the relationship points.
• RELATIONSHIP_ROLE1_ID — The relationship type identifier that qualifies the nature of the association between the two contracts.
• OBJECT_VERSION_NUMBER — Sequential version number set to 1 on insert and incremented on update, used by APIs for optimistic concurrency control.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Who columns providing audit lineage for every row.

The documented surrogate primary key is CONTRACT_RELATIONSHIP_ID via OKC_REP_CONTRACT_RELS_PK. Though not listed among the nine data columns in the excerpt, it is referenced as the PK. The unique index OKC_REP_CONTRACT_RELS_U1 spans (CONTRACT_ID, RELATED_CONTRACT_ID, RELATIONSHIP_ROLE1_ID), serving as the business-key candidate and preventing duplicate relationship definitions between the same contract pair under the same role. Two foreign key paths reference OKC_REP_CONTRACTS_ALL via CONTRACT_ID, reinforcing the contract-centric nature of the table.

Common Use Cases and Queries

Typical scenarios include resolving contract hierarchies for procurement and sales agreements, identifying all amendments or successor contracts, and generating reports that traverse master-to-child contract trees. Reporting queries commonly join OKC_REP_CONTRACT_RELS to OKC_REP_CONTRACTS_ALL to retrieve contract numbers and descriptions for both sides of the relationship.

• Locate all contracts related to a given contract: SELECT CONTRACT_ID, RELATED_CONTRACT_ID, RELATIONSHIP_ROLE1_ID FROM OKC.OKC_REP_CONTRACT_RELS WHERE CONTRACT_ID = :contract_id;
• Identify relationships by role type: filter on RELATIONSHIP_ROLE1_ID to enumerate a specific relationship category.
• Reverse lookup — find source contracts pointing to a target: WHERE RELATED_CONTRACT_ID = :contract_id.
• Audit recently modified links using LAST_UPDATE_DATE and LAST_UPDATED_BY.
• Validate uniqueness against OKC_REP_CONTRACT_RELS_U1 before inserting new relationship rows through the Contracts API.

Related Objects

Significant related objects include:

• OKC.OKC_REP_CONTRACTS_ALL — Referenced twice through CONTRACT_ID foreign keys; the parent contract master table.
• OKC_REP_CONTRACT_RELS (APPS synonym/view) — The applications-layer object that references this table.
• RELATIONSHIP_TYPE_ID — Documented FK target (schema resolved as %) defining relationship semantics.
• OKC_REP_CONTRACT_RELS_PK — Primary key constraint on CONTRACT_RELATIONSHIP_ID.
• OKC_REP_CONTRACT_RELS_U1 — Unique index on (CONTRACT_ID, RELATED_CONTRACT_ID, RELATIONSHIP_ROLE1_ID).
• Contracts public APIs (OKC_CONTRACT_PUB and related) that insert, update, and version rows in this table for relationship maintenance.

The table does not reference other database objects beyond the noted foreign keys, keeping its dependency footprint focused on the contract master and relationship-role dimensions.