Results for “mtl_pending_item_status”

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

Overview

MTL_PENDING_ITEM_STATUS is an Oracle Inventory (INV) transactional history table that records item-status assignment activity. As documented in the ETRM metadata, its description is "History of item-status assignments." The table resides in the INV schema and holds status changes for inventory items, capturing both pending and implemented status transitions together with their effective dates, revisions, lifecycle, and phase context. It is a key supporting object for item lifecycle management in Oracle EBS 12.1.1 and 12.2.2.

From a Data Vault modeling perspective, the mined FK structure classifies this object heuristically as a link. This is a modeling suggestion only: the table sits between items, organizations, and item statuses, and its composite key binds those references together across time. It therefore behaves less like a standalone hub and more like a relationship/transaction record that resolves many-to-many associations between items and their assigned statuses.

Key Information Stored

The table contains 20 documented columns. The primary key is MTL_PENDING_ITEM_STATUS_PK, defined on INVENTORY_ITEM_ID, ORGANIZATION_ID, STATUS_CODE, and EFFECTIVE_DATE. This composite key establishes an item within an organization, its status, and the date the status takes effect as the identifying grain.

• INVENTORY_ITEM_ID — the item being assigned a status; also part of the primary key and business key.
• ORGANIZATION_ID — the inventory organization context; part of the key and an FK to MTL_PARAMETERS.
• STATUS_CODE — the assigned item status; FK to MTL_ITEM_STATUS.
• EFFECTIVE_DATE — date the status assignment becomes effective; part of the primary key.
• IMPLEMENTED_DATE — date the pending status was actually implemented.
• PENDING_FLAG — indicates whether the status assignment is still pending versus implemented.
• REVISION_ID — revision context for the assignment; part of the business-key unique index.
• LIFECYCLE_ID, PHASE_ID — lifecycle and phase context; both appear in the unique index.
• CHANGE_ID, CHANGE_LINE_ID — engineering change order references linked to the status change.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — standard audit columns.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard who columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program audit context.

The unique index MTL_PENDING_ITEM_STATUS_U1 on INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION_ID, LIFECYCLE_ID, PHASE_ID, STATUS_CODE, and EFFECTIVE_DATE represents a broader business-key candidate than the primary key.

Common Use Cases and Queries

This table is typically queried to reconstruct item status history, to identify status changes pending implementation, or to audit who changed a status and when. A common pattern selects the latest effective status for an item within an organization.

• Investigating pending status changes: filter on PENDING_FLAG and compare EFFECTIVE_DATE to IMPLEMENTED_DATE.
• Auditing status transitions over time using EFFECTIVE_DATE ordering.
• Joining to engineering changes via CHANGE_ID and CHANGE_LINE_ID to trace status changes triggered by ECOs.
• Reporting on lifecycle and phase-specific status assignments using LIFECYCLE_ID and PHASE_ID.
• Reconciling concurrent program activity through REQUEST_ID and PROGRAM_ID.

A typical query joins MTL_SYSTEM_ITEMS_B and MTL_PARAMETERS on the key columns and orders by EFFECTIVE_DATE descending to surface the most recent assignment.

Related Objects

The documented foreign keys and reference relationships identify the principal related objects. MTL_ITEM_STATUS is referenced through STATUS_CODE and supplies the status definition. MTL_SYSTEM_ITEMS_B is referenced through INVENTORY_ITEM_ID and ORGANIZATION_ID and supplies item master context. MTL_PARAMETERS is referenced through ORGANIZATION_ID and supplies organization parameters.

• MTL_ITEM_STATUS — joined on STATUS_CODE.
• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• MTL_PARAMETERS — joined on ORGANIZATION_ID.

Depending on the implementation, related status and lifecycle objects such as MTL_ITEM_STATUS and lifecycle/phase definitions referenced by LIFECYCLE_ID and PHASE_ID are also relevant when interpreting the records stored here.