Search Results wms_op_plan_instances




Overview
Explore in AI Chat

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:

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.