Search Results wms_op_plan_instances

Overview

The WMS_OP_PLAN_INSTANCES table resides in the WMS (Warehouse Management) schema and forms part of the Oracle Warehouse Management execution engine introduced with the Advanced Planning and Optimization capabilities of Oracle E-Business Suite Release 12. Within the Oracle Applications data model it stores runtime instances of operation plans — the discrete, executable units that a plan definition spawns when work is dispatched to the warehouse floor. Each row represents one run of an operation plan against a specific organization, tracking status, execution window, parent/child hierarchy, and origin/destination location context.

Under the delivery model described in the ETRM metadata, the object is classified heuristically as standalone, which in Data Vault modeling terms suggests it functions as a hub carrying its own business key (OP_PLAN_INSTANCE_ID) rather than as a dependent satellite or link. This classification should be treated as a modeling suggestion; the table is a transactional anchor that other structures would reference.

The base Release 11.5.9 documentation explicitly notes that this object is not intended for use in Release 11.5.9, confirming that the plan-instance mechanism is a 12.1.1 artifact. The documented physical schema for 12.2.2 retains the same 40-column shape, so the table is structurally stable across 12.1.1 and 12.2.2.

Key Information Stored

The primary key is defined by the unique constraint WMS_OP_PLAN_INS_PK over OP_PLAN_INSTANCE_ID. A second unique index, WMS_OP_PLAN_INSTANCES_U1, also keys on OP_PLAN_INSTANCE_ID, so no distinct business-key candidate beyond the surrogate identifier is documented. The most operationally significant columns include:

• OPERATION_PLAN_ID — foreign key into WMS_OP_PLANS_B, identifying the plan definition this instance was created from.
• STATUS — current lifecycle state of the instance (pending, in-progress, complete, error).
• ORGANIZATION_ID — the inventory organization in which the instance executes.
• SYSTEM_TASK_TYPE — discriminates between task families such as pick, putaway, or move.
• CURRENT_OPERATION_INSTANCE_ID — the active step within a multi-step plan.
• PARENT_PLAN_INSTANCE_ID and ROOT_PLAN_INSTANCE_ID — self-referencing hierarchy pointers for nested plans and traceability to the originating instance.
• PLAN_EXECUTION_START_DATE / PLAN_EXECUTION_END_DATE — scheduled execution window.
• TRANSACTION_ID — links the instance to the inventory transaction it generated.
• TASK_SOURCE_ID and SOURCE_TASK_ID — the task that triggered or was referenced by the instance.
• ORIG_SOURCE_SUB_CODE, ORIG_SOURCE_LOC_ID, ORIG_DEST_SUB_CODE, ORIG_DEST_LOC_ID — origin and destination subinventory/locator context.
• ACTIVITY_TYPE_ID and PLAN_TYPE_ID — classification of the activity and plan type.
• Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) plus the ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 descriptive flexfield.

Common Use Cases and Queries

Typical reporting requirements include monitoring open plan instances by organization, reconciling execution duration against the scheduled window, and tracing nested plan hierarchies. A status dashboard query might read:

• SELECT STATUS, COUNT(*) FROM WMS.OP_PLAN_INSTANCES WHERE ORGANIZATION_ID = :p_org GROUP BY STATUS;
• SELECT OP_PLAN_INSTANCE_ID, OPERATION_PLAN_ID, PLAN_EXECUTION_START_DATE, PLAN_EXECUTION_END_DATE FROM WMS.OP_PLAN_INSTANCES WHERE STATUS = 'IN_PROGRESS' AND ORGANIZATION_ID = :p_org;
• SELECT oi.OP_PLAN_INSTANCE_ID, b.PLAN_NAME FROM WMS_OP_PLAN_INSTANCES oi JOIN WMS_OP_PLANS_B b ON b.OPERATION_PLAN_ID = oi.OPERATION_PLAN_ID;
• Hierarchy walk using ROOT_PLAN_INSTANCE_ID to collect all descendants of a dispatch.

Because the table is high-volume and transactional, queries should be filtered on ORGANIZATION_ID, STATUS, or the execution dates, and reads should target the primary-key index wherever possible.

Related Objects

• WMS_OP_PLANS_B — joined via OPERATION_PLAN_ID; holds plan definition header and name.
• WMS_OP_PLAN_INSTANCES (self) — parent/child relationship through PARENT_PLAN_INSTANCE_ID and ROOT_PLAN_INSTANCE_ID.
• WMS_OP_PLAN_INSTANCE_TASKS (or equivalent task-instance table) — child tasks linked through CURRENT_OPERATION_INSTANCE_ID or TASK_SOURCE_ID.
• MTL_TRANSACTIONS / MTL_MATERIAL_TRANSACTIONS — joined on TRANSACTION_ID to relate the instance to executed inventory movements.
• MTL_PARAMETERS / ORG_ORGANIZATION_DEFINITIONS — supplies organization context via ORGANIZATION_ID.
• MTL_ITEM_LOCATIONS / MTL_SECONDARY_INVENTORIES — resolve ORIG_SOURCE_LOC_ID, ORIG_DEST_LOC_ID, and the corresponding sub codes.
• Public WMS execution APIs that drive plan dispatch typically insert and update this table indirectly through WMS task-management PL/SQL packages.