Results for “commodity_code_id”

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

Overview

MTL_COMMODITY_CODES is an Inventory (INV) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores customer item commodity codes. It sits within the customer item infrastructure, providing a controlled reference list of commodity classifications that can be attached to customer items and to customer item interface records. The table is owned by the INV schema and holds a VALID status in the EBS data dictionary.

From a Data Vault modeling perspective, the heuristic classification for this object is hub-leaning. The reason is its structure: a single surrogate primary key (COMMODITY_CODE_ID), a natural business key candidate (COMMODITY_CODE), and a limited set of descriptive attributes. This pattern is typical of a reference or code table that behaves as a hub with a small satellite of descriptive columns, rather than as a transactional link or a large attribute satellite. In modeling terms, MTL_COMMODITY_CODES is best understood as a stable classification entity that other objects reference through foreign keys.

Key Information Stored

The table contains 29 documented columns. The most significant for functional and interface purposes are:

• COMMODITY_CODE_ID — the surrogate primary key, enforced by MTL_COMMODITY_CODES_PK and also backed by the unique index MTL_COMMODITY_CODES_U1.
• COMMODITY_CODE — the business-key candidate, enforced by the unique index MTL_COMMODITY_CODES_U2. This is the human-readable code used in customer-facing and customs-related contexts.
• DESCRIPTION — the descriptive text associated with the commodity code.
• INACTIVE_DATE — the date the code was end-dated, supporting soft-deletion and historical reporting.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns recording who created and changed the row.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield (DFF) columns, available for customer-specific extension without schema change.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent request and program metadata that identify the batch process responsible for the last change.

The distinction between the surrogate key and the business key matters for integration: external systems should generally key off COMMODITY_CODE, while internal joins use COMMODITY_CODE_ID for referential integrity.

Common Use Cases and Queries

Typical use cases include validating commodity codes during customer item creation, resolving the commodity code on customer item interface imports, and reporting on active versus end-dated codes.

To retrieve active commodity codes:

SELECT commodity_code_id, commodity_code, description
FROM   mtl_commodity_codes
WHERE  inactive_date IS NULL;

To join customer items to their commodity code, use the foreign key path documented for MTL_CUSTOMER_ITEMS:

SELECT ci.customer_item_id, ci.customer_item_number_mv, cc.commodity_code
FROM   mtl_customer_items ci, mtl_commodity_codes cc
WHERE  ci.commodity_code_id = cc.commodity_code_id;

For data migration and error analysis, the same pattern applies to MTL_CI_INTERFACE, which also carries a COMMODITY_CODE_ID foreign key to this table. Reporting scenarios commonly combine MTL_COMMODITY_CODES with customer item and interface data to reconcile imported records against the reference list.

Related Objects

Foreign key metadata documents the following dependent objects:

• MTL_CUSTOMER_ITEMS — references MTL_COMMODITY_CODES via COMMODITY_CODE_ID, associating each customer item with a commodity code.
• MTL_CI_INTERFACE — the customer item open interface, which references MTL_COMMODITY_CODES via COMMODITY_CODE_ID for inbound validation and loading.

In practice, these two tables represent the primary functional consumers of MTL_COMMODITY_CODES. Any report, interface, or API touching customer items or the customer item interface will transitively depend on this reference table through the COMMODITY_CODE_ID join column.