Results for “is_in_inventory”

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

Overview

WMS_OP_OPERTN_INSTANCES_ARCH is a table in the WMS (Warehouse Management) schema of Oracle E-Business Suite. It stores operation instance records associated with warehouse execution plans, capturing the individual atomic operations that make up an operation plan. The "_ARCH" suffix indicates that this table functions as an archival counterpart to the operational WMS_OP_OPERTN_INSTANCES table, retaining historical execution records for audit, reporting, and purging purposes after the corresponding live records have been removed from the transactional tables.

In the context of Oracle EBS 12.1.1 and 12.2.2, the table operates within the Warehouse Management module, which drives task execution, directed putaway, picking, and inventory movement across subinventories and locators. The ETRM documentation notes that this object was "not intended for use in Release 11.5.9," which reflects its introduction or redesign in the R12 codebase lineage. The documented physical schema contains 41 columns under the WMS owner.

Based on the heuristic Data Vault classification mined from the foreign key structure, this object is classified as standalone. This is a modeling suggestion rather than a strict rule: the table lacks the dense, multi-parent FK fan-in typical of a link table, and its primary identity is carried by a single surrogate key (OPERATION_INSTANCE_ID) rather than a composite business key. In a Data Vault model, it would most plausibly be realized as a satellite attached to an operation-instance hub, or, given its archival nature, as a snapshot/archive satellite.

Key Information Stored

The most significant columns documented in the ETRM metadata are:

• OPERATION_INSTANCE_ID — the surrogate primary key that uniquely identifies each archived operation instance.
• OPERATION_PLAN_DETAIL_ID — foreign key to WMS_OP_PLAN_DETAILS, linking the instance to its governing plan detail; a strong business-key candidate.
• OP_PLAN_INSTANCE_ID — identifies the parent operation plan instance to which the operation belongs.
• OPERATION_TYPE_ID — references the type/category of operation being executed.
• ACTIVITY_TYPE_ID — classifies the activity performed (for example, putaway, picking, or replenishment).
• OPERATION_SEQUENCE — the ordinal position of the operation within its plan.
• OPERATION_STATUS — the lifecycle state of the operation instance at archival time.
• SUG_TO_SUB_CODE and SUG_TO_LOCATOR_ID — the suggested destination subinventory and locator.
• FROM_SUBINVENTORY_CODE / FROM_LOCATOR_ID and TO_SUBINVENTORY_CODE / TO_LOCATOR_ID — the material movement endpoints.
• SOURCE_TASK_ID — references the originating task.
• EMPLOYEE_ID — foreign key to PSB_EMPLOYEES, identifying the assigned worker.
• EQUIPMENT_ID — the equipment resource used for the operation.
• ACTIVATE_TIME and COMPLETE_TIME — timestamps marking execution start and completion.
• IS_IN_INVENTORY — a flag indicating whether the operation's material was reflected in inventory.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns reserved for customer extensions.

Common Use Cases and Queries

Because this is an archive table, its principal use cases center on historical analysis rather than live transaction processing. Typical patterns include reconstructing labor productivity by joining EMPLOYEE_ID to PSB_EMPLOYEES and comparing ACTIVATE_TIME to COMPLETE_TIME, and auditing material movement accuracy by examining FROM_ and TO_ subinventory/locator pairs against SOURCE_TASK_ID. Reporting queries commonly filter by ORGANIZATION_ID, OPERATION_STATUS, and CREATION_DATE ranges for period-based extracts.

A representative pattern joins the archive to plan details and employee records:

• SELECT a.OPERATION_INSTANCE_ID, a.OPERATION_PLAN_DETAIL_ID, a.EMPLOYEE_ID, a.ACTIVATE_TIME, a.COMPLETE_TIME FROM WMS.WMS_OP_OPERTN_INSTANCES_ARCH a WHERE a.ORGANIZATION_ID = :org_id AND a.CREATION_DATE BETWEEN :start_date AND :end_date;
• Join to WMS.WMS_OP_PLAN_DETAILS on OPERATION_PLAN_DETAIL_ID to retrieve plan context, and to PSB_EMPLOYEES on EMPLOYEE_ID to resolve worker names.
• Aggregate counts by OPERATION_STATUS or ACTIVITY_TYPE_ID for volumetric trend dashboards.

Analysts should treat the archive as read-only and confirm its purge/retention policy before building recurring extracts, since contents depend on archival jobs rather than live task execution.

Related Objects

The most significant related objects are the foreign-key parents and sibling execution tables:

• WMS_OP_PLAN_DETAILS — referenced via OPERATION_PLAN_DETAIL_ID; the plan detail definition each instance executes.
• PSB_EMPLOYEES — referenced via EMPLOYEE_ID; the assigned employee resource.
• WMS_OP_OPERTN_INSTANCES — the live operational counterpart from which archive rows are typically derived.
• WMS_OP_PLAN_INSTANCES — the parent plan instance referenced conceptually via OP_PLAN_INSTANCE_ID.
• WMS_OP_OPERATIONS — the operation definitions linked through OPERATION_TYPE_ID.
• WMS_OP_TASKS / WMS_TASKS — task records correlated through SOURCE_TASK_ID.
• WMS_EQUIPMENTS (or the corresponding WMS equipment object) — resolved through EQUIPMENT_ID.
• INV_SUBINVENTORIES and INV_LOCATORS — resolve the FROM and TO subinventory/locator columns to descriptive names.

These relationships make the archive valuable for cross-module historical reporting while preserving the referential lineage documented in the ETRM metadata.