Results for “ahl_workorders_u1”

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

Overview

The AHL.AHL_WORKORDERS table is the central transactional repository for production work orders in Oracle Enterprise Asset Management (eAM) and Complex Maintenance, Repair, and Overhaul (cMRO), delivered under the AHL (Advanced Health and Life Sciences) application schema. It stores work order header information created during maintenance visits and visit tasks, bridging the eAM visit structure to Oracle Work in Process (WIP) discrete jobs. In the context of Oracle EBS 12.1.1 and 12.2.2, the table serves as the source of truth for work order identity, lifecycle status, scheduling dates, route assignment, and links to collection plans and quality results.

From a Data Vault modeling perspective, the documented FK topology classifies AHL_WORKORDERS as a hub: its surrogate key WORKORDER_ID anchors a set of descriptive and foreign-key relationships to visits, visit tasks, routes, WIP entities, plans, and security groups. The presence of standard WHO columns and OBJECT_VERSION_NUMBER indicates that this is an auditable operational table rather than a pure reference table.

Key Information Stored

The table contains 43 columns. The primary key is WORKORDER_ID (NUMBER), enforced by the unique index AHL_WORKORDERS_U1 — the index referenced by the user search term. This is the surrogate identifier referenced by dependent child tables and should be treated as immutable.

Two additional unique indexes represent documented business keys: AHL_WORKORDERS_U2 (WIP_ENTITY_ID) ties the work order to its Oracle WIP discrete job entity, and AHL_WORKORDERS_U3 (WORKORDER_NAME) constrains the human-readable work order name, which documentation indicates should match the JOB_NUMBER in WIP_DISCRETE_JOBS.

• WORKORDER_ID — surrogate primary key; the canonical join key across all child tables.
• WORKORDER_NAME — business identifier, mirroring the WIP discrete job number.
• WIP_ENTITY_ID — assigned after a successful WIP mass load; initially null until the WIP job is created.
• VISIT_ID / VISIT_TASK_ID — foreign keys to AHL_VISITS_B and AHL_VISIT_TASKS_B, defining the maintenance context.
• ROUTE_ID — foreign key to AHL_ROUTES_B, identifying the operations sequence.
• STATUS_CODE — lookup code from FND_LOOKUP_VALUES_VL where lookup_type = 'AHL_JOB_STATUS'; drives lifecycle reporting.
• ACTUAL_START_DATE / ACTUAL_END_DATE — execution timestamps used for duration and cycle-time analysis.
• MASTER_WORKORDER_FLAG — distinguishes master work orders from standard ones.
• AOG_FLAG / HOLD_REASON_CODE / MAINTENANCE_TYPE_CODE — operational qualifiers for aircraft-on-ground handling, hold status, and maintenance categorization.
• CONFIRM_FAILURE_FLAG — indicates whether failure confirmation is required.
• PLAN_ID / COLLECTION_ID — links to QA_PLANS and quality collection results.
• SECURITY_GROUP_ID — supports application hosting and multi-tenant segregation.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield segments for customer-specific extension.
• OBJECT_VERSION_NUMBER and the WHO columns — optimistic locking and audit trail.

Common Use Cases and Queries

Typical reporting scenarios include open work order backlogs, work order status by visit, WIP job synchronization checks, and maintenance volume trending. A representative query joining the work order to its visit and WIP job:

• Backlog by status: SELECT w.STATUS_CODE, COUNT(*) FROM AHL.AHL_WORKORDERS w GROUP BY w.STATUS_CODE to drive dashboards against the AHL_JOB_STATUS lookup.
• WIP reconciliation: SELECT w.WORKORDER_NAME, w.WIP_ENTITY_ID FROM AHL.AHL_WORKORDERS w WHERE w.WIP_ENTITY_ID IS NULL identifies work orders never mass-loaded to WIP.
• Visit drill-down: join AHL_WORKORDERS to AHL_VISITS_B on VISIT_ID and to AHL_VISIT_TASKS_B on VISIT_TASK_ID to report work orders produced within a maintenance event.
• Cycle time: compute ACTUAL_END_DATE minus ACTUAL_START_DATE grouped by ROUTE_ID or MAINTENANCE_TYPE_CODE.
• Quality linkage: join PLAN_ID to QA_PLANS and COLLECTION_ID to the collection tables to trace test results per work order.

Because AHL_WORKORDERS_U1 is a unique index on the primary key, queries filtering by WORKORDER_ID receive optimal access-path performance and should be preferred when driving drill-down reports from the UI.

Related Objects

The following objects are the most significant neighbours based on the documented foreign-key relationships:

• AHL_VISITS_B (join on VISIT_ID) — the maintenance visit header that owns work orders.
• AHL_VISIT_TASKS_B (join on VISIT_TASK_ID) — the specific task generating the work order.
• WIP_ENTITIES (join on WIP_ENTITY_ID) — the Oracle WIP discrete job record.
• AHL_ROUTES_B (join on ROUTE_ID) — the operations route assigned to the work order.
• QA_PLANS (join on PLAN_ID) — the quality collection plan.
• AHL_WORKORDER_OPERATIONS (join on WORKORDER_ID) — the operation-level detail of each work order.
• AHL_WORKORDER_TXNS (join on WORKORDER_ID) — material and resource transactions posted against the work order.
• AHL_WORKORDER_MTL_TXNS (join on NON_ROUTINE_WORKORDER_ID) — non-routine material movements.
• AHL_PART_CHANGES (join on NON_ROUTINE_WORKORDER_ID) — parts replaced during non-routine work.
• AHL_OSP_ORDER_LINES (join on WORKORDER_ID) — outside processing order lines linked to the work order.

Together these objects form the eAM/cMRO work execution graph, with AHL_WORKORDERS acting as the header hub that anchors visit context, WIP execution, route definition, and downstream transaction detail.