Results for “mtl_commodity_codes_active_v”

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

Overview

MTL_COMMODITY_CODES_ACTIVE_V is a view owned by the APPS schema in Oracle E-Business Suite, residing within the INV (Inventory) product family. Its documented purpose is narrowly scoped, described in the ETRM metadata simply as "10SC ONLY," indicating it was introduced to support a specific localization or statutory requirement rather than a broadly used functional component. Functionally, the view exposes the set of commodity codes that are currently active, filtering out any records whose INACTIVE_DATE has already passed. In Oracle EBS 12.1.1 and 12.2.2, this makes the view a convenient reporting and integration interface for consumers who require only valid, non-retired commodity code definitions without having to embed the inactivity predicate themselves.

Underlying Base Objects

The view is defined over a single base object: MTL_COMMODITY_CODES, referenced through a synonym in the APPS schema. The view text projects a fixed list of columns directly from that table and applies one filter in the WHERE clause:

• Filter condition: INACTIVE_DATE IS NULL OR INACTIVE_DATE > SYSDATE

This predicate returns every commodity code that has no inactivation date (permanently active) plus those whose inactivation date is still in the future. Commodity codes whose INACTIVE_DATE is on or before the current system date are excluded. No aggregation, joining, or derivational logic is applied; the view is a straightforward filtered projection of the base table. Because the filter depends on SYSDATE, the result set is time-sensitive and can change as codes reach their inactivation date.

Key Columns

All columns of MTL_COMMODITY_CODES are surfaced by the view. The most significant are:

• COMMODITY_CODE_ID — Primary key identifying the commodity code record.
• COMMODITY_CODE — The user-facing code value used in transactions, classifications, and customs or statistical reporting.
• DESCRIPTION — Descriptive text associated with the code.
• INACTIVE_DATE — The date on which the code ceases to be valid; this column drives the view's filtering logic.
• LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY — Standard audit columns recording creation and last modification.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — The DFF (descriptive flexfield) columns for locale- or implementation-specific extensions.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent program tracking columns identifying the request and program responsible for the last update.

Common Use Cases and Queries

Typical usage centers on lookups and reports that must present only currently valid commodity codes, especially in the 10SC context for which the view was created. A common pattern is a simple list query:

• SELECT COMMODITY_CODE, DESCRIPTION FROM APPS.MTL_COMMODITY_CODES_ACTIVE_V ORDER BY COMMODITY_CODE;
• SELECT COMMODITY_CODE_ID, COMMODITY_CODE, INACTIVE_DATE FROM APPS.MTL_COMMODITY_CODES_ACTIVE_V WHERE COMMODITY_CODE LIKE 'A%';

The view is also useful in validation and integration scenarios where a downstream system must confirm that a supplied commodity code is still active before accepting it, and in extracts where forthcoming invalidations need to be visible via the INACTIVE_DATE column. Because it is a filtered projection, it is suitable for read-only reporting and should not be treated as an update path; direct DML against the underlying MTL_COMMODITY_CODES table remains the maintenance route. Administrators should note that inclusion of a row depends on SYSDATE, so long-running reports and cached extracts may reflect different snapshots than real-time queries.