Results for “ahl_doc_revisions_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

In Oracle E-Business Suite 12.1.1 and 12.2.2, AHL_DOC_REVISIONS_V is a reporting view owned by the APPS schema and registered under the AHL (Complex Maintenance Repair and Overhaul) product family. Its documented description is "Documents Revision View," indicating that it exposes revision-level metadata for controlled documents managed within the AHL document framework. The view presents each document revision alongside denormalized, language-aware lookup descriptions and the party identity of the approving person, sparing report developers and integration consumers the effort of manually joining the underlying revision entity to lookups and party tables.

The object is marked VALID and is fully documented in ETRM for the 12.2.2 reference environment. Because APPS-owned views are conventionally granted to the APPS-tier responsibilities and often exposed through the APPS synonym interface, AHL_DOC_REVISIONS_V is typically consumed by concurrent programs, Oracle Reports/BI Publisher layouts, OBIEE or Discoverer extracts, and custom PL/SQL that queries it under the APPS schema. A distinguishing behavior is that the view computes an effective revision status at query time: any revision whose OBSOLETE_DATE has passed is reported as OBSOLETE regardless of the stored status code, so consumers always see a status consistent with the current date.

Underlying Base Objects

The documented 12.2.2 dependency list identifies the following referenced objects:

• AHL_DOC_REVISIONS_VL — the base (view-language) revision entity supplying all revision attributes, including identifiers, dates, security group, and the DFF attribute columns 1–15.
• AHL_HZ_PER_EMPLOYEES_V — the Trading Community Architecture (HZ) party/employee source used to resolve the approver's PARTY_NUMBER and PARTY_NAME.
• FND_LOOKUP_VALUES_VL — joined three times to decode REVISION_TYPE_CODE, REVISION_STATUS_CODE, and MEDIA_TYPE_CODE into user-facing meanings.
• AHL_DI_DOC_INDEX_PVT (package) — the AHL document indexing API associated with the document/revision flow.
• HR_GENERAL, HR_PERSON_NAME, HR_SECURITY (packages) — supplied by the AHL_HZ_PER_EMPLOYEES_V and lookup layers and central to person-name formatting and security enforcement.

All lookup joins are outer joins keyed on lookup type AHL_REVISION_STATUS_TYPE, AHL_REVISION_TYPE, and AHL_MEDIA_TYPE respectively, ensuring revisions still appear even when a code is not yet defined in the lookup set. The party join is likewise outer, so revisions without an approver are not filtered out.

Key Columns

• DOC_REVISION_ID / DOCUMENT_ID — primary key of the revision and the parent document identifier.
• REVISION_NO — the user-visible revision label for the document.
• REVISION_TYPE_CODE and its MEANING — classification of the revision (for example, preliminary versus released), with meaning resolved from FND_LOOKUP_VALUES_VL.
• REVISION_STATUS_CODE and its MEANING — the calculated status; the stored code is returned as OBSOLETE once OBSOLETE_DATE has elapsed, and the MEANING column reflects that calculated value.
• APPROVED_BY_PARTY_ID, PARTY_NUMBER, PARTY_NAME — approver identity sourced from HZ through AHL_HZ_PER_EMPLOYEES_V.
• REVISION_DATE, APPROVED_DATE, EFFECTIVE_DATE, OBSOLETE_DATE, ISSUE_DATE, RECEIVED_DATE — the lifecycle dates of the revision.
• MEDIA_TYPE_CODE and its MEANING, URL, VOLUME, ISSUE, ISSUE_NUMBER, COMMENTS — media and bibliographic descriptors of the controlled document.
• SECURITY_GROUP_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1–15 — multi-org security key and descriptive flexfield attributes.
• ROW_ID, OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard OAF/WHO audit columns, including the ROWID pseudo-column exposed as ROW_ID for optimistic locking.

Common Use Cases and Queries

Typical uses include document revision status dashboards, audit extracts of approver history, and interfaces that publish revision metadata to external archives or document-management systems. A basic listing of active revisions with their meanings is shown below.

• Active revisions by document: SELECT DOCUMENT_ID, REVISION_NO, REVISION_STATUS_CODE, MEANING, EFFECTIVE_DATE FROM AHL_DOC_REVISIONS_V WHERE REVISION_STATUS_CODE = 'RELEASED' ORDER BY DOCUMENT_ID, REVISION_NO;
• Revisions audited by approver: SELECT DOCUMENT_ID, REVISION_NO, PARTY_NUMBER, PARTY_NAME, APPROVED_DATE FROM AHL_DOC_REVISIONS_V WHERE APPROVED_DATE >= :p_from_date;
• Expiring or expiring-now revisions: SELECT DOCUMENT_ID, REVISION_NO, OBSOLETE_DATE FROM AHL_DOC_REVISIONS_V WHERE OBSOLETE_DATE BETWEEN SYSDATE AND SYSDATE + 30;

Because status is evaluated at runtime, filters on REVISION_STATUS_CODE return OBSOLETE for expired revisions even if the underlying AHL_DOC_REVISIONS_VL row still stores the original code. Queries should therefore be written against the view's calculated column, not the base table, whenever current status is required.