Search Results ahl_schedule_materials_v




Overview
Explore in AI Chat

Overview

In Oracle E-Business Suite Release 12.1.1 and 12.2.2, APPS.AHL_SCHEDULE_MATERIALS_V is a reporting and integration view belonging to the AHL product family (Complex Maintenance Repair and Overhaul — CMRO). It exposes the scheduled material demand associated with a maintenance work order and its visit/task context. The view joins scheduled material lines to work order headers and to the inventory item master, producing a single denormalized row per active scheduled material line. Because callers searching for "service_request" typically need the service request tied to the maintenance visit, the view exposes the SERVICE_REQUEST column derived from the work order header aggregation (AHL_WORKORDERS_V), allowing service request context to be reported alongside material demand.

The view is registered in the APPS schema as VALID and is intended to be queried by concurrent programs, OAF pages, SOA integrations, and custom reports rather than updated directly. It is a read-only projection; DML must be directed to the underlying base tables such as AHL_SCHEDULE_MATERIALS.

Underlying Base Objects

The view text joins three core data sources:

  • AHL_WORKORDERS_V (AWOS) — supplies the work order job number, status, priority, organization and department names, incident number, visit number, and the service request reference.
  • AHL_SCHEDULE_MATERIALS (ASML) — the base demand table providing scheduled material IDs, item, quantity, schedule dates, operation information, and status.
  • MTL_SYSTEM_ITEMS_KFV (MSIK) — resolves the inventory item to its concatenated flexfield segments and primary UOM.

Additional package references documented for this view include AHL_PP_MATERIALS_PVT (used in the ISSUED_QUANTITY derivation), and supporting packages such as AHL_COMPLETIONS_PVT, AHL_PRD_UTIL_PKG, AHL_UTILITY_PVT, AHL_UTIL_UC_PKG, FND_GLOBAL, FND_PROFILE, MO_GLOBAL, HR_SECURITY, and MO_GLOBAL which enforce organization and access control. The primary textual join condition is AWOS.VISIT_TASK_ID = ASML.VISIT_TASK_ID combined with item and organization equality, filtered by ASML.STATUS = 'ACTIVE'.

Key Columns

Common Use Cases and Queries

Typical usage includes material shortage reporting, service request cost/consumption analysis, and integration extracts feeding MES or mobile field service applications. A representative query filtering by service request is shown below:

  • SELECT wo_name, service_request, visit_number, concatenated_segments, schedule_quantity, issued_quantity, job_status_meaning FROM ahl_schedule_materials_v WHERE service_request = :p_service_request;
  • SELECT visit_task_id, inventory_item_id, schedule_date, schedule_quantity - NVL(issued_quantity,0) pending_qty FROM ahl_schedule_materials_v WHERE organization_name = :p_org AND job_status_code = 'RELEASED';

Because the view returns only ACTIVE scheduled material lines, queries should account for completed or cancelled demand by joining to the base schedule materials table directly when historical coverage is required.