Results for “wms_op_plan_instances_arch”

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

Overview

The WMS_OP_PLAN_INSTANCES_ARCH table resides in the WMS (Warehouse Management) schema of Oracle E-Business Suite and serves as an archival store for operation plan instance records generated by the Warehouse Management execution engine. Operation plans represent discrete units of warehouse work — put-away, picking, replenishment, and move tasks — that are planned, sequenced, and dispatched to warehouse operators. The _ARCH suffix indicates that this table holds historical or purged copies of rows originally created in the operational WMS_OP_PLAN_INSTANCES table, preserved for audit, trend analysis, and reconciliation after plan completion or closure.

Per the documented ETRM metadata, the object is classified as standalone under a heuristic Data Vault analysis derived from its foreign key structure. Under a Data Vault modeling suggestion, this suggests it behaves primarily as a satellite-style descriptive store anchored to an operation plan hub, rather than participating in a broad network of link tables. The description explicitly notes the object is "Not intended for use in Release 11.5.9," which confirms its introduction aligns with later WMS functionality available in the 12.1.1 and 12.2.2 code lines. The documented physical schema for 12.2.2 lists 33 columns.

Key Information Stored

The most significant columns, drawn from the documented 33-column schema, are:

• OP_PLAN_INSTANCE_ID — the surrogate primary key uniquely identifying each archived plan instance row.
• OPERATION_PLAN_ID — the foreign key linking back to WMS_OP_PLANS_B; the principal business-key candidate connecting the instance to its parent plan definition.
• ACTIVITY_TYPE_ID and PLAN_TYPE_ID — reference identifiers classifying the nature of the work (for example, picking versus put-away) and the planning strategy applied.
• STATUS — the lifecycle state of the plan instance at the time of archival (for example, completed, cancelled, or closed).
• ORGANIZATION_ID — the warehouse or inventory organization in which the work was executed, essential for multi-org reporting.
• PLAN_EXECUTION_START_DATE and PLAN_EXECUTION_END_DATE — the execution window, supporting duration and throughput analysis.
• ORIG_SOURCE_SUB_CODE, ORIG_SOURCE_LOC_ID, ORIG_DEST_SUB_CODE, ORIG_DEST_LOC_ID — the origin and destination subinventory/locator pairs describing material movement.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit and WHO columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the flexible descriptive flexfield (DFF) segments.

Common Use Cases and Queries

Archived plan instances are typically queried for historical throughput reporting, audit trails, and reconciliation of completed warehouse work.

• Plan execution history per organization:
  SELECT operation_plan_id, status, plan_execution_start_date, plan_execution_end_date FROM wms_op_plan_instances_arch WHERE organization_id = :org_id ORDER BY plan_execution_start_date DESC;
• Join to parent plan definitions:
  SELECT a.op_plan_instance_id, b.* FROM wms_op_plan_instances_arch a, wms_op_plans_b b WHERE a.operation_plan_id = b.operation_plan_id;
• Movement analysis by source and destination: aggregate counts grouped by orig_source_sub_code and orig_dest_sub_code to identify high-traffic locator pairs.
• Status distribution trend: group by status and execution date to detect cancelled versus completed plan ratios.

Because the table is archival, it should generally be queried read-only; no DML should be performed against it.

Related Objects

• WMS_OP_PLANS_B — the parent plan definition table, joined via OPERATION_PLAN_ID.
• WMS_OP_PLAN_INSTANCES — the live operational counterpart from which rows are archived.
• WMS_OP_PLANS_TL — translatable plan names and descriptions.
• WMS_OP_PLAN_ACTIVITIES — activity-level detail associated with plan instances.
• INV_ORGANIZATIONS — resolves ORGANIZATION_ID to warehouse names.
• WMS_DISPATCHED_TASKS — dispatch records for the executed work.
• WMS_LICENSE_PLATE_NUMBERS — LPN context for moves driven by plan instances.