Search Results okc_rep_con_status_hist_u1

Overview

OKC.OKC_REP_CON_STATUS_HIST is a transactional history table within the Oracle E-Business Suite Contracts (OKC) schema. Its documented purpose is to store the status change history of a contract, with the important characteristic that each version of a contract maintains its own independent status history. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, placing it in the transactional data tier consistent with its role as a high-volume, append-oriented record keeper.

The object is documented as VALID across the ETRM 12.1.1 and 12.2.2 reference sets, carries FND Design Data entry OKC.OKC_REP_CON_STATUS_HIST, and is registered with a heuristic Data Vault classification of standalone. From a modeling perspective, this classification suggests the table functions as a self-contained record set with no resolved foreign key dependencies to other database objects. In Data Vault terms it would most naturally be modeled as a satellite attached to a contract-version hub, capturing the descriptive, time-variant attribute of status over time. The absence of FK relationships in the documented dependency data is worth noting: referential integrity to the contract header is enforced within the application layer rather than through database constraints.

Key Information Stored

The documented physical schema contains eleven columns. The most significant are described below.

• CONTRACT_ID (NUMBER) — Identifier of the contract. This is the principal business identifier carried into downstream joins.
• CONTRACT_VERSION_NUM (NUMBER) — The version of the contract to which the status change applies. Because status history is maintained per version, this column is essential to obtaining a correct record sequence.
• STATUS_CODE (VARCHAR2, 30) — The status code to which the contract was updated at the point of change.
• STATUS_CHANGE_DATE (DATE) — The effective date on which the contract's status was changed. This column supplies the temporal ordering of the history.
• CHANGED_BY_USER_ID (NUMBER) — The user identifier responsible for the status transition, providing accountability and audit traceability.
• OBJECT_VERSION_NUMBER (NUMBER) — A sequential number set to 1 on insert and incremented on update; it is used by the APIs to ensure the current record is the one being passed, providing optimistic locking semantics.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Who columns recording row creation and last modification metadata, including the login context of the modifying session.

The primary key is documented as OKC_REP_CON_STATUS_HIST_PK on (CONTRACT_ID, CONTRACT_VERSION_NUM). The unique index OKC_REP_CON_STATUS_HIST_U1 — the object the user searched for — covers (CONTRACT_ID, CONTRACT_VERSION_NUM, STATUS_CHANGE_DATE, STATUS_CODE) and resides in the APPS_TS_TX_IDX tablespace. This unique index is the practical business-key candidate, preventing duplicate entries for the same status code on the same contract version at the same timestamp.

Common Use Cases and Queries

The table supports contract lifecycle auditing, status duration analysis, and compliance reporting. A representative query retrieves the full status timeline for a contract version in chronological order:

• SELECT CONTRACT_ID, CONTRACT_VERSION_NUM, STATUS_CODE, STATUS_CHANGE_DATE, CHANGED_BY_USER_ID FROM OKC.OKC_REP_CON_STATUS_HIST WHERE CONTRACT_ID = :p_contract_id ORDER BY CONTRACT_VERSION_NUM, STATUS_CHANGE_DATE;
• To identify the current status, select the row with the maximum STATUS_CHANGE_DATE per (CONTRACT_ID, CONTRACT_VERSION_NUM), typically via an inline view or analytic RANK() function.
• To measure time spent in each status, use LAG over STATUS_CHANGE_DATE partitioned by contract and version.
• For audit purposes, filter by CHANGED_BY_USER_ID or by a STATUS_CHANGE_DATE range to review who moved contracts between statuses within a given period.

Because the unique index leads with CONTRACT_ID and CONTRACT_VERSION_NUM, queries constrained on those two columns benefit from index range scans. Queries filtering only on STATUS_CODE will not use the index efficiently and may warrant a supporting index in high-volume reporting environments.

Related Objects

The documented dependency data states that OKC.OKC_REP_CON_STATUS_HIST does not reference any other database object, meaning no FK constraints are declared from this table. It is, however, referenced by the object OKC_REP_CON_STATUS_HIST# in the OKC schema. Given the standalone classification, the following related objects are the most significant from an application and logical-model perspective:

• OKC_REP_CON_STATUS_HIST# — The referencing object documented in the dependency data.
• OKC_K_HEADERS / OKC_K_HEADERS_B — Contract header entities joined on CONTRACT_ID to obtain contract number and descriptive attributes.
• OKC_K_LINES and versioned contract structures — Joined on CONTRACT_ID and CONTRACT_VERSION_NUM to associate status transitions with the relevant contract version.
• OKC_CONTRACT_STATUS or equivalent status lookup — Provides the descriptive meaning of STATUS_CODE values.
• FND_USER — Joined on CHANGED_BY_USER_ID (and the Who columns) to resolve the user who performed the change.
• OKC_CONTRACT_PUB APIs — The Contracts public APIs that write status transitions and must be treated as the authoritative maintenance path for this table.

Direct DML against OKC_REP_CON_STATUS_HIST is not advisable; status transitions should be driven through the supported Contracts APIs so that OBJECT_VERSION_NUMBER and the Who columns remain internally consistent.