Search Results dom_doc_rev_versions_n1

Overview

The DOM.DOM_DOC_REV_VERSIONS table is a core transactional structure within the Oracle E-Business Suite Document Management (DOM) module. It stores the revision version number associated with each document revision, functioning as the authoritative record that links a specific revision of a document to its corresponding version in the underlying versioning framework. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, reflecting its role as an actively maintained transactional table with UPDATE activity on existing rows.

From a Data Vault modeling perspective, the mined foreign key structure classifies this object as standalone. However, the presence of a foreign key from VERSION_ID to VEA_VERSIONS, combined with unique business-key candidates on VERSION_ID and on (REVISION_ID, VERSION_ID), suggests this table behaves functionally as a link/satellite hybrid — linking document revisions to versions while carrying descriptive attributes such as VERSION, COMMENTS, and STATUS_CODE. In Oracle EBS 12.1.1 and 12.2.2, the table is owned by the DOM schema and is exposed to the APPS schema through the DOM_DOC_REV_VERSIONS synonym, enabling standard application-layer access.

Key Information Stored

The table contains eleven documented columns, of which the following are most significant:

• VERSION_ID (NUMBER(15)) — The version identifier. This is the single most constrained column, carrying the unique index DOM_DOC_REV_VERSIONS_U2 and also participating in the composite unique index DOM_DOC_REV_VERSIONS_U1. The FK relationship confirms it joins to VEA_VERSIONS.
• REVISION_ID (NUMBER(15)) — The revision internal identifier. It anchors the composite unique key DOM_DOC_REV_VERSIONS_U1 alongside VERSION_ID.
• DOCUMENT_ID (NUMBER(15)) — The document internal identifier, indexed non-uniquely via DOM_DOC_REV_VERSIONS_N1.
• VERSION (NUMBER(15)) — The human-readable version label, distinct from the surrogate VERSION_ID.
• STATUS_CODE (VARCHAR2(30)) — Lifecycle status of the revision-version association (for example, draft, released, or obsolete).
• COMMENTS (VARCHAR2(1000)) — Free-text annotations attached to the version record.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns supporting change tracking and audit reporting.

The metadata does not document a single-column surrogate primary key. The closest business-key candidates are the composite unique index U1 (REVISION_ID, VERSION_ID) and the single-column unique index U2 (VERSION_ID). A true hub key would typically be a system-generated identifier; here, VERSION_ID effectively serves that role given its U2 uniqueness.

Common Use Cases and Queries

Typical use cases include retrieving all versions for a given revision, reconciling version labels against the VEA_VERSIONS master, and reporting on revision status changes over time.

To retrieve all versions for a revision:

• SELECT VERSION_ID, VERSION, STATUS_CODE, COMMENTS FROM DOM.DOM_DOC_REV_VERSIONS WHERE REVISION_ID = :revision_id ORDER BY VERSION;

To resolve a version identifier to its descriptive attributes and underlying version record:

• SELECT d.VERSION_ID, d.VERSION, d.STATUS_CODE, v.* FROM DOM.DOM_DOC_REV_VERSIONS d JOIN VEA_VERSIONS v ON d.VERSION_ID = v.VERSION_ID WHERE d.VERSION_ID = :version_id;

To enumerate all versions for a document:

• SELECT DOCUMENT_ID, REVISION_ID, VERSION_ID, VERSION FROM DOM.DOM_DOC_REV_VERSIONS WHERE DOCUMENT_ID = :document_id ORDER BY REVISION_ID, VERSION;

Audit reporting frequently filters on LAST_UPDATE_DATE to identify recently altered version assignments, leveraging the non-unique indexes N1 and N2 for document- and revision-scoped access paths.

Related Objects

The most significant related objects are:

• VEA_VERSIONS — referenced via DOM_DOC_REV_VERSIONS.VERSION_ID → VEA_VERSIONS.VERSION_ID; the master versioning repository.
• APPS.DOM_DOC_REV_VERSIONS — the APPS-schema synonym through which application code and reports access the table.
• The DOM document and revision master tables, joined on DOCUMENT_ID and REVISION_ID respectively, provide document and revision context.
• FND Design Data: DOM.DOM_DOC_REV_VERSIONS — the design-time registration enabling standard EBS patching and dependency management.
• Associated DOM document management APIs that create and update revision-version associations during check-in, check-out, and revision-promotion workflows.

Because the table is standalone in FK terms apart from the VEA_VERSIONS link, join paths are driven primarily by the document and revision identifiers rather than by cascading relationships.