Search Results ahl_doc_file_assoc

Overview

AHL_DOC_FILE_ASSOC_VL is a seeded, VALID view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the AHL product family — Complex Maintenance Repair and Overhaul (CMRO) — which manages the maintenance, documentation, and configuration of complex assets such as aircraft and defense equipment. The view presents document-to-file association data, exposing descriptive, translatable metadata for files that are linked to documents within the CMRO document management framework. Its principal architectural role is to satisfy Oracle's MLS (Multi-Language Support) convention: the "_VL" suffix denotes a "View Language" that joins a base (_B) table containing language-independent data with a translation (_TL) table containing language-dependent text. This design allows a single query to return file records together with descriptions resolved to the session's current language.

In EBS reporting and integration, the view is typically consumed by Oracle Forms, OAF pages, concurrent programs, and custom BI Publisher or Discoverer reports that need user-facing document/file listings. Because it encapsulates the language join and the ROWID-based DML support pattern, it also supports updateable-view behavior for the file description column when accessed through the standard AHL application stack.

Underlying Base Objects

The view is defined over two documented base objects, both referenced through APPS synonyms:

• AHL_DOC_FILE_ASSOC_B — the base table (alias DFAB) holding language-independent attributes: association identity, file identifiers, revision, datatype code, security grouping, audit columns, and the standard 15 descriptive flexfield (DFF) attribute columns.
• AHL_DOC_FILE_ASSOC_TL — the translation table (alias DFAT) holding the language-dependent FILE_DESC value, keyed by language.

The join predicate is DFAB.ASSOCIATION_ID = DFAT.ASSOCIATION_ID, combined with DFAT.LANGUAGE = USERENV('LANG'). The USERENV('LANG') call restricts output to the single translation row matching the current session language, ensuring one row per association. The view also exposes DFAB.ROWID, which supports the "instead-of" style DML mapping used by Oracle's MLS views. No additional tables, lookup joins, or outer joins are present, so the view remains a straightforward B/TL union of data.

Key Columns

• ROW_ID / ROWID — surrogate row identifier used for DML on the view and for standard EBS row-locking.
• ASSOCIATION_ID — primary key linking a document to its file; the join key between the B and TL tables.
• OBJECT_VERSION_NUMBER — optimistic locking value for concurrent update control.
• FILE_ID / FILE_NAME — identifies the file and its physical/display name.
• REVISION_ID — revision context for the associated file or document.
• DATATYPE_CODE — classification of the associated data type (for example, the nature of the document/file content).
• FILE_DESC — the translatable description sourced from the _TL table and rendered in the session language.
• SECURITY_GROUP_ID — supports multi-tenant/security grouping partitioning.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1 … ATTRIBUTE15 — descriptive flexfield segments for user-defined extensions.
• ROW_ID also appears in the documented column list, reflecting the ROWID-based lineage of the view.

Common Use Cases and Queries

Typical scenarios include listing all files associated with a given document, resolving file descriptions in the user's session language, auditing associations by creation or update date, and extracting DFF attribute data for reporting.

-- All files associated with a specific document association
SELECT association_id, file_id, file_name, revision_id,
       datatype_code, file_desc
FROM   apps.ahl_doc_file_assoc_vl
WHERE  association_id = :p_association_id;

-- Recently created file associations with the current-language description
SELECT association_id, file_name, file_desc,
       created_by, creation_date
FROM   apps.ahl_doc_file_assoc_vl
WHERE  creation_date >= SYSDATE - 30
ORDER  BY creation_date DESC;

-- Audit/report of DFF attribute usage across associations
SELECT association_id, datatype_code,
       attribute_category, attribute1, attribute2
FROM   apps.ahl_doc_file_assoc_vl
WHERE  attribute_category IS NOT NULL;

Because the view filters on USERENV('LANG'), results are inherently language-scoped; reports requiring all translations should query AHL_DOC_FILE_ASSOC_TL directly.