Search Results ahl_item_composition_status

Overview

The APPS.AHL_ITEM_COMP_V view is a reporting and integration layer over the item composition model in Oracle E-Business Suite, part of the Oracle Enterprise Asset Management / Asset Lifecycle (AHL) product family. It exposes the composition (bill-of-materials style) relationships recorded in AHL_ITEM_COMPOSITIONS, resolved against inventory item master data, and it decorates the raw approval status code with its lookup meaning and description. A defining characteristic of this view is that it computes a derived approval status rather than simply echoing the stored value: using a DECODE(SIGN(...)) expression, the view returns the stored APPROVAL_STATUS_CODE only while the composition is still effective, and substitutes the literal 'EXPIRED' once the effective end date has passed. Because the user searched on "ahl_item_composition_status", this view is the canonical place where that status — including the system-generated expired state — is exposed for reporting. It is owned by APPS and intended for querying and integration rather than for direct DML.

Underlying Base Objects

The view is documented in ETRM 12.2.2 as being defined over three referenced objects. AHL_ITEM_COMPOSITIONS (referenced as a synonym) is the primary transactional base and supplies the composition identifier, item and master organization keys, draft flag, approval status code, effective end date, link composition ID, audit columns, security group, and the fifteen descriptive flexfield attributes. AHL_MTL_ITEMS_NON_OU_V is a view that provides inventory item context independently of the operating unit — organization name and code, the concatenated item segments, and the item description — and is joined on INVENTORY_ITEM_ID and matching organization. FND_LOOKUP_VALUES_VL is the translated lookup view joined with an outer-join condition on LOOKUP_TYPE = 'AHL_ITEM_COMPOSITION_STATUS', supplying the user-facing meaning and description for the derived status. The ETRM metadata additionally records HR_GENERAL and HR_SECURITY packages as referenced objects, which reflect the security (security group / organization access) enforcement applied to the underlying item views.

Key Columns

• ITEM_COMPOSITION_ID — primary key of the composition record; the unique handle for a parent-child item relationship.
• INVENTORY_ITEM_ID / INVENTORY_MASTER_ORG_ID — the item and its master organization that the composition is defined against.
• APPROVAL_STATUS_CODE — the derived status: the stored code while effective, otherwise 'EXPIRED'. This is the column relevant to "ahl_item_composition_status".
• APPROVAL_STATUS_MEANING / APPROVAL_STATUS_DESC — lookup meaning and description for the status, from FND_LOOKUP_VALUES_VL.
• DRAFT_FLAG — indicates whether the composition is still in draft and not yet released.
• EFFECTIVE_END_DATE — the expiry boundary used in the status computation; NULL is treated as not-yet-expired via NVL(..., SYSDATE+1).
• ORGANIZATION_NAME, ORGANIZATION_CODE, CONCATENATED_SEGMENTS, DESCRIPTION — inventory item and organization context.
• LINK_COMP_ID — links related composition records, typically the reciprocal or counterpart entry.
• Audit and extensibility columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, SECURITY_GROUP_ID, and ATTRIBUTE_CATEGORY through ATTRIBUTE15.

Common Use Cases and Queries

Typical usage includes listing all compositions for an item with a friendly status, identifying expired or soon-to-expire compositions, and isolating draft records pending release. A basic listing returns the item context and derived status:

SELECT inventory_item_id,
       organization_code,
       concatenated_segments,
       approval_status_code,
       approval_status_meaning,
       effective_end_date,
       draft_flag
FROM   apps.ahl_item_comp_v
WHERE  inventory_item_id = :item_id;

To surface only expired compositions — the condition the view itself generates — filter directly on the derived value:

SELECT item_composition_id,
       concatenated_segments,
       approval_status_code,
       effective_end_date
FROM   apps.ahl_item_comp_v
WHERE  approval_status_code = 'EXPIRED';

Because the expiry test is evaluated at query runtime, results change as time passes without any underlying data change. Expiry can also be driven relative to a date range, and draft, non-expired components can be isolated by combining the status filter with DRAFT_FLAG. As the view is owned by APPS, queries should qualify the object with the APPS schema or be issued from a session with suitable privileges, and reporting should treat the view as read-only.