Results for “xle_associations_u2”

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

Overview

The XLE.XLE_ASSOCIATIONS table is a core transactional data object within the Oracle E-Business Suite Legal Entity Management (XLE) module, available in EBS 12.1.1 and 12.2.2. It stores the Legal Associations — the relationships that link subjects (such as legal entities, establishments, or other registered organizations) to objects within the trading or corporate hierarchy. Each row captures one association, its classification, its effective dating window, and a set of descriptive flexfield attributes, making this table the authoritative repository for how legal entities are interrelated across the enterprise.

In Data Vault modeling terms, the mined foreign-key structure classifies XLE_ASSOCIATIONS as a standalone object. This is a heuristic suggestion rather than a formal constraint: because the table has only one documented outbound foreign key (SUBJECT_ID referencing IGS_UC_COM_EBL_SUBJ) and no child tables are documented in the metadata, it functions as a self-contained relationship or link-style record rather than a classic hub fed by many satellites. Practitioners modeling this data for a warehouse should treat it as a link spanning association type, subject, and object, with the effective-dated attributes behaving like a satellite.

Key Information Stored

Among the 34 documented columns, the most significant are grouped below. The surrogate primary key is ASSOCIATION_ID, enforced by the unique index XLE_ASSOCIATIONS_U1. The business-key candidate is the four-column composite enforced by XLE_ASSOCIATIONS_U2 (ASSOCIATION_TYPE_ID, SUBJECT_ID, OBJECT_ID, EFFECTIVE_FROM), which uniquely identifies an association instance per effective date.

• ASSOCIATION_ID (NUMBER 15) — Surrogate primary key; legal association identifier.
• ASSOCIATION_TYPE_ID (NUMBER 15) — Foreign key to XLE_ASSOCIATION_TYPES; classifies the nature of the relationship.
• SUBJECT_ID (NUMBER 15) — Identifier of the subject of the association; documented FK to IGS_UC_COM_EBL_SUBJ.
• SUBJECT_PARENT_ID (NUMBER 15) — Parent of the subject; currently stores the Legal Entity ID when the subject type is an Establishment.
• OBJECT_ID (NUMBER 15) — Identifier of the object of the association; foreign key to the base table where subject instances are stored.
• EFFECTIVE_FROM (DATE) — Date from which the association is effective; part of the U2 business key.
• EFFECTIVE_TO (DATE) — Date after which the association ends; supports point-in-time and range queries.
• OBJECT_VERSION_NUMBER (NUMBER) — Optimistic locking version counter used by the XLE APIs.
• ASSOC_INFORMATION_CONTEXT (VARCHAR2 30) — Descriptive flexfield structure definition for the DFF segments.
• ASSOC_INFORMATION1 through ASSOC_INFORMATION20 (VARCHAR2 150) — Developer flexfield segments for extensible attribute storage.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Who columns providing audit and concurrency tracking.

Common Use Cases and Queries

Typical reporting scenarios include resolving the current legal structure of an entity, tracing the establishment-to-legal-entity hierarchy, and auditing association history through the effective-dating columns. A point-in-time query for active associations for a given type follows this pattern:

• SELECT assoc.association_id, assoc.subject_id, assoc.object_id, assoc.effective_from, assoc.effective_to FROM xle.xle_associations assoc WHERE assoc.association_type_id = :p_type_id AND TRUNC(SYSDATE) BETWEEN assoc.effective_from AND NVL(assoc.effective_to, TRUNC(SYSDATE));
• Hierarchy reconstruction joins SUBJECT_PARENT_ID back to SUBJECT_ID to walk establishment trees.
• Duplicate-prevention checks leverage XLE_ASSOCIATIONS_U2 to verify no overlapping effective-dated record exists for the same type, subject, and object.
• DFF-driven reporting filters on ASSOC_INFORMATION_CONTEXT and the corresponding ASSOC_INFORMATIONn segments.
• Audit queries use the Who columns and OBJECT_VERSION_NUMBER to detect concurrent updates.

Because the table resides in APPS_TS_TX_DATA with indexes in APPS_TS_TX_IDX, queries should filter on the indexed columns (ASSOCIATION_ID, or the composite of association type, subject, object, and effective date) to avoid full scans on large volumes.

Related Objects

The following objects reference or depend on XLE_ASSOCIATIONS based on the documented relationship and dependency data:

• XLE.XLE_ASSOCIATION_TYPES — Referenced by ASSOCIATION_TYPE_ID; defines the category of each legal association.
• IGS_UC_COM_EBL_SUBJ — Base table referenced by SUBJECT_ID via the documented foreign key; supplies subject instance data.
• XLE_ASSOCIATIONS_U1 — Unique index on ASSOCIATION_ID enforcing the primary key.
• XLE_ASSOCIATIONS_U2 — Unique composite index (ASSOCIATION_TYPE_ID, SUBJECT_ID, OBJECT_ID, EFFECTIVE_FROM) enforcing the business key.
• XLE_ASSOCIATIONS base table — The object itself at XLE.XLE_ASSOCIATIONS, referenced in FND Design Data for concurrent program and API validation.
• Standard Who column lookups against FND_USER via CREATED_BY and LAST_UPDATED_BY for audit reporting.