Results for “ahl_document_assos_v”

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

Overview

The AHL_DOCUMENT_ASSOS_V view resides in the APPS schema and belongs to the AHL product family — Complex Maintenance Repair and Overhaul (CMRO). It is a reporting and integration construct that exposes document-to-object associations together with their descriptive lookups, allowing concurrent programs, forms, OAF pages, and external integrations to resolve a document association without joining four separate base objects. The view is documented as VALID in Oracle EBS 12.1.1 and 12.2.2 and is not a table, so it stores no data of its own; every row is derived at query time from the underlying documents, revisions, and title association entities.

Its principal purpose is to present, in a single flattened result set, the document header, the revision context, and the associated object (for example a work order, asset, or maintenance activity), together with human-readable meanings decoded from FND_LOOKUP_VALUES_VL. This makes the view a natural source for BI Publisher reports, OAF search regions, and interface extracts that must translate lookup codes such as DOC_TYPE_CODE, ASO_OBJECT_TYPE_CODE, and SOURCE_REF_CODE into display text.

A notable structural characteristic of the view is that it is defined as a UNION of two branches. The first branch joins documents to revisions in the ordinary manner, while the second branch targets title associations whose DOC_REVISION_ID IS NULL, ensuring documents that have associations at the title level but no revision context are also surfaced. Outer joins ((+)) are applied throughout to preserve associations even when a revision or lookup meaning is absent.

Underlying Base Objects

The documented base objects underlying the view are:

• AHL_DOCUMENTS_VL (VIEW) — supplies document identity, document number, document title, and document type code.
• AHL_DOC_TITLE_ASSOS_VL (VIEW) — supplies the association records, including object ID, object type, source reference, and the chapter/section/subject/figure/page/note attributes.
• AHL_DOC_REVISIONS_B (SYNONYM to the base table) — supplies revision identifiers and revision numbers.
• FND_LOOKUP_VALUES_VL (VIEW) — the standard Oracle lookup view, joined three times to decode document type, associated object type, and source reference.

Additional metadata lists AHL_DOC_FILE_ASSOC_B as a referenced base object; this is the underlying file attachment association used by the CMRO document management model and is commonly involved in related document queries. The view depends only on these objects and inherits the security and language behaviour of the _VL views it consumes; consequently, rows returned depend on the session's language and on the translations available in the corresponding _TL tables.

Key Columns

• DOCUMENT_ID / DOCUMENT_NO / DOCUMENT_TITLE — document surrogate key, user-visible number, and descriptive title.
• DOC_TITLE_ASSO_ID / OBJECT_VERSION_NUMBER — primary key of the title association and its optimistic locking version, used by OAF for update concurrency.
• ASO_OBJECT_ID / ASO_OBJECT_TYPE_CODE — the associated object's key and its type code, decoded into ASO_OBJECT_TYPE_DESC via the AHL_OBJECT_TYPE lookup.
• DOC_REVISION_ID / REVISION_NO — the revision a given association applies to; may be null for title-level associations.
• DOC_TYPE_CODE / DOC_TYPE_DESCRIPTION — document classification decoded through the AHL_DOC_TYPE lookup.
• SOURCE_REF_CODE / SOURCE_MEANING — origin of the document association, decoded through the AHL_DI_SOURCE_REF lookup.
• CHAPTER, SECTION, SUBJECT, FIGURE, PAGE, NOTE — fine-grained locators that point a reader to the exact place in the document where the associated object is referenced.

Common Use Cases and Queries

Typical scenarios include reporting all documents associated with a maintenance work order or asset, building a "where used" index for a controlled document, and extracting association data for downstream document-control systems.

SELECT document_no,
       document_title,
       revision_no,
       aso_object_id,
       aso_object_type_desc,
       source_meaning,
       chapter, section, subject
FROM   apps.ahl_document_assos_v
WHERE  aso_object_type_code = 'WORKORDER'
AND    aso_object_id = :p_object_id;

To list every distinct document attached to each object type, the view can be aggregated by ASO_OBJECT_TYPE_CODE and ASO_OBJECT_ID. Because the second UNION branch returns associations with a null revision, queries intended to report only revision-controlled documents should add WHERE doc_revision_id IS NOT NULL. When the view is used in concurrent programs or interface tables, the OBJECT_VERSION_NUMBER column should be carried through so that subsequent updates against the association can be validated against the correct version.

Because all joins to FND_LOOKUP_VALUES_VL are outer joins, consumers should not assume that DOC_TYPE_DESCRIPTION, ASO_OBJECT_TYPE_DESC, or SOURCE_MEANING are populated; missing lookup rows produce nulls rather than excluded records. Reports that require a display value should therefore apply NVL to the code column as a fallback.