Results for “ahl_work_assignments”

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

Overview

In Oracle E-Business Suite 12.1.1 and 12.2.2, AHL_WORK_ASSIGNMENTS is a transactional table owned by the AHL schema, part of the Complex Maintenance Repair and Overhaul (MRO) product family. As documented in the ETRM repository, the table "stores the workorder assigments," meaning it captures the linkage between a technician or employee and the operation resource on which that individual performs work. The table is classified as VALID and carries 32 documented columns in the 12.2.2 physical schema.

The heuristic Data Vault classification mined from the foreign key structure is satellite-leaning. In modeling terms, this suggests the table behaves as a descriptive satellite attached to a central hub or link — most plausibly the operation resource and employee relationships — rather than acting as an independent hub of business keys. The assignment record therefore qualifies the relationship it describes with dates, flags, and descriptive attributes rather than defining it outright.

Key Information Stored

The surrogate primary key is ASSIGNMENT_ID, enforced by the constraint AHL_WORK_ASSIGNMENTS_PK. A second unique index, AHL_WORK_ASSIGNMENTS_U1, is also documented on ASSIGNMENT_ID, reinforcing it as the sole business-key candidate in the supplied metadata.

The most operationally significant columns include:

• OPERATION_RESOURCE_ID — foreign key to AHL_OPERATION_RESOURCES, identifying the work order operation resource to which the assignment applies.
• EMPLOYEE_ID — foreign key to PSB_EMPLOYEES, identifying the assigned person.
• INSTANCE_ID — associates the assignment with a specific asset or instance context.
• ASSIGN_START_DATE and ASSIGN_END_DATE — define the effective window of the assignment.
• SELF_ASSIGNED_FLAG — indicates whether the employee claimed the work themselves.
• LOGIN_DATE — captures the login or sign-on associated with the assignment event.
• SERIAL_NUMBER — the serial number of the maintained item.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, governing row-level access in a multi-org or restricted context.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the AHL APIs.

The standard WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — provide audit traceability. Fifteen descriptive ATTRIBUTE columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15) support customer extensibility.

Common Use Cases and Queries

Typical reporting scenarios include technician workload analysis, assignment history by asset, and self-service versus dispatcher-driven assignment ratios. A representative query joining to employees and operation resources:

• SELECT wa.ASSIGNMENT_ID, wa.EMPLOYEE_ID, wa.OPERATION_RESOURCE_ID, wa.ASSIGN_START_DATE, wa.ASSIGN_END_DATE FROM AHL_WORK_ASSIGNMENTS wa WHERE wa.OPERATION_RESOURCE_ID = :resource_id AND SYSDATE BETWEEN wa.ASSIGN_START_DATE AND NVL(wa.ASSIGN_END_DATE, SYSDATE);
• Filtering on SELF_ASSIGNED_FLAG = 'Y' isolates self-assigned work for compliance or productivity reporting.
• Joining to PSB_EMPLOYEES on EMPLOYEE_ID resolves the assigned person's name for dashboards.

Related Objects

• AHL_OPERATION_RESOURCES — referenced via OPERATION_RESOURCE_ID; the parent operation resource record.
• PSB_EMPLOYEES — referenced via EMPLOYEE_ID; the assigned employee master.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; controls row-level security.
• AHL_WORK_ASSIGNMENTS_PK and AHL_WORK_ASSIGNMENTS_U1 — the primary key constraint and unique index on ASSIGNMENT_ID.

These relationships make AHL_WORK_ASSIGNMENTS a dependent satellite within the AHL MRO work-order model, consumed primarily through the AHL maintenance and work-order management APIs.