Search Results effectivity_time

Overview

MTL_ITEM_REVISIONS_ALL_V is an APPS-owned view in the Oracle E-Business Suite Inventory (INV) module. It is documented in the E-Business Suite Technical Reference Manual (ETRM) for releases 12.1.1 and 12.2.2 with the description "10SC ONLY," indicating that the object is exposed for a restricted or specialized configuration rather than as a general-purpose, globally supported interface. The view presents item revision data — the revision-controlled definitions that govern how a given inventory item is identified and tracked at a specific organization — by joining the base revision tables and resolving the language-specific description through the current session language.

Because it is a view rather than a table, MTL_ITEM_REVISIONS_ALL_V imposes no separate storage and reflects the state of its underlying tables at query time. Its principal value in reporting and integration is that it supplies a denormalized, ready-to-query shape for revision information, including the descriptive text that resides in the translation table. This makes it convenient for concurrent programs, custom reports, and inbound/outbound interfaces that must read revision attributes — such as the engineering change notice, the implementation date, and the implemented serial number — without writing the join themselves. Its status is VALID in the documented environment.

Underlying Base Objects

The documented metadata identifies two referenced base objects, both exposed to APPS through synonyms:

• MTL_ITEM_REVISIONS_B — the base (non-translatable) table holding the revision records, aliased as B in the view text.
• MTL_ITEM_REVISIONS_TL — the translation table holding language-dependent descriptive text, aliased as T.

The two are joined on the composite key of INVENTORY_ITEM_ID, ORGANIZATION_ID, and REVISION_ID. The translation side is further filtered by T.LANGUAGE = USERENV('LANG'), so each row returned carries the description in the language of the querying session. All non-description columns, including ROWID, are sourced from the base table. Because the join is restricted to the session language, the view returns at most one translated description per revision record for a given session.

Key Columns

The view exposes the full revision attribute set plus descriptive and audit columns. Significant columns include:

• ROW_ID — the ROWID of the underlying base-table row, useful for direct row access.
• INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION_ID — the composite identifiers that define the revision context and form the join key.
• REVISION, REVISION_LABEL — the revision identifier and its display label.
• DESCRIPTION — the translated revision description drawn from MTL_ITEM_REVISIONS_TL.
• CHANGE_NOTICE, ECN_INITIATION_DATE, IMPLEMENTATION_DATE — engineering change control attributes.
• IMPLEMENTED_SERIAL_NUMBER — the serial number at which the revision takes effect; this is the column most closely associated with the user's search term and is central to serial-tracked revision control.
• EFFECTIVITY_DATE, EFFECTIVITY_TIME — the effective date and its formatted time component, produced by TO_CHAR(B.EFFECTIVITY_DATE,'HH24:MI:SS').
• REVISED_ITEM_SEQUENCE_ID — sequencing attribute for revised items.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard DFF (descriptive flexfield) columns.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical uses include revision reporting per organization, serial-effective revision lookups, and engineering change tracking. The following query retrieves active revisions for an item in an organization, restricted to those with an implemented serial number:

SELECT r.revision, r.revision_label, r.description,
       r.change_notice, r.implementation_date,
       r.implemented_serial_number, r.effectivity_date
FROM   apps.mtl_item_revisions_all_v r
WHERE  r.inventory_item_id = :item_id
AND    r.organization_id  = :org_id
AND    r.implemented_serial_number IS NOT NULL
ORDER BY r.implementation_date;

A second pattern joins the view to item master and organization data to produce a revision report grouped by item, and a third uses the DFF attribute columns to surface customer-specific revision classifications. Because the view is documented as "10SC ONLY," consumers should confirm availability in their specific environment before depending on it in production interfaces.