Results for “item_instance_id”

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

Overview

The AHL_MM_WRK_ORDER_RESERVTN_V view is an Oracle EBS Applications (APPS) schema database object owned by the AHL – Complex Maintenance Repair and Overhaul product. It presents a consolidated, denormalized snapshot of maintenance work orders alongside the material reservations that supply them, correlating supply-side work orders with reservation-side work orders, item instances, and discrete jobs. The view is defined over both AHL work order tables and Oracle Manufacturing (WIP) and Inventory (INV) tables, which makes it a cross-module reporting construct rather than a transactional entity. It is heavily used for reporting on availability of components and parts in a maintenance, repair, and overhaul context, particularly where the user is interested in when a reserved item will become available. The available_date column is derived from WIP_DISCRETE_JOBS.SCHEDULED_COMPLETION_DATE of the supply-side discrete job, meaning it reflects the projected completion of the work order that will produce or release the reserved material. In EBS 12.1.1 and 12.2.2, the view remains valid and is referenced by AHL reporting and integration logic.

Underlying Base Objects

The view is defined over a join of several base objects. AHL objects include AHL_WORKORDERS (synonym) which is joined twice — once as the supply work order (AWO) and once as the reservation work order (AWR), AHL_VISITS_VL and AHL_VISIT_TASKS_VL (views), AHL_UNIT_EFFECTIVITIES_B, CS_INCIDENTS_ALL_B, and the package AHL_CMP_UTIL_PKG used for the repair batch name. Inventory and manufacturing objects include CSI_ITEM_INSTANCES, MTL_RESERVATIONS, MTL_SYSTEM_ITEMS_KFV, and WIP_DISCRETE_JOBS (again joined twice). Party and organization data come from HZ_PARTIES, HR_ORGANIZATION_UNITS, and the security/utility packages FND_GLOBAL, HR_GENERAL, and HR_SECURITY. Lookup meanings for status are resolved through FND_LOOKUPS against lookup type AHL_JOB_STATUS.

Key Columns

• WORKORDER_ID / WIP_ENTITY_ID / WORKORDER_NAME — identifiers for the supply work order.
• STATUS_CODE / STATUS_MEANING — the work order status and its decoded AHL job status meaning.
• INVENTORY_ITEM_ID / ITEM / ITEM_INSTANCE_ID / INSTANCE_NUMBER — the item and its specific instance being reserved; ITEM returns the concatenated segments from MTL_SYSTEM_ITEMS_KFV.
• AVAILABLE_DATE — sourced from WIP_DISCRETE_JOBS.SCHEDULED_COMPLETION_DATE of the supply-side discrete job; the projected date the reserved material becomes available.
• WORK_ORDER_START_DATE — sourced from WIP_DISCRETE_JOBS.SCHEDULED_START_DATE of the reservation-side job.
• RESERVING_WORK_ORDER_NUM / RESERVATION_ID — the work order holding the reservation and the reservation record identifier.
• ITEM_OWNER / SERIAL_NUMBER / LOT_NUMBER — ownership party and instance tracking attributes.
• NON_ROUTINE_ID / NON_ROUTINE_NUMBER — incident linkage for non-routine work.
• ORG_NAME / ORGANIZATION_ID / REPAIR_BATCH_NAME — organization context and the derived repair batch from AHL_CMP_UTIL_PKG.GET_RPR_BATCH_FOR_INST.

Common Use Cases and Queries

Typical usage centers on determining when reserved material will be available for a maintenance work order. Analysts filter on AVAILABLE_DATE to identify reservations whose supply jobs are due within a window, or to detect reservations without a projected supply date.

Example: list reservations and availability for a given organization.

SELECT workorder_name, reserving_work_order_num, item, serial_number, available_date, org_name FROM apps.ahl_mm_wrk_order_reservtn_v WHERE organization_id = :org_id AND available_date IS NOT NULL ORDER BY available_date;

Example: aggregate reserved items by projected availability month.

SELECT TRUNC(available_date,'MM') avail_month, COUNT(*) reservations FROM apps.ahl_mm_wrk_order_reservtn_v GROUP BY TRUNC(available_date,'MM') ORDER BY 1;

Note that the view embeds lookup decoding and package calls, and applies filters excluding cancelled, deleted, draft, closed, deferred, and complete statuses, so consumers should not re-duplicate that filtering logic.