Search Results okc_rep_contracts_all_n10

Overview

OKC.OKC_REP_CONTRACTS_ALL is the primary repository table for the Oracle E-Business Suite Contract Repository (OKC), storing the latest version of every contract authored within the module. In Oracle EBS 12.1.1 and 12.2.2, this table serves as the authoritative source for contract header information, encompassing user-entered contract attributes, lifecycle dates, ownership, financial amounts, risk classification, workflow linkage, and descriptive flexfield segments. It resides in the APPS_TS_TX_DATA tablespace and is owned by the OKC schema, with FND design data registered as OKC.OKC_REP_CONTRACTS_ALL.

From a Data Vault modeling perspective, the table exhibits hub-leaning characteristics. Its surrogate primary key, CONTRACT_ID, is drawn from the sequence OKC_REPO_CONTRACTS_ALL_S and remains stable across versions, making it a natural hub candidate. Business-key attribution would, however, be anchored by OKC_REP_CONTRACTS_ALL_U1 on CONTRACT_ID (unique) and OKC_REP_CONTRACTS_ALL_U2 on CONTRACT_NUMBER (unique).

Key Information Stored

Of the 70 documented columns, the following are the most operationally significant:

• CONTRACT_ID — Surrogate primary key, populated from OKC_REPO_CONTRACTS_ALL_S; enforced by unique index OKC_REP_CONTRACTS_ALL_U1.
• CONTRACT_NUMBER — Business identifier; user-entered or automatically generated per profile; enforced by unique index OKC_REP_CONTRACTS_ALL_U2.
• CONTRACT_VERSION_NUM — Auto-incremented version starting at 1; supports versioning of contract revisions.
• CONTRACT_NAME — User-entered name, indexed via a function-based index on UPPER("CONTRACT_NAME").
• CONTRACT_TYPE — User-defined contract classification; foreign key to OKC_BUS_DOC_TYPES_B.
• CONTRACT_STATUS_CODE and LATEST_SIGNED_VER_NUMBER — Lifecycle and signature state indicators.
• ORG_ID — Multi-org operating unit discriminator.
• OWNER_ID — Contract owner; indexed via OKC_REP_CONTRACTS_ALL_N10.
• CONTRACT_EFFECTIVE_DATE, CONTRACT_EXPIRATION_DATE, TERMINATION_DATE — Lifecycle dates, each individually indexed (N5, N8, N9).
• CURRENCY_CODE and AMOUNT — Financial value and denomination; AMOUNT is indexed via OKC_REP_CONTRACTS_ALL_N1.
• OVERALL_RISK_CODE — Risk classification, indexed via OKC_REP_CONTRACTS_ALL_N11.
• KEYWORDS — Oracle Text indexed column via OKC_REP_CONTRACTS_ALL_CTX for keyword search.
• WF_ITEM_TYPE / WF_ITEM_KEY — Workflow process linkage.
• ATTRIBUTE1 through ATTRIBUTE15 plus ATTRIBUTE_CATEGORY — Descriptive flexfield storage.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard WHO audit columns.

Common Use Cases and Queries

Typical uses include contract register reporting, expiry and renewal monitoring, risk exposure analysis, and integration feeds. A frequently used pattern retrieves the unique current contract row using the business key:

• SELECT contract_id, contract_number, contract_name, contract_status_code, amount, currency_code FROM okc.okc_rep_contracts_all WHERE contract_number = :p_number;
• Reporting upcoming expirations: SELECT contract_number, contract_expiration_date FROM okc.okc_rep_contracts_all WHERE contract_expiration_date BETWEEN :from_dt AND :to_dt AND contract_status_code = 'ACTIVE' AND org_id = :org;
• Risk reporting: filter by OVERALL_RISK_CODE leveraging OKC_REP_CONTRACTS_ALL_N11.
• Keyword search using the Oracle Text index on KEYWORDS via CONTAINS(keywords, :term) > 0.
• Case-insensitive lookup on name/number through the function-based indexes N4 and N12.

Related Objects

The table participates in a hub-style relationship set with several dependent child tables joined on CONTRACT_ID, plus lookup lookups on type and contact role:

• OKC.OKC_REP_CONTRACT_PARTIES — Child table; joined via CONTRACT_ID for party associations.
• OKC.OKC_REP_CONTRACT_RELS — Child table; joined via CONTRACT_ID for contract-to-contract relationships.
• OKC.OKC_REP_CONTRACT_ACCESS — Child table; joined via CONTRACT_ID for access-control entries (governed by USE_ACL_FLAG).
• OKC.OKC_BUS_DOC_TYPES_B — Referenced by CONTRACT_TYPE for valid document type definitions.
• OKC.OKC_REP_CONTACT_ROLES_B — Referenced by NOTIFY_CONTACT_ROLE_ID to resolve expiration notification roles.