Search Results link_comp_id

Overview

APPS.AHL_ITEM_COMP_V is a reporting and integration view within the Oracle E-Business Suite product family AHL — Complex Maintenance Repair and Overhaul. It exposes the item composition structure maintained in the AHL module, presenting each composition record together with its current approval status, the associated item name, and the inventory organization context. The view is documented as VALID and owned by the APPS schema in Oracle EBS 12.1.1 and 12.2.2.

The view's documented purpose is to join lookup meaning and lookup codes to the underlying MTL item information so that item composition records can be reported with human-readable status text rather than raw codes. This makes it a convenient single source for forms, concurrent programs, OAF pages, and external integrations that need item composition data without embedding their own lookup joins.

A distinctive characteristic of the view is that it is date-sensitive. The APPROVAL_STATUS_CODE column is not a direct pass-through of the stored column; it is derived through a DECODE over SIGN(TRUNC(NVL(EFFECTIVE_END_DATE, SYSDATE+1)) - TRUNC(SYSDATE)). Where the composition has not yet passed its effective end date the stored approval status code is returned, and where it has passed, the literal value 'EXPIRED' is substituted. Consequently, the approval status reported by the view reflects the status as of the query execution date, not necessarily the value persisted in the base table.

Underlying Base Objects

The documented base objects referenced by AHL_ITEM_COMP_V are:

• AHL_ITEM_COMPOSITIONS (SYNONYM) — the primary driving table, aliased ICB, supplying the composition identifier, inventory item identifier, master organization, draft flag, effective dates, approval status code, descriptive flexfield attributes, and standard WHO/audit columns.
• AHL_MTL_ITEMS_NON_OU_V (VIEW) — aliased MTL, joined on INVENTORY_ITEM_ID and INVENTORY_MASTER_ORG_ID to supply organization name, organization code, concatenated segments, and item description.
• FND_LOOKUP_VALUES_VL (VIEW) — aliased FND, an outer-joined lookup source constrained to LOOKUP_TYPE = 'AHL_ITEM_COMPOSITION_STATUS', supplying the lookup MEANING and DESCRIPTION for the derived status code.
• HR_GENERAL and HR_SECURITY (PACKAGEs) — referenced indirectly, principally in support of organization and security-group based access filtering applied through the underlying objects.

The join between the composition table and the lookup view is made on the derived status expression rather than on the stored column, which is why an expired composition resolves to the code 'EXPIRED'. Because the lookup join is outer (+), a composition whose status code has no matching lookup row still returns a row, with null meaning and description columns.

Key Columns

• ROW_ID — the ROWID of the underlying AHL_ITEM_COMPOSITIONS row, useful for direct row identification.
• ITEM_COMPOSITION_ID — the primary key of the item composition record.
• INVENTORY_ITEM_ID and INVENTORY_MASTER_ORG_ID — the composed item and its master organization; these form the join to the item/organization view.
• DRAFT_FLAG — indicates whether the composition is still in draft form.
• APPROVAL_STATUS_CODE — the derived status code, returning the stored approval status while the record is effective and 'EXPIRED' once the effective end date has passed.
• APPROVAL_STATUS_MEANING and APPROVAL_STATUS_DESC — the lookup meaning and description corresponding to the derived code.
• ORGANIZATION_NAME, ORGANIZATION_CODE, CONCATENATED_SEGMENTS, and DESCRIPTION — item and organization descriptive attributes sourced from the item view.
• EFFECTIVE_END_DATE — the date that drives the expiry derivation.
• LINK_COMP_ID — linkage identifier to the related composition record.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, SECURITY_GROUP_ID.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield context and segment values.

Common Use Cases and Queries

Typical usage includes listing approved versus draft versus expired item compositions, producing item where-used reports, and feeding external systems that require a readable approval status. Traceability queries commonly filter on APPROVAL_STATUS_MEANING, and date-sensitive reporting relies on the view's automatic expiry handling.

Listing all non-expired compositions for a given item:

• SELECT item_composition_id, inventory_item_id, concatenated_segments, approval_status_meaning FROM ahl_item_comp_v WHERE inventory_item_id = :item_id AND approval_status_code <> 'EXPIRED';

Reporting draft versus approved compositions by organization:

• SELECT organization_code, draft_flag, approval_status_meaning, COUNT(*) FROM ahl_item_comp_v GROUP BY organization_code, draft_flag, approval_status_meaning;

Retrieving the full descriptive detail for one composition, including flexfield attributes:

• SELECT * FROM ahl_item_comp_v WHERE item_composition_id = :comp_id;

Because expiry is evaluated at runtime, queries that must reflect the persisted status rather than the derived status should read AHL_ITEM_COMPOSITIONS directly instead of this view.