Results for “ahl_doc_type”

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

Overview

APPS.AHL_REFERENCE_DOC_V is a consolidated reporting view within the Oracle E-Business Suite (EBS) Enterprise Asset Management (eAM) / Asset Lifecycle (AHL) module. In Oracle EBS 12.1.1 and 12.2.2, this view exposes reference documents and their associated title-level metadata, revisions, and file attachments in a single denormalized result set. Its primary purpose is to provide a reporting and integration access point across document headers, document title associations, revision control data, descriptive lookup values, and attached files.

The view is defined as a UNION of two SELECT statements. The first branch drives from AHL_DOCUMENTS_VL and uses outer joins (denoted by the (+) operator) to link document revisions, title associations, lookup descriptions, and file associations. The second branch reverses the driving table precedence, starting from AHL_DOC_TITLE_ASSOS_VL and outer joining to documents where the revision association may be null. This two-branch construction ensures that documents are returned whether or not a revision record exists, and whether or not a title association exists. Because of the outer joins and lookup enrichment, AHL_REFERENCE_DOC_V is a read-only view suitable for operational reporting, custom concurrent programs, and integration extracts rather than for direct DML.

Underlying Base Objects

The view is defined over six documented base objects, all owned by APPS:

• AHL_DOCUMENTS_VL (VIEW) — the document master, providing DOCUMENT_ID, DOCUMENT_NO, DOC_TYPE_CODE, and DOCUMENT_TITLE.
• AHL_DOC_TITLE_ASSOS_VL (VIEW) — title-association detail such as CHAPTER, SECTION, SUBJECT, PAGE, FIGURE, and NOTE.
• AHL_DOC_REVISIONS_B (SYNONYM) — revision records including DOC_REVISION_ID, REVISION_NO, and REVISION_STATUS_CODE.
• AHL_DOC_FILE_ASSOC_VL (VIEW) — file associations returning FILE_ID and FILE_NAME for referenced documents.
• FND_LOOKUP_VALUES_VL (VIEW) — referenced three times to translate coded values into meanings. The lookup types consumed are AHL_DOC_TYPE, AHL_OBJECT_TYPE, and AHL_USE_LATEST_REV_FLAG.

The relationships follow the natural hierarchy of document → revision → title association → file, with lookup joins supplying descriptive text. All lookup joins are outer joins, so rows are preserved even when a lookup code has no matching value.

Key Columns

Important columns exposed by the view include:

• DOCUMENT_ID, DOCUMENT_NO, DOCUMENT_TITLE — document identity and descriptive header.
• DOC_TYPE_CODE, DOC_TYPE_DESCRIPTION — the document type code and its decoded meaning, driven by the user-searched lookup type AHL_DOC_TYPE. This pair is frequently used to filter reference documents by category.
• DOC_REVISION_ID, REVISION_NO, REVISION_STATUS_CODE — revision identity and status.
• DOC_TITLE_ASSO_ID, OBJECT_VERSION_NUMBER, ASO_OBJECT_ID, ASO_OBJECT_TYPE_CODE, ASO_OBJECT_TYPE_DESC — the associated source object and its decoded object type.
• CHAPTER, SECTION, SUBJECT, PAGE, FIGURE, NOTE — structural location within the document where the association applies.
• USE_LATEST_REV_FLAG, USE_LATEST_REV_FLAG_MEANING — flag and decoded meaning indicating whether the latest revision is used.
• FILE_ID, FILE_NAME — attached file reference, restricted by DATATYPE_CODE = 'FILE'.

Common Use Cases and Queries

Typical usage involves retrieving documents of a specific type, locating attached files for a document revision, or listing title associations for a given document.

Retrieve all documents of a particular type:

SELECT DOCUMENT_NO, DOCUMENT_TITLE, DOC_TYPE_CODE,
       DOC_TYPE_DESCRIPTION, REVISION_NO
FROM   APPS.AHL_REFERENCE_DOC_V
WHERE  DOC_TYPE_CODE = '&doc_type';

List attachments for a known document:

SELECT DOCUMENT_NO, FILE_NAME, REVISION_NO
FROM   APPS.AHL_REFERENCE_DOC_V
WHERE  DOCUMENT_ID = :p_document_id
AND    FILE_ID IS NOT NULL;

Report title associations with location detail:

SELECT DOCUMENT_NO, ASO_OBJECT_TYPE_DESC, CHAPTER,
       SECTION, SUBJECT, USE_LATEST_REV_FLAG_MEANING
FROM   APPS.AHL_REFERENCE_DOC_V
ORDER  BY DOCUMENT_NO, REVISION_NO;

Because the view contains UNION logic and multiple outer joins, queries should apply restrictive predicates such as DOCUMENT_ID or DOC_TYPE_CODE to limit scanning overhead. The view is not updatable; modifications must target the underlying AHL document and revision tables through the supported AHL module or APIs.