Search Results to_status_description




Overview
Explore in AI Chat

Overview

APPS.MTL_MATERIAL_STATUS_HIST_ERV is an entity-relationship style reporting view exposed in the Oracle E-Business Suite applications schema. It consolidates the material status change audit trail captured in MTL_MATERIAL_STATUS_HISTORY into a single, denormalized result set that is convenient for reporting, extract, and integration purposes. Rather than presenting status history in its raw transactional form, the view resolves the meaningful attributes of each status change — including organization, subinventory, locator, reason, and the destination material status — into human-readable values.

The view is defined as a UNION of multiple status_change_entity categories, each representing a distinct granularity at which a material status may be applied. The supplied view text enumerates the 'SUBINVENTORY' and 'LOCATOR' branches, with the structure indicating additional branches for item, lot, and serial level status changes. The status_change_entity column identifies which level of the hierarchy a given history row describes, allowing consumers to filter or group by the entity type that is relevant to their analysis.

The user search term "to_status_description" corresponds directly to one of the view's principal projected columns, to_status_description, which surfaces the descriptive name of the status being transitioned into.

Underlying Base Objects

The view is defined over the following documented base objects: HR_ALL_ORGANIZATION_UNITS (synonym), INV_STATUS_PKG (package), MTL_ITEM_LOCATIONS_KFV (view), MTL_MATERIAL_STATUSES (synonym), MTL_MATERIAL_STATUS_HISTORY (synonym), MTL_PARAMETERS (synonym), MTL_SERIAL_NUMBERS (synonym), MTL_SYSTEM_ITEMS_KFV (synonym), and MTL_TRANSACTION_REASONS (synonym).

MTL_MATERIAL_STATUS_HISTORY is the driving table, supplying the status_update_id, organization_id, zone_code, locator_id, lot_number, serial_number, status_id, update_reason_id, and creation_date. MTL_MATERIAL_STATUSES is joined on status_id to resolve both status_code and, critically, the to_status_description value projected by the view. HR_ALL_ORGANIZATION_UNITS provides the organization_name, while MTL_PARAMETERS supplies the organization_code. MTL_TRANSACTION_REASONS is outer-joined via update_reason_id(+) to yield reason_name. The package INV_STATUS_PKG is invoked through its get_from_status_code function to derive the prior status code, which is computed rather than stored. The key flexfield views MTL_ITEM_LOCATIONS_KFV and MTL_SYSTEM_ITEMS_KFV, along with MTL_SERIAL_NUMBERS, are referenced to describe the locator, item, and serial dimensions respectively.

Key Columns

  • status_change_entity — Literal identifying the level of the status change (for example, 'SUBINVENTORY' or 'LOCATOR').
  • status_update_id — Identifier of the source status history record.
  • organization_id / organization_code / organization_name — Inventory organization identifiers and descriptive names.
  • subinventory / locator_id / LOCATOR — Subinventory zone_code and the concatenated locator segments where applicable.
  • from_status_code — Prior status, computed via INV_STATUS_PKG.get_from_status_code.
  • to_status_code / to_status_description / to_status_id — The destination status code, its descriptive text, and its numeric identifier.
  • reason_name — The transaction reason associated with the status update.
  • creation_date — Timestamp of the status change event.

Common Use Cases and Queries

The view is typically used for status change auditing, material availability analysis, and integration extracts. The to_status_description column makes it convenient to report status movements in business language without a separate lookup.

  • Querying all status changes for an organization within a date range.
  • Filtering by status_change_entity to isolate subinventory or locator level movements.
  • Reporting on the reasons driving status transitions.

A representative query retrieving recent status transitions with descriptions:

SELECT organization_code, subinventory, from_status_code, to_status_code, to_status_description, reason_name, creation_date FROM apps.mtl_material_status_hist_erv WHERE organization_id = :org_id AND creation_date >= :from_date AND status_change_entity = 'SUBINVENTORY' ORDER BY creation_date DESC;

Because the view resolves foreign keys and applies an outer join for reasons, it simplifies reporting logic and reduces the need to replicate join conditions across multiple downstream queries.