Search Results okc_rep_doc_versions_v




Overview
Explore in AI Chat

Overview

OKC_REP_DOC_VERSIONS_V is a reporting view owned by the APPS schema within the OKC – Contracts Core product family (Oracle Contracts, part of Oracle E-Business Suite 12.1.1 and 12.2.2). Its documented purpose is to return repository contract versions. It exposes a unified, read-only list of contract documents together with their version identifiers and current status, distinguishing records that reside in the version history table from those that are still active in the current contracts table.

The view serves reporting and integration consumers who must enumerate every version of a repository contract without needing to know the physical storage split between current and historical data. Because it joins across the current and version tables via UNION rather than a single base table, it presents a consolidated document-version picture suitable for extract programs, BI Publisher reports, Oracle Business Intelligence (OBIEE) repositories, and custom concurrent programs.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through APPS synonyms:

  • OKC_REP_CONTRACTS_ALL – the current contracts table, aliased RCA. Rows selected from this object represent the presently active contract records and are flagged as not archived.
  • OKC_REP_CONTRACT_VERS – the contract version history table, aliased RCV. Rows selected from this object represent superseded or historical contract versions and are flagged as archived.

The defining SQL is a UNION of two SELECT statements. The first selects CONTRACT_ID, CONTRACT_TYPE, CONTRACT_VERSION_NUM, and CONTRACT_STATUS_CODE from OKC_REP_CONTRACT_VERS and assigns a constant 'Y' to the calculated ARCHIVED_YN column. The second selects the same four attributes from OKC_REP_CONTRACTS_ALL and assigns a constant 'N' to ARCHIVED_YN. Because UNION (rather than UNION ALL) is used, duplicate rows produced by both branches are eliminated, guaranteeing a distinct set of contract-version combinations.

Key Columns

  • DOCUMENT_ID – aliases CONTRACT_ID; the unique identifier of the repository contract to which the version belongs.
  • DOCUMENT_TYPE – aliases CONTRACT_TYPE; the classification of the contract document.
  • DOCUMENT_VERSION – aliases CONTRACT_VERSION_NUM; the version number of the contract record. This is the column of primary interest to callers searching on "document_version".
  • STATUS – aliases CONTRACT_STATUS_CODE; the lifecycle status code of that version (for example, the contract's current status at the time the version was recorded).
  • ARCHIVED_YN – a derived literal indicating provenance: 'Y' when the row comes from OKC_REP_CONTRACT_VERS (an archived/historical version), 'N' when it comes from OKC_REP_CONTRACTS_ALL (the active record).

The view exposes only these five columns; it does not project descriptive attributes such as contract titles or party names, so consumers typically join DOCUMENT_ID back to the contracts tables for additional detail.

Common Use Cases and Queries

Typical scenarios include auditing the version history of a contract, identifying which contracts have more than one version, and filtering only archived or only active documents.

  • List all versions for a specific contract:
SELECT document_id, document_type, document_version, status, archived_yn
FROM   okc_rep_doc_versions_v
WHERE  document_id = :contract_id
ORDER  BY document_version;
  • Count versions per contract:
SELECT document_id, COUNT(*) version_count
FROM   okc_rep_doc_versions_v
GROUP  BY document_id
HAVING COUNT(*) > 1;
  • Isolate the active (non-archived) version, or conversely retrieve only historical versions:
SELECT * FROM okc_rep_doc_versions_v WHERE archived_yn = 'N';
SELECT * FROM okc_rep_doc_versions_v WHERE archived_yn = 'Y';

Because ARCHIVED_YN is a literal rather than a stored column, filtering on it lets a single query separate current records from historical ones cleanly, which is the primary convenience the view offers over querying the base tables directly.