Search Results document_version




Overview
Explore in AI Chat

Overview

PO_DOCUMENT_VERSIONS_V is a documented view owned by the APPS schema in Oracle E-Business Suite, registered under the Purchasing (PO) product family. Its stated purpose, per the ETRM metadata, is to provide version information on documents for contracts. In practice, the view consolidates purchasing document header records from both the live transaction tables and the corresponding archive tables, returning a single row per document version with a normalized document type, revision number, authorization status, and an indicator of whether the version is archived.

The view is a reporting and integration construct rather than a transactional object. It is not used to update purchasing data; instead it exposes a flattened, query-friendly projection of revision history. Because the underlying data spans active and archived headers, the view allows downstream reports, extracts, and interfaces to retrieve a complete version trail for a given PO header identifier without manually unioning the live and archive tables.

Underlying Base Objects

The documented base objects referenced by this view are PO_HEADERS_ALL and PO_HEADERS_ARCHIVE_ALL, both exposed in the APPS schema as synonyms. The view text is defined as a UNION of two queries:

  • The first branch selects from PO_HEADERS_ARCHIVE_ALL, tagging every row with ARCHIVED_YN = 'Y'.
  • The second branch selects from PO_HEADERS_ALL outer-joined to PO_HEADERS_ARCHIVE_ALL, tagging rows with ARCHIVED_YN = 'N'. This branch filters on NVL(POHA.LATEST_EXTERNAL_FLAG, 'Y') = 'Y' and POH.REVISION_NUM > NVL(POHA.REVISION_NUM, -1).

The net effect is that archived revisions are returned from the archive table, while the live table contributes the current (non-archived) revision only when it is newer than the latest archived revision for that header. The join is performed on PO_HEADER_ID, and the outer join operator is applied to the archive side to protect headers that have never been archived.

Key Columns

  • DOCUMENT_ID — The PO_HEADER_ID of the document. This is the join key back to PO_HEADERS_ALL and is the identifier used to correlate all versions of the same document.
  • DOCUMENT_TYPE — A normalized document type derived via DECODE on TYPE_LOOKUP_CODE. The four possible values are PO_STANDARD (from STANDARD), PA_BLANKET (from BLANKET), PA_CONTRACT (from CONTRACT), and PA_CONTRACT for a CONTRACT-type lookup. Note that only these mappings are produced by the view.
  • DOCUMENT_VERSION — The REVISION_NUM from the source header, representing the version number of that document revision.
  • STATUS — The AUTHORIZATION_STATUS of the header at that revision, indicating the approval state of the document version.
  • ARCHIVED_YN — A flag ('Y' or 'N') indicating whether the row originates from the archive table or from the live headers table.

Common Use Cases and Queries

Typical scenarios include reconstructing the revision history of a contract, auditing which versions were approved, and identifying whether a given version is archived. A common query retrieves the full version list for a specific document:

SELECT document_id, document_type, document_version, status, archived_yn
FROM   apps.po_document_versions_v
WHERE  document_id = :p_po_header_id
ORDER BY document_version;

To list only active (non-archived) headers of a particular type:

SELECT document_id, document_version, status
FROM   apps.po_document_versions_v
WHERE  document_type = 'PA_CONTRACT'
AND    archived_yn = 'N';

To count archived versions per contract:

SELECT document_id, COUNT(*) archived_versions
FROM   apps.po_document_versions_v
WHERE  archived_yn = 'Y'
GROUP BY document_id;

Because the view is defined over synonyms in APPS, reports and integrations should reference it as APPS.PO_DOCUMENT_VERSIONS_V and treat results as read-only. Analysts should be aware that only STANDARD, BLANKET, and CONTRACT lookup codes are mapped; any other type yields a null DOCUMENT_TYPE, and the view's revision comparison logic assumes archive rows are keyed by the same PO_HEADER_ID as the live header.