Results for “inv_item_id”

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

Overview

The AHL_INV_SERVICE_ITEM_RELS table is a core data object within the Oracle E-Business Suite Complex Maintenance Repair and Overhaul (AHL) product family. It stores the relationships between physical inventory items and service items, effectively defining which inventory item numbers map to which service item offerings within a given inventory organization. This relationship is fundamental to maintenance and repair workflows, where a tangible part (an inventory item) is associated with a service definition that describes the work performed on or with that part.

From a Data Vault modeling perspective, the ETRM metadata classifies this table as satellite-leaning, a heuristic mined from its foreign key structure. This suggests that the table behaves primarily as a descriptive satellite attached to the principal inventory and service item hubs, rather than serving as a pure hub or link. In practice, it functions as an intersection or association table that carries its own descriptive attributes (such as ranking and active date ranges) in addition to the two item references it joins.

Key Information Stored

The table is documented with 30 columns in ETRM 12.2.2. The most significant columns are outlined below.

• INV_SERVICE_ITEM_REL_ID — The surrogate primary key, enforced through the INV_SERVICE_ITEM_REL_PK constraint. It is also the single-column unique index candidate AHL_INV_SERVICE_ITEM_RELS_U1.
• INV_ITEM_ID — The physical inventory item, referencing MTL_SYSTEM_ITEMS_B. Combined with INV_ORG_ID and SERVICE_ITEM_ID, it forms the business-key candidate AHL_INV_SERVICE_ITEM_RELS_U2.
• SERVICE_ITEM_ID — The service item, also referencing MTL_SYSTEM_ITEMS_B.
• INV_ORG_ID — The inventory organization that scopes the relationship and participates in both item foreign keys and the U2 candidate.
• RANK — A priority or ordering indicator for the relationship, typically used where multiple service items map to one inventory item.
• ACTIVE_START_DATE and ACTIVE_END_DATE — Effective-dating columns defining the validity window of the relationship.
• SECURITY_GROUP_ID — References FND_SECURITY_GROUPS, supporting multi-tenant or security-group filtering.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the underlying framework.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle EBS flexfield descriptive attribute set.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — Standard WHO audit columns.

Common Use Cases and Queries

Typical reporting scenarios resolve the physical-to-service item mapping for a maintenance organization. The following pattern joins both item references to the inventory item master and filters on active relationships:

• _Identify all service items associated with a given physical item in an organization:_ SELECT SERVICE_ITEM_ID, RANK FROM AHL.AHL_INV_SERVICE_ITEM_RELS WHERE INV_ORG_ID = :org AND INV_ITEM_ID = :item AND SYSDATE BETWEEN ACTIVE_START_DATE AND NVL(ACTIVE_END_DATE, SYSDATE+1);
• _Resolve descriptive item names_ by joining MTL_SYSTEM_ITEMS_B twice (once on INV_ITEM_ID, once on SERVICE_ITEM_ID).
• _Multi-tenant reporting_ applies a SECURITY_GROUP_ID predicate, consistent with AHL security-group conventions.
• _Relationship integrity and audit_ queries use LAST_UPDATE_DATE and OBJECT_VERSION_NUMBER to detect stale or conflicting rows.

Related Objects

• MTL_SYSTEM_ITEMS_B — Referenced twice via INV_ITEM_ID and SERVICE_ITEM_ID; supplies item descriptions and attributes.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID for row-level security scoping.
• AHL_ITEM_VENDOR_RELS — Child table referencing this table via INV_SERVICE_ITEM_REL_ID; extends the relationship to vendor sourcing.
• AHL_INV_SERVICE_ITEM_RELS_U1 — Unique index on INV_SERVICE_ITEM_REL_ID; confirms the primary-key surrogate.
• AHL_INV_SERVICE_ITEM_RELS_U2 — Unique index on (INV_ORG_ID, INV_ITEM_ID, SERVICE_ITEM_ID); enforces the natural business key.