Search Results ahl_doc_revisions_b




Overview
Explore in AI Chat

Overview

The AHL_DOC_REVISIONS_B table is a core data object within the Oracle E-Business Suite module for Complex Maintenance, Repair, and Overhaul (AHL). It serves as the master repository for storing revision-level metadata of documents. In the context of asset-intensive industries like aviation or heavy machinery, where maintenance procedures, service bulletins, and technical manuals are strictly version-controlled, this table is critical. It enables the systematic tracking of document evolution, ensuring that maintenance operations reference the correct and approved revision of a document. Its role is to maintain the integrity of the document revision history, separate from the document header information stored in AHL_DOCUMENTS_B and the associated file attachments.

Key Information Stored

The table's primary key is DOC_REVISION_ID, a unique system-generated identifier for each revision record. A critical composite unique key (DOCUMENT_ID, REVISION_NO) enforces that within a single document, each revision number is unique. Key columns include DOCUMENT_ID, which links to the parent document in AHL_DOCUMENTS_B, and REVISION_NO, which stores the specific revision identifier (e.g., 'A', 'B', '1', '2'). The APPROVED_BY_PARTY_ID column is a foreign key to HZ_PARTIES, storing the identifier for the party (person or organization) who approved this specific revision. Other typical columns would include effective dates for the revision, revision status, and creation/update audit information, though these are not explicitly detailed in the provided metadata.

Common Use Cases and Queries

A primary use case is retrieving the complete revision history for a specific document for audit or compliance reporting. Another is identifying the current, effective revision of a document for use in a work order or maintenance procedure. Common queries include finding the latest revision for a set of documents or listing all revisions pending approval. Sample SQL patterns often involve joins to the document header and translation tables.

  • To fetch all revisions for a document: SELECT * FROM AHL_DOC_REVISIONS_B WHERE DOCUMENT_ID = :doc_id ORDER BY REVISION_NO;
  • To find the latest approved revision: SELECT MAX(REVISION_NO) KEEP (DENSE_RANK LAST ORDER BY EFFECTIVE_START_DATE) FROM AHL_DOC_REVISIONS_B WHERE DOCUMENT_ID = :doc_id AND STATUS = 'APPROVED';

Related Objects

The AHL_DOC_REVISIONS_B table is central to the AHL document management schema, with defined relationships to several key objects.

  • AHL_DOCUMENTS_B: Parent table. Joined via AHL_DOC_REVISIONS_B.DOCUMENT_ID = AHL_DOCUMENTS_B.DOCUMENT_ID.
  • HZ_PARTIES: Provides the "Approved By" entity details. Joined via AHL_DOC_REVISIONS_B.APPROVED_BY_PARTY_ID = HZ_PARTIES.PARTY_ID.
  • AHL_DOC_FILE_ASSOC_B: Child table linking physical files to a specific revision. Joined via AHL_DOC_REVISIONS_B.DOC_REVISION_ID = AHL_DOC_FILE_ASSOC_B.REVISION_ID.
  • AHL_DOC_REVISIONS_TL: Child table for translated columns of the revision. Joined via AHL_DOC_REVISIONS_B.DOC_REVISION_ID = AHL_DOC_REVISIONS_TL.DOC_REVISION_ID.
  • AHL_DOC_REVISION_COPIES & AHL_DOC_TITLE_ASSOS_B: Additional child tables referencing the revision record.