Results for “ahl_doc_file_assoc_b”

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, the AHL.AHL_DOC_FILE_ASSOC_B table is the base (non-translated) table owned by the AHL schema within the Complex Maintenance Repair and Overhaul (CMRO) product module. It stores associations between files and documents, a design element introduced as part of the 11.5.10 enhancements. Its role is to act as the physical repository of linkage records that tie an uploaded or referenced file, identified through its FND_LOBS entry, to a specific document revision. This allows a single document to carry multiple attached files and allows a file reference to participate in a controlled revision lifecycle.

The object is documented as VALID with a Primary Key of AHL_DOC_FILE_ASSOC_B_PK1 (ASSOCIATION_ID). Under the heuristic Data Vault classification mined from the FK structure, it is best modeled as a link table — a many-to-many junction bridging a file (represented by FILE_ID referencing FND_LOBS) and a document revision (REVISION_ID referencing AHL_DOC_REVISIONS_B).

Key Information Stored

The table publishes 28 documented columns. Collectively they carry the surrogate key, the business relationship, and standard Oracle EBS audit and descriptive flexfield attributes. The most significant are:

• ASSOCIATION_ID — the surrogate primary key and sole component of AHL_DOC_FILE_ASSOC_B_PK1. No separate unique business key is documented for this table; the unique identity of the row is the surrogate key, while the business relationship is expressed by the (REVISION_ID, FILE_ID) pair.
• REVISION_ID — foreign key to AHL_DOC_REVISIONS_B, identifying the document revision that owns or receives the file association.
• FILE_ID — foreign key to FND_LOBS, identifying the underlying file stored by the Oracle EBS LOB data model.
• FILE_NAME — the display name of the file as presented in the attachment UI, which may differ from the FND_LOBS stored value.
• DATATYPE_CODE — a code that determines the type or category of the associated file.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing the multi-org / security-group isolation across the association.
• OBJECT_VERSION_NUMBER — the optimistic locking indicator used by the EBS framework to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — the WHO columns that provide the standard audit trail for the row.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield (DFF) segments used for customer-specific extensions.

The translated companion table AHL_DOC_FILE_ASSOC_TL carries ASSOCIATION_ID as a shared key and provides language-specific descriptive text.

Common Use Cases and Queries

Typical reporting and integration scenarios retrieve attachments for a document revision, or locate the document context of a given file. A representative query joins the base table to the revision and LOB layers:

SELECT b.ASSOCIATION_ID,
       b.FILE_NAME,
       b.DATATYPE_CODE,
       r.DOCUMENT_ID
  FROM AHL.AHL_DOC_FILE_ASSOC_B b,
       AHL.AHL_DOC_REVISIONS_B r,
       FND_LOBS l
 WHERE b.REVISION_ID = r.REVISION_ID
   AND b.FILE_ID = l.FILE_ID;

Additional patterns include counting attachments per document revision, identifying orphaned associations (rows whose REVISION_ID no longer resolves), auditing SECURITY_GROUP_ID boundaries, and validating OBJECT_VERSION_NUMBER conflicts during concurrent maintenance. Because the association is security-group scoped, query predicates should include SECURITY_GROUP_ID when multi-org isolation is required.

Related Objects

The following objects are the most significant dependents and referenced parents, based on the documented FK and PK relationships:

• AHL_DOC_REVISIONS_B — linked via AHL_DOC_FILE_ASSOC_B.REVISION_ID; the document revision that the association belongs to.
• FND_LOBS — linked via AHL_DOC_FILE_ASSOC_B.FILE_ID; the physical storage record for the attached file.
• AHL_DOC_FILE_ASSOC_TL — the translated table; shares AHL_DOC_FILE_ASSOC_TL.ASSOCIATION_ID with the base table's primary key.
• FND_SECURITY_GROUPS — linked via AHL_DOC_FILE_ASSOC_B.SECURITY_GROUP_ID; enforces organizational security scope.
• AHL_DOC_FILE_ASSOC_B_PK1 — the primary key constraint that enforces uniqueness on ASSOCIATION_ID.

Attribute columns (ATTRIBUTE1–15) and WHO columns also integrate implicitly with concurrent-program and audit reporting utilities across the EBS instance.