Results for “ahl_all_workorders_v”

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

Overview

The AHL_ALL_WORKORDERS_V view is a predefined, VALID database object owned by the APPS schema in Oracle E-Business Suite, belonging to the AHL (Complex Maintenance Repair and Overhaul) product family. Its documented purpose is to present work order information for the Production module while honoring Multi-Org operating unit security. Critically, the view description notes that it applies no operating unit restriction to master work orders or to work orders created for costing purposes. This makes it a consolidated, security-aware projection of work order data suitable for reporting, concurrent programs, and integration points within the AHL/CMRO and Manufacturing execution flows in both EBS 12.1.1 and 12.2.2.

The view is designed primarily for read access. Because it joins numerous AHL, WIP, CSI, and HR objects, it acts as a denormalized reporting surface rather than a transactional entry point. Operational Unit filtering is applied through FND_GLOBAL and MO_GLOBAL / HR_SECURITY packages, ensuring that a user querying the view sees only the work orders belonging to their currently effective operating unit.

Underlying Base Objects

The view is defined over a large set of base objects documented in the ETRM 12.2.2 metadata. The core work order entity is AHL_WORKORDERS (accessed via a synonym), joined to WIP_DISCRETE_JOBS for WIP entity context. Work order descriptive attributes such as department, class code, and scheduling dates are sourced from BOM_DEPARTMENTS. Item and serialized unit context come from CSI_ITEM_INSTANCES and MTL_SYSTEM_ITEMS_KFV, while locations and subinventories reference MTL_ITEM_LOCATIONS_KFV.

Visit and maintenance activities are drawn from AHL_VISITS_VL, AHL_VISIT_TASKS_B, and AHL_VISIT_TASKS_VL; routing information comes from AHL_ROUTES_VL and AHL_MR_ROUTES_APP_V; maintenance header data from AHL_MR_HEADERS_B. Unit effectivity and QA data derive from AHL_UNIT_EFFECTIVITIES_B, QA_PLANS_VAL_V, and QA_RESULTS. Project information is retrieved from PA_PROJECTS_ALL and PA_TASKS, incident context from CS_INCIDENTS_ALL_B, and organizational data from ORG_ORGANIZATION_DEFINITIONS and HR_ORGANIZATION_UNITS. Lookups are resolved through FND_LOOKUP_VALUES_VL and MFG_LOOKUPS. Security and utility logic rely on FND_GLOBAL, FND_PROFILE, HR_SECURITY, HR_GENERAL, MO_GLOBAL, AHL_UTILITY_PVT, and AHL_COMPLETIONS_PVT.

Key Columns

Prominent columns exposed by the view include:

• WORKORDER_ID, WIP_ENTITY_ID, OBJECT_VERSION_NUMBER — primary work order identifiers and optimistic locking token.
• JOB_NUMBER (aliased from WORKORDER_NAME), DESCRIPTION, STATUS_CODE and the lookup meaning (MLU.MEANING) — human-readable work order status.
• ORGANIZATION_ID and ORG.NAME — the inventory organization context.
• DEPARTMENT_CODE, DEPARTMENT_CLASS_CODE, CLASS_CODE — departmental attribution of the work order.
• SCHEDULED_START_DATE, SCHEDULED_COMPLETION_DATE, ACTUAL_START_DATE, ACTUAL_END_DATE — plus derived HH24/MI hour and minute columns for time-of-day reporting.
• INVENTORY_ITEM_ID, INSTANCE_ID/ITEM_INSTANCE_ID, UNIT_NAME — serialized unit and item linkage resolved through AHL_UTILITY_PVT.GET_UNIT_NAME.
• INSTANCE_NUMBER, SERIAL_NUMBER, QUANTITY, UNIT_OF_MEASURE — physical asset attributes.
• CONCATENATED_SEGMENTS (item, locator, service item) — flexfield descriptions from MTL_SYSTEM_ITEMS_KFV, MTL_ITEM_LOCATIONS_KFV, and service items.
• VISIT_ID, VISIT_NUMBER, VISIT_NAME, VISIT_TASK_ID, VISIT_TASK_NUMBER — visit and visit task context.
• MR_HEADER_ID, MR_ROUTE_ID, TITLE, ROUTE_ID, ROUTE_NO, REVISION_NUMBER — maintenance route chain.
• SERVICE_ITEM_ID, SERVICE_REQUEST_ID, INCIDENT_NUMBER — service request / incident ties.
• PROJECT_ID, PROJECT_NAME, PROJECT_TASK_ID, TASK_NAME — project and task linkage.
• UNIT_EFFECTIVITY_ID, QA_PLAN_ID, QA_COLLECTION_ID, MR_STATUS — unit effectivity and derived maintenance status via AHL_COMPLETIONS_PVT.GET_MR_STATUS.
• PLAN_ID and other plan/costing attributes.

Common Use Cases and Queries

The view is typically used for reporting on work orders across visits, assets, and projects, and for feeding dashboards or integration extracts. A common query to list work orders for the current operating unit is:

SELECT job_number, description, organization_id, status_code, scheduled_start_date, serial_number, visit_number, route_no FROM ahl_all_workorders_v WHERE status_code = 'RELEASED' ORDER BY scheduled_start_date DESC;

Another practical pattern filters by asset and project to trace maintenance history:

SELECT job_number, serial_number, title, task_name, actual_end_date FROM ahl_all_workorders_v WHERE serial_number = :p_serial AND project_id = :p_project_id;

Because the view already enforces operating unit restriction for standard work orders, developers and report authors can bind the current OU via the standard MO_GLOBAL / FND_GLOBAL mechanism without adding their own org predicate. It is important to remember that master work orders and costing-only work orders are intentionally unrestricted, so reporting queries that must be strictly OU-scoped should add an explicit ORGANIZATION_ID filter. Given its broad join footprint, queries against AHL_ALL_WORKORDERS_V should include selective predicates on work order ID, visit, or organization to avoid unnecessary scans.