Search Results wms_dispatched_tasks_arch

Overview

WMS_DISPATCHED_TASKS_ARCH is the archive companion to WMS_DISPATCHED_TASKS in the Oracle Warehouse Management (WMS) module. It resides in the WMS schema and stores historical information about warehouse tasks that have been completed. In Oracle EBS 12.1.1 and 12.2.2, dispatched tasks represent discrete units of warehouse labor — put-aways, picks, replenishments, moves, and similar material handling activities — that have been assigned to a user or equipment resource and subsequently executed. As the active task table grows, completed records are migrated to this archive table, preserving the full operational history while keeping the transactional table performant.

From a dimensional modeling perspective, the heuristic Data Vault classification derived from the foreign key structure is link. This classification is a modeling suggestion: the table records relationships and events between warehouse resources (people, machines, equipment instances), organizational units, and inventory transactions, rather than serving as a pure hub of unique business entities or a satellite of descriptive attributes for a single parent.

Key Information Stored

The documented schema contains 65 columns. The most significant are:

• TASK_ID — Surrogate primary key (WMS_DISPATCHED_TASKS_ARCH_PK); the unique identifier for each archived task record.
• TRANSACTION_ID — Links the task to its underlying inventory transaction.
• ORGANIZATION_ID — Warehouse/inventory organization in which the task was performed. Foreign key to MTL_PARAMETERS.
• STATUS — Disposition of the task at completion (for example, completed, cancelled, or dropped).
• USER_TASK_TYPE and TASK_TYPE — Classify the nature of the work (pick, put-away, move, replenish).
• PERSON_ID — The operator who executed the task; joins to PER_ALL_PEOPLE_F using PERSON_ID combined with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
• EQUIPMENT_ID and EQUIPMENT_INSTANCE — Physical equipment or a specific serialized instance used, the latter referencing MTL_SERIAL_NUMBERS.
• PERSON_RESOURCE_ID and MACHINE_RESOURCE_ID — Labor and machine resources from BOM_RESOURCES that were consumed.
• DISPATCHED_TIME, LOADED_TIME, and DROP_OFF_TIME — Timestamps marking the dispatch, load, and completion/drop-off moments, enabling cycle-time analysis.
• INVENTORY_ITEM_ID, REVISION, TRANSACTION_QUANTITY, and TRANSACTION_UOM_CODE — The material moved and its quantity/UOM.
• SOURCE_LOCATOR_ID, DEST_LOCATOR_ID, SOURCE_SUBINVENTORY_CODE, and DEST_SUBINVENTORY_CODE — Origin and destination context for the movement.
• LPN_ID and CONTENT_LPN_ID — License plate numbers identifying the container and its contents.
• OPERATION_PLAN_ID and OP_PLAN_INSTANCE_ID — The operation plan governing the task, the former referencing WMS_OP_PLANS_B.

Common Use Cases and Queries

The archive table supports historical labor reporting, warehouse productivity dashboards, and audit reconciliation of completed tasks. Typical queries join to MTL_PARAMETERS for the organization name and to PER_ALL_PEOPLE_F for the operator. For example, to compute average task cycle time by operator:

SELECT person_id,
       COUNT(*) tasks,
       AVG(drop_off_time - dispatched_time) avg_cycle
FROM   wms.wms_dispatched_tasks_arch
WHERE  organization_id = :org
  AND  dispatched_time >= :from_date
GROUP BY person_id;

Reconciliation queries compare archived tasks against inventory transaction history (TRANSACTION_ID) to confirm that every completed movement is reflected in MTL_MATERIAL_TRANSACTIONS. Volume trending by task type or equipment supports capacity planning.

Related Objects

• WMS_DISPATCHED_TASKS — The active source table from which archived rows originate; shares the same structure.
• MTL_PARAMETERS — Joined on ORGANIZATION_ID; supplies organization context.
• PER_ALL_PEOPLE_F — Joined on PERSON_ID plus the effective date range for operator names.
• BOM_RESOURCES — Referenced by PERSON_RESOURCE_ID and MACHINE_RESOURCE_ID.
• MTL_SERIAL_NUMBERS — Referenced by EQUIPMENT_INSTANCE / EQUIPMENT_ID.
• WMS_OP_PLANS_B — Referenced by OPERATION_PLAN_ID.
• MTL_TXN_SOURCE_TYPES — Referenced by TRANSACTION_SOURCE_TYPE_ID.