Search Results okc_rep_con_status_hist




Overview
Explore in AI Chat

Overview

OKC_REP_CON_STATUS_HIST is a Contracts Core (OKC) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the status change history of a contract. Its defining characteristic is version awareness: each version of a contract maintains its own status history, which is why the table is keyed by both contract identity and version number rather than by contract alone. This makes the object central to any audit or lifecycle analysis that needs to answer not only "what status is this contract in" but "what status was this contract in at a given point in time, and for which version." The table is owned by the OKC schema and is classified as VALID in the documented ETRM metadata.

Under a heuristic Data Vault classification mined from the foreign key structure, OKC_REP_CON_STATUS_HIST is treated as standalone. In modeling terms this suggests the table does not participate in a classic hub-and-link pattern through documented foreign keys, and is best regarded as a candidate satellite — a descriptive, time-stamped history attached to a contract/version parent. This is a suggestion only; the physical implementation remains a conventional relational table within the OKC schema.

Key Information Stored

The primary key is OKC_REP_CON_STATUS_HIST_PK, defined on the composite of CONTRACT_ID and CONTRACT_VERSION_NUM. These two columns form the surrogate/identity key of the record and are the columns a user searching on "contract_version_num" is most directly concerned with. CONTRACT_ID identifies the contract, and CONTRACT_VERSION_NUM identifies the specific version whose status history is being recorded.

A unique index, OKC_REP_CON_STATUS_HIST_U1, adds STATUS_CHANGE_DATE and STATUS_CODE to the key columns, making it the strong business-key candidate: a given contract version cannot repeat a specific status code at the same change date.

  • STATUS_CODE — the status that the contract version transitioned to at that point in the history.
  • STATUS_CHANGE_DATE — the effective date/time of the status transition, and the primary ordering column for history reconstruction.
  • CHANGED_BY_USER_ID — the user who performed the status change, supporting audit and accountability reporting.
  • OBJECT_VERSION_NUMBER — the optimistic locking / row versioning column used by the Oracle Forms-based contract UI to detect concurrent updates.
  • CREATED_BY, CREATION_DATE — standard WHO columns recording insert audit.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO columns recording the most recent update and the login session context.

The table documents 11 columns in total, of which the version and status columns above carry the substantive business meaning; the remainder are standard EBS audit and concurrency attributes.

Common Use Cases and Queries

The most frequent use case is contract lifecycle reporting: reconstructing the status path of a contract version, measuring time-in-status, or identifying the current status as of a date. Because the history is version-scoped, queries generally filter on both CONTRACT_ID and CONTRACT_VERSION_NUM.

A typical pattern retrieves the complete status history for a given 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 AND contract_version_num = :p_version ORDER BY status_change_date;
  • SELECT status_code, status_change_date FROM okc.okc_rep_con_status_hist WHERE contract_id = :p_contract_id AND contract_version_num = :p_version AND status_change_date = (SELECT MAX(status_change_date) FROM okc.okc_rep_con_status_hist ...); — used to derive the latest known status.
  • Audit queries filtering on changed_by_user_id and a status_change_date range to identify who changed contract statuses during a period.

Because the unique index includes STATUS_CHANGE_DATE and STATUS_CODE, reporting that expects a single row per contract version and status will generally be safe, while analysis across versions should always carry CONTRACT_VERSION_NUM into the GROUP BY.

Related Objects

No explicit foreign key relationships were documented in the provided metadata, consistent with its "standalone" heuristic classification. The most significant logical relationships are to the contract header and version entities in OKC, joined on the composite key columns:

  • OKC_REP_CON_STATUS_HIST_PK — the primary key constraint on (CONTRACT_ID, CONTRACT_VERSION_NUM).
  • OKC_REP_CON_STATUS_HIST_U1 — the unique index on (CONTRACT_ID, CONTRACT_VERSION_NUM, STATUS_CHANGE_DATE, STATUS_CODE).
  • OKC contract header tables joined via CONTRACT_ID to obtain contract identity and descriptive attributes.
  • Contract version entities joined via CONTRACT_ID and CONTRACT_VERSION_NUM to align status history with the version record.
  • OKC contract status lookup/validation objects referenced by STATUS_CODE to resolve status meaning.
  • EBS user/application responsibility objects joined via CHANGED_BY_USER_ID for audit attribution.

Because the metadata documents no FK dependencies, integrators should confirm join paths against the specific 12.1.1 or 12.2.2 instance rather than assuming enforced referential integrity.