Results for “as_relationships”

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

Overview

AS_RELATIONSHIPS is a table in the Oracle E-Business Suite 12.1.1 / 12.2.2 database, owned by the OSM schema and belonging to the AS – Sales Foundation product family. As its description states, the table stores relationships between entities. In practice, it functions as a generic association repository in which any two business objects in the Sales Foundation domain can be linked through a typed relationship, rather than requiring a dedicated intersection table for each pairing. This design gives the module a flexible mechanism for expressing lineage, dependencies, hierarchies, and cross-references among quotes and related sales artifacts.

From a dimensional modeling perspective, the ETRM metadata classifies AS_RELATIONSHIPS heuristically as standalone in Data Vault terms. This classification is a modeling suggestion rather than a mandated construct; because the table carries its own surrogate key and no parent link beyond the documented foreign key, it does not decompose cleanly into a hub, link, or satellite. It is best treated as an independent relationship registry whose rows are meaningful in isolation.

Key Information Stored

The table contains 32 documented columns. The most significant for integration and reporting purposes are:

• RELATIONSHIP_ID — the surrogate primary key, enforced by the AS_RELATIONSHIPS_PK constraint and also the sole business-key candidate documented through the unique index AS_RELATIONSHIPS_U1. Every relationship row is uniquely identified by this value.
• OBJECT_TYPE_CODE and OBJECT_ID — the type and identifier of the source entity in the relationship.
• RELATED_OBJECT_TYPE_CODE and RELATED_OBJECT_ID — the type and identifier of the target entity. RELATED_OBJECT_ID participates in the documented foreign key to ASO_QUOTE_RELATED_OBJECTS.
• RELATIONSHIP_TYPE_CODE — the semantic category that qualifies how the two entities are associated.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context identifying the process that last touched the row.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard extensibility (flexfield) columns for customer-specific data.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/ADF framework.

Common Use Cases and Queries

Typical usage includes tracing how a quote or sales document relates to other objects, reconstructing dependency chains, and auditing relationship creation or modification over time. A common pattern resolves both ends of the relationship by joining the type codes to their lookup meanings and filtering on the relationship type:

SELECT r.RELATIONSHIP_ID,
       r.OBJECT_TYPE_CODE,
       r.OBJECT_ID,
       r.RELATED_OBJECT_TYPE_CODE,
       r.RELATED_OBJECT_ID,
       r.RELATIONSHIP_TYPE_CODE,
       r.CREATION_DATE
FROM   OSM.AS_RELATIONSHIPS r
WHERE  r.RELATIONSHIP_TYPE_CODE = :type_code
AND    r.OBJECT_ID = :object_id;

Reporting queries frequently join RELATED_OBJECT_ID to ASO_QUOTE_RELATED_OBJECTS to obtain the quote-side detail, and use the audit columns to isolate rows created or changed by a given concurrent program. The ATTRIBUTE columns allow customer-specific extensions to be surfaced in custom reports without schema changes.

Related Objects

• ASO_QUOTE_RELATED_OBJECTS — referenced through the documented foreign key AS_RELATIONSHIPS.RELATED_OBJECT_ID → ASO_QUOTE_RELATED_OBJECTS. This is the primary join target.
• AS_RELATIONSHIPS_PK — the primary key constraint on RELATIONSHIP_ID.
• AS_RELATIONSHIPS_U1 — the unique index that also enforces uniqueness on RELATIONSHIP_ID.
• FND_LOOKUP_VALUES — supplies the meanings for OBJECT_TYPE_CODE, RELATED_OBJECT_TYPE_CODE, and RELATIONSHIP_TYPE_CODE.
• FND_USER — resolves CREATED_BY and LAST_UPDATED_BY to user names.
• FND_CONCURRENT_REQUESTS — links REQUEST_ID and PROGRAM_ID to the concurrent program execution that processed the row.
• ASO_QUOTE_HEADERS — the quote header table reached indirectly through the related-object association.

Together these objects support querying, resolving, and auditing the entity relationships maintained in AS_RELATIONSHIPS.