Search Results mtl_item_rev_highdate_v




Overview
Explore in AI Chat

Overview

MTL_ITEM_REV_HIGHDATE_V is a read-only view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Inventory (INV) product family and is documented in the ETRM with the terse description "10SC ONLY," indicating that its use is restricted to a specific vertical or localized implementation rather than the standard out-of-the-box Inventory feature set. The view presents item revision records alongside a computed high (end) date, effectively converting the point-in-time effectivity records stored in MTL_ITEM_REVISIONS_B into interval-based records with both a beginning and an ending boundary.

Its principal purpose is to answer the question "which revision of an item was effective between two dates?" Standard revision data only stores EFFECTIVITY_DATE as a start marker. This view derives the corresponding end of each revision's validity window by locating the next-earliest revision's effectivity date for the same organization and item, then subtracting one second (1/(60*60*24) of a day). Where no subsequent revision exists, the view substitutes GREATEST(SYSDATE, REV1.EFFECTIVITY_DATE), so that the currently active revision always projects forward to the present or beyond. Because the CHANGE_NOTICE column is carried through, the view is also used to correlate revision intervals with engineering change notices (ECOs).

Underlying Base Objects

SELECT REV1.ORGANIZATION_ID, REV1.INVENTORY_ITEM_ID, REV1.REVISION_ID,
       REV1.REVISION, REV1.EFFECTIVITY_DATE,
       NVL(MIN(REV2.EFFECTIVITY_DATE - 1/(60*60*24)),
           GREATEST(SYSDATE, REV1.EFFECTIVITY_DATE)),
       REV1.IMPLEMENTATION_DATE, REV1.CHANGE_NOTICE
FROM MTL_ITEM_REVISIONS_B REV2, MTL_ITEM_REVISIONS_B REV1
WHERE ...

The view is defined over a single documented base object, MTL_ITEM_REVISIONS_B (referenced through a SYNONYM in the APPS schema). It is a self-join: the alias REV1 supplies the driving revision row, while REV2 supplies candidate "next" revisions. The join is an outer join (REV2.ORGANIZATION_ID(+), REV2.INVENTORY_ITEM_ID(+), REV2.EFFECTIVITY_DATE(+) > REV1.EFFECTIVITY_DATE) so that the latest revision, which has no successor, is still returned with a NULL match. Aggregation via GROUP BY and MIN() collapses the multiple REV2 candidates into a single earliest-next date per revision, which is then converted into the high date. Note that MTL_ITEM_REVISIONS_B is a _B (base) table; where translated/attribute columns are required, callers would ordinarily pair it with MTL_ITEM_REVISIONS_TL, but this view does not expose that translation.

Key Columns

  • ORGANIZATION_ID — Inventory organization that owns the item revision.
  • INVENTORY_ITEM_ID — Surrogate key of the item; join to MTL_SYSTEM_ITEMS_B.
  • REVISION_ID — Unique identifier of the revision record.
  • REVISION — The human-readable revision label (for example, "A", "B", "C").
  • EFFECTIVITY_DATE — Start date on which the revision becomes effective.
  • HIGH_DATE — Computed end date: the second before the next revision's effectivity date, or GREATEST(SYSDATE, EFFECTIVITY_DATE) for the current revision.
  • IMPLEMENTATION_DATE — Date the revision was implemented.
  • CHANGE_NOTICE — Engineering change notice reference associated with the revision.

Common Use Cases and Queries

The most common application is as-of revision resolution, for example identifying the revision of an item effective on a given date:

SELECT revision, change_notice
FROM   apps.mtl_item_rev_highdate_v
WHERE  organization_id = :org_id
AND    inventory_item_id = :item_id
AND    :as_of_date BETWEEN effectivity_date AND high_date;

Because HIGH_DATE resolves to GREATEST(SYSDATE, EFFECTIVITY_DATE) when no successor exists, the same predicate naturally returns the current revision when the as-of date is today. A second common pattern is ECO/change-notice investigation: filter or group by CHANGE_NOTICE to list every revision introduced by a given notice, together with the interval it remained effective.

SELECT inventory_item_id, revision, effectivity_date, high_date
FROM   apps.mtl_item_rev_highdate_v
WHERE  organization_id = :org_id
AND    change_notice LIKE :eco_pattern
ORDER BY inventory_item_id, effectivity_date;

Given the "10SC ONLY" restriction, the view should be treated as environment-specific: confirm that it exists and is valid in the target instance before embedding it in reports, concurrent programs, or integration interfaces, and prefer it only where interval-based revision logic is genuinely required.